Hi :)
I've just managed to fix my issue by adding/modifying a couple of methods
of the *pydal.adapters.oracle.OracleAdapter class*; everything seems to be
working for me
def _insert(self, table, fields):
table_rname = table.sqlsafe
if fields:
keys = ','.join(f.sqlsafe_name for f, v in fields)
r_values = dict()
def value_man(f, v, r_values):
if f.type is 'text':
r_values[':' + f.sqlsafe_name] = self.expand(v, f.type)
return ':' + f.sqlsafe_name
else:
return self.expand(v, f.type)
values = ','.join(value_man(f, v, r_values) for f, v in fields)
return ('INSERT INTO %s(%s) VALUES (%s);' % (table_rname, keys,
values), r_values)
else:
return (self._insert_empty(table), None)
def insert(self, table, fields):
query, values = self._insert(table,fields)
try:
if not values:
self.execute(query)
else:
self.execute(query, values)
except Exception:
e = sys.exc_info()[1]
if hasattr(table,'_on_insert_error'):
return table._on_insert_error(table,fields,e)
raise e
if hasattr(table, '_primarykey'):
mydict = dict([(k[0].name, k[1]) for k in fields if k[0].name in
table._primarykey])
if mydict != {}:
return mydict
id = self.lastrowid(table)
if hasattr(table, '_primarykey') and len(table._primarykey) == 1:
id = {table._primarykey[0]: id}
if not isinstance(id, (int, long)):
return id
rid = Reference(id)
(rid._table, rid._record) = (table, None)
return rid
El martes, 4 de agosto de 2015, 1:00:59 (UTC-6), Niphlod escribió:
>
> 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.
