Thread: ODBC & Transactions?

Hi,

I'm having a little bit of trouble with transactions.
I'm using psqlodbc-07.01.0008; PostgreSQL 7.1.2 on i686-pc-linux-gnu,
compiled by GCC 2.95.3; VB 6 SP5 with ADO (MDAC 2.6 SP1)

If I execute the SQL statements using ADO in VB6:

begin work
update mytable set myfield='changed-data' where myid = 1
rollback work

and then I check the database using psql on the server box, myfield =
'changed-data'; and the rollback did *not* work.

In the postgres log appears:
NOTICE:  ROLLBACK: no transaction in progress

I've attached a psqlodbc log file (if attachments are allowed - it should
appear)

Are transactions supported in postgresql ODBC?

(I've tried adoConnection.BeginTrans; etc as well with no success)

Any help appreciated.

Regards,
Mike Miller
Application Software Developer,
School Of Physical Education,
University Of Otago,
+64 3 479 9123
http://physed.otago.ac.nz

"Mike Miller" <mmiller@pooka.otago.ac.nz> writes:
> If I execute the SQL statements using ADO in VB6:

> begin work
> update mytable set myfield='changed-data' where myid = 1
> rollback work

> and then I check the database using psql on the server box, myfield =
> 'changed-data'; and the rollback did *not* work.

> In the postgres log appears:
> NOTICE:  ROLLBACK: no transaction in progress

This strongly suggests that ODBC is emitting its own "begin" and
"commit" commands around each query that you give.  I am not an ODBC
person but I think that this misfeature is called autocommit and that
you can turn it off.

If you want to know what's really going on, try enabling query logging
at the postmaster, then look in the postmaster's log output to see what
queries are really getting sent by ODBC.

            regards, tom lane

Hi,

I think the feature Tom is referring to is called "Use Declare/Fetch" and can be found under Advanced Options for the
ODBCDriver. 

Ryan

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Wednesday, November 07, 2001 12:30 PM
> To: Mike Miller
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] ODBC & Transactions?
>
>
> "Mike Miller" <mmiller@pooka.otago.ac.nz> writes:
> > If I execute the SQL statements using ADO in VB6:
>
> > begin work
> > update mytable set myfield='changed-data' where myid = 1
> > rollback work
>
> > and then I check the database using psql on the server box,
> myfield =
> > 'changed-data'; and the rollback did *not* work.
>
> > In the postgres log appears:
> > NOTICE:  ROLLBACK: no transaction in progress
>
> This strongly suggests that ODBC is emitting its own "begin" and
> "commit" commands around each query that you give.  I am not an ODBC
> person but I think that this misfeature is called autocommit and that
> you can turn it off.
>
> If you want to know what's really going on, try enabling query logging
> at the postmaster, then look in the postmaster's log output
> to see what
> queries are really getting sent by ODBC.
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>

> I think the feature Tom is referring to is called "Use
> Declare/Fetch" and can be found under Advanced Options for the
> ODBC Driver.

Declare/Fetch ... the driver automatically uses declare cursor/fetch to
handle SELECT statements and keeps 100 rows in a cache ... results in the
driver not sucking down lots of memory to buffer the entire result set ...

With this option on;

1) the first time - the rollback worked; *joy*
2) the second time - error message - This option not supported by ODBC
driver *no joy*
3) repeat 1,2 five times
4) any subsequent times - rollback does not work; but no "not supported"
error message

hmmm..

> > If you want to know what's really going on, try enabling query logging
> > at the postmaster, then look in the postmaster's log output
> > to see what
> > queries are really getting sent by ODBC.

How do I do this?  I have set the postmaster debug level to 2 and this is
what it came up with...

DEBUG:  StartTransactionCommand
DEBUG:  query: BEGIN WORK
DEBUG:  ProcessUtility: BEGIN WORK
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: update Practical set Description = 'An introduction to
adventure through a Canadian-
[SNIP]
where PracticalID = 1
DEBUG:  ProcessQuery
DEBUG:  query: SELECT oid FROM ONLY "staff" WHERE "staffid" = $1 FOR UPDATE
OF "staff"
DEBUG:  query: SELECT oid FROM ONLY "practicalcategory" WHERE
"practicalcategoryid" = $1 FOR UPDATE OF "practicalcategory"
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: ROLLBACK WORK
DEBUG:  ProcessUtility: ROLLBACK WORK
DEBUG:  CommitTransactionCommand

You can see my BEGIN WORK, update and ROLLBACK WORK queries, but there are a
lot of surrounding
StartTransactionCommand and CommitTransactionCommand.
What are the DEBUG:StartTransactionCommand and the
DEBUG:CommitTransactionCommand lines?

<SPECULATION>
It *looks* to me like something is wrapping all the queries in a
transaction.  Is this an autocommit feature?  If so, is it from ODBC or
postgres itself.  How does one turn it off - apparently "BEGIN WORK" *does
not* turn it off...
</SPECULATION>

Any help appreciated.

Regards,
Mike Miller
Application Software Developer,
School Of Physical Education,
University Of Otago,
+64 3 479 9123
http://physed.otago.ac.nz

Mike Miller wrote:
>

[snip]

>
> <SPECULATION>
> It *looks* to me like something is wrapping all the queries in a
> transaction.  Is this an autocommit feature?  If so, is it from ODBC or
> postgres itself.  How does one turn it off - apparently "BEGIN WORK" *does
> not* turn it off...
> </SPECULATION>

CommitTransactionCommand doesn't necessarily mean COMMIT.
You have to use BeginTrans to turn of the autocommit mode
but it doesn't seem the cause of your case(It works well
here). Judging from the psqlodbc.log file you posted,
"rollback work" is issued by the differenct connection
from the one which issued "begin work" and "update ...".
Hmm are you using connection pooling ?

regards,
Hiroshi Inoue

> CommitTransactionCommand doesn't necessarily mean COMMIT.
> You have to use BeginTrans to turn of the autocommit mode
> but it doesn't seem the cause of your case(It works well
> here). Judging from the psqlodbc.log file you posted,
> "rollback work" is issued by the differenct connection
> from the one which issued "begin work" and "update ...".
> Hmm are you using connection pooling ?

I don't know if I am using connection pooling or not.  Is it client side or
server side?
Where do I find out? I am using Visual Basic 6 with ADO and psqlodbc.

conn=86609100, query='BEGIN WORK'
[SNIP]
conn=86609100, query='update Practical set Descr ...
[SNIP]
conn=86628184, query='ROLLBACK WORK'
NOTICE from backend during send_query: 'NOTICE:  ROLLBACK: no transaction in
progress

or

conn=86609100, query='BEGIN WORK'
[SNIP]
conn=86628184, query='update Practical set Descri ...
[SNIP]
conn=86609100, query='ROLLBACK WORK'

I see what you mean.  From some tests it *seems* to randomly pick a
connection to use; which would explain why it worked sometimes (and really
confused me :-)

Mike.

Mike Miller wrote:
>
> > CommitTransactionCommand doesn't necessarily mean COMMIT.
> > You have to use BeginTrans to turn of the autocommit mode
> > but it doesn't seem the cause of your case(It works well
> > here). Judging from the psqlodbc.log file you posted,
> > "rollback work" is issued by the differenct connection
> > from the one which issued "begin work" and "update ...".
> > Hmm are you using connection pooling ?
>
> I don't know if I am using connection pooling or not.  Is it client side or
> server side?

You can see it using ODBC Datasource Administrator on your
Windows control panel.
Probably you can find the *connection pooling*(I'm not sure
because I'm using Japanese version) tab together with
*User DSN*, *System DSN*, ..., *Trace*, ...

regards,
Hiroshi Inoue

> > CommitTransactionCommand doesn't necessarily mean COMMIT.
> > You have to use BeginTrans to turn of the autocommit mode
> > but it doesn't seem the cause of your case(It works well
> > here). Judging from the psqlodbc.log file you posted,
> > "rollback work" is issued by the differenct connection
> > from the one which issued "begin work" and "update ...".
> > Hmm are you using connection pooling ?

> You can see it using ODBC Datasource Administrator on your
> Windows control panel.
> Probably you can find the *connection pooling*(I'm not sure
> because I'm using Japanese version) tab together with
> *User DSN*, *System DSN*, ..., *Trace*, ...

Thanks, I found it.  Connection Pooling is off.  How does that relate to the
different connections
I am seeing in the log file?

Regards,
Mike Miller
Application Software Developer,
School Of Physical Education,
University Of Otago,
+64 3 479 9123
http://physed.otago.ac.nz

Mumble, can you show us the exact VB code you are using to
send these queries? Maybe it can help... (well, it would be
excellent if you could post a stripped down test case starting
from connection, query execution, etc. that shows the behaviour
you are experiencing)
Best regards
Andrea Aime

Mike Miller wrote:
>
> Hi,
>
> I'm having a little bit of trouble with transactions.
> I'm using psqlodbc-07.01.0008; PostgreSQL 7.1.2 on i686-pc-linux-gnu,
> compiled by GCC 2.95.3; VB 6 SP5 with ADO (MDAC 2.6 SP1)
>
> If I execute the SQL statements using ADO in VB6:
>
> begin work
> update mytable set myfield='changed-data' where myid = 1
> rollback work
>
> and then I check the database using psql on the server box, myfield =
> 'changed-data'; and the rollback did *not* work.
>
> In the postgres log appears:
> NOTICE:  ROLLBACK: no transaction in progress
>
> I've attached a psqlodbc log file (if attachments are allowed - it should
> appear)
>
> Are transactions supported in postgresql ODBC?
>
> (I've tried adoConnection.BeginTrans; etc as well with no success)
>
> Any help appreciated.
>
> Regards,
> Mike Miller
> Application Software Developer,
> School Of Physical Education,
> University Of Otago,
> +64 3 479 9123
> http://physed.otago.ac.nz
>
>   ------------------------------------------------------------------------
>                               Name: psqlodbc_4294386287.log
>    psqlodbc_4294386287.log    Type: Documento di testo (application/x-unknown-content-type-txtfile)
>                           Encoding: quoted-printable
>
>   ------------------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

> Mumble, can you show us the exact VB code you are using to
> send these queries? Maybe it can help... (well, it would be
> excellent if you could post a stripped down test case starting
> from connection, query execution, etc. that shows the behaviour
> you are experiencing)

'**********************************************************

Dim adoConnection As ADODB.Connection
Dim strADOConnection As String

'**********************************************************

Sub ConnectDB()

   ' Create the ADO object
   Set adoConnection = CreateObject("ADODB.Connection")

   ' Set up the connection string to the database
   strADOConnection = "DSN=PostgreSQL"

   ' Open the connection
   With adoConnection
      .ConnectionString = strADOConnection
      .Open
   End With

End Sub

'**********************************************************

Sub DisconnectDB()

   ' Close connection
   With adoConnection
      .Close
   End With

   ' Clean up
   Set adoConnection = Nothing

End Sub

'**********************************************************

Function SQLCommand(strSQL As String) As Integer

   ' Variables
   Dim adoCommand As ADODB.Command
   Dim lngRecordsAffected As Long

   ' Create the ADO command object
   Set adoCommand = CreateObject("ADODB.Command")

   ' Set the connection, the command and execute it
   With adoCommand
      .ActiveConnection = strADOConnection
      .CommandText = strSQL
      .Execute lngRecordsAffected, , adCmdText
   End With

   ' Clean up
   Set adoCommand = Nothing

   DebugShow "Records affected:" & lngRecordsAffected

   SQLCommand = 0

End Function

'**********************************************************

Function SQLBegin() As Boolean

   SQLBegin = False
   'DebugShow "Begin Transaction"
   'adoConnection.BeginTrans
   If SQLCommand("BEGIN WORK") = 0 Then
      SQLBegin = True
   End If

End Function

'**********************************************************

Function SQLCommit() As Boolean

   SQLCommit = False
   'DebugShow "Commit Transaction"
   'adoConnection.CommitTrans
   If SQLCommand("COMMIT WORK") = 0 Then
      SQLCommit = True
   End If

End Function

'**********************************************************

Function SQLRollback() As Boolean

   SQLRollback = False
   'DebugShow "Rollback Transaction"
   'adoConnection.RollbackTrans
   If SQLCommand("ROLLBACK WORK") = 0 Then
      SQLRollback = True
   End If

End Function

'**********************************************************

** IN A DIFFERENT MODULE **

Sub Main()

   ConnectDB
   SQLBegin
   SQLCommand "update Practical set Description = 'test' where PracticalID =
1"
   SQLRollback
   DisconnectDB

End Sub

'**********************************************************

And the psql_odbc log with different connection for each command marked with
[**!!!!**]
Please note: connection pooling is off.  Something seems to be opening a new
connection; but why and what I don't know.

conn=86609100,
PGAPI_DriverConnect(out)='DSN=PostgreSQL;DATABASE=pracs;SERVER=******;PORT=5
432;UID=******;PWD=******;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLU
MN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;FETCH=100;SOCKET=4096;
UNKNOWNSIZES=0;MAXVARCHARSIZE=254;MAXLONGVARCHARSIZE=8190;DEBUG=0;COMMLOG=1;
OPTIMIZER=1;KSQO=1;USEDECLAREFETCH=1;TEXTASLONGVARCHAR=1;UNKNOWNSASLONGVARCH
AR=0;BOOLSASCHAR=1;PARSE=0;CANCELASFREESTMT=0;EXTRASYSTABLEPREFIXES=dd_;'
[**!!!!**] conn=86609100, query='BEGIN WORK'
conn=86628184, PGAPI_DriverConnect( in)='DSN=PostgreSQL;',
fDriverCompletion=0
DSN info:
DSN='PostgreSQL',server='******',port='5432',dbase='pracs',user='******',pas
swd='******'

onlyread='0',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0'
          conn_settings='',conn_encoding='OTHER'
          translation_dll='',translation_option=''
Global Options: Version='07.01.0008', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
                disable_optimizer=1, ksqo=1, unique_index=1,
use_declarefetch=1
                text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1
                extra_systable_prefixes='dd_;', conn_settings=''
conn_encoding='OTHER'
conn=86628184, query=' '
conn=86628184, query='set DateStyle to 'ISO''
conn=86628184, query='set geqo to 'OFF''
conn=86628184, query='set ksqo to 'ON''
conn=86628184, query='BEGIN'
conn=86628184, query='declare SQL_CUR053A0940 cursor for select oid from
pg_type where typname='lo''
conn=86628184, query='fetch 100 in SQL_CUR053A0940'
    [ fetched 0 rows ]
conn=86628184, query='close SQL_CUR053A0940'
conn=86628184, query='END'
conn=86628184, query='BEGIN'
conn=86628184, query='declare SQL_CUR053A0940 cursor for select version()'
conn=86628184, query='fetch 100 in SQL_CUR053A0940'
    [ fetched 1 rows ]
    [ PostgreSQL version string = 'PostgreSQL 7.1.2 on i686-pc-linux-gnu,
compiled by GCC 2.95.3' ]
    [ PostgreSQL version number = '7.1' ]
conn=86628184, query='close SQL_CUR053A0940'
conn=86628184, query='END'
conn=86628184,
PGAPI_DriverConnect(out)='DSN=PostgreSQL;DATABASE=pracs;SERVER=******;PORT=5
432;UID=******;PWD=******;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLU
MN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;FETCH=100;SOCKET=4096;
UNKNOWNSIZES=0;MAXVARCHARSIZE=254;MAXLONGVARCHARSIZE=8190;DEBUG=0;COMMLOG=1;
OPTIMIZER=1;KSQO=1;USEDECLAREFETCH=1;TEXTASLONGVARCHAR=1;UNKNOWNSASLONGVARCH
AR=0;BOOLSASCHAR=1;PARSE=0;CANCELASFREESTMT=0;EXTRASYSTABLEPREFIXES=dd_;'
[**!!!!**] conn=86628184, query='update Practical set Description = 'test'
where PracticalID = 1'
CONN ERROR: func=PGAPI_GetConnectOption, desc='fOption=1209', errnum=205,
errmsg='Unknown connect option (Get)'
            ------------------------------------------------------------
            henv=87691120, conn=86609100, status=1, num_stmts=16
            sock=87690128, stmts=87690048, lobj_type=-999
            ---------------- Socket Info -------------------------------
            socket=516, reverse=0, errornumber=0, errormsg='(NULL)'
            buffer_in=86619984, buffer_out=86624084
            buffer_filled_in=8, buffer_filled_out=0, buffer_read_in=8
[**!!!!**] conn=86609100, query='ROLLBACK WORK'
conn=86590016, PGAPI_Disconnect
conn=86609100, PGAPI_Disconnect
conn=86628184, PGAPI_Disconnect


Thanks,
Mike

Mike Miller wrote:
>
> > Mumble, can you show us the exact VB code you are using to
> > send these queries? Maybe it can help... (well, it would be
> > excellent if you could post a stripped down test case starting
> > from connection, query execution, etc. that shows the behaviour
> > you are experiencing)
>
> '**********************************************************
>
> Dim adoConnection As ADODB.Connection
> Dim strADOConnection As String
>
> '**********************************************************
>
> Sub ConnectDB()
>
>    ' Create the ADO object
>    Set adoConnection = CreateObject("ADODB.Connection")
>
>    ' Set up the connection string to the database
>    strADOConnection = "DSN=PostgreSQL"
>
>    ' Open the connection
>    With adoConnection
>       .ConnectionString = strADOConnection
>       .Open
>    End With
>
> End Sub
>
> '**********************************************************
>
> Sub DisconnectDB()
>
>    ' Close connection
>    With adoConnection
>       .Close
>    End With
>
>    ' Clean up
>    Set adoConnection = Nothing
>
> End Sub
>
> '**********************************************************
>
> Function SQLCommand(strSQL As String) As Integer
>
>    ' Variables
>    Dim adoCommand As ADODB.Command
>    Dim lngRecordsAffected As Long
>
>    ' Create the ADO command object
>    Set adoCommand = CreateObject("ADODB.Command")
>
>    ' Set the connection, the command and execute it
>    With adoCommand
>       .ActiveConnection = strADOConnection

How about
    .ActiveConnection = adoConnection
?

regards,
Hiroshi Inoue

> How about
>     .ActiveConnection = adoConnection
> ?

That solved my problem.  Thank you Hiroshi, Andrea and others for your help.
I *really* appreciate it.  Plus I learned a lot in the process.  Thanks,
once again.

From the MSDN about ActiveConnection.

You can set this property to a valid Connection object or to a valid
connection string. In this case, the provider creates a new Connection
object using this definition and opens the connection.

Regards,
Mike Miller
Application Software Developer,
School Of Physical Education,
University Of Otago,
+64 3 479 9123
http://physed.otago.ac.nz