macOS how to insert variable in sqlite by C language

[felipe12357]

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!

 

[mrbash]

Prepared Statements

You are looking for 'prepared statements'. I'm sure you can use Google to find what you need.

 

[felipe12357]

example

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

could you please give me an example

 

[chown33]

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.

 

[felipe12357]

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:

#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...

 

[chown33]

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.

 

[felipe12357]

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

 

[chown33]

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

 

[felipe12357]

thanks you are right thats the solution

CREATE TABLE IF NOT EXISTS tbl

now i going to follow with inserting data

thanks

 

[felipe12357]

Finally im inserting some data on my table by this instrution:

 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