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