Cannot execute an sqlite SQL file to create a database

Question

I have an sql (text file) with a long transaction to create a database.

I am using Kompex sqlite c++ library for this:

// open database
Kompex::SQLiteDatabase *pDatabase = new Kompex::SQLiteDatabase(CT2A(strDBFilename), SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, 0);

// create statement instance for sql queries/statements
Kompex::SQLiteStatement *pStmt = new Kompex::SQLiteStatement(pDatabase);

strSQLCreateDB=get_file_contents(strSQLFilename).c_str(); // load the SQL file

try {
    pStmt->Sql(strSQLCreateDB);
    pStmt->ExecuteAndFree();        
} catch(Kompex::SQLiteException &exception)
{
    std::cerr << "Exception Occured" << std::endl;
    exception.Show();
}

pDatabase->Close();

The database is created with 0 bytes and no errors. I am interested in any sample code even without the kompex library, to execute this sql transaction which creates the db.

executing the database from sqlite browser creates the database without errors.

this is a part of the SQL command file

/* Disable Foreign Keys */
pragma foreign_keys = off;
/* Begin Transaction */
begin transaction;

/* Database [scanlog(1)] */
pragma auto_vacuum=0;
pragma encoding='UTF-8';
pragma page_size=1024;

/* Drop table [ApplicationNames] */
drop table if exists [ApplicationNames];

/* Table structure [ApplicationNames] */
CREATE TABLE [ApplicationNames] (
  [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  [ApplicationName] TEXT, 
  [Icon] TEXT);
CREATE INDEX [IndexApps] ON [ApplicationNames] ([Id], [ApplicationName]);

/* Data [ApplicationNames] */
insert into [ApplicationNames] values(1, 'Windows System', null);
insert into [ApplicationNames] values(2, 'Internet Explorer', null); 
insert into [ApplicationNames] values(3, 'Google Chrome', null);

/* Commit Transaction */
commit transaction;

/* Enable Foreign Keys */
pragma foreign_keys = on;

Mario · Accepted Answer · 2013-11-09 01:35:23Z

1

This seems to work:

// open connection to a DB
    if (SQLITE_OK != (ret = sqlite3_open_v2(CT2A(strDBFilename), &pDb, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)))
    {
        TRACE("Failed to open SQLite conn: %d\n", ret);
        break;
    } 

    // prepare the statement
    if (SQLITE_OK != (ret = sqlite3_prepare_v2(pDb, CT2A(strSQLCreateDB), -1, &query, NULL)))
    {
        TRACE("Failed to prepare SQLite script: %d, %s\n", ret, sqlite3_errmsg(pDb));
        break;
    }

    if (SQLITE_OK != (ret=sqlite3_exec(pDb, CT2A(strSQLCreateDB), NULL, 0, NULL)))
    {
        TRACE("Failed to execute SQLite script: %d, %s\n", ret, sqlite3_errmsg(pDb));
        break;
    }

answered Nov 9, 2013 at 1:35

Mario

15k21 gold badges64 silver badges117 bronze badges

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

Comments

CL. · Accepted Answer · 2013-11-09 08:51:51Z

1

Your problem is that the Sql and sqlite3_prepare functions execute only the first SQL command in the string.

To execute multiple commands in a string, you must use sqlite3_exec:

if (SQLITE_OK != (ret = sqlite3_open_v2(CT2A(strDBFilename), &pDb, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)))
{
    TRACE("Failed to open SQLite conn: %d\n", ret);
    break;
} 

if (SQLITE_OK != (ret = sqlite3_exec(pDb, CT2A(strSQLCreateDB), NULL, 0, NULL)))
{
    TRACE("Failed to execute SQLite script: %d, %s\n", ret, sqlite3_errmsg(pDb));
    break;
}

answered Nov 9, 2013 at 8:51

CL.

182k18 gold badges241 silver badges282 bronze badges

2 Comments

Volomike Over a year ago

What include do I need for the CT2A() function and what does it do? I had to use strDBFilename.c_str() because I had std::string in my case. Also, you might want to denote that strSQLCreateDB is a string of SQL statements, not a file name to create. That wasn't apparent at first to me.

CL. Over a year ago

@Volomike All this stuff comes from the question's code.

rollstuhlfahrer · Accepted Answer · 2018-03-26 14:37:29Z

0

view.sql have the statement

Take database definition from file, this way is easy to manage the definition of tables, views, and triggers:

{
    std::ifstream ifs("view.sql");
    std::string content(
        (std::istreambuf_iterator<char>(ifs) ),        
        (std::istreambuf_iterator<char>())
    );

    size_t n_pos,o_pos=0;
    n_pos = content.find(";");
    while (n_pos!=content.npos) {
        ++n_pos;
        sqlite::execute(con,content.substr(o_pos,n_pos-o_pos).c_str(),true);
        o_pos=n_pos;
        n_pos= content.find(";",o_pos);
    }
}
sqlite::execute(con,"VACUUM;",true);

edited Mar 26, 2018 at 14:37

rollstuhlfahrer

4,0789 gold badges28 silver badges38 bronze badges

answered Mar 26, 2018 at 14:26

Helvio Hild

611 silver badge3 bronze badges

1 Comment

rollstuhlfahrer Over a year ago

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value.

Collectives™ on Stack Overflow

Cannot execute an sqlite SQL file to create a database

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related