This great. It is a good solution for now and can be used as example of
similar solutions for other adapters.
On Wednesday, 5 August 2015 12:17:49 UTC-5, Boris Aramis Aguilar Rodríguez
wrote:
>
> 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.
