What is causing the "near ".": syntax error" in my SQLite query?

Question

I am trying to output my table as a .csv file. I am using the sqlite-jdbc java library to execute my query. Here are the statements:

.mode csv
.output test.csv
select * from geninfo;
.output stdout

and this is my java code:

try {
            try {
                // create a database connection
                Class.forName("org.sqlite.JDBC");
            } catch (ClassNotFoundException ex) {
                Logger.getLogger(SQLite.class.getName()).log(Level.SEVERE, null, ex);
            }
            connection = DriverManager.getConnection("jdbc:sqlite:702Data.db");
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.
            statement.executeUpdate("create table if not exists geninfo (id TEXT, DeviceID TEXT)");
            statement.executeUpdate(".mode csv");
            statement.executeUpdate(".output test.csv");
            statement.executeUpdate("select * from geninfo;");
            statement.executeUpdate(".output stdout");

            ResultSet rs = statement.executeQuery("select * from geninfo");
            while (rs.next()) {
                // read the result set
                System.out.println("DeviceID = " + rs.getString("DeviceID"));
                System.out.println("id = " + rs.getInt("id"));
            }
        } catch (SQLException e) {
            // if the error message is "out of memory", 
            // it probably means no database file is found
            System.err.println(e.getMessage());
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                // connection close failed.
                System.err.println(e);
            }
        }
    }

I have tried adding parenthesis around the period and also removing the period and both cause the same syntax error.

A command like `.mode csv` doesn't seem to fit the javadoc `Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.` for `executeUpdate(String)`. You might need `execute(String)`. — Sotirios Delimanolis, Jul 02 '13 at 14:57
@SotiriosDelimanolis Good find, I tried it and received the same error though, `[SQLITE_ERROR] SQL error or missing database (near ".": syntax error)` — jeanqueq, Jul 02 '13 at 15:03
They look more like SQLite console statements, not pure SQL (which JDBC can only handle). Try executing a shell command to open the console using ProcessBuilder and feed it the commands via its InputStream — Bohemian, Jul 02 '13 at 15:04
@Bohemian In the linked question, it seems like it's from an sqlite console. — Sotirios Delimanolis, Jul 02 '13 at 15:05
@SotiriosDelimanolis yes. Using process builder you could feed it such commands via the input stream — Bohemian, Jul 02 '13 at 15:07
@Bohemian thanks for the explanation, I decided it would be easier to use a [CSVWriter library](http://opencsv.sourceforge.net/). You should submit your answer below so you can get credit for it — jeanqueq, Jul 02 '13 at 19:03

score 1 · Accepted Answer · answered Jul 02 '13 at 20:59

Those commands are SQLite console commands, not pure SQL. JDBC can only handle SQL.

If you want to do this from java, try executing the shell command to open the console using ProcessBuilder and feed it the commands via its InputStream.

However, if calling from java is not necessary, you may be better served writing a shell script of some sort and using that directly, or calling the script from java.

If all you are using SQLite for is to parse the CSV file, consider not using SQLite at all and instead load the data directly from the file into your code using one of the several open source CSV parsing libraries out there. This approach will be order of magnitude faster and simpler.

What is causing the "near ".": syntax error" in my SQLite query?

1 Answers1