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?
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.