Re: [cx-oracle-users] Another oracledb to cxOracle Migration Issue: Parsing to and from Column in DATE type

[Amaury F. d'A. <ama...@gm...>]

Hi,

2011/10/11 Wong Wah Meng-R32813 <r3...@fr...>:
> Yesterday I posted the issue about returning data to client application
> written in python 1.5.2, whereby the client application is not able to
> recognize data in date form which is wrapped with datetime type.
>
> I applied some code in the server side, written in python 2.7.2 using
> cx_Oracle, which converts every date data returned from a cursor object to
a
> tuple format date, before returning it to the client. This at least works
> for me which at the moment I can’t upgrade my clients to the same version
of
> python my server has.
>
> Now I need to solve another issue arising from SQL update/insert/delete
> statements whereby the passing in data should be in datetime format.
> Previously this works well in my server application written in python
1.5.2
> using oracledb. Execute() can recognize the first argument is a tuple
> representing the date.
>
> a.execute("update wafers set first_issue_date=:1 where wafr_number=:2",
> (2011, 10, 11, 19, 6, 40), 'DD73165.1C.12D0'))
>
> However  I realize above doesn’t work in cx_Oracle. I need to covert the
> tuple to a datetime object before passing it into the Execute() statement.
>
> a.execute("update wafers set first_issue_date=:1 where wafr_number=:2",
> (cx_Oracle.Timestamp(2011, 10, 11, 19, 6, 40), 'DD73165.1C.12D0'))
>
> Again, is there anyway this can be made backward compatible to python
1.5.2
> whereby it can just accept date in tuple format? I may not be able to
apply
> a generic parser for this one for all API as possibly there could be other
> data which is also a 6 element tuple but not in date context. It looks
like
> I need to change it at each server API level as only the API level knows
the
> context of each data to be sent to the Oracle in the insert/update/delete
> statement.
>
> Appreciate your input in advance.
>
>>>> a.execute("update wafers set first_issue_date=:1 where wafr_number=:2",
>>>> ((2011, 10, 11, 19, 6, 40), 'DD73165.1C.12D0'))
> Traceback (most recent call last):
>   File "<stdin>", line 1, in <module>
>   File "/home/r32813/genesis/GEN_DEV_271/Product/Lib/EComponent.py", line
> 821, in __call__
>     self._method, args, kw )
>   File "/home/r32813/genesis/GEN_DEV_271/Product/Lib/RMI.py", line 1779,
in
> _genericInvocation
>     reply = self._requestReply( replyBit, (method_name, args, kw) )
>   File "/home/r32813/genesis/GEN_DEV_271/Product/Lib/RMI.py", line 1612,
in
> _requestReply
>     reply = self._postReceive(reply)
>   File "/home/r32813/genesis/GEN_DEV_271/Product/Lib/RMI.py", line 1748,
in
> _postReceive
>     raise ValueError(post_msg[1])  # raise application-level exception
> ValueError: Variable_TypeByValue(): unhandled data type tuple

You could use a Connection.inputtypehandler, either on the connection or the
cursor object:
http://cx-oracle.sourceforge.net/html/connection.html#Connection.inputtypehandler
Maybe something like this (did not test):

def inputtypehandler(cursor, value, arraysize):
   if instance(value, tuple):
       var = cursor.var(cx_Oracle.DATETIME, arraysize=arraysize,
                        inconverter=cx_Oracle.Timestamp)
       return var

a.inputtypehandler = inputtypehandler

-- 
Amaury Forgeot d'Arc