How to insert variable at sqlite???

cocos2d-x C++
#1

Hi, everyone.

I have been trying to insert variable at sqlite.
I could connect database, make table and insert non-variable text.
Howerver, I can’t.

Here’s my sourcecode.
I use C++ at Xcode.
I create table: MyTable_1
The table has two column,name and password.
I want to insert variable password column,but I can’t.

If you don’t mind, can you help me??

    //connect to sqlite
    sqlite3 *pDB = NULL;
    char* errMsg = NULL;
    std::string sqlstr;
    int result;
    std::string dbPath = CCFileUtils::sharedFileUtils()->getWritablePath();
    dbPath.append("Settings.db");
    result = sqlite3_open(dbPath.c_str(),&pDB);

    //make table
    result=sqlite3_exec( pDB, "create table MyTable_1( ID integer primary key autoincrement, name nvarchar(32),password nvarchar(32) ) " , NULL, NULL, &errMsg );


    //insert name column
    sqlstr=" insert into MyTable_1( name ) values ( 'Takashi' ) ";
    result = sqlite3_exec( pDB, sqlstr.c_str() , NULL, NULL, &errMsg );


    //insert password column(Here's my Problem)
    //I want to insert string variable into password column.
    string pass;
    pass = "aaaaaaa";
    sqlstr="insert into MyTable_1( password ) values('pass')";


    //closing sqlite
    sqlite3_close(pDB);

Thanks.

#2

With what you did, i think the passwords will become “pass”.
Because your sql statement at time of query execution becomes

insert into MyTable_1( password ) values('pass')

You are not passing the value of variable pass into the sqlStr.
You are just placing it as a string. (what you did might work in php i guess, but not in c**)
Use sprintf to create the sql statement and write the password into sql statement.
<pre>
char sqlStr[256];
char* pass = “aaaaaa”;
// creating sql query statement with value of pass in it
sprintf VALUES (%s)“, pass );
// executing the statement
result = sqlite3_exec(pDB ,sqlStr, NULL, NULL, &errMsg);
// checking result
if(result != SQLITE_OK){
// do error handling
}
sqlite3_close(pDB);
</pre>
By using sprintf (google”c** formaters") you are telling the compiler to place the value of pass where %s is there.
For more info on formatting: http://www.cplusplus.com/reference/cstdio/printf/

#3

Thankyou for your response and I’m Sorry for late my response message.

I tried your sql,but it doesn’ work well.

The errMsg says:

no such column: aaaaaa
Show sql data on scene
#4

Dear, Plato.

I could solved problem!

As for insert string(char), we need ‘’ for SQL statement.
When I attatch’‘(single quatation) for char pass**, it works.
<pre>
char sqlStr[256];
char** pass = “‘aaaaaa’”; //it is attached’’(single quatation)

// creating sql query statement with value of pass in it
sprintf( sqlStr, “INSERT INTO MyTabel_1( password ) VALUES (%s)”, pass );

// executing the statement
result = sqlite3_exec(pDB ,sqlStr, NULL, NULL, &errMsg);

// checking result
if(result != SQLITE_OK){
// do error handling
}

sqlite3_close(pDB);

If you didn’t tell advice, I couldn’t solve my problem. Thank you very much!