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
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++.
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:-/
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());
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
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.
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**)'
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.
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?
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.
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)
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.
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.
const your zErrMsg
and the argv[] comes from the main(int argc, char **argv) function, never used program arguments before?
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?
if exec didnt give any errors, nor did anything you did in the call back, then yes, it created the table.
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
argv is how you pass info, like file names, or config arguments to a program. rather basic stuff.
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.