Fork me on GitHub
#sql
<
2019-11-08
>
markbastian16:11:07

https://github.com/seancorfield/next-jdbc/blob/master/src/next/jdbc/result_set.clj#L677 looks like it takes the schema as an option, but I'm not seeing where this gets called and it is marked as private.

seancorfield18:11:16

I'm working on documentation today so I'll make that clearer with more examples @markbastian -- thanks for the feedback!

markbastian19:11:29

BTW, I came up with an exceptionally minimalist example that works. You are welcome to use it if you want. https://github.com/markbastian/datafy-playground/blob/master/src/datafy_playground/datafy_jdbc_next.clj

seancorfield19:11:57

See the updated docs I linked to -- you would pass the :schema option to execute!. You would not call datafiable-row on its own.

seancorfield19:11:22

The result of execute! is already datafiable.

markbastian19:11:44

Cool, I'll check it out right now. Thanks!

seancorfield19:11:16

You pretty much would never call datafy/`nav` yourself -- that's generally going to be part of tooling, like REBL.

markbastian19:11:43

Yeah, I had this idea to create a web interface to navigate some data, so I guess that would be my own custom tool. Thanks for the all the help and advice.

seancorfield19:11:39

The whole result set from execute! is datafiable so as your tool calls datafy / nav on it, things will "just. work." magically.

seancorfield19:11:32

About the only time you'd ever call datafiable-row yourself is inside a reduction of plan, and even then it's only if you want an entire row realized without performing any operations on it (e.g., calling assoc would realize the row with datafiable-row anyway).

seancorfield19:11:04

Then there's datafiable-result-set which is useful for dealing with metadata pulled directly from JDBC (rather than via queries).

sonnyto16:11:05

the results of a query is namespaced with the table-name. is there a way to remove the table namespace and just have the column names?

kszabo17:11:12

(jdbc/execute-one! ["SELECT 1"] {:builder-fn next.jdbc.result-set/as-unqualified-arrays}

😎 4
4
Janne Sauvala21:11:46

I would love to see a concrete example about this in the jdbc next docs. Today I was using jdbc next for the first time and I faced this issue with qualified names. I saw :builder-fn mentioned in the docs but (because I’m a noob) I didn’t know where to give that map. Fortunately slack comes to rescue!

kszabo17:11:42

oh sorry, that’s for arrays, but you can find the other builder fns in that ns

kszabo17:11:47

it’s also in the README

dharrigan18:11:02

I use {:builder-fn rs/as-unqualified-lower-maps}

seancorfield18:11:06

FYI @sonnyto clojure.java.jdbc used to make column names unique by adding _N to them (`_1`, _2, etc) when there were collisions. next.jdbc does not do that, so if you use unqualified builders, any column conflicts will result in "missing columns" (since it's "just" a hash map). If you use qualified columns -- which I _strongly recommend_ -- then they will remain unique.

😎 4
sonnyto18:11:23

good to know. I'm migrating code from clojure.java.jdbc to next.jdbc

seancorfield18:11:34

Yeah, it's just something to watch out for. It's better practice to explicitly alias overlapping column names in your SQL than rely on the auto-numbering behavior of c.j.j. of course.

sonnyto18:11:36

thanks for building next.jdbc!

Jakub Holý (HolyJak)20:11:21

That implies you strongly recommend NOT to use Oracle :rolling_on_the_floor_laughing: I wholeheartedly agree!

seancorfield20:11:55

@U0522TWDA Am I that transparent? 😆

😁 4
seancorfield18:11:52

Consider select a.*, b.* from table1 a join table 2 b on a.id = b.someid where b.col = ? -- if both table1 and table2 contain id (very likely!), then you will end up with just one :id column in your rows (which will be b.id I expect).

😎 4
seancorfield18:11:08

If you use qualified columns, you'll get both :a/id and :b/id.

seancorfield18:11:43

(I plan to add that as a caveat in both the migration guide and the result set builder section of the docs today)

seancorfield19:11:29

Any other documentation improvements that folks would like to see, while I'm doing a "documentation Friday"? https://github.com/seancorfield/next-jdbc/commits/master

❤️ 4
markbastian19:11:37

When migrating from java.jdbc to next.jdbc is there a preferred replacement for the create-table-ddl functions? Should I be using honeysql instead?

seancorfield19:11:50

create-table-ddl was a pretty poor helper -- you might just as well write it all out as a string.

👍 4
seancorfield19:11:32

(jdbc/execute-one! ds ["
create table foo (
  id int auto_increment,
  name varchar(32),
  primary key(id)
) engine=innodb"])
Aside from string-bashing, I think create-table-ddl was actually worse to use 😐

markbastian19:11:12

Yeah, it doesn't buy you a whole lot over just rolling it on your own.

markbastian19:11:44

One more easy one: How do I convert this java.jdbc connection map {:connection-uri "jdbc:h2:mem:mem_only"} to jdbc.next. I've got {:dbtype "h2" :dbname "mem:mem_only"} but that isn't right. I've tried many of the options at https://github.com/seancorfield/next-jdbc/blob/master/doc/all-the-options.md but no luck. I need to put mem into one of the keys, but not sure which.

markbastian19:11:13

I expect it to be {:dbtype "h2" :dbname "mem_only" :some-key "mem"}. Just trying to figure out what :some-key is.

seancorfield20:11:20

{:dbtype "h2:mem" :dbname "mem_only"} would be the same answer for clojure.java.jdbc BTW

markbastian21:11:07

Perfect. I just hadn't tried putting the mem on the dbtype, which makes sense.

seancorfield20:11:59

@markbastian {:dbtype "h2:mem" :dbname "mem_only"}

seancorfield20:11:28

OK folks, here's a new feature I'm sketching out https://github.com/seancorfield/next-jdbc/commit/b2b7696973e96581450f9997af8b34f60c32bc87 -- the idea is that you can use it for logging (see the test added in that commit) so builder-adapter is a terrible name for it but I'm not sure if logging-adapter is descriptive enough... Suggestions?

dcj22:11:41

builder-hooks ? 🙂

Janne Sauvala21:11:46

I would love to see a concrete example about this in the jdbc next docs. Today I was using jdbc next for the first time and I faced this issue with qualified names. I saw :builder-fn mentioned in the docs but (because I’m a noob) I didn’t know where to give that map. Fortunately slack comes to rescue!