Hello.
I'm trying to use 'exists' keyword from the DB2 database. I know that web2py
doesn't support that natively so I tried to pass the additional condition in
as a string.
However I struck a problem that the same table used in the same query 3
times (query greatly simplified here but it still has the problem):
select NAS.ip from NAS n, SERVICE s where s.intval=n.id and exists (select
s1.id from service s1,service s2 where s.par_id=s1.par_id and
s1.par_id=s2.par_id s1.dict=123 and s2.dict=456);
so I came out with the web2py Set like this:
q = (db2.SERVICE.intval==db2.NAS.id)&("exists (select s1.id from service
s1,service s2 where SERVICE.par_id=s1.par_id and s1.par_id=s2.par_id
s1.dict=123 and s2.dict=456)")
result=db2(q).select(db2.NAS.ip)
However it fails with the KeyError: 's1' due to parsing the string and
finding s1.something and s2.something and collecting s1 and s2 as
tablenames.
Leaving aside questioning the need for _parsing_generated_string_ I came out
with the following fix to sql.py:
def parse_tablenames(text):
text = regex_quotes.sub('', text)
while 1:
i = text.find('IN (SELECT ')
+ if i == -1:
+ i = text.find('EXISTS (SELECT ')
if i == -1:
break
...
and slightly modifying the Set by making two words uppercase:
q = (db2.SERVICE.intval==db2.NAS.id)&("EXISTS (SELECT s1.id from service
s1,service s2 where SERVICE.par_id=s1.par_id and s1.par_id=s2.par_id
s1.dict=123 and s2.dict=456)")
--
Regards
Alexey
--
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en.
