sql

All things SQL and JDBC...
grounded_sage 2020-02-21T19:31:01.258400Z

Is there a way to pass a raw sql string to this execute! function in the Toucan library. https://cljdoc.org/d/toucan/toucan/1.15.0/api/toucan.db#execute!

grounded_sage 2020-02-21T19:31:38.259300Z

I’d like to create a table without pulling in jdbc and creating another connection to the db.

grounded_sage 2020-02-21T19:33:33.259600Z

I actually have an example of the code I have so far here. https://github.com/groundedSAGE/toucan-lib-sqlite-example/blob/master/src/core.clj

seancorfield 2020-02-21T19:50:26.260700Z

@grounded_sage I'll resist expressing horror about the dynamically bound global state in Toucan and point you to HoneySQL's raw feature: https://github.com/jkk/honeysql/#raw-sql-fragments

🙃 1
seancorfield 2020-02-21T19:50:58.261200Z

That should allow you to pass a HoneySQL-constructed form to Toucan's execute! function.

grounded_sage 2020-02-21T19:54:40.261300Z

Can you please clarify what you mean by the dynamically bound global state?

grounded_sage 2020-02-21T19:56:41.261500Z

I found the raw but have been unable to construct the shape of data that passes the whole way through. Ideally I like keeping the .sql files for table generation at this point as well. As I can take advantage of text highlighting and formatting etc in editor.

seancorfield 2020-02-21T20:16:21.261700Z

Toucan uses a dynamic Var for the db connection: mutable global state. So you can't use Toucan with multiple databases in a single app easily.

seancorfield 2020-02-21T20:17:23.261900Z

user=> (require '[honeysql.core :as h])
nil
user=> (h/raw "select * from table")
#sql/raw "select * from table"
user=> (h/format (h/raw "select * from table"))
["select * from table"]
user=> 

seancorfield 2020-02-21T20:18:15.262100Z

You should be able to call (db/execute! (h/raw "your sql string here"))

seancorfield 2020-02-21T20:18:27.262300Z

(where db is your Toucan alias)

grounded_sage 2020-02-21T20:26:11.262500Z

(db/execute! (sql/raw "select * from address"))

grounded_sage 2020-02-21T20:26:26.262700Z

Returns this error:

#error {
 :cause "Query returns results"
 :via
 [{:type java.sql.SQLException
   :message "Query returns results"
   :at [org.sqlite.jdbc3.JDBC3PreparedStatement executeUpdate "JDBC3PreparedStatement.java" 95]}]
 :trace
 [[org.sqlite.jdbc3.JDBC3PreparedStatement executeUpdate "JDBC3PreparedStatement.java" 95]
  [clojure.java.jdbc$db_do_execute_prepared_statement$fn__6328 invoke "jdbc.clj" 1038]
  [clojure.java.jdbc$db_transaction_STAR_ invokeStatic "jdbc.clj" 806]
  [clojure.java.jdbc$db_transaction_STAR_ invoke "jdbc.clj" 776]
  [clojure.java.jdbc$db_transaction_STAR_ invokeStatic "jdbc.clj" 789]
  [clojure.java.jdbc$db_transaction_STAR_ invoke "jdbc.clj" 776]
  [clojure.java.jdbc$db_do_execute_prepared_statement invokeStatic "jdbc.clj" 1037]
  [clojure.java.jdbc$db_do_execute_prepared_statement invoke "jdbc.clj" 1031]
  [clojure.java.jdbc$db_do_prepared invokeStatic "jdbc.clj" 1069]
  [clojure.java.jdbc$db_do_prepared invoke "jdbc.clj" 1049]
  [clojure.java.jdbc$execute_BANG_$execute_helper__6402 invoke "jdbc.clj" 1453]
  [clojure.java.jdbc$execute_BANG_ invokeStatic "jdbc.clj" 1457]
  [clojure.java.jdbc$execute_BANG_ invoke "jdbc.clj" 1424]
  [toucan.db$execute_BANG_ invokeStatic "db.clj" 424]
  [toucan.db$execute_BANG_ doInvoke "db.clj" 419]
  [clojure.lang.RestFn invoke "RestFn.java" 410]
  [core$eval12288 invokeStatic "NO_SOURCE_FILE" 1]
  [core$eval12288 invoke "NO_SOURCE_FILE" 1]
  [clojure.lang.Compiler eval "Compiler.java" 7177]
  [clojure.lang.Compiler eval "Compiler.java" 7132]
  [clojure.core$eval invokeStatic "core.clj" 3214]
  [clojure.core$eval invoke "core.clj" 3210]
  [clojure.main$repl$read_eval_print__9086$fn__9089 invoke "main.clj" 437]
  [clojure.main$repl$read_eval_print__9086 invoke "main.clj" 437]
  [clojure.main$repl$fn__9095 invoke "main.clj" 458]
  [clojure.main$repl invokeStatic "main.clj" 458]
  [clojure.main$repl doInvoke "main.clj" 368]
  [clojure.lang.RestFn invoke "RestFn.java" 1523]
  [nrepl.middleware.interruptible_eval$evaluate invokeStatic "interruptible_eval.clj" 79]
  [nrepl.middleware.interruptible_eval$evaluate invoke "interruptible_eval.clj" 55]
  [nrepl.middleware.interruptible_eval$interruptible_eval$fn__935$fn__939 invoke "interruptible_eval.clj" 142]
  [clojure.lang.AFn run "AFn.java" 22]
  [nrepl.middleware.session$session_exec$main_loop__1036$fn__1040 invoke "session.clj" 171]
  [nrepl.middleware.session$session_exec$main_loop__1036 invoke "session.clj" 170]
  [clojure.lang.AFn run "AFn.java" 22]
  [java.lang.Thread run "Thread.java" 830]]}

seancorfield 2020-02-21T20:29:28.262900Z

You should use db/query for a query. db/execute! is apparently only for DDL.

grounded_sage 2020-02-21T20:30:24.263100Z

Oh okay. Well actually I am trying to create tables. But I’m just trying to get something working where I pass Honey format first lol

seancorfield 2020-02-21T20:30:33.263300Z

(! 798)-> clj -Sdeps '{:deps {toucan {:mvn/version "RELEASE"} honeysql {:mvn/version "RELEASE"} org.xerial/sqlite-jdbc {:mvn/version "3.28.0"}}}'
Clojure 1.10.1
user=> (require '[toucan.db :as db] '[toucan.models :as models :refer [defmodel]] '[honeysql.core :as sql])
nil
user=> (def db2 {:dbtype "sqlite" :dbname "example"})
#'user/db2
user=> (db/set-default-db-connection! db2)
{:dbtype "sqlite", :dbname "example"}
user=> (db/execute! (sql/raw "create table foo ( id int, name varchar(256) )"))
[0]
user=> (defmodel Foo :foo)
#'user/Foo
user=> (db/insert! Foo {:id 1 :name "Sean"})
#user.FooInstance{:id 1, :name "Sean"}
user=> (db/insert! Foo {:id 2 :name "Wade"})
#user.FooInstance{:id 2, :name "Wade"}
user=> (db/select Foo)
[#user.FooInstance{:id 1, :name "Sean"} #user.FooInstance{:id 2, :name "Wade"}]
user=> (db/query (sql/raw "select * from foo where id = 2"))
({:id 2, :name "Wade"})
user=> 

seancorfield 2020-02-21T20:31:14.263500Z

(that's how I found out that db/execute! doesn't work for selects, in my previous REPL session before this one)

grounded_sage 2020-02-21T20:31:35.263700Z

I see

grounded_sage 2020-02-21T20:34:46.263900Z

It seems strange to me that this is not allowed. (db/execute! {:s "create table foo ( id int, name varchar(256) )"})

grounded_sage 2020-02-21T20:34:59.264100Z

because the string is tagged?

seancorfield 2020-02-21T20:36:34.264300Z

HoneySQL uses a record behind the scenes for raw SQL so that it can dispatch on type.

seancorfield 2020-02-21T20:39:05.264500Z

user=> (db/query (honeysql.types/->SqlRaw "select * from foo where id = 2"))
({:id 2, :name "Wade"})
user=> 
The record type is honeysql.types.SqlRaw but if you use sql/raw you don't need to worry about that.

seancorfield 2020-02-21T20:39:31.264700Z

Here's how I figured that out

user=> (sql/raw "select * from foo where id = 2")
#sql/raw "select * from foo where id = 2"
user=> (type *1)
honeysql.types.SqlRaw

seancorfield 2020-02-21T20:40:31.264900Z

You can use the tag instead of the call if you prefer:

user=> (db/query #sql/raw "select * from foo where id = 2")
({:id 2, :name "Wade"})
user=> 

grounded_sage 2020-02-21T20:42:51.265100Z

Sorry I was playing around in the repl.

grounded_sage 2020-02-21T20:43:12.265300Z

Yea the tag works. But I was also trying to pull in the code from an sql file.

grounded_sage 2020-02-21T20:44:15.265500Z

So it would look something like this. (db/execute! #sql/raw [(get-sql "sql/users.sql")])

seancorfield 2020-02-21T20:44:26.265700Z

You can only use the tag with a literal. You have to use the call for an expression

user=> (def sql-from-file "select * from foo where id = 1")
#'user/sql-from-file
user=> (db/query (sql/raw sql-from-file))
({:id 1, :name "Sean"})
user=> 

seancorfield 2020-02-21T20:45:09.266Z

(db/execute! (sql/raw (get-sql "sql/users.sql"))) should work.

👍 1
grounded_sage 2020-02-21T21:06:56.266200Z

So there doesn’t seem to be any way around having the extra dependency for reading and creating the tables as an external resource.

seancorfield 2020-02-21T21:39:03.266500Z

What "extra dependency"?

seancorfield 2020-02-21T21:40:03.266700Z

My REPL session used Toucan, HoneySQL (probably not needed -- I bet Toucan already includes it), and the SQLite JDBC driver. Nothing else.

seancorfield 2020-02-21T21:43:33.266900Z

Yup, just confirmed, all of the above works with just clj -Sdeps '{:deps {toucan {:mvn/version "RELEASE"} org.xerial/sqlite-jdbc {:mvn/version "3.28.0"}}}'

grounded_sage 2020-02-21T23:44:43.267100Z

Yea it includes it. Which is why I was trying to find a way to avoid another dependency.

grounded_sage 2020-02-21T23:45:13.267300Z

I’m not sure how to reach in and get the raw though.

seancorfield 2020-02-21T23:45:47.267500Z

Exactly like I did in that REPL session. I'm not sure what you're asking otherwise?

seancorfield 2020-02-21T23:46:34.267700Z

Are you complaining about the need to require HoneySQL so you can call sql/raw?

seancorfield 2020-02-21T23:47:03.267900Z

It isn't a "dependency" because HoneySQL is already pulled in via Toucan -- you're not adding anything to your project.

👍 1
❤️ 1
grounded_sage 2020-02-21T23:49:13.268100Z

Oh wow. I actually had no idea I could use it that way without explicitely having it in my dependencies…

grounded_sage 2020-02-21T23:50:37.268300Z

I knew there had to be a way to tap into the lower level libraries but didn’t know how.. 😅

1