We are facing an issue where we are attempting to batch sql entries as a transaction. The script loops through a list of sql queries and attempts to execute them, however when the transaction is committed only the last query in the list is actually committed to the database.
try:
tx_id = system.db.beginTransaction(timeout = 10000)
for sql in sql_lines:
print sql
logger.info(sql)
sql.strip()
system.db.runUpdateQuery(sql, database_name, tx_id)
#Commit all the transactions and close the connection
system.db.commitTransaction(tx_id)
system.db.closeTransaction(tx_id)
except Exception as e:
system.db.rollbackTransaction(tx_id)
system.db.closeTransaction(tx_id)
logger.error(traceback.format_exc())
logger.error("SQL Transaction Failure, check the logs")
Each sql line is a simple insert statement and running them against the database by itself works perfectly fine, it's just the transaction that is messing up.
Might also be important to note that this script is in a gateway message handler.
It's important to note that no exceptions are logged and it says the transaction was successfully committed. In the logs we can see every sql line from that "logger.info(sql)" statement as well.
Is there some sort of weird syntax error or an issue with how I am using transactions?
Thanks for your reply, and you bring up very good points. The SQL queries we are using are completely static. Additionally, we aren't at the point with security just yet since we are just testing right now but they will 100% be encoded. Everything is on a completely isolated network so figured it would be fine for testing dummy values at the moment.
As for the return values it is also odd, I've added logging to capture each return value in the for loop but I still face the issue where it will only display the very last entry (as a 1 since it is an insert and affects a single row).
So basically the logs will show something from bottom to top like...
