ok, we were discussing the same thing over at pydal's repo to address
another issue, that is closely related
. https://github.com/web2py/pydal/issues/155
On Monday, August 3, 2015 at 8:57:44 PM UTC+2, Boris Aramis Aguilar
Rodríguez wrote:
>
> I've found the following:
>
> To insert a text using the cx_Oracle driver directly if you do something
> like
>
> import cx_Oracle
> connection = cx_Oracle.connect('connectionstring')
>
> cursor = connection.cursor()
>
> p = ''
> for i in range(1,10000):
> p += str(i)
>
> sss = "INSERT INTO
> ipvpn_route_table(read_datetime,ip_vpn,route_table,destinations) VALUES
> (NULL,6,'" + p + "','" + p + "')"
> cursor.execute(sss)
>
> It fails with the same error as the web2py traceback
>
> DatabaseError: ORA-01704: string literal too long
>
> The correct way to do it is by passing it with parameters (and it works with
> any ammount of characters below 4GB of clob data) as follows:
>
> import cx_Oracle
> connection = cx_Oracle.connect('... connection string')
>
> cursor = connection.cursor()
>
> p = ''
> for i in range(1,10000):
> p += str(i)
>
> sss = "INSERT INTO
> ipvpn_route_table(read_datetime,ip_vpn,route_table,destinations) VALUES
> (NULL,6,:FOO,:BAR)"
> cursor.execute(sss, FOO=p, BAR=p)
>
>
>
> El lunes, 3 de agosto de 2015, 9:51:16 (UTC-6), Boris Aramis Aguilar
> Rodríguez escribió:
>>
>> Hi,
>>
>> I've been currently working with Oracle as a database backend, I have
>> found one issue that I highly suspect has to do with the DAL Adapter; when
>> you use web2py text fields they get mapped into CLOB with oracle database
>> backend (as you can see on the OracleAdapter code) the issue is dealing
>> with text longer than 4k characters. Oracle doesn't support a typical
>> insert with more than 4k characters dealing with a CLOB data type, because
>> it treats it as a string and strings can't be more than 4k characters long.
>> So for example dealing with a table like
>>
>> db.define_table('atable', Field('longtext', 'text'))
>>
>> db.atable.insert(longtext=somelongtext) #This fails with the following
>> exception
>>
>> File "/var/www/web2py/gluon/packages/dal/pydal/objects.py", line 691, in
>> insert
>> ret = self._db._adapter.insert(self, self._listify(fields))
>> File "/var/www/web2py/gluon/packages/dal/pydal/adapters/base.py", line
>> 731, in insert
>> raise e
>> DatabaseError: ORA-01704: string literal too long
>>
>> And reading trough several forums I found that this error appears on CLOB
>> insertion when you do a tipycal insert.
>> https://community.oracle.com/thread/1068414?start=0&tstart=0
>>
>> So i guess OracleAdapter is trying to achieve a typical insertion when it
>> shouldn't as it has to deal with CLOB logic.
>>
>> I think I could fix the problem but I'm not really sure if someone can help
>> me out with a hint of some kind.
>>
>> Thanks!
>>
>>
>>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
