1 * PostgreSQL Data Provider
4 <li>Exists in namespace Mono.Data.PostgreSql and assembly Mono.Data.PostgreSql
6 <li>Is a Mono Data Provider for the <a href="http://www.postgresql.org/">PostgreSQL</a>
7 client/server database management system.
9 <li>Written in C# and has C# bindings to the PostgreSQL C Client library pq.dll on Windows
10 and libpq.so on Linux.
16 <li>We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and
17 DELETE SQL commands using the ExecuteNonQuery method in PgSqlCommand.
19 <li>We can execute multiple queries and do a NextResult() in PgSqlDataReader()
20 to get the next result set.
22 <li>We are also able to do simple aggregate functions,
23 ie, count(), sum(), min(), and max()
24 in a simple SELECT SQL query using the ExecuteScalar() now.
26 <li>We are also able to retrieve data with a simple SELECT SQL query
27 using ExecuteReader() which returns a PgSqlDataReader. We are able to
28 use GetSchemaTable() to get the meta data about the table columns.
29 We are able to Read() to get each row from the result set.
31 <li>We are able to get
32 String data (char, character, text, varchar), Int16 (smallint),
33 Int32 (integer), Int64 (bigint), DateTime (time, date, timestamp),
34 Boolean (boolean), Single (float), and Double (double).
35 More data types will come later. Note, the types that do work still
36 need thorough testing.
38 <li>Rows that are returned which contain columns that are NULL are handled now.
39 The PgSqlDataReader method IsDBNull() needs to be called to determine
40 if a field IS NULL before trying to read data from that field.
42 <li>Calling PostgreSQL stored procedures works. It does not work perfectly.
44 even work to specification - yet. If you want to test it yourself, look at
45 TestSqlDataReader.cs or PostgresTest.cs in
46 mcs/class/System.Data/Test.
48 <li>Below, I have some sample code you can
49 use to call a PostgreSQL stored procedure named "version". This stored
50 procedure returns a string containing the PostgreSQL server version. Notice
51 the CommandType is StoredProcedure and the method ExecuteScalar() is called.
53 <li>ExecuteScalar() is a lightweight method in class PgSqlCommand that only returns
54 one row and one column as one object - even if there is more than row or column.
56 <li>We have the beginnings of Parameters support PostgreSQL. Only
57 Input Parameters are currently supported. Output, Input/Output,
58 and Return parameters still need to be done.
60 <li>A lot of functionality in System.Data is missing, but the
61 infrastructure is starting to come together.
63 <li>A lot of Exceptions need to be thrown for various exceptions. However,
64 PgSqlException, PgSqlErrorCollection, and PgSqlError have been partially
67 <li>Tim Coleman and Rodrigo Moya got the beginnings of the
68 PgSqlDataAdapter/DataSet/DataTable/DataRow to work. Currently,
69 the PgSqlDataAdapter can Fill() relational data into a DataTable in a DataSet.
70 See the test mcs/class/System.Data/Test/TestSqlDataAdapter.cs to see it in action.
73 * Testing the PostgreSQL Provider
76 * <p>In order to test Mono.Data.PostgreSqlClient, you will need to have
77 access to a remote PostgreSQL DBMS, or you will have to install
78 one locally. PostgreSQL was first ADO.NET provider created in Mono.
80 <p>Why use PostgreSQL? Because it is free software, has a client
81 library that is easy to use, PostgreSQL is easy to install on
82 Unix and Windows (using the Cygwin install program), not difficult to setup after
83 installation, and it runs under: Linux,
84 Windows (via cygwin and ipc-daemon), Unix, and
85 others. This allowed us to create the
86 System.Data functionality in Mono much quicker.
88 <p>If you plan on using a remote PostgreSQL DBMS Server,
89 than you will need to have the PostgreSQL client software on your
90 local computer that includes libpq.so (pq.dll on Windows).
92 <p>The System.Data tests use this connection string to connect
93 to the PostgreSQL database named "test" at host "localhost" as
97 "host=localhost;dbname=test;user=postgres"
101 <p>Installation instructions for PostgreSQL DBMS:
106 * Read the PostgreSQL Installation Instructions
107 at \usr\doc\postgresql-x.x.x\html\installation.html
109 * Depending on your Unix system,
110 PostgreSQL maybe already installed, a database user 'postgres' created,
111 a linux user 'postgres' created and initdb ran. Or maybe not.
116 mkdir /usr/local/pgsql/data
117 chown postgres /usr/local/pgsql/data
119 initdb -D /usr/local/pgsql/data
120 postmaster -i -D /usr/local/pgsql/data
125 * Make sure you have a database user named postgres. It is best to install
126 the PostgreSQL DBMS under linux user postgres. When you run the postmaster,
127 run it under the user postgres as well. If this was not done, then you
128 will need to create a user named postgres for the System.Data tests.
130 * If you already installed PostgeSQL and you do not have a database
131 user named postgres, then you can create user postgres using psql:
135 create user postgres with password 'fun2db';
138 * The postmaster must be run with -i option.
140 * In the /usr/local/pgsql/data/pg_hba.conf file, you need
141 to have the AUTH_TYPE set to md5. You can read more on this at
142 /usr/doc/postgresql-7.2.1/html/client-authentication.html
144 PostgreSQL html docs are located. See the 2nd line below,
145 host 127.0.0.1 has an AUTH_TYPE md5 in pg_hba.conf.
148 # TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE
151 host all 127.0.0.1 255.255.255.255 md5
154 * If you can not find your PostgreSQL documentation locally or you
155 did not install it, then you
156 can get it <a href="http://www.postgresql.org/idocs/">here</a>.
163 * Use the <a href="http://www.cygwin.com/">Cygwin</a> installer to
164 install the PostgreSQL DBMS. It is
165 found in the database category.
167 * <p>Read the file postgres-x.x.README at /usr/doc/Cygwin and read
168 the requirements to install PostgreSQL. Those requirements
169 are included with cygwin except cygipc. A default installtion
170 of cygwin does not install everything you will need, so on the
171 safe side, just include everything when installing cygwin.
173 * <p>The -x.x in postgres-x.x is the version of your PostgreSQL DBMS.
175 * <p>Once Cygwin has installed the PostgreSQL DBMS on your computer,
176 read the file FAQ_MSWIN which is available
177 in /usr/doc/postgres-x.x
179 * <p>Important notes from this file are:
182 <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.
184 <p>The cygipc package contains the ipc-daemon you will need
185 to run before you can
186 run the PostgreSQL DBMS Server daemon (postmaster) or run
187 initdb which initializes the PostgreSQL database.
189 <p><b>3.</b> The Cygwin bin directory has to be placed in
190 the path before the Windows program directories,
191 for example, C:\cygwin\bin
193 <p><b>My own note.</b> In the Windows control panel, I set
194 the environment variables PATH to my cygwin /usr/local/bin,
195 /usr/bin, and /bin. I also set my LD_LIBRARY_PATH to
196 /usr/local/lib and /usr/lib. For example:
200 PATH=c:\cygwin\usr\local\bin;c:\cygwin\usr\bin;c:\cygwin\bin;
201 LD_LIBRARY_PATH=c:\cygwin\usr\local\lib;c:\cygwin\usr\lib;
204 <p><b>4.</b> Start the ipc-daemon that came with the cygipc
206 are two ways to do this: run it from the command line as:
212 <p>or you can set it up as a Windows service. See the
213 file cygrunsrv.README at /usr/doc/Cygwin on how to do this
214 for ipc-daemon and postmaster. Note the
215 troubleshooting section at the end of
216 the cygrunsrv.README file.
218 <p>To install ipc-daemon as a service,
223 ipc-daemon --install-as-service' (--remove-as-service)
233 <p>Read the installation.html file
234 at /usr/doc/postgresql-x.x/html/installation.html
236 <p>You will see in this file that you will need to
237 run the following commands:
241 mkdir /usr/local/pgsql/data
242 initdb -D /usr/local/pgsql/data
243 postmaster -D /usr/local/pgsql/data
248 <p>When you need to connect to the database,
249 you will need ipc-daemon and postmaster running. Start ipc-daemon
250 before any of the command above. If you restart your computer, you
251 need to start ipc-daemon and postmaster either manually or as a
254 <p>psql is a command-line PostgreSQL client tool to
255 enter and run SQL commands and queries.
257 <p>If there is no database user named postgres, create a user named
258 postgres with the following SQL command in the client tool psql:
263 create user postgres with password 'fun2db';
265 <p>The only reason I say this is so you can easily use the System.Data tests
266 without having to change the database, userid, etc.
269 <p>In the path mcs/class/System.Data/Test
270 there is a PostgreSQL test program named
271 PostgreTest.cs. Thanks goes to Gonzalo for creating the original
274 <p>To use it to test System.Data, you
275 modify the file to your PostgreSQL database
276 connection requirements:
280 <li><b>dbname</b> database, ie., test</li>
281 <li><b>host</b> hostname of the PostgreSQL DBMS Server, ie., localhost</li>
282 <li><b>user</b> username, ie., someuser</li>
283 <li><b>password</b> password, ie., mypass1234</li>
286 <p>The connection string is in OLE-DB connection string format. Internally,
287 SqlConnection converts this to the PostgreSQL connection string format.
291 OLE-DB: "host=localhost;dbname=test;user=joe;password=smoe"
292 PostgreSQL: "host=localhost dbname=test user=joe password=smoe"
296 Note that OLE-DB includes the semicolons while PostgreSQL's connection
300 To compile the PostgresTest.cs program, do:
304 mcs PostgresTest.cs \
306 -r Mono.Data.PostgreSqlClient.dll
309 <p>If there are compile errors, such as, can not convert IDbConnection
310 to PgSqlConnection, then you need to run mcs like:
313 mono f:/cygwin/home/DanielMorgan/mono/install/bin/mcs.exe \
316 -r Mono.Data.PostgreSqlClient.dll
320 To run using mint, do:
324 mint PostgresTest.exe
328 To run using mono, do:
330 mono PostgresTest.exe
333 <p>Below, I show how the output from PostgresTest. I have omitted a lot
334 of the meta data for the columns except two columns. The classes
335 used were from Mono.Data.PostgreSqlClient and were used to connect to a
336 PostgreSQL database and retrieve data.
341 danmorg@DANPC ~/mono/mcs/class/System.Data/Test
342 $ mcs PostgresTest.cs -r System.Data.dll
344 danmorg@DANPC ~/mono/mcs/class/System.Data/Test
345 $ mono PostgresTest.exe
346 Postgres provider specific tests...
349 Error (don't worry about this one)SqlError:PGRES_FATAL_ERROR ERROR:
350 table "mono_postgres_test" does not exist
353 Create table with all supported types:
355 Insert values for all known types:
359 Insert values for all known types:
363 Aggregate: min(text_value)
364 Agg Result: This is a text
365 Aggregate: max(int4_value)
367 Aggregate: sum(int4_value)
369 Select values from the database:
370 Result is from a SELECT SQL Query. Records Affected: -1
373 ColumnName = boolean_value
381 BaseColumnName = boolean_value
384 DataType = System.Boolean
390 IsAutoIncrement = False
398 ColumnName = null_timestamp_value
406 BaseColumnName = null_timestamp_value
409 DataType = System.DateTime
415 IsAutoIncrement = False
421 Gonna do a Read() now...
423 Col 0: boolean_value: False
426 Col 3: bigint_value: 9
427 Col 4: float_value: 3.141590
428 Col 5: double_value: 3.14159
429 Col 6: numeric_value: 123456789012.345
430 Col 7: char_value: Mono.Data!
431 Col 8: varchar_value: It was not me!
432 Col 9: text_value: We got data!
433 Col 10: point_value: (1,0)
434 Col 11: time_value: 01/01/1 21:13:14
435 Col 12: date_value: 02/29/2000 00:00:00
436 Col 13: timestamp_value: 02/29/2004 14:00:11
437 Col 14: null_boolean_value is NULL
438 Col 15: null_int2_value is NULL
439 Col 16: null_int4_value is NULL
440 Col 17: null_bigint_value is NULL
441 Col 18: null_float_value is NULL
442 Col 19: null_double_value is NULL
443 Col 20: null_numeric_value is NULL
444 Col 21: null_char_value is NULL
445 Col 22: null_varchar_value is NULL
446 Col 23: null_text_value is NULL
447 Col 24: null_point_value is NULL
448 Col 25: null_time_value is NULL
449 Col 26: null_date_value is NULL
450 Col 27: null_timestamp_value is NULL
452 Col 0: boolean_value: True
453 Col 1: int2_value: -22
454 Col 2: int4_value: 1048000
455 Col 3: bigint_value: 123456789012345
456 Col 4: float_value: 3.141590
457 Col 5: double_value: 3.14159
458 Col 6: numeric_value: 123456789012.345
459 Col 7: char_value: This is a char
460 Col 8: varchar_value: This is a varchar
461 Col 9: text_value: This is a text
462 Col 10: point_value: (1,0)
463 Col 11: time_value: 01/01/1 21:13:14
464 Col 12: date_value: 02/29/2000 00:00:00
465 Col 13: timestamp_value: 02/29/2004 14:00:11
466 Col 14: null_boolean_value is NULL
467 Col 15: null_int2_value is NULL
468 Col 16: null_int4_value is NULL
469 Col 17: null_bigint_value is NULL
470 Col 18: null_float_value is NULL
471 Col 19: null_double_value is NULL
472 Col 20: null_numeric_value is NULL
473 Col 21: null_char_value is NULL
474 Col 22: null_varchar_value is NULL
475 Col 23: null_text_value is NULL
476 Col 24: null_point_value is NULL
477 Col 25: null_time_value is NULL
478 Col 26: null_date_value is NULL
479 Col 27: null_timestamp_value is NULL
480 Total Rows Retrieved: 2
482 Call ExecuteReader with a SQL Command.
483 (Not INSERT,UPDATE,DELETE
485 Result is from a SQL Command not (INSERT,UPDATE,DELETE).
488 Call ExecuteReader with a SQL Command.
489 (Is INSERT,UPDATE,DELETE)
491 Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: 1
493 Calling stored procedure version()
494 Result: PostgreSQL 7.2.1 on i686-pc-cygwin, compiled by GCC 2.95.3-5
495 Database Server Version: PostgreSQL 7.2.1 on i686-pc-cygwin,
507 <p>Eventually replace the PostgreSQL data provider in Mono
508 with <a href="http://gborg.postgresql.org/project/npgsql/projdisplay.php">Npgsql</a>.
509 Npgsql is a .Net Data Provider for PostgreSQL which implements
510 the <a href="http://developer.postgresql.org/docs/postgres/protocol.html">PostgreSQL Frontend/Backend Protocol</a>.
511 Npgsql is implemented in 100% C#. This provider was created by Francisco Figueiredo jr.
512 and has many programmers developing the provider.