Parameterized query with clojure.contrib.sql

[Greg Harman]

Would someone mind posting an example of a parameterized query using
clojure.contrib.sql? There are examples in the source of non-
parameterized queries, and do-prepared is used to parameterize values
for inserts, but I can't seem to get my form quite right for a query.

thanks,
Greg

[Greg Harman]

I couldn't figure out how to do this with the included functions/
macros in clojure.contrib.sql so I massaged with-results and do-
prepared together to get this macro (with supporting fn), which seems
to work. Useful addition for contrib.sql?

;; query-with-results should work just like with-results, only
parameterizing query variables.

(defn- get-ps
"Generate a prepared statement with a vector of parameters to
support the query."
[sql params]
(let [ps (.prepareStatement (connection) sql)]
(doseq [[index value] (map vector (iterate inc 1) params)]
(.setObject ps index value))
ps))

(defmacro query-with-results
"Executes a query with parameterized results and then evaluates body
with results bound to a seq of
the results.

Example usage: (with-connection db (query-with-results res \"select
* from mytable
where name = ? or id = ?\" [\"Foo\" 3]
(println res)))"
[results sql params & body]
`(with-open [stmt# (get-ps ~sql ~params)
rset# (.executeQuery stmt#)]
(let [~results (resultset-seq rset#)]
~@body)))

I would have liked to eliminate the helper function and just get this
all into the macro, but I burned my allotted time on it before I got
that working...

-Greg

[Stephen C. Gilardi]

On Jan 12, 2009, at 4:57 PM, Greg Harman wrote:

I couldn't figure out how to do this with the included functions/
macros in clojure.contrib.sql so I massaged with-results and do-
prepared together to get this macro (with supporting fn), which seems
to work. Useful addition for contrib.sql?

Hi Greg,

I checked in changes to clojure.contrib.sql that are intended to address this. If you get a chance to try the updated lib, I'd appreciate hearing how it works for you and any suggestions or bug reports you may have.

Thanks,

--Steve

[Greg Harman]

Steve,

Thanks much for your work. The new with-query-results seems to work
quite well.

Your timing is impeccable with this set of changes: I had just
finished hacking out a (much uglier) version of update-values as well.
(I'll switch over to using the clojure.contrib.sql versions now for a
number of reasons).

One suggestion/request for your next set of updates: insert-or-update-
values, which will determine whether the given row exists in the
database already, and then issue the appropriate insert/update
statement as the case may be. This is a common paradigm that I find
myself doing manually all the time with database coding in any
language.

I had hacked up a version of insert-or-update-values that worked (with
my version of update-values I mentioned above), but it's much too ugly
to post here. :-)

-Greg

On Jan 14, 12:03 am, "Stephen C. Gilardi" <squee...@mac.com> wrote:
> Hi Greg,
>
> I checked in changes to clojure.contrib.sql that are intended to  
> address this. If you get a chance to try the updated lib, I'd  
> appreciate hearing how it works for you and any suggestions or bug  
> reports you may have.
>
> Thanks,
>
> --Steve
>

>  smime.p7s
> 3KViewDownload

[Stephen C. Gilardi]

On Jan 14, 2009, at 1:26 PM, Greg Harman wrote:

Thanks much for your work. The new with-query-results seems to work
quite well.

You're quite welcome. I'm glad to hear it!

Your timing is impeccable with this set of changes: I had just
finished hacking out a (much uglier) version of update-values as well.
(I'll switch over to using the clojure.contrib.sql versions now for a
number of reasons).

Great!

One suggestion/request for your next set of updates: insert-or-update-
values, which will determine whether the given row exists in the
database already, and then issue the appropriate insert/update
statement as the case may be. This is a common paradigm that I find
myself doing manually all the time with database coding in any
language.

I've added update-or-insert-values. I'd appreciate hearing how it works for you.

I had hacked up a version of insert-or-update-values that worked (with
my version of update-values I mentioned above), but it's much too ugly
to post here. :-)

:-)

--Steve

[Greg Harman]

You're my personal Santa Claus today! :-)

Confirmed present and working for both insert and update, using a 2
field where clause.

[luskwater]

I've put together two functions that handle this for me, and although
I haven't looked at recent changes to clojure.contrib.sql (which sound
interesting), perhaps there might be some interest in what I've got.

The first function takes a database connection and a SELECT statement,
returning a function representing the PreparedStatement. That
function can be called with arguments representing the question-marks
in the original SELECT statement. (It tweaks any inbound
java.util.Date args into java.sql.Date, naively.)

(let [query (rl.database/prepare-query (connection) "SELECT * FROM foo
WHERE x = ? AND y > ?")]
(doseq [foo (query "BAR" 42)]
(println foo)))

I was going to try to rig up a function in the metadata for the
returned function that would close the PreparedStatement but had real
work to do with it, so....

The second function builds on this function: it takes SQL with keyword-
style placeholders (like ActiveRecord) and
returns a query function. This function accepts a map as its
argument, and sets the parameters of the PreparedStatement using the
values in the map. This works better (or more sanely) for complex
queries in which the same value is used for multiple placeholders.
Unlike ActiveRecord, however, this uses a PreparedStatement, and so
will not accept syntax like "AND some_field IN (:bunch_of_foos)",
which I believe (in AR) works because they don't prepare the statement
but generate the SQL on the fly.

;; Example of second query with keyword parameters
(let [query (rl.sql/prepare-query (connection)
"SELECT * FROM foo
WHERE (a = :x AND b > :y)
OR (a <> :x AND b <= :y)")]
(doseq [foo (query {:x "BAR" :y 42})]
(println foo)))

On Jan 9, 5:05 pm, Greg Harman <ghar...@gmail.com> wrote: