Fork me on GitHub
Slack Archive

This page is not created by, affiliated with, or supported by Slack Technologies, Inc.

2019-07-10

Channels

#sql
<
2019-07-10
>
Jakub Holý (HolyJak)07:07:11

@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?

gko08:07:35

Will there be official support for TimesTen? I have patched a version that works OK with TimesTen (direct)...

seancorfield15:07:43

@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).

👍 8
Jakub Holý (HolyJak)15:07:46

Do you prefer an issue or a PR?

seancorfield16:07:53

I'd prefer an issue since I'm not sure yet what the right solution is for either.

seancorfield19:07:08

I'll hold off releasing 1.0.2 until those are addressed.

gko01:07:35

Here's the diff with [org.clojure/java.jdbc "0.7.6"]:

gko01:07:47

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)

gko01:07:50

Tested with "direct" only on version 7.0.6.2.0 (now is already 11g something).

seancorfield02:07:48

Interesting, thanks @gko -- I'll add support in next.jdbc and I'll think about backporting it to clojure.java.jdbc

seancorfield02:07:29

I gather you can use the client version without needing to patch clojure.java.jdbc as {:dbtype "timesten:client" :classname "com.timesten.jdbc.TimesTenClientDriver" ...} ?

seancorfield02:07:52

You need the patch for the dbname separator for the direct version only?

gko02:07:42

I have only used the direct version, not the client version yet.

seancorfield02:07:45

Is the driver available on a Maven repo directly? Or does it have to be manually downloaded and installed locally?

gko02:07:45

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.

seancorfield02:07:08

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.

gko02:07:59

The drivers are here: timesten112280.linux8664.tar.gz\timesten112280.linux8664.tar\linux8664\LINUX8664\ttclient.tar.bz2\ttclient.tar\.\lib\

gko02:07:32

Looks like not available in Maven...

seancorfield02:07:59

Because it's Oracle-licensed, yeah.

seancorfield02:07:25

OK, well, I'm not going through that rigmarole just to test it 🙂

seancorfield02:07:56

Feel free to try out the latest git SHA of next.jdbc with that driver etc to see if it works.

seancorfield02:07:10

Or wait until I release 1.0.2 (probably next week).

gko02:07:58

Yeah, thanks for the quick update 🙂...

gko02:07:05

I'll test when I'll have time and provide feed back...

seancorfield02:07:50

Hmm, for the direct, I bet it doesn't want the host or anything in so I still need to make a change there...

seancorfield03:07:52

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...

gko03:07:45

I just use it for a very narrow case... I guess other users may appear later 🙂

seancorfield03:07:42

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...

seancorfield03:07:32

Alright. I think that's "done" now. LMK if you can get it working with next.jdbc as a Git dep...

gko03:07:31

OK, thanks...

gko03:07:44

Will clojure.java.jdbc be updated? Or it's considered "deprecated" ?

seancorfield04:07:17

It's really in bug fix mode at this point, but adding DB support is a minor enhancement so I'd consider it.

seancorfield04:07:46

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."

gko04:07:58

OK, thanks!

seancorfield04:07:46

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.

seancorfield19:07:37

@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.

❤️ 4
gko04:07:26

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?

gko04:07:16

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>

gko04:07:34

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>

seancorfield06:07:47

@gko Thanks. Yeah, it's possible that TimesTen doesn't support that syntax (and, perhaps, doesn't support batch execution either?).

seancorfield06:07:15

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)...

seancorfield06:07:51

But it's good to know the basic stuff all works. Thank you for testing that!

gko06:07:16

Just tested with timesten:client, it looks OK too.

gko06:07:23

(surely with same issues as direct)

gko06:07:20

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>

gko06:07:41

Anyway, what's already working is great!

seancorfield06:07:13

Ah well, that's just syntax it doesn't support...

seancorfield06:07:48

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.

seancorfield06:07:18

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.

gko06:07:13

OK... At least, people will know that TimesTen is "supported" when googling "Clojure TimesTen".

asilverman20:07:16

#sql anyone know how to enable :quoting for HugSQL and PostgreSQL ?

seancorfield20:07:02

{:quoting :ansi} will work for PostgreSQL.

asilverman20:07:38

@seancorfield 

: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 docs

seancorfield20:07:13

MySQL and MS SQL are the "odd ones out" -- most databases use ANSI-style quoting.

asilverman20:07:58

@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\""]

seancorfield20:07:13

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).

asilverman20:07:22

I was expecting a bunch of ? in the SQL stmt

seancorfield20:07:22

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?

seancorfield20:07:04

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.

asilverman20:07:04

@seancorfield you are right, I should have used :v:p , :v:s and :v:t

asilverman20:07:30

(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"]

seancorfield20:07:52

Pretty sure you don't need :v in there... based on what I just read.

asilverman20:07:04

you are correct about that too, thanks! 🙂

asilverman20:07:04

you are correct about that too, thanks! 🙂

asilverman20:07:44

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.

seancorfield19:07:37

@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.

❤️ 4