Thread: Re: Delphi7, ADO, ODBC and PostgreSQL connection problems: SUCCESS!
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
withoutany other changes to client and server PCs. So, I decided to try the same connection string that pgAdmin uses (I
couldconveniently 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
nothandled properly or constructed properly by Delphi's TADOConnection object. So, what OLE DB driver for ODBC would
getwas 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'ssuggestion: 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@onebox.com
-----Original Message-----
From: Hiroshi Inoue <Inoue@tpf.co.jp>
Sent: Thu, 18 Sep 2003 12:45:17 +0900
To: Mikhail Umorin <mikeumo@onebox.com>
Cc: pgsql-odbc@postgresql.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,
TADOConnectionobject 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
mustrun odbc.sql on template1 in order to have ODBC-oriented functions in the database. However, ODBC documentation is
absentin 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@onebox.com
>
> -----Original Message-----
> From: Hiroshi Inoue <Inoue@tpf.co.jp>
> Sent: Wed, 17 Sep 2003 10:30:51 +0900
> To: Mikhail Umorin <mikeumo@onebox.com>
> Cc: pgsql-odbc@postgresql.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
withoutany other changes to client and server PCs. So, I decided to try the same connection string that pgAdmin uses (I
couldconveniently 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/
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
withoutany other changes to client and server PCs. So, I decided to try the same connection string that pgAdmin uses (I
couldconveniently 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/
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@onebox.com> To: <Inoue@tpf.co.jp>; <noelrv@nipahut.org>; <rncombs@covad.net> Cc: <pgsql-odbc@postgresql.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?
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@online.de