[cx-oracle-users] cursor.execute() then conn.commit however SQL update not committed in database

[Wong W. Meng-R. <r3...@fr...>]

Hello there,

I continue to work on my application migration from python 1.5.2 to 2.7.1, from using oracledb to cx_Oracle in a python RMI framework.

I am troubleshooting this issue whereby my API is updating userLastName of a given userId. I am doing cursor.execute("update......") first then connection.commit(). However, the data in the database doesn't seem changed, which means the "commit" did not get through. There is no error generated when ".commit()" is executed though.

I am thinking my API might be using different connection objects to do the cursor.execute() and connection.commit(), respectively however the likelihood is low, as the old code using oracledb was working well.

Does anyone has any idea how this can happen?


Source code.....

    def _processDB( self, conn_type, conn, cursor, db_type, db_msg ):
      """Internal method to execute the database transaction given."""

      reply_error = None
      try:
        print ">>>> _processDB : conn_type=%s, db_type=%s, db_msg=%s"%(conn_type, db_type, db_msg)
        if db_type == STMT_EXEC:
          msg, arg = db_msg
          if arg:
              reply_value = cursor.execute( msg, arg )
          else:
              reply_value = cursor.execute( msg )
          if reply_value == None:  # none-select statement
            if conn_type == GENERAL_CONN:
              conn.commit()
          else:
            reply_value = cursor.fetchall()

        elif db_type == STMT_EXECL:
          msg, arg = db_msg
          if arg:
              reply_value = cursor.execute( msg, arg )
          else:
              reply_value = cursor.execute( msg )
          if reply_value == None:  # select statement
            reply_value = cursor.fetchall()
            # get description
            desc_list = cursor.description
            desc = []
            for i in desc_list:
              desc.append( i[0] )
            reply_value.insert( 0, tuple(desc) )
          else:
            if conn_type == GENERAL_CONN:
              conn.commit()

        elif db_type == STMT_PROC:
          reply_value = conn.callproc( db_msg )

        elif db_type == STMT_COMMIT:
          print "Hello world"
          reply_value = conn.commit()

        elif db_type == STMT_ROLLBACK:
          reply_value = conn.rollback()


        print ">>>> _processDB : reply_value = %s"%(reply_value,)


Debug Printout

>>>> _processDB : conn_type=1, db_type=exec, db_msg=('select sysdate from sys.dual', ())
>>>> _processDB : reply_value = [(datetime.datetime(2011, 10, 9, 19, 25, 39),)]
>>>> _processDB : conn_type=1, db_type=exec, db_msg=("select 1 from USERS where USER_ID = 'ftcs' and ROWNUM = 1", ())
>>>> _processDB : reply_value = [(1,)]
>>>> _processDB : conn_type=1, db_type=exec, db_msg=('update USERS set USER_MODIFY_USER = :2, USER_MODIFY_DATE = :3, USER_FIRST_NAME = :4 where USER_ID = :1', ['ftcs', 'ftcs', datetime.datetime(2011, 10, 9, 19, 25, 39), 'Steven II'])
>>>> _processDB : reply_value = None
>>>> _processDB : conn_type=1, db_type=commit, db_msg=commit
Hello world
>>>> _processDB : reply_value = None


Regards,
Wah Meng
Genesis Wafermap Support Ticket:
To report a problem: http://dyno.freescale.net/Question/QuestionMain3.asp?location=zmy02&category=&tickettype=6820
To request a service: http://dyno.freescale.net/Question/Questionmain3.asp?location=74&category=2&tickettype=6819
Or if it is related to EWM or DSA: http://dyno.freescale.net/Question/Questionmain3.asp?location=ZMY02&tickettype=6539