[egenix-users] egenix zopeda 2.0.2 and execute parameters

[M.-A. Lemburg]

Andrew Altepeter wrote:
> Greetings,
> 
> I recently upgraded to egenix mxodbc zopeda 2.0.2.  Our zope environment is
> zope 2.12.20 and python 2.6.7, 32-bit RHEL 5 (or opensuse 12.1), unixodbc
> 2.2.12 and oracle instantclient 10.2.0.5 (installed via rpm)
> 
> We have some python code which gets an 'eGenix mxODBC Database Connection'
> (the zope object), then gets a connection object via get_connection().
> 
> With the connection object, we can call the 'execute' method, and pass in
> parameters, e.g. to select rows in table `table` where `column` matches 'A':
> 
> conn = db.get_connection()
> conn.execute('select * from table where column=?', ('A',))
> 
> In zopeda 2.2, this always returns an empty rowset.
> 
> If I rewrite the query to be:
> conn.execute("select * from table where column='A' ")
> Then it works.
> 
> Another query, like:
> conn.execute('insert into table (W,X,Y,Z) values(?,?,?,?)',
> ('A','B','C','D'))
> raises the following error:
> 2012-02-28 16:02:36,198 INFO [root] eGenix mxODBC Zope DA:
> <Products.mxODBCZopeDA.ZopeDA.DatabaseConnection "DSN=PROD8" thread
> -1258087568/-1258087568 at 0xc3d83cc> received an OperationalError
> ('HY000', 12899, '[unixODBC][Oracle][ODBC][Ora]ORA-12899: value too large
> for column "schema"."table"."X" (actual: 4, maximum: 1)\n', 8396); trying
> to reconnect and replay transaction
> 
> If I revert back to zopeda 2.0.1 the first query, using a params list,
> works (and of course the second query works too).
> 
> It seems like zopeda2.0.2 isn't passing the parameters down to the odbc
> level correctly.  The error above seems to indicate that the full params
> list is being passed to the first '?' param.

We checked this using the following external method, but
cannot reproduce the problem:

test_execute.py:
----------------
#
# External Method Module test_execute
#
def test(self, request=None, form=None):

    """ Test conn.execute() with parameters.

        Uses the Zope object "oracle" (an eGenix mxODBC Zope DA Connection
        object) which must be accessible in the current context.

    """
    conn = self.oracle.get_connection()
    try:
        conn.execute('drop table test_zopeda')
    except conn.DatabasePackage.ProgrammingError:
        pass
    conn.execute('create table test_zopeda '
                 '(id int, col1 varchar(10), col2 varchar(10))')
    conn.execute('insert into test_zopeda values (?,?,?)',
                 (1, 'a', 'd'))
    conn.execute('insert into test_zopeda values (?,?,?)',
                 (2, 'abc', 'def'))
    rs = conn.execute('select * from test_zopeda where col1=?', ('abc',))
    conn.execute('drop table test_zopeda')
    print 'Results:', rs
    return 'Works. See log for results.'

This gives:

Results: ([{'width': 38, 'null': 1, 'type': 'i', 'name': 'ID'}, {'width': 10, 'null': 1, 'type':
's', 'name': 'COL1'}, {'width': 10, 'null': 1, 'type': 's', 'name': 'COL2'}], [(2, 'abc', 'def')])

in the log.

Note that mxODBC Zope DA was compiled against unixODBC 2.3.0.
Version 2.2.12 is not supported anymore, since it uses a
different SQLLEN parameter type in its APIs.

Could you recheck with unixODBC 2.3.0 ?

Thanks,
-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Mar 02 2012)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2012-02-13: Released eGenix pyOpenSSL 0.13        http://egenix.com/go26
2012-02-09: Released mxODBC.Zope.DA 2.0.2         http://egenix.com/go25
2012-02-06: Released eGenix mx Base 3.2.3         http://egenix.com/go24

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/