So the limitation was my capability to write a SQL 2003 compliant select statement ;-)
On Apr 29, 11:59 pm, Hans <johann.scheibelho...@easytouch-edv.com> wrote: > Yarko, > > deep down in the reference manual of mysql 5.1 I've found the reason > for the issue,http://dev.mysql.com/doc/refman/5.1/en/join.html. > > Basically as of mysql version 5.0.12 there where some changes > introduced which are not backward compatible (on older mysql versions > the select/joins work on newer I get the described error). The change > was caused by mysql moving to SQL 2003 compliance. > > Thanks god the docu and manuals of mysql are very good and it was easy > to re-write the select command in order to work on SQL 2003 based on > the information in the docu. Basically it was down to avoiding the > comma join which causes in combination with ON joins headache as of > SQL 2003. > > In my case the solution looks like: > > records=SQLTABLE(db(db.model_master.id==mid).select( > db.model_master.code, > db.subsidary.name, > db.ipst.id, > ips1.id, > ips2.id, > ips3.id, > ips4.id, > ips5.id, > left=[db.subsidary.on(db.subsidary.id>0), > db.ipst.on(rows_ipst), > ips1.on(rows_ips1), > ips2.on(rows_ips2), > ips3.on(rows_ips3), > ips4.on(rows_ips4), > ips5.on(rows_ips5) > ], > orderby=db.model_master.code|db.subsidary.line), > headers=headers) > > Here db.subsidary is not a comma join any more but a join with ON > clause - making it SQL 2003 compliant. > > Hans > > On Apr 29, 4:42 pm, Yarko Tymciurak <yark...@gmail.com> wrote: > > > I'd be interested to hear a little more about what the mysql issue is... > > (I don't use, but am/will be forced to on one work server, so want to hear > > what others are going thru) > > > Thanks, > > - Yarko > > > 2009/4/29 mdipierro <mdipie...@cs.depaul.edu> > > > > there lots of mysql issues unfortunately. > > > > On Apr 29, 7:08 am, Hans <johann.scheibelho...@easytouch-edv.com> > > > wrote: > > > > Update: I've found that its a mysql issue not a web2py one. > > > > > On Apr 28, 4:52 pm, Hans <johann.scheibelho...@easytouch-edv.com> > > > > wrote: > > > > > > I'm experiencing a strange behavior within the code below. I tried to > > > > > split the problem in the smallest pieces which I'm able to debug. My > > > > > interpretation of the findings is that most likely I run into a web2py > > > > > internal variable length issue/limitation. Let me tell you why I think > > > > > that: > > > > > > The tables ips1, ips2, ips3, ips4 and ips5 are identical aliases of > > > > > db.ipst. > > > > > There code works without error like it is below (watch the 3 commented > > > > > lines!). > > > > > The code also works without error if I comment out *ANY 3* out of the > > > > > db.ipst.id and its 5 aliases. > > > > > Each code line below (also the 3 commented ones) works without error > > > > > as long as any 3 other lines from db.ipst.id and its 5 aliases are > > > > > commented out - all combinations work. > > > > > The problem only occurs if I have less than 3 of the lines below > > > > > commented out. > > > > > > Unfortunately I'm not able to debug into web2py framework code - and > > > > > despite hours of finding a solution for that I could not find one. And > > > > > based on the tools I have I'm at the end of all ideas I had. > > > > > > If it helps I can also send you login information (and instructions to > > > > > get to the function with the issue) for the development server via > > > > > personal email upon request. > > > > > > I'm using MySQL and Web2Py 1.61.1. > > > > > > records=SQLTABLE(db(db.model_master.id==mid).select( > > > > > db.model_master.code, > > > > > db.subsidary.name, > > > > > db.ipst.id, > > > > > ips1.id, > > > > > # ips2.id, > > > > > # ips3.id, > > > > > # ips4.id, > > > > > ips5.id, > > > > > left=[db.ipst.on(rows_ipst), > > > > > ips1.on(rows_ips1), > > > > > ips2.on(rows_ips2), > > > > > ips3.on(rows_ips3), > > > > > ips4.on(rows_ips4), > > > > > ips5.on(rows_ips5) > > > > > ], > > > > > orderby=db.model_master.code|db.subsidary.line), > > > > > headers=headers) > > > > > > Error traceback > > > > > > Traceback (most recent call last): > > > > > File "/usr/lib/web2py/gluon/restricted.py", line 98, in restricted > > > > > exec ccode in environment > > > > > File "/usr/lib/web2py/applications/ips/controllers/default.py", line > > > > > 657, in <module> > > > > > File "/usr/lib/web2py/gluon/globals.py", line 75, in <lambda> > > > > > self._caller = lambda f: f() > > > > > File "/usr/lib/web2py/applications/plugin_t2/modules/t2.py", line > > > > > 1188, in h > > > > > return f(*a,**b) > > > > > File "/usr/lib/web2py/applications/ips/controllers/default.py", line > > > > > 537, in fcc > > > > > orderby=db.model_master.code|db.subsidary.line), > > > > > File "/usr/lib/web2py/gluon/sql.py", line 1977, in select > > > > > r = response(query) > > > > > File "/usr/lib/web2py/gluon/sql.py", line 1972, in response > > > > > self._db._execute(query) > > > > > File "/usr/lib/web2py/gluon/sql.py", line 691, in <lambda> > > > > > self._execute = lambda *a, **b: self._cursor.execute(*a,**b) > > > > > File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line > > > > > 166, in execute > > > > > self.errorhandler(self, exc, value) > > > > > File "/var/lib/python-support/python2.5/MySQLdb/connections.py", > > > > > line 35, in defaulterrorhandler > > > > > raise errorclass, errorvalue > > > > > OperationalError: (1054, "Unknown column 'subsidary.id' in 'on > > > > > clause'") > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "web2py Web Framework" group. To post to this group, send email to web2py@googlegroups.com To unsubscribe from this group, send email to web2py+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
