Fork me on GitHub
#sql
<
2019-08-08
>
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: https://gist.github.com/Ashe/ff245da7595a7afde100d896e3dc64ce

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

carkh12:08:36

i didn't look very hard but there is somethign that might be problematic in get-forum-page

carkh12:08:47

you're using map, which produces a lazy seq

carkh12:08:25

there is no telling when the query will actually be executed

carkh12:08:30

or consumed really

carkh12:08:34

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?

carkh13:08:01

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?

carkh13:08:15

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

carkh13:08:08

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?

carkh13:08:16

looks ok to me

carkh13:08:56

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

carkh13:08:49

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

Ashley Smith13:08:10

I was reading about that actually

seancorfield15:08:48

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.

carkh15:08:07

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' 🙂

carkh15:08:24

there could have been other places, that's the trouble with lazy seqs, you never really know

seancorfield15:08:40

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

seancorfield15:08:54

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

seancorfield16:08:55

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

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

seancorfield17:08:39

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.

seancorfield17:08:35

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.

seancorfield17:08:23

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
kenj19:08:26

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?

seancorfield19:08:29

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

noisesmith19:08:01

an aside, neither (set :id1 :id1) or the closest reasonable substitute (hash-set :id1 :id1) actually make sense...

kenj19:08:15

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

kenj19:08:21

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

kenj19:08:35

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

noisesmith19:08:17

which isn't valid, but (hash-set :id1 :id2) is (sorry to be pedantic, I know this isn't your main point)

seancorfield19:08:40

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.

seancorfield19:08:01

(jdbc/execute! ds (into ["select * from some_table where member_a = ? and member_b = ?"] (sort [member1-id member2-id])))

seancorfield19:08:31

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

noisesmith19:08:27

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?

seancorfield19:08:06

You could use HoneySQL to generate your SQL 🙂 or use (into [(str "select ... where col in (" (str/join "," (repeat (count ids) "?") ")")] ids)

seancorfield19:08:24

SQL requires a placeholder for each parameter...

seancorfield19:08:34

HoneySQL hides that for you.

noisesmith19:08:38

yeah, that makes sense

seancorfield19:08:25

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.

kenj19:08:18

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…

seancorfield19:08:50

(-> (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
seancorfield19:08:36

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

seancorfield19:08:46

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

seancorfield19:08:50

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

seancorfield19:08:46

In the old version of the app (which I'm rewriting in Clojure) that was a giant conditional string building monstrosity of imperative code.

kenj19:08:27

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 🙂

seancorfield19:08:42

This is ten lines longer than the original -- but the original had some hella long lines that I've expanded out vertically here.

seancorfield20:08:11

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

walterl23:08:54

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 ?

seancorfield23:08:16

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

walterl23:08:17

thanks 👍

seancorfield23:08:16

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.

seancorfield23:08:46

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.

walterl23:08:40

odd that it doesn't seem to affect sqlite o_O

seancorfield23:08:37

JDBC is weird about case sensitivity.

seancorfield23:08:12

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

walterl23:08:58

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"