Subject Re: [firebird-support] Hang On ExecuteNonQuery Using Firebird2.5 with visual studio 2010
Author SUKHEN DASS
Post date 2015-02-24T08:52:38Z
Following Code/Process I am Using

Private Function Process_Grid_Data() As Boolean
Dim rdr As FbDataReader
Dim dicStoredProc As New Dictionary(Of String, String)
Dim fbTrans As FbTransaction = Nothing
Dim strErrorMsg as String=""
Dim Is_Update as Boolean=False
Dim nITEM_IDS As Long = 0
Dim nGroup_IDS As Integer = 0
Dim nCategory_IDS As Integer = 0
Dim nUnit_IDS As Integer = 0
Dim nTax_IDS As Integer = 0
Dim nGroup_Added as Integer=0
Public myImportConnection As New FbConnection
Try
If myImportConnection.State = ConnectionState.Closed Then
    If OpenImportConnection() = False Then
      MessageBox.Show("Database Connection Does not Exist. Check database connection....!!!", "Database Connection", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Exit Function
    End If
End If
        fbTrans = myImportConnection.BeginTransaction()
For iLoop as Integer=0 to grid.Rows.Count-1 '--------Grid Has 100,000 Rows
'---------Sometimes Hang Here Also---------
rdr = Get_Reader("USP_CHECK_AND_GET_ITEM_EXIST('" & Mid(Trim(dicFields_Name.Item("SP_ITEM_CODES")), 1, 20) & "','" & Mid(Trim(dicFields_Name.Item("ITEM_PART_CODES")), 1, 20) & "','" & Mid(Trim(dicFields_Name.Item("ITEM_NAMES")), 1, 50) & "','" & Mid(Trim(dicFields_Name.Item("CUSTOM_PART_CODE")), 1, 20) & "','" & Mid(Trim(dicFields_Name.Item("CUSTOM_PART_NAME")), 1, 50) & "','" & Mid(Trim(dicFields_Name.Item("SALE_UNIT")), 1, 10) & "','" & Mid(Trim(dicFields_Name.Item("SALE_SUB_UNIT")), 1, 10) & "','" & Mid(Trim(dicFields_Name.Item("ITEM_LOCAL_TAX")), 1, 20) & "','" & Mid(Trim(dicFields_Name.Item("ITEM_GROUP")), 1, 50) & "','" & Mid(Trim(dicFields_Name.Item("ITEM_CATEGORY_ID")), 1, 50) & "')", fbTrans, , myImportConnection)
While rdr.Read
nITEM_IDS=Val(rdr("ITEM_IDS") & "")
nGroup_IDS=Val(rdr("GROUP_IDS") & "")
nCategory_IDS=Val(rdr("CATEGORY_IDS") & "")
nUnit_IDS=Val(rdr("UNIT_IDS") & "")
nTax_IDS=Val(rdr("TAX_IDS") & "")
End While
rdr.Close()

If nITEM_IDS > 0 Then
    Is_Update = True
Else
    Is_Update = False
End If
IF nGroupd_IDS=0 Then
If Create_Group(Mid(Trim(dicFields_Name.Item("ITEM_GROUP")), 1, 50),myImportConnection)=True Then
'nGroup_Added=nGroup_Added+1
End If
End If
IF nCategory_IDS=0 Then
If Create_Category(Mid(Trim(dicFields_Name.Item("ITEM_CATEGORY")), 1, 50),myImportConnection)=True Then
'Count For Category Added
End If
End If
IF nUnit_IDS=0 Then
If Create_Unit(Mid(Trim(dicFields_Name.Item("SALE_UNIT")), 1, 10),myImportConnection)=True Then
'Count For Unit Added
End If
End If
IF nTax_IDS=0 Then
If Create_Tax(Mid(Trim(dicFields_Name.Item("ITEM_LOCAL_TAX")), 1, 20),myImportConnection)=True Then
'Count For Tax Added
End If
End If
If Is_Update=True Then
If Update_Item_Master(nItem_IDS,Item_Name,Rate,bla,bla,bla,myImportConnection)=True Then
'Count For Item Updated
Else
'Error_Message='Why not updated...!!!'
End
Else
If Insert_Item_Master(Item_Name,Rate,bla,bla,bla,myImportConnection)=True Then
'Count For Item Inserted
Else
'Error_Message='Why not updated...!!!'
End
End
Next
fbTrans.Commit()
return true
Catch ex as Exception
strErrorMsg = strErrorMsg & ex.ToString
GoTo errMSG
Finally

End Try
errr:
If Not fbTrans Is Nothing Then
            fbTrans.Rollback()
        End If
If strErrorMsg <> "" Then
            MessageBox.Show(strErrorMsg, lblInvoice_Caption.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
        Return False
End Function

Public Function Update_Item_Master(Var1,Var2,Var3,Bla,Bla,Bla,myImportConnection)
'Update Statement
return true
End Function

Public Function Update_Item_Master(Var1,Var2,Var3,Bla,Bla,Bla,myImportConnection)
Try
'Insert Statement
PrePare_Column_Parameter_And_Values
            Using fCommand As FbCommand = myImportConnection.CreateCommand
                fCommand.Cancel()
                fCommand.CommandText = "Insert Into M_ITEM(" & strColumns.ToString.TrimEnd(",") & ") Values(" & strValues.ToString.TrimEnd(",") & ")"
                fCommand.Connection = myImportConnection
                fCommand.CommandType = CommandType.Text
                fCommand.Transaction = fbTrans
                myResult = fCommand.BeginExecuteNonQuery(Nothing, Nothing)
                
'While Not myResult.IsCompleted
                    'Me.Text = lblInvoice_Caption.Text & "[Processing Row No." & Row_No.ToString() & " - Adding In Item Master]"
                'End While
                'iError = fCommand.EndExecuteNonQuery(myResult)
iError = fCommand.ExecuteNonQuery() '-------Hang In THis Line--------
                fCommand.Parameters.Clear()
  End Using
Return True
Catch ex as Exception
return False
Finally
End Try
End Function
==========CONNECTION================
Public Function OpenImportConnection(Optional ByVal ByPass_Message As Boolean = False) As Boolean        
        Try
            If myImportConnection.State = ConnectionState.Closed Then
                Dim fbConn As New FbConnectionStringBuilder()
                fbConn.Password = Password
                fbConn.UserID = UserID
                fbConn.Pooling = False
                fbConn.Role = "sysadmin"

                fbConn.MaxPoolSize = 20                
                If Is_Local_Connection = True Then
                    fbConn.Database = strLocalDatabase
                Else
                    fbConn.Database = Database_Path
                End If

                fbConn.DataSource = DB_Server
                fbConn.ConnectionLifeTime = 30
                fbConn.ConnectionTimeout = 30
                fbConn.DbCachePages = 100
                'fbConn.ContextConnection = True
                fbConn.Charset = "UTF8"
                fbConn.ServerType = ServerType
                fbConn.Dialect = 3

                'fbConn.IsolationLevel = IsolationLevel.RepeatableRead
                If Val(strPort) = 0 Then
                    strPort = 3050
                End If
                fbConn.Port = strPort

                myImportConnection.ConnectionString = fbConn.ToString
                If fbConn IsNot Nothing Then
                    fbConn = Nothing
                End If
                myImportConnection.Open()

            End If
            Return True
        Catch ex As Exception
            If ByPass_Message = False Then
                MessageBox.Show("Path is not valid...Please,check data direcotry setting...!" & vbCrLf & vbCrLf & _
                ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
            Return False
        End Try
    End Function


On Tuesday, February 24, 2015 12:59 PM, "Svein Erling Tysvær svein.erling.tysvaer@... [firebird-support]" <firebird-support@yahoogroups.com> wrote:


 
Sukhen wrote:
>>>update never fails but it fails in insert query. loop run in a single connection with a new datarows in same query.

I answered:
>>I think it is about time to show us some code (not all). Could it be that another transaction not visible to your current
>>transaction has inserted a row that creates a lock conflict with your insert and that your code repeatedly tries (and fails)
>>to insert? Or that it is a real deadlock, where one transaction first successfully updates record A, then fails on record B,
>>whereas another transaction has successfully updated record B and fails on record A?
>>
>>Typically, there would be no problems on INSERT, whereas UPDATE could create problems. Getting the problem on INSERT, I'd say
>>indicates some kind of lock conflict which means that you have some UNIQUE field or constraint or don't use generators to
>>populate your primary key.

Sukhen answered:
>is it could be any buffersize/memmroy issue. please, suggest if it is how to check it in firebird
>please, somebody help me out

I didn't answer because I know nothing about buffersize/memory issues with Firebird (other than knowing that buffersize/memory very rarely causes problems with Firebird itself, don't know about the components you use in VB to connect to Firebird).

Normally, INSERTs would never fail. When they fail, that indicate some duplicate in a unique index or constraint or primary key (though that ought to give an error rather than be time consuming). Or it could possibly be an infinite loop. INSERTs can be slow, depending on complexity. Show us some code, I'm particularly interested in the INSERT that fails and the table definitions for the tables that the INSERT refers to.

Set