[Zope-DB] DCOracle2 OracleDate and parameter sequence problem

[Christopher Jenkins]

Thank you very much for this fix.  I have discovered a couple
of other (related?) issues with strings and executemany().

Suppose you have a table created by

create table t (s char(10));

Then from Python, given a cursor object object con:-

>>> l1=[(None,),("hello",),(None,)]
>>> l2=[("",),("hello",),("",)]
>>> stmt="insert into t values (:1)"
>>> cur.executemany(stmt,l1) 
4

This call inserts three NULL values into the table
(ie. "hello" does not get inserted).  Continuing

>>> cur.executemany(stmt+ " ",l2) # Force reparse
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File
"/home/cjenkin1/oracle/Products/DCOracle2/DCOracle2/DCOracle2.py",
line 921, in executemany
    result = self._cursor.execute(batchend-batchstart)
dco2.DatabaseError: (1480, 'ORA-01480: trailing null missing from STR
bind
value')

Using execute() and iterating over the tuples in l1 and l2
works fine.

Once again I would like to thank you for all your help and
especially for the speed with which you have provided fixes.

Chris


"Matthew T. Kromer" wrote:
> 
> OK, Its basically the same problem as executemany() -- when re-executing
> a prior statement, the statement is not re-prepared. On an initial
> execute, a NULL is bound as a SQLT_STR (string) type, so a subsequent
> execute replacing it with a float (or anything non-string) requires a
> re-prepare.  When I get in to the office I'll put some code in that will
> mark columns that are bound as NULL to trigger a re-prepare.   Note that
> tacking a space on to the end of your statement will serve as a
> workaround, since this will also force a prepare.
> 
> On Monday, October 8, 2001, at 02:58 AM, Christopher Jenkins wrote:
> 
> > "Matthew T. Kromer" wrote:
> >>
> >> Christopher Jenkins wrote:
> >>
> >>> Hi,
> >>>
> >>> I have recently started to use DCOracle2 beta5 (Oracle 9.0.1
> >>> on Sparc Solaris and Python 2.1.1) and have encountered
> >>> some strange problems when using OracleDate objects in
> >>> parameter sequences and either execute() or executemany()
> >>>
> >>
> >> OK, I've fixed the problems you've reported in CVS.
> >>
> >> Ken Mannheimer has directed all of my checkin messages to zope-db, of
> >> which I'm a bit dubious, but my suggestion of "dont send them anywhere"
> >> got shot down when someone complained about them showing up on the
> >> zope-checkins list.
> >>
> >> Howls about the checkin messages to me in private, please...
> >
> > Thank you very much for the prompt response.  This has fixed
> > the problem with OracleDate, but has uncovered a similar
> > problem with float types (despite the comment in my origional
> > message...).
> >
> > If you have a table created by
> >
> > create table t (n float);
> >
> > Then from Python if you have a cursor object cur:-
> >
> >>>> stmt="insert into t values (:1)"
> >>>> cur.execute(stmt, (None,))
> > 4
> >>>> cur.execute(stmt, (1.0,))
> > Traceback (most recent call last):
> >   File "<stdin>", line 1, in ?
> >   File "DCOracle2.py", line 766, in execute
> >     result = self._cursor.execute()
> > dco2.DatabaseError: (1475, 'ORA-01475: must reparse cursor to change
> > bind variable datatype')
> >
> > Note that in this case, the problem only manifests itself when the
> > inserts are performed in this order (None first) which is why I did
> > not pick it up earlier.
> >
> > This problem does not seem to affect executemany().
> >
> > I would very much appreciate your help with this issue.
> >
> > Thanks again for all your help,
> >
> > Chris