PostgreSQL DbProvider

[harald]

Here is my PostgreSQL DbProvider implementation: http://springutils.fluffnstuff.org/browser/trunk/SpringUtils/Data/Support/Npgsql/NpgsqlProvider.cs

Maybe someone can add this to the core Spring.NET sources.

Harald
--
http://harald.fluffnstuff.org/

[Mark Pollack]

Hi,

I got an error trying to get to that page. Since it is a .cs file you should note that I've changed how providers are specified - the class per provider approach isn't good for distributing code. A pure config based approach is now used. The docs for it are here (http://www.springframework.net/doc-latest/reference/html/dbprovider.html) - in particular the paragraph just above Section 15.3

The provider definition mechanism is extensible by 'users', no need to modify the spring source but obviously we should support the dozen or so popular DBs 'out of the box'. I'd very much appreciate your help getting and debugging a postgress implementation. Take a look at the dbproviders.xml file for the format - it is a bit verbose but straightforward. Just ping me with any question, I'm happy to help.

Mark

[jvans]

Just put together the DbProvider definition for PostgreSQL. I can confirm that I am able to connect to my local postgres db so I know the connection works. I will be doing some further testing with it tomorrow.

<object id="PostgreSQL" type="Spring.Data.Common.DbProvider, Spring.Data" singleton="false">
<constructor-arg name="dbMetaData">
<object type="Spring.Data.Common.DbMetadata">
<constructor-arg name="productName" value="PostgreSQL, PostgreSQL provider 1.0.0.0" />
<constructor-arg name="assemblyName" value="Npgsql, Version=1.0.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"/>
<constructor-arg name="connectionType" value="Npgsql.NpgsqlConnection, Npgsql, Version=1.0.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"/>
<constructor-arg name="commandType" value="Npgsql.NpgsqlCommand, Npgsql, Version=1.0.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"/>
<constructor-arg name="parameterType" value="Npgsql.NpgsqlParameter, Npgsql, Version=1.0.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"/>
<constructor-arg name="dataAdapterType" value="Npgsql.NpgsqlDataAdapter, Npgsql, Version=1.0.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"/>
<constructor-arg name="commandBuilderType" value="Npgsql.NpgsqlCommandBuilder, Npgsql, Version=1.0.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"/>
<constructor-arg name="commandBuilderDeriveParametersMethod" value="DeriveParameters"/>
<constructor-arg name="parameterDbType" value="NpgsqlTypes.NpgsqlDbType, Npgsql, Version=1.0.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"/>
<constructor-arg name="parameterDbTypeProperty" value="NpgsqlDbType"/>
<constructor-arg name="parameterIsNullableProperty" value="IsNullable"/>
<constructor-arg name="parameterNamePrefix" value="?"/>
<constructor-arg name="exceptionType" value="Npgsql.NpgsqlException, Npgsql, Version=1.0.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"/>
<constructor-arg name="useParameterNamePrefixInParameterCollection" value="true"/>
<constructor-arg name="bindByName" value="true"/>
<!-- this is only true for .net 1.1 kept it here just in case we want to revert back to this strategy for
obtaining error codes-->
<constructor-arg name="errorCodeExceptionExpression" value="Number.ToString()"/>
<property name="ErrorCodes.badSqlGrammarCodes">
<value>42000,42601,42602,42622,42804</value>
</property>
<property name="ErrorCodes.DataAccessResourceFailureCodes">
<value>53000,53100,53200,53300</value>
</property>
<property name="ErrorCodes.DataIntegrityViolationCodes">
<value>23000,23502,23503,23505,23514</value>
</property>
<property name="ErrorCodes.CannotAcquireLockCodes">
<value>55P03</value>
</property>
<property name="ErrorCodes.DeadlockLoserCodes">
<value>40P01</value>
</property>
</object>
</constructor-arg>
</object>

[Mark Pollack]

Hi,
Seems like we have 2 threads going here.

http://forum.springframework.net/showthread.php?t=739

Take a look at my last comment there...

thanks for the error codes, i know getting them isn't much fun... Your list of error codes seems much more complete than the listing in spring.java

- http://fisheye1.cenqua.com/browse/~raw,r=1.22/springframework/spring/src/org/springframework/jdbc/support/sql-error-codes.xml

How did you get them? We should update the spring.java error codes as well.

Mark

[jvans]

Hi,

Okay, someone beat me to the punch. Well anyhow, I have now run the provider through some relatively straight forward tests Load/Save/Update/Delete/Finds using nHibernate and so far everything seems to be working okay.

I got the error codes right from the PostgreSQL docs. Took a stab at what looked reasonable.

http://www.postgresql.org/docs/8.1/static/errcodes-appendix.html

If there is anything I can help you guys with let me know. We have been using Spring for Java at work for close to two years now and it has been a great experience. We are now having to widen our skill base into the .NET world so it was nice to find that there is an effort going on here as well.

John

[Mark Pollack]

Hi John,

Glad to hear the template is working well for you. Error codes are a great contribution - thanks! I'll be adding them to the java version as well. BTW, there is NH1.2beta support in cvs if you want to experiment with that.

Mark

[jvans]

Hi Mark,

I am working with the support for nHibernate 1.2. So far it seems to be working fine as well. I am trying to keep current with the changes being made daily to both the main codebase as well as the Integration stuff

John

[await]

I'm trying to 'port' the Northwind example using Npgsql-1.0 and postgres .

I'm getting the following error when defining my dbProvider. Anyone have any insight to what may be causing this?

Exception text :
Error thrown by a dependency of object 'Npgsql-1.0' defined in 'assembly [Spring.Data, Version=1.1.0.2, Culture=neutral, PublicKeyToken=null], resource [Spring.Data.Common.dbproviders.xml]' : Unsatisfied dependency expressed through constructor argument with index 2 of type [System.Type] : Could not convert constructor argument value [Npgsql.NpgsqlConnection, Npgsql, Version=1.0.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7] to required type [System.Type] : Cannot convert property value of type [System.String] to required type [System.Type] for property ''.
while resolving 'constructor argument with name dbmetadata' to '(inner object)' defined in 'assembly [Spring.Data, Version=1.1.0.2, Culture=neutral, PublicKeyToken=null], resource [Spring.Data.Common.dbproviders.xml]'
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Spring config :

<!-- Database and NHibernate Configuration -->
<db:dbProvider id="DbProvider"
provider="Npgsql-1.0"
connectionString="Data Source=${db.datasource};Database=${db.database};Us er ID=${db.user};Password=${db.password};Trusted_Conn ection=False"/>


<object id="SessionFactory" type="Spring.Data.NHibernate.LocalSessionFactoryObject, Spring.Data.NHibernate">
<property name="DbProvider" ref="DbProvider"/>
<property name="MappingAssemblies">
<list>
<value>Spring.Northwind.Dao.NHibernate</value>
</list>
</property>
<property name="HibernateProperties">
<dictionary>
<entry key="hibernate.connection.provider"
value="NHibernate.Connection.DriverConnectionProvider"/>

<entry key="hibernate.dialect"
value="NHibernate.Dialect.PostgreSQLDialect"/>

<entry key="hibernate.connection.driver_class"
value="NHibernate.Driver.NpgsqlDriver"/>
</dictionary>
</property>

</object>

Thanks in advance.

[Mark Pollack]

Hi,

I don't know if it is related, but it looks like the connection string is for sql server and not postgres. I'll try a hello-world example myself to verify in anycase and post back. Just FYI, the config was taken line-for-line from a working postgres application, so it has passed at least one 'smoke-test' ;)

Cheers,
Mark