how to insert variable in sqlite by C language

Discussion in 'Mac Programming' started by felipe12357, Jun 4, 2010.

  1. felipe12357 macrumors newbie

    Joined:
    Jun 4, 2010
    #1
    HI im using a code in C and i want to store the value of a variable on a fild of a sqlite database, how could be this instrution?

    static int abc = 0;

    queries[ind++] = “INSERT INTO users VALUES(‘abc’,’manish’,1)”;
    retval = sqlite3_exec(handle,queries[ind-1],0,0,0);

    I have see that i other languange but not in C.

    i hope you can helpme thanks!
     
  2. mrbash macrumors 6502

    Joined:
    Aug 10, 2008
    #2
    Prepared Statements

    You are looking for 'prepared statements'. I'm sure you can use Google to find what you need.
     
  3. felipe12357 thread starter macrumors newbie

    Joined:
    Jun 4, 2010
    #3
    example

    im very new on C language i did that but with java...

    could you please give me an example
     
  4. chown33 macrumors 604

    Joined:
    Aug 9, 2009
    #4
    Example:

    Enter google search terms: sqlite prepared statement

    Click link in search results: http://www.sqlite.org/c3ref/stmt.html

    Read.


    If you don't know C well enough to figure it out, then you should learn how to use Google well enough to find examples. Or you should concentrate on learning C first, and only then start writing sqlite programs. Otherwise someone else ends up writing your entire program for you, one example at a time.
     
  5. felipe12357 thread starter macrumors newbie

    Joined:
    Jun 4, 2010
    #5
    starting with prepared stament

    HI i started to study the prepared stament for that reason i wanted to understand the conection to the database, creating tables and reading some data before inserting data on the table. So im studing this code:

    Code:
    #include<stdio.h>
    #include<sqlite3.h>
    #include<stdlib.h>
    
    int main(int argc, char** args, char **argv)
    //int main( int argc, char **argv)
    {
     
    //crea un archivo temporal para realizar la conexion
     char *file = ""; // default to temp db
     sqlite3         *db = NULL;
     int             rc = 0;
    
     if ( argc > 1 )
     file = argv[1];
    
    //linea para la creacion de la tabla
     sqlite3_stmt    *stmt = NULL;
    
    //linea para realizar la extraccion de los datos
    const char      *data = NULL;
    
     sqlite3_initialize( );
     rc = sqlite3_open_v2( "serial.sqlite3", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL );
     if ( rc != SQLITE_OK)
     exit( -1 );
    
    // hasta aca abre la base de datos 
    
    
    // crea una tabla en la base de datos 
    
     rc = sqlite3_prepare_v2( db, "CREATE TABLE tbl ( str TEXT )", -1, &stmt, NULL );
        if ( rc != SQLITE_OK) exit( -1 );
    
        rc = sqlite3_step( stmt );
        if ( rc != SQLITE_DONE ) exit ( -1 );
        
        sqlite3_finalize( stmt );
    
    // Lee los datos de la base de datos 
     
    
     rc = sqlite3_prepare_v2( db, "SELECT str FROM tbl ORDER BY 1", -1, &stmt, NULL );
        if ( rc != SQLITE_OK) exit( -1 );
    
        while( sqlite3_step( stmt ) == SQLITE_ROW ) {
            data = (const char*)sqlite3_column_text( stmt, 0 );
            printf( "%s\n", data ? data : "[NULL]" );
        }
    
        sqlite3_finalize( stmt );
    
     // Cierra la conexion con la base de datos 
    
        sqlite3_close( db );
        sqlite3_shutdown( );
    }
    Part of this code works well, the conection with the database and creating the table, how ever when i tried to read the table that part doesnt work well.

    Can anybody giveme some advice i realize that if i comment the code where im creating the table, the part where i read the code can work, but why?? what do i have to change?

    i tried with sqlite3_reset( stmt );

    but i didnt fix my problem..

    help...
     
  6. chown33 macrumors 604

    Joined:
    Aug 9, 2009
    #6
    Describe exactly what isn't working.

    If your program exits with -1, say that.
    If it doesn't list anything, say that.
    If it does something else, tell us exactly what happens.


    The code you posted creates a table, but doesn't store any data into it. If the problem you have is that nothing is listed, then this is the correct behavior. There is no data, so an empty list is correct.
     
  7. felipe12357 thread starter macrumors newbie

    Joined:
    Jun 4, 2010
    #7
    after executed the programm for first time i inserted some data on the table using this instrution:

    sqlite3 test.db "insert into tbl (str) values (23)";

    and after that i ran my program again, but it didnt showme anything.

    after that i commented the part of the code where i create the table, and i ran the program again, and it shows me: 23

    so the problem its that the program cant execute both instrutions:

    creating table and reading data.

    why? what do i have to fix?

    thanks for the help
     
  8. chown33 macrumors 604

    Joined:
    Aug 9, 2009
    #8
    The obvious solution: if you want to use the data in an existing table, then don't create the table.

    It looks like you don't really understand how sqlite works. If you tell it to create a table, then it will do so. If that's not what you want it to do, then don't tell it that.

    If you want to conditionally create a table only when one doesn't already exist, then you should look at the sqlite reference docs.
    http://www.sqlite.org/lang_createtable.html

    It looks like create table if not exists is what you want.

    If you don't have a working understanding of how to make the correct SQL statements, then you need to learn how to do that before trying to make any of those statements work in C.
    http://www.sqlite.org/sqlite.html
     
  9. felipe12357 thread starter macrumors newbie

    Joined:
    Jun 4, 2010
    #9
    thanks you are right thats the solution

    CREATE TABLE IF NOT EXISTS tbl

    now i going to follow with inserting data

    thanks:)
     
  10. felipe12357 thread starter macrumors newbie

    Joined:
    Jun 4, 2010
    #10
    Finally im inserting some data on my table by this instrution:

    Code:
     char            *data = ""; /* default to empty string */
        sqlite3_stmt    *stmt = NULL;
        int             idx = -1;
    
    rc = sqlite3_prepare_v2( db, "INSERT INTO tbl VALUES ( :str )", -1, &stmt, NULL );
        if ( rc != SQLITE_OK) exit( -1 );
    
        idx = sqlite3_bind_parameter_index( stmt, ":str" );
        sqlite3_bind_text( stmt, idx, data, -1, SQLITE_STATIC );
    
        rc = sqlite3_step( stmt );
        if (( rc != SQLITE_DONE )&&( rc != SQLITE_ROW )) exit ( -1 );
        
        sqlite3_finalize( stmt );
    but the data i can insert its defined here:
    char *data = ""

    but when i put a variable there
    int bla = 2;

    int *data = bla;

    appears me this mistake:

    warning: initialization makes pointer from integer without a cast

    im confuse with binding parameters there any link you suggest me

    thanks for all the help :rolleyes:
     

Share This Page