This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
- # announcements (43)
- # architecture (4)
- # beginners (115)
- # calva (6)
- # cider (4)
- # circleci (4)
- # clara (3)
- # clj-kondo (6)
- # cljs-dev (10)
- # cljsrn (1)
- # clojars (1)
- # clojure (108)
- # clojure-boston (1)
- # clojure-dev (4)
- # clojure-europe (5)
- # clojure-italy (6)
- # clojure-nl (13)
- # clojure-uk (17)
- # clojurescript (47)
- # core-async (2)
- # cursive (13)
- # data-science (1)
- # datavis (15)
- # datomic (12)
- # graphql (5)
- # juxt (10)
- # kaocha (20)
- # lumo (1)
- # off-topic (27)
- # pedestal (4)
- # reitit (2)
- # shadow-cljs (115)
- # spacemacs (4)
- # sql (74)
- # tools-deps (79)
- # vim (15)
- # xtdb (2)
Hey everyone, I need some help with a problem I have with postgresql. I am just a beginner so I hope this is an amateur problem, but I'm getting 'This ResultSet is closed' whenever I try to insert my data from my POST request. My API code and the error I'm getting is all in this gist, its the
submit-forum-post that is problematic: https://gist.github.com/Ashe/ff245da7595a7afde100d896e3dc64ce
Is it something to do with the columns I'm trying to insert into and it not being correct? I hoped that by adding
DEFAULT NOW() I wouldn't need to insert anything there, and the
PostID increments on its own so I don't need that.
PosterID can be null (for guest posts for now) so I didn't include that, so it's just the title and content
i didn't look very hard but there is somethign that might be problematic in get-forum-page
@carkh sorry I was showering. Hmm, so even though the error appears because of
submit-forum-post, you're thinking the query isn't executed from something that happens before then?
using map and queries together leads to this kind of problems, not really sure what happens here
Here's the thing though,
get-forum-page has been working perfectly and not led to any errors before
submit-forum-post and so I'm not sure. I will try that though as that does sound more secure
sorry to say that
get-forum-page doesn't seem to be the problem after using
but i've been using an ancient version of clojure.java.jdbc, so things could have changed
sql/query produces a fully realized result set, I'm not sure why you're seeing that problem. Especially in the submit function.
@seancorfield I actually got everything working. I'm not sure what the problem was, but moving to next-jdbc 'just worked' 🙂
there could have been other places, that's the trouble with lazy seqs, you never really know
Interesting. One subtle change between the libraries is that
clojure.java.jdbc wrapped everything in a transaction but
next.jdbc does not (because it should not have been necessary).
So perhaps you've encountered a bug in c.j.j. caused by its transaction handling. Can't think how tho'...
The thing was I used
next.jdbc.sql for the nice syntax, meaning that everything was pretty much exactly how it was
Yup. The main difference is that
next.jdbc really expects you to create a datasource from a db-spec at startup so you don't incur that overhead on every call (but it will do it for you if you pass it a db-spec, it's just not as efficient).
I do set up the db spec in the beginning, right now its an atom that I change at the start and continue using
clojure.java.jdbc works with a db-spec (hash map) and will create/open and close a connection on each request (how you had your program before).
next.jdbc can work with a db-spec the same way, but it's more efficient to convert that db-spec to a datasource just once at startup (by calling
next.jdbc/get-datasource) and then using that datasource object in subsequent calls (all
next.jdbc functions accept a "connectable" which can be a datasource, a connection, or a db-spec hash map).
It's even more efficient to use a connection pool (with both libraries) and
next.jdbc makes that much easier than
clojure.java.jdbc because it has
next.jdbc/->pool which supports both HikariCP and c3p0 out of the box.
So at startup you would create a connection pool directly from the db-spec hash map (instead of a simple datasource, a connection pool is a caching datasource), and then use that connection pooled datasource in all calls.
You can easily switch from your db-spec hash map to a simple datasource or a pooled datasource at any time since you would only need to modify your startup function to set the atom to whichever you wanted.
but your suggestion there will negate the need for an atom, as it'll only change on startup anyway so once it's set in stone it'll be fine
For those stuck in SQL land, what are favorite ways to model
(set :id1 :id1) as a key of a table? In other words, how do you handle order independent lookups of multiple values?
an aside, neither
(set :id1 :id1) or the closest reasonable substitute
(hash-set :id1 :id1) actually make sense...
Sure, lets say you are wanting to establish a relationship between to users, and in turn use that relationship as a key for other data. So the most straight forward solution would be make a relationship table with (id, user_id_1, user_id_2), decide that user_id_1 should also be < user_id_2, and then use the relationship id as a FK on other tables
which isn't valid, but
(hash-set :id1 :id2) is (sorry to be pedantic, I know this isn't your main point)
Ah, gotcha. Yeah, we do exactly that at work and key on a table with a pair of member IDs ordered so the smallest is first.
(jdbc/execute! ds (into ["select * from some_table where member_a = ? and member_b = ?"] (sort [member1-id member2-id])))
this reminds me of a separate thing - is there a way in clojure.java.jdbc to feed a collection of values to an
in (...) sql clause?
You could use HoneySQL to generate your SQL 🙂 or use
(into [(str "select ... where col in (" (str/join "," (repeat (count ids) "?") ")")] ids)
Inserts have the same "issue" in terms of needing a placeholder for each column value to insert. Same with updates for each column to set.
given you ended up at the same solution, perhaps I should just give up and stop wishing I could use sets as index keys like you can in Clojure…
(-> (select :*) (from :some_table) (where [:in :col some-array-var]) (h/format) (->> (jdbc/execute! ds))) off the top of my head for HoneySQL and
I love that HoneySQL lets you programmatically combine query fragments... here's a (fairly horrendous) thing I just had to write this morning...
(that builds the navigation menu structure for our in-house admin console app, based on admin permissions and whether we need to display all the sub-options for the page that lets admins manage the menu structure and permissions for other admins)
In the old version of the app (which I'm rewriting in Clojure) that was a giant conditional string building monstrosity of imperative code.
I worked on a PHP app years back with a ginormous query builder, driven by form inputs, doing nothing but string interpolation and concatenation… I feel your pain 🙂
This is ten lines longer than the original -- but the original had some hella long lines that I've expanded out vertically here.
@seancorfield hi there. i found a similar issue to @camsaul’s https://github.com/jkk/honeysql/pull/237 in clojure.java.jdbc. it's fixed here: https://github.com/walterl/java.jdbc/tree/locale-agnostic-lower-case
i'm a bit confused about the process to get that reviewed/merged. do i need to become an "official" contributor a la https://clojure.org/dev/dev#_becoming_a_contributor ?
@clojurians-slack100 For Contrib libs -- like
clojure.java.jdbc -- yes, you need a signed CA on file in order to submit a patch. You can report the issue on http://ask.clojure.org (and put it in the
java.jdbc category). That's the on-ramp for reporting issues without needing to go through the contributor stuff -- but you can't get a patch accepted that way.
The same problem will exist in
next.jdbc for the lower case result set builders. That project does accept pull requests however since it's outside Contrib.
I'll open that issue and go ahead and fix it there -- since that's the replacement for
clojure.java.jdbc and will be actively maintained.
Some DBs have case insensitive JDBC drivers. Some are case sensitive for table names but not columns. Some are case sensitive for everything.