Allegro.cc - Online Community

Allegro.cc Forums » Programming Questions » SQLite++

This thread is locked; no one can reply to it. rss feed Print
SQLite++
Raf Vermeulen
Member #7,166
April 2006

In my current project, I need to manage a small database. It basically needs a way to store, delete or edit pairs of words (one word in one language, and its translation in another language) and retrieve the pair corresponding to one word the user gave in. Best thing for this seems to be using SQL, so I've looked into SQLite3 and SQLite++. Dumb thing's that SQLite3's syntax's not compatible with SQLite++ and I can't find a decent tutorial or reference for that. Anybody here that knows how to work with it and can help me? Thanks in advance :)

CGamesPlay
Member #2,559
July 2002
avatar

SQLite3's SQL parsing should be mostly the same as SQLite++'s, if not for small differences in maybe the PRAGMA command. SQLite++... well, the manual for it is the best reference. I haven't used it myself; I just use SQLite3 from C++.

--
Tomasu: Every time you read this: hugging!

Ryan Patterson - <http://cgamesplay.com/>

Raf Vermeulen
Member #7,166
April 2006

Hmm, quick question 'bout SQLite3 then. Do you know how to pass variables into the queries? Like "INSERT INTO table_name VALUES (string1, string2, string3)"? That's mainly what I had a problem with with SQLite3. Doesn't support strings, it seems, and I honestly don't know how to do this with char[]s:-/

CGamesPlay
Member #2,559
July 2002
avatar

You can use sprintf, but since you're using C++, use stringstreams:

#include <sstream>

std::stringstream sql;
sql << "INSERT INTO table VALUES ( '" << my_quoted_string << " ', CURRENT_TIMESTAMP );";
sqlite3_stmt* stmt = sqlite3_prepare(sql.str().c_str());

[append]
Also, you can do this with std::strings:

std::string sql;
sql = "SELECT * FROM table;";
sqlite3_stmt* stmt = sqlite3_prepare(sql.c_str());

--
Tomasu: Every time you read this: hugging!

Ryan Patterson - <http://cgamesplay.com/>

Raf Vermeulen
Member #7,166
April 2006

And after that, I just use sqlite3_execute()? So basically, the sqlite3_stmt type is the equivalent of the result of PHP's mysql_query(). Didn't even know that part:-X Thanks for the help :)

Thomas Fjellstrom
Member #476
June 2000
avatar

hmm, sqlite...

Passing variables:

sqlite3_stmt *stmt = sqlite3_prepare("INSERT INTO table VALUES ( ?, ? );");
sqlite3_bind_int(stmt, 0, 0xDEADBEEF);
sqlite3_bind_int(stmt, 1, 0xDECAFBAD);

Those bind methods sure can be handy.

--
Thomas Fjellstrom - [website] - [email] - [Allegro Wiki] - [Allegro TODO]
"If you can't think of a better solution, don't try to make a better solution." -- weapon_S
"The less evidence we have for what we believe is certain, the more violently we defend beliefs against those who don't agree" -- https://twitter.com/neiltyson/status/592870205409353730

Raf Vermeulen
Member #7,166
April 2006

Hummm, not as simple as it sounded.

  std::string query;
  query = "CREATE TABLE dictionary (word text, translation text);";
  sqlite3_stmt *sql_statement = sqlite3_prepare(query.c_str());

That gives this compiling error:
cannot convert `const char*' to `sqlite3*' for argument `1' to `int sqlite3_prepare(sqlite3*, const char*, int, sqlite3_stmt**, const char**)'

Thomas Fjellstrom
Member #476
June 2000
avatar

Since query is a C++ std::string, you have to use the c_str() method on it to pass to functions that take a char* (just as was said by CGames)

ie:

std::string query = "CREATE TABLE dictionary ('word text', 'translation text');";
sqlite3_stmt *sql_statement = sqlite3_prepare(query.c_str());

Also take a look at my previous post, since using placeholders means you dont have to quote the data yourself.

--
Thomas Fjellstrom - [website] - [email] - [Allegro Wiki] - [Allegro TODO]
"If you can't think of a better solution, don't try to make a better solution." -- weapon_S
"The less evidence we have for what we believe is certain, the more violently we defend beliefs against those who don't agree" -- https://twitter.com/neiltyson/status/592870205409353730

Raf Vermeulen
Member #7,166
April 2006

I edited my post when I noticed the .c_string() function, but it didn't change a thing. I just tried this:
sqlite3_stmt *sql_statement = sqlite3_prepare("CREATE TABLE dictionary (word text, translation text);");
Error message:
cannot convert `const char*' to `sqlite3*' for argument `1' to `int sqlite3_prepare(sqlite3*, const char*, int, sqlite3_stmt**, const char**)'

Could it be that my query's not correct? I'm not too familiar with SQLite queries, only MySQL queries, so could've put some mistakes in there?

Thomas Fjellstrom
Member #476
June 2000
avatar

Sorry, we both (cgames and I) made a small mistake giving you the prepare function, first off, let me say, lookup the sqlite3 docs, they are very usefull, and second, sqlite3_prepare takes the sqlite3* db handle as first arg, as that error is trying to tell you.

--
Thomas Fjellstrom - [website] - [email] - [Allegro Wiki] - [Allegro TODO]
"If you can't think of a better solution, don't try to make a better solution." -- weapon_S
"The less evidence we have for what we believe is certain, the more violently we defend beliefs against those who don't agree" -- https://twitter.com/neiltyson/status/592870205409353730

Raf Vermeulen
Member #7,166
April 2006

Hmm, I've got all parameters right, up til these two now:
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
const char **pzTail /* OUT: Pointer to unused portion of zSql */
Could you guys give a full example of how to set up and execute an SQL query, please? I can't figure these commands out otherwise:-[ (Those double pointer things're confusing me)

Thomas Fjellstrom
Member #476
June 2000
avatar

Heres an example using the exec method, I found through google:

1 static int callback(void *NotUsed, int argc, char **argv, char **azColName){
2 NotUsed=0;
3 int i;
4 for(i=0; i<argc; i++){
5 printf("%s = %s\n", azColName<i>, argv<i> ? argv<i>: "NULL");
6 }
7 printf("\n");
8 return 0;
9 }
10 
11 int main(int argc, char **argv){
12 sqlite3 *db;
13 char *zErrMsg = 0;
14 int rc;
15 
16 if( argc!=3 ){
17 fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
18 exit(1);
19 }
20 rc = sqlite3_open(argv[1], &db);
21 if( rc ){
22 fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
23 sqlite3_close(db);
24 exit(1);
25 }
26 rc = sqlite3_exec(db, argv[2], callback, 0, &zErrMsg);
27 if( rc!=SQLITE_OK ){
28 fprintf(stderr, "SQL error: %s\n", zErrMsg);
29 }
30 sqlite3_close(db);
31 return 0;
32 }

A simple exaple using sqlite3_step instead of sqlite3_exec to follow.

edit, next example:

1 int main(int argc, char **argv){
2 sqlite3 *db = NULL;
3 sqlite3_stmt *stmt = NULL;
4 char *zErrMsg = 0;
5 int rc;
6 
7 if( argc!=3 ){
8 fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
9 exit(1);
10 }
11 rc = sqlite3_open(argv[1], &db);
12 if( rc ){
13 fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
14 sqlite3_close(db);
15 exit(1);
16 }
17 rc = sqlite3_prepare(db, argv[2], strlen(argv[2]), &stmt, &zErrMsg);
18 if( rc!=SQLITE_OK ){
19 fprintf(stderr, "SQL error: %s\n", zErrMsg);
20 }
21 
22 while(sqlite3_step(stmt) == SQLITE_ROW) {
23 printf("YAY A NEW ROW!\n");
24 }
25 
26 if(sqlite3_errcode(db)!=SQLITE_OK) {
27 printf("Mommie, there was an error: %s\n", sqlite3_errmsg(db));
28 }
29 
30 sqlite3_close(db);
31 return 0;
32 }

edit2, NEITHER of these have been tested, use at your own risk, I'm not even guaranteeing that they'll compile.

--
Thomas Fjellstrom - [website] - [email] - [Allegro Wiki] - [Allegro TODO]
"If you can't think of a better solution, don't try to make a better solution." -- weapon_S
"The less evidence we have for what we believe is certain, the more violently we defend beliefs against those who don't agree" -- https://twitter.com/neiltyson/status/592870205409353730

Raf Vermeulen
Member #7,166
April 2006

Thanks for the help. I don't fully understand those codes (argv[1], argv[2]? Where do they come from?) I managed to fill in my code with those examples, though, but it still doesn't work.

1 int answer;
2 int open_dbase;
3 char *zErrMsg = 0;
4 int result;
5 sqlite3_stmt *stmt = NULL;
6 char file[(LENGTH + 1) * MAX_BYTES_PER_CHAR] = "data.dsb";
7 
8 
9 answer = alert("Add to database?", 0, 0, "&Yes", "&No", 'y', 'n');
10 
11 if(answer == 1) {
12 open_dbase = sqlite3_open(file, &database);
13 if(open_dbase) {
14 alert("Problem opening database.", 0, 0, "&Ok", NULL, 'o', NULL);
15 
16 std::string query;
17 query = "CREATE TABLE dictionary (word TEXT, translation TEXT);";
18 result = sqlite3_prepare(database, query.c_str(), query.length(), &stmt, &zErrMsg);

The error it gives, is this:
invalid conversion from `char**' to `const char**'
initializing argument 5 of `int sqlite3_prepare(sqlite3*, const char*, int, sqlite3_stmt**, const char**)'

Both point to the sqlite3_prepare line.

Thomas Fjellstrom
Member #476
June 2000
avatar

const your zErrMsg

and the argv[] comes from the main(int argc, char **argv) function, never used program arguments before?

--
Thomas Fjellstrom - [website] - [email] - [Allegro Wiki] - [Allegro TODO]
"If you can't think of a better solution, don't try to make a better solution." -- weapon_S
"The less evidence we have for what we believe is certain, the more violently we defend beliefs against those who don't agree" -- https://twitter.com/neiltyson/status/592870205409353730

Raf Vermeulen
Member #7,166
April 2006

      std::string query;
      query = "CREATE TABLE dictionary (word TEXT, translation TEXT);";
      sqlite3_exec(database, query.c_str(), callback, 0, NULL);

That's compiled, and didn't give any runtime errors. I'm curious as to whether it actually created the table or not. Suppose I'll see that when putting in some test values.

I've never actually used or heard 'bout program arguments, so the argv[n] and argc're completely new to me. Are they any use?

Thomas Fjellstrom
Member #476
June 2000
avatar

if exec didnt give any errors, nor did anything you did in the call back, then yes, it created the table.

Quote:

I've never actually used or heard 'bout program arguments, so the argv[n] and argc're completely new to me. Are they any use?

How and where did you learn to program?

Every tutorial for C and C++ that I know of starts with just a main(int argc, char **argv) function and lets you pass stuff to it when running :o

argv is how you pass info, like file names, or config arguments to a program. rather basic stuff.

--
Thomas Fjellstrom - [website] - [email] - [Allegro Wiki] - [Allegro TODO]
"If you can't think of a better solution, don't try to make a better solution." -- weapon_S
"The less evidence we have for what we believe is certain, the more violently we defend beliefs against those who don't agree" -- https://twitter.com/neiltyson/status/592870205409353730

Raf Vermeulen
Member #7,166
April 2006

Ahh, those're parameters you give in the commandline part then. Never worked with those before. Never needed to for what I've been coding.

Go to: