Works like a charm. Thanks Massimo.
> why are you doing this again?
I have some fairly complex queries and it helps me in debugging if I
can get a working baseline in SQL (which I know fairly well) before
transforming to web2py DAL syntax (which I'm just learning).
On Apr 6, 12:52 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> This should work
> rows=db.executesql('SELECT ... \
> FROM ... \
> WHERE table1.column = %s AND table2.column = %s;' % \
> (sanitize(session.mysession1,'string','sqlite'),
> sanitize(session.mysession2,'string','sqlite')))
>
> why are you doing this again?
>
> On Apr 5, 7:17 pm, ctalley <ctal...@caci.com> wrote:
>
>
>
> > Just getting back to this after a week.
>
> > Massimo, the syntax you provided for the case indicated does indeed
> > work. Thanks. Now I'm trying a variation of that case - adding an
> > AND to the WHERE - and can't seem to get the syntax quite right
> > (again).
>
> > For the record, this is the case that works...
> > rows=db.executesql('SELECT ... \
> > FROM ... \
> > WHERE table1.column = %s;' % sanitize
> > (session.mysession1,'string','sqlite'))
>
> > ... and this is the variation that's giving me trouble...
>
> > rows=db.executesql('SELECT ... \
> > FROM ... \
> > WHERE table1.column = %s % sanitize
> > (session.mysession1,'string','sqlite') \
> > AND table2.column = %s;' % sanitize
> > (session.mysession2,'string','sqlite'))
>
> > Above is one way I tried dealing with it, with no luck. I tried some
> > other things too, like one sanitize() at the end with multiple
> > arguments. Also, no luck.
>
> > Any assistance is appreciated.
>
> > On Mar 30, 10:03 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> > > On Mar 30, 8:51 am, ctalley <ctal...@caci.com> wrote:
>
> > > > Is it possible to use a session variable as a parameter in a SQL
> > > > "WHERE" clause? See example below.
>
> > > > #assign value to session variable
> > > > session.mysession = ...
>
> > > > #use session variable in web2py DAL query
> > > > #*this works*
> > > > rows=db(db.table.column==session.mysession).select(...)
>
> > > this you can do
>
> > > > #use session variable in SQL query
> > > > #*this doesn't work* - 'no such column' error
> > > > #is there any way to do this?
> > > > rows=db.executesql('SELECT ...
> > > > FROM ...
> > > > WHERE table.column = session.mysession;')
>
> > > this you can but the syntax has to be different and you have to do in
> > > a way safe to prevent SQL injections. Should be:
>
> > > from gluon.sql import sql_represent as sanitize
> > > rows=db.executesql('SELECT ...
> > > FROM ...
> > > WHERE table.column = %s;' % sanitize
> > > (session.mysession,'string','sqlite') )
>
> > > > The obvious answer here is to just use what works (the DAL). However,
> > > > I have some very complex queries already written in SQL and would
> > > > rather not have to go to the trouble of morphing them into web2py DAL
> > > > syntax.
>
> > > > Thanks.- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
