Thanks Igor! I am talking about "Insert". For Micorosft SQL server I don't need to "binding parameters " inside the loop. Only bind the first time(outside the loop) and inside the loop I just copied the new data to the variable(structure) that binded outside the loop. So for Microsoft SQL server no need to bind (sqlite3_bind_int64 ...) inside the loop only bind the first time which is outside the loop. For Sqlite the binding for insert statement must be inside the loop. Thanks, JP
________________________________ From: Igor Tandetnik <itandet...@mvps.org> To: sqlite-users@sqlite.org Sent: Tuesday, May 12, 2009 3:00:34 PM Subject: Re: [sqlite] Prepared statements must be generated inside yourtransaction Joanne Pham <joannekp...@yahoo.com> wrote: > Regarding "batch" insert why we need to put the column binding > (sqlite3_bind...) before running sqlite3_step. For example: > sqlite_prepare_v2 > begin transaction > loop thru all the changes > sqlite3_bind .... > sqlite3_step. > end loop > end transaction > > For other database like Microsoft Sql server I only bind the column > once(bind statement outside the loop to the data structure) in the > loop I don't need to bind column again but just copy the new inserted > row to the data structure that already binded outside of the loop. The term "bind" may have two different, unrelated meanings in DBMS literature. You can bind (assign values to) query parameters, and you can bind columns in the resultset. In the latter sense, you provide pointers to local variables for each column in the query; when you step through the resultset, values of those columns are stored in those variables. You seem to be thinking of this second meaning of "bind": you prepare a SELECT statement, bind variables to its columns outside the loop, then call Step inside the loop and column values for each row automatically appear in your variables. Note however that this doesn't make any sense for INSERT statements, which don't produce any resultset. As to binding parameters - of course you want to bind different parameters every time you perform an INSERT, unless you actually want to insert multiple rows with the same values (SQLite happily supports that: if you don't re-bind parameters, they retain their previous values). SQLite supports "bind" in the sense of binding parameters, but not in the sense of binding columns. For the latter, you retrieve column values in the current row using sqlite3_column_* API. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
