odbc.sql

Thanks everyone for helping me! Especially, Hiroshi, Noel and Richard!

Following Noel's suggestion I installed pgAdmin (II, 1.6.0). It connected fine and I could see values in the tables without any other changes to client and server PCs. So, I decided to try the same connection string that pgAdmin uses (I could conveniently cut and paste it from pgAdmin window: thanks, pgAdmin!) - and it worked! The string was:
Provider=MSDASQL.1;Extended Properties="DRIVER={PostgreSQL};DATABASE=lara;
SERVER=192.168.1.2;PORT=5432;UID=lara;PWD=********;
ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;
RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;
Socket=4096;UnknownSizes=0;MaxVarcharSize=254;
MaxLongVarcharSize=65536;Debug=0;CommLog=0;Optimizer=1;Ksqo=1;
UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;
BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;
ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=1;
DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;
UseServerSidePrepare=0"

I think the problem was that when I specified a file DSN or would build a connection string using Delphi's tools it was not handled properly or constructed properly by Delphi's TADOConnection object. So, what OLE DB driver for ODBC would get was partially illegal; so, the driver would connect but would not communicate beyond the handshake.

Now, the driver's log contains no errors (so, there was something serious there, Hiroshi). And I am going to try Richard's suggestion: run odbc.sql against the database. But my question is:

Where can I get odbc.sql?

I installed my PostgreSQL 7.3.2 from RPMs (Mandrake 9.1) and it was not in any contrib dirs. I also searched PostgreSQL, GBorg and Google sites with no success.

Thanks again,

--
Mikhail Umorin
mikeumo(at)onebox(dot)com

-----Original Message-----
From: Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp>
Sent: Thu, 18 Sep 2003 12:45:17 +0900
To: Mikhail Umorin <mikeumo(at)onebox(dot)com>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: [ODBC] Delphi7, ADO, ODBC and PostgreSQL connection problems

Hi Mikhail,

Aren't you turning on the *Use Declare/Fetch* option ?
If so, please turn it off.

regards,
Hiroshi Inoue

Mikhail Umorin wrote:
>
> Hiroshi --
> thanks for looking into my problem.
> my connection string:
> 'Provider=MSDASQL.1;Persist Security Info=False;User ID=lara;DataSource=PostgreSQL30;Mode=Read;lie=1'
>
> I tried it with your suggestion and the result is exactly the same.
> that log excerpt did contain some error; what was it? I am a newbie to ADO/ODBC so, I can't tell. As far as I know, TADOConnection object I use in my Delphi program is a wrapper around MS ADO API.
>
> Now, what I also discovered on PostgreSQL site was that 7.2 docs contained a chapter on ODBC and mentioned that one must run odbc.sql on template1 in order to have ODBC-oriented functions in the database. However, ODBC documentation is absent in 7.3 (which I have) as well as odbc.sql.
> What's up with that?
> Do I still need to run odbc.sql against template1, or ODBC API functions are already implemented there?
>
> --
> Mikhail Umorin
> mikeumo(at)onebox(dot)com
>
> -----Original Message-----
> From: Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp>
> Sent: Wed, 17 Sep 2003 10:30:51 +0900
> To: Mikhail Umorin <mikeumo(at)onebox(dot)com>
> Cc: pgsql-odbc(at)postgresql(dot)org
> Subject: Re: [ODBC] Delphi7, ADO, ODBC and PostgreSQL connection problems
>
> Mikhail Umorin wrote:
> >
> > here it is up until the message appears. After that I stopped the trace.
>
> Unfortunately I could find no clue in the log.
>
> How do you open the connection ?
> Could you try to add a "lie=1" option to your connection string ?
>
> regards,
> Hiroshi Inoue
> http://www.geocities.jp/inocchichichi/psqlodbc/

--
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/

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

There are 2 sql scripts included in the source file for the odbc driver
(latest version
psqlodbc-07.03.0100.tar.gz available by following the link to psqlodbc
on postgreSQL home page). They are: odbc.sql and odbc-drop.sql These are
the scripts that add/drop certain odbc functions to/from your database.
I apply them to template1 so that they are in all newly created databases.

Mikhail Umorin wrote:

>But my question is:
>
>Where can I get odbc.sql?
>
>I installed my PostgreSQL 7.3.2 from RPMs (Mandrake 9.1) and it was not in any contrib dirs. I also searched PostgreSQL, GBorg and Google sites with no success.
>

Mikhail Umorin wrote:
>
> Thanks everyone for helping me! Especially, Hiroshi, Noel and Richard!
>
> Following Noel's suggestion I installed pgAdmin (II, 1.6.0). It connected fine and I could see values in the tables without any other changes to client and server PCs. So, I decided to try the same connection string that pgAdmin uses (I could conveniently cut and paste it from pgAdmin window: thanks, pgAdmin!) - and it worked! The string was:
> Provider=MSDASQL.1;Extended Properties="DRIVER={PostgreSQL};DATABASE=lara;
> SERVER=192.168.1.2;PORT=5432;UID=lara;PWD=********;
> ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;
> RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;
> Socket=4096;UnknownSizes=0;MaxVarcharSize=254;
> MaxLongVarcharSize=65536;Debug=0;CommLog=0;Optimizer=1;Ksqo=1;
> UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;
> BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;
> ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=1;
> DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;
> UseServerSidePrepare=0"
>
> I think the problem was that when I specified a file DSN or
> would build a connection string using Delphi's tools it was
> not handled properly or constructed properly by Delphi's
> TADOConnection object. So, what OLE DB driver for ODBC would
> get was partially illegal; so, the driver would connect but
> would not communicate beyond the handshake.
>
> Now, the driver's log contains no errors (so, there was
> something serious there, Hiroshi).

I'm still suspecting that the *use declare/fetch option*
is turned on. Could you try to add "UseDeclareFetch=0" option
to your connection string ?

regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/

Good to hear it is working now. I also tried finding odbc.sql but can not
get to it. Should you be able to find it, please email it to me.

I am curious to what additional functionalities are in that file as I seem
to be able to work on the default installation without it. The postgresql
installation manual does not provide the details so it would be nice if
somebody in the list can give us some enlightenment.

:-)

----- Original Message -----
From: "Mikhail Umorin" <mikeumo(at)onebox(dot)com>
To: <Inoue(at)tpf(dot)co(dot)jp>; <noelrv(at)nipahut(dot)org>; <rncombs(at)covad(dot)net>
Cc: <pgsql-odbc(at)postgresql(dot)org>
Sent: Sep 18, 2003 10:57 PM
Subject: Re: [ODBC] Delphi7, ADO, ODBC and PostgreSQL connection problems:
SUCCESS!

>
> Thanks everyone for helping me! Especially, Hiroshi, Noel and Richard!
<snip>
> Now, the driver's log contains no errors (so, there was something serious
there, Hiroshi). And I am going to try Richard's suggestion: run odbc.sql
against the database. But my question is:
>
> Where can I get odbc.sql?

Hiroshi Inoue wrote:
>
> Mikhail Umorin wrote:
> >
> > Thanks everyone for helping me! Especially, Hiroshi, Noel and Richard!
> >
> > Following Noel's suggestion I installed pgAdmin (II, 1.6.0). It connected fine and I could see values in the tables without any other changes to client and server PCs. So, I decided to try the same connection string that pgAdmin uses (I could conveniently cut and paste it from pgAdmin window: thanks, pgAdmin!) - and it worked! The string was:
> > Provider=MSDASQL.1;Extended Properties="DRIVER={PostgreSQL};DATABASE=lara;
> > SERVER=192.168.1.2;PORT=5432;UID=lara;PWD=********;
> > ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;
> > RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;
> > Socket=4096;UnknownSizes=0;MaxVarcharSize=254;
> > MaxLongVarcharSize=65536;Debug=0;CommLog=0;Optimizer=1;Ksqo=1;
> > UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;
> > BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;
> > ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=1;
> > DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;
> > UseServerSidePrepare=0"
> >
> > I think the problem was that when I specified a file DSN or
> > would build a connection string using Delphi's tools it was
> > not handled properly or constructed properly by Delphi's
> > TADOConnection object. So, what OLE DB driver for ODBC would
> > get was partially illegal; so, the driver would connect but
> > would not communicate beyond the handshake.
> >
> > Now, the driver's log contains no errors (so, there was
> > something serious there, Hiroshi).
>
> I'm still suspecting that the *use declare/fetch option*
> is turned on. Could you try to add "UseDeclareFetch=0" option
> to your connection string ?

Please also try *Updatablecursors=1* option.

regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/

Hi,

maybe you try here:

http://gborg.postgresql.org/project/psqlodbc/cvs/cvs.php/psqlodbc/odbc.sql

which is in fact the psql site at GBorg.

This script supplies certain functions for ODBC compatibility.

regards

Johann Zuschlag
zuschlag2(at)online(dot)de