[Zope-DB] Trouble writing a left join using z sql method

[Charlie Clark]

On 2004-04-29 at 17:44:30 [+0200], Laura McCord wrote:
>  
> select
> '<dtml-var name="userid" sql_quote>',
> '<dtml-var name="username" sql_quote>',
> '<dtml-var name="useremail" sql_quote>',
> '<dtml-var name="useroffice" sql_quote>',
> '<dtml-var name="userpwd" sql_quote>',
> '<dtml-var name="userpwdchg" sql_quote>',
> '<dtml-var name="pcTable.pcname" sql_quote>',
> '<dtml-var name="pcmake" sql_quote>',
> '<dtml-var name="pcmodel" sql_quote>',
> '<dtml-var name="pcserial" sql_quote>',
> '<dtml-var name="pcmhz" sql_quote>',
> '<dtml-var name="pcmbram" sql_quote>',
> '<dtml-var name="pc_oem_os" sql_quote>',
> '<dtml-var name="pc_oem_os_key" sql_quote>',
> '<dtml-var name="pc_ip_add" sql_quote>'
>  from users left join pcTable on
> users.pcname = pcTable.pcname
> where
> userid = <dtml-var name="userid" sql_quote>

eek! DTML strikes again!

Laura,

ZSQL methods work best when there is basically very little DTML in them. 
What your doing wrong is passing in variables as table attibutes and then 
quoting them twice! The result is going to be something like 

SELECT '\'Jones\'' FROM users... 

which will confuse most databases I think!

The following snippet should work for you
SELECT
userid,
username
FROM users
LEFT JOIN pcTable ON
(users.pcname = pcTable.pcname)
WHERE
userid = <dtml-sqlvar name="userid" type="int">

Note, I prefer to use sqlvar in ZSQLs and <dtml-var sql_quote> only when I 
really need it.

Charlie
-- 
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::