No, you only need to create the statement once. And bind all variables inside the loop. This at least saves computation time. Additionally you should use sqlite3_mprintf instead of sprintf to protect against sql injection (if that is an issue for you.)
You're already doing it with the blob, why not with the other fields? Mike -----Ursprüngliche Nachricht----- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Yang WenYuan Gesendet: Mittwoch, 9. April 2008 08:11 An: General Discussion of SQLite Database Betreff: Re: [sqlite] WinCE Memory Problem Thanks, Mike. Because, each record has different barcode, tagtype, and printdata. That means, I need to call: sprintf( sqlcmd, "INSERT INTO TEST( BARCODE, TAGTYPE, PRINTDATA ) VALUES( %s, %d, ? );", chrBarCode, tagtype ); rc = sqlite3_prepare( db, sqlcmd, -1, &stat, 0 ); to update the contents of the each records inside the loop. Am I right? Any suggestion? Thanks. WenYuan --- Michael Ruck <[EMAIL PROTECTED]> wrote: > You should only prepare the statement once before the loop. The only > thing you should do in the loop itself is bind varying data and call > sqlite_step. > There's no need to call prepare, reset, finalize inside the loop. If > chrPrintData doesn't change you can also move bind_blob in front of > the loop. > > Mike > > -----Ursprüngliche Nachricht----- > Von: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Im Auftrag von Yang WenYuan > Gesendet: Mittwoch, 9. April 2008 06:08 > An: sqlite-users@sqlite.org > Betreff: [sqlite] WinCE Memory Problem > > I use the Sqlite in the PDA which is WinCE OS. I need to add more than > 200,000 records. I used sqlite3_prepare-> > sqlite3_bind_blob->sqlite3_step->sqlite3_finalize to write each record > in to Database. However, I found that after each record is inserted, > the PDA memory became larger and larger. In the end, the whole PDA > memory is occupied by this application and the system halt. Is there > any thing I miss to release the memory? > Following is my code: > > sqlite3 *db; > sqlite3_stmt * stat; > char *zErrMsg = 0; > char sqlcmd[ 512 ]; > int rc; > char chrBarCode[ 16 ], chrPrintData[ 512 ]; int tagtype; > > > > for( i = 0; i < 2000000; i ++ ) > { > sprintf( sqlcmd, "INSERT INTO TEST( BARCODE, TAGTYPE, PRINTDATA ) > VALUES( %s, %d, ? );", chrBarCode, tagtype ); > > rc = sqlite3_prepare( db, sqlcmd, -1, &stat, 0 ); > if( rc != SQLITE_OK ) > { > sqlite3_close(db); > return -1; > } > > rc = sqlite3_bind_blob( stat, 1, chrPrintData, length, NULL ); > if( rc != SQLITE_OK ) > { > sqlite3_close(db); > return -1; > } > > rc = sqlite3_step( stat ); > if( rc != SQLITE_DONE ) > { > sqlite3_close(db); > return -1; > } > > rc = sqlite3_reset( stat ); > if( rc != SQLITE_OK ) > { > sqlite3_close(db); > return -1; > } > > rc = sqlite3_finalize( stat ); > if( rc != SQLITE_OK ) > { > sqlite3_close(db); > return -1; > } > > } > > > WenYuan > > > > > ______________________________________________________________________ > Search, browse and book your hotels and flights through Yahoo! Travel. > http://sg.travel.yahoo.com > _______________________________________________ > 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 > ______________________________________________________________________ Search, browse and book your hotels and flights through Yahoo! Travel. http://sg.travel.yahoo.com _______________________________________________ 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
