Zoltan Boszormenyi írta:
> 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
The second row of the second query has a comma and a " at the end,
they were outside of my 80 character wide terminal. Bad cut and paste.
> 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.
Actually, they are under /usr/lib64 as my program is compiled 64 bit.
> 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 "garbage" needs to be clarified. RLIB gets every field as strings
using SQLDescribeCol(). It cannot fetch the result row(s) and the
strings aren't memset()ed in advance.
> 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@postgresql.org so that your
> message can get through to the mailing list cleanly
>