sqlite statement not working!! lazarus / 16-Databases ---------------------------------------------------------------------- davesimplewear · 2009-12-19 23:51 I am trying to filter display and report dates by sql statements in sqlite3, I run the following procedure and nothing happens, can someone point me in the right direction? pickDate is a form with a from and to calendar Quoteprocedure TfReports.sbSelectClick(Sender: TObject); begin   with pickDate do    begin     Caption := 'Filter Report Dates';     ShowModal;    end; if PickDate.ModalResult= mrOk then   try       dmData.tDiary.DisableControls;       dmData.tDiary.Close;       dmData.tDiary.SQLList.Clear;       dmData.tDiary.SQLList.Add('select * from blood');       dmData.tDiary.SQLList.Add(' where readDate >= ''' + pickDate.cdFrom.Date + ''' and readDate <= ''' +pickDate.cdTo.Date + '''');       dmData.tDiary.ExecSQLList;       dmData.tDiary.Open;       dmData.tDiary.EnableControls;     finally       case gbrRpt.ItemIndex of       0:begin           diaryRpt.LoadFromFile(ExtractFilePath(Application.ExeName)+' /data/detail.lrf');           diaryRpt.ShowReport;         end;       1:begin           diaryRpt.LoadFromFile(ExtractFilePath(Application.ExeName)+' /data/readings.lrf');           diaryRpt.ShowReport;         end;       end;     end;   dmData.tDiary.DisableControls;   dmData.tDiary.Close;   dmData.tDiary.SQLList.Clear;   dmData.tDiary.SQLList.Add('select * from blood');   dmData.tDiary.ExecSQLList;   dmData.tDiary.Open;   dmData.tDiary.EnableControls; end; ---------------------------------------------------------------------- bluewolf · 2009-12-20 13:58 Hi Dave, I would debug the value of SQLList after you compose the string. Just remind that for SQLLite you should put date in the format  'YYYY-MM-DD'. So your final query should appear like select * from blood where readDate >= '2009-11-01' and readDate <= '2009-12-20' I suggest you to change the code in this way: dmData.tDiary.SQLList.Add(' where readDate >= ''' + FormatDateTime('yyyy-mm-dd', pickDate.cdFrom.Date) + ''' and readDate <= ''' + FormatDateTime('yyyy-mm-dd', pickDate.cdTo.Date) + ''''); If you still have problem, get the SQL string and try executing it in SQLIte Administrator. Regards. Alberto. ---------------------------------------------------------------------- davesimplewear · 2009-12-22 00:48 Thanks for your reply, I tried what you suggested with the statement, but no difference still no result, will try it in a sqlite manager to see if there is an error. ---------------------------------------------------------------------- LuizAmérico · 2009-12-22 00:59 ExecSqlList is just for Insert, Update, Delete statements The Select statement should be set in Sql property About date handling see Q2 and 3 of the FAQ: http://sqlite4fpc.yolasite.com/faq.php ---------------------------------------------------------------------- davesimplewear · 2009-12-22 04:07 I looked at the link provided, entered the code as follows, and the reult is the same as a select * statement, I tried to filter in an sqlite manager and only get a result by using the date representation as 41069 and 41087 and got the correct result, would saving the date as a string give the reult that I am looking for? Quoteprocedure TfReports.sbSelectClick(Sender: TObject); begin   with pickDate do    begin     Caption := 'Filter Report Dates';     ShowModal;    end; if PickDate.ModalResult= mrOk then   try       dmData.tDiary.DisableControls;       dmData.tDiary.Close;       dmData.tDiary.SQL:=('select * from blood where readDate >= ''' + FloatToStr(pickDate.cdFrom.DateTime)  + ''' and readDate <= ''' + FloatToStr(pickDate.cdTo.DateTime) + '''');       dmData.tDiary.Open;       dmData.tDiary.EnableControls;     finally       case gbrRpt.ItemIndex of       0:begin           diaryRpt.LoadFromFile(ExtractFilePath(Application.ExeName)+' /data/detail.lrf');           diaryRpt.ShowReport;         end;       1:begin           diaryRpt.LoadFromFile(ExtractFilePath(Application.ExeName)+' /data/readings.lrf');           diaryRpt.ShowReport;         end;       end;     end;   dmData.tDiary.DisableControls;   dmData.tDiary.Close;   dmData.tDiary.SQL:=('select * from blood');   dmData.tDiary.Open;   dmData.tDiary.EnableControls; end; ---------------------------------------------------------------------- bluewolf · 2009-12-22 18:39 Hi Dave, before answering my first reply I had made a simple test with an application having a DBGrid associated to a SQL table. First of all you should be aware that SQLite does not have a dedicated date or datetime type. The date or datetime is stored internally as a string. There are some functions which might help you and you can check them here: http://www.sqlite.org/lang_datefunc.html. In my case, I just format the date from the TDateEdit component in the way I suggested you and it as working. Note that my type definition was Date in SQLite. In SQLite manager did you try to filter using the format I suggested you? try to use this in SQLite manager: select * from blood where readDate >= '2009-11-01' and readDate <= '2009-12-01'; Obviously you should replace the date with the range that is suitable for you. By the way, are you using date or datetime format in definition? The part of my code (using Zeoslib) was quite simple: procedure TForm1.Button1Click(Sender: TObject); begin   ZReadOnlyQuery1.Active:=False;   ZReadOnlyQuery1.SQL.Text:='select * from invoices where invdate >= ''' + FormatDateTime('yyyy-mm-dd', DateEdit1.Date) + '''';   ShowMessage(ZReadOnlyQuery1.SQL.Text);   ZReadOnlyQuery1.Active:=True; end;  In this way I was selecting records from a table where date was greater than the value of TDateEdit component formatted in the proper way. Regards. Alberto. ---------------------------------------------------------------------- davesimplewear · 2009-12-22 20:43 Hi Alberto, Yes I tried doing as you suggested, ran the query as you suggested, but no result, as the Date field was saved as '41609' format and the only way the query would work was to use that format which is not practical in code, so I am thinking to save as a string instead of Date to give me the result I am after, and yes I used Date not DateTime. Thanks for the link I will check that out. Regards Dave ---------------------------------------------------------------------- LuizAmérico · 2009-12-22 20:50 Try removing the quotes around the dates: 'select * from blood where readDate >= ' + FloatToStr(pickDate.cdFrom.DateTime)  + ' and readDate <= ' + FloatToStr(pickDate.cdTo.DateTime); ---------------------------------------------------------------------- davesimplewear · 2009-12-22 23:31 Hi Luiz, I tried that and the report came up blank! Regards Dave ---------------------------------------------------------------------- LuizAmérico · 2009-12-23 00:23 What control is pickDate.cdFrom ? cdFrom.DateTime is just the date or is the date plus the time? Can you post result of FlotToStr(pickDate.cdFrom)? Can you send me the datafile? ---------------------------------------------------------------------- davesimplewear · 2009-12-23 05:46 Hi Luiz, The Control is a TCalendar, and the Date is DateTime, the Date property is a string type, the result from FloatToStr(pickDate.cdFrom) is 4014.6727998032 (23/12/09)I will attach a csv file with the table and data info. ---------------------------------------------------------------------- LuizAmérico · 2009-12-23 09:48 This date value is date plus time value. Try FloatToStr(Trunc(pickDate.cdFrom.DateTime)) or IntToStr(Trunc(pickDate.cdFrom.DateTime)) ---------------------------------------------------------------------- bluewolf · 2009-12-23 10:46 Hi Dave, could you post a database with only the table blood and few records as they appear in CSV file? Alternatively, can you run this select: select * from sqlite_master and send me the SQL Create statement for table "blood". Also if you have sqlite3.exe can you execute this command and tell me the result: .dump blood Last question, which version of SQLite are you using? Regards. Alberto. ---------------------------------------------------------------------- davesimplewear · 2009-12-23 21:04 Hi Luiz, I tried the FloatToStr(Trunc()) method the result was a blank report. ---------------------------------------------------------------------- LuizAmérico · 2009-12-23 23:48 Strange. There's something wrong at your side. I created a database with the data you sent and created a small example that work as expected. Take a look at the source to see if it'll help you. ---------------------------------------------------------------------- davesimplewear · 2009-12-24 00:14 Hi Luiz, Yes the code will definitely help, it is a different approach to what I was doing, I was using my old delphi code which didn't work the way I expected. Thanks and Regards Dave ---------------------------------------------------------------------- bluewolf · 2009-12-24 17:33 Hi Dave, I made some investigation based also on the file posted by LuizAmerico. To be more precise, based on the information you sent me I have installed sqlite 3 under Linux, more precisely Ubuntu 9.10 using a virtual machine. Installing sqlite in Ubuntu is quite simple and straight forward. It is enough to enter this command at the prompt: $ sudo apt-get install sqlite3 Once sqlite has been installed I have used the sqlite3 command line utility to create a database blood.db: alberto@Ubuntu:~/download$ sqlite3 ./blood.db SQLite version 3.6.16 Enter ".help" for instructions Enter SQL statements terminated with a ";" At this point I have created a table like the one used by LuizAmerico that is having a subset of the column you have in your table: sqlite> PRAGMA foreign_keys=OFF; sqlite> BEGIN TRANSACTION; sqlite> CREATE TABLE [blood] (    ...> [readID] INTEGER  NULL,    ...> [Day] VARCHAR(255)  NULL,    ...> [readDate] DATE  NULL,    ...> [ReadTime] TIME  NULL    ...> ); sqlite> INSERT INTO "blood" VALUES(68,'Tuesday',40162,'16:28'); sqlite> INSERT INTO "blood" VALUES(69,'Tuesday',40162,'21:25'); sqlite> INSERT INTO "blood" VALUES(70,'Wednesday',40163,'07:16'); sqlite> INSERT INTO "blood" VALUES(71,'Wednesday',40163,'16:30'); sqlite> INSERT INTO "blood" VALUES(72,'Wednesday',40163,21.35); sqlite> INSERT INTO "blood" VALUES(73,'Thursday',40164,'08:17'); sqlite> INSERT INTO "blood" VALUES(74,'Thursday',40164,'16:15'); sqlite> INSERT INTO "blood" VALUES(75,'Thursday',40164,'22:23'); sqlite> INSERT INTO "blood" VALUES(76,'Friday',40165,'09:40'); sqlite> INSERT INTO "blood" VALUES(77,'Friday',40165,'17:24'); sqlite> COMMIT; After this command I have made a select to check if the value have been entered correctly: sqlite> select * from blood; 68|Tuesday|40162|16:28 69|Tuesday|40162|21:25 70|Wednesday|40163|07:16 71|Wednesday|40163|16:30 72|Wednesday|40163|21.35 73|Thursday|40164|08:17 74|Thursday|40164|16:15 75|Thursday|40164|22:23 76|Friday|40165|09:40 77|Friday|40165|17:24 Now, I was curious to see if creating a new record with a standard SQLite function to get the current date (date('now')) the date was going to be created with the same format. And here is the result: sqlite> insert into blood values(99,'Tuesday',date('now'),strftime('%H:%M','now')); sqlite> select * from blood; 68|Tuesday|40162|16:28 69|Tuesday|40162|21:25 70|Wednesday|40163|07:16 71|Wednesday|40163|16:30 72|Wednesday|40163|21.35 73|Thursday|40164|08:17 74|Thursday|40164|16:15 75|Thursday|40164|22:23 76|Friday|40165|09:40 77|Friday|40165|17:24 99|Tuesday|2009-12-24|17:07 sqlite> As you can see the date was inserted in the way I was expecting. I still do not understand why your table is having dates in this format. So here are my questions: Is this a table already existing or records are created with an application?Why dates are created in a different way than standard SQLite format?Could you consider converting your dates in the standard format? Another question to understand better your problem: Specify which Linux distribution and version you are using;Specify which SQLite library version you are using. When you start sqlite3 command you can see the version;Specify how records have been created in that table. If you have additional questions just let me know. Regards. Alberto. ---------------------------------------------------------------------- LuizAmérico · 2009-12-24 18:45 Quote    * Is this a table already existing or records are created with an application?     * Why dates are created in a different way than standard SQLite format? Probably they were created using TSqlite*Dataset component. There's no standard format in sqlite. You can add any value to any field type. This is the format (double values) used in TSqlite*Dataset. This format was chosen so the file can be smaller and consume less memory at runtime. Those data files are supposed to be used by applications using such components. Quote    * Could you consider converting your dates in the standard format? In this case it will not work with TSqlite*Dataset Luiz ---------------------------------------------------------------------- davesimplewear · 2009-12-24 20:02 Hi Alberto, The sqlite3 version is the same as yours, I am using Linux Mint8. I believe the date format is what the rxDbGrid saves. All records are saved by the application. I have used the methods to make the application so that the user only has to enter one field by the keyboard (that is the aim, anyway) and the rest are automatically populated. The filtering is to filter a set spread of dates for a report, instead of printing the whole of the database. I appreciate the effort and assistance that Luiz and yourself are giving and I hope that you and everyone in the Lazarus community has a Merry Christmas and a safe and prosperous New Year. ---------------------------------------------------------------------- davesimplewear · 2009-12-24 22:13 An AHA Ureaka moment, I found that the values from the PickDate form calendars were returning either nil values or values that are the same, thus giving the blank and again no result. This must be a bug in lazarus as delphi controls returned a value for the date controls placed on another form. ---------------------------------------------------------------------- LuizAmérico · 2009-12-25 12:37 QuoteI found that the values from the PickDate form calendars were returning either nil values or values that are the same, thus giving the blank and again no result Try to create two properties DateFrom and DateTo in the form and save the values from TCalendar when the user press the OK button in the form so you can read them after closing the form. Luiz ---------------------------------------------------------------------- davesimplewear · 2009-12-26 23:09 Ok Luiz, I got the query working using your code, but the report only shows the first line, does anyone know how to filter data with lazreport? ---------------------------------------------------------------------- LuizAmérico · 2009-12-27 14:05 I use LazReport with sqlite in my projects without problems. I connect a TDBfrDataset to Tsqlite3Dataset and add a masterdata band in LazReport. It show all records. How many records are returned by your query? (Check RecordCount after calling Open) ---------------------------------------------------------------------- davesimplewear · 2009-12-28 04:56 There must have been a bug in my version of Lazarus, reloaded it and now working OK, all data filters, the report now filters, all good, Thank you all for your help. Regards Dave