3
votes

I have the following code for SQLite:

std::vector<std::vector<std::string> > InternalDatabaseManager::query(std::string query)
{
    sqlite3_stmt *statement;
    std::vector<std::vector<std::string> > results;

    if(sqlite3_prepare_v2(internalDbManager, query.c_str(), -1, &statement, 0) == SQLITE_OK)
    {
        int cols = sqlite3_column_count(statement);
        int result = 0;
        while(true)
        {
            result = sqlite3_step(statement);

            std::vector<std::string> values;
            if(result == SQLITE_ROW)
            {
                for(int col = 0; col < cols; col++)
                {
                    std::string s;
                    char *ptr = (char*)sqlite3_column_text(statement, col);
                    if(ptr) s = ptr;

                    values.push_back(s);
                }
                results.push_back(values);
            } else
            {
                break;
            }
        }

        sqlite3_finalize(statement);
    }

    std::string error = sqlite3_errmsg(internalDbManager);
    if(error != "not an error") std::cout << query << " " << error << std::endl;

    return results;
}

When I try to pass a query string like:

INSERT INTO CpuUsage (NODE_ID, TIME_ID, CORE_ID, USER, NICE, SYSMODE, IDLE, IOWAIT, IRQ, SOFTIRQ, STEAL, GUEST) VALUES (1, 1, -1, 1014711, 117915, 175551, 5908257, 112996, 2613, 4359, 0, 0); INSERT INTO CpuUsage (NODE_ID, TIME_ID, CORE_ID, USER, NICE, SYSMODE, IDLE, IOWAIT, IRQ, SOFTIRQ, STEAL, GUEST) VALUES (1, 1, 0, 1014711, 117915, 175551, 5908257, 112996, 2613, 4359, 0, 0); INSERT INTO CpuUsage (NODE_ID, TIME_ID, CORE_ID, USER, NICE, SYSMODE, IDLE, IOWAIT, IRQ, SOFTIRQ, STEAL, GUEST) VALUES (1, 1, 1, 1014711, 117915, 175551, 5908257, 112996, 2613, 4359, 0, 0); 

It results just inserting the first insert. Using some other tool lite SQLiteStudio it performs ok.

Any ideas to help me, please?

Thanks,

Pedro

EDIT

My query is a std::string.

const char** pzTail;
const char* q = query.c_str();

int result = -1;
do {
    result = sqlite3_prepare_v2(internalDbManager, q, -1, &statement, pzTail);
    q = *pzTail;
}
while(result == SQLITE_OK);

This gives me Description: cannot convert ‘const char*’ to ‘const char**’ for argument ‘5’ to ‘int sqlite3_prepare_v2(sqlite3*, const char*, int, sqlite3_stmt*, const char*)’

1

1 Answers

7
votes

SQLite's prepare_v2 will only create a statement from the first insert in your string. You can think of it as a "pop front" mechanism.

int sqlite3_prepare_v2(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);

From http://www.sqlite.org/c3ref/prepare.html

If pzTail is not NULL then *pzTail is made to point to the first byte past the end of the first SQL statement in zSql. These routines only compile the first statement in zSql, so *pzTail is left pointing to what remains uncompiled.

The pzTail parameter will point to the rest of the inserts, so you can loop through them all until they have all been prepared.

The other option is to only do one insert at a time, which makes the rest of your handling code a little bit simpler usually.

Typically I have seen people do this sort of thing under the impression that they will be evaluated under the same transaction. This is not the case, though. The second one may fail and the first and third will still succeed.