SQL error or missing database (near ":01": syntax error) in SQLite

Question

I'm trying to update a SQLite table with Java. This is the relevant part of the code:

    try {
        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection("jdbc:sqlite:test.db");
        c.setAutoCommit(false);
        System.out.println("Opened database successfully");
        stmt = c.createStatement();
        String sql = "INSERT INTO AMBROZIJA (LEVEL,DATE,TIME) " +
                "VALUES ("+koncentracijaAmbrozije+","+date+","+time+");";
        System.out.println(sql);
        stmt.executeUpdate(sql);
        stmt.close();
        c.commit();
        c.close();
    } catch ( Exception e ) {
        System.err.println( e.getClass().getName() + ": " + e.getMessage() );
        System.exit(0);
    }
    System.out.println("Records created successfully");

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

INSERT INTO AMBROZIJA (LEVEL,DATE,TIME) VALUES (6.5,2020-08-21,18:01:44.087078100);
org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near ":01": syntax error)

Process finished with exit code 0

The "near x" in (near ":01": syntax error) part is from the minutes part of time so it changes with every execution.

How can I fix this error (I don't know much about SQLite)?

Date and time values should be enclosed inside single quotes, but it is better to learn to use Prepared statements: https://www.sqlitetutorial.net/sqlite-java/insert/ — forpas, Aug 21 '20 at 16:15
You should look into [prepared statements](https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html) — Mureinik, Aug 21 '20 at 16:37

Sujitmohanty30 · Accepted Answer · 2020-08-21T16:52:05.330

To fix your problem columns time but the date should be passed within quotes and you can add the quotes while concatenating value,

date = "'"+ date +"'";
time = "'"+ time +"'";        
String sql = "INSERT INTO AMBROZIJA (LEVEL,DATE,TIME)" +
          " VALUES (" + koncentracijaAmbrozije + "," + date + "," + time+ ");";

But it is good practice and always recommended to bind the values instead concatenating it using prepareStatement as also suggested in the comment but its little bit more work when you deal with date columns. found a good SO link which you can take a look.

score 1 · Answer 2 · answered Aug 21 '20 at 17:46

1

Pass it as string with single quotes, or you can use strftime() method like this:

strftime('%Y-%m-%d','2020-08-21')

You may take a look at the manual for all valid substitutions Date And Time Functions

answered Aug 21 '20 at 17:46

Mauricio Zárate

440
1
4
8

SQL error or missing database (near ":01": syntax error) in SQLite

2 Answers2