jdbc and postgresql type problem

[Wilfred]

Hi all

I've started a very simple compojure project to wet my feet with
Clojure. I've written a simple model changing function:

(defn approve! [id]
(sql/with-connection db
(sql/transaction
(sql/update-values :comments
["id = ?" id]
{:approved true}))))

but I can't make it work. The exception thrown is:

#<BatchUpdateException java.sql.BatchUpdateException: Batch entry 0
UPDATE comments SET approved='1' WHERE id = '1' was aborted. Call
getNextException to see the cause.>

calling getNextException gives:

#<PSQLException org.postgresql.util.PSQLException: ERROR: operator
does not exist: integer = character varying
Hint: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
Position: 42>

I'm at a bit of a loss. "UPDATE comments SET approved='1' WHERE id =
'1'" works fine in psql. approved is a boolean field, so is this an
issue with the SQL library producing incorrect SQL?

Full code is at https://github.com/Wilfred/blog-comments and any
pointers would be much appreciated.

[Sean Corfield]

I don't (yet) have a PostgreSQL environment to test java.jdbc on but
I'm planning to do that soon. I've also talked to Aaron about how we
can set up DBs for automated testing on build.clojure.org so java.jdbc
can have "real" tests that I can run locally and which will also still
run on the build system. I'll also try to find time this week to test
your app on MySQL to see if it's specific to PostgreSQL or not.

Sean

> --
> You received this message because you are subscribed to the Google
> Groups "Clojure" group.
> To post to this group, send email to clo...@googlegroups.com
> Note that posts from new members are moderated - please be patient with your first post.
> To unsubscribe from this group, send email to
> clojure+u...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/clojure?hl=en

--
Sean A Corfield -- (904) 302-SEAN
An Architect's View -- http://corfield.org/
World Singles, LLC. -- http://worldsingles.com/
Railo Technologies, Inc. -- http://www.getrailo.com/

"Perfection is the enemy of the good."
-- Gustave Flaubert, French realist novelist (1821-1880)

[Brian Carper]

On Jul 10, 4:52 pm, Wilfred <yowilf...@gmail.com> wrote:
> I'm at a bit of a loss. "UPDATE comments SET approved='1' WHERE id =
> '1'" works fine in psql. approved is a boolean field, so is this an
> issue with the SQL library producing incorrect SQL?

I think this is an oddity with the JDBC driver for PostgreSQL. The
driver doesn't seem to like doing automatic typecasting. Try this:

(sql/update-values :comments
["id = cast(? as integer)" id]
;; alternatively "id = ?::integer"
{:approved true})

Or you could (Integer/parseInt id) to do the cast before you pass it
as parameter, though in either case you should validate your id before
casting, if you want to avoid dying on poorly-formed input.

--Brian

[Aaron Bedra]

Silly question, but which version of java.jdbc are you using? Up until
0.0.3 I had no end of troubles with psql because of the batchExecute issue.

Cheers,

Aaron Bedra
--
Clojure/core
http://clojure.com

[Brian Carper]

On Jul 11, 6:41 am, Aaron Bedra <aaron.be...@gmail.com> wrote:
> Silly question, but which version of java.jdbc are you using?  Up until
> 0.0.3 I had no end of troubles with psql because of the batchExecute issue.

I tested Wilfred's code with 0.0.3-SNAPSHOT.

--Brian

[Wilfred]

Perfect, many thanks.