hi all,
first of all,thank to everyone who read this mail.
i use the 3.3.12 version of sqlite in my program base flash fs and OS20 os in
my DYV terminate.
the first questuin about memory usage in my memory db is that,previously i do
sqlite3_open(:memory:) and sqlite_exec(CREATE TABLE ) and insert about 20000
records in the db,after that ,each time i use sqlite_exec(SELECT xxx ,xxx FROM
xxx WHERE xxx AND xxx ORDER BY xxx),the memory take by sqlite increas about
135K BYTE. I think that's because when sqlite do sqlite_exec(SELECT xxx ,xxx
FROM xxx WHERE xxx AND xxx ORDER BY xxx) ,it take some memory as cache for data
pulled from DB,but it not free these memory after retrun from
sqlite_exec(SELECT xxx ,xxx FROM xxx WHERE xxx AND xxx ORDER BY xxx), just want
to speed up the next sqlite_exec(SELECT xxx ,xxx FROM xxx WHERE xxx AND xxx
ORDER BY xxx) call. that's right? if that's right,is there are any way to limit
the cache sqlite taked? i read PRAGMA cache_size = Number-of-pages in PRAGMA
document,it's the answer?
the next question is i use precompiled sqlite_stmt as follow code, is there
something wrong in it? in my test program i haven't find the bug, but i'm not
sure about it.
static sqlite3_stmt *stmt_insert_schedule=NULL;
static int Eit_Insert_ScheduleEvent_IntoTable( SQLITE_EVENT *sqlite_event)
{
int result=0;
int rc=0;
if(OS_MutSemTake ( mutexForDB))
printf("OS_MutSemTake ( mutexForDB) 8 error\n");
if(stmt_insert_schedule==NULL)
{
rc = sqlite3_prepare_v2( db, "INSERT INTO " EIT_SCHEDULE" ( " TABLE_ID_DB
","SECTION_NUMBER ","VERSION_NUMBER","
SERVICE_ID","TS_ID","ON_ID","CRC_32","EVENT_ID","START_TIME_UTC_M","
START_TIME_UTC_L","DURATION_3 ","CONTENT_NIBBLE_LEVEL","
PARRENT_RATE_COUNTRY_CODE_3","PARRENT_RATE_RATE","
TSE_DES_REFERENCE_SERVICE_ID","TSE_DES_REFERENCE_EVENT_ID","
ISO_639_LANGUAGE_CODE_SHORT_3","NAME_LENGTH","EVENT_NAME","SHORT_LENGTH","
SHORT_EVENT","DESCRIPTOR_NUMBER","LAST_DESCRIPTOR_NUMBER","
ISO_639_LANGUAGE_CODE_EXTEND_3","LENTH_OF_ITEM","ITEM","
TEXT_LENGTH","TEXT_EVENT
")VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);", -1,
&stmt_insert_schedule, NULL);
if ((rc != SQLITE_OK) && (rc != SQLITE_ROW) && (rc != SQLITE_DONE))
{
printf( "%s(%d): Failed with error code 0x%x =
%s\n",__FILE__,__LINE__, rc, sqlite3_errmsg(db));
if (stmt_insert_schedule)
{
rc = sqlite3_finalize(stmt_insert_schedule);
stmt_insert_schedule=NULL;
}
if(OS_MutSemGive ( mutexForDB))
printf("OS_MutSemGive ( mutexForDB) 9 error\n");
return 1;
}
}
rc=sqlite3_bind_int(stmt_insert_schedule, 1, sqlite_event->table_id);
rc|=sqlite3_bind_int(stmt_insert_schedule, 2, sqlite_event->section_number);
rc|=sqlite3_bind_int(stmt_insert_schedule, 3, sqlite_event->version_number);
rc|=sqlite3_bind_int(stmt_insert_schedule, 4, sqlite_event->service_id);
rc|=sqlite3_bind_int(stmt_insert_schedule, 5, sqlite_event->ts_id);
rc|=sqlite3_bind_int(stmt_insert_schedule, 6, sqlite_event->on_id);
rc|=sqlite3_bind_int(stmt_insert_schedule, 7, sqlite_event->crc_32);
rc|=sqlite3_bind_int(stmt_insert_schedule, 8, sqlite_event->event_id);
rc|=sqlite3_bind_int(stmt_insert_schedule, 9, sqlite_event->utc_m);
rc|=sqlite3_bind_int(stmt_insert_schedule, 10, sqlite_event->utc_l);
rc|=sqlite3_bind_int(stmt_insert_schedule, 11,
(sqlite_event->duration[0]<<16)|(sqlite_event->duration[1]<<8)|
(sqlite_event->duration[2]));
rc|=sqlite3_bind_int(stmt_insert_schedule, 12,
sqlite_event->content_nibble_level);
rc|=sqlite3_bind_int(stmt_insert_schedule, 13,
(sqlite_event->country_code[0]<<16)|
(sqlite_event->country_code[1]<<8)|(sqlite_event->country_code[2]));
rc|=sqlite3_bind_int(stmt_insert_schedule, 14, sqlite_event->rate);
rc|=sqlite3_bind_int(stmt_insert_schedule, 15,
sqlite_event->reference_service_id);
rc|=sqlite3_bind_int(stmt_insert_schedule, 16,
sqlite_event->reference_event_id);
rc|=sqlite3_bind_int(stmt_insert_schedule, 17,
(sqlite_event->ISO_639_language_code_short[0]<<16)|
(sqlite_event->ISO_639_language_code_short[1]<<8)|(sqlite_event->ISO_639_language_code_short[2]));
rc|=sqlite3_bind_int(stmt_insert_schedule, 18, sqlite_event->name_length);
rc|=sqlite3_bind_text(stmt_insert_schedule, 19, sqlite_event->event_name,
sqlite_event->name_length, SQLITE_STATIC );
rc|=sqlite3_bind_int(stmt_insert_schedule, 20, sqlite_event->short_length);
rc|=sqlite3_bind_text(stmt_insert_schedule, 21, sqlite_event->short_event,
sqlite_event->short_length, SQLITE_STATIC );
rc|=sqlite3_bind_int(stmt_insert_schedule, 22,
sqlite_event->descriptor_number);
rc|=sqlite3_bind_int(stmt_insert_schedule, 23,
sqlite_event->last_descriptor_number);
rc|=sqlite3_bind_int(stmt_insert_schedule, 24,
(sqlite_event->ISO_639_language_code_extend[0]<<16)|
(sqlite_event->ISO_639_language_code_extend[1]<<8)|(sqlite_event->ISO_639_language_code_extend[2]));
rc|=sqlite3_bind_int(stmt_insert_schedule, 25, sqlite_event->length_of_items);
rc|=sqlite3_bind_text(stmt_insert_schedule, 26, sqlite_event->item,
sqlite_event->length_of_items, SQLITE_STATIC );
rc|=sqlite3_bind_int(stmt_insert_schedule, 27, sqlite_event->text_length);
rc|=sqlite3_bind_text(stmt_insert_schedule, 28, sqlite_event->text,
sqlite_event->text_length, SQLITE_STATIC );
if(rc!=SQLITE_OK)
{
printf("qlite3_bind_text error 2 \n");
result=1;
}
else
{
rc = sqlite3_step(stmt_insert_schedule);
if (rc == SQLITE_DONE)
{
result=0;
}
else
{
printf("rc != SQLITE_DONE 2 \n");
result=1;
}
}
if(sqlite3_reset(stmt_insert_schedule)!=SQLITE_OK)
{
rc = sqlite3_finalize(stmt_insert_schedule);
if(rc!=SQLITE_OK)
{
printf("STATEMENT_FINALIZE 2 error\n");
}
else
stmt_insert_schedule=NULL;
}
if(OS_MutSemGive ( mutexForDB))
printf("OS_MutSemGive ( mutexForDB) 10 error\n");
return result;
}
