Re: [sqlite] Step Query

[Kees Nuyt]

On Wed, 25 Mar 2009 18:47:14 -0600, Dennis Cote
 wrote:

>Kees Nuyt wrote:
>>
>> This seems a very nice addition to the
>> http://www.sqlite.org/cvstrac/wiki?p=SampleCode 
>> we already have.
>>
>> I feel tempted to put it in the wiki 
>> http://www.sqlite.org/cvstrac/wiki
>> under the 'Hints For Using SQLite More Effectively' heading,
>> as http://www.sqlite.org/cvstrac/wiki?p=SimpleCode.
>>
>> Would you mind if I do?
>>   
>No, I wouldn't mind in the least. In fact I think it's a good idea, and 
>thank you for making the edits.

Done.

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

The first Q and A in the FAQ points to it as well.

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

>I also think it would be a good replacement for, or addition to, the 
>sample code on the quick start page, since that seems to be where many 
>beginners get started with the C API. It's a shame they get started with 
>depreciated API functions.

I agree.

>Dennis Cote

Thanks for posting your code, and for your frequent valuable
contributions to the mailing list.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Step Query

[Kees Nuyt]

Hi Dennis,

On Tue, 24 Mar 2009 18:23:23 -0600, Dennis Cote
 wrote:

>vinod1 wrote:
>> I am new to sqlite and C.
>>
>> I have not been able to write a code which would read row by row using
>> sqlite3_step.
>>
>> Could anybody guide me please.
>>

>Hi,
>
>This code is equivalent to the very old callback style code shown at 
>http://www.sqlite.org/quickstart.html.
>
>It should provide the same results using the newer prepare/step/finalize 
>set of calls that are discussed at http://www.sqlite.org/cintro.html.
>
>Hopefully it provides a complete, if somewhat basic, intro to the use of 
>the preferred C API functions.
>
>#include 
>#include 

[snip]

>  return rc!=SQLITE_DONE;
>}
>
>HTH
>Dennis Cote

This seems a very nice addition to the
http://www.sqlite.org/cvstrac/wiki?p=SampleCode 
we already have.

I feel tempted to put it in the wiki 
http://www.sqlite.org/cvstrac/wiki
under the 'Hints For Using SQLite More Effectively' heading,
as http://www.sqlite.org/cvstrac/wiki?p=SimpleCode.

Would you mind if I do?
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Step Query

[Dennis Cote]

vinod1 wrote:
> I am new to sqlite and C.
>
> I have not been able to write a code which would read row by row using
> sqlite3_step.
>
> Could anybody guide me please.
>
>   
Hi,

This code is equivalent to the very old callback style code shown at 
http://www.sqlite.org/quickstart.html.

It should provide the same results using the newer prepare/step/finalize 
set of calls that are discussed at http://www.sqlite.org/cintro.html.

Hopefully it provides a complete, if somewhat basic, intro to the use of 
the preferred C API functions.

#include 
#include 

int main(int argc, const char *argv[]){
  sqlite3 *db;
  sqlite3_stmt *stmt;
  int rc = 0;
  int col, cols;

  if( argc!=3 ){
fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
  }else{
// open the database file
rc = sqlite3_open(argv[1], );
if( rc ){
  fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
}else{
  // prepare the SQL statement from the command line
  rc = sqlite3_prepare_v2(db, argv[2], -1, , 0);
  if( rc ){
fprintf(stderr, "SQL error: %d : %s\n", rc, sqlite3_errmsg(db));
  }else{
cols = sqlite3_column_count(stmt);
// execute the statement
do{
  rc = sqlite3_step(stmt);
  switch( rc ){
case SQLITE_DONE:
  break;
case SQLITE_ROW:
  // print results for this row
  for( col=0; col

Re: [sqlite] Step Query

[vinod1]

Hi,
I am new to sqlite and C.

I have not been able to write a code which would read row by row using
sqlite3_step.

Could anybody guide me please.



Dan Kennedy-4 wrote:
> 
> On Tue, 2007-06-19 at 10:58 +0530, anand chugh wrote:
>> Hi
>> 
>> I am having code like this:
>> 
>>rc = sqlite3_prepare(db, zSql, -1, , 0);
>>if( rc!=SQLITE_OK ){
>>  return rc;
>>}
>>sqlite3_bind_text(pStmt, 1, zKey, -1, SQLITE_STATIC);
>>sqlite3_bind_blob(pStmt, 2, zBlob, nBlob, SQLITE_STATIC);
>> 
>>while( sqlite3_step(pStmt)==SQLITE_ROW )
>>  {
>>  *pnBlob = sqlite3_column_bytes(pStmt, 0);
>>  *pzBlob = (unsigned char *)malloc(*pnBlob);
>>  memcpy(*pzBlob, sqlite3_column_blob(pStmt, 0), *pnBlob);
>>}
>> 
>>   sqlite3_finalize(pStmt);
>> 
>> My question here is do I need to do sqlite3_finalize(pStmt); after
>> every sqlite3_step() to free all memory allocated by
>> sqlite3_step().
> 
> No. Exactly one sqlite3_finalize() for each sqlite3_prepare(). In
> this respect the code above is fine.
> 
> It's not SQLite related, but if the SQL statement returns more 
> than one row, the malloc() in the while loop will cause a memory 
> leak.
> 
> Dan.
> 
>> Does calling finalize at end will free all memory
>> allocated by all steps statements?
>> 
>>  Example shown http://www.sqlite.org/cvstrac/wiki?p=BlobExample does
>> same , it calls finalize after  every step.
>> 
>> My Program shows some Memory Leaks(Virtual Bytes).
>> 
>> Please clarify.
>> 
>> Anand
>> 
>> -
>> To unsubscribe, send email to sqlite-users-unsubscr...@sqlite.org
>> -
>> 
> 
> 
> -
> To unsubscribe, send email to sqlite-users-unsubscr...@sqlite.org
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Step-Query-tp11188705p22677241.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] Step Query

[Andrew Finkenstadt]

On 6/20/07, Dennis Cote <[EMAIL PROTECTED]> wrote:


Andrew Finkenstadt wrote:
>
>
> I ended up writing a (multi-thread aware) C++ framework to keep me
> out of trouble.  In the SQLite namespace I have

Is there any chance that your framework is freely licensed open source
so others could use it as well?

It sounds interesting, and I would like to take a look at it if that is
possible. Is there a link to the source?



I still need to check on approval for its distribution.  (I have approval
for our local changes to sqlite.h/c, already.)  I hope to be able to offer
it under the single public domain license similar to drh's sqlite license.
I may end up having to GPL or LGPL it.   Please remind me in about 10 days
if there hasn't been any word here.

--a

Re: [sqlite] Step Query

[Dennis Cote]

Andrew Finkenstadt wrote:



I ended up writing a (multi-thread aware) C++ framework to keep me
out of trouble.  In the SQLite namespace I have

 class exception;
 class database;
 class connection;
 class statement;
 class execution;
 class query_result;

where the ownership model is well-defined, and the data-use paths are
protected from coding mistakes at compile time.  There can be only one
execution attached to a statement at any one time, and the query 
result is
owned by the execution.  When the execution terminates (goes out of 
scope),

the statement is reset automatically.



Since I am a strong believer in "prepare once,
use many" for performance reasons, I ended up having to write my own
framework to keep me out of trouble, and to reduce the amount of "busy 
work"

around the "C" interface to sqlite.



Andrew,

Is there any chance that your framework is freely licensed open source 
so others could use it as well?


It sounds interesting, and I would like to take a look at it if that is 
possible. Is there a link to the source?


Dennis Cote




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Step Query

[Andrew Finkenstadt]

On 6/19/07, Andre du Plessis <[EMAIL PROTECTED]> wrote:


I had lots of problems here when starting with SQLite and painstaking I
think I've figured it out.



Me too.

You have sqlite3_prepare, which compiles the sql into byte code, then

Sqlite3_step to execute the query or update, if it is an update then
there is no row, if query then call step until no more rows.

Once done stepping you must either sqlite3_reset or sqlite3_finalize
I believe that the finalize will do reset and free resources. Reset is
designed to reuse the query or update.



Correct.  I ended up writing a (multi-thread aware) C++ framework to keep me
out of trouble.  In the SQLite namespace I have

 class exception;
 class database;
 class connection;
 class statement;
 class execution;
 class query_result;

where the ownership model is well-defined, and the data-use paths are
protected from coding mistakes at compile time.  There can be only one
execution attached to a statement at any one time, and the query result is
owned by the execution.  When the execution terminates (goes out of scope),
the statement is reset automatically.

It is important though to reset (if you don't finalize) because if you

don't reset you may have an open lock on the table and this will lock
out other processes and they will get a SQLITE_BUSY error, because
depending on what the sql is doing, it may have a cursor which may lock
the table.



Exactly the result I had.  Since I am a strong believer in "prepare once,
use many" for performance reasons, I ended up having to write my own
framework to keep me out of trouble, and to reduce the amount of "busy work"
around the "C" interface to sqlite.

--a

RE: [sqlite] Step Query

[Andre du Plessis]

I had lots of problems here when starting with SQLite and painstaking I
think I've figured it out.

You have sqlite3_prepare, which compiles the sql into byte code, then 
Sqlite3_step to execute the query or update, if it is an update then
there is no row, if query then call step until no more rows. 

Once done stepping you must either sqlite3_reset or sqlite3_finalize
I believe that the finalize will do reset and free resources. Reset is
designed to reuse the query or update.

It is important though to reset (if you don't finalize) because if you
don't reset you may have an open lock on the table and this will lock
out other processes and they will get a SQLITE_BUSY error, because
depending on what the sql is doing, it may have a cursor which may lock
the table.

So your code is fine.
But at the end of your rows you don't have to call finalize but you must
call reset.
You don't have to call finalize right away but maybe on object
destruction, to free the resources, after reset is called you wont have
a lock on the table anymore.

This is how I understand things but would like for someone to tell me if
I'm wrong.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
anand chugh
Sent: 19 June 2007 07:28 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Step Query

Hi

I am having code like this:

   rc = sqlite3_prepare(db, zSql, -1, , 0);
   if( rc!=SQLITE_OK ){
 return rc;
   }
   sqlite3_bind_text(pStmt, 1, zKey, -1, SQLITE_STATIC);
   sqlite3_bind_blob(pStmt, 2, zBlob, nBlob, SQLITE_STATIC);

   while( sqlite3_step(pStmt)==SQLITE_ROW )
 {
 *pnBlob = sqlite3_column_bytes(pStmt, 0);
 *pzBlob = (unsigned char *)malloc(*pnBlob);
 memcpy(*pzBlob, sqlite3_column_blob(pStmt, 0), *pnBlob);
   }

  sqlite3_finalize(pStmt);

My question here is do I need to do sqlite3_finalize(pStmt); after
every sqlite3_step() to free all memory allocated by
sqlite3_step().Does calling finalize at end will free all memory
allocated by all steps statements?

 Example shown http://www.sqlite.org/cvstrac/wiki?p=BlobExample does
same , it calls finalize after  every step.

My Program shows some Memory Leaks(Virtual Bytes).

Please clarify.

Anand


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Step Query

[B V, Phanisekhar]

> My question here is do I need to do sqlite3_finalize(pStmt); after
> every sqlite3_step() to free all memory allocated by
> sqlite3_step().Does calling finalize at end will free all memory
> allocated by all steps statements?

No you don't need to call sqlite3_finalize after every sqlite3_step.

> *pzBlob = (unsigned char *)malloc(*pnBlob);

This portion of the memory needs to be freed up by you.


Regards,
Phani



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Step Query

[Dan Kennedy]

On Tue, 2007-06-19 at 10:58 +0530, anand chugh wrote:
> Hi
> 
> I am having code like this:
> 
>rc = sqlite3_prepare(db, zSql, -1, , 0);
>if( rc!=SQLITE_OK ){
>  return rc;
>}
>sqlite3_bind_text(pStmt, 1, zKey, -1, SQLITE_STATIC);
>sqlite3_bind_blob(pStmt, 2, zBlob, nBlob, SQLITE_STATIC);
> 
>while( sqlite3_step(pStmt)==SQLITE_ROW )
>  {
>  *pnBlob = sqlite3_column_bytes(pStmt, 0);
>  *pzBlob = (unsigned char *)malloc(*pnBlob);
>  memcpy(*pzBlob, sqlite3_column_blob(pStmt, 0), *pnBlob);
>}
> 
>   sqlite3_finalize(pStmt);
> 
> My question here is do I need to do sqlite3_finalize(pStmt); after
> every sqlite3_step() to free all memory allocated by
> sqlite3_step().

No. Exactly one sqlite3_finalize() for each sqlite3_prepare(). In
this respect the code above is fine.

It's not SQLite related, but if the SQL statement returns more 
than one row, the malloc() in the while loop will cause a memory 
leak.

Dan.

> Does calling finalize at end will free all memory
> allocated by all steps statements?
> 
>  Example shown http://www.sqlite.org/cvstrac/wiki?p=BlobExample does
> same , it calls finalize after  every step.
> 
> My Program shows some Memory Leaks(Virtual Bytes).
> 
> Please clarify.
> 
> Anand
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-