sql 2019-11-07 | Slack Archive

@seancorfield is there a decent way to share connection configuration between stuff that relies on clj.j.jdbc and next?

typical example: we have a migration lib relying on ragtime, which uses the former and app that might use the later, with various flavors of db specs (depending on test suites, various prod/staging setups and so on)

mpenet16:11:34

I think I got a decent solution

seancorfield17:11:43

@mpenet yes and it is hinted at in the migration guide : use {:datasource my-ds} as your db-spec -- for c.j.j. use that as-is, for next.jdbc use (:datasource db-spec)

mpenet20:11:50

Awesome thanks

jmayaalv10:11:49

was just wondering the same 🙂 thank you 🙂

seancorfield17:11:14

That's what we're using at work. With a c3p0 pooled datasource.

sonnyto20:11:14

{:insert-into :taxonomy, :columns [:style :category :department :personas], :values [["a" "b" "c" "{\"a\",\"b\"}"]]}

This fails with

1. Unhandled org.postgresql.util.PSQLException                                                                                                                                                                                                     ERROR: column "personas" is of type text[] but expression is of                                                                                                                                                                                 type character varying Hint: You will need to rewrite or cast the                                                                                                                                                                               expression.  Position: 82                                                                                                                                                                                                                                                                                                                                                                                                                                                                        QueryExecutorImpl.java: 2284  org.postgresql.core.v3.QueryExecutorImpl/receiveErrorResponse                                                                                                                                                     QueryExecutorImpl.java: 2003  org.postgresql.core.v3.QueryExecutorImpl/processResults                                                                                                                                                           QueryExecutorImpl.java:  200  org.postgresql.core.v3.QueryExecutorImpl/execute                                                                                                                                                                        PgStatement.java:  424  org.postgresql.jdbc.PgStatement/execute

This seems like a bug. next.jdbc should be smart enough to know :personas is of type text[] and not a varchar. Its probably calling setString on the prepareStatement but should be calling something like setObject. Is there a way around this?

seancorfield20:11:38

next.jdbc calls .setObject

seancorfield20:11:34

Both clojure.java.jdbc and next.jdbc leave it entirely up to the database driver to figure out types and coercions.

seancorfield20:11:12

You might want to check what honeysql produces for that data structure and see what is actually being passed into next.jdbc

sonnyto20:11:16

thanks for responding. It generates this vector

["INSERT INTO taxonomy (style, category, department, personas) VALUES (?, ?, ?, ?)"                                                                                                                                                              "ha4"  "b" "c" "{\"a\",\"b\"}"]

sonnyto20:11:28

sorry for the formatting

seancorfield20:11:38

So next.jdbc is just going to call .setObject and pass in those strings, per this code

(extend-protocol SettableParameter
  Object
  (set-parameter [v ^PreparedStatement s ^long i]
    (.setObject s i v))

  nil
  (set-parameter [_ ^PreparedStatement s ^long i]
    (.setObject s i nil)))

seancorfield20:11:06

set-parameter is called for each of those in turn.

sonnyto20:11:20

hmm... so why is the type not matching? it seems to be treating personas as a varchar

sonnyto20:11:24

and not a text[]

sonnyto20:11:33

so this could be a bug in the JDBC driver?

sonnyto20:11:40

postgresql

seancorfield20:11:03

Of course it's PostgreSQL... it always is with weird stuff like this 🙂

😂 4

sonnyto20:11:13

is there a way to force honeySQL to not generate prepare statement but generate raw SQL string instead?

seancorfield22:11:51

HoneySQL produces data structures.

seancorfield22:11:04

clojure.java.jdbc and next.jdbc create prepared statements.

seancorfield22:11:32

I think you mean: is there a way to force HoneySQL to inline parameter values?

👍 4

seancorfield22:11:24

honeysql.types/inline will do that -- but be warned that it knows nothing about SQL datatypes so it will perform a purely textual substitution which is almost certainly not what you want in most cases.

dharrigan22:11:49

There is the honeysql.core/raw function which I've used in the past

dharrigan22:11:02

i.e.,

dharrigan22:11:12

(def st-point-sql->lat-lng
  (sql/raw (str "ST_Y(location::geometry) lat, ST_X(location::geometry) lng")))

dharrigan22:11:35

sql is just an alias for honeysql.core

seancorfield22:11:18

(note: honeysql.core/raw is an alias for honeysql.types/raw)

sonnyto22:11:35

cool. thanks guys. inline is a workaround for this bug

dharrigan06:11:31

Thanks @seancorfield. You're welcome @U07SXG7DY

🙂 4

2019-11-07

Channels