cx_Oracle string problems...

MooMaster ntv1534 at gmail.com
Mon Feb 13 12:12:47 EST 2006


After some google searching on the forum I couldn't find any topics
that seemed to relate exactly to my problem, so hopefully someone can
help me out...

I'm running python 2.4.1 on a local Win2K system and doing some
work-related development on an AIX box, and I'm running into a problem
using cx_Oracle to add some information to an Oracle 9.2 table.

I have a table_title defined as a VARCHAR2 data type, and when I'm
trying to retrieve the data from a GUI I do so like this:

tableTitle = self.window.TitleCB.GetValue()

After retrieving several pieces of information, including mostly
integers, like this from my display I create a string that represents a
valid SQL statement and then use cx_Oracle 4.1 to connect to the Oracle
db on the server and write the data to the table, like so:

###########snipet#########################
sql = str('INSERT INTO prime.utwsLOT VALUES (' + str(self.kID) + ',' +
string)
curse = self.connection.cursor()
curse.execute(sql)
###########/snipet#########################

which generates the following string in Windows
INSERT INTO prime.utwsLOT VALUES (0,400,  'goo ', 0,  3,   2,  1)

This sucessfully connects to the db and writes the data from my Windows
box to the AIX box. One of the requirements of the application I'm
writing, however, is that it be installed and run from the AIX box
itself. When I try to run my code on the AIX box, however, THIS string
is generated, and the following happens:

INSERT INTO prime.utwsLOTVALUES(0, 400, "goo", 0, 3, 2, 1)
Traceback (most recent call last):
  File "treedisp.py", line 147, in Export
    MyExport.LineExporter()
  File "/users/z04857/nickUT/OracleExporter.py", line 118, in
LineExporter
    self.prepareSQL(toExport, "lines")
  File "/users/z04857/nickUT/OracleExporter.py", line 249, in
prepareSQL
    self.doExport(sql)
  File "/users/z04857/nickUT/OracleExporter.py", line 261, in doExport
    curse.execute(string)
cx_Oracle.DatabaseError: ORA-00984: column not allowed here

In the words of Metal Gear Solid:2: FISSION MAILED...

Notice the double quotes in the above sql string. I thought this might
be a problem, so I ran my SQLPlus client with the same string and got
the same error. The double quotes is definitely a no go! Being an
enterprising programmer, I figured I would just hardcode the single
quotes into the string to fix the problem, so I did the following:

tableTitle =str("'" + self.window.TitleCB.GetValue() +"'")

...and got the following string was created on the AIX box (as
displayed by a print statement I put in the code):
INSERT INTO prime.utwsLOTVALUES(0, 400, "goo", 0, 3, 2, 1)

uh-oh...Thinking that perhaps an escape sequence would solve the
problem, I made the following modification:
tableTitle =str("\'" + self.window.TitleCB.GetValue() +"\'")

and receieved the following string:
INSERT INTO prime.utwsLOTVALUES(0, 400, "goo", 0, 3, 2, 1)

This is when I heard Claire Redfield's quote from Resident Evil 2, "You
LOSE, big boy!", ring loudly in my ears... No matter what I do, I can't
seem to get the string to be set in single quotes on the AIX box.
*Something* keeps converting the string to double quotes, which in turn
causes the cx_Oracle database write to fail. I have no clue what could
be doing this...Has anyone else ever encountered a similar problem? Can
anyone think of something else I can try?

Sorry for the length, but thanks for the help!




More information about the Python-list mailing list