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












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 = 
" & lSeshsSQL = sSQL & " GROUP BY emp_id " sSQL = sSQL & " 
ORDER BY sum(rank) DESC"
 
frmEmpSearch.Caption = sOrigCapt & " - retrieving results"Set 
rsEmps = New ADODB.RecordsetrsEmps.CursorLocation = adUseClient 
'adUseServerrsEmps.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
Mon, 18 Sep 2000 01:36:09 +0100
"Jonathan Stanford" <jstanford@iee.org>
Odd behaviour - *possible* ODBC bug?
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


Tue, 19 Sep 2000 02:36:32 +0100
"Jonathan Stanford" <jstanford@iee.org>
Re: 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



Tue, 18 Sep 2001 12:12:43 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
Re: [ODBC] Odd behaviour - *possible* ODBC bug?