1 * Status of the PostgreSQL ADO.NET Provider
\r
3 <p> Still exists in System.Data.SqlClient and needs to be moved to Mono.Data.PostgreSQL.
\r
5 <p>What follows below is Status information for the PostgreSQL ADO.NET provider.
7 <p>We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and
8 DELETE SQL commands using the ExecuteNonQuery method in SqlCommand.
10 <p>We can execute multiple queries and do a NextResult() in SqlDataReader()
11 to get the next result set.
13 <p>We are also able to do simple aggregate functions,
14 ie, count(), sum(), min(), and max()
15 in a simple SELECT SQL query using the ExecuteScalar() now.
17 <p>We are also able to retrieve data with a simple SELECT SQL query
18 using ExecuteReader() which returns a SqlDataReader. We are able to
19 use GetSchemaTable() to get the meta data about the table columns.
20 We are able to Read() to get each row from the result set.
22 <p>Here is a sample of code that is based on PostgresTest.cs and
23 TestSqlDataReader.cs tests:
26 static void SelectData (IDbConnection cnc) {
28 IDbCommand selectCommand = cnc.CreateCommand();
31 selectCommand.CommandType = CommandType.Text;
32 selectCommand.CommandText =
33 "select * from pg_user;" +
\r
34 "select * from pg_tables;" +
\r
35 "select * from pg_database";
\r
37 reader = selectCommand.ExecuteReader ();
40 // get the DataTable that holds
\r
42 DataTable dt = rdr.GetSchemaTable();
\r
44 if(rdr.RecordsAffected != -1) {
\r
46 // SQL INSERT, UPDATE, DELETE Commands
\r
47 // have RecordsAffected >= 0
\r
48 Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
\r
50 else if (dt == null)
\r
51 Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
\r
54 // SQL not INSERT, UPDATE, nor DELETE
\r
55 // have RecordsAffected = -1
\r
56 Console.WriteLine("Result is from a SQL SELECT Query. Records Affected: " + rdr.RecordsAffected);
\r
58 // Results for a SQL Command (CREATE TABLE, SET, etc)
\r
59 // will have a null reference returned from GetSchemaTable()
\r
61 // Results for a SQL SELECT Query
\r
62 // will have a DataTable returned from GetSchemaTable()
\r
65 Console.WriteLine("Result Set " + results + "...");
\r
67 // number of columns in the table
\r
68 Console.WriteLine(" Total Columns: " +
\r
71 // display the schema
\r
72 foreach (DataRow schemaRow in dt.Rows) {
\r
73 foreach (DataColumn schemaCol in dt.Columns)
\r
74 Console.WriteLine(schemaCol.ColumnName +
\r
76 schemaRow[schemaCol]);
\r
77 Console.WriteLine();
\r
81 string output, metadataValue, dataValue;
\r
82 // Read and display the rows
\r
83 Console.WriteLine("Gonna do a Read() now...");
\r
85 Console.WriteLine(" Row " + nRows + ": ");
\r
87 for(c = 0; c < rdr.FieldCount; c++) {
\r
88 // column meta data
\r
89 DataRow dr = dt.Rows[c];
\r
96 if(rdr.IsDBNull(c) == true)
\r
97 dataValue = " is NULL";
\r
103 // display column meta data and data
\r
104 output = metadataValue + dataValue;
\r
105 Console.WriteLine(output);
\r
109 Console.WriteLine(" Total Rows: " +
\r
112 } while(rdr.NextResult());
\r
113 Console.WriteLine("Total Result sets: " + results);
\r
120 <p>We are able to get
121 String data (char, character, text, varchar), Int16 (smallint),
122 Int32 (integer), Int64 (bigint), DateTime (time, date, timestamp),
123 Boolean (boolean), Single (float), and Double (double).
124 More data types will come later. Note, the types that do work still
125 need thorough testing.
127 <p>Rows that are returned which contain columns that are NULL are handled now.
128 The SqlDataReader method IsDBNull() needs to be called to determine
129 if a field IS NULL before trying to read data from that field.
131 <p>Calling PostgreSQL stored procedures works. It does not work perfectly. It may not
132 even work to specification - yet. If you want to test it yourself, look at
133 TestSqlDataReader.cs or PostgresTest.cs in
134 mcs/class/System.Data/Test.
136 <p>Below, I have some sample code you can
137 use to call a PostgreSQL stored procedure named "version". This stored
138 procedure returns a string containing the PostgreSQL server version. Notice
139 the CommandType is StoredProcedure and the method ExecuteScalar() is called.
141 <p>ExecuteScalar() is a lightweight method in class SqlCommand that only returns
142 one row and one column as one object - even if there is more than row or column.
145 static string GetDatabaseServerVersion (SqlConnection cnc)
147 SqlCommand cmd = cnc.CreateCommand ();
150 cmd.CommandType = CommandType.StoredProcedure;
151 cmd.CommandText = "version";
153 data = (string) cmd.ExecuteScalar ();
159 <p>We have the beginnings of Parameters support PostgreSQL. Only
160 Input Parameters are currently supported. Output, Input/Output,
161 and Return parameters still need to be done.
163 <p>A lot of functionality in System.Data is missing, but the
164 infrastructure is starting to come together.
166 <p>A lot of Exceptions need to be thrown for various exceptions. However,
167 SqlException, SqlErrorCollection, and SqlError have been partially
170 <p>Tim Coleman and Rodrigo Moya got the beginnings of the
171 SqlDataAdapter/DataSet/DataTable/DataRow to work. Currently,
172 the SqlDataAdapter can Fill() relational data into a DataTable in a DataSet.
173 See the test mcs/class/System.Data/Test/TestSqlDataAdapter.cs to see it in action.
174 Below, I show a snippets from the test:
177 string connectionString;
179 SqlDataAdapter adapter;
180 DataSet dataSet = null;
187 sqlQuery = "select * from pg_tables";
189 adapter = new SqlDataAdapter (sqlQuery,
192 dataSet = new DataSet ();
194 adapter.Fill (dataSet);
196 if (dataSet != null) {
197 foreach (DataRow row in dataSet.Tables["Table"].Rows)
198 Console.WriteLine("tablename: " + row["tablename"]);
202 * Testing the PostgreSQL ADO.NET Provider
204 <p>In order to test System.Data.SqlClient, you will need to have
205 access to a remote PostgreSQL DBMS, or you will have to install
206 one locally. PostgreSQL is the DBMS used for the initial
207 implementation of System.Data.SqlClient.
209 <p>Why? Because it is free software, has a client
210 library that is easy to use, PostgreSQL is easy to install on
211 Unix and Windows (using the Cygwin install program), not difficult to setup after
212 installation, and it runs under: Linux,
213 Windows (via cygwin and ipc-daemon), Unix, and
214 others. This allowed us to create the
215 System.Data functionality in Mono much quicker.
217 <p>If you plan on using a remote PostgreSQL DBMS Server,
218 than you will need to have the PostgreSQL client software on your
219 local computer that includes libpq.so (pq.dll on Windows).
221 <p>The System.Data tests use this connection string to connect
222 to the PostgreSQL database named "test" at host "localhost" as
226 "host=localhost;dbname=test;user=postgres"
229 <p>Installation instructions for PostgreSQL DBMS:
234 * Read the PostgreSQL Installation Instructions
235 at \usr\doc\postgresql-x.x.x\html\installation.html
237 * Depending on your Unix system,
238 PostgreSQL maybe already installed, a database user 'postgres' created,
239 a linux user 'postgres' created and initdb ran. Or maybe not.
244 mkdir /usr/local/pgsql/data
\r
245 chown postgres /usr/local/pgsql/data
\r
247 initdb -D /usr/local/pgsql/data
\r
248 postmaster -i -D /usr/local/pgsql/data
\r
253 * Make sure you have a database user named postgres. It is best to install
254 the PostgreSQL DBMS under linux user postgres. When you run the postmaster,
255 run it under the user postgres as well. If this was not done, then you
256 will need to create a user named postgres for the System.Data tests.
258 * If you already installed PostgeSQL and you do not have a database
259 user named postgres, then you can create user postgres using psql:
263 create user postgres with password 'fun2db';
266 * The postmaster must be run with -i option.
268 * In the /usr/local/pgsql/data/pg_hba.conf file, you need
269 to have the AUTH_TYPE set to md5. You can read more on this at
270 /usr/doc/postgresql-7.2.1/html/client-authentication.html
272 PostgreSQL html docs are located. See the 2nd line below,
273 host 127.0.0.1 has an AUTH_TYPE md5 in pg_hba.conf.
276 # TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE
279 host all 127.0.0.1 255.255.255.255 md5
282 * If you can not find your PostgreSQL documentation locally or you
283 did not install it, then you
284 can get it <a href="http://www.postgresql.org/idocs/">here</a>.
291 * Use the <a href="http://www.cygwin.com/">Cygwin</a> installer to
292 install the PostgreSQL DBMS. It is
293 found in the database category.
295 * <p>Read the file postgres-x.x.README at /usr/doc/Cygwin and read
296 the requirements to install PostgreSQL. Those requirements
297 are included with cygwin except cygipc. A default installtion
298 of cygwin does not install everything you will need, so on the
299 safe side, just include everything when installing cygwin.
301 * <p>The -x.x in postgres-x.x is the version of your PostgreSQL DBMS.
303 * <p>Once Cygwin has installed the PostgreSQL DBMS on your computer,
304 read the file FAQ_MSWIN which is available
305 in /usr/doc/postgres-x.x
307 * <p>Important notes from this file are:
310 <p><b>2.</b> - Install the latest <a href="http://www.neuro.gatech.edu/users/cwilson/cygutils/OBSOLETE/V1.1/cygipc/index.html">CygIPC</a> package.
312 <p>The cygipc package contains the ipc-daemon you will need
313 to run before you can
314 run the PostgreSQL DBMS Server daemon (postmaster) or run
315 initdb which initializes the PostgreSQL database.
317 <p><b>3.</b> The Cygwin bin directory has to be placed in
318 the path before the Windows program directories,
319 for example, C:\cygwin\bin
321 <p><b>My own note.</b> In the Windows control panel, I set
322 the environment variables PATH to my cygwin /usr/local/bin,
323 /usr/bin, and /bin. I also set my LD_LIBRARY_PATH to
324 /usr/local/lib and /usr/lib. For example:
328 PATH=c:\cygwin\usr\local\bin;c:\cygwin\usr\bin;c:\cygwin\bin;
329 LD_LIBRARY_PATH=c:\cygwin\usr\local\lib;c:\cygwin\usr\lib;
332 <p><b>4.</b> Start the ipc-daemon that came with the cygipc
334 are two ways to do this: run it from the command line as:
340 <p>or you can set it up as a Windows service. See the
341 file cygrunsrv.README at /usr/doc/Cygwin on how to do this
342 for ipc-daemon and postmaster. Note the
343 troubleshooting section at the end of
344 the cygrunsrv.README file.
346 <p>To install ipc-daemon as a service,
351 ipc-daemon --install-as-service' (--remove-as-service)
361 <p>Read the installation.html file
362 at /usr/doc/postgresql-x.x/html/installation.html
364 <p>You will see in this file that you will need to
365 run the following commands:
369 mkdir /usr/local/pgsql/data
\r
370 initdb -D /usr/local/pgsql/data
\r
371 postmaster -D /usr/local/pgsql/data
\r
376 <p>When you need to connect to the database,
377 you will need ipc-daemon and postmaster running. Start ipc-daemon
378 before any of the command above. If you restart your computer, you
379 need to start ipc-daemon and postmaster either manually or as a
382 <p>psql is a command-line PostgreSQL client tool to
383 enter and run SQL commands and queries.
385 <p>If there is no database user named postgres, create a user named
386 postgres with the following SQL command in the client tool psql:
391 create user postgres with password 'fun2db';
393 <p>The only reason I say this is so you can easily use the System.Data tests
394 without having to change the database, userid, etc.
397 <p>In the path mcs/class/System.Data/Test
398 there is a PostgreSQL test program named
399 PostgreTest.cs. Thanks goes to Gonzalo for creating the original
402 <p>To use it to test System.Data, you
403 modify the file to your PostgreSQL database
404 connection requirements:
408 <li><b>dbname</b> database, ie., test</li>
409 <li><b>host</b> hostname of the PostgreSQL DBMS Server to connect to, ie., localhost</li>
410 <li><b>user</b> username, ie., someuser</li>
411 <li><b>password</b> password, ie., mypass1234</li>
414 <p>The connection string is in OLE-DB connection string format. Internally,
415 SqlConnection converts this to the PostgreSQL connection string format.
419 OLE-DB: "host=localhost;dbname=test;user=joe;password=smoe"
420 PostgreSQL: "host=localhost dbname=test user=joe password=smoe"
424 Note that OLE-DB includes the semicolons while PostgreSQL's connection
428 To compile the PostgresTest.cs program, do:
432 mcs PostgresTest.cs -r System.Data.dll
436 To run using mint, do:
440 mint PostgresTest.exe
444 To run using mono, do:
446 mono PostgresTest.exe
449 <p>Below, I show how the output from PostgresTest. I have omitted a lot
450 of the meta data for the columns except two columns. The classes
451 used were from System.Data.SqlClient and were used to connect to a
452 PostgreSQL database and retrieve data.
457 danmorg@DANPC ~/mono/mcs/class/System.Data/Test
\r
458 $ mcs PostgresTest.cs -r System.Data.dll
\r
460 danmorg@DANPC ~/mono/mcs/class/System.Data/Test
\r
461 $ mono PostgresTest.exe
\r
462 Postgres provider specific tests...
\r
465 Error (don't worry about this one)SqlError:PGRES_FATAL_ERROR ERROR: table "mono
\r
466 _postgres_test" does not exist
\r
469 Create table with all supported types:
\r
471 Insert values for all known types:
\r
475 Insert values for all known types:
\r
477 Aggregate: count(*)
\r
479 Aggregate: min(text_value)
\r
480 Agg Result: This is a text
\r
481 Aggregate: max(int4_value)
\r
482 Agg Result: 1048000
\r
483 Aggregate: sum(int4_value)
\r
484 Agg Result: 1048003
\r
485 Select values from the database:
\r
486 Result is from a SELECT SQL Query. Records Affected: -1
\r
489 ColumnName = boolean_value
\r
492 NumericPrecision = 0
\r
497 BaseColumnName = boolean_value
\r
500 DataType = System.Boolean
\r
501 AllowDBNull = False
\r
504 IsExpression = False
\r
506 IsAutoIncrement = False
\r
507 IsRowVersion = False
\r
514 ColumnName = null_timestamp_value
\r
517 NumericPrecision = 0
\r
522 BaseColumnName = null_timestamp_value
\r
525 DataType = System.DateTime
\r
526 AllowDBNull = False
\r
527 ProviderType = 1184
\r
529 IsExpression = False
\r
531 IsAutoIncrement = False
\r
532 IsRowVersion = False
\r
537 Gonna do a Read() now...
\r
539 Col 0: boolean_value: False
\r
540 Col 1: int2_value: 5
\r
541 Col 2: int4_value: 3
\r
542 Col 3: bigint_value: 9
\r
543 Col 4: float_value: 3.141590
\r
544 Col 5: double_value: 3.14159
\r
545 Col 6: numeric_value: 123456789012.345
\r
546 Col 7: char_value: Mono.Data!
\r
547 Col 8: varchar_value: It was not me!
\r
548 Col 9: text_value: We got data!
\r
549 Col 10: point_value: (1,0)
\r
550 Col 11: time_value: 01/01/1 21:13:14
\r
551 Col 12: date_value: 02/29/2000 00:00:00
\r
552 Col 13: timestamp_value: 02/29/2004 14:00:11
\r
553 Col 14: null_boolean_value is NULL
\r
554 Col 15: null_int2_value is NULL
\r
555 Col 16: null_int4_value is NULL
\r
556 Col 17: null_bigint_value is NULL
\r
557 Col 18: null_float_value is NULL
\r
558 Col 19: null_double_value is NULL
\r
559 Col 20: null_numeric_value is NULL
\r
560 Col 21: null_char_value is NULL
\r
561 Col 22: null_varchar_value is NULL
\r
562 Col 23: null_text_value is NULL
\r
563 Col 24: null_point_value is NULL
\r
564 Col 25: null_time_value is NULL
\r
565 Col 26: null_date_value is NULL
\r
566 Col 27: null_timestamp_value is NULL
\r
568 Col 0: boolean_value: True
\r
569 Col 1: int2_value: -22
\r
570 Col 2: int4_value: 1048000
\r
571 Col 3: bigint_value: 123456789012345
\r
572 Col 4: float_value: 3.141590
\r
573 Col 5: double_value: 3.14159
\r
574 Col 6: numeric_value: 123456789012.345
\r
575 Col 7: char_value: This is a char
\r
576 Col 8: varchar_value: This is a varchar
\r
577 Col 9: text_value: This is a text
\r
578 Col 10: point_value: (1,0)
\r
579 Col 11: time_value: 01/01/1 21:13:14
\r
580 Col 12: date_value: 02/29/2000 00:00:00
\r
581 Col 13: timestamp_value: 02/29/2004 14:00:11
\r
582 Col 14: null_boolean_value is NULL
\r
583 Col 15: null_int2_value is NULL
\r
584 Col 16: null_int4_value is NULL
\r
585 Col 17: null_bigint_value is NULL
\r
586 Col 18: null_float_value is NULL
\r
587 Col 19: null_double_value is NULL
\r
588 Col 20: null_numeric_value is NULL
\r
589 Col 21: null_char_value is NULL
\r
590 Col 22: null_varchar_value is NULL
\r
591 Col 23: null_text_value is NULL
\r
592 Col 24: null_point_value is NULL
\r
593 Col 25: null_time_value is NULL
\r
594 Col 26: null_date_value is NULL
\r
595 Col 27: null_timestamp_value is NULL
\r
596 Total Rows Retrieved: 2
\r
597 Total Result sets: 1
\r
598 Call ExecuteReader with a SQL Command. (Not INSERT,UPDATE,DELETE
\r
600 Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: -1
\r
601 Total Result sets: 0
\r
602 Call ExecuteReader with a SQL Command. (Is INSERT,UPDATE,DELETE)
\r
604 Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: 1
\r
605 Total Result sets: 0
\r
606 Calling stored procedure version()
\r
607 Result: PostgreSQL 7.2.1 on i686-pc-cygwin, compiled by GCC 2.95.3-5
\r
608 Database Server Version: PostgreSQL 7.2.1 on i686-pc-cygwin, compiled by GCC 2.9
\r