1

I'm doing some calcultations by ArcSDESQLExecute. The idea is to make some part localy and at the end copy table/featureclass to SDE and make some extra operations by sql. I want to put all sql to transaction,but it seems like it does not work as a transaction. Here is example of code, which works wrong.

sde_connector = arcpy.ArcSDESQLExecute("path to connector")
sql = [ u"ALTER TABLE {tab_mc} ADD bez_UR number(38,8)".format(tab_mc = mc_results),
        u"UPDATE TP_MC_STAT set test_col = 1000",
        # u"DROP TABLE TP_MC_STAT",     ##WHEN TABLE IS NOT DELETED, NEXT SQL RAISE AN EXCEPTION
        u"CREATE TABLE TP_MC_STAT (test_col int)",
        u"UPDATE TP_MC_STAT set test_col = 4"]
sde_connector.startTransaction()
for row in sql:
    try:
        sde_connector.execute(row)
    except Exception as err:
        raise Exception(row," ...>>>... ",err.message)
sde_connector.commitTransaction()

When I run full code without commenting "DROP TABLE" everything is ok and column test_col has value 4. But When I comment DROP statement I except no sql is gonna be commited and test_col has still value 4. But after raising exception there is 1000 in test_col - previous sql were commited. How is this possible?

  • 2
    Imho, you are misusing arcsdesqlexecute. Just get a proper package for working with SQL Server, pymssql, and submit your call in one shot. No need to use the for loop for iteration! – Alex Tereshenkov Jul 27 '17 at 18:18
  • There is only one reason why I'm using arcpy tool - package management at side of my colleagues. I'm not able to to manage their packages, but everybody has arcpy... So I'm not sure if this solution works for us. – betka Aug 2 '17 at 5:58
  • I hear you. I don't get the results committed if an exception is raised, I've tested running your code in ArcGIS Desktop 10.5. Can you please try running just these three rows (leave all other code as is): u"UPDATE TP_MC_STAT set test_col = 4", u"UPDATE TP_MC_STAT set test_col = ( 10 / 0)". No rows should be updated to value 4. – Alex Tereshenkov Aug 2 '17 at 7:56
  • That is strange. When I use only yours sql it works clearly, no problem it absolutely respect transaction. I started to celebrate, but when I add alter table statement like this[ u"UPDATE TP_MC_STAT set z = 4",u"alter table tp_mc_stat add test number(38,8)", u"UPDATE TP_MC_STAT set z = ( 10/0)"], then no transaction is respected. Exception is raised, but 1st and 2nd sql is commited. – betka Aug 2 '17 at 11:29
  • My mistake DDL are not transactional in ORACLE ... found here. Thanks for help – betka Aug 2 '17 at 11:52
2

There is a note in this documentation stating:

A commit may also occur when the connection to ArcSDE it terminated (check specific DBMS documentation to see how each DBMS deals with a disconnect while in a transaction).

If the exception causes the connection to terminate, your DBMS may commit the transaction. Try calling rollbackTransaction() in the catch block.

  • I put it there but the result is the same. – betka Jul 27 '17 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.