Skip site navigation (1) Skip section navigation (2)

bug report - Transaction Rollback not successful

From: Unais Muhammed <kmunais(at)gmail(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: bug report - Transaction Rollback not successful
Date: 2012-01-09 17:53:52
Message-ID: CAHZyxsBg_qRQQ+MAmqOLFkqEY7MtiSbXpNqHMV9wKdb3EvpefA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-odbc
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.

Responses

pgsql-odbc by date

Next:From: Hiroshi InoueDate: 2012-01-10 11:51:20
Subject: Re: bug report - Transaction Rollback not successful
Previous:From: Honza HorakDate: 2012-01-06 08:56:31
Subject: Errors found by static analysis tool

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group