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!
int main(int argc, char ** argv)
{
sqlite3 * db = NULL;
int rc = 0;
sqlite3_stmt *stmt = NULL;
char sql_str[1024];
char *err_msg = NULL;
int i;
char tmp_str[100];
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;
}
}
sqlite3_finalize(stmt);
sqlite3_close(db);
return 0;
}_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
