Using COUNT DISTINCT

Using PGNP with Microsoft .NET

Using COUNT DISTINCT

Postby paulsk » Wed Apr 15, 2009 7:19 am

[PGNP32-1.2.8.1110 with .NET 2.0]

I am using the following SQL:

SELECT COUNT(DISTINCT GroupID) AS count FROM MyTable

but i am getting a System.NullReferenceException being thrown when the SQL is executed. I can't see what could be wrong with the SQL, especially since this same SQL is used successfully for Oracle and SqlServer.
paulsk
 
Posts: 8
Joined: Wed Apr 15, 2009 4:42 am

Re: Using COUNT DISTINCT

Postby Moderator » Wed Apr 15, 2009 9:30 am

We were unable to reproduce the bug. We used the following code:
Code: Select all
dbcmd = new OleDbCommand("select count(distinct id) from testtbl", dbcon);
Console.WriteLine("Count: {0}", dbcmd.ExecuteScalar());


The returned value is Int64. Could you send the table definition and the fragment of code that executes the query?
User avatar
Moderator
Site Admin
 
Posts: 293
Joined: Wed Oct 29, 2008 11:27 pm

Re: Using COUNT DISTINCT

Postby paulsk » Thu Apr 16, 2009 12:56 am

My code snippet is this.

Code: Select all
    System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection("db-connection-string")
    System.Data.OleDb.OleDbCommand stmt = con.CreateCommand();
    stmt.CommandText = "SELECT COUNT(DISTINCT GroupID) AS count FROM MyTable";
    System.Data.OleDb.OleDbDataReader rs = stmt.ExecuteReader();


I have also found the source of my problem :D, it is the
Code: Select all
AS count
part of the SQL. When this is removed I can use either ExecuteReader() or ExecuteScalar() okay.

It is worth noting that my SQL snippet is used okay for Oracle and Sqlserver with .NET, and also with Oracle, Sqlserver and Postgres with Java. Any thoughts as to why it will not work with your driver?
paulsk
 
Posts: 8
Joined: Wed Apr 15, 2009 4:42 am

Re: Using COUNT DISTINCT

Postby Moderator » Thu Apr 16, 2009 11:52 pm

There was a bug in <select_element> production rule definition. We have extended the rule to allow COUNT and some other words in the column alias.

We'll publish the fix in the next release of the provider. Please PM or e-mail us if you need the fix immediately.

Thank you for the good catch!
User avatar
Moderator
Site Admin
 
Posts: 293
Joined: Wed Oct 29, 2008 11:27 pm

Re: Using COUNT DISTINCT

Postby paulsk » Fri Apr 17, 2009 12:24 am

I have worked around this issue so an immediate fix is not necessary. When will 1.3 be released?
paulsk
 
Posts: 8
Joined: Wed Apr 15, 2009 4:42 am

Re: Using COUNT DISTINCT

Postby Moderator » Mon Apr 20, 2009 12:13 am

We are trying to determine the approximate release date. Most likely it will be middle of Summer 2009.
User avatar
Moderator
Site Admin
 
Posts: 293
Joined: Wed Oct 29, 2008 11:27 pm


Return to .NET

Who is online

Users browsing this forum: No registered users and 1 guest