sqlite statement not working!!

[davesimplewear]

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]

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]

    * 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.

    * Could you consider converting your dates in the standard format?

In this case it will not work with TSqlite*Dataset

Luiz

[davesimplewear]

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]

 
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]

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

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]

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]

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]

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