Hey guys Im new to clojure and I have a question? I am trying to convert all of my shell and sql scripts to clojure so that I can test and whats going on with my scripts. I can select data just fine. I can insert data into the database just fine manually but I would like to be able to select from my database then manipulate the data from my select statement then insert the data into another table. Here is my select query that I am using

(jdbc/query db ["select id, SUM(amount) from product where dt >= '2017-12-1' and dt <= '2017-12-31'"])

(jdbc/query db ["insert into new_products (query) values (?);" "query"]))

First off, query is for selects only. You can use insert-multi! which accepts a sequence of rows -- which is what query returns:

(let [data (jdbc/query db ["select id, sum(amount) from product ..."])]
  (jdbc/insert-multi! db :new_products data))

You could also use execute! with a single SQL state that did both the select and the insert.

^ @jisaacs46

ok that looks like it will work thank you @seancorfield ill try it out

i keep getting this error

I was able to get the let to work with jdbc it worked greate i couldnt get the execute to work though

What code is around balances/core.clj line 20 @jisaacs46?

The error means you have a string in a place where Clojure is expecting a function.

No, you have an extra set of parentheses around the expression.

The create-table-ddl function returns a string and then you're trying to call that string - because you have it wrapped in parentheses.

Lol oh man thank you so much lol 😂 I’ll give that a try in the morning @seancorfield

I missed that extra set of parentheses

(and I see you have a query with a drop command -- query only works for select, you want db-do-commands for DDL per the documentation)

http://clojure-doc.org/articles/ecosystem/java_jdbc/using_ddl.html

Alright I’ll give that a try thanks again you’ve been a big help.

Alright I’ll give that a try thanks again you’ve been a big help.

That worked thank you. @seancorfield I have another question? Do I need to close the database connections and if I do do I just do (db connectio.close)

As long as you don't directly call jdbc/get-connection, the connections will all be managed for you. Each operation opens and closes a connection.

That's why connection pooling is a good idea (for performance) since opening and closing a full database connection can be expensive, but once you have a connection pool, "opening" (getting) and "closing" (returning) a connection against that pool is cheap.

Again, the documentation is your friend: http://clojure-doc.org/articles/ecosystem/java_jdbc/reusing_connections.html

Your call is incorrect -- it should be (jdbc/db-do-commands db ["DROP TABLE fruit" "CREATE TABLE fruit..."]) -- it takes a collection (sequence) of commands. You specified them as separate arguments.