[Zope-DB] Z sql Method and using 'OR'

[Laura McCord]

>However, like others have already said, this is all documented in the
Zope book.  A bit of reading first would avoid an 
>awful lot of these questions.

Sorry, I didn't even know you could use dtml in zsql methods I thought
they were only used in dtml methods/documents because the majority of
the examples use these. But, I finally found an example of them being
used in a query. For any newbies you can find it in Appendix A: DTML
Reference towards the bottom if needed.

-----Original Message-----
From: Smith, Neil (Worcester) [mailto:Neil.Smith at npower.com] 
Sent: Thursday, May 06, 2004 2:46 AM
To: Laura McCord; zope-db at zope.org
Subject: RE: [Zope-DB] Z sql Method and using 'OR'


Even better would be:

SELECT 
	users.userid,
	users.username,
	users.useremail,
	users.useroffice,
	users.userpwd,
	users.userpwdchg,
	pcTable.pcname
FROM users LEFT JOIN pcTable ON users.pcid = pcTable.pcid <dtml-sqlgroup
where> users.userid = users.userid <dtml-and>
	<dtml-sqltest username column="users.username" type="nb"
optional="Yes"> <dtml-and> 
	<dtml-sqltest useroffice column="users.useroffice" type="nb"
optional="Yes"> 
</dtml-sqlgroup>
ORDER BY users.useroffice, users.username asc

The type=nb is "non-blank", and the sqltest does not evaluate to
anything if the argument is NULL, so you don't need the dtml-if tests.

However, like others have already said, this is all documented in the
Zope book.  A bit of reading first would avoid an awful lot of these
questions.

-- 
Neil



-----Original Message-----
From: Laura McCord [mailto:Laura.McCord at doucet-austin.com]
Sent: 05 May 2004 22:20
To: Charlie Clark; zope-db at zope.org
Subject: RE: [Zope-DB] Z sql Method and using 'OR'


Actually, Kent Hoxsey clued me in on using <dtml-sqlgroup>, <dtml-and>
....

This is what resulted and it works:

select 
users.userid,
users.username,
users.useremail,
users.useroffice,
users.userpwd,
users.userpwdchg,
pcTable.pcname from users left join pcTable on users.pcid = pcTable.pcid

<dtml-sqlgroup where> users.userid = users.userid
<dtml-and>
<dtml-if username>
 <dtml-sqltest username column="users.username" type="string"
optional="Yes"> </dtml-if> 
 <dtml-and> 
 <dtml-if useroffice> 
  <dtml-sqltest useroffice column="users.useroffice" type="string"
optional="Yes"> 
 </dtml-if>
</dtml-sqlgroup>
order by users.useroffice, users.username asc

-Laura

P.S I didn't even know about <dtml-sqlgroup> or <dtml-and> until now. I
have been looking at a couple of zope books and neither had these.

-----Original Message-----
From: zope-db-bounces at zope.org [mailto:zope-db-bounces at zope.org] On
Behalf Of Charlie Clark
Sent: Wednesday, May 05, 2004 4:08 PM
To: Kent Hoxsey
Cc: zope-db at zope.org
Subject: Re: [Zope-DB] Z sql Method and using 'OR'


Eek! This is one of the reasons I really don't like DTML this is
essentially 
duplicating SQL functionality with a lot of impenetrable syntax making 
debugging more difficult because SQL is going to be generated.

On 2004-05-05 at 21:39:57 [+0200], Kent Hoxsey wrote:
> The ZSQL method would have two input parameters, 'username' and
> 'useroffice', and would look like this:
> 
> select * from users u
> <dtml-sqlgroup where> u.user_id = u.user_id
> <dtml-and>
> <dtml-if username>
> <dtml-sqltest username column="u.username" type="string"
> optional="Yes"> </dtml-if> <dtml-and>
> <dtml-if useroffice>
> <dtml-sqltest useroffice column="u.useroffice" type="string"
optional="Yes">
> </dtml-if>
> 
> I know this is a bit complicated and messy, but that's due to ZSQL
> methods using DTML to dynamically define the SQL (switch ZSQL to 
> PageTemplates and TAL? we'll save that discussion for another time...)

> . Stick with me and I'll break it down...

I think a good compromise can be achieved like this:

select * from users
where
<dtml-if username>
(username = <dtml-sqlvar name="username" type="string"> </dtml-if>
<dtml-if useroffice> useroffice = <dtml-sqlvar name="useroffice"
type="string"> </dtml>

I've got so used to this I haven't even started looking at SQLs own
approach 
to flow control...

Now, this only works for either "username" OR "useroffice" but will give
a 
syntax error if both are passed. There is an elegant solution to this 
problem...

Charlie

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

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


**********************************************************************
 
The information contained in this e-mail is confidential and intended
only for the use of the addressee. If the reader of this message is not
the addressee, you are hereby notified that you have received this
e-mail in error and you must not copy, disseminate, distribute, use or
take any action as a result of the information contained in it.
    
If you have received this e-mail in error, please contact the address
below (UK 01384 275454) and delete it immediately from your system.

postmaster at npower.com
    
**********************************************************************



**********************************************************************
This e-mail is provided for general information purposes only and does
not constitute investment or transactional advice. For  the avoidance of
doubt the contents of this email are subject to contract and will not
constitute a legally binding contract.

The information contained in this e-mail is confidential and intended
only for the use of the addressee. If the reader of this message is not
the addressee, you are hereby notified that you have received this
e-mail in error and you must not copy, disseminate, distribute, use or
take any action as a result of the information contained in it.
    
If you have received this e-mail in error, please notify
postmaster at npower.com (UK 01384 275454) and delete it immediately from
your system.
    
Neither Npower nor any of the other companies in the RWE Innogy group
from whom this e-mail originates accept any responsibility for losses or
damage as a result of any viruses and it is your responsibility to check
attachments (if any) for viruses. Npower Limited Registered office:
Windmill Hill Business Park, Whitehill Way, Swindon SN5 6PB. Registered
in England and Wales: number 3653277.  This e-mail may be sent on behalf
of a member of the RWE Innogy group of companies.
**********************************************************************