Announcement Announcement Module
Collapse
No announcement yet.
Does it work with Oracle at all? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Does it work with Oracle at all?

    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.

    Code:
    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

    Code:
        def testUnitInsert(self):
            insert_unit(100, "Unit 100")
    results in :

    Code:
    ======================================================================
    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.

  • #2
    Queries with binding do not seem to work either:

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

    Code:
        def testUserQuery(self):
            print get_user_pk(u"admin")
    results in:

    Code:
    ======================================================================
    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

    Comment


    • #3
      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

      Comment


      • #4
        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.

        Comment


        • #5
          Looks like cx_Oracle supports named arguments only...

          Code:
          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',
          }

          Comment


          • #6
            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.

            Comment


            • #7
              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

              Comment

              Working...
              X