Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Delphi 7 - Query MySql

Posted on 2009-04-07
27
Medium Priority
?
542 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 38

Accepted Solution

by:
Geert Gruwez earned 1500 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
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!

 
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 38

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 38

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 38

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 38

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 38

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 38

Expert Comment

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

972 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