Clojurians Log v2

guides on date and time in postgresql?

We are using postgres and have success just converting whatever object we have to a timestamp (string) and inserting that

This one was helpful for me: https://www.postgresqltutorial.com/postgresql-timestamp/ And this is very enlightening although not about PostgreSQL specifically: https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/

Is there a way to use jdbc to query using multiple uuid?

(jdbc/execute-one! db [(str "SELECT * FROM " (name table) " WHERE uuid IN(?)") (clojure.string/join "," (map uuid/as-uuid uuids))])

What DB are you using @kwladyka?

postgresql

Read https://cljdoc.org/d/seancorfield/next.jdbc/1.0.395/doc/getting-started/tips-tricks#postgresql which talks about IN and an array of values.

The example there uses int-array but you'd need object-array for UUIDs (and you'll need to pass them as objects I suspect -- no idea how PG handles that).

(jdbc/execute-one! db [(str "SELECT * FROM " (name table) " WHERE uuid IN(?)") (map uuid/as-uuid uuids)])

Can't infer the SQL type to use for an instance of clojure.lang.LazySeq. Use setObject() with an explicit Types value to specify the type to use.

Did you actually read the docs I linked to?

oh so maybe jdbc conver it right, but still have issue about ::uuid

sure, but probably I didn’t understand

Read it again. Carefully.

And read my note above about object-array.

ok this is first time when I use object-array - I have no idea what it does, but I have:

Can't infer the SQL type to use for an instance of [Ljava.lang.Object;. Use setObject() with an explicit Types value to specify the type to use.

Your SQL is still wrong I expect.

yes, I have no idea how to write this to let know postgresql this is uuid not string

The docs I linked to show you.

(jdbc/execute-one! db [(str "SELECT * FROM " (name table) " WHERE uuid = ?::uuid") (uuid/as-uuid uuid)])

sorry, don’t get it

I don’t know how to create this objects I guess

First off, the docs say that in your SQL you need WHERE uuid = ANY(?)

Oh I misinterpreted this, but still doesn’t work

Then you need either an Object array with your UUID objects in it, or you might need an array of UUID objects that is typed for PostgreSQL

That will depend on what your uuids variable contains -- is it objects or strings at that point?

I can have whichever will be easier string / #uuid

Either (object-array uuids) or (into-array uuids) might work, or you might need (into-array SomeType uuids) where SomeType is whatever UUID type is acceptable to PostgreSQL.

I don't use PG so you'll have to figure this out based on the information I've given you.

Can't infer the SQL type to use for an instance of [Ljava.lang.Object;. Use setObject() with an explicit Types value to specify the type to use. this is what I have with object-array

into-array the same

(jdbc/execute-one! db ["SELECT * FROM shops WHERE uuid = ANY(?)" (into-array #{#uuid "00000000-0000-0000-0000-000000000001", #uuid "00000000-0000-0000-0000-000000000000"})])

this is my current state and fighting with this

Then you may need the "some type" variant I mentioned above

(but I've no idea what UUID type you'll need there)

yes, trying to figure out how to write this

(jdbc/execute-one! db ["SELECT * FROM shops WHERE uuid = ANY(?)" (into-array java.util.UUID #{#uuid "00000000-0000-0000-0000-000000000001", #uuid "00000000-0000-0000-0000-000000000000"})])

Can't infer the SQL type to use for an instance of [Ljava.util.UUID;. Use setObject() with an explicit Types value to specify the type to use.

Good ol' PostgreSQL strikes again... ¯\(ツ)/¯

You could always break down and do this:

(jdbc/execute-one! db (into ["SELECT * FROM shops WHERE uuid IN (" (str/join "," (repeat (count uuids) "?")) ")"] (map uuid/as-uuid uuids)))

no way to make uuid to be understandable by jdbc?

any hints how it can be done?

Similar to JSON example?

this is the part with I don’t have too much experience

Each database is different.

The variant I gave with repeat etc should work just like the single UUID example you had working above. You may need "?::uuid" instead of just plan "?".

there is 1 thing which I can’t understand

this works

(jdbc/execute-one! db [(str "SELECT * FROM " (name table) " WHERE uuid = ?") (uuid/as-uuid uuid)])

(jdbc/execute-one! db [(str "SELECT * FROM " (name table) " WHERE uuid = ?::uuid") uuid])

(jdbc/execute-one! db [(str "SELECT * FROM " (name table) " WHERE uuid = ?") uuid])

so it looks like this understand #uuid

but not when in array

Did you try the repeat variant I posted above?

yes, it works

Then you have a solution.

(jdbc/execute! db (into ["SELECT * FROM shops WHERE uuid IN (?,?)"] (map uuid/as-uuid #{#uuid "00000000-0000-0000-0000-000000000001", #uuid "00000000-0000-0000-0000-000000000000"})))

Good. Chalk the rest up to PostgreSQL/JDBC weirdness. Sometimes JDBC stuff is just... tears hair out 🔥

Thank you for help. I will still try to find the solution with array, but I don’t give myself too much chance 🙂

this one work

(jdbc/execute-one! db ["SELECT * FROM shops WHERE uuid = ANY(?::uuid[])" "{00000000-0000-0000-0000-000000000001,00000000-0000-0000-0000-000000000000}"])

not perfect but good enough

Interesting. So I guess that's a PG cast to an array type? Kind of weird that it takes a string of that format tho'... with braces around a comma-separated list...

https://clojurians.slack.com/archives/C1Q164V29/p1584137042188400 still this one confuse me

beucase it work with #uuid object

but not when I use ANY

so with arrray

Can't infer the SQL type to use for an instance of [Ljava.util.UUID;. Use setObject() with an explicit Types value to specify the type to use. using array as an input always throw error like this