[sqlite] there is problem when getting data by sqlite3's c apis in signal-handling func

[liubin liu]

there is problem when getting data by sqlite3's c apis in signal-handling
func.


_________________________________________________________________


#include <stdio.h>      // for printf()
#include <signal.h>     // for signal()
#include <unistd.h>     // for alarm()
#include <stdlib.h>     // for system()
#include <sqlite3.h>    // for sqlite3_***



sqlite3 *db = NULL;


int sqlite3_helper_create_db (void);


int sqlite3_helper_insert_data (void);


// psf - sqlite3_prepare_v2()+sqlite3_step()+sqlite3_finalize()
int sqlite3_helper_get_data_psf_from_tb1 ( int id );

void timer_handler()
{
    printf ( "# IN timer_handler()\n" );


    int ret = -1;
    int i = 9;

    char *query_format2 = "SELECT * FROM ts2 WHERE id=%d;";
    char *query_string = NULL;


    sqlite3_stmt *p_stmt = NULL;

    printf ( "# IN timer_handler(), ago   sqlite3_mprintf()\n" );
    query_string = sqlite3_mprintf ( query_format2, i%500 );

    printf ( "# IN timer_handler(), ago   sqlite3_prepare_v2()\n" );
    ret = sqlite3_prepare_v2 ( db, query_string, -1, &p_stmt, NULL );
    printf ( "# IN timer_handler(), ago   sqlite3_step()\n" );
    ret = sqlite3_step ( p_stmt );
    printf ( "# IN timer_handler(), ago   printf()\n" );
    if ( SQLITE_ROW == ret )
        printf ( "# IN timer_handler(), id: %d, length: %d\n", 
sqlite3_column_int( p_stmt, 0 ), sqlite3_column_int( p_stmt, 1 ) );
    printf ( "# IN timer_handler(), ago   sqlite3_free_table()\n" );
    sqlite3_finalize ( p_stmt );


    alarm(1);
}

int inittimer()
{
    signal ( SIGALRM, timer_handler );
    alarm(1);
    return 0;
}


int main ( void )
{
    int ret = -1;
    int i = 0;


    // open db file
    ret = sqlite3_open ( "testsignal.db", &db );

    // create tables: ts1 and ts2
    sqlite3_helper_create_db ();

    // insert datas into the tables(ts1&ts2)
    sqlite3_helper_insert_data ();

    // init the timer
    ret = inittimer();

    // get datas from table ts1
    for ( i=0; ; i++ )
        ret = sqlite3_helper_get_data_psf_from_tb1 ( i );

    // close db file
    ret = sqlite3_close ( db );

    // get the num of records
    system ( "sqlite3 testsignal.db \"SELECT COUNT(*) FROM ts1;\"" );

    // delete db file
    //system ( "rm testsignal.db -rf" );

    return 0;
}


int sqlite3_helper_create_db (void)
{
    int ret = -1;
    char *cr_tb1 = "CREATE TABLE ts1 (id INTEGER PRIMARY KEY, length
INTEGER, data CHAR(50));";
    char *cr_tb2 = "CREATE TABLE ts2 (id INTEGER PRIMARY KEY, length
INTEGER, data CHAR(50));";

    ret = sqlite3_exec ( db, cr_tb1, NULL, NULL, NULL );
    //printf ( "ret: %d\n", ret );
    ret = sqlite3_exec ( db, cr_tb2, NULL, NULL, NULL );
    //printf ( "ret: %d\n", ret );
    if ( SQLITE_OK == ret )
        printf ( "# IN main(), create tables, DONE~\n" );


    return 0;
}


int sqlite3_helper_insert_data (void)
{
    int ret = -1;
    int i = 0;

    sqlite3_stmt *p_stmt = NULL;


    char *sql_format1 = "INSERT OR REPLACE INTO ts1 (id, length, data)
VALUES (%d, %d, %Q);";
    char *sql_format2 = "INSERT OR REPLACE INTO ts2 (id, length, data)
VALUES (%d, %d, %Q);";
    char *sql = NULL;


    ret = sqlite3_exec ( db, "BEGIN", NULL, NULL, NULL );
    for ( i=0; i<500; i++ )
    {
        sql = sqlite3_mprintf ( sql_format1, i, i%10,
"datadatadatadatadatadatadatadatadatadatadata" );
        ret = sqlite3_prepare_v2 ( db, sql, -1, &p_stmt, NULL );
        ret = sqlite3_step ( p_stmt );
        sqlite3_free ( sql );
        ret = sqlite3_finalize ( p_stmt );

        sql = sqlite3_mprintf ( sql_format2, i, i%10,
"datadatadatadatadatadatadatadatadatadatadata" );
        ret = sqlite3_prepare_v2 ( db, sql, -1, &p_stmt, NULL );
        ret = sqlite3_step ( p_stmt );
        sqlite3_free ( sql );
        ret = sqlite3_finalize ( p_stmt );
    }
    ret = sqlite3_exec ( db, "COMMIT", NULL, NULL, NULL );
    if ( SQLITE_OK == ret )
        printf ( "# IN main(), insert datas, DONE~\n" );

    return 0;
}


int sqlite3_helper_get_data_psf_from_tb1 ( int id )
{
    int ret = -1;

    sqlite3_stmt *p_stmt = NULL;


    char *query_format1 = "SELECT * FROM ts1 WHERE id=%d;";
    char *query_string = NULL;

    query_string = sqlite3_mprintf ( query_format1, id%500 );
    ret = sqlite3_prepare_v2 ( db, query_string, -1, &p_stmt, NULL );
    ret = sqlite3_step ( p_stmt );
    if ( SQLITE_ROW == ret )
        printf ( "# IN main(), id: %d, length: %d\n", sqlite3_column_int(
p_stmt, 0 ), sqlite3_column_int( p_stmt, 1 ) );
    sqlite3_finalize ( p_stmt );

    return 0;
}


-- 
View this message in context: 
http://www.nabble.com/there-is-problem-when-getting-data-by-sqlite3%27s-c-apis-in-signal-handling-func-tp24365955p24365955.html
Sent from the SQLite mailing list archive at Nabble.com.

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users