Hi, I have a legacy database which uses an internal convention of naming the primary key of a table as 'code'. When executing queries which include joins, it is necessary to add a table name prefix in order to make them unambiguous. For example: SELECT foo.*, bar.desc FROM foo INNER JOIN bar ON foo.bar = bar.code WHERE foo.code = some_value I created a Z SQL Method called listFoo thus: -8<------ Parameter: foo.code SELECT foo.*, bar.desc FROM foo INNER JOIN bar ON foo.bar = bar.code <dtml-sqlgroup where> <dtml-sqltest foo.code op="eq" type="int"> </dtml-sqlgroup> -8<------- This works fine when I test it. However, I'm having a bit of trouble calling from my dtml document. I'm trying to use a line like: <dtml-in expr="listFoo(foo.code=code)"> which bombs (naturally). So how do I go about this? I'm sorry if this is a FAQ, I'm a Zope newbie -- I've been using it precisely 5 days. TIA -- Biju -- ------------------------------------------------- Biju Chacko | biju@exocore.com (work) Exocore Consulting | biju_chacko@yahoo.com (play) Bangalore, India | http://www.exocore.com ------------------------------------------------- You shouldn't wallow in self-pity. But it's OK to put your feet in it and swish them around a little. -- Guindon
SELECT foo.*, bar.desc FROM foo INNER JOIN bar ON foo.bar = bar.code <dtml-sqlgroup where> <dtml-sqltest foo.code op="eq" type="int"> </dtml-sqlgroup>
-8<-------
This works fine when I test it. However, I'm having a bit of trouble calling from my dtml document. I'm trying to use a line like:
<dtml-in expr="listFoo(foo.code=code)">
which bombs (naturally).
Biju -- First of all, rename your parameter to simply code (or foo_code if you want). While it's not impossible to have a parameter have a dot in it, you often have to provide a workaround for this, since Python thinks that foo.code == subobject code of obj foo. Often, your parameter for the ZSQLMethod has the same name as the column you want to compare it to; in your case, your parameter is named "code" but the column name is "foo.code". <dtml-sqltest code col="foo.code" op="eq" type="int"> This will resolve to WHERE foo.code=<dtml-sqlvar code type=int> which is what you want Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Knowledge Management & Technology Consultant
Biju Chacko writes:
I created a Z SQL Method called listFoo thus:
-8<------
Parameter: foo.code
SELECT foo.*, bar.desc FROM foo INNER JOIN bar ON foo.bar = bar.code <dtml-sqlgroup where> <dtml-sqltest foo.code op="eq" type="int"> </dtml-sqlgroup>
Use simple names (valid as Python names) as parameters. You can use the "column" attribute of "dtml-sql*" tags to use different column names for the generated SQL. Dieter
participants (3)
-
Biju Chacko -
Dieter Maurer -
Joel Burton