Re: [sqlite] execute or prepare+step+finalize

[Simon Davies]

2009/11/11 T :
>
> i`ve done it, and i think i understand it :)
>
> thank you..
>
>
> few more question to be sure that i`m not missing something important..
>
> if i use prepared statements only (dont use exec function in program at all)
> i dont need callback function, do i?

No

>  somehow after i replaced exec (i.e. select_statement function) and i dont
> need select_statement function, neither callback function, evetything seems
> much more simple (which is why i`m bit suspicious if i`ve done everything
> correct)..
>
> are there any cases when it is better to use exec ?


perhaps simpler/quicker to code for sql that returns no data (INSERT etc)


>
>
> does using prepared statement slow down my program since prepare function is
> used every time when i want to execute some command ?

No - sqlite3_exec() is a wrapper around sqlite3_prepare()/sqlite3_step()

> here is how my
> function looks like now:
>
> bool create2 (char * command)
> {
>
> sqlite3_stmt * statement2;
>
> if ( sqlite3_prepare (db, command, -1, , 0) != SQLITE_OK )
>        {
>                int err = sqlite3_prepare (db, command, -1, , 0);

no need to call prepare a second time - assign to err in your if
statement, or use sqlite3_errcode()

>                const char * pErr = sqlite3_errmsg (db);
>                printf ("\nError %d occured! \n %s", err, pErr  );
>                return false;
>        }
>
> int iCol = sqlite3_column_count (statement2);
>
> int smth, i;
> //int rows=1;
> smth=sqlite3_step(statement2);
>
> while (smth == SQLITE_ROW)
> {
>        printf ("\n");
>        //printf ("\n Row %d:\t", rows);   // doesnt give right number of 
> column
> for other select statements except select all
>        for (i=0; i        {
>                const char *txt = (const char*)sqlite3_column_text(statement2, 
> i); // save
> it into dynamical multidimensional array
>                printf (" %s = %s \t", sqlite3_column_name (statement2,i), txt 
> );
>        }
>        printf ("\n");
>        //rows++;
>        smth=sqlite3_step (statement2);
>
> }
>
>        sqlite3_reset (statement2);

Use sqlite3_reset if you are going to bind new values to your prepared
statement and restep. If you are finalizing there is no need to reset.

>        sqlite3_finalize (statement2);
>
>        return true;
> }
>
>
> main:
>
>
> create2 ("CREATE TABLE two (ID INTEGER PRIMARY KEY ASC, a,b,c)");
> create2 ("INSERT INTO two (a,b) VALUES (3, 4)");
> create2 ("INSERT INTO two (a,b,c) VALUES (2, 8, 9)");
> create2 ("INSERT INTO two (a,c) VALUES (4, 1)");
> create2 ("INSERT INTO two (a,b,c) VALUES (1, 4, 9)");
> create2 ("INSERT INTO two (a,b,c) VALUES (1, 2, 8)");
> create2 ("SELECT * FROM two");
> create2 ("SELECT * FROM two WHERE b=4");
>
>
> if you have some advices or suggestions please let me know..
>

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] execute or prepare+step+finalize

[TTTTT]

i`ve done it, and i think i understand it :)

thank you..


few more question to be sure that i`m not missing something important..

if i use prepared statements only (dont use exec function in program at all)
i dont need callback function, do i?
 somehow after i replaced exec (i.e. select_statement function) and i dont
need select_statement function, neither callback function, evetything seems
much more simple (which is why i`m bit suspicious if i`ve done everything
correct)..

are there any cases when it is better to use exec ?


does using prepared statement slow down my program since prepare function is
used every time when i want to execute some command ? here is how my
function looks like now:





bool create2 (char * command)
{

sqlite3_stmt * statement2;

if ( sqlite3_prepare (db, command, -1, , 0) != SQLITE_OK )
{
int err = sqlite3_prepare (db, command, -1, , 0);
const char * pErr = sqlite3_errmsg (db);
printf ("\nError %d occured! \n %s", err, pErr  );
return false;
}

int iCol = sqlite3_column_count (statement2);

int smth, i;
//int rows=1;
smth=sqlite3_step(statement2);

while (smth == SQLITE_ROW)
{
printf ("\n");
//printf ("\n Row %d:\t", rows);   // doesnt give right number of column
for other select statements except select all
for (i=0; i

Re: [sqlite] execute or prepare+step+finalize

[Simon Davies]

2009/11/11 T :
>
.
.
.
>
>> and finaly i get SQLITE_DONE but it still doesnt show me table i have
>> selected...
>
> See http://www.sqlite.org/c3ref/column_blob.html
>
>
> i dont understand what exactly do you mean..
>
> combination of these:
>
> sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);
> typedef struct Mem sqlite3_value;
> void sqlite3_result_value(sqlite3_context*, sqlite3_value*);

>From http://www.sqlite.org/c3ref/column_blob.html:
"These routines form the "result set query" interface."

If you want to see the data, you need to get it using the
sqlite3_column_xxx() routines after each call to sqlite3_step(). If
your column contains an integer, use sqlite3_column_int(), for real
values use sqlite3_column_double(), and so on.

>
> maybe?
>
>

Rgds,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] execute or prepare+step+finalize

[Marcus Grimm]

you may also take a look at:

http://www.sqlite.org/cvstrac/wiki?p=SimpleCode

it explains how to use the step mechanism.

hth
Marcus

>
>
> why not:
> int smth = sqlite3_step (statement2);
> while( smth == SQLITE_ROW )
> {
> printf( "\n command= %s result code = %d \n", command, smth );
> smth = sqlite3_step (statement2);
> }
> so that it will work no matter how many rows
>
>
>
> because I dont have much experience :)
> that works great, thank you
>
>
>
>> and finaly i get SQLITE_DONE but it still doesnt show me table i have
>> selected...
>
> See http://www.sqlite.org/c3ref/column_blob.html
>
>
> i dont understand what exactly do you mean..
>
> combination of these:
>
> sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);
> typedef struct Mem sqlite3_value;
> void sqlite3_result_value(sqlite3_context*, sqlite3_value*);
>
> maybe?
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> View this message in context:
> http://old.nabble.com/execute-or-prepare%2Bstep%2Bfinalize-tp26299247p26300548.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
>


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

Re: [sqlite] execute or prepare+step+finalize

[TTTTT]

why not:
int smth = sqlite3_step (statement2);
while( smth == SQLITE_ROW )
{
printf( "\n command= %s result code = %d \n", command, smth );
smth = sqlite3_step (statement2);
}
so that it will work no matter how many rows



because I dont have much experience :)
that works great, thank you



> and finaly i get SQLITE_DONE but it still doesnt show me table i have
> selected...

See http://www.sqlite.org/c3ref/column_blob.html


i dont understand what exactly do you mean..

combination of these:

sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);
typedef struct Mem sqlite3_value;
void sqlite3_result_value(sqlite3_context*, sqlite3_value*);

maybe?




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



-- 
View this message in context: 
http://old.nabble.com/execute-or-prepare%2Bstep%2Bfinalize-tp26299247p26300548.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

Re: [sqlite] execute or prepare+step+finalize

[Simon Davies]

2009/11/11 T :
>
> i think i understand why it doesnt work for select *..
>
> because sqlite_step executes one row at time.. so after i prepare SELECT * ,
> i need to use sqlite_step as many times as table i`m selecting from has rows
> (in this case 3 times)..
> so i made another function that looks like this:
>
>
>
> bool create2 (char * command)
>
> {
> sqlite3_stmt * statement2;
>
> if ( sqlite3_prepare (db, command, -1, , 0) != SQLITE_OK )
>        {
>                int err = sqlite3_prepare (db, command, -1, , 0);
>                const char * pErr = sqlite3_errmsg (db);
>                printf ("\nError %d occured! \n %s", err, pErr  );
>                return 1;
>        }
> int i;
> for (i=0; i<=3; i++)
> {
>        int smth= sqlite3_step (statement2);
>        printf ("\n command= %s result code = %d \n",command, smth);
> }

why not:
int smth = sqlite3_step (statement2);
while( smth == SQLITE_ROW )
{
printf( "\n command= %s result code = %d \n", command, smth );
smth = sqlite3_step (statement2);
}
so that it will work no matter how many rows

>        sqlite3_reset (statement2);
>        sqlite3_finalize (statement2);
>
>        return 0;
> }
>
>
>
> and finaly i get SQLITE_DONE but it still doesnt show me table i have
> selected...

See http://www.sqlite.org/c3ref/column_blob.html

>
>
> do i need to use prepare function for each command? if so, isnt then
> function select_statement better to use?

You are using prepare for each command...

>
>

Rgds,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] execute or prepare+step+finalize

[TTTTT]

i think i understand why it doesnt work for select *..

because sqlite_step executes one row at time.. so after i prepare SELECT * ,
i need to use sqlite_step as many times as table i`m selecting from has rows
(in this case 3 times)..
so i made another function that looks like this:



bool create2 (char * command)

{
sqlite3_stmt * statement2;

if ( sqlite3_prepare (db, command, -1, , 0) != SQLITE_OK )
{
int err = sqlite3_prepare (db, command, -1, , 0);
const char * pErr = sqlite3_errmsg (db);
printf ("\nError %d occured! \n %s", err, pErr  );
return 1;
}
int i;
for (i=0; i<=3; i++)
{
int smth= sqlite3_step (statement2);
printf ("\n command= %s result code = %d \n",command, smth);
}
sqlite3_reset (statement2);
sqlite3_finalize (statement2);

return 0;
}



and finaly i get SQLITE_DONE but it still doesnt show me table i have
selected...


do i need to use prepare function for each command? if so, isnt then
function select_statement better to use? 


-- 
View this message in context: 
http://old.nabble.com/execute-or-prepare%2Bstep%2Bfinalize-tp26299247p26299743.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

[sqlite] execute or prepare+step+finalize

[TTTTT]

Hello everyone!

i have been using function select_statement to execute SQL statements as
SELECT, CREATE, DROP, INSERT etc.. 
then i`ve replaced insert by bind function because you ve told me about
various advantages it has over insert.

now i have realized that in official documentation is also said that its
better to use prepared statements instead of execute function..

I wonder does it apply for all cases? 


here is select function that i`ve used:


int select_stmt(const char* stmt) {  
  char *errmsg;
  int   ret;
  int   nrecs = 0;

  first_row = 1;

  ret = sqlite3_exec(db, stmt, select_callback, , );

  if(ret!=SQLITE_OK) {
printf("Error in select statement %s [%s].\n", stmt, errmsg);
getchar ();
  }
  else {
printf("\n   %d records returned.\n", nrecs);
}
return 0;
}



and new function which uses prepared statements:


bool create (char * command)

{
sqlite3_stmt * statement2;


if ( sqlite3_prepare (db, command, -1, , 0) != SQLITE_OK )
{
int err = sqlite3_prepare (db, command, -1, , 0);
const char * pErr = sqlite3_errmsg (db);
printf ("\nError %d occured! \n %s", err, pErr  );
return 1;
}

int smth= sqlite3_step (statement2);
printf ("\n result code = %d \n", smth);


//sqlite3_reset (statement2);
sqlite3_finalize (statement2);

return 0;

}




main :


create ("CREATE TABLE two (a,b,c)");
create ("INSERT INTO two (a,b) VALUES (3, 1)");
create ("INSERT INTO two (a,b,c) VALUES (2, 8, 9)");
create ("INSERT INTO two (a,c) VALUES (4, 1)");
create ("SELECT * FROM two");




The thing is that create function does not execute last instruction:  create
("SELECT * FROM two");
it returns sqlite_row instead of sqlite_done which i would expect.. 

I would appreciate if someone could explain me this..

Thanky in advance..

T
-- 
View this message in context: 
http://old.nabble.com/execute-or-prepare%2Bstep%2Bfinalize-tp26299247p26299247.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