8.02.00.02 driver incompatibilities

I have found the following incompatibilities in 8.02.00.02 development
snapshot compared with last stable version (8.01.02.00)

1. 8.02.00.02 returns multiple record set (2 tables) for the following
string. First table in result set contains single column drop_table

SELECT drop_table('tempDOK') ;
CREATE TEMP TABLE tempDOK AS SELECT * FROM dok WHERE doktyyp=?ko.liik
AND (?ko.option1=0 or dok.tasumata<>0) AND
(?ko.option2=0 or dok.taidetud) AND true ORDER BY dokumnr LIMIT
100 OFFSET 100;SELECT * FROM tempDOK

How to force new driver to return single record set for this query?

2. C:\Documents and Settings\Administrator\Application
Data\postgresql\pgpass.conf file is not used for default passwords.

How to force driver to use pgpass.conf file for passwords like used by
pgAdmin and other libpq applications ?

3. My application uses hard-coded driver name

DRIVER={PostgreSQL Unicode}

and thus does not work with new driver. How to force new driver to be
invoked by connection string DRIVER={PostgreSQL Unicode} ?


Windows XP SP2
Postgres 8.1
Microsoft Visual FoxPro 9.0


Andrus.


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: 8.02.00.02 driver incompatibilities

Andrus wrote:
> I have found the following incompatibilities in 8.02.00.02 development
> snapshot compared with last stable version (8.01.02.00)
>
> 1. 8.02.00.02 returns multiple record set (2 tables) for the following
> string. First table in result set contains single column drop_table
>
> SELECT drop_table('tempDOK') ;
> CREATE TEMP TABLE tempDOK AS SELECT * FROM dok WHERE
> doktyyp=?ko.liik AND (?ko.option1=0 or dok.tasumata<>0) AND
> (?ko.option2=0 or dok.taidetud) AND true ORDER BY dokumnr LIMIT
> 100 OFFSET 100;SELECT * FROM tempDOK
>
> How to force new driver to return single record set for this query?

What kind of tool are you using ?
Doesn't your tool have any way to skip the first recordset ?

> 2. C:\Documents and Settings\Administrator\Application
> Data\postgresql\pgpass.conf file is not used for default passwords.
>
> How to force driver to use pgpass.conf file for passwords like used by
> pgAdmin and other libpq applications ?

Select an SSL Mode other than *disable* e.g. *allow*.

> 3. My application uses hard-coded driver name
>
> DRIVER={PostgreSQL Unicode}
>
> and thus does not work with new driver. How to force new driver to be
> invoked by connection string DRIVER={PostgreSQL Unicode} ?

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: 8.02.00.02 driver incompatibilities

> >1. 8.02.00.02 returns multiple record set (2 tables) for the following
> >string. First table in result set contains single column drop_table
> >
> >SELECT drop_table('tempDOK') ;
> >CREATE TEMP TABLE tempDOK AS SELECT * FROM dok WHERE
> >doktyyp=?ko.liik AND (?ko.option1=0 or dok.tasumata<>0) AND
> > (?ko.option2=0 or dok.taidetud) AND true ORDER BY dokumnr LIMIT
> >100 OFFSET 100;SELECT * FROM tempDOK
> >
> >How to force new driver to return single record set for this query?
>
> What kind of tool are you using ?
> Doesn't your tool have any way to skip the first recordset ?

This is wrong behaviour of 08.01.0200. Your app have to call
SQLMoreResults to get next result set. I suppose you are still
using FoxPro. I see the google and FoxPro may know SQLMoreResults.

If you need only driver change it's dangerous as another part of
the driver require more result sets. If you still want it we can show
you the way. But it'll be unsupported change.

> >3. My application uses hard-coded driver name
> >
> >DRIVER={PostgreSQL Unicode}
> >
> >and thus does not work with new driver. How to force new driver to be
> >invoked by connection string DRIVER={PostgreSQL Unicode} ?

You have to change Registry (rename):
HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\PostgreSQL
->
HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\PostgreSQL Unicode

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: 8.02.00.02 driver incompatibilities

Ludek,

thank you.

> This is wrong behaviour of 08.01.0200. Your app have to call
> SQLMoreResults to get next result set. I suppose you are still
> using FoxPro. I see the google and FoxPro may know SQLMoreResults.

How to tell to postgres, odbc or change my code so that I can call

drop_table('tempDOK')

without returning recordset. I don't need any result from this.


>> >and thus does not work with new driver. How to force new driver to be
>> >invoked by connection string DRIVER={PostgreSQL Unicode} ?
>
> You have to change Registry (rename):
> HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\PostgreSQL
> ->
> HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\PostgreSQL Unicode

It seems that I must change my application due to the multiple recordset
problem.
So I think that I try to connect first to "PostgreSQL Unicode" driver and if
this fails then to
"PostgreSQL" driver.

How to dedect connection to nonexistent driver ("PostgreSQL Unicode")
It seems that error and sql state code returned in this case are returned
also in some other cases if driver is present.

Andrus.



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: 8.02.00.02 driver incompatibilities

Hiroshi,

thank you.

> What kind of tool are you using ?

I'm using Microsoft Visual FoxPro 9.0 SP1 (see initial message).

> Doesn't your tool have any way to skip the first recordset ?

I can call FoxPro SQLMORERESULTS() function
but this is artifical: I do'nt need result set from drop_table() call to be
returned at all.
Also this meas re-writing, testing and debugging deployed application.

How to call Postgres procedure so that it does not return recordset ?
My drop_table is

CREATE OR REPLACE FUNCTION drop_table(TEXT)
RETURNS VOID STRICT LANGUAGE plpgsql AS $$
BEGIN
EXECUTE 'DROP TABLE ' || $1;
EXCEPTION WHEN UNDEFINED_TABLE THEN
RETURN;
END;
$$;

maybe I can use

CREATE TEMP TABLE randomtablename AS SELECT drop_table('mytemptable')

But this creates a lot of randomtablename tables in each connection.

>> 2. C:\Documents and Settings\Administrator\Application
>> Data\postgresql\pgpass.conf file is not used for default passwords.
>>
>> How to force driver to use pgpass.conf file for passwords like used by
>> pgAdmin and other libpq applications ?
>
> Select an SSL Mode other than *disable* e.g. *allow*.

How to set it in connection string like SSLMode=Allow ?

I cannot force users to set it manually.

Andrus.



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: 8.02.00.02 driver incompatibilities

Andrus wrote:
> Hiroshi,
>
> thank you.
>
>
>> What kind of tool are you using ?
>>
>
> I'm using Microsoft Visual FoxPro 9.0 SP1 (see initial message).
>
>
>> Doesn't your tool have any way to skip the first recordset ?
>>
>
> I can call FoxPro SQLMORERESULTS() function
> but this is artifical: I do'nt need result set from drop_table() call to be
> returned at all.
>

Do you need success/failure information ?

>>> 2. C:\Documents and Settings\Administrator\Application
>>> Data\postgresql\pgpass.conf file is not used for default passwords.
>>>
>>> How to force driver to use pgpass.conf file for passwords like used by
>>> pgAdmin and other libpq applications ?
>>>
>> Select an SSL Mode other than *disable* e.g. *allow*.
>>
>
> How to set it in connection string like SSLMode=Allow ?
>
> I cannot force users to set it manually.
>

Could you try the dll at
http://www.geocities.jp/inocchichichi/psqlodbc.index.html ?

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: 8.02.00.02 driver incompatibilities

Hiroshi,

thank you.

>> I can call FoxPro SQLMORERESULTS() function
>> but this is artifical: I do'nt need result set from drop_table() call to
>> be
>> returned at all.
>>
> Do you need success/failure information ?

No. drop_table() function always suceeds.

>> How to set it in connection string like SSLMode=Allow ?
>
> Could you try the dll at
> http://www.geocities.jp/inocchichichi/psqlodbc.index.html ?

I haven't found download link in this site. How to download it ?
It there a connection string setting to set SSLMode=Allow ?

Documentation does not describe connection string options.

Andrus.



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: 8.02.00.02 driver incompatibilities

Andrus wrote:
> Hiroshi,
>
> thank you.
>
>>> How to set it in connection string like SSLMode=Allow ?
>> Could you try the dll at
>> http://www.geocities.jp/inocchichichi/psqlodbc.index.html ?
>
> I haven't found download link in this site. How to download it ?

Oops my mistake, sorry. Please try
http://www.geocities.jp/inocchichichi/psqlodbc/index.html .

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: 8.02.00.02 driver incompatibilities

> > This is wrong behaviour of 08.01.0200. Your app have to call
> > SQLMoreResults to get next result set. I suppose you are still
> > using FoxPro. I see the google and FoxPro may know SQLMoreResults.
>
> How to tell to postgres, odbc or change my code so that I can call
>
> drop_table('tempDOK')
>
> without returning recordset. I don't need any result from this.

I'm not so experienced but there is some ODBC specification for
calling stored procedure. Something like {call ....}. Maybe this
is the way? But why you need it? I see no problem in spliting the
queries or calling SQLMoreResults.

> >> >and thus does not work with new driver. How to force new driver to be
> >> >invoked by connection string DRIVER={PostgreSQL Unicode} ?
> >
> > You have to change Registry (rename):
> > HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\PostgreSQL
> > ->
> > HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\PostgreSQL Unicode
>
> It seems that I must change my application due to the multiple recordset
> problem.
> So I think that I try to connect first to "PostgreSQL Unicode" driver and if
> this fails then to
> "PostgreSQL" driver.
>
> How to dedect connection to nonexistent driver ("PostgreSQL Unicode")

SQLDrivers? It's ODBC function so I hope it could be accessed from
VFP.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster