How do I use the SQLite3 import command using the C API?

Question

I have the following code:

int rc;
rc = sqlite3_exec(sqlite3_database, ".import mydata.csv mytable", callback, 0, &errMsg);

After this gets run, errMsg contains this error message:

near ".": syntax error

I assume that it does not recognize the import command. However, this command works when running it from the sqlite3 program on the command line.

I need to be able to use the import command directly in my program. Is there a way I can do this? The reason I need to use the import command is because doing inserts for each line of the CSV file takes over 5 minutes, and the import command takes a split second.

Answer 1

.import is a part of the command line program interface and not the C API I believe. You can (as i have done) set up all your data in a SQLite3 database file using another tool and then include that database file in your app. Then when you open it in your code, the data is already there.

Answer 2

Here's a related question and subsequent answers on SO (though not iPhone specific):

http://stackoverflow.com/questions/364017/faster-bulk-inserts-in-sqlite3

Answer 3

The command-line shell's .import is not part of the C API; the sqlite3 tool implements it as

sqlite3_prepare(..., "INSERT INTO '...' VALUES (?, ..., ?)", ...);
sqlite3_exec(..., "BEGIN", ...);
for (each entry) {
    for (each column) sqlite3_bind_text(..., column, ...);
    sqlite3_step(...);
}
sqlite3_exec(..., "COMMIT", ...);

with some error-checking (ROLLBACK if anything goes wrong) and handling the prepared statement (sqlite3_reset, sqlite3_finalize).

Answer 4

I suspect the insert is taking so long because you're having SQLite reparse your INSERT statement for each row (that is, using sqlite3_exec()) rather than using a parameterized prepared statement (that is, using sqlite3_prepare_v2(), sqlite_bind_*() and sqlite3_step()). As ephemient said above, that's how import is implemented internally.

Using a parameterized statement should achieve the same performance as .import, I believe.