Fork me on GitHub
Slack Archive

This page is not created by, affiliated with, or supported by Slack Technologies, Inc.

2022-07-05

Channels

#sql
<
2022-07-05
>
Eugen12:07:27

I am seeing some strange behavior with next.jdbc and h2 (might be a bug). Porting migratus from clojure.java.jdbc -> next.jdbc I encountered this issue that causes tests to fail. The next statement returns nil - though it should return a record of inserted rows 

(next.jdbc.sql/insert! (:db config) "foo_bar" {:id -1})
with trace 
TRACE t12951: (next.jdbc.sql/insert! {:dbtype "h2", :dbname "./site.db"} "foo_bar" {:id -1})
TRACE t12952: | (next.jdbc.sql/insert! {:dbtype "h2", :dbname "./site.db"} "foo_bar" {:id -1} {})
TRACE t12953: | | (next.jdbc/execute-one! {:dbtype "h2", :dbname "./site.db"} ["INSERT INTO foo_bar (id) VALUES (?)" -1] {:return-keys true})
TRACE t12954: | | | (next.jdbc.protocols/-execute-one {:dbtype "h2", :dbname "./site.db"} ["INSERT INTO foo_bar (id) VALUES (?)" -1] {:return-keys true, :next.jdbc/sql-params ["INSERT INTO foo_bar (id) VALUES (?)" -1]})
TRACE t12955: | | | | (next.jdbc.protocols/get-datasource {:dbtype "h2", :dbname "./site.db"})
TRACE t12955: | | | | => #object[next.jdbc.connection$url_PLUS_etc$reify__6940 0x53ad92b8 "jdbc:h2:./site.db"]
TRACE t12956: | | | | (next.jdbc.protocols/-execute-one #object[next.jdbc.connection$url_PLUS_etc$reify__6940 0x53ad92b8 "jdbc:h2:./site.db"] ["INSERT INTO foo_bar (id) VALUES (?)" -1] {:return-keys true, :next.jdbc/sql-params ["INSERT INTO foo_bar (id) VALUES (?)" -1]})
TRACE t12957: | | | | | (next.jdbc.protocols/get-connection #object[next.jdbc.connection$url_PLUS_etc$reify__6940 0x53ad92b8 "jdbc:h2:./site.db"] {:return-keys true, :next.jdbc/sql-params ["INSERT INTO foo_bar (id) VALUES (?)" -1]})
TRACE t12957: | | | | | => #object[org.h2.jdbc.JdbcConnection 0x25b3a2ad "conn18: url=jdbc:h2:./site.db user="]
TRACE t12956: | | | | => nil
TRACE t12954: | | | => nil
TRACE t12953: | | => nil
TRACE t12952: | => nil
TRACE t12951: => nil
running the statement like this works as expected: 
(jdbc/execute-one! (:db config) ["insert into foo_bar(id) values (?)" -1])
gives: 
TRACE t12978: (next.jdbc/execute-one! {:dbtype "h2", :dbname "./site.db"} ["insert into foo_bar(id) values (?)" -1])
TRACE t12979: | (next.jdbc.protocols/-execute-one {:dbtype "h2", :dbname "./site.db"} ["insert into foo_bar(id) values (?)" -1] #:next.jdbc{:sql-params ["insert into foo_bar(id) values (?)" -1]})
TRACE t12980: | | (next.jdbc.protocols/get-datasource {:dbtype "h2", :dbname "./site.db"})
TRACE t12980: | | => #object[next.jdbc.connection$url_PLUS_etc$reify__6940 0x39b3aee9 "jdbc:h2:./site.db"]
TRACE t12981: | | (next.jdbc.protocols/-execute-one #object[next.jdbc.connection$url_PLUS_etc$reify__6940 0x39b3aee9 "jdbc:h2:./site.db"] ["insert into foo_bar(id) values (?)" -1] #:next.jdbc{:sql-params ["insert into foo_bar(id) values (?)" -1]})
TRACE t12982: | | | (next.jdbc.protocols/get-connection #object[next.jdbc.connection$url_PLUS_etc$reify__6940 0x39b3aee9 "jdbc:h2:./site.db"] #:next.jdbc{:sql-params ["insert into foo_bar(id) values (?)" -1]})
TRACE t12982: | | | => #object[org.h2.jdbc.JdbcConnection 0x33b2250e "conn21: url=jdbc:h2:./site.db user="]
TRACE t12981: | | => #:next.jdbc{:update-count 1}
TRACE t12979: | => #:next.jdbc{:update-count 1}
TRACE t12978: => #:next.jdbc{:update-count 1}
#:next.jdbc{:update-count 1}
I tried with h2 1.4.xxx and 2.1.xxx (latest)

Eugen12:07:13

so it seems that adding {:return-keys true} returns nil . This is not obvious from docs https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.2.780/doc/getting-started#execute--execute-one . Is this an issue with the code , the docs or myself ?

seancorfield16:07:14

@U011NGC5FFY This is one of those annoying differences between databases/drivers. With :return-keys true, the underlying PreparedStatement is set up to tell the driver to return any new generated keys -- but it's up to the database/driver what it actually returns, and different DBs return different things, including nil. Without :return-keys true you get back either a ResultSet or an update count -- which is why you get {:next.jdbc/update-count 1} from execute-one!.

Eugen16:07:40

is it something that is worth adding to a Known Issues you will hit section ? btw, thanks

seancorfield16:07:52

See All the Options where :return-keys behavior is explained in detail...

seancorfield16:07:02

Any function that creates a PreparedStatement will additionally accept the following options: :return-keys -- a truthy value asks that the JDBC driver to return any generated keys created by the operation; it can be true or it can be a vector of keywords identifying column names that should be returned. Not all databases or drivers support all of these options, or all values for any given option. If :return-keys is a vector of column names and that is not supported, next.jdbc will attempt a generic "return generated keys" option instead. If that is not supported, next.jdbc will fall back to a regular SQL operation. If other options are not supported, you may get a SQLException.

Eugen16:07:39

I am getting nil

seancorfield16:07:00

With the version of H2 that next.jdbc is tested again (1.4.200), I think you should get uppercase keys per this test (in sql_test.cljc): 

(deftest test-insert-delete
  (let [new-key (cond (derby?)    :1
                      (jtds?)     :ID
                      (maria?)    :insert_id
                      (mssql?)    :GENERATED_KEYS
                      (mysql?)    :GENERATED_KEY
                      (postgres?) :fruit/id
                      (sqlite?)   (keyword "last_insert_rowid()")
                      :else       :FRUIT/ID)]
    (testing "single insert/delete"
      (is (== 5 (new-key (sql/insert! (ds) :fruit
                                      {:name (as-varchar "Kiwi")
                                       :appearance "green & fuzzy"
                                       :cost 100 :grade (as-real 99.9)}))))
      (is (= 5 (count (sql/query (ds) ["select * from fruit"]))))
      (is (= {:next.jdbc/update-count 1}
             (sql/delete! (ds) :fruit {:id 5})))
      (is (= 4 (count (sql/query (ds) ["select * from fruit"])))))

Eugen16:07:44

I updated to the latest h2 -"2.1.214"

seancorfield16:07:45

But it will depend on how your table is defined and whether your primary key is defined in a way that it will auto-generate a unique value for the driver to return.

Eugen16:07:13

once I am done with what I am working with I might downgrade to test that - but I recall it was providing the same results

Eugen16:07:06

able is defined like 

(str "CREATE TABLE " table-name
             " (id BIGINT UNIQUE NOT NULL, applied " timestamp-column-type
             ", description VARCHAR(1024) )")

seancorfield16:07:10

In the tests, the primary key is defined as AUTO_INCREMENT PRIMARY KEY for H2.

Eugen16:07:16

no auto generated pk

Eugen16:07:35

since pk is the migration id - defined in file

seancorfield16:07:37

Right, so you won't get "generated keys" back because your primary key doesn't generate anything.

Eugen16:07:03

thanks for taking the time to explain - kind of makes sense that it should work like this

Eugen16:07:16

but nil was unexpected - at least in the test

seancorfield16:07:34

If there are no generated keys to return... ¯\(ツ)/¯

seancorfield16:07:45

JDBC is often not very intuitive

Eugen16:07:26

down to last test and migratus will join next.jdbc familly

1
sheluchin15:07:27

Has anyone used Postgres COPY FROM to populate their DB from EDN? Looking for some tips/example snippets to get started. https://www.postgresql.org/docs/current/sql-copy.html

Eugen15:07:28

did not try but curios to see the outcome. I think you can use the FROM PROGRAM form and use babashka with data.csv to transform edn into csv. I think it might be faster to convert directly to CSV from your service / data producer.

Eugen15:07:37

please share your results

👍 1
sheluchin16:07:42

https://github.com/jgdavey/clj-pgcopy could be helpful for anyone trying to do this. Haven't tried it myself yet.

👍 1