This discussion is archived
4 Replies Latest reply: Sep 16, 2010 8:46 PM by cj RSS

Problem: Commit no longer works

408518 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    No probs. Thanks for letting us know you'd solved it.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points