[PyQt] QSqlQuery / Firebird: alternating two prepared queries

[Sibylle Koczian]

Hello,

the script below should take the strings in the list newData and put the
contents into two different tables of the Firebird example database
"employee.fdb". Strings starting with "C" belong to the "customer" table and
the insert statement returns the newly created cust_no (new in firebird 2.0).
Strings starting with "O" belong to table "sales" and their content is 
combined with the last new cust_no to form a new record.

In the present form, with all "transaction()" and "commit()" calls commented
out, I get the DatabaseError "insOrder deadlock Unable to execute query". Same
error if I uncomment the calls labeled "Transaction use 1". This probably
shouldn't work because the documentation says "When using transactions you 
must start the transaction before you create your query." - but it would be 
the sensible way to do the inserts.

If I uncomment instead the calls labeled "Transaction use 2", then I get 
another error message from the second call to insCustomer: "insCustomer/exec 
The cursor identified in a FETCH or CLOSE statement is not open. Unable to 
close statement"

In all the cases no new record gets into the database. What is wrong? Of
course, in this very small example I could first put all the "C" records in,
collect the new cust_no values in a list and process them together with 
the "O" records. But what if this is no tiny example list, but a huge file?

Thanks for any shove in the right direction,
Sibylle


import sys
from PyQt4.QtCore import *
from PyQt4.QtSql import *

newData = ['C;Moritz;Germany', 'O;V08A0001;141;520.10', 
           'C;Capitalism Kill & Destroy Ltd.;England', 'O;V08A0002;6;7200', 
           'C;Camorra Corp.;Italy', 'O;V08A0003;121;240', 
           'C;Adam & Eve;Austria', 'O;V08A0004;141;3500', 
           'C;Toblerone Inc.;Switzerland', 'O;V08A0005;141;689.50']

class DatabaseError(Exception):
    pass

def employeeConn(user='sysdba', passwd='masterkey'):
    db = QSqlDatabase.addDatabase('QIBASE')
    db.setHostName('localhost')
    db.setConnectOptions('ISC_DPB_LC_CTYPE=ISO8859_1')
    db.setDatabaseName('Employee')
    db.setUserName(user)
    db.setPassword(passwd)
    ok = db.open()
    msg = (db.lastError().text() if not ok else '')
    return (ok, msg)

def insCustomer(custdata, query):
    (idchar, custname,  custcountry) = custdata.split(';')
    custno_var = QVariant()
    query.bindValue(':customer', QVariant(custname))
    query.bindValue(':country', QVariant(custcountry))
    if not query.exec_():
        raise DatabaseError, 'insCustomer/exec %s' % query.lastError().text()
    if query.first():
        custno_var = query.value(0)
    else:
        raise DatabaseError, 'insCustomer/first %s' % query.lastError().text()
    return custno_var
    
def insOrder(custno_var, orderdata, query):
    (idchar, orderno, salesrep, total) = orderdata.split(';')
    query.bindValue(':po_number', QVariant(orderno))
    query.bindValue(':cust_no', custno_var)
    query.bindValue(':sales_rep', QVariant(salesrep))
    query.bindValue(':total_value', QVariant(total))
    if not query.exec_():
        raise DatabaseError, 'insOrder %s' % query.lastError().text()
   
def main(args):
    app = QCoreApplication(args)
    (ok, msg) = employeeConn()
    if not ok:
        print msg
        sys.exit(1)
    try:
        # Transaction use 2
#        QSqlDatabase.database().transaction()
        custQuery = QSqlQuery()
        custQuery.prepare('INSERT INTO CUSTOMER (customer, country) '
                          'VALUES (:customer, :country) RETURNING cust_no')
        orderQuery = QSqlQuery()
        orderQuery.prepare('INSERT INTO SALES (po_number, cust_no, 
sales_rep, '
                           'total_value) VALUES (:po_number, :cust_no, '
                           ':sales_rep, ':total_value)')
        new_custno = QVariant()
        for data in newData:
            # Transaction use 1
#            QSqlDatabase.database().transaction()
            if data.startswith('C'):
                new_custno = insCustomer(data, custQuery)
            elif data.startswith('O'):
                insOrder(new_custno, data, orderQuery)
            # Transaction use 1
#            QSqlDatabase.database().commit()
            print data
        # Transaction use 2
#        QSqlDatabase.database().commit()
    except DatabaseError, e:
        print e
    finally:
        QSqlDatabase.database().close()
    print 'Ready.'
    
if __name__ == '__main__':
    main(sys.argv)

-- 
Dr. Sibylle Koczian