read/display results of query - Mysql

[bowens44]

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

 

[lespaul]

if you name your calculated field you can access it just like you would any other field:

select priority,(avg(datediff(dateresolve,dateoccurred)) [b] as AverageDays [/b] from table group by priority.

Leslie

Have you met Hardy Heron?

 

[bowens44]

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?

 

[whosrdaddy]

maybe show us some code?

one example how to access a field in a query:

....
var AvgDays : Double;

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

Cheers,
Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[bowens44]

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.

 

[towerbase]

I suspect that you are not executing this

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

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

You button's OnClick event handler should look something like this:

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

 

[bowens44]

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.

 

[whosrdaddy]

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!

 

[bowens44]

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.

 

[whosrdaddy]

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!