-* PostgreSQL Data Provider
+* PostgreSQL and Mono
-<ul>
- <li>Exists in namespace Mono.Data.PostgreSql and assembly Mono.Data.PostgreSql
-
- <li>Is a Mono Data Provider for the <a href="http://www.postgresql.org/">PostgreSQL</a>
- client/server database management system.
-
- <li>Written in C# and has C# bindings to the PostgreSQL C Client library pq.dll on Windows
- and libpq.so on Linux.
-</ul>
+ When it comes to Mono and PostgreSQL, there are many ways
+ you can access your data.
+
+* Data Providers
-* Current Status
+ There are many ADO.NET data providers for <a href="http://www.postgresql.org/">PostgreSQL</a>:
+
+ There are two providers created specifically for PostgreSQL included with Mono:
<ul>
- <li>We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and
- DELETE SQL commands using the ExecuteNonQuery method in PgSqlCommand.
-
- <li>We can execute multiple queries and do a NextResult() in PgSqlDataReader()
- to get the next result set.
-
- <li>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.
-
- <li>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.
-
- <li>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.
-
- <li>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.
-
- <li>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.
+
+ <li><a href="http://gborg.postgresql.org/project/npgsql/projdisplay.php">Npgsql</a>
+ <ul>
+ <li>included with Mono</li>
+
+ <li>a .NET Managed Data Provider for PostgreSQL</li>
+
+ <li>Written in 100% C#</li>
+
+ <li>does not require a client library</li>
+
+ <li>works on Mono and Microsoft .NET</li>
+
+ <li>created by Francisco Figueiredo jr. and has many developers working on it
+
+ <li>works in the SQL# (command-line and GTK# GUI versions)</li>
+
+ <li>in namespace Npgsql and assembly Npgsql and is found in mcs
+ at mcs/class/Npgsql</li>
+ </ul>
+ </li>
- <li>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.
+ <li>Mono.Data.PostgreSqlClient (DEPRECATED)
+ <ul>
- <li>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.
+ <li>Deprecated in favor of Npgsql. No longer included in a release of Mono.</li>
- <li>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.
+ </ul>
+ </li>
- <li>A lot of functionality in System.Data is missing, but the
- infrastructure is starting to come together.
+ <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>
- <li>A lot of Exceptions need to be thrown for various exceptions. However,
- PgSqlException, PgSqlErrorCollection, and PgSqlError have been partially
- implemented.
+ <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>
- <li>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.
+ <li>If none of the above providers meet your needs. There is the ODBC and OLEDB providers included with Mono.</li>
+
+ <li>Bugs with Mono or the data provider should be reported
+ in Mono's Bugzilla <a href="http://bugzilla.ximian.com/">here</a>. If you
+ do not have Bugzilla user account, it is free
+ and easy to
+ create one <a href="http://bugzilla.ximian.com/createaccount.cgi">here</a>.</li>
+
+
</ul>
-* Testing the PostgreSQL Provider
+** Current Status
+
+<ul>
+ <li>Npgsql
+ <ul>
+ <li>Builds and Runs on both Microsoft .NET and Mono.</li>
+ <li>Works using SQL# (command-line and GTK# versions)</li>
+ <li>You can send insert, update, delete queries \r
+ through NpgsqlCommand.ExecuteNonQuery() method.</li>\r
+ <li>You can send queries like, select count(*) from table, select version()\r
+ with NpgsqlCommand.ExecuteScalar() method.</li>\r
+ <li>There is logging support. (Thanks Dave Page)\r
+ To use it, place code like that in your program:</li>\r
+\r
+<pre> \r
+ // Enable logging.\r
+ NpgsqlEventLog.Level = LogLevel.Debug; // LogLevel.\r
+ NpgsqlEventLog.LogName = "NpgsqlTests.LogFile"; // LogFile.\r
+</pre>\r
+ \r
+ <li>You can use Npgsql with Mono (Thanks Kristis Makris). It is not working perfectly.</li>\r
+ <li>There is a winforms test suite (Thanks Dave Page).</li>\r
+ <li>Clearer code in NpgsqlConnection removing *magic* numbers and constants. (Thanks Kristis Makris)</li>\r
+ <li>Better support of ODBC-like ConnectionString in NpgsqlConnection (Thanks Dave Page)</li>\r
+ <li>Thanks Ulrich Sprick for all discussion and ideas.</li>\r
+
+ </ul>
+ </li>
+</ul>
+** Action Plan
<ul>
- * <p>In order to test Mono.Data.PostgreSqlClient, you will need to have
- access to a remote PostgreSQL DBMS, or you will have to install
- one locally. PostgreSQL was first ADO.NET provider created in Mono.
+ <li>More testing and fixing bugs</li>
+
+ <li>Better error handling</li>
- <p>Why use PostgreSQL? Because it is free software, has a client
- library that is easy to use, PostgreSQL is easy to install on
- Unix and Windows (using the Cygwin install program), not difficult to setup after
- installation, and it runs under: Linux,
- Windows (via cygwin and ipc-daemon), Unix, and
- others. This allowed us to create the
- System.Data functionality in Mono much quicker.
+ <li>More Data Types to use</li>
- <p>If you plan on using a remote PostgreSQL DBMS Server,
- than you will need to have the PostgreSQL client software on your
- local computer that includes libpq.so (pq.dll on Windows).
+ <li>Any features for Npgsql should be implemented in Npgsql's main cvs repository at
+ gborg.postgresql.org. Most bugs should be fixed in gborg.postgresql.org's cvs.
+ Only bugs neccessary for building and running of Npgsql on Mono can be done in Mono cvs,
+ but once applied they should be sent to Npgsql's mailing list
+ at gborg.postgresql.org for inclusion into cvs there. Whenever there is
+ a release of Npgsql (determined by Francisco Figueiredo jr. or a release
+ of Mono (determined by Miguel de Icaza), then the Npgsql source
+ in gborg.postgresql.org's cvs will be used to update the Npgsql source in
+ Mono's cvs.
+ </li>
- <p>The System.Data tests use this connection string to connect
- to the PostgreSQL database named "test" at host "localhost" as
- user "postgres".
+ <li>Add any missing functionality to Npgsql. If this funtionality works on
+ .NET but not on Mono, implement the missing features or fix the bugs in Mono</li>
+ <li>Npgsql has been replaced Mono.Data.PostgreSqlClient as the provider of
+ choice to use. Mono.Data.PostgreSqlClient is deprecated and is no longer included in
+ Mono releases. Please use Npgsql for PostgreSQL data access.</li>
+
+ <li>Implement new features of PostgreSQL.</li>
+ </ul>
+
+</ul>
+
+** Testing Npgsql
+
+<ul>
+ <li>Have a working mono and mcs</li>
+
+ <li>Get <a href="http://gborg.postgresql.org/project/npgsql/projdisplay.php">Npgsql</a>
+ and make sure the binary assembly Npgsql.dll is installed in the same place that the
+ mono class libraries are located.
+
+ <li>C# Example for Npgsql:
<pre>
-"host=localhost;dbname=test;user=postgres"
+ using System;
+ using System.Data;
+ using Npgsql;
+
+ public class Test
+ {
+ public static void Main(string[] args)
+ {
+ string connectionString =
+ "Server=localhost;" +
+ "Database=test;" +
+ "User ID=postgres;" +
+ "Password=fun2db;";
+ IDbConnection dbcon;
+ dbcon = new NpgsqlConnection(connectionString);
+ dbcon.Open();
+ IDbCommand dbcmd = dbcon.CreateCommand();
+ // requires a table to be created named employee
+ // with columns firstname and lastname
+ // such as,
+ // CREATE TABLE employee (
+ // firstname varchar(32),
+ // lastname varchar(32));
+ string sql =
+ "SELECT firstname, lastname " +
+ "FROM employee";
+ dbcmd.CommandText = sql;
+ IDataReader reader = dbcmd.ExecuteReader();
+ while(reader.Read()) {
+ string FirstName = (string) reader["firstname"];
+ string LastName = (string) reader["lastname"];
+ Console.WriteLine("Name: " +
+ FirstName + " " + LastName);
+ }
+ // clean up
+ reader.Close();
+ reader = null;
+ dbcmd.Dispose();
+ dbcmd = null;
+ dbcon.Close();
+ dbcon = null;
+ }
+ }
</pre>
+ </li>
+ <li>Building C# Example:
+ <ul>
+ <li>Save the example to a file, such as, TestExample.cs</li>
+<pre>
+ mcs TestExample.cs -r System.Data.dll \
+ -r Npgsql.dll
+</pre>
+ </ul>
+ </li>
+ <li>Running the Example:
+<pre>
+mono TestExample.exe
+</pre>
+</li>
</ul>
-
- <p>Installation instructions for PostgreSQL DBMS:
- <b>On Unix</b>
+
+** Installation instructions for PostgreSQL DBMS:
+ <p><b>On Unix</b>
<ul>
* Read the PostgreSQL Installation Instructions
* <p>Important notes from this file are:
<ul>
- <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.
+ <p><b>2.</b> - Install the latest <a href="http://www.neuro.gatech.edu/users/cwilson/cygutils/cygipc/index.html">CygIPC</a> package.
+ Cygwin includes a utility bunzip2 which can be used to unzip it. Now, change to
+ the root directory by
+ typing "cd /" then
+ you can use "tar xvf cygipc.xxx.tar" to untar it
+ in the root directory in cygwin.
- <p>The cygipc package contains the ipc-daemon you will need
+ <p>The cygipc package contains the support to run ipc-daemon
+ that you will need
to run before you can
run the PostgreSQL DBMS Server daemon (postmaster) or run
initdb which initializes the PostgreSQL database.
without having to change the database, userid, etc.
</ul>
- <p>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.
-
- <p>To use it to test System.Data, you
- modify the file to your PostgreSQL database
- connection requirements:
-
- <p>
- <ul>
- <li><b>dbname</b> database, ie., test</li>
- <li><b>host</b> hostname of the PostgreSQL DBMS Server, ie., localhost</li>
- <li><b>user</b> username, ie., someuser</li>
- <li><b>password</b> password, ie., mypass1234</li>
- </ul>
-
- <p>The connection string is in OLE-DB connection string format. Internally,
- SqlConnection converts this to the PostgreSQL connection string format.
-
- <p>
-<pre>
- OLE-DB: "host=localhost;dbname=test;user=joe;password=smoe"
-PostgreSQL: "host=localhost dbname=test user=joe password=smoe"
-</pre>
-
- <p>
- Note that OLE-DB includes the semicolons while PostgreSQL's connection
- string does not.
-
- <p>
- To compile the PostgresTest.cs program, do:
-
- <p>
-<pre>
- mcs PostgresTest.cs \
- -r System.Data.dll \
- -r Mono.Data.PostgreSqlClient.dll
-</pre>
-
- <p>If there are compile errors, such as, can not convert IDbConnection
- to PgSqlConnection, then you need to run mcs like:
-
-<pre>
- mono f:/cygwin/home/DanielMorgan/mono/install/bin/mcs.exe \
- PostgresTest.cs \
- -r System.Data.dll \
- -r Mono.Data.PostgreSqlClient.dll
-</pre>
-
- <p>
- To run using mint, do:
-
- <p>
-<pre>
-mint PostgresTest.exe
-</pre>
-
- <p>
- To run using mono, do:
-<pre>
-mono PostgresTest.exe
-</pre>
-
- <p>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.
-
-<p>
-<pre>
-
- 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
- <Stack Trace>
-
- 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
-
-</pre>
-
-* Action Plan
- <p>Eventually replace the PostgreSQL data provider in Mono
- with <a href="http://gborg.postgresql.org/project/npgsql/projdisplay.php">Npgsql</a>.
- Npgsql is a .Net Data Provider for PostgreSQL which implements
- the <a href="http://developer.postgresql.org/docs/postgres/protocol.html">PostgreSQL Frontend/Backend Protocol</a>.
- Npgsql is implemented in 100% C#. This provider was created by Francisco Figueiredo jr.
- and has many programmers developing the provider.
projects / mono.git / blobdiff