Mailing List Archive

----- Original Message -----
From: "Ashley Lloyd" <ashleylloyd@hotmail.com>
To: <zope-db@zope.org>
Sent: Friday, August 05, 2005 6:55 AM
Subject: [Zope-DB] Zope/Plone, interbase, and blob fields


> Apologies if this appears twice - I initially sent from the wrong
address!!
>
> Hi, I've been having trouble updating BLOB (subtype text) fields from
Zope.
>
> I've a table with several fields, one of which is a blob. Creating a
record
> works fine, but when I attempt to update a record, I get an error.
Initially
> I used gvib, but recently (thanks to a lot of help from another list) I
> changed to kinterbasdbDA, and I still get errors.
> The errors I get are as follows:
> Error Type ProgrammingError
> Error Value (-413, 'execute.isc_dsql_execute: conversion error from string
> "BLOB". ')
> Error Trace Traceback (most recent call last):
> File "C:\Program
> Files\ESOPPlone\Zope\lib\python\DocumentTemplate\DT_Try.py", line 149, in
> render_try_except
> result = render_blocks(self.section, md)
> File "C:\Program
> Files\ESOPPlone\Zope\lib\python\DocumentTemplate\DT_Util.py", line 201, in
> eval
> return eval(code, d)
> File "", line 2, in f
> File "C:\Program Files\ESOPPlone\Zope\lib\python\Shared\DC\ZRDB\DA.py",
line
> 428, in __call__
> else: result=DB__.query(query, self.max_rows_)
> File "C:\Program
> Files\ESOPPlone\Zope\lib\python\Products\kinterbasdbDA\db.py", line 198,
in
> query
> self.cursor.execute(src)
> File "C:\Program
>
Files\ESOPPlone\Zope\lib\python\Products\kinterbasdbDA\kinterbasdb\__init__.
> py", line 585, in execute
> res = _kinterbasdb.execute(self._C_cursor, sql, params)
> ProgrammingError: (-413, 'execute.isc_dsql_execute: conversion error from
> string "BLOB". ')
> I also get a similar error (same value & type) when just testing the SQL
> method in the ZMI, even when the SQL that the method generates is just:
> UPDATE ENTITY
> SET EN_NOTES = 'Hello world'
> WHERE EN_KEY = 12
> The metatata of the table has the notes field definition as:
> EN_NOTES BLOB SUB_TYPE 1 SEGMENT SIZE 80,

Maybe you should try changing the blob type? (as a test, anyway)
For InterBase 6 there are reserved 7 subtypes:

0 - Unstructured, generally applied to binary data or data of an
indeterminate type
1 - Text
2 - Binary language representation (BLR)
3 - Access control list
4 - (Reserved for future use)
5 - Encoded description of a table's current metadata
6 - Description of multi-database transaction that finished irregularly

or possbly sub_type -1 segment size 65535 ---user defined subtype -1 with
segment size of 64K for example

hth

bobb

> Any update that does not involve this field works fine, and the field can
be
> set to null, but it will not allow me to update.
> I am running quite an old version of Zope - its from a Plone install.
> Zope Version - (Zope 2.6.1 (binary release, python 2.1, win32-x86), python
> 2.1.3, win32)
> Python Version - 2.1.3 (#35, Apr 8 2002, 17:47:50) [MSC 32 bit (Intel)]
> System Platform - win32
> I got quite a bit of help from the general list, but no-one could
understand
> why I was getting this error, especially when in one case, someone else
had
> a remarkably similar setup, and even sent me his database adaptor, and he
> doesn't get the errors! I even removed all variables, and just tested the
> SQL exactly as it was above (not generated by dtml-sqlvar tags at all),
and
> still got the errors.
> Can anyone help?
> Thanks in advance
> Ashley
> _______________________________________________
> Zope-DB mailing list
> Zope-DB@zope.org
> http://mail.zope.org/mailman/listinfo/zope-db

_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Thanks again for your post, and my sincere apologies for the delay in
writing (I've been off and then had to sort out some other stuff which
required all of my limited attention!).

Thanks also to the off-list help from David Rushby.

His advice which sort of worked was basically:
Using a database interface that allows you to take
advantage of prepared statements and bound parameters,
as in:
cur.execute("update table_1 set blob_field_1 = ?
where other_field_1 = ?", ('blob string value', 'other
field value'))

This was a slightly different python syntax to that which I was using, and
seems to work - but only when using it straight from Python. I can run this
absolutely fine (even using gvib!) from the python instance in the Plone
installation (Plone\Python\python.exe). There are absolutely no errors, and
it updates the field.
However, copying the function almost exactly (just inserting a "self"
parameter before the two notes/key parameters .. or even trying it without)
does not work. When I call this function, Plone freezes - no errors,
nothing, just everything freezes up (there's nothing I can see in the log
files apart from it calling the page).
I don't know if this is of any significance, but when this happens, for any
calls to a page served by Plone, the browser tries to open the page, but
nothing happens, no page not found errors, etc, it just keeps trying. Once
Plone has been stopped, then the page not found appears. I doubt its of any
use, but you can't have too much info!!

Anyway, the function in the external method is:
def updateEntityNotes(self,notes,entKey):
try:
dbCon = gvib.connect(theDB, userName, userPass)
cur=dbCon.cursor()
cur.execute("UPDATE ENTITY SET EN_NOTES=? WHERE
EN_KEY=?",(notes,int(entKey)))
dbCon.commit()
return 'ok'
except:
return '<b>Error:</b> Sorry, your request could not be processed
correctly.<br> '+str(traceback.format_exception(sys.exc_type, sys.exc_value,
sys.exc_traceback))

And it is called with a dtml-let call (so the result can be used):
<dtml-let err="updateEntityNotes(theNotes,theKey)">
(theNotes and theKey have already been subject to calls to be str() and
int() respectively).
I just tested the interface to this from Plone, removing any database access
at all, and just returning a random statement, and it works fine. So Plone
is accessing the external method, there are no imported functions used that
aren't used in other external methods, so it just seems to be executing the
cur.execute() statement. Without the dbCon.commit(), Plone still crashes.

Can anyone tell me why the actual python instance that came with Plone can
do this fine, but Plone itself can't?

Kind regards, and thanks once again to everyone who has helped me.

Ashley


----- Original Message -----
From: "bobb" <rawsystems@gmail.com>
To: "Ashley Lloyd" <ashleylloyd@hotmail.com>; <zope-db@zope.org>
Sent: Friday, August 05, 2005 6:39 PM
Subject: Re: [Zope-DB] Zope/Plone, interbase, and blob fields


>
> ----- Original Message -----
> From: "Ashley Lloyd" <ashleylloyd@hotmail.com>
> To: <zope-db@zope.org>
> Sent: Friday, August 05, 2005 6:55 AM
> Subject: [Zope-DB] Zope/Plone, interbase, and blob fields
>
>
> > Apologies if this appears twice - I initially sent from the wrong
> address!!
> >
> > Hi, I've been having trouble updating BLOB (subtype text) fields from
> Zope.
> >
> > I've a table with several fields, one of which is a blob. Creating a
> record
> > works fine, but when I attempt to update a record, I get an error.
> Initially
> > I used gvib, but recently (thanks to a lot of help from another list) I
> > changed to kinterbasdbDA, and I still get errors.
> > The errors I get are as follows:
> > Error Type ProgrammingError
> > Error Value (-413, 'execute.isc_dsql_execute: conversion error from
string
> > "BLOB". ')
> > Error Trace Traceback (most recent call last):
> > File "C:\Program
> > Files\ESOPPlone\Zope\lib\python\DocumentTemplate\DT_Try.py", line 149,
in
> > render_try_except
> > result = render_blocks(self.section, md)
> > File "C:\Program
> > Files\ESOPPlone\Zope\lib\python\DocumentTemplate\DT_Util.py", line 201,
in
> > eval
> > return eval(code, d)
> > File "", line 2, in f
> > File "C:\Program Files\ESOPPlone\Zope\lib\python\Shared\DC\ZRDB\DA.py",
> line
> > 428, in __call__
> > else: result=DB__.query(query, self.max_rows_)
> > File "C:\Program
> > Files\ESOPPlone\Zope\lib\python\Products\kinterbasdbDA\db.py", line 198,
> in
> > query
> > self.cursor.execute(src)
> > File "C:\Program
> >
>
Files\ESOPPlone\Zope\lib\python\Products\kinterbasdbDA\kinterbasdb\__init__.
> > py", line 585, in execute
> > res = _kinterbasdb.execute(self._C_cursor, sql, params)
> > ProgrammingError: (-413, 'execute.isc_dsql_execute: conversion error
from
> > string "BLOB". ')
> > I also get a similar error (same value & type) when just testing the SQL
> > method in the ZMI, even when the SQL that the method generates is just:
> > UPDATE ENTITY
> > SET EN_NOTES = 'Hello world'
> > WHERE EN_KEY = 12
> > The metatata of the table has the notes field definition as:
> > EN_NOTES BLOB SUB_TYPE 1 SEGMENT SIZE 80,
>
> Maybe you should try changing the blob type? (as a test, anyway)
> For InterBase 6 there are reserved 7 subtypes:
>
> 0 - Unstructured, generally applied to binary data or data of an
> indeterminate type
> 1 - Text
> 2 - Binary language representation (BLR)
> 3 - Access control list
> 4 - (Reserved for future use)
> 5 - Encoded description of a table's current metadata
> 6 - Description of multi-database transaction that finished irregularly
>
> or possbly sub_type -1 segment size 65535 ---user defined subtype -1
with
> segment size of 64K for example
>
> hth
>
> bobb
>
> > Any update that does not involve this field works fine, and the field
can
> be
> > set to null, but it will not allow me to update.
> > I am running quite an old version of Zope - its from a Plone install.
> > Zope Version - (Zope 2.6.1 (binary release, python 2.1, win32-x86),
python
> > 2.1.3, win32)
> > Python Version - 2.1.3 (#35, Apr 8 2002, 17:47:50) [MSC 32 bit (Intel)]
> > System Platform - win32
> > I got quite a bit of help from the general list, but no-one could
> understand
> > why I was getting this error, especially when in one case, someone else
> had
> > a remarkably similar setup, and even sent me his database adaptor, and
he
> > doesn't get the errors! I even removed all variables, and just tested
the
> > SQL exactly as it was above (not generated by dtml-sqlvar tags at all),
> and
> > still got the errors.
> > Can anyone help?
> > Thanks in advance
> > Ashley
> > _______________________________________________
> > Zope-DB mailing list
> > Zope-DB@zope.org
> > http://mail.zope.org/mailman/listinfo/zope-db
>
>
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Sorry to nag you all again, but I've not managed to resolve this issue.

Basically, I can update a blob field using the Python instance that Python
relies on, but can't use the same syntax to update from an external method
called by Plone.

The syntax that works from the python instance is:
cur.execute("update table_1 set blob_field_1 = ? where other_field_1 = ?",
('blob string value', 'other field value'))

When trying to execute this from an external method, Plone just freezes - no
errors, it just freezes.

The external method is defined as follows:

def updateEntityNotes(self,notes,entKey):
try:
dbCon = gvib.connect(theDB, userName, userPass)
cur=dbCon.cursor()
cur.execute("UPDATE ENTITY SET EN_NOTES=? WHERE
EN_KEY=?",(notes,int(entKey)))
dbCon.commit()
return 'ok'
except:
return '<b>Error:</bSorry, your request could not be processed
correctly.<br'+str(traceback.format_exception(sys.exc_type, sys.exc_value,
sys.exc_traceback))

And it is called with a dtml-let call (so the result can be used):
<dtml-let err="updateEntityNotes(theNotes,theKey)">
(theNotes and theKey have already been subject to calls to be str() and
int() respectively).
I just tested the interface to this from Plone, removing any database access
at all, and just returning a random statement, and it works fine. So Plone
is accessing the external method, there are no imported functions used that
aren't used in other external methods, so it just seems to be executing the
cur.execute() statement. Without the dbCon.commit(), Plone still crashes.

Can anyone tell me why the actual python instance that came with Plone can
do this fine, but Plone itself can't?

Thanks once again to everyone that has helped thus far, but it has been some
time since I received a post, and I've not managed to get this to work.

Cheers

Ashley



_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Ashley wrote:
> When trying to execute this from an external method, Plone just freezes - no
> errors, it just freezes.

PDB is your friend...

> The external method is defined as follows:
>
> def updateEntityNotes(self,notes,entKey):
import pdb
pdb.set_trace()
> try:
> dbCon = gvib.connect(theDB, userName, userPass)
> cur=dbCon.cursor()
> cur.execute("UPDATE ENTITY SET EN_NOTES=? WHERE
> EN_KEY=?",(notes,int(entKey)))
> dbCon.commit()
> return 'ok'
> except:
> return '<b>Error:</bSorry, your request could not be processed
> correctly.<br'+str(traceback.format_exception(sys.exc_type, sys.exc_value,
> sys.exc_traceback))

Bare excepts as you have above are a pretty bad idea, don't try and
handle it like that, either catch specific exceptions you know about or
leave standard_error_message to handle the lot...

PDB documentation can be found in the Documentation section of
http://python.org in the "Module Index" section.

cheers,

Chris

--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk

_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db