Asynchronous connection to postgres using ODBC driver

From: Sergio Ramalho <asergio(at)ptinovacao(dot)pt>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Asynchronous connection to postgres using ODBC driver
Date: 2003-04-24 15:51:45
Message-ID: D397A7FACCC45A44A0A207FD892961EA079FE919@mail.intra.cet.pt
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

Hi,
I'm trying to execute commands asynchronously using the postgres ODBC Driver
and using ADO interface in Visual Basic.

Sometimes (not always) I'm getting the error "Connection is already in use"
when there are more than one command to be executed. Here is a piece of the
ODBC log:

conn=127021784,
PGAPI_DriverConnect(out)='DSN=MYDSN;DATABASE=MYDATABASE;SERVER=MYSERVER;PORT
=5432;UID=MYUSER;PWD=MYPASSWORD;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowO
idColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket
=4096;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=1;Comm
Log=1;Optimizer=1;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLon
gVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd
_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0
'
conn=127021784, query='INSERT INTO main_pap
(originphone,regdate,lotterynum,contactphone) VALUES
('222222222','2003-03-06 12:04:06','12345','222222222')'
STATEMENT ERROR: func=PGAPI_Execute, desc='', errnum=3, errmsg='Connection
is already in use.'

------------------------------------------------------------
hdbc=127021784, stmt=127076368, result=0
manual_result=0, prepare=0, internal=0
bindings=0, bindings_allocated=0
parameters=0, parameters_allocated=0
statement_type=1, statement='INSERT INTO main_pap
(originphone,regdate,lotterynum,contactphone) VALUES
('222079300','2003-03-06 12:04:06','12345','222079324')'
stmt_with_params='(NULL)'
data_at_exec=-1, current_exec_param=-1, put_data=0
currTuple=-1, current_col=-1, lobj_fd=-1
maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0,
scroll_concurrency=1
cursor_name=''
----------------QResult Info
-------------------------------
CONN ERROR: func=PGAPI_Execute, desc='', errnum=0, errmsg='(NULL)'
------------------------------------------------------------
henv=127075968, conn=127021784, status=3, num_stmts=208
sock=127076016, stmts=127040976, lobj_type=-999
---------------- Socket Info -------------------------------
socket=536, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=127032768, buffer_out=127036872
buffer_filled_in=63, buffer_filled_out=0, buffer_read_in=0

Is this a limitation of the ODBC driver (executing two commands on the same
connection)? Is there any parameters that helps with this problem?

The following example helps reproduce this problem:

Option Explicit

Private WithEvents cn As ADODB.Connection

Private cmd1 As ADODB.Command
Private cmd2 As ADODB.Command

Private Sub cn_ConnectComplete(ByVal pError As ADODB.Error, adStatus As
ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)


If adStatus = adStatusOK Then
Debug.Print "Connection Established"

'Create and initialize command objects
Set cmd1 = New ADODB.Command
With cmd1
.CommandText = "delete from MYTABLE"
.ActiveConnection = cn
End With

Set cmd2 = New ADODB.Command
With cmd2
.CommandText = "delete from MYTABLE"
.ActiveConnection = cn
End With

End If
End Sub

Private Sub cn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError
As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As
ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As
ADODB.Connection)
If adStatus = adStatusOK Then
Debug.Print "Command Executed"
ElseIf adStatus = adStatusErrorsOccurred Then
Debug.Print pError.Description
End If
End Sub

Private Sub Form_Load()

Set cn = New ADODB.Connection

' Connect to the database using ODBC
With cn
.ConnectionString = "dsn=pgsql_test_blob;"
.CursorLocation = adUseClient
.Open , , , adAsyncConnect
End With

End Sub

Private Sub Command1_Click()
'Execute 2 commands at the same time
cmd1.Execute , , adAsyncExecute
cmd2.Execute , , adAsyncExecute

End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)

Set cmd1 = Nothing
Set cmd2 = Nothing
Set cn = Nothing

End Sub

thanks,
Sérgio

Browse pgsql-odbc by date

  From Date Subject
Next Message fabrice 2003-04-24 17:40:12 calling stored procedure via odbc from Access
Previous Message pedwards 2003-04-24 06:04:31 Contact us!