Re: [ODBC] Odd behaviour - *possible* ODBC bug?

-----Original Message-----
From: Jonathan Stanford

> Guys,

> I have some odd behaviour with VB6 & postgresql that may be a bug - I
would appreciate someone else > > replicating this; or any other suggestions
anyone might have.

[snip]

> PostgreSQL code:

> CREATE TABLE tb_search (
> session_id int,
> emp_id int,
> rank int
> );

> and some data:

> insert into tb_search (session_id , emp_id, rank) values (1,101, 5);

[snip several insert commands]

> VB Code:

[snip]

> sSQL = "SELECT emp_id, sum(rank) "
> sSQL = sSQL & "FROM tb_search "
> sSQL = sSQL & "ON e.emp_id = s.emp_id "
> sSQL = sSQL & "WHERE session_id = " & lSesh
> sSQL = sSQL & " GROUP BY emp_id "
> sSQL = sSQL & " ORDER BY sum(rank) DESC"

> frmEmpSearch.Caption = sOrigCapt & " - retrieving results"
> Set rsEmps = New ADODB.Recordset
> rsEmps.CursorLocation = adUseClient 'adUseServer
> rsEmps.Open sSQL, DBConn, adOpenForwardOnly, adLockReadOnly

I don't think it's an ODBC driver's bug.
The cause is that PostgreSQL returns NUMERIC type as sum(int).

adUseClient for CursorLocation property indicates ADO to use
Microsoft Cursor Service for OLE DB. Microsoft Cursor service
seems to think that sum(rank) is of type int but PostgreSQL
returns NUMERIC type. I don't know what should be done here.
Please change sum(rank) -> sum(rank)::int and try.

regards,
Hiroshi Inoue