1 * PostgreSQL Provider
\r
3 <p>Exists in Mono.Data.PostgreSql now.
\r
5 <p>We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and
6 DELETE SQL commands using the ExecuteNonQuery method in PgSqlCommand.
8 <p>We can execute multiple queries and do a NextResult() in PgSqlDataReader()
9 to get the next result set.
11 <p>We are also able to do simple aggregate functions,
12 ie, count(), sum(), min(), and max()
13 in a simple SELECT SQL query using the ExecuteScalar() now.
15 <p>We are also able to retrieve data with a simple SELECT SQL query
16 using ExecuteReader() which returns a PgSqlDataReader. We are able to
17 use GetSchemaTable() to get the meta data about the table columns.
18 We are able to Read() to get each row from the result set.
20 <p>Here is a sample of code that is based on PostgresTest.cs and
21 TestSqlDataReader.cs tests:
25 static void SelectData (IDbConnection cnc) {
27 IDbCommand selectCommand = cnc.CreateCommand();
30 selectCommand.CommandType = CommandType.Text;
31 selectCommand.CommandText =
32 "select * from pg_user;" +
\r
33 "select * from pg_tables;" +
\r
34 "select * from pg_database";
\r
36 reader = selectCommand.ExecuteReader ();
39 // get the DataTable that holds
\r
41 DataTable dt = rdr.GetSchemaTable();
\r
43 if(rdr.RecordsAffected != -1) {
\r
45 // SQL INSERT, UPDATE, DELETE Commands
\r
46 // have RecordsAffected >= 0
\r
47 Console.WriteLine("Result is from
\r
49 (INSERT,UPDATE,DELETE).
\r
50 Records Affected: " +
\r
51 rdr.RecordsAffected);
\r
53 else if (dt == null)
\r
54 Console.WriteLine("Result is from
\r
56 not (INSERT,UPDATE,DELETE).
\r
57 Records Affected: " +
\r
58 rdr.RecordsAffected);
\r
61 // SQL not INSERT, UPDATE, nor DELETE
\r
62 // have RecordsAffected = -1
\r
63 Console.WriteLine("Result is from a
\r
65 Records Affected: " +
\r
66 rdr.RecordsAffected);
\r
68 // Results for a SQL Command
\r
69 (CREATE TABLE, SET, etc)
\r
70 // will have a null reference returned
\r
71 from GetSchemaTable()
\r
73 // Results for a SQL SELECT Query
\r
74 // will have a DataTable returned
\r
75 from GetSchemaTable()
\r
78 Console.WriteLine("Result Set " +
\r
81 // number of columns in the table
\r
82 Console.WriteLine(" Total Columns: " +
\r
85 // display the schema
\r
86 foreach (DataRow schemaRow
\r
88 foreach (DataColumn schemaCol
\r
91 schemaCol.ColumnName +
\r
93 schemaRow[schemaCol]);
\r
94 Console.WriteLine();
\r
101 // Read and display the rows
\r
102 Console.WriteLine("Gonna do a
\r
104 while(rdr.Read()) {
\r
105 Console.WriteLine(" Row " +
\r
109 c < rdr.FieldCount;
\r
111 // column meta data
\r
112 DataRow dr = dt.Rows[c];
\r
119 if(rdr.IsDBNull(c) == true)
\r
120 dataValue = " is NULL";
\r
126 // display column meta
\r
128 output = metadataValue +
\r
130 Console.WriteLine(output);
\r
138 } while(rdr.NextResult());
\r
139 Console.WriteLine("Total Result sets: " +
\r
147 <p>We are able to get
148 String data (char, character, text, varchar), Int16 (smallint),
149 Int32 (integer), Int64 (bigint), DateTime (time, date, timestamp),
150 Boolean (boolean), Single (float), and Double (double).
151 More data types will come later. Note, the types that do work still
152 need thorough testing.
154 <p>Rows that are returned which contain columns that are NULL are handled now.
155 The PgSqlDataReader method IsDBNull() needs to be called to determine
156 if a field IS NULL before trying to read data from that field.
158 <p>Calling PostgreSQL stored procedures works. It does not work perfectly.
160 even work to specification - yet. If you want to test it yourself, look at
161 TestSqlDataReader.cs or PostgresTest.cs in
162 mcs/class/System.Data/Test.
164 <p>Below, I have some sample code you can
165 use to call a PostgreSQL stored procedure named "version". This stored
166 procedure returns a string containing the PostgreSQL server version. Notice
167 the CommandType is StoredProcedure and the method ExecuteScalar() is called.
169 <p>ExecuteScalar() is a lightweight method in class PgSqlCommand that only returns
170 one row and one column as one object - even if there is more than row or column.
173 static string GetDatabaseServerVersion (PgSqlConnection cnc)
175 PgSqlCommand cmd = cnc.CreateCommand ();
178 cmd.CommandType = CommandType.StoredProcedure;
179 cmd.CommandText = "version";
181 data = (string) cmd.ExecuteScalar ();
187 * <p>We have the beginnings of Parameters support PostgreSQL. Only
188 Input Parameters are currently supported. Output, Input/Output,
189 and Return parameters still need to be done.
191 <p>A lot of functionality in System.Data is missing, but the
192 infrastructure is starting to come together.
194 <p>A lot of Exceptions need to be thrown for various exceptions. However,
195 PgSqlException, PgSqlErrorCollection, and PgSqlError have been partially
198 <p>Tim Coleman and Rodrigo Moya got the beginnings of the
199 PgSqlDataAdapter/DataSet/DataTable/DataRow to work. Currently,
200 the PgSqlDataAdapter can Fill() relational data into a DataTable in a DataSet.
201 See the test mcs/class/System.Data/Test/TestSqlDataAdapter.cs to see it in action.
202 Below, I show a snippets from the test:
205 string connectionString;
207 PgSqlDataAdapter adapter;
208 DataSet dataSet = null;
215 sqlQuery = "select * from pg_tables";
217 adapter = new PgSqlDataAdapter (sqlQuery,
220 dataSet = new DataSet ();
222 adapter.Fill (dataSet);
224 if (dataSet != null) {
225 foreach (DataRow row in dataSet.Tables["Table"].Rows)
226 Console.WriteLine("tablename: " + row["tablename"]);
230 * Testing the PostgreSQL Provider
233 * <p>In order to test Mono.Data.PostgreSqlClient, you will need to have
234 access to a remote PostgreSQL DBMS, or you will have to install
235 one locally. PostgreSQL was first ADO.NET provider created in Mono.
237 <p>Why use PostgreSQL? Because it is free software, has a client
238 library that is easy to use, PostgreSQL is easy to install on
239 Unix and Windows (using the Cygwin install program), not difficult to setup after
240 installation, and it runs under: Linux,
241 Windows (via cygwin and ipc-daemon), Unix, and
242 others. This allowed us to create the
243 System.Data functionality in Mono much quicker.
245 <p>If you plan on using a remote PostgreSQL DBMS Server,
246 than you will need to have the PostgreSQL client software on your
247 local computer that includes libpq.so (pq.dll on Windows).
249 <p>The System.Data tests use this connection string to connect
250 to the PostgreSQL database named "test" at host "localhost" as
254 "host=localhost;dbname=test;user=postgres"
258 <p>Installation instructions for PostgreSQL DBMS:
263 * Read the PostgreSQL Installation Instructions
264 at \usr\doc\postgresql-x.x.x\html\installation.html
266 * Depending on your Unix system,
267 PostgreSQL maybe already installed, a database user 'postgres' created,
268 a linux user 'postgres' created and initdb ran. Or maybe not.
273 mkdir /usr/local/pgsql/data
\r
274 chown postgres /usr/local/pgsql/data
\r
276 initdb -D /usr/local/pgsql/data
\r
277 postmaster -i -D /usr/local/pgsql/data
\r
282 * Make sure you have a database user named postgres. It is best to install
283 the PostgreSQL DBMS under linux user postgres. When you run the postmaster,
284 run it under the user postgres as well. If this was not done, then you
285 will need to create a user named postgres for the System.Data tests.
287 * If you already installed PostgeSQL and you do not have a database
288 user named postgres, then you can create user postgres using psql:
292 create user postgres with password 'fun2db';
295 * The postmaster must be run with -i option.
297 * In the /usr/local/pgsql/data/pg_hba.conf file, you need
298 to have the AUTH_TYPE set to md5. You can read more on this at
299 /usr/doc/postgresql-7.2.1/html/client-authentication.html
301 PostgreSQL html docs are located. See the 2nd line below,
302 host 127.0.0.1 has an AUTH_TYPE md5 in pg_hba.conf.
305 # TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE
308 host all 127.0.0.1 255.255.255.255 md5
311 * If you can not find your PostgreSQL documentation locally or you
312 did not install it, then you
313 can get it <a href="http://www.postgresql.org/idocs/">here</a>.
320 * Use the <a href="http://www.cygwin.com/">Cygwin</a> installer to
321 install the PostgreSQL DBMS. It is
322 found in the database category.
324 * <p>Read the file postgres-x.x.README at /usr/doc/Cygwin and read
325 the requirements to install PostgreSQL. Those requirements
326 are included with cygwin except cygipc. A default installtion
327 of cygwin does not install everything you will need, so on the
328 safe side, just include everything when installing cygwin.
330 * <p>The -x.x in postgres-x.x is the version of your PostgreSQL DBMS.
332 * <p>Once Cygwin has installed the PostgreSQL DBMS on your computer,
333 read the file FAQ_MSWIN which is available
334 in /usr/doc/postgres-x.x
336 * <p>Important notes from this file are:
339 <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.
341 <p>The cygipc package contains the ipc-daemon you will need
342 to run before you can
343 run the PostgreSQL DBMS Server daemon (postmaster) or run
344 initdb which initializes the PostgreSQL database.
346 <p><b>3.</b> The Cygwin bin directory has to be placed in
347 the path before the Windows program directories,
348 for example, C:\cygwin\bin
350 <p><b>My own note.</b> In the Windows control panel, I set
351 the environment variables PATH to my cygwin /usr/local/bin,
352 /usr/bin, and /bin. I also set my LD_LIBRARY_PATH to
353 /usr/local/lib and /usr/lib. For example:
357 PATH=c:\cygwin\usr\local\bin;c:\cygwin\usr\bin;c:\cygwin\bin;
358 LD_LIBRARY_PATH=c:\cygwin\usr\local\lib;c:\cygwin\usr\lib;
361 <p><b>4.</b> Start the ipc-daemon that came with the cygipc
363 are two ways to do this: run it from the command line as:
369 <p>or you can set it up as a Windows service. See the
370 file cygrunsrv.README at /usr/doc/Cygwin on how to do this
371 for ipc-daemon and postmaster. Note the
372 troubleshooting section at the end of
373 the cygrunsrv.README file.
375 <p>To install ipc-daemon as a service,
380 ipc-daemon --install-as-service' (--remove-as-service)
390 <p>Read the installation.html file
391 at /usr/doc/postgresql-x.x/html/installation.html
393 <p>You will see in this file that you will need to
394 run the following commands:
398 mkdir /usr/local/pgsql/data
\r
399 initdb -D /usr/local/pgsql/data
\r
400 postmaster -D /usr/local/pgsql/data
\r
405 <p>When you need to connect to the database,
406 you will need ipc-daemon and postmaster running. Start ipc-daemon
407 before any of the command above. If you restart your computer, you
408 need to start ipc-daemon and postmaster either manually or as a
411 <p>psql is a command-line PostgreSQL client tool to
412 enter and run SQL commands and queries.
414 <p>If there is no database user named postgres, create a user named
415 postgres with the following SQL command in the client tool psql:
420 create user postgres with password 'fun2db';
422 <p>The only reason I say this is so you can easily use the System.Data tests
423 without having to change the database, userid, etc.
426 <p>In the path mcs/class/System.Data/Test
427 there is a PostgreSQL test program named
428 PostgreTest.cs. Thanks goes to Gonzalo for creating the original
431 <p>To use it to test System.Data, you
432 modify the file to your PostgreSQL database
433 connection requirements:
437 <li><b>dbname</b> database, ie., test</li>
438 <li><b>host</b> hostname of the PostgreSQL DBMS Server, ie., localhost</li>
439 <li><b>user</b> username, ie., someuser</li>
440 <li><b>password</b> password, ie., mypass1234</li>
443 <p>The connection string is in OLE-DB connection string format. Internally,
444 SqlConnection converts this to the PostgreSQL connection string format.
448 OLE-DB: "host=localhost;dbname=test;user=joe;password=smoe"
449 PostgreSQL: "host=localhost dbname=test user=joe password=smoe"
453 Note that OLE-DB includes the semicolons while PostgreSQL's connection
457 To compile the PostgresTest.cs program, do:
461 mcs PostgresTest.cs \
463 -r Mono.Data.PostgreSqlClient.dll
466 <p>If there are compile errors, such as, can not convert IDbConnection
467 to PgSqlConnection, then you need to run mcs like:
470 mono f:/cygwin/home/DanielMorgan/mono/install/bin/mcs.exe \
\r
473 -r Mono.Data.PostgreSqlClient.dll
477 To run using mint, do:
481 mint PostgresTest.exe
485 To run using mono, do:
487 mono PostgresTest.exe
490 <p>Below, I show how the output from PostgresTest. I have omitted a lot
491 of the meta data for the columns except two columns. The classes
492 used were from Mono.Data.PostgreSqlClient and were used to connect to a
493 PostgreSQL database and retrieve data.
498 danmorg@DANPC ~/mono/mcs/class/System.Data/Test
\r
499 $ mcs PostgresTest.cs -r System.Data.dll
\r
501 danmorg@DANPC ~/mono/mcs/class/System.Data/Test
\r
502 $ mono PostgresTest.exe
\r
503 Postgres provider specific tests...
\r
506 Error (don't worry about this one)SqlError:PGRES_FATAL_ERROR ERROR:
\r
507 table "mono_postgres_test" does not exist
\r
510 Create table with all supported types:
\r
512 Insert values for all known types:
\r
516 Insert values for all known types:
\r
518 Aggregate: count(*)
\r
520 Aggregate: min(text_value)
\r
521 Agg Result: This is a text
\r
522 Aggregate: max(int4_value)
\r
523 Agg Result: 1048000
\r
524 Aggregate: sum(int4_value)
\r
525 Agg Result: 1048003
\r
526 Select values from the database:
\r
527 Result is from a SELECT SQL Query. Records Affected: -1
\r
530 ColumnName = boolean_value
\r
533 NumericPrecision = 0
\r
538 BaseColumnName = boolean_value
\r
541 DataType = System.Boolean
\r
542 AllowDBNull = False
\r
545 IsExpression = False
\r
547 IsAutoIncrement = False
\r
548 IsRowVersion = False
\r
555 ColumnName = null_timestamp_value
\r
558 NumericPrecision = 0
\r
563 BaseColumnName = null_timestamp_value
\r
566 DataType = System.DateTime
\r
567 AllowDBNull = False
\r
568 ProviderType = 1184
\r
570 IsExpression = False
\r
572 IsAutoIncrement = False
\r
573 IsRowVersion = False
\r
578 Gonna do a Read() now...
\r
580 Col 0: boolean_value: False
\r
581 Col 1: int2_value: 5
\r
582 Col 2: int4_value: 3
\r
583 Col 3: bigint_value: 9
\r
584 Col 4: float_value: 3.141590
\r
585 Col 5: double_value: 3.14159
\r
586 Col 6: numeric_value: 123456789012.345
\r
587 Col 7: char_value: Mono.Data!
\r
588 Col 8: varchar_value: It was not me!
\r
589 Col 9: text_value: We got data!
\r
590 Col 10: point_value: (1,0)
\r
591 Col 11: time_value: 01/01/1 21:13:14
\r
592 Col 12: date_value: 02/29/2000 00:00:00
\r
593 Col 13: timestamp_value: 02/29/2004 14:00:11
\r
594 Col 14: null_boolean_value is NULL
\r
595 Col 15: null_int2_value is NULL
\r
596 Col 16: null_int4_value is NULL
\r
597 Col 17: null_bigint_value is NULL
\r
598 Col 18: null_float_value is NULL
\r
599 Col 19: null_double_value is NULL
\r
600 Col 20: null_numeric_value is NULL
\r
601 Col 21: null_char_value is NULL
\r
602 Col 22: null_varchar_value is NULL
\r
603 Col 23: null_text_value is NULL
\r
604 Col 24: null_point_value is NULL
\r
605 Col 25: null_time_value is NULL
\r
606 Col 26: null_date_value is NULL
\r
607 Col 27: null_timestamp_value is NULL
\r
609 Col 0: boolean_value: True
\r
610 Col 1: int2_value: -22
\r
611 Col 2: int4_value: 1048000
\r
612 Col 3: bigint_value: 123456789012345
\r
613 Col 4: float_value: 3.141590
\r
614 Col 5: double_value: 3.14159
\r
615 Col 6: numeric_value: 123456789012.345
\r
616 Col 7: char_value: This is a char
\r
617 Col 8: varchar_value: This is a varchar
\r
618 Col 9: text_value: This is a text
\r
619 Col 10: point_value: (1,0)
\r
620 Col 11: time_value: 01/01/1 21:13:14
\r
621 Col 12: date_value: 02/29/2000 00:00:00
\r
622 Col 13: timestamp_value: 02/29/2004 14:00:11
\r
623 Col 14: null_boolean_value is NULL
\r
624 Col 15: null_int2_value is NULL
\r
625 Col 16: null_int4_value is NULL
\r
626 Col 17: null_bigint_value is NULL
\r
627 Col 18: null_float_value is NULL
\r
628 Col 19: null_double_value is NULL
\r
629 Col 20: null_numeric_value is NULL
\r
630 Col 21: null_char_value is NULL
\r
631 Col 22: null_varchar_value is NULL
\r
632 Col 23: null_text_value is NULL
\r
633 Col 24: null_point_value is NULL
\r
634 Col 25: null_time_value is NULL
\r
635 Col 26: null_date_value is NULL
\r
636 Col 27: null_timestamp_value is NULL
\r
637 Total Rows Retrieved: 2
\r
638 Total Result sets: 1
\r
639 Call ExecuteReader with a SQL Command.
\r
640 (Not INSERT,UPDATE,DELETE
\r
642 Result is from a SQL Command not (INSERT,UPDATE,DELETE).
\r
643 Records Affected: -1
\r
644 Total Result sets: 0
\r
645 Call ExecuteReader with a SQL Command.
\r
646 (Is INSERT,UPDATE,DELETE)
\r
648 Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: 1
\r
649 Total Result sets: 0
\r
650 Calling stored procedure version()
\r
651 Result: PostgreSQL 7.2.1 on i686-pc-cygwin, compiled by GCC 2.95.3-5
\r
652 Database Server Version: PostgreSQL 7.2.1 on i686-pc-cygwin,
\r
653 compiled by GCC 2.9
\r