sql 2022-07-29 | Slack Archive

Ovi Stoica06:07:58

Interesting thing I found with next.jdbc/with-transaction If I pass the tx object as a conn object to a function to insert something (as part of the transaction) it will time out but if I do the same insert in the original function’s scope it will work. Does the tx lose a reference when it is passed as a parameter to another function? Ex:

(defn save-user-role
  [conn user-id role]

If I pass the tx map to this function, it will timeout but if I copy all the queries from it in the original function, it works. I resolved to do all the queries inside the original scope but I’m curious for the reason for this

seancorfield09:07:33

The tx "object" is a Connection not a map. Passing it as an argument doesn't change that. Can you share your code? What you seem to describing should work fine.

Ovi Stoica09:07:07

(jdbc/with-transaction [tx conn]
      ; Store user object and return generated id
      (let [user-sqo-id
            (-> (execute!
                 tx (-> (insert-into :identity_object)
                        (values identity_vals}]))
                 {:return-keys true})
                (first)
                :generated_key)]
     (save-user-role tx user-sqo-id)

     ;; Cleanup any existing entries for new user
     (execute! tx (-> (delete-from :user_domain)
                      (where [:= :user_id user-sqo-id])))

Where save-user-role is

(defn save-user-role
  [conn user-id role]
  (let [group-type-id (get-io-type-id conn "GROUP")
        group (-> (execute! conn (-> (select :id)
                                              (from [:identity_object :io])
                                              (where [group-condition])))
                           (first)
                           :id)
        relationship-member-type (get-io-relationship-type-id "membership")
        relationship-role-type (get-io-relationship-type-id conn "role")]
    ;; Cleanup any existing relationships
    (execute! conn (-> (delete-from :identity_object_relationship)
                       (where condition)))
    (execute! conn (-> (insert-into :identity_object_relationship)
                       (values vals)))))

Indeed in the save-user-role example I use the tx to also query group, relationship-types (4 other queries) while when I put all the queries inside the with-transaction I query the group group-type-id relationship-member-type outside of the transaction. Maybe this influences?

seancorfield10:07:17

I'm sorry, I don't understand what you're saying there. Do you perhaps use with-transaction inside one of those other functions? You can't nest transactions.

respatialized17:07:01

@seancorfield I just wanted to thank you for the design work you've put into next.jdbc. Being able to quickly datafy a huge number of schema/column ResultSets across an entire database really made me feel like I could get comprehensive and thorough information about an unfamiliar and very complicated DB without needing to learn a new tool like DataGrip.

seancorfield21:07:00

Glad that's working well for you -- I rely on Portal and datafy a lot when working with our database at work 🙂

mwolf23:07:50

I'm getting a java.sql.SQLFeatureNotSupportedException from execute! when querying hive:

1. Unhandled java.sql.SQLFeatureNotSupportedException
   Method not supported

HiveResultSetMetaData.java:  102  org.apache.hive.jdbc.HiveResultSetMetaData/getTableName
            result_set.clj:   41  next.jdbc.result-set/get-column-names/fn
                  core.clj: 6979  clojure.core/mapv/fn
            LongRange.java:  233  clojure.lang.LongRange/reduce
                  core.clj: 6885  clojure.core/reduce
                  core.clj: 6970  clojure.core/mapv
                  core.clj: 6970  clojure.core/mapv
            result_set.clj:   40  next.jdbc.result-set/get-column-names
            result_set.clj:   36  next.jdbc.result-set/get-column-names
            result_set.clj:  335  next.jdbc.result-set/as-arrays
            result_set.clj:  330  next.jdbc.result-set/as-arrays
            result_set.clj:  638  next.jdbc.result-set/datafiable-result-set
            result_set.clj:  618  next.jdbc.result-set/datafiable-result-set
            result_set.clj:  930  next.jdbc.result-set/eval23966/fn
             protocols.clj:   33  next.jdbc.protocols/eval23050/fn/G
            result_set.clj: 1008  next.jdbc.result-set/eval24005/fn
             protocols.clj:   33  next.jdbc.protocols/eval23050/fn/G
                  jdbc.clj:  253  next.jdbc/execute!
                  jdbc.clj:  237  next.jdbc/execute!
[etc]

any way around this?

seancorfield23:07:00

No. That's the Hive JDBC driver throwing it, on a perfectly reasonable method call 😕

mwolf23:07:48

any way to get the raw columns, a different result set builder?

seancorfield23:07:10

According to this (JDBC docs), it should return "" if it can't produce the table name -- which is what Oracle always does and what MS SQL Server does unless you specify an additional option. https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSetMetaData.html#getTableName(int)

seancorfield23:07:48

If you use an unqualified builder, it won't try to call .getTableName, so that might be your best option.

seancorfield23:07:54

There seem to be quite a few badly-behaved JDBC drivers out there once you get outside the mainstream databases 😞

mwolf23:07:47

yeah ....... well, as-unqualified-arrays didn't barf, so that's something .... no data, but I guess I'm pointing at an empty table. thanks, sean.

seancorfield01:08:54

https://github.com/seancorfield/next-jdbc/issues/208 -- not sure when I'll release the next version.

seancorfield23:07:33

It's a fairly minor release but I've been sitting on some of these changes for a while...

2022-07-29

Channels