"[SQLITE_ERROR] SQL error or missing database (near "DATABASE": syntax error)"

Question

I am trying to create a new database file named test in the folder D:\sqlite, using JDBC, as follows:

import java.sql.*;

public class Class1 {

    private static void createNewDataBase(){

        String url = "jdbc:sqlite:D:sqlite/";
        Connection conn = null;
        Statement statement = null;

        try{

            conn = DriverManager.getConnection(url);
            System.out.println("Connection Established");
            statement = conn.createStatement();

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        try {
            statement.execute("CREATE DATABASE test");

        } catch (SQLException e) {
            System.out.println(e.getMessage());

        } finally{
            try{
                if(statement != null)
                    statement.close();

            }catch(SQLException e){
                System.out.println(e.getMessage());
            }

            try{
                if(conn != null)
                    conn.close();

            }catch(SQLException e){
                System.out.println(e.getMessage());
            }
        }
    }

    public static void main(String[] args){
        createNewDataBase();
    }

}

When I run the project, I get the following output:

Connection Established
[SQLITE_ERROR] SQL error or missing database (near "DATABASE": syntax error)

Process finished with exit code 0

It says the syntax is wrong but I can't find the error. I've looked for answers for similar questions, but none solved my problem. Can anyone tell me what's the problem? Thanks in advance!

There is no CREATE DATABASE statement in SQLite. See documentation. Why did you think there was? — Andreas
– Andreas, Commented Dec 28, 2017 at 20:09
Possibly a rhetorical question, but what's the point of SQLite if it doesn't use SQL, it has less functions, and adds an additional layer of complexity? — Jacob H
– Jacob H, Commented Dec 28, 2017 at 20:10
@JacobH - Ummm, because it's SQL, but Lite? It's a light weight server-less relational database engine. It doesn't have CREATE DATABASE because you'd just create a different instance instead. There is no consideration to users, because the executing process is the only user. Etc, etc. — MatBailie
– MatBailie, Commented Dec 28, 2017 at 20:30

DevilsHnd - 退した · Accepted Answer · 2017-12-28 21:00:20Z

As already stated by @Andreas, there is no CREATE DATABASE SQL statement for SQLite. To create a new SQLite database you need to merely make a connection to one and it is automatically created (you do however need to ensure that the D:/sqlite/ path already exists within the local file system).

The following code should create an empty (no tables) SQLite Database named MyNewDatabase.sqlite within the folder sqlite located in the root of drive D of your local file system:

String dbPath = "D:/sqlite/MyNewDatabase.sqlite";
Connection conn = null;
try {
   Class.forName("org.sqlite.JDBC");
   conn = DriverManager.getConnection("jdbc:sqlite:" + dbPath;);
}
catch (ClassNotFoundException ex) { 
    ex.printStackTrace();
}
catch (SQLException ex) { 
    ex.printStackTrace();
}
finally {
    try { 
        if (conn != null) { 
            conn.close(); 
        } 
    } 
    catch (SQLException e) { e.printStackTrace(); }
}

Now you need to create one or more Tables for your new database to make it useful. SQLite does accept the CREATE TABLE SQL statement and you could do this through the same connection if desired.

MikeT · Accepted Answer · 2017-12-29 08:14:50Z

The database exists, it has been connected i.e the database is the connection, which is basically the file. Hence there is no SQL for CREATE DATABASE.

Inside the database you would typically create tables (and perhaps other components).

e.g. CREATE TABLE mytable (mycolumn TEXT, myothercolumn INTEGER) which would create a table named mytable with 2 columns mycolumn and myothercolumn (the former being defined with a column type of TEXT, the latter with a column type of INTEGER).

As such, if you were to change :-

     statement.execute("CREATE DATABASE test");

to :-

    statement.execute("CREATE TABLE mytable (mycolumn TEXT, myothercolumn INTEGER)");

Note you'd only want to do this once, otherwise it would fail as the table already exists, so you could use CREATE TABLE IF NOT EXISTS mytable (mycolumn TEXT, myothercolumn INTEGER), of cousre it depends upon your requirements.

You may find that it will work. Obviously you will need to do other things such as add some data as the table will be empty.

Perhaps then try

    statement.execute("INSERT INTO mytable VALUES('fred',100)");

Every time this is run a new ROW will be added to the table name mytable.

Collectives™ on Stack Overflow

"[SQLITE_ERROR] SQL error or missing database (near "DATABASE": syntax error)"

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related