Solved

Delphi 7 - Query MySql

Posted on 2009-04-07
27
505 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 37

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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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 37

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
 
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 37

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 37

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 37

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 37

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 37

Expert Comment

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

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

786 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