Yes it works from the DB’s interface and yes, I’m using MySQL. From: Brian M Sent: Wednesday, March 11, 2015 3:18 PM To: web2py@googlegroups.com Subject: Re: [web2py] Re: Running multiple lines of SQL in executesql
Strange. Does that same query work if you enter it directly in your DB's interface? I'm guessing from the link you provided that you're using MySQL; I've only done it with MS SQL and SQLite but I'd think that the triple quoting technique should work with other databases too. On Wednesday, March 11, 2015 at 11:09:58 AM UTC-5, naveed wrote: Thank you, I tried this: db.executesql("""set @num := 0, @type := ''; select type, variety, price from ( select type, variety, price, @num := if(@type = type, @num + 1, 1) as row_number, @type := type as dummy from fruits order by type, price ) as x where x.row_number <= 2;""")But it returns a null. Sent: Tuesday, March 10, 2015 9:42 PM To: web...@googlegroups.com Subject: [web2py] Re: Running multiple lines of SQL in executesql Actually just surround your big long multi line SQL statement with triple quotes and it will work fine. I do that all the time. No need for the \ at each line break then either. You can declare and set your @variables all within one executesql query too. On Tuesday, March 10, 2015 at 2:36:28 PM UTC-5, naveed wrote: I need to execute multiple lines of SQL in web2py, something like this: db.executesql("set @num := 0, @type := '';")\ rows = db.executesql("select type, variety, price\ from (\ select type, variety, price,\ @num := if(@type = type, @num + 1, 1) as row_number,\ @type := type as dummy\ from fruits\ order by type, price\ ) as x where x.row_number <= 2;")But, when I run it I don't get expected results in web2py (the variables don't get initialized), even though it works from the mysql console. What am I doing wrong? They don't work, even if I put the variable initialization in the same call. This piece of SQL is from here: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/SQEWZzMQ4Gw/unsubscribe. To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/SQEWZzMQ4Gw/unsubscribe. To unsubscribe from this group and all its topics, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.