Recent

Author Topic: sqlite statement not working!!  (Read 21010 times)

davesimplewear

  • Sr. Member
  • ****
  • Posts: 307
    • Davids Freeware
sqlite statement not working!!
« on: December 20, 2009, 12:51:23 am »
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

Quote
procedure 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;
All things considered insanity seems the best option

bluewolf

  • New Member
  • *
  • Posts: 24
Re: sqlite statement not working!!
« Reply #1 on: December 20, 2009, 02:58:57 pm »
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.
Alberto

davesimplewear

  • Sr. Member
  • ****
  • Posts: 307
    • Davids Freeware
Re: sqlite statement not working!!
« Reply #2 on: December 22, 2009, 01:48:29 am »
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.
All things considered insanity seems the best option

LuizAmérico

  • Sr. Member
  • ****
  • Posts: 458
Re: sqlite statement not working!!
« Reply #3 on: December 22, 2009, 01:59:00 am »
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

  • Sr. Member
  • ****
  • Posts: 307
    • Davids Freeware
Re: sqlite statement not working!!
« Reply #4 on: December 22, 2009, 05:07:26 am »
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?

Quote
procedure 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; 
All things considered insanity seems the best option

bluewolf

  • New Member
  • *
  • Posts: 24
Re: sqlite statement not working!!
« Reply #5 on: December 22, 2009, 07:39:39 pm »
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.
Alberto

davesimplewear

  • Sr. Member
  • ****
  • Posts: 307
    • Davids Freeware
Re: sqlite statement not working!!
« Reply #6 on: December 22, 2009, 09:43:11 pm »
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
All things considered insanity seems the best option

LuizAmérico

  • Sr. Member
  • ****
  • Posts: 458
Re: sqlite statement not working!!
« Reply #7 on: December 22, 2009, 09:50:44 pm »
Try removing the quotes around the dates:

'select * from blood where readDate >= ' + FloatToStr(pickDate.cdFrom.DateTime)  + ' and readDate <= ' + FloatToStr(pickDate.cdTo.DateTime);

davesimplewear

  • Sr. Member
  • ****
  • Posts: 307
    • Davids Freeware
Re: sqlite statement not working!!
« Reply #8 on: December 23, 2009, 12:31:53 am »
Hi Luiz,

I tried that and the report came up blank!

Regards
Dave
All things considered insanity seems the best option

LuizAmérico

  • Sr. Member
  • ****
  • Posts: 458
Re: sqlite statement not working!!
« Reply #9 on: December 23, 2009, 01:23:51 am »
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

  • Sr. Member
  • ****
  • Posts: 307
    • Davids Freeware
Re: sqlite statement not working!!
« Reply #10 on: December 23, 2009, 06:46:18 am »
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.
All things considered insanity seems the best option

LuizAmérico

  • Sr. Member
  • ****
  • Posts: 458
Re: sqlite statement not working!!
« Reply #11 on: December 23, 2009, 10:48:56 am »
This date value is date plus time value.

Try FloatToStr(Trunc(pickDate.cdFrom.DateTime))

or

IntToStr(Trunc(pickDate.cdFrom.DateTime))

bluewolf

  • New Member
  • *
  • Posts: 24
Re: sqlite statement not working!!
« Reply #12 on: December 23, 2009, 11:46:27 am »
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.
« Last Edit: December 23, 2009, 12:17:13 pm by bluewolf »
Alberto

davesimplewear

  • Sr. Member
  • ****
  • Posts: 307
    • Davids Freeware
Re: sqlite statement not working!!
« Reply #13 on: December 23, 2009, 10:04:39 pm »
Hi Luiz,

I tried the FloatToStr(Trunc()) method the result was a blank report.

All things considered insanity seems the best option

LuizAmérico

  • Sr. Member
  • ****
  • Posts: 458
Re: sqlite statement not working!!
« Reply #14 on: December 24, 2009, 12:48:30 am »
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.

 

TinyPortal © 2005-2018