Re: ODBC & Transactions?

> 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