[Zope-dev] Z SQL Method problems (now two confirmed bugs)

[Matthew T. Kromer]

For extra credit, consider writing a proposal (and implementing, of course)
to change SQL methods to be capable of handling positional and/or named
parameters -- i.e. pass params as PARAMETERS and let the DRIVER figure out
what to do.  So, instead of SELECT * FROM emp WHERE empno = 7902; you could
SELECT * FROM emp WHERE empno = :empno.  The current implemention changes
everything to a string before sending it to the driver, it cannot handle
parameters.  The numero-uno gotcha is the 6 or so different ways parameters
can be passed.  Still, if converting from DTML-ish notation, you could
generate the proper statement AND parameter list at the same time.

----- Original Message -----
From: "Andy Dustman" <andy@dustman.net>
To: <zope-dev@zope.org>
Sent: Thursday, August 09, 2001 3:15 PM
Subject: [Zope-dev] Z SQL Method problems (now two confirmed bugs)


> [Moving this to Zope-Dev] I've done some more investigation and determined
> there is a fundamental flaw in the way Z SQL Method parameters are
> handled.
>
> On Tue, 7 Aug 2001, Andy Dustman wrote:
>
> > I have a Z SQL Method (SELECT) with the following parameters:
> >
> > HostId DomainId Name IP
> >
> > and the following SQL:
> >
> > SELECT
> > HostId, DomainId, RecordType, Name, IP, Distance, Target, TTL,
> > StartEndTime, Location, Updated
> > FROM Hosts
> > <dtml-sqlgroup where>
> > <dtml-sqltest HostId column="HostId" type=int optional>
> > <dtml-and>
> > <dtml-sqltest DomainId column="DomainId" type=int optional>
> > <dtml-and>
> > <dtml-sqltest Name column="Name" type=nb optional>
> > <dtml-and>
> > <dtml-sqltest IP column="IP" type=int optional>
> > </dtml-sqlgroup>
> >
> > Note that all tested columns are optional. Using the Test tab,
everything
> > works correctly: If I leave off values, that portion isn't rendered.
> >
> > Now... I have a DTML Method which performs:
> >
> > <dtml-in SELECT mapping> ... </dtml-in>
> >
> > This bombs unless all the parameters are specified in form data.
>
> Now, for the reason it bombs: When called, a Z SQL Method ultimately gets
> information about parameters from the parse() function in Aquaduct.py. For
> each parameter parsed (and there are problems with the parsing, but that's
> another problem), it checks trys three things:
>
> 1) Get the parameter value from REQUEST.
>
> 2) Failing that: Assign the parameter it's default value, if it exists.
>
> 5^H3) Failing that: If the parameter is marked optional, ignore it.
>
> Here is the problem: There is no way for parse() to mark a parameter as
> optional. It simply is not tested for in any way. It also cannot get this
> information from the <dtml-sqltest ...> element, because it doesn't have
> access to it or the template.
>
> Solution: parse() does have the capability of finding a parameter type
> (for the above-mentioned reason), i.e. the parameters could be written:
>
> HostId:int DomainId:int Name:nb IP:int
>
> I propose also allowing :optional to follow the type, so that parse() can
> set this properly, i.e.:
>
> HostId:int:optional DomainId:int:optional Name:nb:optional IP:int:optional
>
> Note that currently default values take precedence over optional values
> (if it could find any).
>
> I'll prepare a patch that can do this if requested (DC/ZopeCorp decide
> this is a good solution) and it will also fix the parameter="value"
> parsing problem (broken in 2.4.0).
>
> --
> Andy Dustman         PGP: 0xC72F3F1D
>     @       .net     http://dustman.net/andy
> I'll give spammers one bite of the apple, but they'll
> have to guess which bite has the razor blade in it.
>
>
> _______________________________________________
> Zope-Dev maillist  -  Zope-Dev@zope.org
> http://lists.zope.org/mailman/listinfo/zope-dev
> **  No cross posts or HTML encoding!  **
> (Related lists -
>  http://lists.zope.org/mailman/listinfo/zope-announce
>  http://lists.zope.org/mailman/listinfo/zope )
>