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 по дате отправления:

Предыдущее
От: fabrice
Дата:
Сообщение: Stored Procedures
Следующее
От: pedwards@anedsociety.org
Дата:
Сообщение: Contact us!