Printable View
Hi
When I try to do many insert in table (ORACLE), after some times (40 to 70) inserts show message in eroor handler
But When I see StateCode:? err.Description
ORA-03114: not connected to ORACLE
Why show message not connected and state is Active ?Code:? cn.State
1
Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)
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 :)
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
My open connectionCode: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
Code: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
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 ?
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
Code: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
End FunctionCode: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
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