Fork me on GitHub
#sql
<
2018-01-26
>
djtango15:01:46

Hey all, quick question - has anyone had success using row value expressions in a parameterized query?

djtango15:01:51

e.g.

(query ["select * from my_table where (my_table.x, my_table.y) in (values (?))" [1,2])

djtango15:01:25

I had a look at the Java docs (I'm not a Java programmer) but couldn't find a type for row expressions (to extend ISQLValue in clojure.java.jdbc)

bja15:01:28

I haven't tried that, but I do something similar with CTEs and it works out okay

djtango15:01:46

Oo that was an alternative solution I was toying with. How are you creating the CTE?

bja15:01:12

I use a helper for honeysql

bja15:01:30

the cte itself is just using a values expression

bja15:01:52

IIRC, I don't think a values is required for IN

djtango15:01:10

yeah, you can just use double parens

bja15:01:36

my helper does something like this

bja15:01:13

(apply with-static :d [:x :y :z] [[1 2 3] [4 5 6] [7 8 9]])

bja15:01:38

did you mean, what does the SQL look like?

djtango15:01:38

is it:

with my_cte as values (?) select * from my_cte

bja16:01:50

(honeysql.core/format
   (-> (apply with-static :my_cte [:x :y :z] (partition 3 (range 9)))
       (sql/select :*)
       (sql/from :my_cte)))
["WITH my_cte (x, y, z) AS (VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)) SELECT * FROM my_cte" 0 1 2 3 4 5 6 7 8]

Jacob19:01:24

Im trying to run my sql queries from

(def create-tables  ["Create table customers(id     integer,
                                             name   varchar(60),
                                             amount numeric(14,2))"])
(let [data (jdbc/query (@db-connections (keyword "pgdb"))  [(create-tables)])]
  (println data))
I keep getting this error Wrong number of args (0) passed to: PersistentVector this works just fine
(let [data (jdbc/query (@db-connections (keyword "pgdb"))  ["select * from customer"])]
(println data))

bja21:01:54

@jacob (prn [(create-tables)]) in your repl

bja21:01:32

you get the same error

bja21:01:04

the issue is that you used def and created a vector, not a function (although a vector happens to be a function of its indexes)

bja21:01:32

so (create-tables 0) would technically work (since create-tables is a vector and vectors are functions of their indexes), however (create-tables) (vector called with no arguments) has no definition. This is why you get the seemingly weird error message.

bja21:01:57

I suspect what you want is (jdbc/query .... create-tables)

bja21:01:45

def binds a value to a var defn creates a function

Jacob21:01:30

alright so I changed the let to

(let [data (jdbc/query (@db-connections (keyword "pgdb"))  (create-tables))]
  (println data))
(defn create-tables  ["DROP TABLE customers"])
but now i get this error Unsupported binding form: DROP TABLE customers

seancorfield22:01:02

@jisaacs46 defn declares functions.

seancorfield22:01:50

You also cannot use query with DDL (create table) -- you must use db-do-commands.

seancorfield22:01:15

(I feel like we've had this conversation before -- but maybe it was @josmith2016 trying to do the same thing)

Jacob22:01:26

oh maybe thats what im doing wrong kk ill give that a try

seancorfield22:01:45

(defn create-tables ["DROP TABLE customers"])
                    ^ this should be the parameter vector for the function you are trying to define
Make sense?

seancorfield22:01:58

(although defining something called create-... that actually DROPS a table is asking for problems 🙂 )

Jacob22:01:23

yeah but can I pass a function in the

[(function-name)]
instead of a
["DROP TABLE customers"]

Jacob22:01:37

@seancorfield is it ok to drop the tables then create them in the same function or would you recommend a different approach?

Jacob22:01:01

(let [data (jdbc/db-do-commands (@db-connections (keyword "pgdb"))  (create-tables))]
  (println data))

seancorfield22:01:27

If create-tables is a function that takes no arguments and returns a vector of SQL commands, that will work.

Jacob23:01:23

(defn create-tables  ["CREATE TABLE customer(
                        id     integer,
                        amount numeric(14,2),
                        name   varchar(60))"])

Jacob23:01:52

(let [data (jdbc/db-do-commands (@db-connections (keyword "pgdb"))  (create-tables))])

Jacob23:01:28

`java.lang.Exception: Unsupported binding form: CREATE TABLE customer( id integer, amount numeric(14,2), name varchar(60))`

Jacob23:01:04

I figured out its something to do with my (defn create-tables) ill keep messing around with that to see what im doing wrong