I'm not an expert, but I suspect it's the SQL_STATIC that's causing the problem. This means SQLite doesn't take a copy of your data and so when you do the sqlite3_step, all values are the same. Try SQL_TRANSIENT.
Sent from Samsung Galaxy Note -------- Original message -------- From: lyx <[email protected]> Date: 04/05/2014 06:11 (GMT+00:00) To: [email protected] Subject: [sqlite] sqlite3_bind_text issue Hi Experts, I'm trying to use sqlite3_bind_text in my database insert procedure using sqlite 3.8.4.3. I have a very simple table and the '.schema' output is as following: sqlite> .schema CREATE TABLE ins_test(col1 text, col2 text, col3 text, col4 text, col5 text, col6 text); CREATE INDEX idx_ins_test on ins_test(col1, col2, col3); I want to insert the following data into ins_test table. col1000000000|col2000000000|col3000000000|col4000000000|col5000000000|col6000000000 col1000000001|col2000000001|col3000000001|col4000000001|col5000000001|col6000000001 col1000000002|col2000000002|col3000000002|col4000000002|col5000000002|col6000000002 ... So I wrote a program to insert the test data. Since the sql statement is not changing and we only need to change the insert value of the insert statement. I plan to use sqlite3_bind_text. The core program is as following. Please check if there is anything wrong in my code. rc = sqlite3_open(argv[1], &db); if (rc != SQLITE_OK) { printf ("Error call sqlite3_open function, return value is [%d]. Error message is [%s]\n", rc, sqlite3_errmsg(db)); sqlite3_close(db); return -1; } sprintf (sql_str, "insert into ins_test values (?,?,?,?,?,?)"); if (SQLITE_OK != (rc = sqlite3_prepare_v2(db, sql_str, -1, &stmt, NULL))) { printf ("sqlite3_prepare_v2 error code is [%d]", rc); sqlite3_close(db); return -1; } for (i = 0; i < 100000000; i++) { memset (tmp_str, 0, sizeof(tmp_str)); sprintf (tmp_str, "col1%09d", i); sqlite3_bind_text (stmt, 1, tmp_str, -1, SQLITE_STATIC); memset (tmp_str, 0, sizeof(tmp_str)); sprintf (tmp_str, "col2%09d", i); sqlite3_bind_text (stmt, 2, tmp_str, -1, SQLITE_STATIC); memset (tmp_str, 0, sizeof(tmp_str)); sprintf (tmp_str, "col3%09d", i); sqlite3_bind_text (stmt, 3, tmp_str, -1, SQLITE_STATIC); memset (tmp_str, 0, sizeof(tmp_str)); sprintf (tmp_str, "col4%09d", i); sqlite3_bind_text (stmt, 4, tmp_str, -1, SQLITE_STATIC); memset (tmp_str, 0, sizeof(tmp_str)); sprintf (tmp_str, "col5%09d", i); sqlite3_bind_text (stmt, 5, tmp_str, -1, SQLITE_STATIC); memset (tmp_str, 0, sizeof(tmp_str)); sprintf (tmp_str, "col6%09d", i); sqlite3_bind_text (stmt, 6, tmp_str, -1, SQLITE_STATIC); if (SQLITE_DONE != (rc = sqlite3_step(stmt))) { printf ("sqlite3_step error code is [%d]\n", rc); sqlite3_finalize(stmt); sqlite3_close(db); return -1; } } After I compiled and ran my test program, I found the data in ins_test table was not what I want. Data in ins_test was as following. I was so confused that why all the data in every columns starting with col6. Is there anything wrong with my test code using sqlite3_bind_text? sqlite> select * from ins_test order by col1,col2,col3 limit 10; col6000000000|col6000000000|col6000000000|col6000000000|col6000000000|col6000000000 col6000000001|col6000000001|col6000000001|col6000000001|col6000000001|col6000000001 col6000000002|col6000000002|col6000000002|col6000000002|col6000000002|col6000000002 col6000000003|col6000000003|col6000000003|col6000000003|col6000000003|col6000000003 col6000000004|col6000000004|col6000000004|col6000000004|col6000000004|col6000000004 col6000000005|col6000000005|col6000000005|col6000000005|col6000000005|col6000000005 col6000000006|col6000000006|col6000000006|col6000000006|col6000000006|col6000000006 col6000000007|col6000000007|col6000000007|col6000000007|col6000000007|col6000000007 col6000000008|col6000000008|col6000000008|col6000000008|col6000000008|col6000000008 col6000000009|col6000000009|col6000000009|col6000000009|col6000000009|col6000000009 sqlite> I also attached my full test program in attachment. Please help. Thanks a lot! _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
