Fork me on GitHub
Ashley Smith11:08:33

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:

Ashley Smith11:08:18

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

Ashley Smith13:08:03

@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

Ashley Smith13:08:40

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


or something like (into [] myquery-result)

Ashley Smith13:08:52

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


there are other uses of map somewhere

Ashley Smith13:08:49

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

Ashley Smith13:08:14

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


looks ok to me


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


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

Ashley Smith13:08:10

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

Ashley Smith15:08:17

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

Ashley Smith16:08:59

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

Ashley Smith17:08:07

I do set up the db spec in the beginning, right now its an atom that I change at the start and continue using

seancorfield17:08:41 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 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.

Ashley Smith17:08:27

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

Ashley Smith17:08:05

@seancorfield @carkh Thank you for your help!

🎉 12
🥂 4

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


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.

💯 4

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


(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!)


i'm a bit confused about the process to get that reviewed/merged. do i need to become an "official" contributor a la ?


@clojurians-slack100 For Contrib libs -- like -- yes, you need a signed CA on file in order to submit a patch. You can report the issue on (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 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.


ah yes. that'll do it:

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

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

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