Tuesday, May 19, 2009

ASP.Net SQL Provider has a build server

Hi all!

Daniel Nauck, the developer of ASP.Net SQL Provider for Postgresql, let me know today that he has setup a build server for his project. This means that users will be able to get the latest build easily.

In order to download the latest build, point your browser to: http://build.nauck-it.de.

Nice job, Daniel!!

Tuesday, May 12, 2009

Npgsql Tips: Using " in (...)" queries with parameters list and "any" operator

Hi, all!

We have received some users questions about how to send a list of values to be used in queries using the "in" operator. Something like:

select foo, bar from table where foo in (blah1, blah2, blah3);

Npgsql supports array-like parameter values and the first idea to have this working would try to use it directly:

NpgsqlCommand command = new NpgsqlCommand("select * from tablee where field_serial in (:parameterlist)", conn);

ArrayList l = new ArrayList();
l.Add(5);
l.Add(6);

command.Parameters.Add(new NpgsqlParameter("parameterlist", NpgsqlDbType.Array | NpgsqlDbType.Integer));

command.Parameters[0].Value = l.ToArray();


NpgsqlDataReader dr = command.ExecuteReader();


but unfortunately this won't work as expected. Npgsql will send a query like this:

select * from tablee where field_serial in ((array[5,6])::int4[])

And Postgresql will complain with the following message:

ERROR: operator does not exist: integer = integer[]

This is because it is trying to compare the integer value of the column with an array of integers.

But, thanks to Josh Cooley and Jerónimo Milea who posted a solution on our forum, you just have to change your "in" operator to use the "any" operator and it will work as expected:

NpgsqlCommand command = new NpgsqlCommand("select * from tablee where field_serial = any (:parameterlist)", conn);

So if you want to use a query to compare a list of values, remember to use the any operator.

Thursday, May 07, 2009

Npgsql 2.0.5 released!

Hi, all!

Npgsql Development Team released Npgsql 2.0.5.

This is a bug fix release. And we fixed some important bugs.

The first one is about the famous NotSupportedException seek operation. Npgsql now handles this by flushing the stream before writing to it. It is not the complete solution but it is an step in the right direction.

Another bug which is open for quite some time is the return value of DataAdapter.Update. It wasn't working very well for multiple statements. Now it is working ok.

Josh Cooley added a lot of fixes for Entity Framework and Transactions support.

Please, check our release notes to see all the changes on this release.

You can download this release on our usual location.

Thanks everybody who helped us with comments, feedback and patches.