4 Replies Latest reply: Sep 16, 2010 10:46 PM by cj RSS

    Problem: Commit no longer works

    408518
      This is a puzzler - we have many python apps the do inserts and updates to Oracle (10.1.0.4) using cx_oracle 4.4.1 and python 2.5.1 under Windows XP Pro - they all work

      writing a new app I found I could no longer do an insert - to be more specific: I can do an insert or update but the commit that fails!

      after performing a commit, the changes do not seem to be committed to the db and do not show up in another oracle session. They do not persist.

      this behavior happens on 3 separate computers, against 3 separate oracle instances, yet the previously written py apps are working just fine as are apps written in C and C++.

      I can perform a create table command (DDL), and a select statement (DML) but cannot commit DML changes

      any ideas?

      is there a way to test error or sql execution status from within python similar to sqlca.sqlcode in C?

      here is sample code:

      insert_test.py
      //////////////////////////////////////////////////////////////////

      import cx_Oracle

      ## params
      CX_LOGON = 'myuser/mypword@myserver'
      ORACLE_CHUNK=1000

      ## open 2 separate connections (oracle sessions)
      dbc1 = cx_Oracle.Connection(CX_LOGON)
      dbc2 = cx_Oracle.connect(CX_LOGON)

      ## create 2 separate cursors
      crsr1 = dbc1.cursor()
      crsr1.arraysize = ORACLE_CHUNK
      crsr2 = dbc2.cursor()
      crsr2.arraysize = ORACLE_CHUNK

      ## result lists
      result1=[]
      count1=[]
      count2=[]

      ## our sql statements
      sql_drop = "DROP TABLE PYTEST"
      sql_create = "CREATE TABLE PYTEST (name varchar2(20))"
      sql_trunc = "TRUNCATE TABLE PYTEST"
      sql_select = "SELECT name FROM PYTEST"
      sql_insert1 = "INSERT INTO PYTEST VALUES ('xxx')"
      sql_insert2 = "INSERT INTO PYTEST VALUES ('yyy')"
      sql_count = "SELECT COUNT(*) FROM PYTEST"
      sql_update = "UPDATE PYTEST SET NAME = 'zzz' WHERE NAME = 'yyy'"

      ## first create the table - we only do this once
      ##crsr1.execute(sql_drop)
      ##crsr1.execute(sql_create)

      ## clear out the table
      crsr1.execute(sql_trunc)

      ## check how many rows
      crsr1.execute(sql_count)
      count1 = crsr1.fetchall()
      if(len(count1)>0):
      nrows = int(count1[0][0])
      else:
      nrows = 0
      print "before insert we have ", nrows, " rows"

      ## now insert a row using same connection
      crsr1.execute(sql_insert1)
      dbc1.commit;

      ## check how many rows
      crsr1.execute(sql_count)
      count1 = crsr1.fetchall()
      if(len(count1)>0):
      nrows = int(count1[0][0])
      else:
      nrows = 0
      print "after insert we have ", nrows, " rows using same connection"

      ## now insert another row using same connection
      crsr1.execute(sql_insert2)
      dbc1.commit;

      ## check how many rows
      crsr1.execute(sql_count)
      count1 = crsr1.fetchall()
      if(len(count1)>0):
      nrows = int(count1[0][0])
      else:
      nrows=0
      print "after insert we have ", nrows, " rows using same connection"

      ## get table contents
      crsr1.execute(sql_select)
      result1 = crsr1.fetchall()
      if(nrows>0):
      print "here are the rows: "
      for result in result1:
      name = result[0]
      print " name = ", name


      ## now do an update using same connection
      crsr1.execute(sql_update)
      dbc1.commit;

      ## check how many rows
      crsr1.execute(sql_count)
      count1 = crsr1.fetchall()
      if(len(count1)>0):
      nrows = int(count1[0][0])
      else:
      nrows=0
      print "after update we have ", nrows, " rows using same connection"

      ## get table contents
      crsr1.execute(sql_select)
      result1 = crsr1.fetchall()
      if(nrows>0):
      print "here are the rows after update: "
      for result in result1:
      name = result[0]
      print " name = ", name


      ## now check how many rows using 2nd connection
      crsr2.execute(sql_count)
      count2 = crsr2.fetchall()
      if(len(count2)>0):
      nrows2 = int(count2[0][0])
      else:
      nrows2 = 0
      print "second connection sees ", nrows2, " rows"

      ## lets' try swinging a chicken over our heads and commit both connects again
      dbc1.commit
      dbc2.commit

      print "end - after dual commits - check row count externally with sql*plus"

      /////////////////////////////////////


      Mike
        • 1. Re: Problem: Commit no longer works
          408518
          sorry - forgot to include the output from the insert_test.py app

          before insert we have 0 rows
          after insert we have 1 rows using same connection
          after insert we have 2 rows using same connection
          here are the rows:
          name = xxx
          name = yyy
          after update we have 2 rows using same connection
          here are the rows after update:
          name = xxx
          name = zzz
          second connection sees 0 rows
          end - after dual commits - check row count externally with sql*plus

          here's the sql*plus output after running the above app:

          SQL> select * from pytest;

          no rows selected


          mike

          Edited by: mstenzler on Sep 14, 2010 6:09 AM

          Edited by: mstenzler on Sep 14, 2010 6:10 AM
          • 2. Re: Problem: Commit no longer works
            408518
            Solved the problem - it's my own fault - I left off the () after the commit.

            dbc.commit

            should have been

            dbc.commit()


            somehow I missed it
            • 3. Re: Problem: Commit no longer works
              408518
              too much C, C++ and Objective C makes Mike a bad python coder..

              looking at the py source closer I see I even ended the commit with a ";"

              :-)

              silly mistake - spent several hours banging my head against the wall trying to figure out what happened.

              Looked at previous code but simply didn't catch the missing parens..

              Mike
              • 4. Re: Problem: Commit no longer works
                cj
                No probs. Thanks for letting us know you'd solved it.