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.
- Martin Luther King, Jr.