[egenix-users] egenix zopeda 2.0.2 and execute parameters

[Andrew Altepeter]

Hello,

Thank you for your quick response.  I forgot that 2.0.2 was compiled
against unixodbc 2.3.0.  That's the problem.

Thanks,
Andy

On Fri, Mar 2, 2012 at 9:05 AM, M.-A. Lemburg <mal at egenix.com> wrote:

> 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/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: /mailman-archives/egenix-users/attachments/20120302/9f4dd5bf/attachment.htm