Running manual insert or update queries?

[tominated]

I'm using this in one of my projects at work, and one of the features I'm implementing needs some stuff that doesn't seem possible with the insert-record or update-values functions. I'm trying to increment a counter in a row. If I was doing it in SQL I would do something along the lines of this:

UPDATE posts
SET likes = likes + 1
WHERE id = ?

I had noticed the with-db macro, but when looking at the official clojure jdbc library, they say that with-connection is deprecated and shouldn't be used. Is there a way to just run a query like this?

Also, how would I go about doing a database transaction?

Thanks for any help! Tom

[tominated]

Actually, is there a way to just get one of the connections so I can run the newer clojure.java.jdbc functions (that require you to pass the connection in)?

[shenfeng]

java.jdbc changed a lot since the last time I read it. Some time is needed to figure out what's going on.

As for how to get the connection:

(:ds @db-factory)                       ; javax.sql.DataSource, a Connection pool
(.getConnection ^DataSource (:ds @db-factory)) ; java.sql.Connection

[tominated]

Thanks. I haven't used atoms before - will dereferencing it work in my own code, or will I have to place that in the dbcp.clj source?

[shenfeng]

(:use [org.httpkit.dbcp :only [db-factory]])

should do the trick

[tominated]

Thanks again! I just tried both out in the leiningen repl (not sure if that affects it) with the following code (db-conn is one of the lines that you posted previously)

(jdbc/query (db-conn) "SELECT * FROM posts LIMIT 1")

but I get the same error for both (although the .getConnection one takes a bit of time to actually get the connection first):

IllegalArgumentException db-spec PerThreadDataSource[opened=0, active=0, threads=[]] is missing a required parameter  clojure.java.jdbc/get-connection (jdbc.clj:221)

I had a look at the jdbc source and it seems like it wasn't picking it up as a datasource (it expects a map). I tried both wrapping it like {:datasource (:ds @db-factory)} and by just passing in the db-factory, but they also have the same error:

NullPointerException   org.postgresql.jdbc2.AbstractJdbc2Statement.replaceProcessing (AbstractJdbc2Statement.java:829)

Any ideas?

EDIT: I spotted this on the JDBC documentation - it may be helpful: http://clojure.github.io/java.jdbc/doc/clojure/java/jdbc/ConnectionPooling.html

[shenfeng]

From the source code:

(defn db-find-connection
  "Returns the current database connection (or nil if there is none)"
  ^java.sql.Connection [db]
  (and (map? db)
       (:connection db)))

;; db-conn
{:connection (.getConnection ^DataSource (:ds @db-factory))}

[shenfeng]

or from the get-connection source code:

(jdbc/query @db-factory "SELECT * FROM posts LIMIT 1")

[tominated]

Both of those still get the null pointer exception that I posted earlier. I think it may be the postgres driver being odd. I might try an earlier version

[tominated]

I just tried an earlier postgres driver and I get the same null pointer exception (but a different line of code)

[shenfeng]

have you call the use-database! ? 在 2013-5-14 下午12:29，"Tom Brunoli" notifications@github.com写道：

I just tried an earlier postgres driver and I get the same null pointer exception (but a different line of code)

— Reply to this email directly or view it on GitHubhttps://github.com/http-kit/dbcp.clj/issues/1#issuecomment-17856177 .

[tominated]

Yeah I have. Sorry about that - i forgot to mention it

[tominated]

I just realised I am an idiot. I should have read the documentation more thoroughly.

(jdbc/query @db-factory ["SELECT * FROM posts LIMIT 1"])

worked properly. I forgot to put the query in a vector.