Thread: Turn off implicit BEGIN from ODBC driver on Windows?
Two questions really...
(1) Is there a way to turn off the implicit BEGIN that PsqlODBC sends to PostgreSQL? I want to use multiple SQL statements within transactions.
(2) Is there a way to stop PsqlODBC from sending ABORT when disconnnected and there is no transaction in progress? (Not so critical as first question.)
I know that the ODBC driver is sending the BEGIN and ABORT on its own by viewing the psqlodbc_%.log file and observing postmaster response.
I am using...
+ PostgreSQL v7.0.3 (on Linux)
+ PsqlODBC v6.50
+ Microsoft Windows NT SP6a
+ Microsoft Visual Basic 6 SP4
+ Microsoft ADO v3.520.5303.2
Following is a code sample with comments.
-------------------------------------------------------------------
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
.ConnectionString = MyConnectionString
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
.ConnectionString = MyConnectionString
.Open
.BeginTrans '<--- Seems to be ignored by ODBC driver.
.CommitTrans '<--- Seems to be ignored by ODBC driver.
.BeginTrans '<--- Seems to be ignored by ODBC driver.
.CommitTrans '<--- Seems to be ignored by ODBC driver.
End With
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.Source = "BEGIN WORK;"
.Open
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.Source = "BEGIN WORK;"
.Open
'-- At this point, the ODBC driver sends "BEGIN" and "BEGIN WORK;"
'-- to PostgreSQL and the postmaster complains by displaying the
'-- message "BEGIN: already a transaction in progress.
.Source = "COMMIT WORK;"
.Open '<--- Seems to work OK.
End With
cn.Close
Set rs = Nothing
Set cn = Nothing
End
End With
cn.Close
Set rs = Nothing
Set cn = Nothing
End
'-- The ODBC driver sends "ABORT" to PostgreSQL. The postmaster
'-- displays "ROLLBACK: no transaction in progress" and displays the
'-- message "pg_recvbuf: unexpected EOF on client connection".
-------------------------------------------------------------------
Any help would be appreciated.
Thanks!
Doug
Did you try putting a couple SQL statements between the BeginTrans and CommitTrans and see what happened? Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: "Doug Haddan" <dhadden2@home.com> To: <pgsql-interfaces@postgresql.org> Sent: Sunday, January 14, 2001 5:27 PM Subject: [INTERFACES] Turn off implicit BEGIN from ODBC driver on Windows? Two questions really... (1) Is there a way to turn off the implicit BEGIN that PsqlODBC sends to PostgreSQL? I want to use multiple SQL statements within transactions. (2) Is there a way to stop PsqlODBC from sending ABORT when disconnnected and there is no transaction in progress? (Not so critical as first question.) I know that the ODBC driver is sending the BEGIN and ABORT on its own by viewing the psqlodbc_%.log file and observing postmaster response. I am using... + PostgreSQL v7.0.3 (on Linux) + PsqlODBC v6.50 + Microsoft Windows NT SP6a + Microsoft Visual Basic 6 SP4 + Microsoft ADO v3.520.5303.2 Following is a code sample with comments. ------------------------------------------------------------------- Dim cn As ADODB.Connection Set cn = New ADODB.Connection With cn .ConnectionString = MyConnectionString .Open .BeginTrans '<--- Seems to be ignored by ODBC driver. .CommitTrans '<--- Seems to be ignored by ODBC driver. End With Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset With rs .ActiveConnection = cn .Source = "BEGIN WORK;" .Open '-- At this point, the ODBC driver sends "BEGIN" and "BEGIN WORK;" '-- to PostgreSQL and the postmaster complains by displaying the '-- message "BEGIN: already a transaction in progress. .Source = "COMMIT WORK;" .Open '<--- Seems to work OK. End With cn.Close Set rs = Nothing Set cn = Nothing End '-- The ODBC driver sends "ABORT" to PostgreSQL. The postmaster '-- displays "ROLLBACK: no transaction in progress" and displays the '-- message "pg_recvbuf: unexpected EOF on client connection". ------------------------------------------------------------------- Any help would be appreciated. Thanks! Doug
This is the official driver? Is there an installer? or do we need to register the driver ourselves? Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: "kovacsz" <zoli@pc10.radnoti-szeged.sulinet.hu> To: "Adam Lang" <aalang@rutgersinsurance.com> Cc: <pgsql-interfaces@postgresql.org> Sent: Tuesday, January 16, 2001 10:17 AM Subject: Re: [INTERFACES] Turn off implicit BEGIN from ODBC driver on Windows? > > Did you try putting a couple SQL statements between the BeginTrans and > > CommitTrans and see what happened? > > > > [...] > > > > I am using... > > + PostgreSQL v7.0.3 (on Linux) > > + PsqlODBC v6.50 > AFAIK the newest driver (see 7.1 source) will support your needs. You can > download a binary version from my ftp site > (ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz/Linux/PostgreSQL/WinODBC) > which may be enough for you, but there are newer ones as well. Note that > v6.50 is NOT the newest version, in fact it's rather old. > > Zoltan > > (Please reply to tip@pc10.radnoti-szeged.sulinet.hu.)
On Tue, 16 Jan 2001, Adam Lang wrote: > This is the official driver? Is there an installer? or do we need to > register the driver ourselves? I suggest install an older version, then it's enough to copy the new PSQLODBC.DLL into the place of the old one. There is no official binary version at the moment. I think Julia will make a new one soon. Zoltan
> Did you try putting a couple SQL statements between the BeginTrans and > CommitTrans and see what happened? > > [...] > > I am using... > + PostgreSQL v7.0.3 (on Linux) > + PsqlODBC v6.50 AFAIK the newest driver (see 7.1 source) will support your needs. You can download a binary version from my ftp site (ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz/Linux/PostgreSQL/WinODBC) which may be enough for you, but there are newer ones as well. Note that v6.50 is NOT the newest version, in fact it's rather old. Zoltan (Please reply to tip@pc10.radnoti-szeged.sulinet.hu.)
And this shows up as 6.4, correct? Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: "kovacsz" <zoli@pc10.radnoti-szeged.sulinet.hu> To: "Adam Lang" <aalang@rutgersinsurance.com> Cc: <pgsql-interfaces@postgresql.org> Sent: Tuesday, January 16, 2001 10:17 AM Subject: Re: [INTERFACES] Turn off implicit BEGIN from ODBC driver on Windows? > > Did you try putting a couple SQL statements between the BeginTrans and > > CommitTrans and see what happened? > > > > [...] > > > > I am using... > > + PostgreSQL v7.0.3 (on Linux) > > + PsqlODBC v6.50 > AFAIK the newest driver (see 7.1 source) will support your needs. You can > download a binary version from my ftp site > (ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz/Linux/PostgreSQL/WinODBC) > which may be enough for you, but there are newer ones as well. Note that > v6.50 is NOT the newest version, in fact it's rather old. > > Zoltan > > (Please reply to tip@pc10.radnoti-szeged.sulinet.hu.)
On Wed, 17 Jan 2001, Adam Lang wrote: > And this shows up as 6.4, correct? Yes! > > > Did you try putting a couple SQL statements between the BeginTrans and > > > CommitTrans and see what happened? > > > > > > [...] > > > > > > I am using... > > > + PostgreSQL v7.0.3 (on Linux) > > > + PsqlODBC v6.50 > > AFAIK the newest driver (see 7.1 source) will support your needs. You can > > download a binary version from my ftp site [...]
> > > > I am using... > > > > + PostgreSQL v7.0.3 (on Linux) > > > > + PsqlODBC v6.50 > > > AFAIK the newest driver (see 7.1 source) will support your needs. You can > > > download a binary version from my ftp site Where is new pgsql ODBC driver ? Or: Where is that ftp site ?
Mr. Hermit Hacker sir! :) Is it possible for the lists to automatically append text to all messages? On another list I'm on they do that and it is very helpful. The append a link to the list archives to every message. This post reminded me of that. It is a lot easier to tell someone to check the archives (http://postgresql.readysetnet.com/mhonarc/pgsql-interfaces/) when the link accompanies every post. Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: "Minas Abrahamian" <a_minas@chillitech.am> To: <pgsql-interfaces@postgresql.org> Sent: Friday, August 18, 2000 5:41 AM Subject: [INTERFACES] Where is new pgsql ODBC driver ? > > > > > I am using... > > > > > + PostgreSQL v7.0.3 (on Linux) > > > > > + PsqlODBC v6.50 > > > > AFAIK the newest driver (see 7.1 source) will support your needs. You can > > > > download a binary version from my ftp site > > Where is new pgsql ODBC driver ? > > Or: Where is that ftp site ?