Does it work with Oracle at all?

[jacek99]

I cannot get variable binding to work with Oracle (cx_oracle) in 1.2 RC1 at all.
Every single attempt to do an insert ends with an Oracle error.

E.g.

def insert_unit(unit_id,unit_name):
    pk = central.query_for_int("SELECT S_UNIT.NEXTVAL FROM DUAL")
    central.update("INSERT INTO T_UNIT (F_UNIT_PK, F_UNIT_ID, F_NAME) VALUES (?, ?, ?)",(pk,unit_id,unit_name))
    return pk

Running this in a unit test

    def testUnitInsert(self):
        insert_unit(100, "Unit 100")

results in :

======================================================================
ERROR: testUnitInsert (msdp.test.test.Test)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/jacekf/src/msdp-server/msdp-server/lib/common-test/src/main/python/msdp/test/test.py", line 19, in testUnitInsert
    insert_unit(100, "Unit 100")
  File "/home/jacekf/src/msdp-server/msdp-server/lib/common-test/src/main/python/msdp/test/central_facade.py", line 13, in insert_unit
    central.update("INSERT INTO T_UNIT (F_UNIT_PK, F_UNIT_ID, F_NAME) VALUES (?, ?, ?)",(pk,unit_id,unit_name))
  File "/usr/local/lib/python2.6/dist-packages/springpython-1.2.0.RC1-py2.6.egg/springpython/database/core.py", line 182, in update
    return self.execute(sql_statement, args)
  File "/usr/local/lib/python2.6/dist-packages/springpython-1.2.0.RC1-py2.6.egg/springpython/database/core.py", line 80, in execute
    raise DataAccessException(error)
DataAccessException: ORA-01036: illegal variable name/number

I get this across all tables I've tried.

[jacek99]

Queries with binding do not seem to work either:

def get_user_pk(name):
    return central.query_for_int("SELECT F_USER_PK FROM T_USER WHERE F_USERNAME = ?",(name,))

test

    def testUserQuery(self):
        print get_user_pk(u"admin")

results in:

======================================================================
ERROR: testUserQuery (msdp.test.test.Test)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/jacekf/src/msdp-server/msdp-server/lib/common-test/src/main/python/msdp/test/test.py", line 22, in testUserQuery
    print get_user_pk(u"admin")
  File "/home/jacekf/src/msdp-server/msdp-server/lib/common-test/src/main/python/msdp/test/central_facade.py", line 23, in get_user_pk
    return central.query_for_int("SELECT F_USER_PK FROM T_USER WHERE F_USERNAME = %s",(name,))
  File "/usr/local/lib/python2.6/dist-packages/springpython-1.2.0.RC1-py2.6.egg/springpython/database/core.py", line 140, in query_for_int
    return self.query_for_object(sql_query, args, types.IntType)
  File "/usr/local/lib/python2.6/dist-packages/springpython-1.2.0.RC1-py2.6.egg/springpython/database/core.py", line 157, in query_for_object
    results = self.query_for_list(sql_query, args)
  File "/usr/local/lib/python2.6/dist-packages/springpython-1.2.0.RC1-py2.6.egg/springpython/database/core.py", line 98, in query_for_list
    results, metadata = self.__query_for_list(sql_query, args)
  File "/usr/local/lib/python2.6/dist-packages/springpython-1.2.0.RC1-py2.6.egg/springpython/database/core.py", line 106, in __query_for_list
    raise InvalidArgumentType(type(args), self.connection_factory.acceptable_types)
InvalidArgumentType

[gregturn]

I don't have Oracle, so I haven't been able to test this feature out. Everything I know and that has been contributed to the project for cx_ora support can be read here https://jira.springsource.org/browse...INGPYTHONPY-45

[jacek99]

Would you be able to test it out with Oracle XE?
It's free and can be easily installed via RPMs or DEBs from the Oracle site.

We have our Django models working with it successfully, so it's definitely installed properly.

[jacek99]

Looks like cx_Oracle supports named arguments only...

cursor.execute("INSERT INTO User_Tables(login,first_name,last_name,age,date_of_birth) 
                VALUES (:login,:first,:last,:age,to_date(:dob,'YYYY-MM-DD HH24:MI:SS'))",
               {
                'login' : 'some_user_login',
                'first' : 'some_first_name',
                'last' : 'some_last_name,
                'age' : 42,
                'dob' : '1970-01-01 23:52:00',
}

[gregturn]

You have uncovered a bug in Spring Python.

The query operations check the input arguments and make sure they are in formats acceptable to the connection factory. You, though, are using update, which calls execute under the hood, which does NOT perform this argument check.

It should take little effort to patch this up so that a proper error message would have been generated, tipping you off that Oracle requires a dictionary instead of a tuple. I will probably put this patch into 1.1 as well as 1.2, since some people may be using 1.1 with either the book or with jython.

[gregturn]

This issue has been patched in 1.1.1, and 1.2.1. These versions haven't been released yet in distribution form, but you can checkout the source code to get it.

You can read more at https://jira.springsource.org/browse...NGPYTHONPY-159