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
[email protected]
On Nov 30, 2014, at 5:20 AM, George Neuner <[email protected]> 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
____________________
Racket Users list:
http://lists.racket-lang.org/users
