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
);
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"
'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: