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

Question

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:

// check 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!

score 2 · Accepted Answer · edited May 23 '17 at 12:19

2

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.

edited May 23 '17 at 12:19

Community

1
1

answered Oct 02 '11 at 11:01

Donal Fellows

120,022
18
134
199

Thank you! I understand, but can't yet write a working program in my case. Experimenting. – Sophie Sperner Oct 02 '11 at 11:26
Well, I thought I'd point to the things you need to bear in mind and tried to include pointers to places that explain with syntax in C++ (or C or ObjC; the differences are miniscule for the SQLite API). – Donal Fellows Oct 03 '11 at 10:56

score 1 · Answer 2 · answered Oct 02 '11 at 10:30

1

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.

answered Oct 02 '11 at 10:30

DavidEG

5,589
2
23
43

Sort-of. It _does_ provide a mechanism to allow you to add your own functions, but that relies on the language/runtime that SQLite is embedded within. OTOH, it's support for prepared statements is excellent and there's no deep saving from reusing prepared statements from one connection with another due to the nature of the way SQLite does its compilation internally. (I suppose I ought to go somewhere with this comment, but it's a bit of a brain-dump. :-)) – Donal Fellows Oct 03 '11 at 11:00

score 0 · Answer 3 · answered Oct 02 '11 at 10:31

0

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

answered Oct 02 '11 at 10:31

Don Reba

12,756
3
41
55

Thanks. The problem with their documentation they do not give examples, instead a plain theory. – Sophie Sperner Oct 02 '11 at 10:42

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

3 Answers3