Re: [Zope-DB] dtml-let variables in sql queries
Andreas Tille wrote at 2006-11-23 16:06 +0100: > >Unfortunately > >does not seem to work transparently in the SQL-Method Right. "ZSQL Method"s take their parameters either from explicitly passed keyword arguments or from the request but not from the DTML namespace. -- Dieter ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
AW: [Zope-DB] [ANN] Modified version of DCOracle2 is available
Hi
you remember my report
http://mail.zope.org/pipermail/zope/2005-August/160762.html
of an BUG for the handling of LONGs in DCO2?
The consequence of this BUG is stochastic results for LONG fields
We use an Fix of this BUG:
Hint: The Documentation of the OCI from Oracle say us:
defnp (IN), iter (IN), bufpp (OUT), alenpp (IN/OUT), piecep (IN/OUT),
indpp (IN), rcodep (IN)
Caution: When working with callback parameters, it is important
to keep in mind what is meant by IN and OUT for the parameter
mode. Normally, in an OCI function, an IN parameter refers to data
being passed to Oracle, and an OUT parameter refers to data
coming back from Oracle. In the case of callbacks, this is reversed.
IN means data is coming from Oracle into the callback, and OUT
means data is coming out of the callback and going to Oracle.
Docu from OCI for OCIDefineByPos:
indp (IN/OUT), alenp (IN/OUT), rcodep (OUT): Ignored for
dynamic binds.
First we fix Cursor_ResultSet and Second we fix Cursor_fetch:
First Fix:
static PyObject *Cursor_ResultSet(Cursor *self, int count) {
PyObject *list;
ResultSet *rs;
int status;
int i;
sword mode = OCI_DEFAULT;
dvoid *valuep;
ub4 width;
LongFetch *lf;
TRACE(T_ENTRY,("sAd", "Cursor_ResultSet", self, count));
if (self->definition == NULL) {
TRACE(T_ERROR,("ss","Cursor_ResultSet","description is NULL"));
PyErr_SetString(ProgrammingErrorObject,
"cursor description is None");
return NULL;
}
self->batchsz = count;
if ((list = Py_BuildValue("[]")) == NULL) {
TRACE(T_ERROR,("ss","Cursor_ResultSet",
"PyBuildValue returned NULL"));
return NULL;
}
for (i = 1; i <= PyList_Size(self->definition); i++) {
mode = OCI_DEFAULT;
if ((rs = (ResultSet *) ResultSet_alloc(self, i, count))
== NULL) {
Py_DECREF(list);
TRACE(T_ERROR,("ss","Cursor_ResultSet",
"ResultSetAlloc returned NULL"));
return NULL;
}
valuep = rs->valuep;
width = rs->width;
rs->fetchResultCode = OCI_SUCCESS;
if (self->flags & LONG_COLUMN && (char) i == self->longcol) {
mode = OCI_DYNAMIC_FETCH;
lf = (LongFetch *) rs->valuep;
longFetchInit(lf);
/*valuep = NULL;*/
width = 0x7FFF; /* Max unsigned long */
rs->indp = &lf->ind; //KMH, 2.8.2005 synchronisation of
dynamicFetch with ResultSet
rs->rcodep = &lf->rcode; //KMH, 2.8.2005
synchronisation of dynamicFetch with ResultSet
}
TRACE(T_CALL,("sdAddd", "OCIDefineByPos", i, valuep, width,
rs->cdty, mode));
/* Now bind the result set */
/*
Docu from OCI:
indp (IN/OUT), alenp (IN/OUT), rcodep (OUT): Ignored for
dynamic binds.
*/
status = OCIDefineByPos(self->stmtp, &(rs->defnp),
self->errhp, i, valuep, width,
rs->cdty, (dvoid *) rs->indp,
rs->rlenp, rs->rcodep, mode);
TRACE(T_RETURN,("sR", "OCIDefineByPos", status));
if (status != OCI_SUCCESS) {
Py_DECREF(rs);
Py_DECREF(list);
return RaiseOCIError(self->errhp, OCI_HTYPE_ERROR);
}
if (self->flags & LONG_COLUMN && (char) i == self->longcol) {
TRACE(T_CALL,("sA", "OCIDefineDynamic", rs->valuep));
status = OCIDefineDynamic(rs->defnp, self->errhp,
(dvoid *) rs->valuep,
(OCICallbackDefine) dynamicFetch);
TRACE(T_RETURN,("sR", "OCIDefineDynamic", status));
if (status != OCI_SUCCESS) {
Py_DECREF(rs);
Py_DECREF(list);
return RaiseOCIError(self->errhp,
OCI_HTYPE_ERROR);
}
}
PyList_Append(list, OBJECT(rs));
Py_DECREF(rs); /* Now that its in the list ... */
}
if (self->results != NULL) {
Py_DECREF(self->results);
}
self->results = list;
self->current = 0;
Py_INCREF(Py_None);
Re: [Zope-DB] Re: dtml-let variables in sql queries
Am 23.11.2006, 17:03 Uhr, schrieb Andreas Tille <[EMAIL PROTECTED]>: You are right here and I would definitely prefer Python scripts. The problem is that I'm using the Formulator Product and despite there are descriptions how to use it with Python scripts I failed while it was very straigtforeward to use DTML (in fact, this is the only part of my application in DTML). I just stripped down the application to a very simple example and avoided Formulator magic which is obviousely not the source of the problem. It's easy enough to call Formulator from within a script and extract the results from it: result = context.MY_FORMULATOR_THINGY.validate_all(context.REQUEST) Make it work with a Script so you can forget all the evil DTML stuff, you won't regret it! Charlie ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
[Zope-DB] Re: dtml-let variables in sql queries
- Original Message - From: "Andreas Tille" <[EMAIL PROTECTED]> To: "Jonathan" <[EMAIL PROTECTED]> Cc: Sent: Thursday, November 23, 2006 11:03 AM Subject: Re: dtml-let variables in sql queries On Thu, 23 Nov 2006, Jonathan wrote: name nameid SELECT * FROM names WHERE fake = 0 AND AND First off, i would highly recommend using python scripts instead of DTML for the kind of processing you have described. You are right here and I would definitely prefer Python scripts. The problem is that I'm using the Formulator Product and despite there are descriptions how to use it with Python scripts I failed while it was very straigtforeward to use DTML (in fact, this is the only part of my application in DTML). I just stripped down the application to a very simple example and avoided Formulator magic which is obviousely not the source of the problem. That said, you have not described the GetCases method. I forgot to mention the name. It is the code snipped above. If GetCases is the name of your SQL method, then you need to pass in the parameter 'nameid' Well, nameid is not explicitely passed but taken from the parent out of and this works. The problem is that name which is builded out of Namepart = is ignored by the SQL method and I fail to see the difference. The seems to be false in any case. It may be due to the fact that the 'let' statement creates a variable in another part of the 'namespace' (ie. not in the REQUEST namespace). You could test this theory by replacing the dtml-let with a dtml-call REQUEST.set eg. Namepart = hth Jonathan ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
[Zope-DB] Re: dtml-let variables in sql queries
On Thu, 23 Nov 2006, Jonathan wrote: name nameid SELECT * FROM names WHERE fake = 0 AND AND First off, i would highly recommend using python scripts instead of DTML for the kind of processing you have described. You are right here and I would definitely prefer Python scripts. The problem is that I'm using the Formulator Product and despite there are descriptions how to use it with Python scripts I failed while it was very straigtforeward to use DTML (in fact, this is the only part of my application in DTML). I just stripped down the application to a very simple example and avoided Formulator magic which is obviousely not the source of the problem. That said, you have not described the GetCases method. I forgot to mention the name. It is the code snipped above. If GetCases is the name of your SQL method, then you need to pass in the parameter 'nameid' Well, nameid is not explicitely passed but taken from the parent out of and this works. The problem is that name which is builded out of Namepart = is ignored by the SQL method and I fail to see the difference. The seems to be false in any case. Kind regards Andreas. -- http://fam-tille.de ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
Re: [Zope-DB] dtml-let variables in sql queries
- Original Message - From: "Andreas Tille" <[EMAIL PROTECTED]> To: Sent: Thursday, November 23, 2006 10:06 AM Subject: [Zope-DB] dtml-let variables in sql queries Hi, I have defined the following table CREATE TABLE names ( nameid int, name text, fake int ); INSERT INTO names values ( 1, 'Alfons',0 ); INSERT INTO names values ( 2, 'Alberto', 0 ); INSERT INTO names values ( 3, 'Adam', 0 ); INSERT INTO names values ( 4, 'Anibal',0 ); INSERT INTO names values ( 5, 'Bert', 0 ); INSERT INTO names values ( 6, 'Caesar',0 ); INSERT INTO names values ( 7, 'Dagobert', 1 ); INSERT INTO names values ( 8, 'Kuno', 1 ); GRANT SELECT ON names TO zope ; The following SQL-Method should extract all names where fake = 0 except if there are further parameters like name or nameid that should restrict the result set if they are given: name nameid SELECT * FROM names WHERE fake = 0 AND AND This works in the ZMI test tab as expected. The original problem is that I want to design a Form where you can specify only first letters like 'Al' and the query looks for "name like 'Al%'". I tried to do this using: Selected nameid= Namepart = Seek for name = ''. where namepart is constructed as name+'%' to enable the like query. Unfortunately does not seem to work transparently in the SQL-Method, because this does not work and just prints every single name. If somebody wants to try this code here is the print_cases method: Any idea why the variable namepart is not known in the SQL-Method while nameid is and works perfectly to select one name from the list? Many thanks Andreas. First off, i would highly recommend using python scripts instead of DTML for the kind of processing you have described. That said, you have not described the GetCases method. If GetCases is the name of your SQL method, then you need to pass in the parameter 'nameid' hth Jonathan ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
[Zope-DB] dtml-let variables in sql queries
Hi, I have defined the following table CREATE TABLE names ( nameid int, name text, fake int ); INSERT INTO names values ( 1, 'Alfons',0 ); INSERT INTO names values ( 2, 'Alberto', 0 ); INSERT INTO names values ( 3, 'Adam', 0 ); INSERT INTO names values ( 4, 'Anibal',0 ); INSERT INTO names values ( 5, 'Bert', 0 ); INSERT INTO names values ( 6, 'Caesar',0 ); INSERT INTO names values ( 7, 'Dagobert', 1 ); INSERT INTO names values ( 8, 'Kuno', 1 ); GRANT SELECT ON names TO zope ; The following SQL-Method should extract all names where fake = 0 except if there are further parameters like name or nameid that should restrict the result set if they are given: name nameid SELECT * FROM names WHERE fake = 0 AND AND This works in the ZMI test tab as expected. The original problem is that I want to design a Form where you can specify only first letters like 'Al' and the query looks for "name like 'Al%'". I tried to do this using: Selected nameid= Namepart = Seek for name = ''. where namepart is constructed as name+'%' to enable the like query. Unfortunately does not seem to work transparently in the SQL-Method, because this does not work and just prints every single name. If somebody wants to try this code here is the print_cases method: Any idea why the variable namepart is not known in the SQL-Method while nameid is and works perfectly to select one name from the list? Many thanks Andreas. -- http://fam-tille.de ___ Zope-DB mailing list Zope-DB@zope.org http://mail.zope.org/mailman/listinfo/zope-db
