Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

read/display results of query - Mysql

Status
Not open for further replies.

bowens44

Programmer
Jan 29, 2009
10
US
I am execute the following query:

select priority,(avg(datediff(dateresolve,dateoccurred)) from table group by priority.

if I execute this in mysql I get:
+----------+--------------------------------------------+
| priority | avg(datediff(date_resolved,date_occurred)) |
+----------+--------------------------------------------+
| 1 | 0.0000 |
| 2 | 5.0604 |
| 3 | 0.9837 |
| 4 | 0.0564 |
| 5 | 0.2000 |
+----------+--------------------------------------------+

When I execute this query with a query control in Delphi , how do I get the results of 'avg(datediff(date_resolved,date_occurred)) ' ? I am able to get the results under priority but noot the calculated result of avg().

thanks
 
if you name your calculated field you can access it just like you would any other field:
Code:
select priority,(avg(datediff(dateresolve,dateoccurred)) [b] as AverageDays [/b] from table group by priority.

Leslie

Have you met Hardy Heron?
 
Hi Leslie,
Thanks for your response. I'm still having a problem accessing this data. The query executes as it shouold but when I attempt to access the field 'AverageDays' , I am told field does not exist.

Does the field have to exist in the table in the database?

 
maybe show us some code?

one example how to access a field in a query:
Code:
....
var AvgDays : Double;

...
   AvgDays := ADOQuery1.FieldByName('AverageDays').AsFloat;
...

Cheers,
Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!
 
this is the code that I am using to build the query

datamodule2.mysqldataset2.active:=false;
datamodule2.mysqldataset2.sql.clear;
datamodule2.mysqldataset2.sql.add('select ');
datamodule2.mysqldataset2.SQL.add('priority,avg(datediff(date_resolved,date_occurred)) as AverageDays ');
datamodule2.mysqldataset2.SQL.add('from s_data group by priority');
datamodule2.mysqldataset2.active:=true;


have this in a button just see if I can read the data.

form1.caption:=datamodule2.mysqldataset2.fieldbyname('AverageDays').asstring;

I get an error staying field : field 'AverageDays' not found.
 
I suspect that you are not executing this
Code:
datamodule2.mysqldataset2.active:=false;
datamodule2.mysqldataset2.sql.clear;
datamodule2.mysqldataset2.sql.add('select ');
datamodule2.mysqldataset2.SQL.add('priority,avg(datediff(date_resolved,date_occurred)) as AverageDays ');
datamodule2.mysqldataset2.SQL.add('from s_data group by priority');
datamodule2.mysqldataset2.active:=true;
before you are doing this
Code:
form1.caption:=datamodule2.mysqldataset2.fieldbyname('AverageDays').asstring;

You button's OnClick event handler should look something like this:
Code:
procedure TForm1.Button1Click(Sender: TObject);
begin
  datamodule2.mysqldataset2.active:=false;
  datamodule2.mysqldataset2.sql.clear;
  datamodule2.mysqldataset2.sql.add('select ');
  datamodule2.mysqldataset2.SQL.add('priority,avg(datediff(date_resolved,date_occurred)) as AverageDays ');
  datamodule2.mysqldataset2.SQL.add('from s_data group by priority');
  datamodule2.mysqldataset2.active:=true;
  form1.caption := datamodule2.mysqldataset2.fieldbyname('AverageDays').AsString;
end;
What happens if you try the above?

Andrew
Hampshire, UK
 
Hi Andrew,
Actually , I was executing the query before hitting the button to update the form caption. I was doing it in another button. It seems to execute alright, I have a datasource and datagrid attached to the query, I see the field 'priority' listed in the grid.

the datagrid looks like this:

priority |
+----------+----------------------------------+
| 1 | |
| 2 | |
| 3 | |
| 4 | |
| 5 | |

but when I attempt to access 'AverageDays' I'm told the field doesn't exist.

(BTW, I did paste your code into the click event of a button with the same result)

thanks for your help.
 
some questions:

what for query component are you using? TADOQuery?
what is the version of the MySQL server?
what version of ODBC driver are you using?



-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!
 
I am beginning to suspect that is were my problem lies.

I'm not using ODBC, I am using a freeware query component in conjunction with libmysql.dll.

I have been hoping to accomplish this with out OBDC or BDE. As long as I don't use calculated fields on the mysql side, everything is great.

 
I use ADO in combination with 3.51/5.01 ODBC drivers for all my MySQL projects without any problem. If you really want to avoid the ODBC driver, I can recommend ZEOSlib to directly access MySQL.

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top