Fork me on GitHub
#datomic
<
2023-10-05
>
twashing15:10:00

I’ve downloaded and am trying to run datomic-pro-1.0.6735. I’m trying to run a transactor against MS SQL Server (in a Docker container). I’ve also included maven/com.microsoft.sqlserver/[email protected] in Datomic Pro’s pom.xml. But when I try to run it using sqlserver-transactor.properties, I get a ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver. 👉:skin-tone-5: How do I make the SQL Server driver available to the Transactor and Peer?

./bin/transactor config/sqlserver-transactor.properties

Terminating process - Lifecycle thread failed
java.util.concurrent.ExecutionException: java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerDriver
	at java.base/java.util.concurrent.FutureTask.report(FutureTask.java:122)
    ...
Caused by: java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerDriver
	at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:253)
    ...
Caused by: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
	at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:641)
	at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:188)
    ...
Files
# config/sqlserver-transactor.properties
protocol=sql
host=localhost
port=1433

sql-url=jdbc:;database=datomic;encrypt=true;trustServerCertificate=true;
sql-user=...
sql-password=...
sql-driver-class=com.microsoft.sqlserver.jdbc.SQLServerDriver
sql-validation-query=select 1

# pom.xml
 <dependencies>
  ...
  <dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>12.4.1.jre11</version>
  </dependency>

ghadi15:10:10

i do not believe datomic's pom is used to launch it

ghadi15:10:57

> Only the Postgres driver is included with the transactor distribution. For other SQL distributions, you will have to make the driver available on the classpath of the transactor (by placing it in <datomic-install>/lib).

ghadi15:10:09

drop the mssql jar in /lib

ghadi15:10:37

(You will need to add the mssql dep to your app's dependencies)

twashing15:10:54

Ah ok… 🏃:skin-tone-5:

twashing15:10:44

Yep, looks like that did it. Cheers man 👏:skin-tone-6:

favila15:10:19

You can also set DATOMIC_EXT_CLASSPATH instead of copying stuff into lib if you want https://docs.datomic.com/pro/reference/database-functions.html#classpath-functions

1
favila15:10:26

(on the transactor side)

twashing15:10:14

Nice! Thanks man.

Black17:10:42

Is it possible to use collection binding in a way that ensures all elements within it are combined using the AND operator? For instance, in the given example query:

(d/q '[:find ?e
       :in $ [[?attr ?val] ...]
       :where [?e ?attr ?val]]
      db [[:attr/name "value1"]
          [:attr/name2 "value2"]])
I want to query entities where both conditions are simultaneously satisfied.

favila17:10:33

but consider just building the query dynamically

Black17:10:49

can I use rules for that?

favila17:10:47

you can use rules, as the answer I linked to states, but the query is static

favila17:10:03

to make a query dynamically, you would build it with clojure

Black17:10:31

thank you

favila17:10:05

(let [db :DB
      conditions [[:attr/name "value1"]
                  [:attr/name2 "value2"]]
      args (into [db]
                 cat
                 conditions)
      in (into ['$]
               (mapcat (fn [i] [(symbol nil (str "?attr" i))
                                (symbol nil (str "?val" i))]))
               (range (count conditions)))
      where (into []
                  (map (fn [i] ['?e
                                (symbol nil (str "?attr" i))
                                (symbol nil (str "?val" i))]))
                  (range (count conditions)))
      query-map {:query {:find '[?e]
                         :in in
                         :where where}
                 :args args}]
  query-map)

favila17:10:15

{:query {:find [?e], :in [$ ?attr0 ?val0 ?attr1 ?val1], :where [[?e ?attr0 ?val0] [?e ?attr1 ?val1]]},
 :args [:DB :attr/name "value1" :attr/name2 "value2"]}

Black17:10:17

I have used rules, but your solution is probably better for caching?

(defn find-entity
  [db & args]
  (let [attrs (if (= 1 (count args))
                [[:db/id (first args)]]
                (partition 2 args))
        rules [(into ['(applied ?e)]
                     (map (fn [[attr val]]
                            ['?e attr val])
                          attrs))]]
    (when-let [e (d/q '[:find ?e .
                        :in $ %
                        :where (applied ?e)]
                      db rules)]
      (d/touch (d/entity db e)))))

favila17:10:59

oh, I see, you build the rule body dynamically

favila17:10:09

I misunderstood what you meant

favila17:10:03

I’m not sure there’s any significant difference between building a rule with inlined values and building a query with inlined values.

favila17:10:43

if you want max query plan caching (and know the overhead is significant), what I did is best because it can share the plan per count of args

🙏 1
favila17:10:27

actually the static approaches in the stackoverflow answer are best

favila17:10:39

but they are much harder to comprehend and I’m not too sure about performance

Black17:10:48

Ok I will decide based on performance, thank you very much

twashing18:10:34

One other things re. https://clojurians.slack.com/archives/C03RZMDSH/p1696519994616739?thread_ts=1696519320.426689&amp;cid=C03RZMDSH. And beyond that, I’m failing with :db.error/read-transactor-location-failed Could not read transactor location from storage when trying to d/create-database.

./bin/repl

user=> (require '[datomic.api :as d])
user=> (def db-uri "datomic:...")
user=> (d/create-database db-uri)

Execution error (Exceptions$IllegalArgumentExceptionInfo) at datomic.error/arg (error.clj:79).
:db.error/read-transactor-location-failed Could not read transactor location from storage
👉:skin-tone-5: Why can’t the Peer d/create-database, if the “db.sql” “table.sql” and “user.sql” have been setup ok? SQL Server and the Transactor are https://clojurians-log.clojureverse.org/datomic/2020-04-22. All the preambles seem ok. • ok. docker run ... -d • ok. Checked SQL Server connection in Datagrip • ok. Datomic DB creation scripts (for sqlserver) are good: “db.sql” “table.sql” and “user.sql” (analog to https://docs.datomic.com/pro/overview/storage.html#sql-database) • ok. Succeeded with ./bin/transactor config/sqlserver-transactor.properties

ghadi18:10:34

is the transactor successfully running?

twashing19:10:33

Yes! Although without much information.

./bin/transactor config/sqlserver-transactor.properties
Launching with Java options -server -Xms1g -Xmx1g -XX:+UseG1GC -XX:MaxGCPauseMillis=50

System started

ghadi19:10:48

does the sql user (you're using datomic) have access to read+write from those tables?

twashing19:10:36

It should… config/sqlserver-transactor.properties

protocol=sql
host=localhost
port=4334

sql-url=jdbc:;database=datomic;encrypt=true;trustServerCertificate=true;
sql-user=datomic
sql-password=...
sql-driver-class=com.microsoft.sqlserver.jdbc.SQLServerDriver
sql-validation-query=select 1
memory-index-threshold=32m
memory-index-max=256m
object-cache-max=128m
SQL
-- db.sql
USE master;
GO

CREATE DATABASE datomic COLLATE Latin1_General_100_CI_AS_SC_UTF8;
GO

-- table.sql
USE datomic;
GO

CREATE TABLE datomic_kvs
(
 id varchar(900) NOT NULL,
 rev int,
 map nvarchar(max),
 val varbinary(max),
 CONSTRAINT pk_id PRIMARY KEY (id)
);
GO

GRANT ALL ON datomic_kvs TO public;
GO

-- MSSQL-user.sql
CREATE LOGIN datomic WITH PASSWORD = '...';
GO

USE datomic;
GO

CREATE USER datomic FOR LOGIN datomic;
GO

ghadi19:10:39

do you see rows in the table?

ghadi19:10:57

this would tell you that the transactor can successfully write

ghadi19:10:07

also check for errors in your $DATOMIC/logs dir

👀 1
twashing19:10:54

do you see rows in the table?” Now that you mention it… In Datagrip, no I don’t see the table I created

twashing19:10:20

also check for errors in your $DATOMIC/logs dir” Ermm, just a bunch of Transactor heartbeats in there.

uwo19:10:33

Apologies if this has already been brought up: I don't see any mention of :query-stats here https://docs.datomic.com/pro/clojure/index.html

Keith22:10:58

Thanks for letting us know, we’ll look into it!

clojure-spin 1
twashing20:10:38

@ghadi Ok I got it sorted. Thanks very much for helping me troubleshoot. The table was there after all. The Peer just couldn’t authenticate when connecting. When creating the DB using bin/repl, the DB url has to be in the correct format.

(def db-uri "datomic:...")