Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Problem: Commit no longer works
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/[email protected]'
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
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/[email protected]'
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
Answers
-
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 -
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 -
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 -
No probs. Thanks for letting us know you'd solved it.
This discussion has been closed.