[sqlite] temporay file
my query take hundreds of MB temporary file if i execute this query. why sqlite make enourmous big temporary table. my table in only 9 MB and have 12000 row. then i compare with mysql again, it's not make big temp table too much. my query is in topic [sqlite] confusing with how to to this in sqlite ___ 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
later i consider that my query take hundreds of MB temporary file if i execute this query. why sqlite make big temporary table is enormous big. my table in only 9 MB and have 12000 row. then i compare with mysql again, it's not make big temp table too much - Original Message From: Griggs, Donald donald.gri...@allscripts.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Tuesday, December 30, 2008 2:52:11 AM Subject: Re: [sqlite] confusing with how to to this in sqlite -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rachmat Febfauza Sent: Sunday, December 28, 2008 9:13 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] confusing with how to to this in sqlite thanks simon for the explanation. after holiday, i works on how to optimize my query. actually awal1 table consist 12000 rows and akhir1 too. how to improve performance? i added index on table awal1 and akhir1 with following syntax : create index awal1i1 on awal1(Code,Category,Product,Location,Begin); create index akhir1i1 on akhir1(Code,Category,Product,Location,End); is this create index syntax right? or i must specify each column with individual index?? like create index awal1i1 on awal1(Code); create index awal1i2 on awal1(Product); etc and i want to know to to improve performance of my query? some hint? i have one question again, is sqlite suitable for large database file? coz my apps may grow up to 1 giga database file. thanks again = = Regarding syntax: If you don't get an error, the syntax is acceptible.;-) Sqlite *does* support compound indicies. However: -- You may want to use EXPLAIN QUERY PLAN as a prefix to your SELECT (just running as a test) to ensure than your index is used. -- You can quickly experiment with using a simple index on BEGIN or PRODUCT instead and measure times. -- As you measure times, be aware of possible caching effects -- i.e. the first run may be slower than subsequent runs of the a query on the same tables. -- Make sure you see the link on performance, below. -- Make sure you include many INCLUDES within a single TRANSACTION (if appropriate to your application). This can make a dramatic difference. -- You want to be familiar with the PRAGMA's that can affect performance. http://www.sqlite.org/pragma.html (but note that some of these can be used to trade data safety for performance -- make sure you're making an informed choice) Regarding: is sqlite suitable for large database file? coz my apps may grow up to 1 giga database file. Have you read http://www.sqlite.org/whentouse.html And http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations ? If not, you'll want to. Many folks successfully run sqlite on multi-gigabyte databases, BUT -- in those cases, the simplicity and small footprint of sqlite may be less compelling, -- Are there any features in http://www.sqlite.org/omitted.html that you will grieve in their absence? You might go over the detailed feature lists for postgres, Mysql, etc. with the same question in mind. -- How much concurant access do you anticipate? -- Will you control the queries (so as to optimize them and the indicies) or will the database be subjected frequently to ad hoc queries (which *might* benefit from a sophisticated query optimizer)? Hope this helps, Donald ___ 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
thanks simon for the explanation. after holiday, i works on how to optimize my query. actually awal1 table consist 12000 rows and akhir1 too. how to improve performance? i added index on table awal1 and akhir1 with following syntax : create index awal1i1 on awal1(Code,Category,Product,Location,Begin); create index akhir1i1 on akhir1(Code,Category,Product,Location,End); is this create index syntax right? or i must specify each column with individual index?? like create index awal1i1 on awal1(Code); create index awal1i2 on awal1(Product); etc and i want to know to to improve performance of my query? some hint? i have one question again, is sqlite suitable for large database file? coz my apps may grow up to 1 giga database file. thanks again ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] cancel query in sqlite
how to cancel query in sqlite specially in delphi, coz i execute query from delphi code. thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] cancel query in sqlite
I don't know What that is in the Delphi wrapper you use. === i use zeos ___ 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
= Without the group by clause the result set is: sqlite select awal1.Code, awal1.Level, awal1.Category, awal1.Product, awal1.Location, awal1.Begin,akhir1.End, strftime(%s,akh ir1.End)-strftime(%s,awal1.Begin) as Difference from awal1, akhir1 where awal1.Code = akhir1.Code and akhir1.Category like awa l1.Category || '%' and awal1.Product = akhir1.Product and awal1.Location = akhir1.Location and akhir1.End = awal1.Begin; A1220SMALLFOODMARGARINEHOMS 12007-05-06 11:42:46 2007-05-06 11:42:460 A1221SMALLFOODCAKEHOMS 22007-05-06 11:31:57 2007-05-06 11:31:570 A1221SMALLFOODCAKEHOMS 12007-05-06 11:31:57 2007-05-06 11:31:570 A1221SMALLFOODCAKEHOMS 12007-05-06 11:31:57 2007-05-06 11:42:46649 A1221SMALLFOODCAKEHOMS 12007-05-06 11:42:46 2007-05-06 11:42:460 A1222SMALLFOODWAFERHOMS 22007-05-06 11:20:34 2007-05-06 11:31:57683 A1222SMALLFOODWAFERHOMS 12007-05-06 11:20:34 2007-05-06 11:31:57683 A1222SMALLFOODWAFERHOMS 12007-05-06 11:20:34 2007-05-06 11:42:461332 A1222SMALLFOODWAFERHOMS 12007-05-06 11:42:46 2007-05-06 11:42:460 A1236MEDIUMFOODSNACKHOMS 22007-05-06 10:48:57 2007-05-06 11:19:211824 A1236MEDIUMFOODSNACKHOMS 12007-05-06 10:48:57 2007-05-06 11:19:251828 A1269SMALLCLOTHESBELTHOMS 32007-05-07 17:28:25 2007-05-07 17:28:272 The group by clause combines rows A1221SMALLFOODCAKEHOMS 12007-05-06 11:31:57 2007-05-06 11:31:570 A1221SMALLFOODCAKEHOMS 12007-05-06 11:31:57 2007-05-06 11:42:46649 into 1 row. The values in the columns not included in the group by clause (Begin, End and Difference) could be from any of the combined rows (which rows is not, I believe, specified in any standard). MySql and Sqlite seem to result in different selections. If you want specific rows, then you need to modify the query to control the data selection. In this case it appears that: selectawal1.Code, awal1.Level, awal1.Category, awal1.Product, awal1.Location, awal1.Begin, min( akhir1.End ), min( strftime(%s,akhir1.End)-strftime(%s,awal1.Begin) ) as Difference fromawal1, akhir1 whereawal1.Code = akhir1.Code and akhir1.Category like awal1.Category || '%' and awal1.Product = akhir1.Product and awal1.Location = akhir1.Location and akhir1.End = awal1.Begin group byawal1.Begin, awal1.Code, awal1.Category, awal1.Product, awal1.Location; gives the result you want. Rgds, Simon = thanks to Simon. it works. but i have some question. i hope u can help me to explain these : 1. is it necessary or not to specify min(awal1.begin). 2. is min(strftime(%s,akhir1.End)-strftime(%s,awal1.Begin)) as Difference is more time consuming to compute than strftime(%s,min(akhir1.End))-strftime(%s,awal1.Begin) as Difference. i think these give the same result. 3. akhir1.Category like awal1.Category || '%'. what does it mean? why is use || '%'???. why not to write awal1.category = akhir1.category. it also give the same result. once again, i would say thank you for ur solution. sory for my bad english. ___ 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
It looks like you are using the sqlite3 shell, so experiment with .separator Have you used .help? Rgds, Simon yes i am using sqlite3 shell, i mean not to make display like mysql does, but the difference column that i want like mysql does. 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) why in sqlite we got (649) : 2007-05-06 11:31:57|2007-05-06 11:42:46|649 and in mysql we got (0) : 2007-5-6 11:31:572007-5-6 11:31:570 or i think the reason is like i found in mysql documentation like this : If you use LEFT JOIN to find rows that do not exist in some table and you have the following test: col_name IS NULL in the WHERE part, where col_name is a column that is declared as NOT NULL, MySQL stops searching for more rows (for a particular key combination) after it has found one row that matches the LEFT JOIN condition. i also have test with left join but the result don't like mysql does. sory for my less knowledge thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
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] confusing with how to to this in sqlite
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
Re: [sqlite] confusing with how to to this in sqlite
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
[sqlite] how to load in file and outfile
how to do load infile and into outfile like mysql does in sqlite? thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how sqlite use index
how to make sqlite use index? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how do this stuff in sqlite
anyone can help me? - Original Message From: Igor Tandetnik [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Thursday, November 27, 2008 12:07:31 PM Subject: Re: [sqlite] how do this stuff in sqlite Rachmat Febfauza [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] i have query that don't work in sqlite but in mysql work and make good result. Define don't work. Do you get an error? What's the error text? 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)); Begin and End are keywords in SQLite (SQL is case insensitive). If you insist on naming your columns this way, you have to enclose the names in double quotes, just as you did when creating awal1 and akhir1. 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] how do this stuff in sqlite
Define don't work. Do you get an error? What's the error text? i mean it can be run but the result doesn't like mysql do. whats wrong with my query? Begin and End are keywords in SQLite (SQL is case insensitive). If you insist on naming your columns this way, you have to enclose the names in double quotes, just as you did when creating awal1 and akhir1. thanks but, i think we don't need to quote begin or end coz this is some exceprtion. like mysql does. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how do this stuff in sqlite
i have query that don't work in sqlite but in mysql work and make good result. sqlite : table definition CREATE TABLE awal1(Code char(5),Level varchar(8), Category varchar(50), Product varchar(60), Location varchar(50), begin datetime); CREATE TABLE akhir1(Code char(5),Level varchar(8),Category varchar(50),Product varchar(60), Location varchar(50),end datetime); 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)); 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 data i attached in zip file. mysql table definition CREATE TABLE `awal1` ( `Code` char(5) NOT NULL default '', `Level` enum('SMALL','MEDIUM','BIG') NOT NULL default 'SMALL', `Category` varchar(50) NOT NULL default '', `Product` varchar(60) NOT NULL default '', `Location` varchar(50) NOT NULL default '', `Begin` datetime NOT NULL default '-00-00 00:00:00' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `akhir1` ( `Code` char(5) NOT NULL default '', `Level` enum('SMALL','MEDIUM','BIG') NOT NULL default 'SMALL', `Category` varchar(50) NOT NULL default '', `Product` varchar(60) NOT NULL default '', `Location` varchar(50) NOT NULL default '', `End` datetime NOT NULL default '-00-00 00:00:00' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `hasil1` ( `Code` char(5) NOT NULL default '', `Level` enum('SMALL','MEDIUM','BIG') NOT NULL default 'SMALL', `Category` varchar(50) NOT NULL default '', `Product` varchar(60) NOT NULL default '', `Location` varchar(50) NOT NULL default '', `Begin` datetime NOT NULL default '-00-00 00:00:00', `End` datetime NOT NULL default '-00-00 00:00:00', `Difference` int(8) NOT NULL, PRIMARY KEY (`Code`,`Level`,`Category`,`Product`,`Location`,`Begin`,`End`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; query insert ignore into hasil1 select awal1.Code, awal1.Level, awal1.Category, awal1.Product, awal1.Location, awal1.Begin, akhir1.End, time_to_sec(timediff(akhir1.End, 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; sory if my post messed up, sory for my bad english. i attached data and this file or u can download at http://rapidshare.com/files/167784565/up.zip.html thanks 4 ur advanced ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users