3 When it comes to Mono and PostgreSQL, there are many ways
4 you can handle your data. You have many Mono Data Providers which can be used
5 to access data from a application written for Mono.
6 Then there is the future goal of having the ability to host Mono within PostgreSQL to
7 have the applications run on the server which makes things much faster.
9 * Hosting Mono in PostgreSQL
11 There is a project to <a href="http://gborg.postgresql.org/project/plmono/projdisplay.php">host Mono within PostgreSQL</a>.
13 plMono is a PostgreSQL language using the embedded Mono runtime. It provides support for writing functions in C#, or any other language that supports .NET.
17 There are many ADO.NET data providers for <a href="http://www.postgresql.org/">PostgreSQL</a>:
19 There are two providers created specifically for PostgreSQL included with Mono:
23 <li><a href="http://gborg.postgresql.org/project/npgsql/projdisplay.php">Npgsql</a>
25 <li>included with Mono</li>
27 <li>a .NET Managed Data Provider for PostgreSQL</li>
29 <li>Written in 100% C#</li>
31 <li>does not require a client library</li>
33 <li>works on Mono and Microsoft .NET</li>
35 <li>created by Francisco Figueiredo jr. and has many developers working on it
37 <li>works in the SQL# (command-line and GTK# GUI versions)</li>
39 <li>in namespace Npgsql and assembly Npgsql and is found in mcs
40 at mcs/class/Npgsql</li>
44 <li>Mono.Data.PostgreSQL (deprecated)
46 <li>included with Mono</li>
48 <li>Deprecated in favor of Npgsql</li>
50 <li>Exists in namespace Mono.Data.PostgreSql and assembly Mono.Data.PostgreSql</li>
52 <li>Is a Mono Data Provider for the <a href="http://www.postgresql.org/">PostgreSQL</a>
53 client/server database management system.</li>
55 <li>Written in C# and has C# bindings to the PostgreSQL C Client library pq.dll on Windows
56 and libpq.so on Linux.</li>
60 <li><a href="http://sourceforge.net/projects/mysqlnet/">ByteFX.Data</a> has a provider for PostgreSQL too, but I do not know how well it works with Mono.</li>
62 <li>There is another .NET data provider for PostgreSQL named <a href="http://sourceforge.net/projects/pgsqlclient/">PgSqlClient</a>, but I do not know if it works with Mono.</li>
64 <li>If none of the above providers meet your needs. There is the ODBC and OLEDB providers included with Mono.</li>
66 <li>Bugs with Mono or the data provider should be reported
67 in Mono's Bugzilla <a href="http://bugzilla.ximian.com/">here</a>. If you
68 do not have Bugzilla user account, it is free
70 create one <a href="http://bugzilla.ximian.com/createaccount.cgi">here</a>.</li>
75 Below, see separate Testing sections for Npgsql and Mono.Data.PostgreSqlClient.
82 <li>Builds and Runs on both Microsoft .NET and Mono.</li>
83 <li>Works using SQL# (command-line and GTK# versions)</li>
84 <li>You can send insert, update, delete queries
\r
85 through NpgsqlCommand.ExecuteNonQuery() method.</li>
\r
86 <li>You can send queries like, select count(*) from table, select version()
\r
87 with NpgsqlCommand.ExecuteScalar() method.</li>
\r
88 <li>There is logging support. (Thanks Dave Page)
\r
89 To use it, place code like that in your program:</li>
\r
93 NpgsqlEventLog.Level = LogLevel.Debug; // LogLevel.
\r
94 NpgsqlEventLog.LogName = "NpgsqlTests.LogFile"; // LogFile.
\r
97 <li>You can use Npgsql with Mono (Thanks Kristis Makris). It is not working perfectly.</li>
\r
98 <li>There is a winforms test suite (Thanks Dave Page).</li>
\r
99 <li>Clearer code in NpgsqlConnection removing *magic* numbers and constants. (Thanks Kristis Makris)</li>
\r
100 <li>Better support of ODBC-like ConnectionString in NpgsqlConnection (Thanks Dave Page)</li>
\r
101 <li>Thanks Ulrich Sprick for all discussion and ideas.</li>
\r
105 <li>Mono.Data.PostgreSQL status
108 <li>Deprecated in favor of Npgsql</li>
110 <li>We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and
111 DELETE SQL commands using the ExecuteNonQuery method in PgSqlCommand.</li>
113 <li>We can execute multiple queries and do a NextResult() in PgSqlDataReader()
114 to get the next result set.</li>
116 <li>We are also able to do simple aggregate functions,
117 ie, count(), sum(), min(), and max()
118 in a simple SELECT SQL query using the ExecuteScalar() now.</li>
120 <li>We are also able to retrieve data with a simple SELECT SQL query
121 using ExecuteReader() which returns a PgSqlDataReader. We are able to
122 use GetSchemaTable() to get the meta data about the table columns.
123 We are able to Read() to get each row from the result set.</li>
125 <li>We are able to get
126 String data (char, character, text, varchar), Int16 (smallint),
127 Int32 (integer), Int64 (bigint), DateTime (time, date, timestamp),
128 Boolean (boolean), Single (float), and Double (double).
129 More data types will come later. Note, the types that do work still
130 need thorough testing.</li>
132 <li>Rows that are returned which contain columns that are NULL are handled now.
133 The PgSqlDataReader method IsDBNull() needs to be called to determine
134 if a field IS NULL before trying to read data from that field.</li>
136 <li>Calling PostgreSQL stored procedures works. It does not work perfectly.
138 even work to specification - yet. If you want to test it yourself, look at
139 TestSqlDataReader.cs or PostgresTest.cs in
140 mcs/class/System.Data/Test.</li>
142 <li>Below, I have some sample code you can
143 use to call a PostgreSQL stored procedure named "version". This stored
144 procedure returns a string containing the PostgreSQL server version. Notice
145 the CommandType is StoredProcedure and the method ExecuteScalar() is called.</li>
147 <li>ExecuteScalar() is a lightweight method in class PgSqlCommand that only returns
148 one row and one column as one object - even if there is more than row or column.</li>
150 <li>We have the beginnings of Parameters support PostgreSQL. Only
151 Input Parameters are currently supported. Output, Input/Output,
152 and Return parameters still need to be done.</li>
154 <li>A lot of Exceptions need to be thrown for various exceptions. However,
155 PgSqlException, PgSqlErrorCollection, and PgSqlError have been partially
158 <li>Tim Coleman and Rodrigo Moya got the beginnings of the
159 PgSqlDataAdapter/DataSet/DataTable/DataRow to work. Currently,
160 the PgSqlDataAdapter can Fill() relational data into a DataTable in a DataSet.
161 See the test mcs/class/System.Data/Test/TestSqlDataAdapter.cs to see it in action.</li>
163 <li>Works in the SQL# command-line version
164 and the GTK# version on Linux. It only works in SQL# command-line version
174 <li>More testing and fixing bugs</li>
176 <li>Better error handling</li>
178 <li>More Data Types to use</li>
180 <li>Any features for Npgsql should be implemented in Npgsql's main cvs repository at
181 gborg.postgresql.org. Most bugs should be fixed in gborg.postgresql.org's cvs.
182 Only bugs neccessary for building and running of Npgsql on Mono can be done in Mono cvs,
183 but once applied they should be sent to Npgsql's mailing list
184 at gborg.postgresql.org for inclusion into cvs there. Whenever there is
185 a release of Npgsql (determined by Francisco Figueiredo jr. or a release
186 of Mono (determined by Miguel de Icaza), then the Npgsql source
187 in gborg.postgresql.org's cvs will be used to update the Npgsql source in
191 <li>Mono.Data.PostgreSqlClient even though deprecated can still
192 accept bug fixes. This is because other areas, such as, ASP.NET examples
193 may still use this provider.</li>
195 <li>Add any missing functionality to Npgsql. If this funtionality works on
196 .NET but not on Mono, implement the missing features or fix the bugs in Mono</li>
198 <li>Npgsql has replaced Mono.Data.PostgreSqlClient as the provider of
199 choice to use. However, Mono.Data.PostgreSqlClient will remain in a
200 deprecated state until nobody uses it anymore - then it can be removed</li>
202 <li>Implement more of PostgreSQL 7.3 features in Npgsql</li>
205 ** Testing Mono.Data.PostgreSqlClient
208 * <p>In order to test Mono.Data.PostgreSqlClient, you will need to have
209 access to a remote PostgreSQL DBMS, or you will have to install
210 one locally. PostgreSQL was the first ADO.NET provider created in Mono.
212 <p>Why use PostgreSQL? Because it is free software, has a client
213 library that is easy to use, PostgreSQL is easy to install on
214 Unix and Windows (using the Cygwin install program), not difficult to setup after
215 installation, and it runs under: Linux,
216 Windows (via cygwin and ipc-daemon), Unix, and
217 others. This allowed us to create the
218 System.Data functionality in Mono much quicker.
220 <p>If you plan on using a remote PostgreSQL DBMS Server,
221 than you will need to have the PostgreSQL client software on your
222 local computer that includes libpq.so (pq.dll on Windows).
224 <p>The System.Data tests use this connection string to connect
225 to the PostgreSQL database named "test" at host "localhost" as
229 "Server=localhost;Database=test;User ID=postgres;Password=fun2db"
231 "host=localhost;dbname=test;user=postgres;password=fun2db"
235 <p>Installation instructions for PostgreSQL DBMS:
240 * Read the PostgreSQL Installation Instructions
241 at \usr\doc\postgresql-x.x.x\html\installation.html
243 * Depending on your Unix system,
244 PostgreSQL maybe already installed, a database user 'postgres' created,
245 a linux user 'postgres' created and initdb ran. Or maybe not.
250 mkdir /usr/local/pgsql/data
251 chown postgres /usr/local/pgsql/data
253 initdb -D /usr/local/pgsql/data
254 postmaster -i -D /usr/local/pgsql/data
259 * Make sure you have a database user named postgres. It is best to install
260 the PostgreSQL DBMS under linux user postgres. When you run the postmaster,
261 run it under the user postgres as well. If this was not done, then you
262 will need to create a user named postgres for the System.Data tests.
264 * If you already installed PostgeSQL and you do not have a database
265 user named postgres, then you can create user postgres using psql:
269 create user postgres with password 'fun2db';
272 * The postmaster must be run with -i option.
274 * In the /usr/local/pgsql/data/pg_hba.conf file, you need
275 to have the AUTH_TYPE set to md5. You can read more on this at
276 /usr/doc/postgresql-7.2.1/html/client-authentication.html
278 PostgreSQL html docs are located. See the 2nd line below,
279 host 127.0.0.1 has an AUTH_TYPE md5 in pg_hba.conf.
282 # TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE
285 host all 127.0.0.1 255.255.255.255 md5
288 * If you can not find your PostgreSQL documentation locally or you
289 did not install it, then you
290 can get it <a href="http://www.postgresql.org/idocs/">here</a>.
297 * Use the <a href="http://www.cygwin.com/">Cygwin</a> installer to
298 install the PostgreSQL DBMS. It is
299 found in the database category.
301 * <p>Read the file postgres-x.x.README at /usr/doc/Cygwin and read
302 the requirements to install PostgreSQL. Those requirements
303 are included with cygwin except cygipc. A default installtion
304 of cygwin does not install everything you will need, so on the
305 safe side, just include everything when installing cygwin.
307 * <p>The -x.x in postgres-x.x is the version of your PostgreSQL DBMS.
309 * <p>Once Cygwin has installed the PostgreSQL DBMS on your computer,
310 read the file FAQ_MSWIN which is available
311 in /usr/doc/postgres-x.x
313 * <p>Important notes from this file are:
316 <p><b>2.</b> - Install the latest <a href="http://www.neuro.gatech.edu/users/cwilson/cygutils/cygipc/index.html">CygIPC</a> package.
317 Cygwin includes a utility bunzip2 which can be used to unzip it. Now, change to
318 the root directory by
320 you can use "tar xvf cygipc.xxx.tar" to untar it
321 in the root directory in cygwin.
323 <p>The cygipc package contains the support to run ipc-daemon
325 to run before you can
326 run the PostgreSQL DBMS Server daemon (postmaster) or run
327 initdb which initializes the PostgreSQL database.
329 <p><b>3.</b> The Cygwin bin directory has to be placed in
330 the path before the Windows program directories,
331 for example, C:\cygwin\bin
333 <p><b>My own note.</b> In the Windows control panel, I set
334 the environment variables PATH to my cygwin /usr/local/bin,
335 /usr/bin, and /bin. I also set my LD_LIBRARY_PATH to
336 /usr/local/lib and /usr/lib. For example:
340 PATH=c:\cygwin\usr\local\bin;c:\cygwin\usr\bin;c:\cygwin\bin;
341 LD_LIBRARY_PATH=c:\cygwin\usr\local\lib;c:\cygwin\usr\lib;
344 <p><b>4.</b> Start the ipc-daemon that came with the cygipc
346 are two ways to do this: run it from the command line as:
352 <p>or you can set it up as a Windows service. See the
353 file cygrunsrv.README at /usr/doc/Cygwin on how to do this
354 for ipc-daemon and postmaster. Note the
355 troubleshooting section at the end of
356 the cygrunsrv.README file.
358 <p>To install ipc-daemon as a service,
363 ipc-daemon --install-as-service' (--remove-as-service)
373 <p>Read the installation.html file
374 at /usr/doc/postgresql-x.x/html/installation.html
376 <p>You will see in this file that you will need to
377 run the following commands:
381 mkdir /usr/local/pgsql/data
382 initdb -D /usr/local/pgsql/data
383 postmaster -D /usr/local/pgsql/data
388 <p>When you need to connect to the database,
389 you will need ipc-daemon and postmaster running. Start ipc-daemon
390 before any of the command above. If you restart your computer, you
391 need to start ipc-daemon and postmaster either manually or as a
394 <p>psql is a command-line PostgreSQL client tool to
395 enter and run SQL commands and queries.
397 <p>If there is no database user named postgres, create a user named
398 postgres with the following SQL command in the client tool psql:
403 create user postgres with password 'fun2db';
405 <p>The only reason I say this is so you can easily use the System.Data tests
406 without having to change the database, userid, etc.
409 <p>In the path mcs/class/System.Data/Test
410 there is a test for Mono.Data.PostgreSqlClient named
411 PostgreTest.cs. Thanks goes to Gonzalo for creating the original
415 To compile the PostgresTest.cs program, do:
419 mcs PostgresTest.cs \
421 -r Mono.Data.PostgreSqlClient.dll
424 <p>If there are compile errors, such as, can not convert IDbConnection
425 to PgSqlConnection, then you need to run mcs like:
428 mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \
431 -r Mono.Data.PostgreSqlClient.dll
435 To run using mint, do:
439 mint PostgresTest.exe
443 To run using mono, do:
445 mono PostgresTest.exe
448 <p>C# Example for Mono.Data.PostgreSqlClient:
452 using Mono.Data.PostgreSqlClient;
456 public static void Main(string[] args)
458 string connectionString =
459 "Server=localhost;" +
461 "User ID=postgres;" +
464 dbcon = new PgConnection(connectionString);
466 IDbCommand dbcmd = dbcon.CreateCommand();
467 // requires a table to be created named employee
468 // with columns firstname and lastname
470 // CREATE TABLE employee (
471 // firstname varchar(32),
472 // lastname varchar(32));
474 "SELECT firstname, lastname" +
476 dbcmd.CommandText = sql;
477 IDataReader reader = dbcmd.ExecuteReader();
478 while(reader.Read()) {
479 string FirstName = (string) reader["firstname"];
480 string LastName = (string) reader["lastname"];
481 Console.WriteLine("Name: " +
482 FirstName + " " + LastName);
495 <li>Building C# Example:
497 <li>Save the example to a file, such as, TestExample.cs</li>
500 mcs TestExample.cs -r System.Data.dll \
501 -r Mono.Data.PostgreSqlClient.dll
504 <li>Build on Windows via Cygwin:
506 mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \
508 -lib:C:/cygwin/home/MyHome/mono/install/lib \
509 -r System.Data.dll -r Mono.Data.PostgreSqlClient.dll
514 <li>Running the Example:
524 <li>Have a working mono and mcs</li>
526 <li>Get <a href="http://gborg.postgresql.org/project/npgsql/projdisplay.php">Npgsql</a>
527 and make sure the binary assembly Npgsql.dll is installed in the same place that the
528 mono class libraries are located.
530 <li>Read the Testing notes for Mono.Data.PostgreSqlClient too
532 <li>C# Example for Npgsql:
540 public static void Main(string[] args)
542 string connectionString =
543 "Server=localhost;" +
545 "User ID=postgres;" +
549 dbcon = new NpgsqlConnection(connectionString);
550 IDbCommand dbcmd = dbcon.CreateCommand();
551 // requires a table to be created named employee
552 // with columns firstname and lastname
554 // CREATE TABLE employee (
555 // firstname varchar(32),
556 // lastname varchar(32));
558 "SELECT firstname, lastname " +
560 dbcmd.CommandText = sql;
561 IDataReader reader = dbcmd.ExecuteReader();
562 while(reader.Read()) {
563 string FirstName = (string) reader["firstname"];
564 string LastName = (string) reader["lastname"];
565 Console.WriteLine("Name: " +
566 FirstName + " " + LastName);
579 <li>Building C# Example:
581 <li>Save the example to a file, such as, TestExample.cs</li>
584 mcs TestExample.cs -r System.Data.dll \
588 <li>Build on Windows via Cygwin:
590 mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \
592 -lib:C:/cygwin/home/MyHome/mono/install/lib \
593 -r System.Data.dll -r Npgsql.dll
598 <li>Running the Example: