passing variables to Oracle via mxODBC

[ari_deja at ivritype.com]

I'm having an interesting problem talking with an oracle database via
ODBC,
using the mxDateTime and mxODBC modules.

The problem original appeared as below, but I seem to have since
generalized it to "anything that is a variable that is in the
cursor.execute statement doesn't appear to be parsed prior to being
passed to Oracle."

For instance:

The problem comes in trying to insert the current date/time from the
system clock, into the record. First, I use the "now" constructor to
create what I think is a string with the current date, and then print
it out to check:

        now=DateTime.now()
        print now

That part works great. But then I try to use Oracle's
(8i) "TO_DATE" function to convert this to something that Oracle
understands:

        cursor.execute("""
                insert (mystring,mydate) into bar.test values
('foo',TO_DATE('now','YYYY-MM-DD HH:MI:SS'))
                """)
This, invariably gives me the result:
------------------------
2000-10-02 14:32:27.09
Traceback (innermost last):
  File "D:\PROGRA~1\Python\Tools\idle\ScriptBinding.py", line 131, in
run_module_event
    execfile(filename, mod.__dict__)
  File "D:\Program Files\Python\junk\dbtest6.py", line 20, in ?
    crsr.execute("""
OperationalError: ('S1000', 1858, '[Oracle][ODBC][Ora]ORA-01858: a
non-numeric character was found where a numeric was expected\012', 3205)
---------------------------------------

Which suggests some sort of typing error, or that the variable isn't
being parsed properly. This sort of makes sense--at what point, for
instance, is "now" converted to the value set by DateTime()? If not
converted prior to the function being sent to Oracle, how would TO_DATE
know what to do with it? And, in fact, if I try to insert a record
using something like

bar = 'string1'
rab = 'string2'
 ... (insert into foo values (bar,rab)...

I get an error. If I put quotes around 'bar' and 'rab', I insert them
as literals into Oracle, which is NOT the idea at all ;-).

Help?


Sent via Deja.com http://www.deja.com/
Before you buy.