I am already doing the prepare_v2/reset/bind_int/step/finalize set. Before I
do more intensive testing to confirm my earlier findings about not much time
difference between using the separate statement compared with using exec, I
want to confirm that it is true that I can't do bind_int on integer primary
key.
If I do "insert into functions values(?,?)" and use bind_int on the primary
key, nothing is added to the table. But if I do "insert into functions
values(NULL,?)", then the primary key got autoincremented even though I didn't
declare it as autoincrement and data got added properly:
const char *tablesCreateCmd =
// Create table for functions
"CREATE TABLE functions ("
" function_id integer PRIMARY KEY,"
" function_name varchar2(1000) not null"
");\n"
I am already using the latest sqlite3 source.
Thanks,
Bella
-----Original Message-----
From: [email protected] [mailto:[email protected]]
On Behalf Of Marian Cascaval
Sent: Wednesday, January 26, 2011 7:26 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] how to create sqlite3_value structure to be used with
sqlite3_bind_value()?
I'm no expert either in C or in SQLite but what Igor is pointing out is
extremely important, because I have noticed myself the benefit of using
sqlite3_prepare_v2() and sqlite3_reset() instead of just using sqlite3_exec()
within iterations. And this is what SQLite documentation is talking about.
The speed optimization in my C++ program was incredible and the code structure
was like this (just like Igor's recommendations):
sqlite3_exec(... "BEGIN TRANSACTION" ...);
sqlite3_prepare_v2();
for (int i=0; i<100; i++){
sqlite3_reset();
sqlite3_bind_int();
sqlite3_step();
}
sqlite3_exec(... "COMMIT TRANSACTION" ...);
Sorry if this is too basic and you already knew it, but I felt like sharing my
basic knowledge :P
Marian Cascaval
________________________________
From: Igor Tandetnik <[email protected]>
To: [email protected]
Sent: Wed, January 26, 2011 2:51:38 PM
Subject: Re: [sqlite] how to create sqlite3_value structure to be used with
sqlite3_bind_value()?
Bella Chan <[email protected]> wrote:
> I am surprised to see that C is slower than Perl when inserting lots of data
>into DB sequentially as I have 100 columns in a row
> so I have been making 100 bind_int calls while Perl I only need to do execute
>once.
You are doing something wrong. Are you re-preparing the statement for each row,
by any chance? Are you grouping your inserts within a transaction? Show some
code.
> Trying to see if I can use bind_value()
> instead but no clue ho to create the sqlite3_value structure.
sqlite3_bind_value is only useful inside custom functions. In any case, your
problem lies elsewhere.
--
Igor Tandetnik
_______________________________________________
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
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
