date:20081219

[sqlite] TCL Sqlite busy errorcode

2008-12-19 Thread Rick Pritchett

I was reading another site that mentioned that Sqlite busy crashes a TCL proc
instead of returning an error code of 5.  Is this true?

Jim Dodgen replied:

"pretty vague question.

I recommend you supply more details like:

what "other website"

What version of SQLite are they referring to"



Actually I searched and found an example on this in the archives of site here is
the entry.   

Re: [sqlite] Tcl - timeout method

drh
Wed, 16 Aug 2006 10:27:58 -0700

Bud Beacham <[EMAIL PROTECTED]> wrote:
> Yes.  This is a bug, but I have not filed it yet.  The problem is that the 
> the Tcl errorcode command does not return a 5 (SQLITE_BUSY) when the DB is in 
> use.  Instead, Tcl crashes with a "database locked" message.  
>   For example,
>sqlite dbCmd $dataBase
>  dbCmd timeout 3000
>  set qryResult [dbCmd eval $query]
>  set errorCode [dbCmd errorcode]
>  dbCmd close
>
>   So this means that instead of checking the errorcode to try again on a 
> locked DB you need to enclose everything in a "catch" statement, and check 
> the "catch" for an error.
>   

The current behavior is by design.  It is as intented.  It is
the "TCL way" to throw an exception when something goes wrong,
and encountering an SQLITE_BUSY error counts as something
going wrong.
--
D. Richard Hipp   <[EMAIL PROTECTED]>



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

Re: [sqlite] confusing with how to to this in sqlite

2008-12-19 Thread Rachmat Febfauza

> The result should not be as you are expecting because column category
> in table awal1 contain values 'FOOD' and column category in table
> akhir1 contains values 'FOOD ' (an additional space character). If the
> category columns (containijng 'FOOD') contain exactly the same text
> then you would get your expected result.
>>

i have to change 'FOOD ' to 'FOOD', but the result is not that i hope.

Rachmat:  If what Simon says is correct (and I have no reason to doubt  
it) you might also get your query to work by specifying a collating  
sequence of RTRIM on the category column.  
http://www.sqlite.org/datatype3.html#collation

i also spesifiy collate in create table awal1 and akhir1 like this :

CREATE TABLE awal1(Code char(5) collate RTRIM,Level varchar(8) collate RTRIM, 
Category varchar(50) collate RTRIM, Product varchar(60) collate RTRIM, Location 
varchar(50) collate RTRIM, "Begin" datetime);

CREATE TABLE akhir1(Code char(5) collate RTRIM,Level varchar(8) collate 
RTRIM,Category varchar(50) collate RTRIM,Product varchar(60) collate RTRIM, 
Location varchar(50) collate RTRIM,"End" datetime);

but the result is not my hope.

the results are :

A1236|MEDIUM|FOOD|SNACK|HOMS 1|2007-05-06 10:48:57|2007-05-06 11:19:25|1828
A1236|MEDIUM|FOOD|SNACK|HOMS 2|2007-05-06 10:48:57|2007-05-06 11:19:21|1824
A1222|SMALL|FOOD|WAFER|HOMS 1|2007-05-06 11:20:34|2007-05-06 11:42:46|1332
A1222|SMALL|FOOD|WAFER|HOMS 2|2007-05-06 11:20:34|2007-05-06 11:31:57|683
A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:31:57|2007-05-06 11:42:46|649
A1221|SMALL|FOOD|CAKE|HOMS 2|2007-05-06 11:31:57|2007-05-06 11:31:57|0
A1220|SMALL|FOOD|MARGARINE|HOMS 1|2007-05-06 11:42:46|2007-05-06 11:42:46|0
A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:42:46|2007-05-06 11:42:46|0
A1222|SMALL|FOOD|WAFER|HOMS 1|2007-05-06 11:42:46|2007-05-06 11:42:46|0
A1269|SMALL|CLOTHES|BELT|HOMS 3|2007-05-07 17:28:25|2007-05-07 17:28:27|2

take a look at this different
A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:31:57|2007-05-06 11:42:46|649 (this 
is done with sqlite)

A1221SMALLFOODCAKEHOMS 12007-5-6 11:31:572007-5-6 
11:31:570 (this is done with mysql)

how to solve this?


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



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

Re: [sqlite] confusing with how to to this in sqlite

2008-12-19 Thread D. Richard Hipp

On Dec 19, 2008, at 9:06 PM, Simon Davies wrote:
>>
>> how to make result like i hope?
>
> The result should not be as you are expecting because column category
> in table awal1 contain values 'FOOD' and column category in table
> akhir1 contains values 'FOOD ' (an additional space character). If the
> category columns (containijng 'FOOD') contain exactly the same text
> then you would get your expected result.
>>

Nice work.  Thanks for the analysis, Simon!

Rachmat:  If what Simon says is correct (and I have no reason to doubt  
it) you might also get your query to work by specifying a collating  
sequence of RTRIM on the category column.  
http://www.sqlite.org/datatype3.html#collation

D. Richard Hipp
d...@hwaci.com

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

Re: [sqlite] confusing with how to to this in sqlite

2008-12-19 Thread Simon Davies

2008/12/20 Rachmat Febfauza :
> I have problem with executing this query in sqlite. to reconstruct problem 
> please follow the following steps.
>
>
> 1. create table awal1, akhir1 and hasil1 first.
>
> CREATE TABLE awal1(Code char(5),Level varchar(8), Category varchar(50), 
> Product varchar(60), Location varchar(50), "Begin" datetime);
>
> INSERT INTO `awal1` VALUES ('A1220', 'SMALL', 'FOOD', 'MARGARINE', 'HOMS 1', 
> '2007-05-06 11:42:46');
> INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 2', 
> '2007-05-06 11:31:57');
> INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 1', 
> '2007-05-06 11:31:57');
> INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 1', 
> '2007-05-06 11:42:46');
> INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 2', 
> '2007-05-06 11:20:34');
> INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 1', 
> '2007-05-06 11:20:34');
> INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 1', 
> '2007-05-06 11:42:46');
> INSERT INTO `awal1` VALUES ('A1236', 'MEDIUM', 'FOOD', 'SNACK', 'HOMS 2', 
> '2007-05-06 10:48:57');
> INSERT INTO `awal1` VALUES ('A1236', 'MEDIUM', 'FOOD', 'SNACK', 'HOMS 1', 
> '2007-05-06 10:48:57');
> INSERT INTO `awal1` VALUES ('A1269', 'SMALL', 'CLOTHES', 'BELT', 'HOMS 3', 
> '2007-05-07 17:28:25');
>
>
> CREATE TABLE akhir1(Code char(5),Level varchar(8),Category 
> varchar(50),Product varchar(60), Location varchar(50),"End" datetime);
>
> INSERT INTO `akhir1` VALUES ('A1220', 'SMALL', 'FOOD ', 'MARGARINE', 'HOMS 
> 1', '2007-05-06 11:42:46');
> INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 2', 
> '2007-05-06 11:31:57');
> INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 1', 
> '2007-05-06 11:31:57');
> INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 1', 
> '2007-05-06 11:42:46');
> INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 2', 
> '2007-05-06 11:31:57');
> INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 1', 
> '2007-05-06 11:31:57');
> INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 1', 
> '2007-05-06 11:42:46');
> INSERT INTO `akhir1` VALUES ('A1236', 'MEDIUM', 'FOOD ', 'SNACK', 'HOMS 2', 
> '2007-05-06 11:19:21');
> INSERT INTO `akhir1` VALUES ('A1236', 'MEDIUM', 'FOOD ', 'SNACK', 'HOMS 1', 
> '2007-05-06 11:19:25');
> INSERT INTO `akhir1` VALUES ('A1269', 'SMALL', 'CLOTHES', 'BELT', 'HOMS 3', 
> '2007-05-07 17:28:27');
>
>
>
> CREATE TABLE hasil1 (Code char(5), Level vachar(8), Category varchar (50), 
> Product varchar(60), Location varchar(50), "Begin" datetime, "End" datetime, 
> Difference integer, PRIMARY KEY 
> (Code,Level,Category,Product,Location,"Begin","End"));
>
>
> 2. then execute this query
>
> insert or ignore into hasil1 select awal1.Code, awal1.Level, awal1.Category, 
> awal1.Product, awal1.Location, awal1."Begin",akhir1."End", 
> strftime("%s",akhir1."End")-strftime("%s",awal1."Begin") as Difference from 
> awal1, akhir1 where awal1.Code = akhir1.Code and awal1.Category = 
> akhir1.Category and awal1.Product = akhir1.Product and awal1.Location = 
> akhir1.Location and akhir1."End" >= awal1."Begin" group by awal1."Begin", 
> awal1.Code, awal1.Category, awal1.Product, awal1.Location;
>
>
> 3. check the result
>
> the result that i hope is like this
>
> A1236MEDIUMFOODSNACKHOMS 12007-5-6 10:48:572007-5-6 
> 11:19:251828
> A1236MEDIUMFOODSNACKHOMS 22007-5-6 10:48:572007-5-6 
> 11:19:211824
> A1222SMALLFOODWAFERHOMS 12007-5-6 11:20:342007-5-6 
> 11:31:57683
> A1222SMALLFOODWAFERHOMS 22007-5-6 11:20:342007-5-6 
> 11:31:57683
> A1221SMALLFOODCAKEHOMS 12007-5-6 11:31:572007-5-6 
> 11:31:570
> A1221SMALLFOODCAKEHOMS 22007-5-6 11:31:572007-5-6 
> 11:31:570
> A1220SMALLFOODMARGARINEHOMS 12007-5-6 11:42:46
> 2007-5-6 11:42:460
> A1221SMALLFOODCAKEHOMS 12007-5-6 11:42:462007-5-6 
> 11:42:460
> A1222SMALLFOODWAFERHOMS 12007-5-6 11:42:462007-5-6 
> 11:42:460
> A1269SMALLCLOTHESBELTHOMS 32007-5-7 17:28:252007-5-7 
> 17:28:272
>
>
> how to make result like i hope?

The result should not be as you are expecting because column category
in table awal1 contain values 'FOOD' and column category in table
akhir1 contains values 'FOOD ' (an additional space character). If the
category columns (containijng 'FOOD') contain exactly the same text
then you would get your expected result.

>
> thanks for advanced
>
>
> note : the result that i hope is mysql result's, so i confuse how to do this 
> in sqlite. Why i prefer sqlite other than mysql, coz if i execute about 12000 
> rows in mysql server, my computer starts not responding

[sqlite] confusing with how to to this in sqlite

2008-12-19 Thread Rachmat Febfauza

I have problem with executing this query in sqlite. to reconstruct problem 
please follow the following steps.


1. create table awal1, akhir1 and hasil1 first.

CREATE TABLE awal1(Code char(5),Level varchar(8), Category varchar(50), Product 
varchar(60), Location varchar(50), "Begin" datetime);

INSERT INTO `awal1` VALUES ('A1220', 'SMALL', 'FOOD', 'MARGARINE', 'HOMS 1', 
'2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 2', 
'2007-05-06 11:31:57');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 1', 
'2007-05-06 11:31:57');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 1', 
'2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 2', 
'2007-05-06 11:20:34');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 1', 
'2007-05-06 11:20:34');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 1', 
'2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1236', 'MEDIUM', 'FOOD', 'SNACK', 'HOMS 2', 
'2007-05-06 10:48:57');
INSERT INTO `awal1` VALUES ('A1236', 'MEDIUM', 'FOOD', 'SNACK', 'HOMS 1', 
'2007-05-06 10:48:57');
INSERT INTO `awal1` VALUES ('A1269', 'SMALL', 'CLOTHES', 'BELT', 'HOMS 3', 
'2007-05-07 17:28:25');


CREATE TABLE akhir1(Code char(5),Level varchar(8),Category varchar(50),Product 
varchar(60), Location varchar(50),"End" datetime);

INSERT INTO `akhir1` VALUES ('A1220', 'SMALL', 'FOOD ', 'MARGARINE', 'HOMS 1', 
'2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 2', 
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 1', 
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 1', 
'2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 2', 
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 1', 
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 1', 
'2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1236', 'MEDIUM', 'FOOD ', 'SNACK', 'HOMS 2', 
'2007-05-06 11:19:21');
INSERT INTO `akhir1` VALUES ('A1236', 'MEDIUM', 'FOOD ', 'SNACK', 'HOMS 1', 
'2007-05-06 11:19:25');
INSERT INTO `akhir1` VALUES ('A1269', 'SMALL', 'CLOTHES', 'BELT', 'HOMS 3', 
'2007-05-07 17:28:27');



CREATE TABLE hasil1 (Code char(5), Level vachar(8), Category varchar (50), 
Product varchar(60), Location varchar(50), "Begin" datetime, "End" datetime, 
Difference integer, PRIMARY KEY 
(Code,Level,Category,Product,Location,"Begin","End"));


2. then execute this query 

insert or ignore into hasil1 select awal1.Code, awal1.Level, awal1.Category, 
awal1.Product, awal1.Location, awal1."Begin",akhir1."End", 
strftime("%s",akhir1."End")-strftime("%s",awal1."Begin") as Difference from 
awal1, akhir1 where awal1.Code = akhir1.Code and awal1.Category = 
akhir1.Category and awal1.Product = akhir1.Product and awal1.Location = 
akhir1.Location and akhir1."End" >= awal1."Begin" group by awal1."Begin", 
awal1.Code, awal1.Category, awal1.Product, awal1.Location;


3. check the result

the result that i hope is like this

A1236MEDIUMFOODSNACKHOMS 12007-5-6 10:48:572007-5-6 
11:19:251828
A1236MEDIUMFOODSNACKHOMS 22007-5-6 10:48:572007-5-6 
11:19:211824
A1222SMALLFOODWAFERHOMS 12007-5-6 11:20:342007-5-6 
11:31:57683
A1222SMALLFOODWAFERHOMS 22007-5-6 11:20:342007-5-6 
11:31:57683
A1221SMALLFOODCAKEHOMS 12007-5-6 11:31:572007-5-6 
11:31:570
A1221SMALLFOODCAKEHOMS 22007-5-6 11:31:572007-5-6 
11:31:570
A1220SMALLFOODMARGARINEHOMS 12007-5-6 11:42:462007-5-6 
11:42:460
A1221SMALLFOODCAKEHOMS 12007-5-6 11:42:462007-5-6 
11:42:460
A1222SMALLFOODWAFERHOMS 12007-5-6 11:42:462007-5-6 
11:42:460
A1269SMALLCLOTHESBELTHOMS 32007-5-7 17:28:252007-5-7 
17:28:272


how to make result like i hope?

thanks for advanced


note : the result that i hope is mysql result's, so i confuse how to do this in 
sqlite. Why i prefer sqlite other than mysql, coz if i execute about 12000 rows 
in mysql server, my computer starts not responding temporary, it tooks 100% cpu 
usage but less PF or memory usage.


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

[sqlite] (no subject)

2008-12-19 Thread Rachmat Febfauza

I have problem with executing this query in sqlite. to reconstruct problem 
please follow the following steps.


1. create table awal1, akhir1 and hasil1 first.

CREATE TABLE awal1(Code char(5),Level varchar(8), Category varchar(50), Product 
varchar(60), Location varchar(50), "Begin" datetime);

INSERT INTO `awal1` VALUES ('A1220', 'SMALL', 'FOOD', 'MARGARINE', 'HOMS 1', 
'2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 2', 
'2007-05-06 11:31:57');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 1', 
'2007-05-06 11:31:57');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 1', 
'2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 2', 
'2007-05-06 11:20:34');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 1', 
'2007-05-06 11:20:34');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 1', 
'2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1236', 'MEDIUM', 'FOOD', 'SNACK', 'HOMS 2', 
'2007-05-06 10:48:57');
INSERT INTO `awal1` VALUES ('A1236', 'MEDIUM', 'FOOD', 'SNACK', 'HOMS 1', 
'2007-05-06 10:48:57');
INSERT INTO `awal1` VALUES ('A1269', 'SMALL', 'CLOTHES', 'BELT', 'HOMS 3', 
'2007-05-07 17:28:25');


CREATE TABLE akhir1(Code char(5),Level varchar(8),Category varchar(50),Product 
varchar(60), Location varchar(50),"End" datetime);

INSERT INTO `akhir1` VALUES ('A1220', 'SMALL', 'FOOD ', 'MARGARINE', 'HOMS 1', 
'2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 2', 
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 1', 
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 1', 
'2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 2', 
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 1', 
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 1', 
'2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1236', 'MEDIUM', 'FOOD ', 'SNACK', 'HOMS 2', 
'2007-05-06 11:19:21');
INSERT INTO `akhir1` VALUES ('A1236', 'MEDIUM', 'FOOD ', 'SNACK', 'HOMS 1', 
'2007-05-06 11:19:25');
INSERT INTO `akhir1` VALUES ('A1269', 'SMALL', 'CLOTHES', 'BELT', 'HOMS 3', 
'2007-05-07 17:28:27');



CREATE TABLE hasil1 (Code char(5), Level vachar(8), Category varchar (50), 
Product varchar(60), Location varchar(50), "Begin" datetime, "End" datetime, 
Difference integer, PRIMARY KEY 
(Code,Level,Category,Product,Location,"Begin","End"));


2. then execute this query 

insert or ignore into hasil1 select awal1.Code, awal1.Level, awal1.Category, 
awal1.Product, awal1.Location, awal1."Begin",akhir1."End", 
strftime("%s",akhir1."End")-strftime("%s",awal1."Begin") as Difference from 
awal1, akhir1 where awal1.Code = akhir1.Code and awal1.Category = 
akhir1.Category and awal1.Product = akhir1.Product and awal1.Location = 
akhir1.Location and akhir1."End" >= awal1."Begin" group by awal1."Begin", 
awal1.Code, awal1.Category, awal1.Product, awal1.Location;


3. check the result

the result that i hope is like this

A1236MEDIUMFOODSNACKHOMS 12007-5-6 10:48:572007-5-6 
11:19:251828
A1236MEDIUMFOODSNACKHOMS 22007-5-6 10:48:572007-5-6 
11:19:211824
A1222SMALLFOODWAFERHOMS 12007-5-6 11:20:342007-5-6 
11:31:57683
A1222SMALLFOODWAFERHOMS 22007-5-6 11:20:342007-5-6 
11:31:57683
A1221SMALLFOODCAKEHOMS 12007-5-6 11:31:572007-5-6 
11:31:570
A1221SMALLFOODCAKEHOMS 22007-5-6 11:31:572007-5-6 
11:31:570
A1220SMALLFOODMARGARINEHOMS 12007-5-6 11:42:462007-5-6 
11:42:460
A1221SMALLFOODCAKEHOMS 12007-5-6 11:42:462007-5-6 
11:42:460
A1222SMALLFOODWAFERHOMS 12007-5-6 11:42:462007-5-6 
11:42:460
A1269SMALLCLOTHESBELTHOMS 32007-5-7 17:28:252007-5-7 
17:28:272


how to make result like i hope?

thanks for advanced


note : the result that i hope is mysql result's, so i confuse how to do this in 
sqlite. Why i prefer sqlite other than mysql, coz if i execute about 12000 rows 
in mysql server, my computer starts not responding temporary, it tooks 100% cpu 
usage but less PF or memory usage.


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

[sqlite] initcap function (patch for icu.c)

2008-12-19 Thread Miroslav Zagorac

Hello,

here is a patch which adds initcap function for use with icu.c (which uses
the icu4c library - International Components for Unicode).

SQL syntax for use of initcap is similar to lower or upper functions
(see documentation in the file ext/icu/README.txt.

If you need it (or like it) use it freely.  Otherwise, ignore it.  :)

--- patch ---
diff -Nru sqlite-3.6.7-orig/ext/icu/icu.c sqlite-3.6.7/ext/icu/icu.c
--- sqlite-3.6.7-orig/ext/icu/icu.c 2008-11-05 23:38:52.0 +0100
+++ sqlite-3.6.7/ext/icu/icu.c  2008-12-20 01:37:53.0 +0100
@@ -26,6 +26,8 @@
 **
 **   * An implementation of the LIKE operator that uses ICU to 
 ** provide case-independent matching.
+**
+** gcc -shared icu.c `icu-config --ldflags` -o libSqliteIcu.so
 */
 
 #if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_ICU)
@@ -348,7 +350,9 @@
 return;
   }
 
-  if( sqlite3_user_data(p) ){
+  if( sqlite3_user_data(p) == (void*)2){
+u_strToTitle(zOutput, nOutput/2, zInput, nInput/2, NULL, zLocale, );
+  }else if( sqlite3_user_data(p) == (void*)1){
 u_strToUpper(zOutput, nOutput/2, zInput, nInput/2, zLocale, );
   }else{
 u_strToLower(zOutput, nOutput/2, zInput, nInput/2, zLocale, );
@@ -454,20 +458,24 @@
 void *pContext;   /* sqlite3_user_data() context */
 void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
   } scalars[] = {
-{"regexp",-1, SQLITE_ANY,  0, icuRegexpFunc},
+{"regexp",  -1, SQLITE_ANY,  0, icuRegexpFunc},
 
-{"lower",  1, SQLITE_UTF16,0, icuCaseFunc16},
-{"lower",  2, SQLITE_UTF16,0, icuCaseFunc16},
-{"upper",  1, SQLITE_UTF16, (void*)1, icuCaseFunc16},
-{"upper",  2, SQLITE_UTF16, (void*)1, icuCaseFunc16},
-
-{"lower",  1, SQLITE_UTF8, 0, icuCaseFunc16},
-{"lower",  2, SQLITE_UTF8, 0, icuCaseFunc16},
-{"upper",  1, SQLITE_UTF8,  (void*)1, icuCaseFunc16},
-{"upper",  2, SQLITE_UTF8,  (void*)1, icuCaseFunc16},
+{"lower",1, SQLITE_UTF16,0, icuCaseFunc16},
+{"lower",2, SQLITE_UTF16,0, icuCaseFunc16},
+{"upper",1, SQLITE_UTF16, (void*)1, icuCaseFunc16},
+{"upper",2, SQLITE_UTF16, (void*)1, icuCaseFunc16},
+{"initcap",  1, SQLITE_UTF16, (void*)2, icuCaseFunc16},
+{"initcap",  2, SQLITE_UTF16, (void*)2, icuCaseFunc16},
+
+{"lower",1, SQLITE_UTF8, 0, icuCaseFunc16},
+{"lower",2, SQLITE_UTF8, 0, icuCaseFunc16},
+{"upper",1, SQLITE_UTF8,  (void*)1, icuCaseFunc16},
+{"upper",2, SQLITE_UTF8,  (void*)1, icuCaseFunc16},
+{"initcap",  1, SQLITE_UTF8,  (void*)2, icuCaseFunc16},
+{"initcap",  2, SQLITE_UTF8,  (void*)2, icuCaseFunc16},
 
-{"like",   2, SQLITE_UTF8, 0, icuLikeFunc},
-{"like",   3, SQLITE_UTF8, 0, icuLikeFunc},
+{"like", 2, SQLITE_UTF8, 0, icuLikeFunc},
+{"like", 3, SQLITE_UTF8, 0, icuLikeFunc},
 
 {"icu_load_collation",  2, SQLITE_UTF8, (void*)db, icuLoadCollation},
   };
--- patch ---

-- 
Zaga

You have worked and not worked.  Not working is the hardest work of all.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cannot commit transaction - SQL statements in progress

2008-12-19 Thread Pankaj Gupta

Thanks Igor! I used an equivalent function in Qt called 
QSqlQuery::clear() that did the job.



Igor Tandetnik wrote:
> Pankaj Gupta 
> wrote:
>   
>> I'm using SQLite 3.6.3 with C++ and Qt.
>>
>> My problem is that when a use a select statement in a transaction
>> containing insert statements, I get an error: "cannot commit
>> transaction - SQL statements in progress - Unable to fetch row". The
>> transaction seems to work if I remove the 'select' statement.
>> 
>
> You must reset or finalize all open statements before comitting the 
> transaction.
>
> Igor Tandetnik 
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   


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

Re: [sqlite] [perl] unable to open database file

2008-12-19 Thread Ribeiro, Glauber

Perhaps the directory name is mispeled? Do "$!" or "$@" have anything
interesting in them?

Perl's debugger is invoked with the -d switch. See the "perldebug"
document 

-Original Message-
From: LUKE [mailto:l...@tc.program.com.tw] 
Sent: Thursday, December 18, 2008 1:31 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] [perl] unable to open database file

DBD::SQLite::st execute failed: unable to open database file(14) at
dbdimp.c line
 423
1. I use root to run the program.
2. I try to chmod 777 -R directory.

But the error is still exist. How to debug the program?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] IN expression performance

2008-12-19 Thread Sander Jansen

On Thu, Dec 18, 2008 at 7:44 PM, Griggs, Donald
 wrote:
>  Regarding:
>   Is there a way to use a prepared statement and bind a (variable)
> array of integers?
>
>Like in:
>SELECT  FROM table WHERE someinteger IN ( 2,18,19,340,1,72, 15
> ... )
>
>Becomes:
>SELECT  FROM table WHERE someinteger IN ( ? )
>
>
> I don't know that you can bind these, but
> do I remember correctly that these integers are selected by human users?
> If so, I guess the savings in time would be unmeasurably small, right?
> Or do your users type the string directly were you aiming to reduce sql
> injections?
>

Yes, these come in from user input through multi-select lists (it's a
itunes like interface), so I'm not worried about SQL injections. When
the selection changes, the resulting query gets build up, compiled and
executed. The query is the same everytime, so only the contents of the
"someinteger IN " changes.

I thought it would be nicer, at least from a coding standpoint, if I
could make use of prepared statements. Faster would be nice, but not a
requirement. Slower would be a big no no ofcourse :)

So it seems I could use a temp table that gets filled with the new
selection everytime the user changes the selection. I guess there is
some overhead there as well, but on the other hand, the parsing of the
query everytime also has a overhead. I'll give it a try!

Thanks,

Sander

-- 
"And any fool knows a dog needs a home
A shelter from pigs on the wing"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Find out difference before and after update with cb

2008-12-19 Thread P Kishor

On 12/18/08, Daniel Schnell  wrote:
> Hi,
>
>  is there a possibility to find out the difference before and after an
>  update of a table through the update callback ?
>
>  I know that I can register a callback to a table for getting the rowid
>  of an SQLITE_UPDATE call. But the documentation doesn't state, if the
>  callback can still retrieve the old values to find out what these were
>  before the update occured.
>
>  Is there a way to find these out ?
>
>
>

I once had to do this (the silly client wanted to know the values of
the row "just changed" by him!). I created a transaction whereby I
first copied the row values to a temp table, then changed those row
values, and then presented the old values stored in the temp table
along with the new values in the actual table. Kludgy, but once
implemented, it actually worked very well, especially with a web
interface that showed the old values slightly greyed out just below
the new ones. And, I also allowed a "rollback" to the old values until
they were overwritten by another change. In other words, my undo
buffer was only once change deep.

-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] delete-stmt-limited perf

2008-12-19 Thread Dan

On Dec 18, 2008, at 4:47 AM, Daniel Witte wrote:

> So, I'd like to use the statement
>
> DELETE FROM moz_cookies WHERE lastAccessed <= ?1 ORDER BY  
> lastAccessed ASC
> LIMIT ?2
>
> for a query in Firefox backend code (we use the shipped  
> amalgamation, which
> doesn't allow SQLITE_ENABLE_UPDATE_DELETE_LIMIT). The closest query  
> we can
> implement, as far as I can tell, would be (note id is the pkey):
>
> DELETE FROM moz_cookies WHERE id IN (SELECT id FROM moz_cookies WHERE
> lastAccessed
> <= ?1 ORDER BY lastAccessed ASC LIMIT ?2)
>
> Will sqlite optimize the latter to the former? If not, what are the  
> likely
> perf differences here? (And why isn't  
> SQLITE_ENABLE_UPDATE_DELETE_LIMIT
> enabled by default?)

Unless 'id' is already an INTEGER PRIMARY KEY, it would be slightly  
better
to use rowid than 'id'. i.e. do:

   DELETE FROM moz_cookies WHERE rowid IN (
 SELECT rowid
 FROM moz_cookies
 WHERE lastAccessed <= ?1
 ORDER BY lastAccessed
 LIMIT ?2
   );

The special syntax enabled by SQLITE_ENABLE_UPDATE_DELETE_LIMIT is  
basically
implemented by transforming queries like your first example to the  
above. So
you should get exactly the same performance if you do the transform  
yourself
by hand before passing the query to SQLite.

I think ENABLE_UPDATE_DELETE_LIMIT is not enabled by default because  
it is
non-standard SQL. And because the policy is to keep the binary footprint
as small as possible.

Dan.

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

Re: [sqlite] Unexpected interaction between virtual tables and the IN operator.

2008-12-19 Thread Filip Navara

Any update on this? It's seriously affecting performance in our application.

Best regards,
Filip Navara

On Tue, Aug 12, 2008 at 6:33 PM, Scott Hess  wrote:
> [Full text of an example later in the email.]
>
> For a query like this:
>
>   SELECT * FROM t_ft WHERE docid = (SELECT rowid FROM t WHERE label = 'y');
>
> fulltextBestIndex() gets a SQLITE_INDEX_CONSTRAINT_EQ on the docid
> column, which can be efficiently accessed, so fulltextFilter() can do
> a very efficient query.  Meanwhile, for this:
>
>   SELECT * FROM t_ft WHERE docid IN (SELECT rowid FROM t WHERE label = 'y');
>
> fulltestBestIndex() gets no constraints, and thus can't do anything,
> so you end up with a full table scan of the fts3 table.
>
> As far as I can tell from sqlite/src/where.c, the IN operator is
> explicitly not optimized for virtual tables.  I can't quite figure out
> why, though.  An oversight?  It makes things very inefficient for
> certain schema arrangements.  You can work around it by making your
> sub-select explicit in your application code, but since IN is
> optimized fine for regular tables, it's a bit unexpected.
>
> Thanks,
> scott
>
>
> An example for feeding to sqlite3 (though you'll need to set
> breakpoints in fts3.c to see it happening):
>
> DROP TABLE IF EXISTS t;
> DROP TABLE IF EXISTS t_ft;
> CREATE TABLE t (
>  id INTEGER PRIMARY KEY,
>  label TEXT
> );
> CREATE INDEX t_idx ON t(label);
> CREATE VIRTUAL TABLE t_ft USING fts3(c);
>
> INSERT INTO t (id, label) VALUES (null, 'x');
> INSERT INTO t_ft (docid, c) VALUES (LAST_INSERT_ROWID(), 'This is a test');
> INSERT INTO t (id, label) VALUES (null, 'y');
> INSERT INTO t_ft (docid, c) VALUES (LAST_INSERT_ROWID(), 'That was a test');
> INSERT INTO t (id, label) VALUES (null, 'x');
> INSERT INTO t_ft (docid, c) VALUES (LAST_INSERT_ROWID(), 'Another test');
> INSERT INTO t (id, label) VALUES (null, 'z');
> INSERT INTO t_ft (docid, c) VALUES (LAST_INSERT_ROWID(), 'And another');
>
> SELECT * FROM t_ft WHERE docid = (SELECT rowid FROM t WHERE label =
> 'y');   -- Efficient
> SELECT * FROM t_ft WHERE docid IN (SELECT rowid FROM t WHERE label =
> 'y');   -- Inefficient, sub-select matches one row
> SELECT * FROM t_ft WHERE docid IN (SELECT rowid FROM t WHERE label =
> 'x');   -- Inefficient, sub-select matches multiple rows
> ___
> 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

[sqlite] delete-stmt-limited perf

2008-12-19 Thread Daniel Witte

So, I'd like to use the statement

DELETE FROM moz_cookies WHERE lastAccessed <= ?1 ORDER BY lastAccessed ASC
LIMIT ?2

for a query in Firefox backend code (we use the shipped amalgamation, which
doesn't allow SQLITE_ENABLE_UPDATE_DELETE_LIMIT). The closest query we can
implement, as far as I can tell, would be (note id is the pkey):

DELETE FROM moz_cookies WHERE id IN (SELECT id FROM moz_cookies WHERE
lastAccessed
<= ?1 ORDER BY lastAccessed ASC LIMIT ?2)

Will sqlite optimize the latter to the former? If not, what are the likely
perf differences here? (And why isn't SQLITE_ENABLE_UPDATE_DELETE_LIMIT
enabled by default?)

Many thanks in advance.

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

Re: [sqlite] sqlite3_create_function callback problem

2008-12-19 Thread Guillaume Schub

Ok, I figured it out !!!

This was actually a calling convention problem. Our software is built
using __fastcall as default convention, and the function in sqlite3.h
is declared as:

int __cdecl sqlite3_create_function(
  sqlite3 *,
  const char *zFunctionName,
  int nArg,
  int eTextRep,
  void*,
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
  void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  void (*xFinal)(sqlite3_context*)
);

So the compiler considered the pointers xFunc, xStep and xFinal as
being __fastcall. That's why I got this error when declaring my
callback as __cdecl.

As a solution, I modified the delclaration like this:

int __cdecl sqlite3_create_function(
  sqlite3 *,
  const char *zFunctionName,
  int nArg,
  int eTextRep,
  void*,
  void (__cdecl *xFunc)(sqlite3_context*,int,sqlite3_value**),
  void (__cdecl *xStep)(sqlite3_context*,int,sqlite3_value**),
  void (__cdecl *xFinal)(sqlite3_context*)
);

to force the pointers to be __cdecl. No I can (I even have to) declare
my callback as __cdecl and the parameters are correct again.

Now is there any reason for the declaration to be like this, or do you
think I should submit the fix somewhere ?

Thanks for your clues

Guillaume


On Fri, Dec 19, 2008 at 9:06 AM, Guillaume Schub  wrote:
> Hello,
>
> Looks like my last reply wasn't received
>
> So I tried with a static function out of a class without more success.
>
> Then I tried changing the calling convention to __stdcall and even
> __cdecl, but I got a compilation error asking for a __fastcall
> function, so no more success here (I'm using Visual Studio on Windows
> XP).
>
> I also thought about a module issue (pointer to callback was not on
> the same dll as sqlite3_create_function) but again no success.
>
>
>
> On Wed, Dec 17, 2008 at 7:17 PM, Igor Tandetnik  wrote:
>> Guillaume Schub 
>> wrote:
>>> sqlite3_create_function(m_pDB, "ScorePosition", "ScorePosition", 8,
>>> SQLITE_ANY, NULL, , NULL, NULL);
>>>
>>> class MyClass
>>> {
>>> ...
>>> static void ScorePosition(sqlite3_context *pContext,int argc,
>>> sqlite3_value** argv);
>>> ...
>>> };
>>>
>>> No the problem is that the callback is called, but pContext is always
>>> NULL, argc a fancy value, and argv always the same value (like
>>> 0x0012f2d4)
>>
>> Sounds like a calling convention mismatch. What compiler are you using,
>> on what OS?
>>
>> Igor Tandetnik
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_create_function callback problem

2008-12-19 Thread Guillaume Schub

Sorry I forgot to mention: I already tried single C function. I even
tried with extern "C" just in case but got exactly the same result.

I also tried __stdcall and __cdecl but looks like SQLite is wayting
for __fastcall function pointer

On Wed, Dec 17, 2008 at 5:37 PM, Martin Engelschalk
 wrote:
> Hi,
>
> i suspect that you problem is the class MyClass.
> Try to define your function "ScorePosition" as a C-function outside of a
> class.
>
> Martin
>
> Guillaume Schub wrote:
>> Hi everyone,
>>
>> I have spent nearly one day on this already with no success. I am
>> currently trying to create some custom function on a SQLite3 database.
>>
>> What I did (just to test):
>>
>> sqlite3_create_function(m_pDB, "ScorePosition", "ScorePosition", 8,
>> SQLITE_ANY, NULL, , NULL, NULL);
>>
>> class MyClass
>> {
>> ...
>> static void ScorePosition(sqlite3_context *pContext,int argc,
>> sqlite3_value** argv);
>> ...
>> };
>>
>> void MyClass::ScorePosition(sqlite3_context *pContext,int argc,
>> sqlite3_value** argv)
>> {
>>   ...
>> }
>>
>> SELECTScorePosition(1, 2, 3, 4, 5, 6, 7, 8) FROM MyTable;
>>
>> No the problem is that the callback is called, but pContext is always
>> NULL, argc a fancy value, and argv always the same value (like
>> 0x0012f2d4)
>>
>> What am I missing here ?
>>
>> Guishu
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> --
>
> * Codeswift GmbH *
> Traunstr. 30
> A-5026 Salzburg-Aigen
> Tel: +49 (0) 8662 / 494330
> Mob: +49 (0) 171 / 4487687
> Fax: +49 (0) 12120 / 204645
> engelsch...@codeswift.com
> www.codeswift.com / www.swiftcash.at
>
> Codeswift Professional IT Services GmbH
> Firmenbuch-Nr. FN 202820s
> UID-Nr. ATU 50576309
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_create_function callback problem

2008-12-19 Thread Guillaume Schub

Hello,

Looks like my last reply wasn't received

So I tried with a static function out of a class without more success.

Then I tried changing the calling convention to __stdcall and even
__cdecl, but I got a compilation error asking for a __fastcall
function, so no more success here (I'm using Visual Studio on Windows
XP).

I also thought about a module issue (pointer to callback was not on
the same dll as sqlite3_create_function) but again no success.

On Wed, Dec 17, 2008 at 7:17 PM, Igor Tandetnik  wrote:
> Guillaume Schub 
> wrote:
>> sqlite3_create_function(m_pDB, "ScorePosition", "ScorePosition", 8,
>> SQLITE_ANY, NULL, , NULL, NULL);
>>
>> class MyClass
>> {
>> ...
>> static void ScorePosition(sqlite3_context *pContext,int argc,
>> sqlite3_value** argv);
>> ...
>> };
>>
>> No the problem is that the callback is called, but pContext is always
>> NULL, argc a fancy value, and argv always the same value (like
>> 0x0012f2d4)
>
> Sounds like a calling convention mismatch. What compiler are you using,
> on what OS?
>
> Igor Tandetnik
>
>
>
> ___
> 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

[sqlite] TCL Sqlite busy errorcode

Re: [sqlite] confusing with how to to this in sqlite

Re: [sqlite] confusing with how to to this in sqlite

Re: [sqlite] confusing with how to to this in sqlite

[sqlite] confusing with how to to this in sqlite

[sqlite] (no subject)

[sqlite] initcap function (patch for icu.c)

Re: [sqlite] cannot commit transaction - SQL statements in progress

Re: [sqlite] [perl] unable to open database file

Re: [sqlite] IN expression performance

Re: [sqlite] Find out difference before and after update with cb

Re: [sqlite] delete-stmt-limited perf

Re: [sqlite] Unexpected interaction between virtual tables and the IN operator.

[sqlite] delete-stmt-limited perf

Re: [sqlite] sqlite3_create_function callback problem

Re: [sqlite] sqlite3_create_function callback problem

Re: [sqlite] sqlite3_create_function callback problem

17 matches

Site Navigation

Mail list logo

Footer information