Re: [sqlite] Executing SQL from file

[Dennis Cote]

deBooza (sent by Nabble.com) wrote:

Hi
I'm using sqlite in a c++ program, using functions sqlite3_open, sqlite3_exec, 
sqlite3_close etc.

I am trying to import a lot of data, probably around 100,000 rows+. I have 
found it
quicker if I format the data into SQL statements and then use
the shell statement .read to read in the SQL from file and
populate the database, similar to the following:

.read c:\tmp\sql.txt

While this is a solution it's not an ideal one. I would much
prefer to do it programatically, something like this:
void main()
{
        while(not end of data)
        {
                Write data to file
        }
        
        ExecuteSQLFromFile( SQLFile )
}

Does anyone know how to do this?

What is the equivelent to .read?

Or does anyone know of a better way?

DFB




--
View this message in context: 
http://www.nabble.com/Executing-SQL-from-file-t1040732.html#a2702793
Sent from the SQLite forum at Nabble.com.

 

I do exactly this to initialize the tables in empty database files. This 
should give you the basic idea.

void ExecuteSQLFromFile(string SQLFile)
{
   ExecuteSQLFromString(LoadFile(SQLFile));
}

void ExecuteSQLFromString(string SQL)
{
   sqlite3_exec(DB, SQL.c_str(), NULL, NULL, NULL);
}

string LoadFile(string Filename)
{
   ifstream file(Filename);
   stringstream s;
   s << file.rdbuf();
   return s.str();
}

The function sqlite3_exec() will execute all the statements in the 
supplied string, one after the other. I do this to execute a sequence of 
different create table statements. In your case you are trying to 
execute the same insert statement over and over to populate a table. 
This is probably best done by using a precompiled statement executed 
multiple times directly in your read loop. Instead of writing the data 
to a file in the loop and then executing the file, you just bind your 
new data to the precompiled statement and then execute the statement 
once for each row of data. Your main function should look something like 
this.

void main()
{
        open the database file
        
        create table using sqlite3_exec()       

        sqlite3_stmt *s = Prepare Insert Statement using sqlite3_prepare()

        while(not end of data)
        {
                ExecuteStatement(db, s, data)           
        }

        destroy s using sqlite3_finalize()
}

ExecuteStatement(db, s, data)
{
   Bind data To Statement s using sqlite3_bind...()
   Execute s using sqlite3_step()
   Reset s using sqlite3_reset()
}

To get much better insert speed, you should execute the loop inside a 
transaction. To do this your main would become:

void main()
{
        open the database file
        
        create table using sqlite3_exec()       
        
        begin a transaction using sqlite3_exec()

        sqlite3_stmt *s = Prepare Insert Statement using sqlite3_prepare()

        while(not end of data)
        {
                ExecuteStatement(s, data)               
        }

        destroy s using sqlite3_finalize()

        end the transaction using sqlite3_exec()
}

HTH
Dennis Cote