Connection dropped but active

[mutley]

Hi

When I try to do many insert in table (ORACLE), after some times (40 to 70) inserts show message in eroor handler

? err.Description
ORA-03114: not connected to ORACLE

But When I see State

? cn.State
 1

Why show message not connected and state is Active ?

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

[Optional]

If the connection has been dropped from the oracle server side your connection object would not update it's state but thinks it's open and tries to continue.

Maybe one of you inserts causes a time out response from the Oracle Server or for some reason your connection is getting killed off on the oracle server side.

I had a similar problem where our oracle DBA kept killing my VB connection to the database cause he didn't realise it was a valid connection.

Have a look what might be causing the server to kill your connection.

Hope I'm not sending you ona wild goose-chase

[mutley]

Thank you

But how can I

thinks it's open and tries to continue.


I tried put in handler erro a GOTO and try open connection , but the connection return me NOTHING

Public Sub Grava_Log_Email(tipoProcesso As String, tipoErro As String, dsErro As String)
'Dim mRsLog                    As ADODB.Recordset
Dim iBegin As Boolean
Dim cn As ADODB.Connection


On Error GoTo ERRO_LOG
  
inicio:
    Set cn = Nothing
    Set cn = Abre_Conexao
    iBegin = False
   ' Set mRsLog = New ADODB.Recordset
   ' mRsLog.CursorLocation = adUseClient
    
    mSql = "INSERT INTO SIBTB_LOG_ERRO_EMAIL  (LOG_PROC,LOG_DATA,LOG_TERMINAL,LOG_USER,LOG_TP_MSG,LOG_MSG) VALUES("
    mSql = mSql & "'" & Mid(tipoProcesso, 1, 25) & "',"
    mSql = mSql & " SYSDATE,'','"
    mSql = mSql & cUser & "','"
    mSql = mSql & tipoErro & "','"
    mSql = mSql & Converte_Apostrofo_em_Ponto(dsErro) & "')"
  '  cn.BeginTrans
    iBegin = True
    cn.Execute mSql, adExecuteNoRecords
   ' cn.CommitTrans
    
    Set itx = MdiSib.lvwMsg.ListItems.Add(1, , tipoProcesso)
    MdiSib.lvwMsg.ListItems(1).SubItems(1) = tipoErro
    MdiSib.lvwMsg.ListItems(1).SubItems(2) = dsErro
   ' DoEvents
    cn.Close
   
    Exit Sub
ERRO_LOG:
    If Err.Number = -2147467259 Then
      GoTo inicio
    End If
    'If iBegin = True Then cn.RollbackTrans
    If UCase(Command) = "RMAIL" Then
        
        Call Grava_Log_Email("Grava_Log_Email", 9, Err.Number & " - " & Err.Description & "(" & cUser & ")")
    Else
        MsgBox "Erro na rotina de Log: " & Err.Number & " - " & Err.Description, vbCritical
    End If
    End
End Sub

My open connection

Public Function Abre_Conexao() As ADODB.Connection

   Dim cn As ADODB.Connection
   
   On Error GoTo Abre_Conexao_Error
    Set cn = Nothing
    Set cn = New ADODB.Connection
    With cn
        .ConnectionTimeout = 40
        .Provider = "MSDAORA.1"
        .Properties("Data Source").Value = gDataSource
        .Properties("Persist Security Info").Value = False
        .Properties("User ID").Value = gUser
        .Properties("Password").Value = gPws
        .CursorLocation = adUseServer ' = adUseClient
         'MdiSib.sbaUser.Panels("Banco") = gSID
        .Open
    End With
    Set Abre_Conexao = cn
    
   On Error GoTo 0
   Exit Function

Abre_Conexao_Error:

    'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Abre_Conexao of Módulo modGeral"
End Function

[Optional]

You originally stated you loose your connection after about 40 to 70 records.
Could that be related to the SQL function (Converte_Apostrofo_em_Ponto(dsErro)) call in your SQL statement?

Is dsError always different per insert ?
If so....

I'm assuming the SQL function does some formatting to the dsError value to be inserted.
I'm further assuming that not all dsError values you are passing have qualifying characters to require formatting.
Thus, could it be that when a dsError value is passed (after 40 to 70 records) with a character which requires the formatting that the function gernerates an unrecoverable error ?

I'm only thinking out loud.

What does function (Converte_Apostrofo_em_Ponto(dsErro) ) exactly do?

Can you pinpoint the value dsError is at the time the conection is lost ?
(I'm assuming its 1 insert call previous to the one which has the connection lost)

Ones you have that value can your SQL function process it when called manually ?

Again, I'm only speculating to what could cause 40 to 70 records to insert and then all of a sudden stop working. If its not a database time-out then I'm always leaning torwards data issues, meaning the values you are passing to the SQL statement.

Edit
Forgot to ask, what is the exact line in the code that you are executing when the error is raised ?

[mutley]

Thank you

the error happens anywhere , almost always when I Open a recordset

eg below, but happen in others places of my program, main when I try open a recordset

Public Sub Carrega_List_Negocio()
    Set rsAux = Nothing
    Set rsAux = New ADODB.Recordset
    rsAux.CursorLocation = adUseClient
    mSql = "SELECT DISTINCT CD_NEGOCIO AS Codigo, DS_NEGOCIO AS Descricao FROM SIBTB_NEGOCIO"
    If Len(gStr_PermissaoNegocio) > 0 Then
        If UCase(Command) <> "RMAIL" Then
            mSql = mSql & " WHERE CD_MARCA||CD_NEGOCIO IN (" & gStr_PermissaoNegocio & ")"
        Else
            mSql = mSql & " WHERE CD_NEGOCIO IN (" & gStr_PermissaoNegocio_eMail & ")"
        End If
    End If
'  the error here , cn.state =1 
' Err.Number = -2147467259   ORA-03114: not connected to ORACLE
    rsAux.Open mSql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

Public Function Converte_Apostrofo_em_Ponto(st As String) As String
Dim str As String
    For i = 1 To Len(st)
        If Mid(st, i, 1) = "'" Then
            str = "."
        Else
            str = Mid(st, i, 1)
        End If
        Converte_Apostrofo_em_Ponto = Converte_Apostrofo_em_Ponto & str
    Next i

End Function

[Optional]

I'm a bit at a loss now as it seems a sporadic occurrence.

I did some searching and it could be related to an issue with network conectivity.

You establish a connection to the database just fine but by the time you want to open the record set your network connectivity is lost (and also re-established again) and thus the state = 1 but as the connection was interrupted the object's handle is not pointing to the active connection anymore.

The link below seems to describe similar symptoms:
ADODB Connection does not error when no network connection is available

The poster stated that you should always delete the connection object before using it again, regardless of state, thus ensuring a fresh connection everytime.

For example, I can see in your posted code in method "Carrega_List_Negocio", that you are using CN without re-initializing it.

It's also good practice to always initialize, open, use, close and destroy a connection object everytime you want to use it. Unless off course there are requirements to meet which need you have a constantly open connection.

I'm assuming the poster in the above link means that just before using the connection object with rsAux.Open you re-initialize cn completly.

For example:
Set CN = nothing
Set CN = new ADODB.Connection
Set CN = Abre_Conexao
(Setting an object as = new <ObjectName> is good practice, even if assigning it in the next line of code)

Edit
In your method "Grava_Log_Email", try placing the line "Set CN = Abre_Conexao" right before "cn.Execute mSql, adExecuteNoRecords".
Also, try using cn.open before using the connection object too and see if that makes a difference.

Basically make your code look similar to the below and see if that makes any difference.
Set cn = Abre_Conexao
cn.Open
cn.Execute mSql, adExecuteNoRecords