• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

Delphi 7 - Query MySql

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
danz67
Asked:
danz67
  • 13
  • 7
  • 7
1 Solution
 
bokistCommented:
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
 
Geert GruwezOracle dbaCommented:
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
 
danz67Author Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
bokistCommented:
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
 
Geert GruwezOracle dbaCommented:
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
 
danz67Author Commented:
See error
error.bmp
0
 
bokistCommented:
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
 
danz67Author Commented:
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
 
danz67Author Commented:
Sorry for image much big.

This is right imgae of list record in DB
real-records.bmp
0
 
bokistCommented:
Strange!
First line of the results even not exists in your table ?
Did you try second solution?
0
 
danz67Author Commented:
With second solution.
error2.bmp
0
 
bokistCommented:
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
 
danz67Author Commented:
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
 
bokistCommented:
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
 
danz67Author Commented:
Sorry but now result is null records view.
0
 
Geert GruwezOracle dbaCommented:
0
 
bokistCommented:
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
 
danz67Author Commented:
You have write:
I have successfully tested both versions with your data on MSSQL.

Sorry but in MySQL not result view :(
0
 
danz67Author Commented:
Any other solution for me?
0
 
Geert GruwezOracle dbaCommented:
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
 
danz67Author Commented:
i know that the links are for Mysql.

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

Thanks.
0
 
Geert GruwezOracle dbaCommented:
what does the query give ?
0
 
danz67Author Commented:
?
0
 
danz67Author Commented:
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
 
Geert GruwezOracle dbaCommented:
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
 
danz67Author Commented:
If i convert field type accettazionedata from varchar in date maybe is better?


0
 
Geert GruwezOracle dbaCommented:
that would be better, yes
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 13
  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now