Odd behaviour - *possible* ODBC bug? - Mailing list pgsql-odbc

From Jonathan Stanford
Subject Odd behaviour - *possible* ODBC bug?
Date
Msg-id 00ca01c02108$808022b0$8119fea9@w2kpro
Whole thread Raw
Responses Re: Odd behaviour - *possible* ODBC bug?
List pgsql-odbc
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
 
 

 

pgsql-odbc by date:

Previous
From: "Mister ics"
Date:
Subject: Re: secure ODBC connection
Next
From: "Hiroshi Inoue"
Date:
Subject: Re: Odd behaviour - *possible* ODBC bug?