PsqlODBC problem with complex query

Hi,

I am running PostgreSQL-8.0.3 (8.0.1 before that) on
an FC3/AMD64 system and presently developing a cross-
platform program using ODBC, GTK2 and use RLIB
(http://rlib.sicompos.com) for reporting.

The development is done under Linux and
I compile the program for Windows using MingW.

Here's the query in two versions, quote from
my ODBC code, both cause problems I describe later:

#if 1
ret =
SQLExecDirect(hStmt,
  "select jk.id,jk.datum,megrend.nev,well.nev,"
  "(select nev from csoport where id=jk.csoport) as csopnev,"
  "jk.muszak from jk,megrend,well where jk.megr=megrend.id and "
  "jk.fpont=well.id;", SQL_NTS);
#else
ret =
SQLExecDirect(hStmt,
  "select jk.id,jk.datum,megrend.nev,well.nev,csoport.nev,"
  "jk.muszak from jk left outer join csoport on (csoport.id=jk.csoport)
  "megrend,well where jk.megr=megrend.id and "
  "jk.fpont=well.id;", SQL_NTS);
#endif

Both query correctly produce the same 10 rows in psql and in
my application using unixODBC's built-in PostgreSQL/Postgre7.1,
a.k.a. ODBC drivers /usr/lib/libodbcpsql.so.1 and .so.2.

The problem in question hit me first when I compiled my
program under Windows, and tried to access PostgreSQL on
my Linux box. The problem is, the queries fetch only 1 row,
not 10. I reproduced the problem using the "official"
postgresql-odbc package, both with the original (I mean updated
FC3 errata) postgresql-odbc-7.3-8.FC3.1 and the newly compiled
postgresql-odbc-08.00.0100-1 that I downloaded from the RawHide
repository. I tried to debug what results I get from the fetch,
using the below code:

  ret = SQL_SUCCESS;
  while (test_sqlreturn(ret)) {
    ret = SQLFetch(hStmt);
printf("refresh_jk() fetch 0x%02x\n", ret);
    if (test_sqlreturn(ret)) {
      printf("JK id=%d\n", id);
      /* ... */
    }
  }

With the unixODBC drivers, I get:

$ ./myapp
refresh_jk() exec 0x00
refresh_jk() fetch 0x00
JK id=1
refresh_jk() fetch 0x00
JK id=2
refresh_jk() fetch 0x00
JK id=11
refresh_jk() fetch 0x00
JK id=12
refresh_jk() fetch 0x00
JK id=9
refresh_jk() fetch 0x00
JK id=10
refresh_jk() fetch 0x00
JK id=7
refresh_jk() fetch 0x00
JK id=6
refresh_jk() fetch 0x00
JK id=4
refresh_jk() fetch 0x00
JK id=3
refresh_jk() fetch 0x64

With the "official" ODBC drivers I got:

$ ./myapp
refresh_jk() exec 0x00
refresh_jk() fetch 0x00
JK id=1
refresh_jk() fetch 0xffffffff

Also, I use these two queries to produce a PDF report in RLIB:

select well.nev as fpontnev,megrend.nev as megrnev,
kocsi.frsz as frsz,csoport.nev as csopnev,
dolgozo.nev as csopvez,jelleg1,jelleg2,datum,dijszab,
n1,n2,p1,p2,talpcdt,bcsosaru,bcsoatm,nivo,tmax,dvmax,megj
from jk left outer join csoport on (jk.csoport=csoport.id)
left outer join dolgozo on (jk.csopvez=dolgozo.id)
left outer join kocsi on (jk.berend=kocsi.id),
well,megrend
where jk.id=X and jk.fpont=well.id and jk.megr=megrend.id;

select
case when jkitem.szolg is not null then szolgalt.nev else potdij.nev end 
as nev,
case when jkitem.szolg is not null then mszam1.mszam else mszam2.mszam 
end as mszam,
case when jkitem.szolg is not null then szolgalt.egys else 'Ft' end as 
egys,jkitem.min as min,jkitem.max as max,jkitem.muv as menny,
jkitem.osszeg as osszeg
from jkitem left outer join (szolgalt join mszam as mszam1 on 
(szolgalt.mszam=mszam1.id)) on (jkitem.szolg=szolgalt.id)
left outer join (potdij join mszam as mszam2 on 
(potdij.mszam=mszam2.id)) on (jkitem.potdij=potdij.id)
where jkitem.jk=X order by jkitem.id;

Where X is an "id" serial field value of my "jk" table.

The report is correct with both unixODBC PostgreSQL drivers but
contains garbage when used with the official psqlodbc drivers.

The unixODBC drivers (old and newer) contain these version strings:

#define POSTGRESDRIVERVERSION  "06.40.0007"
#define POSTGRESDRIVERVERSION  "07.01.0003"

I tried these Windows psqlodbc.dll versions, which all
produce both bugs described above:
08.00.0101, 08.00.0100, 07.02.0005 and 07.03.0200

Psqlodbc version 07.01.0010 under Windows can handle my report
queries but fetches only one row for the first query above.

BUT! Finally I found psqlodbc 06.50.0000, and that behaves as
I expected.

How could it be possible that the latest versiona of the ODBC
driver (08.xxxx) that theoretically follow the internals of
PostgreSQL 8.0.x, doesn't work well with the new server, but
ancient versions of the ODBC driver do?

Best regards,
Zoltán Böszörményi

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
     message can get through to the mailing list cleanly