* PostgreSQL Provider

Exists in Mono.Data.PostgreSql now.

We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and DELETE SQL commands using the ExecuteNonQuery method in PgSqlCommand.

We can execute multiple queries and do a NextResult() in PgSqlDataReader() to get the next result set.

We are also able to do simple aggregate functions, ie, count(), sum(), min(), and max() in a simple SELECT SQL query using the ExecuteScalar() now.

We are also able to retrieve data with a simple SELECT SQL query using ExecuteReader() which returns a PgSqlDataReader. We are able to use GetSchemaTable() to get the meta data about the table columns. We are able to Read() to get each row from the result set.

Here is a sample of code that is based on PostgresTest.cs and TestSqlDataReader.cs tests: 

 
 static void SelectData (IDbConnection cnc) {
	
   IDbCommand selectCommand = cnc.CreateCommand();
   IDataReader reader;

   selectCommand.CommandType = CommandType.Text;
   selectCommand.CommandText = 
     "select * from pg_user;" + 
	 "select * from pg_tables;" + 
     "select * from pg_database";

   reader = selectCommand.ExecuteReader ();

   do {
	// get the DataTable that holds
	// the schema
	DataTable dt = rdr.GetSchemaTable();

	if(rdr.RecordsAffected != -1) {
		// Results for 
		// SQL INSERT, UPDATE, DELETE Commands 
		// have RecordsAffected >= 0
		Console.WriteLine("Result is from 
			a SQL Command 
	         (INSERT,UPDATE,DELETE).  
			Records Affected: " + 
				rdr.RecordsAffected);
	}
	else if (dt == null)
		Console.WriteLine("Result is from 
		      a SQL Command 
		      not (INSERT,UPDATE,DELETE).   
		      Records Affected: " + 
				   rdr.RecordsAffected);
	else {
		// Results for
		// SQL not INSERT, UPDATE, nor DELETE
		// have RecordsAffected = -1
		Console.WriteLine("Result is from a 
	           SQL SELECT Query.  
	           Records Affected: " + 
		         rdr.RecordsAffected);
		
		// Results for a SQL Command 
		     (CREATE TABLE, SET, etc)
		// will have a null reference returned 
           from GetSchemaTable()
		// 
		// Results for a SQL SELECT Query
		// will have a DataTable returned 
		     from GetSchemaTable()

		results++;
		Console.WriteLine("Result Set " + 
		         results + "...");
                        			
		// number of columns in the table
		Console.WriteLine("   Total Columns: " +
			dt.Columns.Count);

		// display the schema
		foreach (DataRow schemaRow 
			in dt.Rows) {
			foreach (DataColumn schemaCol 
				in dt.Columns)
				Console.WriteLine(
				    schemaCol.ColumnName + 
					" = " + 
					schemaRow[schemaCol]);
				Console.WriteLine();
			}

			int nRows = 0;
			string output, 
				metadataValue, 
				dataValue;
			// Read and display the rows
			Console.WriteLine("Gonna do a 
				Read() now...");
			while(rdr.Read()) {
				Console.WriteLine("   Row " + 
					nRows + ": ");
					
				for(c = 0; 
					c < rdr.FieldCount; 
					c++) {
					// column meta data 
					DataRow dr = dt.Rows[c];
					metadataValue = 
						"    Col " + 
						c + ": " + 
						dr["ColumnName"];
						
					// column data
					if(rdr.IsDBNull(c) == true)
						dataValue = " is NULL";
					else
						dataValue = 
							": " + 
							rdr.GetValue(c);
				
					// display column meta 
					// data and data
					output = metadataValue + 
							dataValue;					
					Console.WriteLine(output);
				}
				nRows++;
			}
			Console.WriteLine(
				"   Total Rows: " + 
				nRows);
		}	
	} while(rdr.NextResult());
	Console.WriteLine("Total Result sets: " + 
		results);
			
	rdr.Close();
 }

We are able to get String data (char, character, text, varchar), Int16 (smallint), Int32 (integer), Int64 (bigint), DateTime (time, date, timestamp), Boolean (boolean), Single (float), and Double (double). More data types will come later. Note, the types that do work still need thorough testing.

Rows that are returned which contain columns that are NULL are handled now. The PgSqlDataReader method IsDBNull() needs to be called to determine if a field IS NULL before trying to read data from that field.

Calling PostgreSQL stored procedures works. It does not work perfectly. It may not even work to specification - yet. If you want to test it yourself, look at TestSqlDataReader.cs or PostgresTest.cs in mcs/class/System.Data/Test.

Below, I have some sample code you can use to call a PostgreSQL stored procedure named "version". This stored procedure returns a string containing the PostgreSQL server version. Notice the CommandType is StoredProcedure and the method ExecuteScalar() is called.

ExecuteScalar() is a lightweight method in class PgSqlCommand that only returns one row and one column as one object - even if there is more than row or column. 

 static string GetDatabaseServerVersion (PgSqlConnection cnc) 
 {			 
	PgSqlCommand cmd = cnc.CreateCommand ();
	string data;

	cmd.CommandType = CommandType.StoredProcedure;
	cmd.CommandText = "version";
							
	data = (string) cmd.ExecuteScalar ();

	return data;
 }
*

We have the beginnings of Parameters support PostgreSQL. Only Input Parameters are currently supported. Output, Input/Output, and Return parameters still need to be done.

A lot of functionality in System.Data is missing, but the infrastructure is starting to come together.

A lot of Exceptions need to be thrown for various exceptions. However, PgSqlException, PgSqlErrorCollection, and PgSqlError have been partially implemented.

Tim Coleman and Rodrigo Moya got the beginnings of the PgSqlDataAdapter/DataSet/DataTable/DataRow to work. Currently, the PgSqlDataAdapter can Fill() relational data into a DataTable in a DataSet. See the test mcs/class/System.Data/Test/TestSqlDataAdapter.cs to see it in action. Below, I show a snippets from the test: 

 string connectionString;
 string sqlQuery;
 PgSqlDataAdapter adapter;
 DataSet dataSet = null;

 connectionString =
	"host=localhost;" +
	"dbname=test;" +
	"user=postgres";
						
 sqlQuery = "select * from pg_tables";

 adapter = new PgSqlDataAdapter (sqlQuery, 
			connectionString);
 				
 dataSet = new DataSet ();

 adapter.Fill (dataSet);
			
 if (dataSet != null) {
	foreach (DataRow row in dataSet.Tables["Table"].Rows)
		Console.WriteLine("tablename: " + row["tablename"]);
 }
* Testing the PostgreSQL Provider

Installation instructions for PostgreSQL DBMS: On Unix

On Windows

In the path mcs/class/System.Data/Test there is a PostgreSQL test program named PostgreTest.cs. Thanks goes to Gonzalo for creating the original PostgreSQL test.

To use it to test System.Data, you modify the file to your PostgreSQL database connection requirements:

The connection string is in OLE-DB connection string format. Internally, SqlConnection converts this to the PostgreSQL connection string format. 

    OLE-DB: "host=localhost;dbname=test;user=joe;password=smoe"
PostgreSQL: "host=localhost dbname=test user=joe password=smoe"

Note that OLE-DB includes the semicolons while PostgreSQL's connection string does not.

To compile the PostgresTest.cs program, do: 

 mcs PostgresTest.cs \
    -r System.Data.dll \
    -r Mono.Data.PostgreSqlClient.dll

If there are compile errors, such as, can not convert IDbConnection to PgSqlConnection, then you need to run mcs like: 

 mono f:/cygwin/home/DanielMorgan/mono/install/bin/mcs.exe \
    PostgresTest.cs \
    -r System.Data.dll \
    -r Mono.Data.PostgreSqlClient.dll

To run using mint, do: 

mint PostgresTest.exe

To run using mono, do: 

mono PostgresTest.exe

Below, I show how the output from PostgresTest. I have omitted a lot of the meta data for the columns except two columns. The classes used were from Mono.Data.PostgreSqlClient and were used to connect to a PostgreSQL database and retrieve data. 

   

 danmorg@DANPC ~/mono/mcs/class/System.Data/Test
 $ mcs PostgresTest.cs -r System.Data.dll

 danmorg@DANPC ~/mono/mcs/class/System.Data/Test
 $ mono PostgresTest.exe
        Postgres provider specific tests...

                Drop table:
 Error (don't worry about this one)SqlError:PGRES_FATAL_ERROR ERROR:  
 table "mono_postgres_test" does not exist
 

                Create table with all supported types:
 OK
                Insert values for all known types:
 OK
                Update values:
 OK
                Insert values for all known types:
 OK
 Aggregate: count(*)
 Agg Result: 2
 Aggregate: min(text_value)
 Agg Result: This is a text
 Aggregate: max(int4_value)
 Agg Result: 1048000
 Aggregate: sum(int4_value)
 Agg Result: 1048003
                Select values from the database:
 Result is from a SELECT SQL Query.  Records Affected: -1
 Result Set 1...
   Total Columns: 28
 ColumnName = boolean_value
 ColumnOrdinal = 1
 ColumnSize = 1
 NumericPrecision = 0
 NumericScale = 0
 IsUnique = False
 IsKey =
 BaseCatalogName =
 BaseColumnName = boolean_value
 BaseSchemaName =
 BaseTableName =
 DataType = System.Boolean
 AllowDBNull = False
 ProviderType = 16
 IsAliased = False
 IsExpression = False
 IsIdentity = False
 IsAutoIncrement = False
 IsRowVersion = False
 IsHidden = False
 IsLong = False
 IsReadOnly = False

 ...

 ColumnName = null_timestamp_value
 ColumnOrdinal = 28
 ColumnSize = 8
 NumericPrecision = 0
 NumericScale = 0
 IsUnique = False
 IsKey =
 BaseCatalogName =
 BaseColumnName = null_timestamp_value
 BaseSchemaName =
 BaseTableName =
 DataType = System.DateTime
 AllowDBNull = False
 ProviderType = 1184
 IsAliased = False
 IsExpression = False
 IsIdentity = False
 IsAutoIncrement = False
 IsRowVersion = False
 IsHidden = False
 IsLong = False
 IsReadOnly = False

 Gonna do a Read() now...
   Row 0:
    Col 0: boolean_value: False
    Col 1: int2_value: 5
    Col 2: int4_value: 3
    Col 3: bigint_value: 9
    Col 4: float_value: 3.141590
    Col 5: double_value: 3.14159
    Col 6: numeric_value: 123456789012.345
    Col 7: char_value: Mono.Data!
    Col 8: varchar_value: It was not me!
    Col 9: text_value: We got data!
    Col 10: point_value: (1,0)
    Col 11: time_value: 01/01/1 21:13:14
    Col 12: date_value: 02/29/2000 00:00:00
    Col 13: timestamp_value: 02/29/2004 14:00:11
    Col 14: null_boolean_value is NULL
    Col 15: null_int2_value is NULL
    Col 16: null_int4_value is NULL
    Col 17: null_bigint_value is NULL
    Col 18: null_float_value is NULL
    Col 19: null_double_value is NULL
    Col 20: null_numeric_value is NULL
    Col 21: null_char_value is NULL
    Col 22: null_varchar_value is NULL
    Col 23: null_text_value is NULL
    Col 24: null_point_value is NULL
    Col 25: null_time_value is NULL
    Col 26: null_date_value is NULL
    Col 27: null_timestamp_value is NULL
   Row 1:
    Col 0: boolean_value: True
    Col 1: int2_value: -22
    Col 2: int4_value: 1048000
    Col 3: bigint_value: 123456789012345
    Col 4: float_value: 3.141590
    Col 5: double_value: 3.14159
    Col 6: numeric_value: 123456789012.345
    Col 7: char_value: This is a char
    Col 8: varchar_value: This is a varchar
    Col 9: text_value: This is a text
    Col 10: point_value: (1,0)
    Col 11: time_value: 01/01/1 21:13:14
    Col 12: date_value: 02/29/2000 00:00:00
    Col 13: timestamp_value: 02/29/2004 14:00:11
    Col 14: null_boolean_value is NULL
    Col 15: null_int2_value is NULL
    Col 16: null_int4_value is NULL
    Col 17: null_bigint_value is NULL
    Col 18: null_float_value is NULL
    Col 19: null_double_value is NULL
    Col 20: null_numeric_value is NULL
    Col 21: null_char_value is NULL
    Col 22: null_varchar_value is NULL
    Col 23: null_text_value is NULL
    Col 24: null_point_value is NULL
    Col 25: null_time_value is NULL
    Col 26: null_date_value is NULL
    Col 27: null_timestamp_value is NULL
   Total Rows Retrieved: 2
 Total Result sets: 1
                Call ExecuteReader with a SQL Command. 
                (Not INSERT,UPDATE,DELETE
 ).
 Result is from a SQL Command not (INSERT,UPDATE,DELETE).  
                        Records Affected: -1
 Total Result sets: 0
                Call ExecuteReader with a SQL Command. 
                        (Is INSERT,UPDATE,DELETE)
 .
 Result is from a SQL Command (INSERT,UPDATE,DELETE).  Records Affected: 1
 Total Result sets: 0
                Calling stored procedure version()
 Result: PostgreSQL 7.2.1 on i686-pc-cygwin, compiled by GCC 2.95.3-5
 Database Server Version: PostgreSQL 7.2.1 on i686-pc-cygwin, 
              compiled by GCC 2.9
 5.3-5
 Clean up...
                Drop table...
 OK
 RESULT: 0