Conman with sqlite

[simax]

I'm using sqlite and yesql in my project (but not luminus). I need the snake-case and camel-case functionality and thought I'd try and give conman a go.

I was connecting successfully using pure yesql with the following

(def db-spec
  {:classname "org.sqlite.JDBC"
   :subprotocol "sqlite"
   :subname "data/metime.sqlite"})

Now when I'm trying to use conman I guessed at the following spec:

(def db-spec
  {:dataSourceClassName "org.sqlite.SQLiteDataSource"
   :jdbc-url "jdbc:sqlite/data/metime.sqlite"
 })

When I try (cc/connect! conn db-spec)

I get the following error:

SQLException No suitable driver  java.sql.DriverManager.getDriver (DriverManager.java:278)

Any ideas what my db-spec should be set to? and indeed will conman work in such a situation. i.e. Just using Yesql and not luminus?

[yogthos]

It should work, conman uses HikariCP for connection pooling, and it looks like Hikari works with sqlite, here's a discussion on that. The error might indicate that you're missing a dependency for the driver. Make sure that org.sqlite.SQLiteDataSource is provided on the classpath.

[simax]

Yes, I've seen that discussion you refer to. Good to know you think it should work. I'll try your suggestion. Thanks.

[simax]

I'm from the .Net and windows world so putting org.sqlite.SQLiteDataSource on the classpath is not something I'm familiar with. When I had sqlite working with vanilla yesql I just added a dependency to my project.clj file and everything just worked. Ive just downloaded sqlite-jdbc-3.7.2.jar from the xerial bitbucket site. Do I put that somewhere? Bit lost here, could you advise further?

[yogthos]

you should be able to just add it as a dependency in project.clj under the :dependencies key as [org.xerial/sqlite-jdbc "3.8.11.2"]

[simax]

I already have a dependency in my project.clj file [org.xerial/sqlite-jdbc "3.7.2"] the one I was using with vanilla yesql.

I have moved my sql definition file into my resources/sql folder which is suggested in the readme. It looks like this:

-- name: db-get-all-departments
-- Get all departments
select
-- Department info
d.id as 'department-id', d.department, d.manager_id,
-- Manager info
e.firstname as 'manager-firstname', e.lastname as 'manager-lastname', e.email as 'manager-email'
from departments d left join employees e on d.manager_id = e.id
order by d.department

And this is the start of the data access file I'm trying to load in the REPL to see if I can connect to my DB.

(ns metime.data.departments
  (:require [yesql.core :refer [defqueries defquery]]
            [conman.core :as cc]
            [metime.data.database :as db]
            [camel-snake-kebab.core :refer :all]
            [camel-snake-kebab.extras :refer [transform-keys]]))

(defonce ^:dynamic conn (atom nil))

(cc/bind-connection conn "sql/metime.sql" )  

(cc/connect! conn db/sl-spec) ; NOTE: This is the line referred to in the error 

It throws an error that starts like this:

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
#error {
 :cause No suitable driver
 :via
 [{:type clojure.lang.Compiler$CompilerException
   :message java.lang.Exception: Error occured while connecting to the database!, compiling:(metime/data/departments.clj:14:1)
   :at [clojure.lang.Compiler load Compiler.java 7239]}
  {:type java.lang.Exception
   :message Error occured while connecting to the database!
   :at [conman.core$connect_BANG_ invoke core.clj 58]}
  {:type java.lang.RuntimeException
   :message Unable to get driver instance for jdbcUrl=jdbc:sqlite/data/metime.sqlite
   :at [com.zaxxer.hikari.util.DriverDataSource <init> DriverDataSource.java 88]}
  {:type java.sql.SQLException
   :message No suitable driver
   :at [java.sql.DriverManager getDriver DriverManager.java 278]}]
 :trace...

It fails with the driver error when its trying to connect to the DB. The sl-spec looks like this:

(def db-spec
  {:dataSourceClassName "org.sqlite.SQLiteDataSource"
   :jdbc-url "jdbc:sqlite/data/metime.sqlite"
 })

Any idea what's wrong ?

[yogthos]

Check that the jar has the same class name that you're using, that's the only thing I can think of. If you've got the dependency included then all the classes in the jar will be on the classpath. Another option could be to create an instance of the DataSource object and pass it using the : datasource key, e.g:

{:datasource
 (doto (org.sqlite.SQLiteDataSource.)
   (.setUrl "jdbc:sqlite/data/metime.sqlite"))}

[simax]

Using :datasource allowed me to connect. Thanks again for your help.