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: [email protected]
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: [email protected]
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].
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
[email protected].
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.
