Search code examples
sqlvb.nettransactionsinformixdatareader

SQL Transaction within DataReader or outside of it? And Function Sequence Error?


Currently my code structure (in VB.NET) is as follows -

Using reader As IfxDataReader = command.ExecuteReader()
    If reader.HasRows Then
        Do While reader.Read()
            Using transaction As IfxTransaction = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted)
                'multiple update statements
                 transaction.Commit()
            End Using
        Loop
    End If
End Using

The reader is reading multiple records and for every record, there are multiple update statements to be run. I figure that it would be better to begin a transaction for each record, and the commit after it is done, move on to the next record and create a new transaction for that, "rinse and repeat".

Everything works fine and is committed to the database, but when the reader checks for more rows after the last record, this peculiar error shows up -

ERROR [HY010][Informix .NET provider] Function sequence error.

After doing some reasearch, the IBM website says that I would have to update to a CSDK 3.5 or higher (http://www-01.ibm.com/support/docview.wss?uid=swg1IC58696). However, to me this seems a bit unnecessary since the code is working fine, it's just throwing that error at the end.

Would it be better to have the transaction OUTSIDE of the reader, and go through all the records in the table and THEN commit all at once? Or is it the most efficient/optimal the way it is now (in other words, going through each record, with all the necessary update statements for that record, and committing one at a time)? Secondly, would the former choice resolve the function sequence error?


Solution

  • Within the code, there was a datareader, and inside the datareader were some update statements. I changed the way the code was structured by separating these functions. First I have it read all the data, and then store into objects. Then, after that was done and closed, I ran the update statements while iterating through each object. That seemed to solve the function sequence error that was coming up.