using sqlite3

[loonster]

After searching long and hard, there really isn't any good
documentation for using sqlite3 with clojure 1.3. Any help connecting
to an existing sqlite3 db and performing selects and updates greatly
appreciated. Tim

[Mark Rathwell]

Haven't tested, but seems like this should get you started with korma:

lein:

[korma "0.2.1"]
[sqlitejdbc "0.5.6"]

korma [1]:

(defdb mydb {:classname "org.sqlite.JDBC"
:subprotocol "sqlite"
:subname "db/mydb.sqlite3"}) ;; Location of the db

[1] http://sqlkorma.com/

> --
> You received this message because you are subscribed to the Google
> Groups "Clojure" group.
> To post to this group, send email to clo...@googlegroups.com
> Note that posts from new members are moderated - please be patient with your first post.
> To unsubscribe from this group, send email to
> clojure+u...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/clojure?hl=en
>

[Tim Sally]

Hi,

From the cursory glance, it seems Korma supports sqlite3.

http://sqlkorma.com/docs

https://github.com/ibdknox/Korma/blob/master/src/korma/db.clj#L100

Best,

Tim

On Tue, Nov 8, 2011 at 3:47 PM, loonster <tbu...@acm.org> wrote:

[willyh]

project.clj:
---------------------------------------------------------------------------------------------------------------------------------------
(defproject testsqlite "1.0.0-SNAPSHOT"
  :description "FIXME: write description"
  :disable-deps-clean false
  :dependencies [[org.clojure/clojure "1.3.0"]
                 [org.clojure/clojure-contrib "1.2.0"]
                 [org.clojure/java.jdbc "0.1.0"]
                 [org.xerial/sqlite-jdbc "3.7.2"]]
  :main testsqlite.core)

core.clj:
---------------------------------------------------------------------------------------------------------------------------------------
(ns testsqlite.core
  (:import [java.io File])
  (:require [clojure.java.jdbc :as sql])
  (:gen-class))

(def db-name "test.db")

(def db {
         :classname "org.sqlite.JDBC"
         :subprotocol "sqlite"    ; Protocol to use
         :subname db-name    ; Location of the db
         })

(def new-db-conn (merge db {:create true}))

(defn create-tables
 "Creates the tables needed."
 []
 (sql/create-table
  :members
  [:id :integer "PRIMARY KEY"]
  [:name "varchar(32)"]
  [:age :integer]
  [:programmer "tinyint"]))


(defn insert-record
  [name age programmer?]
  (sql/insert-values
   :members
   [:name
    :age
    :programmer]
   [name
    age
    programmer?]))


(defn get-records
  [record-fn]
  (sql/with-query-results
    rs
    ["select * from members"]
    (doseq [r rs]
      (record-fn r))))

(defn delete-record
  [id]
  (sql/delete-rows :members ["id = ?" id]))

(defn -main
  [& args]
  (when (not (.exists (File. db-name)))
    (sql/with-connection new-db-conn
      (create-tables)))
  (cond (= (nth args 0) "insert")
        (sql/with-connection db
          (insert-record (nth args 1)
                         (Integer/parseInt (nth args 2))
                         (Integer/parseInt (nth args 3))))
        (= (nth args 0) "dump")
        (sql/with-connection db
          (get-records println))
        (= (nth args 0) "delete")
        (sql/with-connection db
          (delete-record (Integer/parseInt (nth args 1))))
        )
  )

[willyh]

Use this project.clj instead (eliminating the reference to clojure-contrib):

(defproject testsqlite "1.0.0-SNAPSHOT"
  :description "FIXME: write description"
  :disable-deps-clean false
  :dependencies [[org.clojure/clojure "1.3.0"]

[loonster]

Thanks all. In the end, the following works like a charm. I found
that clojure/java/jdbc namespace has a conflict with clojure/core,
both having resultset-seq function, hence the exclusion:

(ns psyN.core
(:use [clojure.java.jdbc])
(defproject psyN "0.1.0"
:dependencies [[org.clojure/clojure "1.3.0"]
[org.clojure/java.jdbc "0.1.1"]
[org.xerial/sqlite-jdbc "3.7.2"]])
;**********************************************************
(ns psyN.core
(:use [clojure.java.jdbc])
(:refer-clojure :exclude [resultset-seq]))

(def db
{:classname "org.sqlite.JDBC"
:subprotocol "sqlite"

:subname "/path/to the sqlite file/psyN/db/thx"
})

;get count of table.notes records:
(def rec-cnt
(with-connection db
(with-query-results rs ["select * from notes"]
(count rs))))

(def output
(with-connection db
(with-query-results rs ["SELECT * FROM notes WHERE id = ?" (+ 1
(rand-int rec-cnt))]
(into {} rs))))