Preparing statement with lists

simkoc commented 4 years ago

I have the following issue:

(dbi:with-connection (con :postgres
                          :username "test"
                          :database-name "test")
  (dbi:execute
     (dbi:prepare con
                  "SELECT a,
                          b
                   FROM T
                   WHERE c = ? AND
                         a IN ? AND")
     2
     (list "1" "2")))

results in

DB Error: syntax error at or near "$2" (Code: 42601)

But

(dbi:with-connection (con :postgres
                          :username "rc-study-manager"
                          :database-name "rc-study")
  (dbi:execute
     (dbi:prepare con
                  "SELECT a,
                          b
                   FROM T
                   WHERE c = ? AND
                         a IN ('1', '2')")
     2))

does not.

How can I prepare a statement when I have list(s) as parameters?

kat-co commented 4 years ago

Please try this:

(dbi:with-connection (con :postgres
                          :username "test"
                          :database-name "test")
  (apply #'dbi:execute
         (dbi:prepare con
                      "SELECT a,
                          b
                   FROM T
                   WHERE c = ? AND
                         a IN ? AND")
         2
         (list "1" "2")))

simkoc commented 4 years ago

Sorry for the delay. I still get the same exception.

fukamachi / cl-dbi

Preparing statement with lists #59