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

you're using map, which produces a lazy seq

there is no telling when the query will actually be executed

or consumed really

though json.write-str should consume it directly

@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

maybe get the result of query first and then map over that instead?

or something like (into [] myquery-result)

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

or doall

there are other uses of map somewhere

sorry to say that get-forum-page doesn't seem to be the problem after using doall or into

Am I using sql/insert! okay? Especially with my table?

looks ok to me

but i've been using an ancient version of clojure.java.jdbc, so things could have changed

though i haven't used it, i would recommend using @seancorfield’s next-jdbc

I was reading about that actually

Given that sql/query produces a fully realized result set, I'm not sure why you're seeing that problem. Especially in the submit function.

ah indeed, i remember being bitten by such problems in the past

@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

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

@seancorfield @carkh Thank you for your help!

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?

@risinglight Not quite sure what you are asking... can you elaborate?

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

I can’t help but think there should be a better way though

I typoed above… I was thinking (set :id1 :id2)

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])))

Or, if we have a set of IDs, (into ["select..."] (sort id-set))

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)

:/ OK

SQL requires a placeholder for each parameter...

HoneySQL hides that for you.

yeah, that makes sense

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 in.

(yeah, just tested that in the REPL and it works @noisesmith)

I love that HoneySQL lets you programmatically combine query fragments... here's a (fairly horrendous) thing I just had to write this morning...

haha, wow

(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.

(and in REPL testing I just found a bug in that code above!)

@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.

thanks 👍

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.

odd that it doesn't seem to affect sqlite o_O

JDBC is weird about case sensitivity.

Some DBs have case insensitive JDBC drivers. Some are case sensitive for table names but not columns. Some are case sensitive for everything.

https://github.com/seancorfield/next-jdbc/issues/52

ah yes. that'll do it:

user=> (Locale/forLanguageTag "tr")
#object[java.util.Locale 0x40e69487 "tr"]

user=> (clojure.string/lower-case "ID")
"ıd"

user=> (clojure.string/lower-case "id")
"id"