This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2019-07-10
Channels
- # announcements (3)
- # beginners (67)
- # calva (4)
- # cider (3)
- # clj-kondo (58)
- # cljs-dev (4)
- # clojure (172)
- # clojure-berlin (4)
- # clojure-chicago (8)
- # clojure-europe (4)
- # clojure-greece (8)
- # clojure-italy (12)
- # clojure-nl (4)
- # clojure-spec (7)
- # clojure-uk (77)
- # clojurescript (13)
- # clojutre (16)
- # core-async (10)
- # cursive (3)
- # datomic (29)
- # figwheel-main (27)
- # fulcro (22)
- # garden (3)
- # jobs (2)
- # jobs-discuss (13)
- # juxt (5)
- # leiningen (14)
- # mount (4)
- # off-topic (28)
- # pathom (6)
- # pedestal (8)
- # portland-or (2)
- # re-frame (20)
- # remote-jobs (6)
- # shadow-cljs (13)
- # sql (74)
- # testing (17)
- # tools-deps (1)
- # vim (1)
- # xtdb (1)
@seancorfield why does insert-multi!
require a vector and not simply sequential? I.e. :rows (s/coll-of (s/coll-of any? :kind vector?) :kind vector?)
-> :rows (s/coll-of (s/coll-of any? :kind sequential?) :kind sequential?)
? So that I can for/map/..
over my data without having to convert to a vector? Or is there a special reason for this?
PS: I have also noticed that insert-multi!
fails if the data vector is empty. Perhaps it should check for that and just do nothing it if its?
Will there be official support for TimesTen? I have patched a version that works OK with TimesTen (direct)...
@holyjak feel free to open issues regarding those two things and I'll take a look. @gko you too -- I'd like to support as many databases as possible (I've never heard of Times Ten).
Do you prefer an issue or a PR?
I'd prefer an issue since I'm not sure yet what the right solution is for either.
I created https://github.com/seancorfield/next-jdbc/issues/43 and https://github.com/seancorfield/next-jdbc/issues/44 for these @holyjak -- feel free to add your thoughts.
I'll hold off releasing 1.0.2 until those are addressed.
thanks a lot!
42c42 < clojure.java.jdbc --- > tt.jdbc-tt 166c166,169 < "sqlserver" "com.microsoft.sqlserver.jdbc.SQLServerDriver"}) --- > "sqlserver" "com.microsoft.sqlserver.jdbc.SQLServerDriver" > "timesten:direct" "com.timesten.jdbc.TimesTenDriver" > "timesten:client" "com.timesten.jdbc.TimesTenClientDriver" > }) 356a360,361 > (= "timesten:direct" dbtype) > (str "jdbc:" dbtype ":dsn=" dbname)
Interesting, thanks @gko -- I'll add support in next.jdbc
and I'll think about backporting it to clojure.java.jdbc
I gather you can use the client version without needing to patch clojure.java.jdbc
as {:dbtype "timesten:client" :classname "com.timesten.jdbc.TimesTenClientDriver" ...}
?
You need the patch for the dbname separator for the direct version only?
Is the driver available on a Maven repo directly? Or does it have to be manually downloaded and installed locally?
I have downloaded them from a platform I'm using at my company, which I think is coming in https://www.oracle.com/technetwork/database/database-technologies/timesten/downloads/index.html ? Let me check.
Looks like that :dsn=
strings needs to be present for both client and direct, according to the docs. I'll update the next.jdbc
code.
The drivers are here: timesten112280.linux8664.tar.gz\timesten112280.linux8664.tar\linux8664\LINUX8664\ttclient.tar.bz2\ttclient.tar\.\lib\
Because it's Oracle-licensed, yeah.
OK, well, I'm not going through that rigmarole just to test it 🙂
Feel free to try out the latest git SHA of next.jdbc
with that driver etc to see if it works.
Or wait until I release 1.0.2 (probably next week).
Hmm, for the direct, I bet it doesn't want the host or anything in so I still need to make a change there...
The documentation also shows both direct and client connections without dsn=
, just the :
but I'll leave that in place for now. The old-fashioned oracle:sid
subprotocol also uses (just) :
so I guess I'm not surprised that TimesTen needs it too...
Reading the docs, I now see that the client connection setup works the same way -- because it uses ODBC under the hood for the actual connection. Wow... OK...
Alright. I think that's "done" now. LMK if you can get it working with next.jdbc
as a Git dep...
It's really in bug fix mode at this point, but adding DB support is a minor enhancement so I'd consider it.
From the clojure.java.jdbc
README: "This library is mature and stable. It is widely used and its use is described in many books and tutorials. It will continue to get bug fixes and minor releases."
At this point, I prefer to experiment with features (and new DB support) in next.jdbc
, and look at back-porting changes that seem worthwhile. 0.7.9 was released in February. There are two tickets fixed on master since then, both very minor. If you can confirm the TimesTen stuff works in a quick test with next.jdbc
, then I'll back-port it to clojure.java.jdbc
and cut 0.7.10.
@holyjak #43 and #44 are fixed on master -- #43 was just relaxing the specs: it "just worked" after that (without instrumentation you could already generate rows with for
/`map`); #44 adds a conditional to insert-multi!
itself. @gko I'm ready to cut 1.0.2 but I can hold off until Monday if you'd like more time to test the TimesTen support.
Just tested the "friendly SQL functions" part and all work except insert-multi!, which yields Syntax error in SQL statement before or at: ",", character position: 88: maybe TimesTen (7.x version in my case) doesn't support multiple values in INSERT?
Command> INSERT INTO xxx (company_name, iid, audit_input_id, record_hash_key) VALUES ('1', 'one', 'un', 1), ('1', 'one', 'un', 1); INSERT INTO xxx (company_name, iid, audit_input_id, record_hash_key) VALUES ('1', 'one', 'un', 1), ('1', 'one', 'un', 1); 1001: Syntax error in SQL statement before or at: ",", character position: 97 ..., record_hash_key) VALUES ('1', 'one', 'un', 1), ('1', 'one', 'un',... ^ The command failed. Command>
tt> (sql/insert-multi! dbc :xxx [:company_name :iid :audit_input_id :record_hash_key] [["1" "one" "un" 1] ["2" "two" "deux" 2]]) Execution error (SQLException) at com.timesten.jdbc.JdbcOdbc/createSQLException (JdbcOdbc.java:3184). [TimesTen][TimesTen 7.0.6.2.0 ODBC Driver][TimesTen]TT1001: Syntax error in SQL statement before or at: ",", character position: 88 -- file "ptSqlY.y", lineno 248, procedure "sbPtParseSql" tt>
@gko Thanks. Yeah, it's possible that TimesTen doesn't support that syntax (and, perhaps, doesn't support batch execution either?).
From my reading of the docs, it's clear that the TimesTen JDBC driver is an adapter between SQL and the underlying data access API for the DB. It might be that it accepts a format that has all the values in a single clause, so you might try execute-one!
with INSERT INTO xxx (company_name, iid, audit_input_id, record_hash_key) VALUES ('1', 'one', 'un', 1, '1', 'one', 'un', 1)
...
But it's good to know the basic stuff all works. Thank you for testing that!
From command line: Command> INSERT INTO xxx (company_name, iid, audit_input_id, record_hash_key) VALUES ('1', 'one', 'un', 1, '1', 'one', 'un', 1); INSERT INTO xxx (company_name, iid, audit_input_id, record_hash_key) VALUES ('1', 'one', 'un', 1, '1', 'one', 'un', 1); 1025: Number of column names in INSERT statement is not equal to number of values The command failed. Command>
Ah well, that's just syntax it doesn't support...
I've added a new ticket to the 1.0.2 milestone (internal refactoring) so it'll be Tuesday or Wednesday now before I cut 1.0.2.
Given the changes I made today, I'm somewhat inclined to remove TimesTen from core since you'd be able to do it easily via the extended db-spec now:
{:dbtype "timesten:direct" :classname "..." :host :none :dbname-separator ":dsn=" ...}
but since I've added it, I'll leave it in. I'm considering a way to move database type definitions out of core to an EDN file which would make it much easier to support new DBs anyway.OK... At least, people will know that TimesTen is "supported" when googling "Clojure TimesTen".
#sql anyone know how to enable :quoting for HugSQL and PostgreSQL ?
{:quoting :ansi}
will work for PostgreSQL.
:quoting options for identifiers are:
:ansi double-quotes: "identifier"
:mysql backticks: `identifier`
:mssql square brackets: [identifier]
:off no quoting (default)
It doesn’t clarify that :ansi works for postgres in the docsMySQL and MS SQL are the "odd ones out" -- most databases use ANSI-style quoting.
@seancorfield I’m curious why I am not getting a prepared statement for this :
(hugsql.core/def-sqlvec-fns-from-string "-- :name test :? :raw \n SELECT :i*:cols FROM table WHERE purpose = :i:p AND source = :i:s AND target = :i:t ")
(test-sqlvec {:cols ["c1" "c2"]
:p "p"
:s "s"
:t "t"} {:quoting :ansi})
=> ["SELECT \"c1\", \"c2\" FROM contracts WHERE purpose = \"p\" AND source = \"s\" AND target = \"t\""]
FWIW, honeysql
, clojure.java.jdbc
, and next.jdbc
all follow the same pattern (with slightly different ways to specify it, but with much the same names for the quoting strategies).
I was expecting a bunch of ?
in the SQL stmt
I'm not very familiar with HugSQL but I suspect it's because you've used :i
for the parameters, rather than some other option?
According to the docs (I'm just reading through them for the first time), :i
should only be used for inserting identifier I think? So just remove :i
from the three parameters and it should work.
@seancorfield you are right, I should have used :v:p , :v:s and :v:t
(hugsql.core/def-sqlvec-fns-from-string "-- :name test :? :raw \n SELECT :i*:cols FROM contracts WHERE purpose = :v:purpose AND source = :v:source AND target = :v:target ")
=> nil
(test-sqlvec {:cols ["c1" "c2"]
:purpose "p"
:source "s"
:target "t"} {:quoting :mysql})
=> ["SELECT `c1`, `c2` FROM contracts WHERE purpose = ? AND source = ? AND target = ?" "p" "s" "t"]
Pretty sure you don't need :v
in there... based on what I just read.
you are correct about that too, thanks! 🙂
@seancorfield thank you 🙂
you are correct about that too, thanks! 🙂
https://www.hugsql.org/#param-value
Value Parameters are the default parameter type, so you can omit the type portion of the parameter placeholder in your SQL statements.
@holyjak #43 and #44 are fixed on master -- #43 was just relaxing the specs: it "just worked" after that (without instrumentation you could already generate rows with for
/`map`); #44 adds a conditional to insert-multi!
itself. @gko I'm ready to cut 1.0.2 but I can hold off until Monday if you'd like more time to test the TimesTen support.