Postgres Pro
Downloads
Home   >   mailing lists

Thread: Odd behaviour - *possible* ODBC bug?

Odd behaviour - *possible* ODBC bug?

From
"Jonathan Stanford"
Date:
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.
 
Versions: VB6 sp5 on W2K pro sp2 running postgresql 7.1.2 via cygwin.  Insight ODBC driver 7.01.00.06 with ODBC 3.520.6526.0 (obtained from control panel/admin tools/ODBC).  7.01.00.05 behaves identically.
 
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);
insert into tb_search (session_id , emp_id, rank) values (1,101, 5);
insert into tb_search (session_id , emp_id, rank) values (1,101, 10);
insert into tb_search (session_id , emp_id, rank) values (1,101, 10);
insert into tb_search (session_id , emp_id, rank) values (1,101, 5);
insert into tb_search (session_id , emp_id, rank) values (1,102, 5);
insert into tb_search (session_id , emp_id, rank) values (1,102, 10);
insert into tb_search (session_id , emp_id, rank) values (1,102, 5);
insert into tb_search (session_id , emp_id, rank) values (1,103, 10);
insert into tb_search (session_id , emp_id, rank) values (1,103, 5);
insert into tb_search (session_id , emp_id, rank) values (1,104, 5);
insert into tb_search (session_id , emp_id, rank) values (1,104, 5);
insert into tb_search (session_id , emp_id, rank) values (1,105, 5);
insert into tb_search (session_id , emp_id, rank) values (1,106, 5);
insert into tb_search (session_id , emp_id, rank) values (1,107, 5);
insert into tb_search (session_id , emp_id, rank) values (1,108, 5);
 
VB Code:
 
dim lSesh as long
dim rsEmps as ADODB.Recordset
'set up your DBConn here or use implicit connection
 
lSesh = 1
 
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
 
if rsEmps.BOF and rsEmps.EOF then
    msgbox "No records returnes"             'adUseClient returns no records
else
    msgbox "We got records!"                   'adUseSever returns records
end if

The select statement returns records when run from psql, yet the location of cursor affects whether or not rows are returned when the select is run from within VB.  The fact that location of cursor determines success makes me think there *could* be an issue with the ODBC driver.
 
Out of interest, replacing
sSQL = "SELECT  emp_id, sum(rank) "
with
sSQL = "SELECT  emp_id, max(rank) "
causes the query to work wherever the cursor is!?!?!
 
All help/suggestions appreciated.
 
Jonathan Stanford, UK
 
 

 

Re: Odd behaviour - *possible* ODBC bug?

From
"Hiroshi Inoue"
Date:
-----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

Re: Odd behaviour - *possible* ODBC bug?

From
"Jonathan Stanford"
Date:
Hiroshi

Your ::int works a treat.

Thanks

Jon

----- Original Message -----
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Jonathan Stanford" <jstanford@iee.org>
Cc: "pgsql-hackers" <pgsql-hackers@postgresql.org>;
<pgsql-odbc@postgresql.org>
Sent: Tuesday, September 18, 2001 4:12 AM
Subject: 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org