Solved

Delphi 7 - Query MySql

Posted on 2009-04-07
27
530 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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 Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Course of the Month7 days, 20 hours left to enroll

617 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