Asynchronous connection to postgres using ODBC driver
От | Sergio Ramalho |
---|---|
Тема | Asynchronous connection to postgres using ODBC driver |
Дата | |
Msg-id | D397A7FACCC45A44A0A207FD892961EA079FE919@mail.intra.cet.pt обсуждение исходный текст |
Список | 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
В списке pgsql-odbc по дате отправления: