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
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! |