[Zope-DB] dtml-let variables in sql queries

2006-11-23 Thread Andreas Tille

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:

paramsname nameid

/params
SELECT * FROM names
WHERE fake = 0
dtml-if name
  AND dtml-sqltest name op=like type=nb
/dtml-if
dtml-if nameid
  AND dtml-sqltest nameid op=eq type=int
/dtml-if

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:


dtml-var standard_html_header

dtml-if REQUEST.form.has_key('nameid')
dtml-let nameid=REQUEST.form['nameid']
   Selected nameid= dtml-var nameidbr /
   dtml-var print_cases
/dtml-let
dtml-else

  dtml-if REQUEST.form.has_key('namepart')
dtml-let namepart=REQUEST.form['namepart']
Namepart = dtml-var namepartbr /
dtml-let name=namepart + '%'
  Seek for name = 'dtml-var name'.
  dtml-var print_cases
/dtml-let
/dtml-let
  dtml-else
form action=index_html name=test method=POST
  input name=namepart type=text size=10 /
/form
  /dtml-if
/dtml-if

dtml-var standard_html_footer


where namepart is constructed as name+'%' to enable the like
query.  Unfortunately
 dtml-let namepart=namepart + '%'
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:

table
dtml-in GetCases
  trtd
   a href=dtml-var URL0?nameid=dtml-var nameiddtml-var name/a
  /td/tr
/dtml-in
/table


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


Re: [Zope-DB] dtml-let variables in sql queries

2006-11-23 Thread Jonathan


- Original Message - 
From: Andreas Tille [EMAIL PROTECTED]

To: zope-db@zope.org
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:

paramsname nameid

/params
SELECT * FROM names
WHERE fake = 0
dtml-if name
  AND dtml-sqltest name op=like type=nb
/dtml-if
dtml-if nameid
  AND dtml-sqltest nameid op=eq type=int
/dtml-if

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:


dtml-var standard_html_header

dtml-if REQUEST.form.has_key('nameid')
dtml-let nameid=REQUEST.form['nameid']
   Selected nameid= dtml-var nameidbr /
   dtml-var print_cases
/dtml-let
dtml-else

  dtml-if REQUEST.form.has_key('namepart')
dtml-let namepart=REQUEST.form['namepart']
Namepart = dtml-var namepartbr /
dtml-let name=namepart + '%'
  Seek for name = 'dtml-var name'.
  dtml-var print_cases
/dtml-let
/dtml-let
  dtml-else
form action=index_html name=test method=POST
  input name=namepart type=text size=10 /
/form
  /dtml-if
/dtml-if

dtml-var standard_html_footer


where namepart is constructed as name+'%' to enable the like
query.  Unfortunately
 dtml-let namepart=namepart + '%'
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:

table
dtml-in GetCases
  trtd
   a href=dtml-var URL0?nameid=dtml-var nameiddtml-var name/a
  /td/tr
/dtml-in
/table


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


Re: [Zope-DB] dtml-let variables in sql queries

2006-11-23 Thread Dieter Maurer
Andreas Tille wrote at 2006-11-23 16:06 +0100:
 
Unfortunately
  dtml-let namepart=namepart + '%'
does not seem to work transparently in the SQL-Method

Right.

ZSQL Methods 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