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

[Wong W. Meng-R. <r3...@fr...>]

Hello Amaury,

Thanks a lot! Overriding the inputtypehandler works. Since my application is sending date as tuple but cx_Oracle.Timestamp() is expecting 6 argument in integer, I wrote another converter function and use it against inputtypehandler. Now I need to plug this into my application

Once again, thanks! :)

$ python
Python 2.7.1 (r271:86832, Oct  6 2011, 11:10:10) [C] on hp-ux11
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> c=cx_Oracle.connect("gen812ora8/gen812db1ora8@DB1ORA9")
>>> a=c.cursor()
>>> def convertT2D(x):
...     return cx_Oracle.Timestamp(x[0], x[1], x[2], x[3], x[4], x[5])
...
>>> def inputtypehandler(cursor, value, arraysize):
...     if isinstance(value, tuple):
...        return cursor.var(cx_Oracle.DATETIME, arraysize=arraysize, inconverter=convertT2D)
...
>>> a.inputtypehandler=inputtypehandler
>>> a.execute("update users set user_last_name=:1, user_first_name=:2, USER_MODIFY_DATE=:3 where user_id=:4", ('XXXXX', 'YYYY', (2011, 10, 11, 11, 11, 11), 'ftcs'))
>>> c.commit()
>>>

SQL> select user_last_name, user_first_name, USER_MODIFY_DATE from users where user_id='ftcs';

USER_LAST_NAME       USER_FIRST_NAME      USER_MODI
-------------------- -------------------- ---------
XXXXX                YYYY                 11-OCT-11

Regards,
Wah Meng

________________________________
From: Amaury Forgeot d'Arc [mailto:ama...@gm...]
Sent: Tuesday, October 11, 2011 8:16 PM
To: cx-...@li...
Subject: Re: [cx-oracle-users] Another oracledb to cxOracle Migration Issue: Parsing to and from Column in DATE type

Hi,

2011/10/11 Wong Wah Meng-R32813 <r3...@fr...<mailto: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