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?

mordack550 · Accepted Answer · 2014-04-18 15:38:30Z

4

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.

answered Apr 18, 2014 at 15:38

mordack550

5024 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

conan_z · Accepted Answer · 2014-04-18 15:32:33Z

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.

answered Apr 18, 2014 at 15:32

conan_z

4606 silver badges17 bronze badges

Comments

Saeid Mohammadi · Accepted Answer · 2021-10-18 13:15:46Z

1

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.

edited Oct 18, 2021 at 13:15

Saeid Mohammadi

4491 gold badge3 silver badges12 bronze badges

answered Apr 16, 2019 at 6:03

Geeky64

292 bronze badges

Comments

Chetan Bhagat · Accepted Answer · 2017-01-12 13:09:57Z

0

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");

answered Jan 12, 2017 at 13:09

Chetan Bhagat

5726 silver badges21 bronze badges

Comments

Mohammed Sarfaraz · Accepted Answer · 2018-02-07 08:55:09Z

0

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";

edited Feb 7, 2018 at 8:55

answered Feb 4, 2018 at 18:26

Mohammed Sarfaraz

4706 silver badges11 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related