Thread: bug report - Transaction Rollback not successful
Dear sir,
This is to report a suspected bug in psqlODBC 09.01.0100 when used with Microsoft Visual Basic 6 with ADODB
This happens when a transaction with INSERT INTO - SELECT combination queries rolled back on an error. The queries before the the occurance of the error ( Rollback is initiated) found committed when some other transaction is committed. ie. the querries in the transaction block upto the rollback statement is committed when some other transaction block is committed (Eg. Sale is saved, Order is Edited etc.).
In my case the bug noticed in the following function. This is for converting a sale order to a sale.
--------------------------------------------------------------------------------------------------
--
Public Function ConvertOrder(OrdNo As Long) As Boolean
On Error GoTo err_here
Dim IsStartTrans as boolean
IsStartTrans = False
ConvertOrder = False
Dim RSOrd As New ADODB.Recordset
SqlStr = "Select * from orders where firm_id=" & PubFirmId & " AND OrdNumber=" & OrdNo & " ;"
RSOrd.Open SqlStr, dbx
If RSOrd.EOF Then
MsgBox "Order No. " & OrdNo & " Not Available for Conversion", vbInformation
Exit Function
End If
RSOrd.MoveFirst
If RSOrd!closed = 1 Then
'Closed Order
Exit Function
End If
Dim SaleNo As Long
SaleNo = GetNewSaleNumber ' THIS IS A FUNCTION TO GET THE NEXT SALE BILL NUMBER
IsStartTrans = True
dbx.BeginTrans
'SQL No.1
SqlStr = "INSERT INTO sale( " & _
"firm_id, invnumber, invdate, salesmancode, cust_number, cust_name, " & _
"routecode, pricegroup, totalamount, totaldiff, discount, paid," & _
"journalrefno, isrm, orderno, memo, lastbalance) " & _
"SELECT firm_id, " & SaleNo & " as invnumber,'" & Format(Now(), "yyyy-mm-dd hh:mm:ss") & "', salesmancode, cust_number, " & _
"cust_name, routecode, pricegroup, totalamount, totaldiff, discount, " & _
"paid , 0, IsRM, ordnumber, Memo, get_head_current_balance(" & PubFirmId & ", cust_number) " & _
"FROM orders where firm_id=" & PubFirmId & " AND OrdNumber=" & OrdNo & " ; "
dbexecute SqlStr 'dbexecute IS A FUNCTION TO EXECUTE THE QUERRY SqlStr
'SQL No.2
SqlStr = "INSERT INTO saleit(" & _
"firm_id, invnumber, item_code, qty, rate, cost, slno) " & _
"SELECT firm_id, " & SaleNo & " as invnumber, item_code, qty, rate, cost, slno " & _
"FROM orderit where firm_id=" & PubFirmId & " AND OrdNumber=" & OrdNo & " ;"
dbexecute SqlStr
'SQL No.3
SqlStr = "select add_item_stock(" & PubFirmId & ",item_code, (0 - qty)::real ) FROM orderit where firm_id=" & PubFirmId & " AND OrdNumber=" & OrdNo & " ;"
dbexecute SqlStr
'add_item_stock is a function to modify the stock in the items table
'SQL No.4
SqlStr = "UPDATE orderit SET SoldQty = Qty WHERE firm_id=" & PubFirmId & " AND OrdNumber=" & OrdNo & " ;"
dbexecute SqlStr
SQL No.5
SqlStr = "update orders set Closed=true where firm_id=" & PubFirmId & " AND OrdNumber=" & OrdNo
dbexecute SqlStr
dbx.CommitTrans
IsStartTrans = False
ConvertOrder = True
Exit Function
err_here:
if IsStartTrans then
dbx.RollbackTrans
MsgBox "Order No. " & OrdNo & " Could not Convert" & vbCrLf & Err.Description
Else
Msgbox "Error : " & Err.Description
End if
End Function
--------------------------------------------------------------------------------------------------------------------------------------------------
Query No:1 and 2 are INSERT INTO - SELECT combination queries for taking some data (one row ) from order table and inserting to Sale table & Orderit to SaleIt Table
Query No.3 is for updating the stock. if the stock goes below zero, the function triggers an error and the error handler is executed and the transaction is rolled back. When I used ODBC 09.01.0100 the transaction is not rolled back completely. The SQL No.1 &2 are held until any other program segment tries to commit some other transaction outside this function and new rows are found added in Sale and SaleIt tables.
This is not happening when PSQLODBC Version 8.01.02.00 is used.
I hope this report will help improving the PSQLODBC project.
For any further clarification, kindly feel free to call me on +91 9447033489
Thanks & Regards
K.Muhammed Unais
System Administrator,
Kerala State Electricity Board,
Kozhikode, Kerala, india
---------------------------------------------------------------------------------------------------------------------------------------
If a man is called to be a streetsweeper, he should sweep streets even as Michelangelo painted, or Beethoven composed music, or Shakespeare wrote poetry. He should sweep streets so well that all the hosts of heaven and earth will pause to say, here lived a great streetsweeper who did his job well.
If a man is called to be a streetsweeper, he should sweep streets even as Michelangelo painted, or Beethoven composed music, or Shakespeare wrote poetry. He should sweep streets so well that all the hosts of heaven and earth will pause to say, here lived a great streetsweeper who did his job well.
- Martin Luther King, Jr.