SQLite: "SQLException [SQLITE_ERROR] SQL error or missing database"

[mnicky]

Hello.

I tried to connect to the SQLite database, but my attempts failed:

org.sqlite.JDBC
;=> org.sqlite.JDBC

(use 'korma.db)

(defdb my_db (sqlite3 {:db "sqlite.db"}))

(use 'korma.core)

(exec-raw ["CREATE TABLE my_table (id INTEGER NOT NULL, value TEXT(25), PRIMARY KEY (id))"])
;=> nil

(exec-raw ["SELECT * FROM my_table"])

Failure to execute query with SQL:
SELECT * FROM my_table :: nil
SQLException:
Message: [SQLITE_ERROR] SQL error or missing database (no such table: my_table)
SQLState: null
Error Code: 0
SQLException [SQLITE_ERROR] SQL error or missing database (no such table: my_table) org.sqlite.DB.newSQLException (DB.java:383)

The file "sqlite.db" is created, but it has 0 bytes.

I'm using sqlite-jdbc 3.7.2 and korma 0.3.0-beta11

I also tried with (defentity ...) and normal (select ...) or connecting to the existing SQLite database and doing select from the table created with clojure.java.jdbc as well, but in both cases got the same exception...

Is there anybody that could point me to the potential cause?

Thanks in advance.
Marek.

[Kei Tsuji]

Hi,

From: mnicky <markus...@gmail.com>
Subject: SQLite: "SQLException [SQLITE_ERROR] SQL error or missing database"
Date: Fri, 15 Jun 2012 14:31:02 -0700 (PDT)

> (exec-raw ["CREATE TABLE my_table (id INTEGER NOT NULL, value TEXT(25),
> PRIMARY KEY (id))"])

...

> I also tried with (defentity ...) and normal (select ...) or connecting to
> the existing SQLite database and doing select from the table created with
> clojure.java.jdbc as well, but in both cases got the same exception...
>
> Is there anybody that could point me to the potential cause?

Currently, korma doesn't seem to support jdbc/do-commands.

So if you want to create tables, you can use clojure.java.jdbc.

(require '(clojure.java [jdbc :as jdbc]))
(jdbc/with-connection (db/sqlite3 {:db "sqlite.db"})
(jdbc/do-commands
"CREATE TABLE my_table (id INTEGER NOT NULL primary key, value TEXT);"))

or jdbc/create-table if you like that style.

--
ktsuji

[mnicky]

As I wrote before: I've also tried to create the table with clojure.java.jdbc (successfully) and then to do select from this table using Korma, and that gave me the same exception.

And btw, although Korma doesn't support DDL SQL queries via its API, you can use the (exec-raw ...) fn to send arbitrary SQL queries. I verified that with PostgreSQL, where slight modification of the above command [1] worked as expected.

So it seems to be problem only with SQLite...

Marek.

[1] (exec-raw ["CREATE TABLE my_table (id INTEGER NOT NULL, value VARCHAR(25), PRIMARY KEY (id))"])

[Kei Tsuji]

From: mnicky <markus...@gmail.com>
Subject: Re: SQLite: "SQLException [SQLITE_ERROR] SQL error or missing database"
Date: Sat, 16 Jun 2012 10:01:47 -0700 (PDT)

> As I wrote before: I've also tried to create the table with clojure.java.jdbc (successfully) and then to do select from this table using Korma, and that gave me the same
> exception.

Oh I didn't notice that you have already created table with
jdbc successfully. Sorry about that.
But in my environment, after creating the table with
jdbc,(or any other way like directly using sqlite3 command)
below worked without exception.
(korma/exec-raw ["select count(*) cnt from my_table;"])

I'm using same version as you are and tested on both Mac OS X and ubuntu.
[korma "0.3.0-beta11"]
[org.xerial/sqlite-jdbc "3.7.2"]

--
ktsuji