java.sql.SQLException: SQL logic error or missing database, SQLite, JDBC

Question

Hi All, I ahve created database connection with SQLite using JDBC in java. My sql statements execute properly. But sometimes I get the following error while i use conn.commit() java.sql.SQLException: SQL logic error or missing database

Can anyone please help me how to avoid this type of problem. Can anyone give me better approach of calling JDBC programs

Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:/home/Data/database.db3");
conn.setAutoCommit(false);

String query = "Update Chits set BlockedForChit = 0 where ServerChitID = '" + serverChitId + "' AND ChitGatewayID = '" + chitGatewayId + "'";
        Statement stmt = conn.createStatement();
        try {

            stmt.execute(query);

            conn.commit();
            stmt.close();
            stmt = null;
        }

Thanks Sunil Kumar Sahoo

Answer 1

Can your variables serverChitId & chitGatewayId contain characters that would corrupt the SQL? It is usually safer to use PreparedStatements:

PreparedStatement ps = conn.prepareStatement("Update Chits set BlockedForChit = 0 where ServerChitID = ? AND ChitGatewayID = ?");
ps.setString(1, serverChitId);
ps.setString(2, chitGatewayId);
ps.executeUpdate();

This way the JDBC driver is responsible for making sure the necessary escapes are made to the strings.