[racket] db: SQL in clause

Hi Sharon,

On 12/2/2014 12:32 AM, Sharon Tuttle wrote:
> Quick question: did you perhaps try putting single quotes around the $1?
>
> where id in '$1'

That also gives a syntax error: quoting the identifier results in a SQL 
string literal rather than a variable substitution.

According to SQL and the Postgresql manual, the syntax should be "... in 
($1)" because IN takes a subquery or equivalent.   However, working with 
various SQL libraries, I have found that the correct syntax can vary 
depending on how the library assembles the query. And, of course, 
libraries sometimes have gaps - but I have trouble believing that in 
this case because racket/db has around a long time and IN is standard 
SQL (from 1986).

Whatever the case, I can't figure out how to make  IN work passing a 
list or array.   From a bit of experimenting, it appears that in context 
of the IN clause, arguments can only be bound to single values: e.g., I 
can do

     (set! sql-cmd "select * from contacts where id in ($1,$2)" )
     (set! result (query db sql-cmd 42 101))

but I can't do

     (set! sql-cmd "select * from contacts where id in ($1)" )
     (set! result (query db sql-cmd  (list 42 101)))

=> query: cannot convert given value to SQL type
   given: '(14 20)
   type: int4
   expected: int32?
   dialect: PostgreSQL

I get the same error passing a pg-array.

However, if I convert the IN to  "= ANY"  then passing it a list or 
array works.

Maybe a bug?
George


> ...my experience is with Oracle SQL rather than Postgresql, and I haven't tried it from Racket, but it might be worth a try.
>
> -- Sharon Tuttle
>
> --
> Sharon M. Tuttle, Professor
> Department of Computer Science
> Humboldt State University, Arcata, CA
> sharon.tuttle at humboldt.edu
>
>
> On Nov 30, 2014, at 5:20 AM, George Neuner <gneuner2 at comcast.net> wrote:
>
> > Hi all,
> >
> > I'm using Postgresql 9.3.5 and I keep getting a syntax error trying to pass a list of identifiers to an "in" clause of a select.
> >
> >    (set! sql-cmd "select * from contacts where id in $1" )
> >    (set! result (query db sql-cmd (list 42 101)))
> >
> > I've tried :
> >    where id in $1
> >    where id in ($1)
> >    where id in (values $1)
> >    where id in (values [$1])
> >
> > and also tried converting the list to a pg-array for the values clause.
> >
> >
> > I have worked around it using "= any" and a pg-array, e.g.,
> >
> >    (set! sql-cmd "select * from contacts where id = any ($1)" )
> >    (set! result (query db sql-cmd (list->pg-array (list 42 101))))
> >
> > but is there any way to actually use an "in" clause?
> > Thanks,
> > George
> >
> > ____________________
> > Racket Users list:
> > http://lists.racket-lang.org/users
>