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.

Here's a related question and subsequent answers on SO (though not iPhone specific): [http://stackoverflow.com/questions/364017/faster-bulk-inserts-in-sqlite3](http://stackoverflow.com/questions/364017/faster-bulk-inserts-in-sqlite3) — Matt Long, Oct 26 '09 at 18:53

score 10 · Answer 1 · answered Oct 26 '09 at 18:56

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).

score 3 · Answer 2 · answered Apr 21 '13 at 06:02

3

Being a quite old post, I just wanted to update for reference only.

There is an open source API I am maintaining for SQLite3 C/C++ API import/export functionality. The code can be accessed via this link

answered Apr 21 '13 at 06:02

fnisi

1,089
12
21

score 3 · Accepted Answer · answered Oct 26 '09 at 18:52

3

.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.

answered Oct 26 '09 at 18:52

Devin Ceartas

4,571
1
18
32

The prpogram starts out using a preloaded sqlite3 database file. Problem is I am trying to make an update feature and the CSV file is part of downloadable update to the program. – David Barnes Oct 26 '09 at 19:30
I see. Thanks for clarifying. I haven't dealt with this yet in my code, but when I do I was considering downloading the CSV update and running the actual update one line at a time from another thread. – Devin Ceartas Oct 26 '09 at 21:36
I ended up generating a SQLite database from the server using PHP. If I manually do prepared statements and insert each row, it still takes a long time, even on a fast server. But, using PHP's proc_open command I was able to interface with sqlite3 and use the ".import" command. By doing this the file is generated in a split second, and I simply output the file over HTTP and save it in the iPhone's application writeable directory, and my application is able to use the file without a problem. – David Barnes Oct 26 '09 at 22:16
kudos for finding a good solutions and being community minded enough to post it back here. Thanks! – Devin Ceartas Oct 26 '09 at 22:19

score 1 · Answer 4 · answered Oct 26 '09 at 19:09

1

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.

answered Oct 26 '09 at 19:09

Drew Hall

26,700
10
58
79

I was using those commands, also sqlite3_reset and sqlite3_finalize. They work but takes 55 seconds using the iPhone Simulator, but over 5 minutes on the actual iPhone. – David Barnes Oct 26 '09 at 19:27
1

@David Barnes: Was sqlite3_prepare_v2() inside or outside the loop over your file contents? It should be outside, with bind()/step() inside the loop. – Drew Hall Oct 26 '09 at 20:04

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

4 Answers4