cx_Oracle string problems...

[MooMaster]

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!

 

[tooper]

Looks like a space is missing before VALUES keyword ?

 

[MooMaster]

Lol, that was a copy paste error into the post on my part...but the
problem has been fixed. Turns out that there was a string.replace call
somewhere else in the code that replaced all single quotes with empty
strings, which thus caused the singe quotes to disappear! Whoops!

Thanks for the look, though

 

[Carsten Haese]

Lol, that was a copy paste error into the post on my part...but the
problem has been fixed. Turns out that there was a string.replace call
somewhere else in the code that replaced all single quotes with empty
strings, which thus caused the singe quotes to disappear! Whoops!

Please do yourself a favor and make use of the fact that Python's
database API allows parametrized queries. Here's a sketch of what that
might look like:

sql = 'INSERT INTO prime.utwsLOT VALUES kID, etc...)'
curse = self.connection.cursor()
curse.execute(sql, kID=self.kID, etc...)

Of course, "etc..." would have to be replaced with additional
placeholders and parameter values for all the columns in utwsLOT.

By using parametrized queries, you don't have to worry about any of the
supplied values requiring special treatment due to any quotation marks
or apostrophes that might they might contain.

Hope this helps,

Carsten.

 

[Carsten Haese]

By using parametrized queries, you don't have to worry about any of the
supplied values requiring special treatment due to any quotation marks
or apostrophes that might they might contain.

Add grammar corrections to taste.

-Carsten