sqlite3 inside C++ (stored procedure or complex sql with TABLE and its INDEX)

I'm trying sqlite3 in my C++ app. I have done:

• sqlite3 my.db
• sqlite> CREATE TABLE links(UrlAsID VARCHAR(255) PRIMARY KEY, Owner VARCHAR(255), ......, CreationTime INTEGER);
• sqlite> CREATE INDEX linkIDs ON links(UrlAsID, CreationTime ASC);

Then I opened a connection from the C++ code to the database.

From within the code I have an Url object. Now I have todo:

// chec开发者_JS百科k if url is in index (and in table as well)
string urlID = sqlite3_exec("SELECT UrlAsID FROM linkIDs WHERE UrlAsID = " + Url.id + ";");
if (urlID.empty()) {
    sqlite3_exec("INSERT INTO links VALUES (" + Url.properties + ");");
    sqlite3_exec("INSERT INTO linkIDs VALUES (" + Url.id + "," + int(Url.creationTime) + ");");
} else {
    sqlite3_exec("UPDATE links SET (CreationTime = " + int(Url.creationTime) + "," + ... + ") WHERE UrlAsID = " + Url.id + ";");
    sqlite3_exec("UPDATE linkIDs SET (CreationTime = " + int(Url.creationTime) + ") WHERE UrlAsID = " + Url.id + ";");
}

I thought to create a stored procedure or to use a complex SQL statement to encapsulate the above logic. Could you please provide me with more precise code to accomplish this.

Thank you in advance!

Your statements should be wrapped in a transaction for both safety and speed. Furthermore, you should use prepared statements with parameters, again for both safety and speed (different sort of safety, but even so). And you should use INSERT OR REPLACE with a suitable COALESCE. All of this is irrespective of which language you're embedding within, but the links are to relevant syntax.

SQLite doesn't support stored procedures: http://www.sqlite.org/whentouse.html

If the SQL code is complex I would try to put in a .sql file, then load into a variable and execute.

SQLite does not support stored procedures. The most you can do is use prepared statements. You should also use the SQLite binding methods to set parameters, instead of string concatenation. Read the introduction here: http://sqlite.org/cintro.html