Syntax error in SQLite query

Question

I am trying to insert a large number of records into a SQLite database. I get the above error if I try to use the sqlite3_exec C-API.

The code looks like this:

ret = sqlite_exec(db_p,".import file.txt table", NULL, NULL, NULL);

I know that the .import is command line, but can there be any way that you can do a extremely large insert of records that takes minimal time. I have read through previous bulk insert code and attempted to make changes but these are not providing the desired results.

Is there not a way to directly insert the string into the tables without having intermediate API's being called?

Could you add a snippet of code that is generating SQL and calling sqlite3_exec? This error is usually caused by either a syntax error or formatting error. — D.Shawley
– D.Shawley, Commented Feb 23, 2011 at 12:36
ret = sqlite_exec(db_p,".import file.txt table", NULL, NULL, NULL); — SportyMe
– SportyMe, Commented Feb 23, 2011 at 12:40

Benoit · Accepted Answer · 2011-02-23 12:45:20Z

3

.import is most probably not available via the API. However there's one crucial thing to speed up inserts: wrap them in a transaction.

BEGIN;
lots of insert statements here;
COMMIT;

Without this, sqlite will need to write to the file after each insert to keep the ACID principle. The transaction let's it write to file later in bulk.

edited Feb 23, 2011 at 12:45

Benoit

79.7k24 gold badges226 silver badges241 bronze badges

answered Feb 23, 2011 at 12:38

Andreas Gohr

5,0155 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Benoit Over a year ago

This is the right answer. SQLite is a library. .import and other dot-commands are part of the SQLite shell, which is just a sample program (though official) built upon SQLite. However you should add semicolons after BEGIN and COMMIT.

SportyMe Over a year ago

do I need to have the sqlite3_exec between each transaction for individual inserts that I do here? Thanks :-)

D.Shawley Over a year ago

@SportyMe: take a look at the source code for the shell utility (sqlite.org/src/…). Search for "import" including the quotes. They create a transaction using BEGIN/END, use a prepared statement for the INSERT calling sql3_bind_text for each column and sql3_step for each row. The code is quite readable.

Daniel Hilgarth · Accepted Answer · 2011-02-23 12:34:28Z

0

The answer to the syntax error could well be, that your strings are not enclosed in quotes in your SQL statement.

answered Feb 23, 2011 at 12:34

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

1 Comment

SportyMe Over a year ago

There has to be a way to use the command line equivalents in the C API functions? I have posted the code snippet that the error is reported as above.

Collectives™ on Stack Overflow

Syntax error in SQLite query

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related