Hi,
I'm playing around with contrib.sql for the last few days and I was
quite enthusiastic about it but for now I'm struggling with problems I
encountered in no other language before.
My first problem was to save a timestamp value. I defined a field in
pgsql as "timestamp" and wasn't able to store a simple timestamp value
because I got type mismatch errors and it also displayed the statement
which I could copy and execute successfully in pgadmin which confused
me a lot.
I solved this issue by defining:
(defn timestamp [time-string] (java.sql.Timestamp/valueOf time-
string))
so I could successfully save timestamps to my db by "casting" my
strings to a java timestamp (timestamp (params :foobar)).
It really took me some time to figure this out and I also had an
unpleasant chat with hiredman on freenode though he helped me solving
this issue (thanks).
But my current problem I'm not able to solve is that I can't update
values in my database by using the "update-values" function.
I have a very simple definition to update just a tiny value in my db:
(defn set-todo-done [id true-or-false] (sql/update-values :todos
["id=?" id] {:done true-or-false}))
And I call it like that:
(GET "/todo-done"
(if (master-password-correct? (params :mp))
(if-not (empty? (params :id))
(try
(sql/with-connection dbc
(sql/transaction
(set-todo-done (params :id) 1)))
(html "ok")
(catch Exception _)))))
And I get the following error when this gets executed at runtime:
Update counts:
BatchUpdateException:
Message: Batch entry 0 UPDATE todos SET done='1' WHERE id='0' was
aborted. Call getNextException to see the cause.
SQLState: 42883
Error Code: 0
PSQLException:
Message: 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: 34
SQLState: 42883
Error Code: 0
Position 34? Seems to be the "where id='0'" part. This statement like
shown there is totally correct and works on that version of pgsql I am
using (8.4.2) so I don't see any reason why it shouldn't work here.
I really tried out several variants. I tried to "cast" the id to
integer with (int (params :id)) or with (java.lang.Integer. (.parseInt
(params :id))) and I also tried to do it with the ":done" value but
whenever I do that with the id, the where-part, I don't even get an
exception but also no change in the database. In fact contrib.sql then
seems to _not_ hit the database at all.
I am having the "postgresql-8.4-701.jdbc4.jar" on my classpath and
there is no other jar for that. I tried out several other versions of
the jdbc package for pgsql but they all show me this error.
// Update!
While I was writing this text I had another idea that really solved
the issue.
I simply changed my update procedure like that:
(defn set-todo-done [id true-or-false] (sql/update-values :todos
["id=?::integer" id] {:done true-or-false}))
You see the typecast inside the vector? Ya, that really solved the
issue but it's more a workaround than a good solution and I'm really
angry now because the solution was so simple. I do know how to
typecast data in pgsql but well, I haven't seen a need for a typecast
in any example and because I also can enclose integers in '' I thought
it's ok! It's not!
With that knowledge I probably can also omit my java.sql.Timestamp
"cast" but I'm too tired to change anything now :P. When I asked for
help today in the irc channel, I got a hint to "clojureql".
The creator of clojureql told me about his project and while I browsed
the source I noticed something like (.setInt ...), (.set*...) etc...
He seems to "cast" the values for the current statement. I don't know
the source of contrib.sql by heart but a quick look told me there is
nothing like that.
However, what I complain about is that in no fracking example any
typecast was used and I assumed this is normal as supposed to other
interfaces/libs I've used in the past.
I'll write it down tomorrow on my blog and hopefully someone who has
the same problems will gain something from it. One user spending hours
on that should be enough :).
I'm really tired now...please excuse anything.
Thanks in advance!
Andreas Schipplock.
--
You received this message because you are subscribed to the Google
Groups "Clojure" group.
To post to this group, send email to clojure@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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
