Solved

Delphi 7 - Query MySql

Posted on 2009-04-07
27
494 Views
Last Modified: 2012-05-06
Hi,

I have a table with this fields:
datain: string
name: string
causa: string


i need to view all record where name = daniele and datain beetwen 01/01/2009 and 07/04/2009 and causa = accettazione

use Delphi 7 and MySql




nome        ! datain     ! causa        !
 

daniele     ! 01/01/2009 ! accettazione !

daniele     ! 20/03/2009 ! reso !

daniele     ! 18/03/2009 ! accettazione !

daniele     ! 16/01/2009 ! accettazione !

daniele     ! 15/01/2009 ! riparazione !

daniele     ! 05/04/2009 ! accettazione !

daniele     ! 01/08/2009 ! rottura !

daniele     ! 01/09/2009 ! accettazione !

daniele     ! 01/10/2009 ! garanzia !

daniele     ! 01/11/2009 ! accettazione !

Open in new window

0
Comment
Question by:danz67
  • 13
  • 7
  • 7
27 Comments
 
LVL 6

Expert Comment

by:bokist
ID: 24085889
you can use some type of Query (Ado, dbExpress, BDE..)

with My_query do
   begin
   Sq.Clear;
   Sql.add('select * from my_table_name')
   Sql.add(' where name like ''daniele%''');
   Sql.add('  and causa like ''accettazione%''');
   Sql.add('  and datain between ''01/01/2009'' and ''07/04/2009''');
   open;
end;

If you are using table then put filter on table.
0
 
LVL 36

Accepted Solution

by:
Geert Gruwez earned 500 total points
ID: 24086404
use parameters

with My_query do
begin
  SQL.Text :=
    'select * from my_table_name where name like :name  '+
    'and causa like :causa and datin between :datain_1 and :datain_2 ';
  Parameters.ParamByName('NAME').AsString := 'daniele';
  Parameters.ParamByName('CAUSA').AsString := 'accettazione';
  Parameters.ParamByName('DATAIN_1').AsDateTime := StrToDate('01/01/2009');
  Parameters.ParamByName('DATAIN_2').AsDateTime := StrToDate('07/04/2009');
   open;
end;
0
 

Author Comment

by:danz67
ID: 24087531
I have try

with My_Query1 do
begin
  SQL.Text :=
    'select * from riparazioni where cliente like :cliente  '+
     'and StatoAssistenza like :statorip and accettazionedata between :datain_1 and :datain_2 ';
  Params.ParamByName('CLIENTE').AsString := 'VOLPI';
  Params.ParamByName('STATORIP').AsString := 'ATTESA ACCETTAZIONE PREVENTIVO';
  Params.ParamByName('DATAIN_1').AsDateTime := StrToDate('01/01/2009');
  Params.ParamByName('DATAIN_2').AsDateTime := StrToDate('07/04/2009');
  open;
end;

Not result view, field accettazionedata it's strind type and not datetime.
0
 
LVL 6

Expert Comment

by:bokist
ID: 24087604
in your description :

I have a table with this fields:
datain: string
name: string
causa: string

therefore
 Params.parambyname('DATAIN_1').AsString := '01/01/2009';
 Params.parambyname('DATAIN_2').AsString := '07/04/2009';
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24087762
you stored the data in the database for a date as a string ? whatever for ?
then you'll need to convert it to a number and compare to that number:




SQL.Text := 

  'select * from ( '+

  '  select t.*, substr(t.datain, 7, 4) + substr(t.datain, 4, 2) + substr(t.datain, 1, 2) Xdatain  '+

  '  from my_table_name as t '+

  '  where name like :name '+

  '    and causa like :causa) as X '+

  'where X.Xdatain between :datain_1 and :datain_2 ';

Parameters.ParamByName('NAME').AsString := 'daniele';

Parameters.ParamByName('CAUSA').AsString := 'accettazione';

Parameters.ParamByName('DATAIN_1').AsInteger := 20090101;

Parameters.ParamByName('DATAIN_2').AsInteger := 20090407;

Open in new window

0
 

Author Comment

by:danz67
ID: 24089064
See error
error.bmp
0
 
LVL 6

Expert Comment

by:bokist
ID: 24089610
perhaps it's worth to try this

1. solution

with My_Query1 do
begin
  SQL.Text :=
    'select * from riparazioni where cliente like :cliente  '+
       'and StatoAssistenza like :statorip and accettazionedata between :datain_1 and :datain_2 ';
     Params.ParamByName('CLIENTE').AsString := 'VOLPI' + '%';
     Params.ParamByName('STATORIP').AsString := 'ATTESA ACCETTAZIONE PREVENTIVO' + '%';
     Params.ParamByName('DATAIN_1').AsString := '01/01/2009';
     Params.ParamByName('DATAIN_2').AsString := '07/04/2009';
  open;
end;

2. solution

with My_Query1 do
begin
  SQL.Text :=
    'select * from riparazioni where cliente like :cliente  '+
       'and StatoAssistenza like :statorip and convert(datetime, accettazionedata) between :datain_1 and :datain_2 ';
     Params.ParamByName('CLIENTE').AsString := 'VOLPI' + '%';
     Params.ParamByName('STATORIP').AsString := 'ATTESA ACCETTAZIONE PREVENTIVO' + '%';
     Params.ParamByName('DATAIN_1').AsDateTime := StrToDate('01/01/2009');
     Params.ParamByName('DATAIN_2').AsDateTime := StrToDate('07/04/2009');
     open;
end;
0
 

Author Comment

by:danz67
ID: 24089975
With this solution

with My_Query1 do
begin
  SQL.Text :=
    'select * from riparazioni where cliente like :cliente  '+
       'and StatoAssistenza like :statorip and accettazionedata between :datain_1 and :datain_2 ';
     Params.ParamByName('CLIENTE').AsString := 'VOLPI' + '%';
     Params.ParamByName('STATORIP').AsString := 'ATTESA ACCETTAZIONE PREVENTIVO' + '%';
     Params.ParamByName('DATAIN_1').AsString := '01/01/2009';
     Params.ParamByName('DATAIN_2').AsString := '07/04/2009';
  open;
end;

result.bmp is result of query

real_records.bmp is list of records in database


result.bmp
real-records.bmp
0
 

Author Comment

by:danz67
ID: 24090068
Sorry for image much big.

This is right imgae of list record in DB
real-records.bmp
0
 
LVL 6

Expert Comment

by:bokist
ID: 24090146
Strange!
First line of the results even not exists in your table ?
Did you try second solution?
0
 

Author Comment

by:danz67
ID: 24090514
With second solution.
error2.bmp
0
 
LVL 6

Expert Comment

by:bokist
ID: 24090863
Ok, I am not familiar with MySql, perhaps convert function is not supported.
The first version not works, because of the date is treated as string.
Therefore you can do following  :  convert DATAIN_1 field  in table riparazioni to datetime type,        
  or  divide string field DATAIN_1 into year,month,day parts and compare parts with sql command.
0
 

Author Comment

by:danz67
ID: 24090996
datain type string have value '01/01/2009' , if convert in Datetime of MySql, value is right?
I can convert field type in Datetime with one command SQL?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 6

Expert Comment

by:bokist
ID: 24091146
As I said earlier, I'm not familiar with MySql,
but you can achieve the goal with string field this way :

with My_Query1 do
begin
  SQL.Text :=
    'select * from riparazioni where cliente like :cliente  '+
       'and StatoAssistenza like :statorip  ' +
       'and (substring(accettazionedata,7,4)+substring(accettazionedata,4,2)+substring(accettazionedata,1,2))  between :datain_1 and :datain_2 ';
     Params.ParamByName('CLIENTE').AsString := 'VOLPI' + '%';
     Params.ParamByName('STATORIP').AsString := 'ATTESA ACCETTAZIONE PREVENTIVO' + '%';
     Params.ParamByName('DATAIN_1').AsString := '20090101';
     Params.ParamByName('DATAIN_2').AsString := '20090407';
     open;
end;

Anyway, first record from your example (02/02/09) will not meet above conditions
 

0
 

Author Comment

by:danz67
ID: 24091349
Sorry but now result is null records view.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24094424
0
 
LVL 6

Expert Comment

by:bokist
ID: 24094576
I have checked links from Geert  and found that both versions are usable with slight modifications:

1. solution

with My_Query1 do
begin
  SQL.Text :=
    'select * from riparazioni where cliente like :cliente  '+
       'and StatoAssistenza like :statorip ' +
       'and (substring(accettazionedata,7,4)+substring(accettazionedata,4,2)+substring(accettazionedata,1,2))  between :datain_1 and          :datain_2 ';
     Params.ParamByName('CLIENTE').AsString := 'VOLPI' + '%';
     Params.ParamByName('STATORIP').AsString := 'ATTESA ACCETTAZIONE PREVENTIVO' + '%';
     Params.ParamByName('DATAIN_1').AsString := '20090101';
     Params.ParamByName('DATAIN_2').AsString := '20090407';  
    open;
end;

2. solution

with My_Query1 do
begin
  SQL.Text :=
    'select * from riparazioni where cliente like :cliente  '+
       'and StatoAssistenza like :statorip and cast(accettazionedata as datetime) between :datain_1 and :datain_2 ';
     Params.ParamByName('CLIENTE').AsString := 'VOLPI' + '%';
     Params.ParamByName('STATORIP').AsString := 'ATTESA ACCETTAZIONE PREVENTIVO' + '%';
     Params.ParamByName('DATAIN_1').AsDateTime := StrToDate('01/01/2009');
     Params.ParamByName('DATAIN_2').AsDateTime := StrToDate('07/04/2009');
     open;
end;

I have successfully tested both versions with your data on MSSQL.
0
 

Author Comment

by:danz67
ID: 24094947
You have write:
I have successfully tested both versions with your data on MSSQL.

Sorry but in MySQL not result view :(
0
 

Author Comment

by:danz67
ID: 24097328
Any other solution for me?
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24105047
for your information, the links are for mysql ...

what does this query give you ?
just open it in a dbgrid to check
especially check the X field

SQL.Text :=
    'select substring(accettazionedata,7,4)+substring(accettazionedata,4,2)+substring(accettazionedata,1,2) As X, A.*
    'from riparazioni A  ';


0
 

Author Comment

by:danz67
ID: 24105380
i know that the links are for Mysql.

I need a complete sample.....if is possible.

Thanks.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24105383
what does the query give ?
0
 

Author Comment

by:danz67
ID: 24105501
?
0
 

Author Comment

by:danz67
ID: 24105755
We review the situation.

the actual data present nela database are present in attchment image "Actual Data right"

resul of query in attchment image "List data of query"





begin

with MyQuery1 do

begin

SQL.Text :=

    'select substring(accettazionedata,7,4)+substring(accettazionedata,4,2)+substring(accettazionedata,1,2) As X, A.accettazionedata, A.numero '+

    'from riparazioni A where idcliente = :idcliente  '+

       'and StatoAssistenza = :statorip and accettazionedata between :datain_1 and :datain_2 ';

     Params.ParamByName('idCLIENTE').AsInteger := 506;

     Params.ParamByName('STATORIP').AsString := 'ATTESA ACCETTAZIONE PREVENTIVO';

     Params.ParamByName('DATAIN_1').AsString := '01/01/2009';

     Params.ParamByName('DATAIN_2').AsString := '19/03/2009';

     open;

end;
 

end;

Open in new window

lis.bmp
resul-query.bmp
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24106929
well as long as you don't get a decent number in X this won't work

what does this query give ?

select substring(accettazionedata,1,16) As X, A.accettazionedata from riparazioni A


you need to be able to convert correctly before proceeding
0
 

Author Comment

by:danz67
ID: 24107416
If i convert field type accettazionedata from varchar in date maybe is better?


0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24157137
that would be better, yes
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now