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:* [email protected] <javascript:>
> *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
> [email protected] <javascript:>.
> 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 [email protected].
For more options, visit https://groups.google.com/d/optout.
