sqlite, zeos and delphi urgent help

[vvv]

Hi guys.

I use Delphi 2007, ZEOSDBO-6.6.6-stable and SQLite3. I successfully create database and write data in database. In database I have one table that have two columns, in the first column I write date YYYY-MM-DD HH:MM:SS and in the seccond message. I want to use Query to select date and messages from start date to end date that is specified by user. My question is how to compose my query. I try to use SQL command SELECT to select the data that satisfies the criterion but I dont know how. And second thing is for SELECT command. I read that the result of a SELECT is zero or more rows of data where each row has a fixed number of columns. My question is how to get the data returned by Query with SELECT command into array in delphi code.

From this link I read for Important Properties and Methods for ZConnection control.
http://zeos.firmos.at/kb.php?mode=article&k=6

But there is no explanation for Important Properties and Methods for other zeos controls. Where can I find this explanation and also some simple examples for reading and writing in database using zeos and sqlite.

Thanks in advance.
Regards.

[trupka]

vvv, I'm not sure where to start.. do you have any experience in SQL, Delphi and databases? I will gladly give you some hints and directions but don't want to write stuff you already now...

[vvv]

I have some experience. I create my database and write data in the database. Everything is OK.
I read that the result of a SELECT is zero or more rows of data where each row has a fixed number of columns.

Command:='SELECT Date, FROM MyTable';
ZQuery1.SQL.Clear;
ZQuery1.SQL.Text := Command;
ZQuery1.ExecSQL;

If i execute the query above the result is more rows of data where each row has a fixed number of columns. How to get this data in delphi array. My goal is to iterate with for loop in the returned data.

Thanks.

[trupka]

after you open query, you can manipulate with data directly whith standard dataset methods (next, first, prior, insert, update etc.. - see delphi help).
Unfortunately, Delphi 2007 Help system sucks - go search for D7 old winhelp files, they are much beter and (still) useable with D2007.
But if you need data inside array, do something like:

Code: Select all

ZQuery1.Clear;
ZQuery1.Text := 'select field1, field2 from table';
ZQuery1.Open;
Zquery1.First; // not really necessary
while not ZQuery1.EOF do
begin
  SomeArrayElement := ZQuery1.fieldByName('field1').asString;
  SomeOtherArrayElement :=    ZQuery1.fieldByName('field2').asInteger;
ZQuery1.Next;
end;

btw, may I ask, is english your native language? Your name is often in countries where I live...

[vvv]

Thanks for your answer.
English is not my native language.

Next thing.
I create table with this query.
'CREATE TABLE IF NOT EXISTS "Events"("Date" VARCHAR(50), "Message" VARCHAR(50))'
In column Date I store date in format YYYY-MM-DD HH:mm:ss:msec as text. Data from the database are shown in DBGrid control. My goal is when the user specifies date range Start Date and End Date to select the data in that date range and show in the same DBGrid control, like filtering. But I need to make iteration in the selected data and to make some operations with date for example time difference between two events.

Any ideas, please.

Thanks in advance.

btw in which country do you live?

[trupka]

If I understood problem, SQL might be:

Code: Select all

 select date, message from events where date between :start_date and :end_date

then you can do interations as shown before..

SQLLite date/time support is very limited and error prone, beware...

btw, I'm from HR.

[Pitfiend]

mmm... A common SELECT statement must be in the form

Code: Select all

SELECT field1, field2, ..., fieldN
FROM tablename
WHERE some_condition

Anything you put in the field definition will become a field of your result table (take in mind that any query becomes a logical table), any field can be fetched as a .fieldbyname('fieldN') query function, converted to any type you want (they can be .AsString, .AsInteger, .AsDateTime, .AsFloat, just check the help for this), after that you can manipulate the query fields as any table field that you can assign to an appropiated variable.

Also you need to be aware that SQLite has a very flexible datatype system, take a look here.

For your information about sql syntax as understood by SQLite, look here.