[sqlite] How to write the trigger?

[liubin liu]

I created a table like this：
CREATE TABLE record_trip (trip_id CHAR(1), gp_no CHAR(1), rec_date INTEGER,
trun CHAR(1), ctrl_id CHAR(1), moment_value INTEGER, minutes_value INTEGER,
set_value INTEGER );
CREATE UNIQUE INDEX i_record_trip ON record_trip (trip_id, gp_no, rec_date);


And the trigger like：
CREATE TRIGGER trig1 before insert on record_trip 
when (select count(*) from record_trip where trip_id=new.trip_id AND
gp_no=new.gp_no)>4 
begin 
delete from record_trip where trip_id=new.trip_id AND gp_no=new.gp_no AND
rec_date=(select rec_date from record_trip where trip_id=new.trip_id and
gp_no = new.gp_no order by rec_date limit 1);
end;


I felt the when clause is wrong. It couldn't realize the intention of
executing the trigger after the num of trip_id and gp_no is larger than 4.
-- 
View this message in context: 
http://old.nabble.com/How-to-write-the-trigger--tp29331491p29331491.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] Doesn't update return wrong code while there isn't record in table?

[liubin liu]

Thank you for you reply, :)

But I see the return errmsg is "unknown error" on sqlite3.6.23-1. What does
it mean?


Kees Nuyt wrote:
> 
> On Mon, 24 May 2010 01:27:05 -0700 (PDT), liubin liu
> <7101...@sina.com> wrote:
> 
> 
>> Doesn't update return wrong code while 
>> there isn't record in table?
> 
> No. Your update tells SQLite to update all rows in the
> selection. It did. In this case "all" meaning zero, but that
> is not an error. Think of it as executing a mathematical set
> function on an empty set. An set being empty is normal, not
> an error.
> 
>>Is it normal action?
> 
> Yes. It's common in most if not all SQL database systems.
> 
> The number of rows affected can be retrieved with the
> sqlite3_changes() function.
> http://www.sqlite.org/lang_corefunc.html
> -- 
>   (  Kees Nuyt
>   )
> c[_]
> ___
> 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/Doesn%27t-update-return-wrong-code-while-there-isn%27t-record-in-table--tp28654671p28663384.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] Doesn't update return wrong code while there isn't record in table?

[liubin liu]

Doesn't update return wrong code while there isn't record in table?

Is it normal action?


_code__

#include 
#include 

void create_db (void);

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

create_db ();

sqlite3 *db = NULL;
ret = sqlite3_open ("test.db", );


sqlite3_stmt *p_stmt = NULL;
ret = sqlite3_prepare_v2 (db, "UPDATE t1 SET id=12, d111='hello'", -1,
_stmt, NULL);
printf ("sqlite3_prepare_v2(): %d, %s\n", ret, sqlite3_errmsg(db));
ret = sqlite3_step (p_stmt);
printf ("sqlite3_step():   %d, %s\n", ret, sqlite3_errmsg(db));
ret = sqlite3_finalize (p_stmt);
printf ("sqlite3_finalize():   %d, %s\n", ret, sqlite3_errmsg(db));


ret = sqlite3_close (db);
return 0;
}

void create_db (void)
{
int ret = -1;

sqlite3 *db = NULL;
ret = sqlite3_open ("test.db", );
ret = sqlite3_exec (db, "CREATE TABLE t1 (id INTEGER PRIMARY KEY, d111
TEXT)", NULL,NULL,NULL);
ret = sqlite3_close (db);
}


run result___

In Fedora-7.0, SQLite version 3.4.2, the result is:
[t...@localhost sqlite3]$ ./sqlite3update
sqlite3_prepare_v2(): 0, not an error
sqlite3_step():   101, not an error
sqlite3_finalize():   0, not an error
[t...@localhost sqlite3]$


In my dev-board, SQLite version 3.6.23.1, the result is:
[/usr1/bin]# ./sqlite3update
sqlite3_prepare_v2(): 0, not an error
sqlite3_step():   101, unknown error
sqlite3_finalize():   0, not an error
[/usr1/bin]#

-- 
View this message in context: 
http://old.nabble.com/Doesn%27t-update-return-wrong-code-while-there-isn%27t-record-in-table--tp28654671p28654671.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] the wrong in building sqlite-3.6.23.1 on Fedora-7.0

[liubin liu]

the original version of sqlite in fedora is 3.4.2. I built the newest version
on Fedora-7.0. And get the the wrong in building sqlite-3.6.23.1 on
Fedora-7.0 like below:
./libtool --mode=compile --tag=CC gcc   -g -O2 -DSQLITE_OS_UNIX=1 -I.
-I../SQLite-b078b588d617e078/src -D_HAVE_SQLITE_CONFIG_H -DNDEBUG  
-DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1
-DSQLITE_OMIT_LOAD_EXTENSION=1   -DSQLITE_TEMP_STORE=1 -c
../SQLite-b078b588d617e078/src/alter.c
libtool: compile:  gcc -g -O2 -DSQLITE_OS_UNIX=1 -I.
-I../SQLite-b078b588d617e078/src -D_HAVE_SQLITE_CONFIG_H -DNDEBUG
-DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1
-DSQLITE_OMIT_LOAD_EXTENSION=1 -DSQLITE_TEMP_STORE=1 -c
../SQLite-b078b588d617e078/src/alter.c  -fPIC -DPIC -o .libs/alter.o
In file included from ../SQLite-b078b588d617e078/src/alter.c:15:
../SQLite-b078b588d617e078/src/sqliteInt.h:418: error: expected ‘=’, ‘,’,
‘;’, ‘asm’ or ‘__attribute__’ before ‘i64’
../SQLite-b078b588d617e078/src/sqliteInt.h:419: error: expected ‘=’, ‘,’,
‘;’, ‘asm’ or ‘__attribute__’ before ‘u64’
In file included from ../SQLite-b078b588d617e078/src/sqliteInt.h:630,
 from ../SQLite-b078b588d617e078/src/alter.c:15:
../SQLite-b078b588d617e078/src/btree.h:58: error: expected declaration
specifiers or ‘...’ before ‘sqlite3’
../SQLite-b078b588d617e078/src/btree.h:158: error: expected declaration
specifiers or ‘...’ before ‘i64’
../SQLite-b078b588d617e078/src/btree.h:164: error: expected declaration
specifiers or ‘...’ before ‘i64’
../SQLite-b078b588d617e078/src/btree.h:172: error: expected declaration
specifiers or ‘...’ before ‘i64’
...
...
...



_

What does it happen? How to do the job in right way?


-- 
View this message in context: 
http://old.nabble.com/the-wrong-in-building-sqlite-3.6.23.1-on-Fedora-7.0-tp28579834p28579834.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] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

[liubin liu]

How to rollback current transaction?





Pavel Ivanov-2 wrote:
> 
>> I assume you want the sqllite3_stmt to work -- so you need to loop that
>> while it's busy.
> 
> Michael, don't give bad advices.
> The most general advice when one gets SQLITE_BUSY is to reset/finalize
> all statements and rollback current transaction. It's only in certain
> type of transactions and certain type of statements one can loop while
> sqlite3_stmt returns SQLITE_BUSY. But even in this case one must call
> sqlite3_reset on the statement that returned SQLITE_BUSY. And this
> call to sqlite3_reset will return SQLITE_BUSY again.
> 
> 
> Pavel
> 
> On Thu, May 13, 2010 at 7:20 AM, Black, Michael (IS)
> <michael.bla...@ngc.com> wrote:
>> It also means you're not handling the SQLITE_BUSY from the sqlite3_stmt()
>> before it.
>> I assume you want the sqllite3_stmt to work -- so you need to loop that
>> while it's busy.
>>
>> Michael D. Black
>> Senior Scientist
>> Northrop Grumman Mission Systems
>>
>>
>> 
>>
>> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
>> Sent: Thu 5/13/2010 2:07 AM
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and
>> SQLITE_MISUSE
>>
>>
>>
>>
>> I see many SQLITE_BUSY returned from sqlite3_finalize(). Are they memory
>> leak
>> because it don't succeed in finalizing the sqlite3_stmt pointer?
>>
>>
>>
>>
>> Black, Michael (IS) wrote:
>>>
>>> SQLITE_BUSY is not an error...just a fact.
>>>
>>> All your processes cannot work on the database at the same time...at
>>> least
>>> not when one of them is doing an insert.  You could be changing the
>>> table
>>> while you're scanning it.  EXXCLUSIVE doesn't change that idea.
>>>
>>> Somebody please correct me if I'm wrong on this one...
>>> I think sqlite can work with multiple processes just doing read-onliy
>>> operations (like SELECT).    It's just the write operations
>>> (INSERT/UPDATE) which will cause SQLITE_BUSY to occur.
>>>
>>> Michael D. Black
>>> Senior Scientist
>>> Northrop Grumman Mission Systems
>>>
>>>
>>> 
>>>
>>> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
>>> Sent: Tue 5/11/2010 9:20 PM
>>> To: sqlite-users@sqlite.org
>>> Subject: Re: [sqlite] multi processes, so many errores of SQLITE_BUSY
>>> and
>>> SQLITE_MISUSE
>>>
>>>
>>>
>>>
>>> Thank you very much!
>>>
>>> It may be because my system's resource is limited. It's a embedded
>>> system
>>> containing 32M RAM, ARM9 CPU.
>>>
>>> My "reiterating 20 times" is already using usleep().
>>>
>>> After I add the loop in the prepare statements, the system performance
>>> is
>>> still very bad... And there are still many errores of SQLITE_BUSY.
>>>
>>> The only improvement is the disappear of the error of SQLITE_MISUSE.
>>>
>>> And I tried to use the "BEGIN EXCLUSIVE TRANSACTION". The things are
>>> same
>>> with them without using it.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Black, Michael (IS) wrote:
>>>>
>>>> Your "reiterating 20 times" is not using a usleep so you'll blow by
>>>> this
>>>> most every time it's busy.
>>>>
>>>> Do this instead in all your proc's
>>>>
>>>>         ret = sqlite3_step (p_stmt);
>>>>         if (SQLITE_BUSY == ret)
>>>>         {
>>>>                 int n=0;
>>>>                 usleep(10); // try one more time before error
>>>>                 while ((ret=sqlite3_step(p_stmt))==SQLITE_BUSY) {
>>>>                         printf("proc1 ret==BUSY %d\n",++n);
>>>>                         usleep(10);
>>>>                 }
>>>>         }
>>>>
>>>> And you'll also need to handle "database is locked" coming from your
>>>> prepare statements.  I saw that error too.
>>>> You'll need to loop there too.
>>>>
>>>> The more you drop the usleep time the more times it will show as busy.
>>>> 1/10th or 1/100th of second is about all you want I would think.

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

[liubin liu]

I see many SQLITE_BUSY returned from sqlite3_finalize(). Are they memory leak
because it don't succeed in finalizing the sqlite3_stmt pointer?




Black, Michael (IS) wrote:
> 
> SQLITE_BUSY is not an error...just a fact.
>  
> All your processes cannot work on the database at the same time...at least
> not when one of them is doing an insert.  You could be changing the table
> while you're scanning it.  EXXCLUSIVE doesn't change that idea.
>  
> Somebody please correct me if I'm wrong on this one...
> I think sqlite can work with multiple processes just doing read-onliy
> operations (like SELECT).It's just the write operations
> (INSERT/UPDATE) which will cause SQLITE_BUSY to occur.
>  
> Michael D. Black
> Senior Scientist
> Northrop Grumman Mission Systems
>  
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
> Sent: Tue 5/11/2010 9:20 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and
> SQLITE_MISUSE
> 
> 
> 
> 
> Thank you very much!
> 
> It may be because my system's resource is limited. It's a embedded system
> containing 32M RAM, ARM9 CPU.
> 
> My "reiterating 20 times" is already using usleep().
> 
> After I add the loop in the prepare statements, the system performance is
> still very bad... And there are still many errores of SQLITE_BUSY.
> 
> The only improvement is the disappear of the error of SQLITE_MISUSE.
> 
> And I tried to use the "BEGIN EXCLUSIVE TRANSACTION". The things are same
> with them without using it.
> 
> 
> 
> 
> 
> 
> 
> Black, Michael (IS) wrote:
>>
>> Your "reiterating 20 times" is not using a usleep so you'll blow by this
>> most every time it's busy.
>> 
>> Do this instead in all your proc's
>> 
>> ret = sqlite3_step (p_stmt);
>> if (SQLITE_BUSY == ret)
>> {
>> int n=0;
>> usleep(10); // try one more time before error
>> while ((ret=sqlite3_step(p_stmt))==SQLITE_BUSY) {
>> printf("proc1 ret==BUSY %d\n",++n);
>> usleep(10);
>> }
>> }
>>
>> And you'll also need to handle "database is locked" coming from your
>> prepare statements.  I saw that error too.
>> You'll need to loop there too.
>>
>> The more you drop the usleep time the more times it will show as busy.
>> 1/10th or 1/100th of second is about all you want I would think.
>> 
>> And get rid of the usleep at the bottom of each proc -- it's pretty
>> useless at 100 microseconds.  You don't need to sleep unless you're busy.
>> 
>> I tested your code with this and got no errors at all -- just a bunch of
>> BUSY messages.
>> 
>> 
>> Not sure what your purpose is in sqlrun.c with looping and killing. 
>> Looks
>> pretty squirrely to me.  You're not waiting for the forks to finish so
>> what is your logic here?
>> 
>> Michael D. Black
>> Senior Scientist
>> Northrop Grumman Mission Systems
>> 
>>
>> 
>>
>> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
>> Sent: Tue 5/11/2010 4:57 AM
>> To: sqlite-users@sqlite.org
>> Subject: [sqlite] multi processes, so many errores of SQLITE_BUSY and
>> SQLITE_MISUSE
>>
>> ...
>>
>> --
>> View this message in context:
>> http://old.nabble.com/multi-processes%2C-so-many-errores-of-SQLITE_BUSY-and-SQLITE_MISUSE-tp28522127p28522127.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
>>
>>
> 
> --
> View this message in context:
> http://old.nabble.com/multi-processes%2C-so-many-errores-of-SQLITE_BUSY-and-SQLITE_MISUSE-tp28522127p28531394.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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/multi-processes%2C-so-many-errores-of-SQLITE_BUSY-and-SQLITE_MISUSE-tp28522127p28544420.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] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

[liubin liu]

Thank you very much!

It may be because my system's resource is limited. It's a embedded system
containing 32M RAM, ARM9 CPU.

My "reiterating 20 times" is already using usleep().

After I add the loop in the prepare statements, the system performance is
still very bad... And there are still many errores of SQLITE_BUSY.

The only improvement is the disappear of the error of SQLITE_MISUSE.

And I tried to use the "BEGIN EXCLUSIVE TRANSACTION". The things are same
with them without using it.







Black, Michael (IS) wrote:
> 
> Your "reiterating 20 times" is not using a usleep so you'll blow by this
> most every time it's busy.
>  
> Do this instead in all your proc's
>  
> ret = sqlite3_step (p_stmt);
> if (SQLITE_BUSY == ret)
> {
> int n=0;
> usleep(10); // try one more time before error
> while ((ret=sqlite3_step(p_stmt))==SQLITE_BUSY) {
> printf("proc1 ret==BUSY %d\n",++n);
> usleep(10);
> }
> }
> 
> And you'll also need to handle "database is locked" coming from your
> prepare statements.  I saw that error too.
> You'll need to loop there too.
> 
> The more you drop the usleep time the more times it will show as busy. 
> 1/10th or 1/100th of second is about all you want I would think.
>  
> And get rid of the usleep at the bottom of each proc -- it's pretty
> useless at 100 microseconds.  You don't need to sleep unless you're busy.
>  
> I tested your code with this and got no errors at all -- just a bunch of
> BUSY messages.
>  
>  
> Not sure what your purpose is in sqlrun.c with looping and killing.  Looks
> pretty squirrely to me.  You're not waiting for the forks to finish so
> what is your logic here?
>  
> Michael D. Black
> Senior Scientist
> Northrop Grumman Mission Systems
>  
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
> Sent: Tue 5/11/2010 4:57 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] multi processes, so many errores of SQLITE_BUSY and
> SQLITE_MISUSE
> 
> ...
> 
> --
> View this message in context:
> http://old.nabble.com/multi-processes%2C-so-many-errores-of-SQLITE_BUSY-and-SQLITE_MISUSE-tp28522127p28522127.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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/multi-processes%2C-so-many-errores-of-SQLITE_BUSY-and-SQLITE_MISUSE-tp28522127p28531394.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] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

[liubin liu]

Multi processes, getting so many errores of SQLITE_BUSY and SQLITE_MISUSE...

And the system performance is very bad because of the three processes of
insert/read/update database.

How to improve the sqlite3's operations?


_my codes___


___proc_main.c_


// create three processes of insert/read/update database.

// proc_1 : insert;
// proc_2 : select;
// proc_3 : update;


#include 
#include   // for fork();
#include  // for fork(); execv(); usleep();
#include  // for kill();

#include 


static int createdb (void);

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


ret = createdb ();
usleep (5);


int i=0;
while (1)
{
pid_t p1, p2, p3;

p1 = fork();
if (0 == p1)
{
char *argv1[] = {"proc_1"};
ret = execv ("./proc_1", argv1);
}

p2 = fork();
if (0 == p2)
{
char *argv2[] = {"proc_2"};
ret = execv ("./proc_2", argv2);
}

p3 = fork();
if (0 == p3)
{
char *argv3[] = {"proc_3"};
ret = execv ("./proc_3", argv3);
}


usleep (100 * 100);

while (1)
{
ret = kill (p1, SIGKILL);
ret = kill (p2, SIGKILL);
ret = kill (p3, SIGKILL);


usleep (10 * 100);
}
}

return 0;
}

static int createdb (void)
{
int ret = -1;

sqlite3 *db = NULL;
ret = sqlite3_open ("test.db", );


ret = sqlite3_exec (db, "CREATE TABLE t1 (mp_no INTEGER, di INTEGER,
data INT64, rec_time INTEGER, data_type CHAR(1) )", NULL,NULL,NULL);
ret = sqlite3_exec (db, "CREATE UNIQUE INDEX i_t1 ON t1 (mp_no, di)",
NULL,NULL,NULL);


ret = sqlite3_close (db);
return ret;
}




___proc_1.c_


#include 
#include// for time();
#include  // for srand(); rand();
#include  // for usleep();
#include 

#define DELAY_TIME  2   // 20ms
#define REDO_TIMES  60

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

struct tm *tm = NULL;
time_t t;
char datee[30];

FILE *fp1;
fp1 = fopen ("proc_1.log", "a+");

srand ((int) time(0));


sqlite3 *db = NULL;
ret = sqlite3_open ("test.db", );


char *sql_f = "INSERT OR REPLACE INTO t1 VALUES (%d, %d, %llu, %d, %d)";
char *sql = NULL;

sqlite3_stmt *p_stmt = NULL;

int i=0, n=5000;
for (i=0; i

Re: [sqlite] Is there any memory leak in the normal routine?

[liubin liu]

 ppStmt=0x7fff655c1ac8, pzTail=0x7fff655c1ad0) at sqlite3.c:80153
> #21 0x00451bc7 in sqlite3_exec (db=0x2acd74d28c10,
> zSql=0x47b358 "CREATE TABLE data_his (id INTEGER PRIMARY KEY, d1
> CHAR(16))", xCallback=0, pArg=0x0,
> pzErrMsg=0x0) at sqlite3.c:76835
> #22 0x00401d8a in main () at thread.c:16
> 
> 
>  
> Michael D. Black
> Senior Scientist
> Northrop Grumman Mission Systems
>  
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
> Sent: Sat 4/24/2010 1:58 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Is there any memory leak in the normal routine?
> 
> 
> 
> 
> And I watch the the process's run by top.
> 
> At first, the memory statistics is:
> PID PPID   USER STAT   VSZ%MEM  %CPU  COMMAND
> 17731 15488  root  S1104   5%  7% ./sqlite3multiwrite
> 
> When the printf() prints the 150, the memory statistics is:
> PID PPID   USER STAT   VSZ%MEM  %CPU  COMMAND
> 17731 15488  root  S1552   5%   7% ./sqlite3multiwrite
> 
> 
> It means that after 150 for-cycles, the memory used by sqlite3multiwrite
> increase from 1104KB to 1552KB.
> 
> What does it mean? memory leak or other thing?
> 
> --
> View this message in context:
> http://old.nabble.com/Is-there-any-memory-leak-in-the-normal-routine--tp28348648p28348725.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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Is-there-any-memory-leak-in-the-normal-routine--tp28348648p28354683.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] Is there any memory leak in the normal routine?

[liubin liu]

And I watch the the process's run by top.

At first, the memory statistics is:
PID PPID   USER STAT   VSZ%MEM  %CPU  COMMAND
17731 15488  root  S1104   5%  7% ./sqlite3multiwrite 

When the printf() prints the 150, the memory statistics is:
PID PPID   USER STAT   VSZ%MEM  %CPU  COMMAND
17731 15488  root  S1552   5%   7% ./sqlite3multiwrite 


It means that after 150 for-cycles, the memory used by sqlite3multiwrite
increase from 1104KB to 1552KB.

What does it mean? memory leak or other thing?

-- 
View this message in context: 
http://old.nabble.com/Is-there-any-memory-leak-in-the-normal-routine--tp28348648p28348725.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] Is there any memory leak in the normal routine?

[liubin liu]

A routine of sqlite3_prepare_v2() + sqlite3_step() + sqlite3_finalize() could
contain leak.

It sound ridiculous. But the test code seems to say it. Or I used the
sqlite3_* wrongly.



__code__

#include 
#include  // for usleep()
#include 


int multi_write (int j);

sqlite3 *db = NULL;

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

ret = sqlite3_open("test.db", );
ret = sqlite3_exec(db,"CREATE TABLE data_his (id INTEGER PRIMARY KEY, d1
CHAR(16))", NULL,NULL,NULL);
usleep (10);


int j=0;
while (1)
{
multi_write (j++);
usleep (200);
printf ("   --- %d\n", j);
}


ret = sqlite3_close (db);
return 0;
}


int multi_write (int j)
{
int ret = -1;

char *sql_f = "INSERT OR REPLACE INTO data_his VALUES (%d, %Q)";
char *sql = NULL;

sqlite3_stmt *p_stmt = NULL;


ret = sqlite3_prepare_v2 (db, "BEGIN TRANSACTION", -1, _stmt, NULL);
ret = sqlite3_step ( p_stmt );
ret = sqlite3_finalize ( p_stmt );

int i=0;
for (i=0; i<100; i++)
{
sql = sqlite3_mprintf ( sql_f, j*10 + i, "68FD");

ret = sqlite3_prepare_v2 (db, sql, -1, _stmt, NULL );
sqlite3_free ( sql );
//printf ("sqlite3_prepare_v2(): %d, %s\n", ret, sqlite3_errmsg
(db));

ret = sqlite3_step ( p_stmt );
//printf ("sqlite3_step():   %d, %s\n", ret, sqlite3_errmsg
(db));

ret = sqlite3_finalize ( p_stmt );
//printf ("sqlite3_finalize():   %d, %s\n\n", ret, sqlite3_errmsg
(db));
}

ret = sqlite3_prepare_v2 (db, "COMMIT TRANSACTION", -1, _stmt, NULL );
ret = sqlite3_step ( p_stmt );
ret = sqlite3_finalize ( p_stmt );


return 0;
}
-- 
View this message in context: 
http://old.nabble.com/Is-there-any-memory-leak-in-the-normal-routine--tp28348648p28348648.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] Is there any memory leak in the code while being busy?

[liubin liu]

the code's test result in my first post is :

pthread2:   prepare:  0,   p_stmt: 0xb6a00de0, errmsg: not an error
pthread2:   step: 5,   p_stmt: 0xb6a00de0, errmsg: database is locked
pthread2:   finalize: 5,   p_stmt: 0xb6a00de0, errmsg: database is locked
pthread2:   finalize: 21,   p_stmt: 0xb6a00de0, errmsg: database is locked

...
... (same as upper, nine times)
...

pthread2:   close: 0,  p_stmt: 0xb6a00dd0, errmsg: library routine
called out of sequence

___

>From the result, could I suppose that the sqlite3_finalize()'s return code
is wrong, and the memory pointed by p_stmt  isn't freed by
sqlite3_finalize()?






Simon Slavin-3 wrote:
> 
> 
> On 23 Apr 2010, at 1:16pm, liubin liu wrote:
> 
>> But I test the routine of sqlite3_prepare_v2() + sqlite3_step() +
>> sqlite3_finalize() in my real code.
>> 
>> And the test result say when sqlite3_step() is shadowed, the leak is
>> zero.
>> When doing the sqlite3_step(), the leak is about 1k byte. And another
>> curious phenomenon is that while there are many datas in the data file,
>> the
>> leak is more bigger than while there are few datas in the data file.
> 
> It is okay for both sqlite3_prepare_v2() and sqlite3_step() to use memory,
> but all the memory they use should be released by sqlite3_finalize().  So
> ignore any information you get until you have correctly called the
> _finalize() function.
> 
> Similarly, opening a database may use up memory, but calling _close()
> should free it all.
> 
> Simon.
> ___
> 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/Is-there-any-memory-leak-in-the-code-while-being-busy--tp28337646p28347933.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] Is there any memory leak in the code while being busy?

[liubin liu]

Thanks :)

But in my real code, the sqlite3_free(sql) is in the right place. So I think
that there are other reasons causing the leak.





Black, Michael (IS) wrote:
> 
> You need to sqlite3_free(sql) after you use the sql from your
> sqlite3_mprintf().
>  
>  sql = sqlite3_mprintf (sql_f, i);
>  ret = sqlite3_prepare_v2 (db1, sql, -1, _stmt, NULL);
>  sqlite3_free(sql);
> 
>  
> Michael D. Black
> Senior Scientist
> Northrop Grumman Mission Systems
>  
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org on behalf of liubin liu
> Sent: Fri 4/23/2010 7:16 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Is there any memory leak in the code while being
> busy?
> 
> 
> 
> 
> sorry for multi-send the message.
> 
> I just test the code again. And sqlite3_finalize() may free the memory.
> I'm
> wrong in the first post.
> 
> 
> But I test the routine of sqlite3_prepare_v2() + sqlite3_step() +
> sqlite3_finalize() in my real code.
> 
> And the test result say when sqlite3_step() is shadowed, the leak is zero.
> When doing the sqlite3_step(), the leak is about 1k byte. And another
> curious phenomenon is that while there are many datas in the data file,
> the
> leak is more bigger than while there are few datas in the data file.
> 
> 
> 
> 
> 
> Marcus Grimm wrote:
>>
>> it is not necessary to send your question multible times... ;)
>>
>> to answer: what makes you think that sqlite3_finalize can't
>> free the prepared statement ?
>>
>> liubin liu wrote:
>>> Is there any memory leak in the code?
>>>
>>> Below is the code. Is there any memory leak in the pthread2?
>>>
>>> While pthread1 is using test.db exclusively, the sqlite3_prepare_v2() of
>>> pthread2 still prepares the p_stmt pointer to a piece of memory malloced
>>> by
>>> sqlite3_preapare_v2(). And then the sqlite3_finalize() can't free the
>>> memory
>>> still because pthread1 is using test.db exclusively. Does it cause a
>>> memory
>>> leak?
>>>
>>>
>>>
>>> __code__
>>>
>>> #include 
>>> #include   // for usleep()
>>> #include// for gettimeofday()
>>> #include 
>>> #include 
>>>
>>> void pthread1 (void);
>>> void pthread2 (void);
>>>
>>>
>>> int main (void)
>>> {
>>> int ret = -1;
>>>
>>> sqlite3 *g_db = NULL;
>>> ret = sqlite3_open ("test.db", _db);
>>> ret = sqlite3_exec (g_db, "CREATE TABLE t1 (id INTEGER PRIMARY KEY,
>>> d1
>>> TEXT)", NULL,NULL,NULL);
>>> ret = sqlite3_close (g_db);
>>>
>>> usleep (50);
>>>
>>>
>>> pthread_t pthr1, pthr2;
>>> ret = pthread_create (, NULL, (void *) pthread1, NULL);
>>> ret = pthread_create (, NULL, (void *) pthread2, NULL);
>>>
>>>
>>> ret = pthread_join (pthr1, NULL);
>>> printf ("thread1 end\n");
>>> ret = pthread_join (pthr2, NULL);
>>> printf ("thread2 end\n");
>>>
>>> return 0;
>>> }
>>>
>>>
>>> void pthread1 (void)
>>> {
>>> int ret = -1;
>>> sqlite3 *db1 = NULL;
>>> ret = sqlite3_open ("test.db", );
>>>
>>> char *sql_f = "INSERT OR REPLACE INTO t1 VALUES (%d,
>>> 'aaabbbcccddd1122')";
>>> char *sql = NULL;
>>>
>>> sqlite3_stmt *p_stmt = NULL;
>>>
>>> struct timeval tv1, tv2;
>>> ret = gettimeofday (, NULL);
>>>
>>> ret = sqlite3_prepare_v2 (db1, "BEGIN TRANSACTION", -1, _stmt,
>>> NULL);
>>> ret = sqlite3_step (p_stmt);
>>> ret = sqlite3_finalize (p_stmt);
>>>
>>> // n=100, test.db is 25843712 bytes, 25M;
>>> int i=0, n=100;
>>> for (i=0; i<n; i++)
>>> {
>>> sql = sqlite3_mprintf (sql_f, i);
>>> ret = sqlite3_prepare_v2 (db1, sql, -1, _stmt, NULL);
>>> ret = sqlite3_step (p_stmt);
>>> ret = sqlite3_finalize (p_stmt);
>>> }
>>>
>>> printf ("pthread1:   ret: %d\n", ret);
>>> ret = sqlite3_prepare_v2 (db1, "COMMIT TRANSACTION", -1, _stmt,
>>> NULL);
>&g

Re: [sqlite] Is there any memory leak in the code while being busy?

[liubin liu]

sorry for multi-send the message.

I just test the code again. And sqlite3_finalize() may free the memory. I'm
wrong in the first post.


But I test the routine of sqlite3_prepare_v2() + sqlite3_step() +
sqlite3_finalize() in my real code.

And the test result say when sqlite3_step() is shadowed, the leak is zero.
When doing the sqlite3_step(), the leak is about 1k byte. And another
curious phenomenon is that while there are many datas in the data file, the
leak is more bigger than while there are few datas in the data file.





Marcus Grimm wrote:
> 
> it is not necessary to send your question multible times... ;)
> 
> to answer: what makes you think that sqlite3_finalize can't
> free the prepared statement ?
> 
> liubin liu wrote:
>> Is there any memory leak in the code? 
>> 
>> Below is the code. Is there any memory leak in the pthread2? 
>> 
>> While pthread1 is using test.db exclusively, the sqlite3_prepare_v2() of
>> pthread2 still prepares the p_stmt pointer to a piece of memory malloced
>> by
>> sqlite3_preapare_v2(). And then the sqlite3_finalize() can't free the
>> memory
>> still because pthread1 is using test.db exclusively. Does it cause a
>> memory
>> leak? 
>> 
>> 
>> 
>> __code__ 
>> 
>> #include  
>> #include   // for usleep() 
>> #include// for gettimeofday() 
>> #include  
>> #include  
>> 
>> void pthread1 (void); 
>> void pthread2 (void); 
>> 
>> 
>> int main (void) 
>> { 
>> int ret = -1; 
>> 
>> sqlite3 *g_db = NULL; 
>> ret = sqlite3_open ("test.db", _db); 
>> ret = sqlite3_exec (g_db, "CREATE TABLE t1 (id INTEGER PRIMARY KEY,
>> d1
>> TEXT)", NULL,NULL,NULL); 
>> ret = sqlite3_close (g_db); 
>> 
>> usleep (50); 
>> 
>> 
>> pthread_t pthr1, pthr2; 
>> ret = pthread_create (, NULL, (void *) pthread1, NULL); 
>> ret = pthread_create (, NULL, (void *) pthread2, NULL); 
>> 
>> 
>> ret = pthread_join (pthr1, NULL); 
>> printf ("thread1 end\n"); 
>> ret = pthread_join (pthr2, NULL); 
>> printf ("thread2 end\n"); 
>> 
>> return 0; 
>> } 
>> 
>> 
>> void pthread1 (void) 
>> { 
>> int ret = -1; 
>> sqlite3 *db1 = NULL; 
>> ret = sqlite3_open ("test.db", ); 
>> 
>> char *sql_f = "INSERT OR REPLACE INTO t1 VALUES (%d,
>> 'aaabbbcccddd1122')"; 
>> char *sql = NULL; 
>> 
>> sqlite3_stmt *p_stmt = NULL; 
>> 
>> struct timeval tv1, tv2; 
>> ret = gettimeofday (, NULL); 
>> 
>> ret = sqlite3_prepare_v2 (db1, "BEGIN TRANSACTION", -1, _stmt,
>> NULL); 
>> ret = sqlite3_step (p_stmt); 
>> ret = sqlite3_finalize (p_stmt); 
>> 
>> // n=100, test.db is 25843712 bytes, 25M; 
>> int i=0, n=100; 
>> for (i=0; i<n; i++) 
>> { 
>> sql = sqlite3_mprintf (sql_f, i); 
>> ret = sqlite3_prepare_v2 (db1, sql, -1, _stmt, NULL); 
>> ret = sqlite3_step (p_stmt); 
>> ret = sqlite3_finalize (p_stmt); 
>> } 
>> 
>> printf ("pthread1:   ret: %d\n", ret); 
>> ret = sqlite3_prepare_v2 (db1, "COMMIT TRANSACTION", -1, _stmt,
>> NULL); 
>> ret = sqlite3_step (p_stmt); 
>> ret = sqlite3_finalize (p_stmt); 
>> 
>> ret = gettimeofday (, NULL); 
>> printf ("time is : %ds\n", (int) (tv2.tv_sec - tv1.tv_sec)); 
>> 
>> 
>> ret = sqlite3_close (db1); 
>> } 
>> 
>> 
>> 
>> void pthread2 (void) 
>> { 
>> int ret = -1; 
>> 
>> sqlite3 *db2 = NULL; 
>> ret = sqlite3_open ("test.db", ); 
>> 
>> usleep (200); 
>> 
>> 
>> sqlite3_stmt *p_stmt = NULL; 
>> int i=0, n=10; 
>> for (i=0; i<n; i++) 
>> { 
>> char *sql_f = "INSERT OR REPLACE INTO t1 VALUES (%d,
>> '1122')"; 
>> char *sql = NULL; 
>> 
>> sql = sqlite3_mprintf (sql_f, i); 
>> ret = sqlite3_prepare_v2 (db2, sql, -1, _stmt, NULL); 
>> printf ("pthread2:   prepare:  %d,   p_stmt: %p, errmsg: %s\n",
>> ret,
>> p_stmt, sqlite3_errmsg(db2)); 
>> ret = sqlite3_step (p_stmt); 
>> printf ("pthread2:   step: %d,   p_stmt: %p, er

[sqlite] Is there any memory leak in the code while being busy?

[liubin liu]

Is there any memory leak in the code? 

Below is the code. Is there any memory leak in the pthread2? 

While pthread1 is using test.db exclusively, the sqlite3_prepare_v2() of
pthread2 still prepares the p_stmt pointer to a piece of memory malloced by
sqlite3_preapare_v2(). And then the sqlite3_finalize() can't free the memory
still because pthread1 is using test.db exclusively. Does it cause a memory
leak? 



__code__ 

#include  
#include   // for usleep() 
#include// for gettimeofday() 
#include  
#include  

void pthread1 (void); 
void pthread2 (void); 


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

sqlite3 *g_db = NULL; 
ret = sqlite3_open ("test.db", _db); 
ret = sqlite3_exec (g_db, "CREATE TABLE t1 (id INTEGER PRIMARY KEY, d1
TEXT)", NULL,NULL,NULL); 
ret = sqlite3_close (g_db); 

usleep (50); 


pthread_t pthr1, pthr2; 
ret = pthread_create (, NULL, (void *) pthread1, NULL); 
ret = pthread_create (, NULL, (void *) pthread2, NULL); 


ret = pthread_join (pthr1, NULL); 
printf ("thread1 end\n"); 
ret = pthread_join (pthr2, NULL); 
printf ("thread2 end\n"); 

return 0; 
} 


void pthread1 (void) 
{ 
int ret = -1; 
sqlite3 *db1 = NULL; 
ret = sqlite3_open ("test.db", ); 

char *sql_f = "INSERT OR REPLACE INTO t1 VALUES (%d,
'aaabbbcccddd1122')"; 
char *sql = NULL; 

sqlite3_stmt *p_stmt = NULL; 

struct timeval tv1, tv2; 
ret = gettimeofday (, NULL); 

ret = sqlite3_prepare_v2 (db1, "BEGIN TRANSACTION", -1, _stmt, NULL); 
ret = sqlite3_step (p_stmt); 
ret = sqlite3_finalize (p_stmt); 

// n=100, test.db is 25843712 bytes, 25M; 
int i=0, n=100; 
for (i=0; i

[sqlite] Is there any memory leak in the code ?

[liubin liu]

Is there any memory leak in the code?

Below is the code. Is there any memory leak in the pthread2? 

While pthread1 is using test.db exclusively, the sqlite3_prepare_v2() of
pthread2 still prepares the p_stmt pointer to a piece of memory malloced by
sqlite3_preapare_v2(). And then the sqlite3_finalize() can't free the memory
still because pthread1 is using test.db exclusively. Does it cause a memory
leak? 



__code__ 

#include  
#include   // for usleep() 
#include// for gettimeofday() 
#include  
#include  

void pthread1 (void); 
void pthread2 (void); 


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

sqlite3 *g_db = NULL; 
ret = sqlite3_open ("test.db", _db); 
ret = sqlite3_exec (g_db, "CREATE TABLE t1 (id INTEGER PRIMARY KEY, d1
TEXT)", NULL,NULL,NULL); 
ret = sqlite3_close (g_db); 

usleep (50); 


pthread_t pthr1, pthr2; 
ret = pthread_create (, NULL, (void *) pthread1, NULL); 
ret = pthread_create (, NULL, (void *) pthread2, NULL); 


ret = pthread_join (pthr1, NULL); 
printf ("thread1 end\n"); 
ret = pthread_join (pthr2, NULL); 
printf ("thread2 end\n"); 

return 0; 
} 


void pthread1 (void) 
{ 
int ret = -1; 
sqlite3 *db1 = NULL; 
ret = sqlite3_open ("test.db", ); 

char *sql_f = "INSERT OR REPLACE INTO t1 VALUES (%d,
'aaabbbcccddd1122')"; 
char *sql = NULL; 

sqlite3_stmt *p_stmt = NULL; 

struct timeval tv1, tv2; 
ret = gettimeofday (, NULL); 

ret = sqlite3_prepare_v2 (db1, "BEGIN TRANSACTION", -1, _stmt, NULL); 
ret = sqlite3_step (p_stmt); 
ret = sqlite3_finalize (p_stmt); 

// n=100, test.db is 25843712 bytes, 25M; 
int i=0, n=100; 
for (i=0; i

[sqlite] Is there any memory leak in the code?

[liubin liu]

Below is my code. My question is:
Is there any memory leak in the pthread2?

While pthread1 is using test.db exclusively, the sqlite3_prepare_v2() of
pthread2 still prepares the p_stmt pointer to a piece of memory malloced by
sqlite3_preapare_v2(). And then the sqlite3_finalize() can't free the memory
still because pthread1 is using test.db exclusively. Does it cause a memory
leak?


__code__

#include 
#include   // for usleep()
#include// for gettimeofday()
#include 
#include 

void pthread1 (void);
void pthread2 (void);


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

sqlite3 *g_db = NULL;
ret = sqlite3_open ("test.db", _db);
ret = sqlite3_exec (g_db, "CREATE TABLE t1 (id INTEGER PRIMARY KEY, d1
TEXT)", NULL,NULL,NULL);
ret = sqlite3_close (g_db);

usleep (50);


pthread_t pthr1, pthr2;
ret = pthread_create (, NULL, (void *) pthread1, NULL);
ret = pthread_create (, NULL, (void *) pthread2, NULL);


ret = pthread_join (pthr1, NULL);
printf ("thread1 end\n");
ret = pthread_join (pthr2, NULL);
printf ("thread2 end\n");

return 0;
}


void pthread1 (void)
{
int ret = -1;
sqlite3 *db1 = NULL;
ret = sqlite3_open ("test.db", );

char *sql_f = "INSERT OR REPLACE INTO t1 VALUES (%d,
'aaabbbcccddd1122')";
char *sql = NULL;

sqlite3_stmt *p_stmt = NULL;

struct timeval tv1, tv2;
ret = gettimeofday (, NULL);

ret = sqlite3_prepare_v2 (db1, "BEGIN TRANSACTION", -1, _stmt, NULL);
ret = sqlite3_step (p_stmt);
ret = sqlite3_finalize (p_stmt);

// n=100, test.db is 25843712 bytes, 25M;
int i=0, n=100;
for (i=0; i

[sqlite] why block at sqlite3_prepare_v2(*)?

[liubin liu]

my program is blocked at the point of sqlite3_prepare_v2(*). Why? 


512 
513 sqlite3_stmt *p_stmt = NULL; 
514 printf ("  ###  500, %p, %s, %p\n",
g_db_base, sql, p_stmt); 
515 ret = sqlite3_prepare_v2 (g_db_base, sql, -1, _stmt, NULL); 
516 printf ("  ###  501\n"); 
517 if (SQLITE_OK != ret) 


the printf of line 514 done. 
the printf of line 516 didn't done. 
-- 
View this message in context: 
http://old.nabble.com/why-block--at--sqlite3_prepare_v2%28*%29--tp28221827p28221827.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] why block at sqlite3_prepare_v2(*)?

[liubin liu]

my program is blocked at the point of sqlite3_prepare_v2(*). Why?


512 
513 sqlite3_stmt *p_stmt = NULL;
514 printf ("  ###  500, %p, %s, %p\n",
g_db_base, sql, p_stmt);
515 ret = sqlite3_prepare_v2 (g_db_base, sql, -1, _stmt, NULL);
516 printf ("  ###  501\n");
517 if (SQLITE_OK != ret)


the printf of line 514 done.
the printf of line 516 didn't done.

-- 
View this message in context: 
http://old.nabble.com/why-block-at-sqlite3_prepare_v2%28*%29--tp28220519p28220519.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] to get 2 records after a special records. But wrong count, the count is 5

[liubin liu]

Thanks, I've gotten the point, :)

and the next question is that how to know the true count after a special
record while getting a special number records.



P Kishor-3 wrote:
> 
> On Sun, Mar 21, 2010 at 9:19 PM, liubin liu <7101...@sina.com> wrote:
>>
>>
>> my application is like below. To get 2 records after a special records.
>> But
>> wrong count, the count is 5:
>> sqlite>
>> sqlite> CREATE TABLE t1 (id INTEGER PRIMARY KEY, data INTEGER);
>> sqlite> INSERT INTO t1 VALUES (3, 999);
>> sqlite> INSERT INTO t1 VALUES (2, 989);
>> sqlite> INSERT INTO t1 VALUES (4, 1009);
>> sqlite> INSERT INTO t1 VALUES (7, 1019);
>> sqlite> INSERT INTO t1 VALUES (9, 1029);
>> sqlite> INSERT INTO t1 VALUES (10, 1039);
>> sqlite> SELECT * FROM t1 WHERE id>=3 LIMIT 2;   # right
>> 3|999
>> 4|1009
>> sqlite> SELECT COUNT(*) FROM t1 WHERE id>=3 LIMIT 2;   # wrong count,
>> why?
>> 5
>> sqlite>
>>
>>
>> 
>>
>> I'm confused that the "count(*)" return wrong count.
>>
> 
> Read the docs -- "The LIMIT clause places an upper bound on the number
> of rows returned in the result."
> 
> 
> You are confused about what Count(*) is returning. Count(*) is not
> returning the number of rows in your result set. It is returning the
> number of rows that match your WHERE clause, and that answer itself
> takes up only one row. The LIMIT keyword, on the other hand, is
> limiting the number of rows in your result set. Since the LIMIT clause
> is limiting the answer to 2 rows, but the answer itself consists of
> only one row, the LIMIT clause is pointless.
> 
> 
> 
> -- 
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> ___
> 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/to-get-2-records-after-a-special-records.-But-wrong-count%2C-the-count-is-5-tp27981805p27982625.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] to get 2 records after a special records. But wrong count, the count is 5

[liubin liu]

my application is like below. To get 2 records after a special records. But
wrong count, the count is 5:
sqlite> 
sqlite> CREATE TABLE t1 (id INTEGER PRIMARY KEY, data INTEGER);
sqlite> INSERT INTO t1 VALUES (3, 999);
sqlite> INSERT INTO t1 VALUES (2, 989);
sqlite> INSERT INTO t1 VALUES (4, 1009);
sqlite> INSERT INTO t1 VALUES (7, 1019);
sqlite> INSERT INTO t1 VALUES (9, 1029);
sqlite> INSERT INTO t1 VALUES (10, 1039);
sqlite> SELECT * FROM t1 WHERE id>=3 LIMIT 2;   # right
3|999
4|1009
sqlite> SELECT COUNT(*) FROM t1 WHERE id>=3 LIMIT 2;   # wrong count, why?
5
sqlite> 




I'm confused that the "count(*)" return wrong count.

-- 
View this message in context: 
http://old.nabble.com/to-get-2-records-after-a-special-records.-But-wrong-count%2C-the-count-is-5-tp27981805p27981805.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] How to patch several sql words into one words

[liubin liu]

I want to insert several pieces of data (include blob) into a table in a
transaction by programing in C language , like:
char *sql[n+2];

sql[0] = sqlite3_mprintf ("BEGIN;");

char *sql_f = "INSERT OR REPLACE INTO collstate VALUES (%d, %d, ?);";
for (i=1; i

[sqlite] one column is another table's row num

[liubin liu]

I want to create two tables like:
create table t1 (id INT PRIMARY KEY, cont TEXT);
create table t2 ({select count(*) from t1} INT);

How could I do it?
-- 
View this message in context: 
http://old.nabble.com/one-column-is-another-table%27s-row-num-tp27940860p27940860.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] why SQLITE_BUSY when read the database

[liubin liu]

why SQLITE_BUSY when read the database

My application suffered a problem that sqlite3_step(*) of SELECT returns
SQLITE_BUSY.

I know "Multiple processes can be doing a SELECT at the same time. But only
one process can be making changes to the database at any moment in time,
however." from the sqlite3's faq in the link
http://www.sqlite.org/faq.html#q5;.

Do The words mean that while one process is writing the database other
processes could not read the database at the same time?
-- 
View this message in context: 
http://old.nabble.com/why-SQLITE_BUSY-when-read-the-database-tp27848863p27848863.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] how to show the equivalent hex value of integer data while select in sqlite3 shell?

[liubin liu]

how to show the equivalent hex value of integer data while select in sqlite3
shell?


sqlite> 
sqlite> CREATE TABLE test (id INTEGER, data INTEGER);
sqlite> INSERT INTO test VALUES (1, 32);
sqlite> SELECT * FROM test;
1|32
sqlite> 
sqlite> 
sqlite> SELECT hex(data) FROM test;
3332   ### hope to get 0x20
sqlite> 

-- 
View this message in context: 
http://old.nabble.com/how-to-show-the-equivalent-hex-value-of-integer-data-while-select-in-sqlite3-shell--tp27009563p27009563.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] speed, writing spends 30ms, reading spends 2ms, still slow

[liubin liu]

The environment is:
Linux at91sam9260nand 2.6.25.10
SQLite 3.6.5


liubin liu wrote:
> 
> the process runs on a board with Atmel AT91SAM9260 (Clocks: CPU 198 MHz,
> ...), Memory: 16MB, yaffs on NAND 256MB.
> 
> the time of writing one row is more than 20mS, and the time of reading one
> row is more than 3mS.
> 
> Is it the ceiling of the SQLite3? If no, how to improve it? espically
> improving the routine of writing.
> 
> Thanks in advance. :)
> 
> code___
> 
> // 测试sqlite3 写入、读取、修改数据库的时间 (一个进程只包含一个打开、关闭数据库), 20091223
> //
> // arm-linux-gcc -Wall -g -lpthread -lsqlite3sqlite3speed.c   -o
> sqlite3speed
> 
> #include 
> #include 
> #include  // for system(*)
> #include// for gettimeofday(*)
> 
> void test_sqlite3_time_write (void);
> void test_sqlite3_time_read (void);
> 
> sqlite3 *g_db = NULL;
> struct timeval  g_tv1;
> struct timeval  g_tv2;
> struct timezone g_tz;
> 
> int main (void)
> {
> int ret = -1;
> int c = 0;
> char *ct = "CREATE TABLE testspeed (id INTEGER PRIMARY KEY, type
> INTEGER, content TEXT)";
> 
> ret = sqlite3_open ("testspeed.db", _db);
> ret = sqlite3_exec (g_db, ct, NULL, NULL, NULL);
> 
> printf ("test sqlite3's writing、reading time\n");
> printf ("1、 writing\n");
> printf ("2、 reading\n");
> while ((c = getchar()) != 'q')
> {
> switch (c)
> {
> case '1':
> test_sqlite3_time_write ();
> break;
> case '2':
> test_sqlite3_time_read ();
> break;
> default:
> break;
> }
> }
> 
> ret = sqlite3_close (g_db);
> 
> return 0;
> }
> 
> void test_sqlite3_time_write (void)
> {
> int  ret  = -1;
> char *i_f = "INSERT OR REPLACE INTO testspeed VALUES (%d, %d, %Q)";
> char *i_s = NULL;
> 
> /*
> gettimeofday (_tv1, _tz);
> i_s = sqlite3_mprintf (i_f, 1, 22, "reliable测试");
> ret = sqlite3_exec (g_db, i_s, NULL, NULL, NULL);
> gettimeofday (_tv2, _tz);
> ret = system ("sqlite3 testspeed.db \"SELECT * FROM testspeed\"");
> if (0 == ret)
> {
> printf ("using sqlite3_exec() writing one row spends: %d us\n\n",
> (int) ((g_tv2.tv_sec - g_tv1.tv_sec)*100 +
> g_tv2.tv_usec - g_tv1.tv_usec));
> system ("sqlite3 testspeed.db \"DELETE FROM testspeed\"");
> }
> */
> 
> 
> sqlite3_stmt *stmt = NULL;
> 
> 
> gettimeofday (_tv1, _tz);
> 
> i_s = sqlite3_mprintf (i_f, 1, 22, "test可靠吗？");
> ret = sqlite3_prepare_v2 (g_db, i_s, -1, , NULL);
> if (SQLITE_OK == ret)
> {
> ret = sqlite3_step (stmt);
> if (SQLITE_DONE == ret)
> {
> sqlite3_finalize (stmt);
> 
> gettimeofday (_tv2, _tz);
> 
> ret = system ("sqlite3 testspeed.db \"SELECT * FROM
> testspeed\"");
> if (0 == ret)
> {
> printf ("using sqlite3_prepare_v2() + sqlite3_step()
> writing one row spends: %d uS\n\n",
> (int) ((g_tv2.tv_sec - g_tv1.tv_sec)*100 +
> g_tv2.tv_usec - g_tv1.tv_usec));
> system ("sqlite3 testspeed.db \"DELETE FROM testspeed\"");
> }
> }
> else
> sqlite3_finalize (stmt);
> }
> }
> 
> void test_sqlite3_time_read (void)
> {
> int  ret  = -1;
> char *i_f = "SELECT * FROM testspeed WHERE id=%d";
> char *i_s = NULL;
> 
> 
> sqlite3_exec (g_db, "INSERT OR REPLACE INTO testspeed VALUES (1, 22,
> \"测试, yes\")", NULL,NULL,NULL);
> 
> /*
> char **sgt_medi = NULL;
> char **sgt_resu = NULL;
> int column = 0;
> int row = 0;
> 
> gettimeofday (_tv1, _tz);
> i_s = sqlite3_mprintf (i_f, 1);
> ret = sqlite3_get_table (g_db, i_s, _medi, , , NULL);
> if (SQLITE_OK == ret)
> {
> sgt_resu = sgt_medi + column;
> printf ("%d, %d, %d, %s\n", row, atoi (sgt_resu[0]), atoi
> (sgt_resu[1]), sgt_resu[2]);
> 
> gettimeofday (_tv2, _tz);
> printf ("using sqlite3_get_table() reading one row spends: %d
> uS\n\n",
> (int) ((g_tv2.tv_sec - g_tv1.tv_sec)*100 +
> g_tv2.tv_usec - g_tv1.tv_usec));
> 
> syst

[sqlite] speed, writing spends 30ms, reading spends 2ms, still slow

[liubin liu]

the process runs on a board with Atmel AT91SAM9260 (Clocks: CPU 198 MHz,
...), Memory: 16MB, yaffs on NAND 256MB.

the time of writing one row is more than 20mS, and the time of reading one
row is more than 3mS.

Is it the ceiling of the SQLite3? If no, how to improve it? espically
improving the routine of writing.

Thanks in advance. :)

code___

// 测试sqlite3 写入、读取、修改数据库的时间 (一个进程只包含一个打开、关闭数据库), 20091223
//
// arm-linux-gcc -Wall -g -lpthread -lsqlite3sqlite3speed.c   -o
sqlite3speed

#include 
#include 
#include  // for system(*)
#include// for gettimeofday(*)

void test_sqlite3_time_write (void);
void test_sqlite3_time_read (void);

sqlite3 *g_db = NULL;
struct timeval  g_tv1;
struct timeval  g_tv2;
struct timezone g_tz;

int main (void)
{
int ret = -1;
int c = 0;
char *ct = "CREATE TABLE testspeed (id INTEGER PRIMARY KEY, type
INTEGER, content TEXT)";

ret = sqlite3_open ("testspeed.db", _db);
ret = sqlite3_exec (g_db, ct, NULL, NULL, NULL);

printf ("test sqlite3's writing、reading time\n");
printf ("1、 writing\n");
printf ("2、 reading\n");
while ((c = getchar()) != 'q')
{
switch (c)
{
case '1':
test_sqlite3_time_write ();
break;
case '2':
test_sqlite3_time_read ();
break;
default:
break;
}
}

ret = sqlite3_close (g_db);

return 0;
}

void test_sqlite3_time_write (void)
{
int  ret  = -1;
char *i_f = "INSERT OR REPLACE INTO testspeed VALUES (%d, %d, %Q)";
char *i_s = NULL;

/*
gettimeofday (_tv1, _tz);
i_s = sqlite3_mprintf (i_f, 1, 22, "reliable测试");
ret = sqlite3_exec (g_db, i_s, NULL, NULL, NULL);
gettimeofday (_tv2, _tz);
ret = system ("sqlite3 testspeed.db \"SELECT * FROM testspeed\"");
if (0 == ret)
{
printf ("using sqlite3_exec() writing one row spends: %d us\n\n",
(int) ((g_tv2.tv_sec - g_tv1.tv_sec)*100 + g_tv2.tv_usec
- g_tv1.tv_usec));
system ("sqlite3 testspeed.db \"DELETE FROM testspeed\"");
}
*/


sqlite3_stmt *stmt = NULL;


gettimeofday (_tv1, _tz);

i_s = sqlite3_mprintf (i_f, 1, 22, "test可靠吗？");
ret = sqlite3_prepare_v2 (g_db, i_s, -1, , NULL);
if (SQLITE_OK == ret)
{
ret = sqlite3_step (stmt);
if (SQLITE_DONE == ret)
{
sqlite3_finalize (stmt);

gettimeofday (_tv2, _tz);

ret = system ("sqlite3 testspeed.db \"SELECT * FROM
testspeed\"");
if (0 == ret)
{
printf ("using sqlite3_prepare_v2() + sqlite3_step() writing
one row spends: %d uS\n\n",
(int) ((g_tv2.tv_sec - g_tv1.tv_sec)*100 +
g_tv2.tv_usec - g_tv1.tv_usec));
system ("sqlite3 testspeed.db \"DELETE FROM testspeed\"");
}
}
else
sqlite3_finalize (stmt);
}
}

void test_sqlite3_time_read (void)
{
int  ret  = -1;
char *i_f = "SELECT * FROM testspeed WHERE id=%d";
char *i_s = NULL;


sqlite3_exec (g_db, "INSERT OR REPLACE INTO testspeed VALUES (1, 22,
\"测试, yes\")", NULL,NULL,NULL);

/*
char **sgt_medi = NULL;
char **sgt_resu = NULL;
int column = 0;
int row = 0;

gettimeofday (_tv1, _tz);
i_s = sqlite3_mprintf (i_f, 1);
ret = sqlite3_get_table (g_db, i_s, _medi, , , NULL);
if (SQLITE_OK == ret)
{
sgt_resu = sgt_medi + column;
printf ("%d, %d, %d, %s\n", row, atoi (sgt_resu[0]), atoi
(sgt_resu[1]), sgt_resu[2]);

gettimeofday (_tv2, _tz);
printf ("using sqlite3_get_table() reading one row spends: %d
uS\n\n",
(int) ((g_tv2.tv_sec - g_tv1.tv_sec)*100 + g_tv2.tv_usec
- g_tv1.tv_usec));

system ("sqlite3 testspeed.db \"DELETE FROM testspeed\"");
}
*/


sqlite3_stmt *stmt = NULL;

gettimeofday (_tv1, _tz);

i_s = sqlite3_mprintf (i_f, 1);
ret = sqlite3_prepare_v2 (g_db, i_s, -1, , NULL);
if (SQLITE_OK == ret)
{
ret = sqlite3_step (stmt);
if (SQLITE_ROW == ret)
{
printf ("%d, %d, %s\n",
sqlite3_column_int (stmt, 0), sqlite3_column_int (stmt, 1),
sqlite3_column_text (stmt, 2));

sqlite3_finalize (stmt);

gettimeofday (_tv2, _tz);
printf ("using sqlite3_prepare_v2() + sqlite3_step() reading one
row spends: %d uS\n\n",
(int) ((g_tv2.tv_sec - g_tv1.tv_sec)*100 + g_tv2.tv_usec -
g_tv1.tv_usec));

system ("sqlite3 testspeed.db \"DELETE FROM testspeed\"");
}
else
sqlite3_finalize (stmt);
}
}


-- 
View this message in context: 
http://old.nabble.com/speed%2C-writing-spends-30ms%2C-reading-spends-2ms%2C-still-slow-tp26909939p26909939.html
Sent from the SQLite mailing list archive 

Re: [sqlite] how to show blob data while select in sqlite3 shell?

[liubin liu]

Thanks!!!


SimonDavies wrote:
> 
> 2009/12/9 liubin liu <7101...@sina.com>:
>>
>> sqlite> INSERT OR REPLACE INTO periods_value VALUES (0, 1,
>> x'000102030400a0afaabbaa');
>> sqlite>
>> sqlite> SELECT * FROM periods_value;
>> 0|1|
>> sqlite>
>>
>> how to show the blob data?
> 
> Select id, valid, hex( value ) from periods_value;
> 
> Regards,
> Simon
> ___
> 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/how-to-show-blob-data-while-select-in-sqlite3-shell--tp26709509p26709778.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] how to show blob data while select in sqlite3 shell?

[liubin liu]

[...@** createdb]$ 
[...@** createdb]$ sqlite3 ./db_ctrl_0.1.db
SQLite version 3.6.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE periods_value (id INTEGER PRIMARY KEY, valid CHAR(1),
value BLOB );
sqlite> 
sqlite> INSERT OR REPLACE INTO periods_value VALUES (0, 1,
x'000102030400a0afaabbaa');
sqlite> 
sqlite> SELECT * FROM periods_value;
0|1|
sqlite> 



how to show the blow data?
-- 
View this message in context: 
http://old.nabble.com/how-to-show-blob-data-while-select-in-sqlite3-shell--tp26709509p26709509.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] sqlite3 is blocked by transaction when wanting to close the sqlite3 *

[liubin liu]

Thank you

I do the job as you say, but the problem is still here. 

There are several processes who write the database. And the place is not
same each time that the problem happens. 

How could I know the reason exactly? 

PS: 
I used the signal to close the connection to database. Just like: 

int main () 
{ 
... 
signal (SIGUSR2, (void*)sig_handler); 
 
} 

void sig_handler(int sig) 
{ 
... 
switch(sig) 
{ 
case SIGUSR2: 
... 
sqlite3_interrupt (db);
ret = sqlite3_close (db);  // is blocked here 
... 
} 
... 
} 




Pavel Ivanov-2 wrote:
> 
>> Because there are several process who use the database. I have another
>> question:Could I close the database of other process in main process?
> 
> Just use your favorite IPC mechanism and write your application so
> that main process sends message to other process and when other
> process receives it then it closes its database connection...
> 
> Pavel
> 
> On Sun, Nov 29, 2009 at 7:46 PM, liubin liu <7101...@sina.com> wrote:
>>
>> Thank you!
>>
>> I'm sorry for not showing clearly the environment is embedded linux on
>> arm
>> board.
>>
>> Because there are several process who use the database. I have another
>> question:Could I close the database of other process in main process?
>>
>>
>> Nick Shaw-3 wrote:
>>>
>>> By "other process" do you mean a separate DLL or similar?  You can't
>>> free memory allocated in a DLL from an application, even when that
>>> application has the DLL loaded - Windows will complain.  This could be
>>> what's happening.
>>>
>>> Could you instead write the database close call within this other
>>> process, and call it from the main process when you shut down?
>>>
>>> Nick.
>>>
>>> -Original Message-
>>> From: sqlite-users-boun...@sqlite.org
>>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of liubin liu
>>> Sent: 26 November 2009 02:29
>>> To: sqlite-users@sqlite.org
>>> Subject: [sqlite] sqlite3 is blocked by transaction when wanting to
>>> close the sqlite3 *
>>>
>>>
>>> My application includes a main process and some other processes. I open
>>> the
>>> database in other process, but at end I will close the database in main
>>> process.
>>>
>>> The problem happens while I close the database. The main process is
>>> blocked.
>>> And I could see the journal file is still there, so I guess there are
>>> still
>>> some transactions.
>>>
>>> How resolve the problem?
>>>
>>> Thanks in advance!
>>> --
>>> View this message in context:
>>> http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-wanting-to-
>>> close-the-sqlite3-*-tp26523551p26523551.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
>>>
>>>
>>
>> --
>> View this message in context:
>> http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-closing-the-sqlite3-*-tp26523551p26568098.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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-closing-the-sqlite3-*-tp26523551p26635818.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] sqlite3 is blocked by transaction when wanting to close the sqlite3 *

[liubin liu]

I do the job as you say, but the problem is still here.

There are several processes who write the database. And the place is not
same each time that the problem happens.

How could I know the reason exactly?

PS:
I used the signal to close the connection to database. Just like:

int main ()
{ 
... 
signal (SIGUSR2, (void*)sig_handler); 
 
} 

void sig_handler(int sig) 
{ 
... 
switch(sig) 
{ 
case SIGUSR2: 
... 
sqlite3_close (db);  // is blocked here 
... 
} 
... 
} 


Pavel Ivanov-2 wrote:
> 
>> Because there are several process who use the database. I have another
>> question:Could I close the database of other process in main process?
> 
> Just use your favorite IPC mechanism and write your application so
> that main process sends message to other process and when other
> process receives it then it closes its database connection...
> 
> Pavel
> 
> On Sun, Nov 29, 2009 at 7:46 PM, liubin liu <7101...@sina.com> wrote:
>>
>> Thank you!
>>
>> I'm sorry for not showing clearly the environment is embedded linux on
>> arm
>> board.
>>
>> Because there are several process who use the database. I have another
>> question:Could I close the database of other process in main process?
>>
>>
>> Nick Shaw-3 wrote:
>>>
>>> By "other process" do you mean a separate DLL or similar?  You can't
>>> free memory allocated in a DLL from an application, even when that
>>> application has the DLL loaded - Windows will complain.  This could be
>>> what's happening.
>>>
>>> Could you instead write the database close call within this other
>>> process, and call it from the main process when you shut down?
>>>
>>> Nick.
>>>
>>> -Original Message-
>>> From: sqlite-users-boun...@sqlite.org
>>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of liubin liu
>>> Sent: 26 November 2009 02:29
>>> To: sqlite-users@sqlite.org
>>> Subject: [sqlite] sqlite3 is blocked by transaction when wanting to
>>> close the sqlite3 *
>>>
>>>
>>> My application includes a main process and some other processes. I open
>>> the
>>> database in other process, but at end I will close the database in main
>>> process.
>>>
>>> The problem happens while I close the database. The main process is
>>> blocked.
>>> And I could see the journal file is still there, so I guess there are
>>> still
>>> some transactions.
>>>
>>> How resolve the problem?
>>>
>>> Thanks in advance!
>>> --
>>> View this message in context:
>>> http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-wanting-to-
>>> close-the-sqlite3-*-tp26523551p26523551.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
>>>
>>>
>>
>> --
>> View this message in context:
>> http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-closing-the-sqlite3-*-tp26523551p26568098.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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-closing-the-sqlite3-*-tp26523551p26635809.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] sqlite3 is blocked by transaction when wanting to close the sqlite3 *

[liubin liu]

PS:
I used the signal to close the connection to database. Just like:

int main ()
{
...
signal (SIGUSR2, (void*)sig_handler);

}

void sys_sig_handler(int sig)
{
...
switch(sig)
{
case SIGUSR2:
...
sqlite3_close (db);
...
}
...
}



liubin liu wrote:
> 
> I do the job as you say, but the problem is still here.
> 
> There are several processes who write the database. And the place is not
> same each time that the problem happens.
> 
> How could I know the reason exactly?
> 
> 
> 
> Pavel Ivanov-2 wrote:
>> 
>>> Because there are several process who use the database. I have another
>>> question:Could I close the database of other process in main process?
>> 
>> Just use your favorite IPC mechanism and write your application so
>> that main process sends message to other process and when other
>> process receives it then it closes its database connection...
>> 
>> Pavel
>> 
>> On Sun, Nov 29, 2009 at 7:46 PM, liubin liu <7101...@sina.com> wrote:
>>>
>>> Thank you!
>>>
>>> I'm sorry for not showing clearly the environment is embedded linux on
>>> arm
>>> board.
>>>
>>> Because there are several process who use the database. I have another
>>> question:Could I close the database of other process in main process?
>>>
>>>
>>> Nick Shaw-3 wrote:
>>>>
>>>> By "other process" do you mean a separate DLL or similar?  You can't
>>>> free memory allocated in a DLL from an application, even when that
>>>> application has the DLL loaded - Windows will complain.  This could be
>>>> what's happening.
>>>>
>>>> Could you instead write the database close call within this other
>>>> process, and call it from the main process when you shut down?
>>>>
>>>> Nick.
>>>>
>>>> -Original Message-
>>>> From: sqlite-users-boun...@sqlite.org
>>>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of liubin liu
>>>> Sent: 26 November 2009 02:29
>>>> To: sqlite-users@sqlite.org
>>>> Subject: [sqlite] sqlite3 is blocked by transaction when wanting to
>>>> close the sqlite3 *
>>>>
>>>>
>>>> My application includes a main process and some other processes. I open
>>>> the
>>>> database in other process, but at end I will close the database in main
>>>> process.
>>>>
>>>> The problem happens while I close the database. The main process is
>>>> blocked.
>>>> And I could see the journal file is still there, so I guess there are
>>>> still
>>>> some transactions.
>>>>
>>>> How resolve the problem?
>>>>
>>>> Thanks in advance!
>>>> --
>>>> View this message in context:
>>>> http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-wanting-to-
>>>> close-the-sqlite3-*-tp26523551p26523551.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
>>>>
>>>>
>>>
>>> --
>>> View this message in context:
>>> http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-closing-the-sqlite3-*-tp26523551p26568098.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
>> 
>> 
> 
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-closing-the-sqlite3-*-tp26523551p26635743.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] sqlite3 is blocked by transaction when wanting to close the sqlite3 *

[liubin liu]

I do the job as you say, but the problem is still here.

There are several processes who write the database. And the place is not
same each time that the problem happens.

How could I know the reason exactly?



Pavel Ivanov-2 wrote:
> 
>> Because there are several process who use the database. I have another
>> question:Could I close the database of other process in main process?
> 
> Just use your favorite IPC mechanism and write your application so
> that main process sends message to other process and when other
> process receives it then it closes its database connection...
> 
> Pavel
> 
> On Sun, Nov 29, 2009 at 7:46 PM, liubin liu <7101...@sina.com> wrote:
>>
>> Thank you!
>>
>> I'm sorry for not showing clearly the environment is embedded linux on
>> arm
>> board.
>>
>> Because there are several process who use the database. I have another
>> question:Could I close the database of other process in main process?
>>
>>
>> Nick Shaw-3 wrote:
>>>
>>> By "other process" do you mean a separate DLL or similar?  You can't
>>> free memory allocated in a DLL from an application, even when that
>>> application has the DLL loaded - Windows will complain.  This could be
>>> what's happening.
>>>
>>> Could you instead write the database close call within this other
>>> process, and call it from the main process when you shut down?
>>>
>>> Nick.
>>>
>>> -Original Message-
>>> From: sqlite-users-boun...@sqlite.org
>>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of liubin liu
>>> Sent: 26 November 2009 02:29
>>> To: sqlite-users@sqlite.org
>>> Subject: [sqlite] sqlite3 is blocked by transaction when wanting to
>>> close the sqlite3 *
>>>
>>>
>>> My application includes a main process and some other processes. I open
>>> the
>>> database in other process, but at end I will close the database in main
>>> process.
>>>
>>> The problem happens while I close the database. The main process is
>>> blocked.
>>> And I could see the journal file is still there, so I guess there are
>>> still
>>> some transactions.
>>>
>>> How resolve the problem?
>>>
>>> Thanks in advance!
>>> --
>>> View this message in context:
>>> http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-wanting-to-
>>> close-the-sqlite3-*-tp26523551p26523551.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
>>>
>>>
>>
>> --
>> View this message in context:
>> http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-closing-the-sqlite3-*-tp26523551p26568098.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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-closing-the-sqlite3-*-tp26523551p26635742.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] sqlite3 is blocked by transaction when wanting to close the sqlite3 *

[liubin liu]

Thank you!

I'm sorry for not showing clearly the environment is embedded linux on arm
board.

Because there are several process who use the database. I have another
question:Could I close the database of other process in main process?


Nick Shaw-3 wrote:
> 
> By "other process" do you mean a separate DLL or similar?  You can't
> free memory allocated in a DLL from an application, even when that
> application has the DLL loaded - Windows will complain.  This could be
> what's happening.
> 
> Could you instead write the database close call within this other
> process, and call it from the main process when you shut down?
> 
> Nick.
> 
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of liubin liu
> Sent: 26 November 2009 02:29
> To: sqlite-users@sqlite.org
> Subject: [sqlite] sqlite3 is blocked by transaction when wanting to
> close the sqlite3 *
> 
> 
> My application includes a main process and some other processes. I open
> the
> database in other process, but at end I will close the database in main
> process.
> 
> The problem happens while I close the database. The main process is
> blocked.
> And I could see the journal file is still there, so I guess there are
> still
> some transactions.
> 
> How resolve the problem?
> 
> Thanks in advance!
> -- 
> View this message in context:
> http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-wanting-to-
> close-the-sqlite3-*-tp26523551p26523551.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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-closing-the-sqlite3-*-tp26523551p26568098.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] sqlite3 is blocked by transaction when wanting to close the sqlite3 *

[liubin liu]

My application includes a main process and some other processes. I open the
database in other process, but at end I will close the database in main
process.

The problem happens while I close the database. The main process is blocked.
And I could see the journal file is still there, so I guess there are still
some transactions.

How resolve the problem?

Thanks in advance!
-- 
View this message in context: 
http://old.nabble.com/sqlite3-is-blocked-by-transaction-when-wanting-to-close-the-sqlite3-*-tp26523551p26523551.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] How to input a double num?

[liubin liu]

Thank you!

I mayn't need the high precision like
"212345678901234567890123456.988290112".



John Crenshaw-2 wrote:
> 
>> May I use sqlite3_bind_double() and sqlite3_prepare_v2() to solve the
>> problem.
> 
> That won't fix it. Your number is too large to fit in any native data
> type. Even the plain math inside your own program won't work right,
> because the precision of the number is limited at the C level, not the
> SQLite or printf level.
> 
> You'll need an arbitrary precision math library, sqlite3_bind_blob(),
> and sqlite3_prepare_v2(). You can't use sqlite3_bind_double() because
> your number is too big for a double.
> 
> FYI, those arbitrary precision libraries are brutes, so brace yourself.
> 
> John
> ___
> 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/How-to-input-a-double-num--tp26105457p26127343.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] How to input a double num?

[liubin liu]

Thanks a lot for every reply.

The num is just random num. The project is on power-measurement. So there
are some big nums with high precision.

May I use sqlite3_bind_double() and sqlite3_prepare_v2() to solve the
problem.




liubin liu wrote:
> 
> Now I use the sqlite3_mprintf() and the "%f" to get the double num. My
> code is below.
> 
> Now there is a num like "212345678901234567890123456.988290112". With the
> way of "sqlite3_mprintf()" and "%f", the num is cut to
> "2123456789012346000.00".
> 
> 
> How to input the num "212345678901234567890123456.988290112"?
> 
> 
> code_
> 
> ...
> char *query_format = "INSERT OR REPLACE INTO pow_value_st (id, valid,
> powervalue_1, powervalue_2) VALUES (%d, %d, %f, %f)";
> char *query_string = NULL;
> query_string = sqlite3_mprintf (query_format, index, tc->valid,
> tc->powervalue[0], tc->powervalue[1]);
> printf ("%s\n", query_string); 
> ...
> 
> 
> 
> PS:
> the columns of powervalue_1 and powervalue_2 are defined as DOUBLE type:
> CREATE TABLE pow_value_st (id INTEGER PRIMARY KEY, valid CHAR(1),
> powervalue_1 DOUBLE, powervalue_2 DOUBLE );
> 

-- 
View this message in context: 
http://www.nabble.com/How-to-input-a-double-num--tp26105457p26123863.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] How to input a double num?

[liubin liu]

Now I use the sqlite3_mprintf() and the "%f" to get the double num. My code
is below.

Now there is a num like "212345678901234567890123456.988290112". With the
way of "sqlite3_mprintf()" and "%f", the num is cut to
"2123456789012346000.00".


How to input the num "212345678901234567890123456.988290112"?


code_

...
char *query_format = "INSERT OR REPLACE INTO pow_value_st (id, valid,
powervalue_1, powervalue_2) VALUES (%d, %d, %f, %f)";
char *query_string = NULL;
query_string = sqlite3_mprintf (query_format, index, tc->valid,
tc->powervalue[0], tc->powervalue[1]);
printf ("%s\n", query_string); 
...

-- 
View this message in context: 
http://www.nabble.com/How-to-input-a-double-num--tp26105457p26105457.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] how to get the records matching some two characters?

[liubin liu]

Thanks!

This is what I need, :)




Jay A. Kreibich-2 wrote:
> 
> On Mon, Aug 31, 2009 at 06:33:08PM -0700, liubin liu scratched on the
> wall:
>> 
>> create table and index：
>> CREATE TABLE data_rt ( id INTEGER, dataid CHAR(4), data CHAR(12),
>> rec_time
>> INTEGER, data_type CHAR(1) );
>> CREATE UNIQUE INDEX i_drt ON data_rt ( id, dataid );
>> 
>> there are data in the table of data_rt：
>> sqlite> SELECT * FROM data_rt;
>> 6|1290|7e22473a|857000|22
>> 7|1291|7e22473a|859000|22
>> 8|1190|7e22473a|861000|22
>> 9|1390|7e22473|862000|22
>> 8|1390|7e22473|861000|22
>> 7|1391|7e22473|86|22
>> 6|1391|7e22473|859000|22
>> 5|1391|7e22473|858000|22
>> 
>> 
>> I want to get the records that the dataid's two end characters is '90'.
>> 
>> How to write the select words?
> 
>   As long as you're sure dataid is a four character string, and not
>   a number, you can use:
> 
> SELECT * FROM data_rt WHERE dataid LIKE '__90';
> 
>   See http://www.sqlite.org/lang_expr.html for specifics.  You might
>   want to use '%90'.
> 
>   This will do a full table scan.
> 
>-j
> 
> -- 
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> 
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
:jumping::jumping:
-- 
View this message in context: 
http://www.nabble.com/how-to-get-the-records-matching-some-two-characters--tp25233067p2526.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] how to get the records matching some two characters?

[liubin liu]

create table and index：
CREATE TABLE data_rt ( id INTEGER, dataid CHAR(4), data CHAR(12), rec_time
INTEGER, data_type CHAR(1) );
CREATE UNIQUE INDEX i_drt ON data_rt ( id, dataid );

there are data in the table of data_rt：
sqlite> SELECT * FROM data_rt;
6|1290|7e22473a|857000|22
7|1291|7e22473a|859000|22
8|1190|7e22473a|861000|22
9|1390|7e22473|862000|22
8|1390|7e22473|861000|22
7|1391|7e22473|86|22
6|1391|7e22473|859000|22
5|1391|7e22473|858000|22


I want to get the records that the dataid's two end characters is '90'.

How to write the select words?

-- 
View this message in context: 
http://www.nabble.com/how-to-get-the-records-matching-some-two-characters--tp25233067p25233067.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] How to create a data of unsigned int?

[liubin liu]

Thanks, :)

But now I think that's ok to use the int type to store the four byte.
Because I could get the value by using forced conversion.

But Do SQLite3 support the unsigned type?

And You know that int64 is too wasting here.



Pavel Ivanov-2 wrote:
> 
> int64 type is okay to store data of type unsigned int. And int64 is
> exactly the type used for all integers in SQLite.
> 
> Pavel
> 
> On Fri, Aug 7, 2009 at 5:53 AM, liubin liu<7101...@sina.com> wrote:
>>
>> I want to use a integer to save the ip address of ipv4. Because they all
>> are
>> 4 bytes.
>>
>> but it is better when the data type is unsigned int.
>>
>> How to create a data of unsigned int?
>> 1334
>> 1335     // tcp->gate_addr[0]是地址的高8位
>> 1336     for ( i=0, m=1; i<4; i++, m*=0x100 )
>> 1337         gateaddr = gateaddr + tcp->gate_addr[3-i] * m;
>> 1338
>>
>> --
>> View this message in context:
>> http://www.nabble.com/How-to-create-a-data-of-unsigned-int--tp24861945p24861945.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
> 
> 

-- 
View this message in context: 
http://www.nabble.com/How-to-create-a-data-of-unsigned-int--tp24861945p24864619.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] How to create a data of unsigned int?

[liubin liu]

I want to use a integer to save the ip address of ipv4. Because they all are
4 bytes.

but it is better when the data type is unsigned int.

How to create a data of unsigned int?
1334
1335 // tcp->gate_addr[0]是地址的高8位
1336 for ( i=0, m=1; i<4; i++, m*=0x100 ) 
1337 gateaddr = gateaddr + tcp->gate_addr[3-i] * m;
1338 

-- 
View this message in context: 
http://www.nabble.com/How-to-create-a-data-of-unsigned-int--tp24861945p24861945.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] two question, 1.)sqlite3's select? 2.)errno and the sqlite3's result code

[liubin liu]

1.)How does sqlite3's select work?
Does it need to seach for all the records?

2.)Is there any relation between errno and the sqlite3's result code?

-- 
View this message in context: 
http://www.nabble.com/two-question%2C-1.%29sqlite3%27s-select--2.%29errno-and-the-sqlite3%27s-result-code-tp24457119p24457119.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] is the sqlite3's C-api used in thread-safety mode ? or non-reentrant?

[liubin liu]

How could I know which SQLite3's api is reentrant?


Pavel Ivanov-2 wrote:
> 
> Why do you re-post your code as if it's another question not related
> to previous one (along with the answer)?
> 
>> does it mean that the sqlite3's C-api isn't reentrant or thread-safety?
> 
> If SQLite is compiled with SQLITE_THREADSAFE = 1 or 2 then API is
> thread-safe (with some limitations when it's 2), if SQLITE_THREADSAFE
> = 0 then API is not thread-safe. But in both cases I have a doubt
> about signal-safety and also API is not re-entrant for sure except for
> some specially designed functions.
> 
> Pavel
> 
> On Thu, Jul 9, 2009 at 3:52 AM, liubin liu<7101...@sina.com> wrote:
>>
>> does it mean that the sqlite3's C-api isn't reentrant or thread-safety?
>>
>>
>> 
>>
>>
>> #include       // for printf()
>> #include      // for signal()
>> #include      // for alarm()
>> #include      // for system()
>> #include     // for pthread_create()
>> #include     // for sqlite3_***
>>
>>
>>
>> sqlite3 *db = NULL;
>>
>> int timer_handle_mark1 = 0;
>> int timer_handle_mark2 = 9;
>>
>>
>>
>> pthread_t trd1;
>>
>> void trd1_task ( void );
>>
>>
>>
>> int sqlite3_helper_create_db (void);
>>
>> int sqlite3_helper_insert_data (void);
>>
>> // psf - prepare, step, finalize
>> int sqlite3_helper_get_data_psf_from_tb1 ( int id );
>>
>> void timer_handler()
>> {
>>    int ret = -1;
>>    int i = 9;
>>
>>    char *query_format2 = "SELECT * FROM ts2 WHERE id=%d;";
>>    char *query_string = NULL;
>>
>>
>>    sqlite3_stmt *p_stmt = NULL;
>>
>>    timer_handle_mark2 = 0;
>>    query_string = sqlite3_mprintf ( query_format2, i%500 );
>>    timer_handle_mark1++;
>>
>>    timer_handle_mark2 = 1;
>>    ret = sqlite3_prepare_v2 ( db, query_string, -1, _stmt, NULL );
>>    timer_handle_mark1++;
>>
>>    timer_handle_mark2 = 2;
>>    ret = sqlite3_step ( p_stmt );
>>    timer_handle_mark1++;
>>
>>    //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 ) );
>>
>>    timer_handle_mark2 = 3;
>>    sqlite3_finalize ( p_stmt );
>>    timer_handle_mark1++;
>>
>>
>>    alarm(1);
>> }
>>
>> int inittimer()
>> {
>>    signal ( SIGALRM, timer_handler );
>>    alarm(1);
>>    return 0;
>> }
>>
>> void trd1_task ( void )
>> {
>>    sleep (30);
>>    printf ( "# IN thread 1, after 30 seconds, timer_handle_mark1: %d,
>> timer_handle_mark2: %d\n", timer_handle_mark1, timer_handle_mark2 );
>>    sleep (50);
>>    printf ( "# IN thread 1, after 50 seconds, timer_handle_mark1: %d,
>> timer_handle_mark2: %d\n", timer_handle_mark1, timer_handle_mark2 );
>> }
>>
>>
>>
>> int main ( void )
>> {
>>    int ret = -1;
>>    int i = 0;
>>
>>    ret = pthread_create ( , 0, (void *)trd1_task, 0 );
>>
>>    ret = sqlite3_open ( "testsignal.db",  );
>>
>>    sqlite3_helper_create_db ();
>>
>>    sqlite3_helper_insert_data ();
>>
>>    ret = inittimer();
>>
>>    for ( i=0; ; i++ )
>>        ret = sqlite3_helper_get_data_psf_from_tb1 ( i );
>>
>>    ret = sqlite3_close ( db );
>>
>>    system ( "sqlite3 testsignal.db \"SELECT COUNT(*) FROM ts1;\"" );
>>
>>    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 db file, SUCCESS!\n" );
>>
>>
>>    return 0;
>> }
>>
>>
>> int sqlite3_helper_insert_data (void)
>> {
>>    int ret = -1;
>>    int i = 0;
>>
>>    sqlite3_stmt *p_stmt = NULL;
>>
>>
>>    c

Re: [sqlite] is the sqlite3's C-api used in thread-safety mode ? or non-reentrant?

[liubin liu]

Thank you very much for your post

Just after I post the last code I know the concept of "reentrant" and
"thread-safety".

And you should notice the wrong usage of printf() in signal handle func. ^_^

So I re-writed the code and re-test. And the problem is still in someplace.
So I re-post the question.

Again, I appreciate your answer, :)



Pavel Ivanov-2 wrote:
> 
> Why do you re-post your code as if it's another question not related
> to previous one (along with the answer)?
> 
>> does it mean that the sqlite3's C-api isn't reentrant or thread-safety?
> 
> If SQLite is compiled with SQLITE_THREADSAFE = 1 or 2 then API is
> thread-safe (with some limitations when it's 2), if SQLITE_THREADSAFE
> = 0 then API is not thread-safe. But in both cases I have a doubt
> about signal-safety and also API is not re-entrant for sure except for
> some specially designed functions.
> 
> Pavel
> 
> On Thu, Jul 9, 2009 at 3:52 AM, liubin liu<7101...@sina.com> wrote:
>>
>> does it mean that the sqlite3's C-api isn't reentrant or thread-safety?
>>
>>
>> 
>>
>>
>> #include       // for printf()
>> #include      // for signal()
>> #include      // for alarm()
>> #include      // for system()
>> #include     // for pthread_create()
>> #include     // for sqlite3_***
>>
>>
>>
>> sqlite3 *db = NULL;
>>
>> int timer_handle_mark1 = 0;
>> int timer_handle_mark2 = 9;
>>
>>
>>
>> pthread_t trd1;
>>
>> void trd1_task ( void );
>>
>>
>>
>> int sqlite3_helper_create_db (void);
>>
>> int sqlite3_helper_insert_data (void);
>>
>> // psf - prepare, step, finalize
>> int sqlite3_helper_get_data_psf_from_tb1 ( int id );
>>
>> void timer_handler()
>> {
>>    int ret = -1;
>>    int i = 9;
>>
>>    char *query_format2 = "SELECT * FROM ts2 WHERE id=%d;";
>>    char *query_string = NULL;
>>
>>
>>    sqlite3_stmt *p_stmt = NULL;
>>
>>    timer_handle_mark2 = 0;
>>    query_string = sqlite3_mprintf ( query_format2, i%500 );
>>    timer_handle_mark1++;
>>
>>    timer_handle_mark2 = 1;
>>    ret = sqlite3_prepare_v2 ( db, query_string, -1, _stmt, NULL );
>>    timer_handle_mark1++;
>>
>>    timer_handle_mark2 = 2;
>>    ret = sqlite3_step ( p_stmt );
>>    timer_handle_mark1++;
>>
>>    //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 ) );
>>
>>    timer_handle_mark2 = 3;
>>    sqlite3_finalize ( p_stmt );
>>    timer_handle_mark1++;
>>
>>
>>    alarm(1);
>> }
>>
>> int inittimer()
>> {
>>    signal ( SIGALRM, timer_handler );
>>    alarm(1);
>>    return 0;
>> }
>>
>> void trd1_task ( void )
>> {
>>    sleep (30);
>>    printf ( "# IN thread 1, after 30 seconds, timer_handle_mark1: %d,
>> timer_handle_mark2: %d\n", timer_handle_mark1, timer_handle_mark2 );
>>    sleep (50);
>>    printf ( "# IN thread 1, after 50 seconds, timer_handle_mark1: %d,
>> timer_handle_mark2: %d\n", timer_handle_mark1, timer_handle_mark2 );
>> }
>>
>>
>>
>> int main ( void )
>> {
>>    int ret = -1;
>>    int i = 0;
>>
>>    ret = pthread_create ( , 0, (void *)trd1_task, 0 );
>>
>>    ret = sqlite3_open ( "testsignal.db",  );
>>
>>    sqlite3_helper_create_db ();
>>
>>    sqlite3_helper_insert_data ();
>>
>>    ret = inittimer();
>>
>>    for ( i=0; ; i++ )
>>        ret = sqlite3_helper_get_data_psf_from_tb1 ( i );
>>
>>    ret = sqlite3_close ( db );
>>
>>    system ( "sqlite3 testsignal.db \"SELECT COUNT(*) FROM ts1;\"" );
>>
>>    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 )
>> 

[sqlite] is the sqlite3's C-api used in thread-safety mode ? or non-reentrant?

[liubin liu]

does it mean that the sqlite3's C-api isn't reentrant or thread-safety?





#include   // for printf()
#include  // for signal()
#include  // for alarm()
#include  // for system()
#include // for pthread_create()
#include // for sqlite3_***



sqlite3 *db = NULL;

int timer_handle_mark1 = 0;
int timer_handle_mark2 = 9;



pthread_t trd1;

void trd1_task ( void );



int sqlite3_helper_create_db (void);

int sqlite3_helper_insert_data (void);

// psf - prepare, step, finalize
int sqlite3_helper_get_data_psf_from_tb1 ( int id );

void timer_handler()
{
int ret = -1;
int i = 9;

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


sqlite3_stmt *p_stmt = NULL;

timer_handle_mark2 = 0;
query_string = sqlite3_mprintf ( query_format2, i%500 );
timer_handle_mark1++;

timer_handle_mark2 = 1;
ret = sqlite3_prepare_v2 ( db, query_string, -1, _stmt, NULL );
timer_handle_mark1++;

timer_handle_mark2 = 2;
ret = sqlite3_step ( p_stmt );
timer_handle_mark1++;

//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 ) );

timer_handle_mark2 = 3;
sqlite3_finalize ( p_stmt );
timer_handle_mark1++;


alarm(1);
}

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

void trd1_task ( void )
{
sleep (30);
printf ( "# IN thread 1, after 30 seconds, timer_handle_mark1: %d,
timer_handle_mark2: %d\n", timer_handle_mark1, timer_handle_mark2 );
sleep (50);
printf ( "# IN thread 1, after 50 seconds, timer_handle_mark1: %d,
timer_handle_mark2: %d\n", timer_handle_mark1, timer_handle_mark2 );
}



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

ret = pthread_create ( , 0, (void *)trd1_task, 0 );

ret = sqlite3_open ( "testsignal.db",  );

sqlite3_helper_create_db ();

sqlite3_helper_insert_data ();

ret = inittimer();

for ( i=0; ; i++ )
ret = sqlite3_helper_get_data_psf_from_tb1 ( i );

ret = sqlite3_close ( db );

system ( "sqlite3 testsignal.db \"SELECT COUNT(*) FROM ts1;\"" );

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 db file, SUCCESS!\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, _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, _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 data, SUCCESS!\n" );

return 0;
}


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


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

char **resultp = NULL;
int row = 0;
int column = 0;
char *errmsg = NULL;
char **tmp_str = NULL;

query_string = sqlite3_mprintf ( query_format1, id%500 );
ret = sqlite3_get_table ( db, query_string, , , ,
 );
if ( SQLITE_OK == ret )
{
//printf ( "sqlite3_get_table() success!\n" );
tmp_str = resultp + column;
printf ( "# IN main(), id: %d, length: %d\n", atoi(tmp_str[0]),
atoi(tmp_str[1]) );
}
sqlite3_free ( query_string );
sqlite3_free_table ( resultp );

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, 

[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   // for printf()
#include  // for signal()
#include  // for alarm()
#include  // for system()
#include // 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, _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",  );

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

// insert datas into the tables(ts1)
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, _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, _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, _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

Re: [sqlite] why is the data file locked after after using transaction?

[liubin liu]

You are right

I have mend the fault, ^_^

Thank You a lot!


Igor Tandetnik wrote:
> 
> liubin liu wrote:
>> why is the data file locked after after using transaction?
>>
>> the last result of printf() is:
>> # IN END, ret = 5
>>
>> It means to the database file is locked?
>> why does it happen after using transaction although using
>> sqlite3_finalize()?
> 
> You call sqlite3_prepare_v2 n+2 times, but sqlite3_finalize only once. 
> You leak statement handles like there's no tomorrow.
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/why-is-the-data-file-locked-after-after-using-transaction--tp24085034p24085861.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] why is the data file locked after after using transaction?

[liubin liu]

why is the data file locked after after using transaction?

the last result of printf() is:
# IN END, ret = 5

It means to the database file is locked?
why does it happen after using transaction although using
sqlite3_finalize()?


PS:
if shielding the codes relating with the transaction, all the result of
printf() is correct.


___

// basic9.c

#include  // for sleep();
#include  // for system();
#include   // for printf();

#include 

int main ( void )
{
int ret = 0;
int i = 0;
int n = 10;

sqlite3 *db = NULL;
sqlite3_stmt *p_stmt = NULL;

char *sql_ct = "CREATE TABLE table1 (id INTEGER, m INTEGER, con
VARCHAR(512))";
char *sql_in = "INSERT INTO table1 VALUES (%d, %d, %Q)";
char *sql = NULL;

ret = sqlite3_open ( "test.db",  );
printf ( "# AFTER sqlite3_open, ret = %d\n", ret );

ret = sqlite3_exec ( db, sql_ct, NULL, NULL, NULL );
printf ( "# AFTER sqlite3_exec, create table,   ret = %d\n", ret );

// system ( "free" );
// 采用事务;
ret = sqlite3_prepare_v2 ( db, "BEGIN", -1, _stmt, 0 );
ret = sqlite3_step ( p_stmt );

// 创建事务;
for ( i=0; i

[sqlite] why is the data file locked after writing?

[liubin liu]

why is the data file locked after writing?

the last printf() result is:
# IN END, ret = 5

according to the "Result Codes":
#define SQLITE_BUSY 5   /* The database file is locked */

It means to the database file is locked? why does it happen after writing?


_

// basic10.c

#include 
#include 

#include 


int main ( void )
{
int ret = 0;

sqlite3 *db = NULL;
sqlite3_stmt *p_stmt = NULL;

char *sql_ct = "CREATE TABLE table1 (id INTEGER, m INTEGER, n
VARCHAR(32), t CHAR(1), con VARCHAR(512))";
char *sql_in = "INSERT INTO table1 VALUES (%d, %d, %Q, %d, %Q)";
char *sql = NULL;

ret = sqlite3_open ( "test.db",  );
printf ( "# AFTER sqlite3_open, ret = %d\n", ret );

ret = sqlite3_exec ( db, sql_ct, NULL, NULL, NULL );
printf ( "# AFTER sqlite3_exec, create table,   ret = %d\n", ret );

sql = sqlite3_mprintf ( sql_in, 0, 0, "goodc", 0, "test -
varcharvarcharvarchar" );
ret = sqlite3_prepare_v2 ( db, sql, -1, _stmt, NULL );
printf ( "# AFTER sqlite3_prepare_v2,   ret = %d\n", ret );
ret = sqlite3_step ( p_stmt );
printf ( "# AFTER sqlite3_step, ret = %d\n", ret );
sqlite3_free ( sql );

ret = sqlite3_close ( db );
printf ( "# IN END, ret = %d\n", ret );
system ( "rm test.db" );

return 0;
}

_

[...@lb basic]$ make basic10
[...@lb basic]$ ./basic10 
# AFTER sqlite3_open, ret = 0
# AFTER sqlite3_exec, create table,   ret = 0
# AFTER sqlite3_prepare_v2,   ret = 0
# AFTER sqlite3_step, ret = 101
# IN END, ret = 5

-- 
View this message in context: 
http://www.nabble.com/why-is-the-data-file-locked-after-writing--tp24071633p24071633.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] how to compose the sql sentence?

[liubin liu]

Thank you very much!

you told me how to do. but I missed the "... defined as  UNIQUE ...".

Actually my question is just solved by two step:

First - CREATE UNIQUE INDEX i_recdata ON rec_data (num, di, time1);

Second - INSERT OR REPLACE INTO rec_data (num, di, data, time1, time2,
format) VALUES (12, '1290', '732e4a39', 8323000, 8323255, 22); 




Simon Slavin-2 wrote:
> 
> 
> On 3 Jun 2009, at 7:05am, liubin liu wrote:
> 
>> the first step is to tell if there is the data in the table.
>> if the answer is not, I want to insert a row of data into the table
>> if the answer is yes, I need to update the row of data acccording to  
>> the
>> data inputting from me.
> 
> INSERT OR REPLACE INTO table (columns) VALUES (values)
> 
> This will use the columns and indices you have already defined as  
> UNIQUE to decide whether it should INSERT a new row or REPLACE an  
> existing one.  So take care in creating UNIQUE columns or a UNIQUE  
> index that does what you want.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/how-to-compose-the-sqlite---if-exists-%28select-...%29---update-...--else---insert-into...-tp23845882p23883819.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] how to compose the sql sentence?

[liubin liu]

and I think of another question:
how to know the return value of a command IN the command-line mode of
sqlite3?



Martin Engelschalk wrote:
> 
> Hi,
> 
> First, you have to declare the index as unique:
> 
> CREATE UNIQUE INDEX i_data ON data (num, di, time1);
> 
> or - depending on your database design, declare a primary key with these 
> three fields.
> 
> Second, the error message says it all: You supplied three column - 
> names, but 6 values.
> 
> Martin
> 
> liubin liu schrieb:
>> Thank you a lot!
>>
>>
>> I created a table:
>> CREATE TABLE data ( num INTEGER, di CHAR(4), data CHAR(12), time1
>> INTEGER,
>> time2 INTEGER, format CHAR(1) );
>>
>> and create a index:
>> CREATE INDEX i_data ON data (num, di, time1);
>>
>> I want to do:
>> first tell whether there is a record in the table "data" according to the
>> index "i_data".
>> to update the record if there is a record;
>> to insert the record if there isn't any record.
>>
>>
>>
>> when I run the sql:
>> INSERT OR REPLACE INTO data (num, di, time1) VALUES (12, '1290',
>> '732e4a39', 8323000, 8323255, 22);
>>
>> the sqlite3 report a error:
>> SQL error: 6 values for 3 columns
>>
>> Does It mean the method isn't the right way?
>>
>>
>>
>>
>> Simon Slavin-2 wrote:
>>   
>>> On 3 Jun 2009, at 7:05am, liubin liu wrote:
>>>
>>> 
>>>> the first step is to tell if there is the data in the table.
>>>> if the answer is not, I want to insert a row of data into the table
>>>> if the answer is yes, I need to update the row of data acccording to  
>>>> the
>>>> data inputting from me.
>>>>   
>>> INSERT OR REPLACE INTO table (columns) VALUES (values)
>>>
>>> This will use the columns and indices you have already defined as  
>>> UNIQUE to decide whether it should INSERT a new row or REPLACE an  
>>> existing one.  So take care in creating UNIQUE columns or a UNIQUE  
>>> index that does what you want.
>>>
>>> Simon.
>>> ___
>>> 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
> 
> 

-- 
View this message in context: 
http://www.nabble.com/how-to-compose-the-sqlite---if-exists-%28select-...%29---update-...--else---insert-into...-tp23845882p23865786.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] how to compose the sql sentence?

[liubin liu]

Yeah!

Thank you very very much!

this way could get my goal. but I am afraid the sql will cause some sqlite3
err?



Martin Engelschalk wrote:
> 
> Hi,
> 
> First, you have to declare the index as unique:
> 
> CREATE UNIQUE INDEX i_data ON data (num, di, time1);
> 
> or - depending on your database design, declare a primary key with these 
> three fields.
> 
> Second, the error message says it all: You supplied three column - 
> names, but 6 values.
> 
> Martin
> 
> liubin liu schrieb:
>> Thank you a lot!
>>
>>
>> I created a table:
>> CREATE TABLE data ( num INTEGER, di CHAR(4), data CHAR(12), time1
>> INTEGER,
>> time2 INTEGER, format CHAR(1) );
>>
>> and create a index:
>> CREATE INDEX i_data ON data (num, di, time1);
>>
>> I want to do:
>> first tell whether there is a record in the table "data" according to the
>> index "i_data".
>> to update the record if there is a record;
>> to insert the record if there isn't any record.
>>
>>
>>
>> when I run the sql:
>> INSERT OR REPLACE INTO data (num, di, time1) VALUES (12, '1290',
>> '732e4a39', 8323000, 8323255, 22);
>>
>> the sqlite3 report a error:
>> SQL error: 6 values for 3 columns
>>
>> Does It mean the method isn't the right way?
>>
>>
>>
>>
>> Simon Slavin-2 wrote:
>>   
>>> On 3 Jun 2009, at 7:05am, liubin liu wrote:
>>>
>>> 
>>>> the first step is to tell if there is the data in the table.
>>>> if the answer is not, I want to insert a row of data into the table
>>>> if the answer is yes, I need to update the row of data acccording to  
>>>> the
>>>> data inputting from me.
>>>>   
>>> INSERT OR REPLACE INTO table (columns) VALUES (values)
>>>
>>> This will use the columns and indices you have already defined as  
>>> UNIQUE to decide whether it should INSERT a new row or REPLACE an  
>>> existing one.  So take care in creating UNIQUE columns or a UNIQUE  
>>> index that does what you want.
>>>
>>> Simon.
>>> ___
>>> 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
> 
> 

-- 
View this message in context: 
http://www.nabble.com/how-to-compose-the-sqlite---if-exists-%28select-...%29---update-...--else---insert-into...-tp23845882p23865605.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] how to compose the sql sentence?

[liubin liu]

column name and table name is my fault, :)

the sql "INSERT OR REPLACE INTO table () VALUES ()" is equal with "INSERT
INTO table VALUES()"

so It can't achieve my goal.



Harold Wood  Meyuni Gani wrote:
> 
> Well you have a column named data and a table named data, but the biggest
> issue is you only supplied 3 column names but in the values list you have
> 6 values.
>  
> 
> 
> --- On Thu, 6/4/09, liubin liu <7101...@sina.com> wrote:
> 
> 
> From: liubin liu <7101...@sina.com>
> Subject: Re: [sqlite] how to compose the sql sentence?
> To: sqlite-users@sqlite.org
> Date: Thursday, June 4, 2009, 2:39 AM
> 
> 
> 
> Thank you a lot!
> 
> 
> I created a table:
> CREATE TABLE data ( num INTEGER, di CHAR(4), data CHAR(12), time1 INTEGER,
> time2 INTEGER, format CHAR(1) );
> 
> and create a index:
> CREATE INDEX i_data ON data (num, di, time1);
> 
> I want to do:
> first tell whether there is a record in the table "data" according to the
> index "i_data".
> to update the record if there is a record;
> to insert the record if there isn't any record.
> 
> 
> 
> when I run the sql:
> INSERT OR REPLACE INTO data (num, di, time1) VALUES (12, '1290',
> '732e4a39', 8323000, 8323255, 22);
> 
> the sqlite3 report a error:
> SQL error: 6 values for 3 columns
> 
> Does It mean the method isn't the right way?
> 
> 
> 
> 
> Simon Slavin-2 wrote:
>> 
>> 
>> On 3 Jun 2009, at 7:05am, liubin liu wrote:
>> 
>>> the first step is to tell if there is the data in the table.
>>> if the answer is not, I want to insert a row of data into the table
>>> if the answer is yes, I need to update the row of data acccording to  
>>> the
>>> data inputting from me.
>> 
>> INSERT OR REPLACE INTO table (columns) VALUES (values)
>> 
>> This will use the columns and indices you have already defined as  
>> UNIQUE to decide whether it should INSERT a new row or REPLACE an  
>> existing one.  So take care in creating UNIQUE columns or a UNIQUE  
>> index that does what you want.
>> 
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
> 
> -- 
> View this message in context:
> http://www.nabble.com/how-to-compose-the-sqlite---if-exists-%28select-...%29---update-...--else---insert-into...-tp23845882p23864558.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
> 
> 

-- 
View this message in context: 
http://www.nabble.com/how-to-compose-the-sqlite---if-exists-%28select-...%29---update-...--else---insert-into...-tp23845882p23865368.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] how to compose the sql sentence?

[liubin liu]

Thank you a lot!


I created a table:
CREATE TABLE data ( num INTEGER, di CHAR(4), data CHAR(12), time1 INTEGER,
time2 INTEGER, format CHAR(1) );

and create a index:
CREATE INDEX i_data ON data (num, di, time1);

I want to do:
first tell whether there is a record in the table "data" according to the
index "i_data".
to update the record if there is a record;
to insert the record if there isn't any record.



when I run the sql:
INSERT OR REPLACE INTO data (num, di, time1) VALUES (12, '1290',
'732e4a39', 8323000, 8323255, 22);

the sqlite3 report a error:
SQL error: 6 values for 3 columns

Does It mean the method isn't the right way?




Simon Slavin-2 wrote:
> 
> 
> On 3 Jun 2009, at 7:05am, liubin liu wrote:
> 
>> the first step is to tell if there is the data in the table.
>> if the answer is not, I want to insert a row of data into the table
>> if the answer is yes, I need to update the row of data acccording to  
>> the
>> data inputting from me.
> 
> INSERT OR REPLACE INTO table (columns) VALUES (values)
> 
> This will use the columns and indices you have already defined as  
> UNIQUE to decide whether it should INSERT a new row or REPLACE an  
> existing one.  So take care in creating UNIQUE columns or a UNIQUE  
> index that does what you want.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/how-to-compose-the-sqlite---if-exists-%28select-...%29---update-...--else---insert-into...-tp23845882p23864558.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] how to compose the sql sentence?

[liubin liu]

the first step is to tell if there is the data in the table.
if the answer is not, I want to insert a row of data into the table
if the answer is yes, I need to update the row of data acccording to the
data inputting from me.

-- 
View this message in context: 
http://www.nabble.com/how-to-compose-the-sql-sentence--tp23845882p23845882.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] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[liubin liu]

Thanks!

It sounds pretty good. But I don't understand it exactly. Could you write
down the sample codes?



Matthew L. Creech wrote:
> 
> On Mon, Apr 27, 2009 at 10:25 PM, liubin liu <7101...@sina.com> wrote:
>>
>> thanks
>>
>> I'm not sure of the real reason.
>> It's true that the speed of inserting with transaction is very fast. But
>> my
>> project is using SQLite mainly in selecting something. I don't know how
>> to
>> use transaction in the situation. May some friends give me some codes on
>> that?
>>
> As someone else mentioned, you probably don't want to open/close the
> database every time you do a single SELECT.  In the example you're
> doing 100,000 iterations of open/exec/close, which will give pretty
> terrible performance.
> 
> Instead, try creating an initialization call, which opens the
> database, prepare()s your SELECT stmt (with a '?' placeholder for the
> ID in this example), and returns a handle to the caller.  The caller
> can then loop 100,000 times calling getdata() with this handle.
> getdata() can just do sqlite3_bind_XXX() followed by sqlite3_step()
> (and sqlite3_reset()), which will be _much_ faster than
> sqlite3_exec().
> 
> -- 
> Matthew L. Creech
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/speed-test%2C-Sqlite3-vs-BerkeleyDB%2C-I%27m-confused-tp23209208p23271593.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] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[liubin liu]

thanks

I'm not sure of the real reason.
It's true that the speed of inserting with transaction is very fast. But my
project is using SQLite mainly in selecting something. I don't know how to
use transaction in the situation. May some friends give me some codes on
that?

My application needs some funcs that serve as the database API, so I must
encapsulate SQLite func in my func. It includes the basic routine like this:
1.) sqlite3_open();
2.) sqlite3_get_table(),  sqlite3_exec()+callback, 
sqlite3_prepare_v2()+sqlite3_step()+sqlite3_column*+sqlite3_finalize()
3.) sqlite3_close();

I found that every routine running in the object board (Atmel 9260 (200MHz)
+ 128M RAM + ...) spend 20 millisecond. It's too slow...

And I have tried some PRAGMA settings for example: cache_size/page_size, but
the effect is very few.



John Stanton-3 wrote:
> 
> P Kishor wrote:
>> On Fri, Apr 24, 2009 at 5:27 AM, John Stanton 
>> wrote:
>>   
>>> Sqlte provides COMMIT and ROLLBACK unlike Berkeley.  If you can get by
>>> without the advanced features of Sqlite, then use Berkely and take
>>> advantage of its simplicity and faster execution.
>>> 
>>
>> BDB does support transactions...
>>
>> http://www.oracle.com/technology/documentation/berkeley-db/xml/gsg_xml_txn/cxx/usingtxns.html
>>
>> In fact, BDB acts as the (a) transactional layer in MySQL (the
>> alternative is InnoDB).
>>
>> Of course, other advantages of SQLite still apply. A key-value
>> metaphor can only be pushed so far.
>>
>>   
> Guess what make BDB run faster - no transactions. If you are not using
> transactions BDB mght be for you. Personally as a long time user of BDB
> in various ways I have a very poor opinion of it. It has a very
> different application to Sqlite.
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/speed-test%2C-Sqlite3-vs-BerkeleyDB%2C-I%27m-confused-tp23209208p23269006.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] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[liubin liu]

Thanks

I amend the code according to your message.


Marcus Grimm wrote:
> 
> in your test code I see that you open the sqlite DB
> each time you perform the single query.
> I guess it would be fair to open the sqlite DB only once
> outside your test loop.
> 2nd: You query against ID without an index, try either
> define an index on ID or change your table definition
> into "ID INTEGER PRIMARY KEY" which will have an index
> on ID automatically, I think..
> 
> Marcus
> 
>>
>> our project is using sqlite3, but found that the speed is too slow.
>> I just tested the BerkeleyDB, and found that the speed is very fast. But
>> I
>> knew the sqlite3 is fast enough. And so I'm confused.
>> I may be using sqlite3 in wrong way?
>>
>> anyway, next is my test code. I'm glad to receive your message.
>>
>> __
>>
>>
>> // http://www.ibm.com/developerworks/cn/linux/l-embdb/
>>
>> //head///
>> #include 
>> #include  // for system
>> #include  // for memset strcpy
>> #include  // for time
>>
>> #include  // for Sqlite3
>> #include  // for Berkeley DB
>>
>>
>>
>>   macro and struct
>> /
>> #define DB_FILE_SQLITE "test_sqlite_0.1.db"
>> #define DB_FILE_BDB "test_bdb_0.1.db"
>>
>> struct customer
>> {
>>  int c_id;
>>  char name[10];
>>  char address[20];
>>  int age;
>> };
>>
>>
>>
>>
>>
>>
>> //   global variable
>> ///
>>
>> sqlite3 *db = NULL;
>>
>> int ret = -1; // 各函数返回值
>>
>>
>>
>>
>>
>>
>>
>> //   func proto
>> ///
>>
>> void way01(); // 打开、关闭的影响
>>
>>
>>
>> / sqlite3 //
>>
>> int sqlite_createtb(sqlite3 *db);
>> int sqlite_insertdb(sqlite3 *db);
>>
>> int getdata_sqlite ( sqlite3 *db, struct customer *tb1 );
>>
>>
>>
>> /// berkeley db 
>>
>> int bdb_createdb(); // 包含了插入数据
>>
>> void print_error(int r);
>> void init_dbt( DBT *key, DBT *data );
>>
>>
>>
>>
>>
>>
>> /// code
>> ///
>>
>> int main ( void )
>> {
>>  int c = 0;
>>
>>  system ( "rm -rf test_0.1.db" );
>>  ret = sqlite3_open ( DB_FILE_SQLITE,  );
>>  ret = sqlite_createtb(db);
>>  ret = sqlite_insertdb(db);
>>  sqlite3_close (db);
>>
>>  printf ( "Sqlite3 / Berkeley DB, 创建数据库 + 插入数据 ...
>> 完成\n" );
>>
>>  printf ( "/\n" );
>>  printf ( "1 : 查询性能比较 - Berkeley DB 与 Sqlite3 ///\n" );
>>
>>  while ( (c=getchar()) != 'q' )
>>  {
>>  switch (c)
>>  {
>>  case '1':
>>  way01();
>>  break;
>>  default:
>>  break;
>>  }
>>  }
>>
>>  system ( "rm -rf test_sqlite_0.1.db" );
>>  system ( "rm -rf test_bdb_0.1.db" );
>>
>>  return 0;
>> }
>>
>> ///
>> // 查询性能比较 - Berkeley DB 与 Sqlite3
>> void way01()
>> {
>>  time_t tick1, tick2;
>>
>>  int i = 0;
>>  int num = 1000*100;
>>
>>  struct customer tb_data;
>>
>>  ///
>>  time (  );
>>  for ( i=0; i>  {
>>  ret = sqlite3_open ( DB_FILE_SQLITE,  );
>>  ret = getdata_sqlite ( db, _data );
>>  sqlite3_close (db);
>>  }
>>  time (  );
>>  printf("Sqlite3 : 打开、关闭并操作数据库文件 %d 次,
>> 时间为: %4ld s\n", num, tick2 -
>> tick1 );
>>
>>  ///
>>  bdb_createdb();
>> }
>>
>>
>>
>>
>>
>>
>>
>> ///
>> void *callback(void *para, int col, char **value, char **colname )
>> {
>> //   int i;
>> //   for(i=0; i> //   printf("%s, ", (value[i] ? value[i] : "NULL") );
>> //   }
>> //   printf("col = %d\n", col);
>>  return (void *) 0;
>> }
>> int getdata_sqlite ( sqlite3 *db, struct customer *tb1 )
>> {
>>  char *sql = "SELECT * FROM table1 WHERE id=500;";
>>  ret = sqlite3_exec ( db, sql, (void *)callback, NULL, NULL );
>>  return 0;
>> }
>>
>> ///
>> int sqlite_createtb( sqlite3 *db )
>> {
>>  char *sql1 = "CREATE TABLE table1 (id INTEGER, name VARCHAR(10), address
>> VARCHAR(20), age INTEGER)";
>>  ret = sqlite3_exec ( db, sql1, NULL, NULL, NULL );
>>  return 0;
>> }
>> int sqlite_insertdb(sqlite3 *db)
>> {
>>  time_t tick1, tick2;
>>  int i = 0;
>>  int num = 1000;
>>
>>  char *qf = "INSERT INTO table1 VALUES (%d, %Q, %Q, %d)";
>>  char 

[sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[liubin liu]

our project is using sqlite3, but found that the speed is too slow.
I just tested the BerkeleyDB, and found that the speed is very fast. But I
knew the sqlite3 is fast enough. And so I'm confused.
I may be using sqlite3 in wrong way?

anyway, next is my test code. I'm glad to receive your message.

__


// http://www.ibm.com/developerworks/cn/linux/l-embdb/

//head///
#include 
#include  // for system
#include  // for memset strcpy
#include  // for time

#include  // for Sqlite3
#include  // for Berkeley DB



  macro and struct 
/
#define DB_FILE_SQLITE "test_sqlite_0.1.db"
#define DB_FILE_BDB "test_bdb_0.1.db"

struct customer
{
int c_id;
char name[10];
char address[20];
int age;
};






//   global variable  
///

sqlite3 *db = NULL;

int ret = -1; // 各函数返回值







//   func proto  
///

void way01(); // 打开、关闭的影响



/ sqlite3 //

int sqlite_createtb(sqlite3 *db);
int sqlite_insertdb(sqlite3 *db);

int getdata_sqlite ( sqlite3 *db, struct customer *tb1 );



/// berkeley db 

int bdb_createdb(); // 包含了插入数据

void print_error(int r);
void init_dbt( DBT *key, DBT *data );






/// code///

int main ( void )
{
int c = 0;

system ( "rm -rf test_0.1.db" );
ret = sqlite3_open ( DB_FILE_SQLITE,  );
ret = sqlite_createtb(db);
ret = sqlite_insertdb(db);
sqlite3_close (db);

printf ( "Sqlite3 / Berkeley DB, 创建数据库 + 插入数据 ... 完成\n" );

printf ( "/\n" );
printf ( "1 : 查询性能比较 - Berkeley DB 与 Sqlite3 ///\n" );

while ( (c=getchar()) != 'q' )
{
switch (c)
{
case '1':
way01();
break;
default:
break;
}
}

system ( "rm -rf test_sqlite_0.1.db" );
system ( "rm -rf test_bdb_0.1.db" );

return 0;
}

///
// 查询性能比较 - Berkeley DB 与 Sqlite3
void way01()
{
time_t tick1, tick2;

int i = 0;
int num = 1000*100;

struct customer tb_data;

///
time (  );
for ( i=0; iopen(dbp, NULL, DB_FILE_BDB, NULL, DB_BTREE, DB_CREATE, 
0664);

time (  );
 

Re: [sqlite] which func could get the number of rows

[liubin liu]

Yes, sqlite3_get_table() is good to handle the mission. and I am always using
it.

but I found that the prefomance was a little weak. and so I want to get some
other API to handle the same mission.



Kees Nuyt wrote:
> 
> On Sat, 7 Mar 2009 01:09:28 -0800 (PST), liubin liu
> <7101...@sina.com> wrote:
> 
>>I'm very happy that my question have triggerred the wiki-FAQ's question.
>>
>>but if the next version could solve(settle?) the question,
>>many guys will be happy, :)
> 
> In my opinion, there is no problem, so there is nothing to
> solve.
> 
> By the way, there is new code in the works which will
> improve the performance of COUNT(*) in some cases.
> See the timeline:
> http://www.sqlite.org/cvstrac/timeline
> 2009-Feb-24 and 2009-Feb-25
> 
>>and now, does it mean that we have to use link-list struct to write such
>>kind of codes when using "sqlite3_prepare_v2() + sqlite3_step() +
>>sqlite3_column_*()"?
> 
> Perhaps the sqlite3_get_table() API is what you are looking
> for? http://www.sqlite.org/c3ref/free_table.html
> 
>>
>>Kees Nuyt wrote:
>>> 
>>> On Fri, 6 Mar 2009 02:15:10 -0800 (PST), liubin liu
>>> <7101...@sina.com> wrote:
>>> 
>>>>which func could get the number of rows?
>>> 
>>> There is no function to retrieve the number of rows in a
>>> result set. SQLite doesn't know the number in advance, but
>>> returns row by row while iterating through the tables. The
>>> application can increment a row counter as needed at every
>>> successful sqlite3_step() .
>>> 
>>> Some wrappers are able to collect all rows in a resultset in
>>> a in-memory table, so they can return the number of rows.
>>> 
>>> You can always get the number of rows that a certain SELECT
>>> statement would return at the cost of some performance:
>>> 
>>>BEGIN IMMEDIATE TRANSACTION;
>>>SELECT COUNT(*) FROM x WHERE y;
>>>SELECT a,b,c FROM x WHERE y;
>>>ROLLBACK TRANSACTION;
>>> 
>>> You have to wrap this in a transaction to prevent other
>>> connections from inserting / deleting rows between the two
>>> SELECT statements.
>>>  
>>> http://www.sqlite.org/lang_transaction.html
>>> 
>>> I hope this helps and I added it to the wiki FAQ:
>>> 
>>> http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq
> -- 
>   (  Kees Nuyt
>   )
> c[_]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/which-func-could-get-the-number-of-rows-tp22369246p22407568.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] Are there distillate posts in the sqlite3 board?

[liubin liu]

Yes, I'm looking for the digest articles.

and appreciate you for you tell me that, :)


Ribeiro, Glauber wrote:
> 
> Are you looking perhaps for digest mode? Yes, there is a digest mode for
> the SQLite email lists, it's one of the options you can set up.
> 
> Or are you thinking of posts that were made under the influence of
> distillates? :)
> 
> g 
> 
> -Original Message-
> From: liubin liu [mailto:7101...@sina.com] 
> Sent: Friday, March 06, 2009 2:39 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Are there distillate posts in the sqlite3 board?
> 
> 
> are there any distillate posts in the sqlite3 board ?
> 
> 
> -- 
> View this message in context:
> http://www.nabble.com/Are-there-distillate-posts-in-the-sqlite3-board--t
> p22368215p22368215.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
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Are-there-distillate-posts-in-the-sqlite3-board--tp22368215p22386161.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] which func could get the number of rows

[liubin liu]

I'm very happy that my question have triggerred the wiki-FAQ's question.

but if the next version could solve(settle?) the question, many guys will be
happy, :)

and now, does it mean that we have to use link-list struct to write such
kind of codes?


Kees Nuyt wrote:
> 
> On Fri, 6 Mar 2009 02:15:10 -0800 (PST), liubin liu
> <7101...@sina.com> wrote:
> 
>>which func could get the number of rows?
> 
> There is no function to retrieve the number of rows in a
> result set. SQLite doesn't know the number in advance, but
> returns row by row while iterating through the tables. The
> application can increment a row counter as needed at every
> successful sqlite3_step() .
> 
> Some wrappers are able to collect all rows in a resultset in
> a in-memory table, so they can return the number of rows.
> 
> You can always get the number of rows that a certain SELECT
> statement would return at the cost of some performance:
> 
>BEGIN IMMEDIATE TRANSACTION;
>SELECT COUNT(*) FROM x WHERE y;
>SELECT a,b,c FROM x WHERE y;
>ROLLBACK TRANSACTION;
> 
> You have to wrap this in a transaction to prevent other
> connections from inserting / deleting rows between the two
> SELECT statements.
>  
> http://www.sqlite.org/lang_transaction.html
> 
> I hope this helps and I added it to the wiki FAQ:
> 
> http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq
> -- 
>   (  Kees Nuyt
>   )
> c[_]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/which-func-could-get-the-number-of-rows-tp22369246p22385592.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] which func could get the number of row

[liubin liu]

which func could get the number of row?
-- 
View this message in context: 
http://www.nabble.com/which-func-could-get-the-number-of-row-tp22369246p22369246.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] Are there distillate posts in the sqlite3 board?

[liubin liu]

are there any distillate posts in the sqlite3 board ?


-- 
View this message in context: 
http://www.nabble.com/Are-there-distillate-posts-in-the-sqlite3-board--tp22368215p22368215.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] manual with sample C programs

[liubin liu]

there are many errors in the codes.

but still worthwhile to go through them carefully.



liubin liu wrote:
> 
> This's very good!
> 
> I just download the codes from here:
> http://www.apress.com/book/downloadfile/2847
> 
> 
> dcharno wrote:
>> 
>>> Could you tell me where can I find such documentation, or can you
>>> recommend some books.
>> 
>> "The Definitive Guide to SQLite" by Michael Owens explains the SQLite 
>> API in detail and provides a number of samples in C.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/manual-with-sample-C-programs-tp22215461p22282476.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] manual with sample C programs

[liubin liu]

This's very good!

I just download the codes from here:
http://www.apress.com/book/downloadfile/2847


dcharno wrote:
> 
>> Could you tell me where can I find such documentation, or can you
>> recommend some books.
> 
> "The Definitive Guide to SQLite" by Michael Owens explains the SQLite 
> API in detail and provides a number of samples in C.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/manual-with-sample-C-programs-tp22215461p22243521.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] a question on the callback function's return values of sqlite3_exec()

[liubin liu]

Thank you very much!

this is my first time to use sqlite3. using prepared statements is a little
difficult. Could I avert to another way(more simple way) to achieve the same
thing?

Or where could I get some example codes on the method of using prepared
statements?


Igor Tandetnik wrote:
> 
> "liubin liu" <7101...@sina.com> wrote in message
> news:22176984.p...@talk.nabble.com
>> the question is on the callback function's return values of
>> sqlite3_exec()
>>
>> when using sqlite3_exec() to do "select * from ...", how to get all
>> the return values by using the callback function?
>>
>> it could print the result, but couldn't return the values. If do like
>> so, just one value could be get.
>> how to get all the values?
> 
> Memory allocated for value[] strings is valid only inside the callback. 
> It is deallocated or reused as soon as the callback returns. So, you 
> can't just store a pointer you receive - it'll soon become invalid. You 
> need to allocate your own memory and make a copy of string contents.
> 
> Also, consider using prepared statements instead of sqlite3_exec - see 
> sqlite3_prepare, sqlite3_step, sqlite3_finalize, sqlite3_column_*. For 
> one thing, values of numeric fields could be retrieved directly as 
> integers, rather than converted to strings and then converted back.
> 
> Igor Tandetnik
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/a-question-on-the-callback-function%27s-return-values-of-sqlite3_exec%28%29-tp22176984p22194312.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] a question on the callback function's return values of sqlite3_exec()

[liubin liu]

the question is on the callback function's return values of sqlite3_exec()

when using sqlite3_exec() to do "select * from ...", how to get all the
return values by using the callback function?

struct olt_info
{

int olt_index;
int olt_logo;
char* olt_line;
// int nmber;
};

int my_callback(void *olt_temp, int argc, char *value[], char *name[])
{
struct olt_info *pdata = NULL;
pdata = (struct olt_info *)olt_temp; 

int jj;
for (int i = 0; i < argc; i++)
jj = printf("%s == %s\n", name[i], value[i]);

pdata->olt_index = (int)atoi(value[0]);
pdata->olt_logo = (int)atoi(value[1]);
pdata->olt_line = value[2];

return 0;
}

it could print the result, but couldn't return the values. If do like so,
just one value could be get.
how to get all the values?

-- 
View this message in context: 
http://www.nabble.com/a-question-on-the-callback-function%27s-return-values-of-sqlite3_exec%28%29-tp22176984p22176984.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