Java and SQLite [SQLITE_ERROR] SQL error or missing database ()

Question

I'm trying SQLite with Java, this is the first time using both together, here is the code:

package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
/**
 *
 * @author Alessio
 */
public class DB {
 public static void main(String[] args){
 Connection c = null;
 Statement stmt = null;
 try{
 Class.forName("org.sqlite.JDBC");
 c = DriverManager.getConnection("jdbc:sqlite:test.db");
 stmt = c.createStatement();
 String sql = "CREATE TABLE COMPANY " +
 "(ID INT PRIMARY KEY NOT NULL," +
 " NAME TEXT NOT NULL, " + 
 " AGE INT NOT NULL, " + 
 " ADDRESS CHAR(50), " + 
 " SALARY REAL)"; 
 stmt.executeUpdate(sql);
 stmt.close();
 c.close();
 }
 catch(Exception e){
 System.err.println(e.getClass().getName() + ": " + e.getMessage());
 System.exit(0);
 }
 System.out.println("Database opened succesfully!!!");
 }
}

When I run the code for the first time I have any error, my result in console is:

run:
Database opened succesfully!!!
BUILD SUCCESSFUL (total time: 1 second)

while the second time I get

run:
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database ()
BUILD SUCCESSFUL (total time: 1 second)

What am I doing wrong?

Answer 1

Your question is not so detailed. What do you have to do for get a normal situation again? Do you have to delete you DB file?

Anyway, I would say that the error is caused by the table already existing in the Database. If you want to try a simple query for debug purpose, just try

SELECT date('now');

or something not dependant on the DB structure. Try a generic query or check if the table exists and drop it before re-creating it.

Answer 2

If you are executing the exact same code, then I guess the problem is you are creating same table twice.

It went well for the first time because there is no such table in the database. It failed on the second time because there is already an exactly same table named COMPANY.

Answer 3

The error caused by the fact that SQL, creates the exact table every time it runs. Which does not conform best SQL practices.

In your SQL Query, Change the

"CREATE TABLE COMPANY " +
 "(ID INT PRIMARY KEY NOT NULL," +
 " NAME TEXT NOT NULL, " + 
 " AGE INT NOT NULL, " + 
 " ADDRESS CHAR(50), " + 
 " SALARY REAL)";
to
"CREATE TABLE IF NOT EXISTS COMPANY " +
 "(ID INT PRIMARY KEY NOT NULL," +
 " NAME TEXT NOT NULL, " + 
 " AGE INT NOT NULL, " + 
 " ADDRESS CHAR(50), " + 
 " SALARY REAL)";

The IF NOT EXIST statement checks if the table exists if no, it creates a new one else it skips creating the table.

Answer 4

First way your table name COMPANY as already exists so you can not create same table name(COMPANY) with same database like test

and second you can write proper or full getconnection URL like

Connection con = null;
Class.forName("org.sqlite.JDBC");
//getConnection(String url)
con = DriverManager.getConnection("jdbc:sqlite:C:\\Documents and Settings\\demo\\Test_demo.db");
 //SQL error or missing database 
System.out.println("Operation done successfully");

Answer 5

I think you should answer this question by seeing the code.You are creating the Table on boot and again you are running the same query twice.It is bound to happened.Either you drop the table and recreate or check the table it it exist and then add.

Other Case: This error also happens if your connection string is wrong.The URL which is pointing to SQLite db file.I suggest you place it in resource folder and access as below public static String connectionString = "jdbc:sqlite::resource:localdata.db";