Mysql Query Replying With Incorrect Value

[ledbelly2142]

I am using the following sequence to query a MySQL db for the Max value of a column. I know the MySQL syntax is correct, the query works with MySQL Admin correctly.

Here is what I am using:

Sequence---

global dbase

global qr

dbase = db.OpenEx("DSN=MySQLdb;UID=ssc01;PWD=xxxxxxx;DATABASE=agtest")

qr = db.Query(dbase,"Select Max(PwrDmdTotal) from agpower081012")

db.CloseQuery(qr)

db.Close(dbase)

end Sequence---

This is a simple query that should result in the variable qr as 281.554

The result I am getting through the ODBC client and DF is 69033044402.00

This result also changes every time I run the sequence.

Seems like I don't have something in the qr = line. What am I doing wrong?

-Greg

[AzeoTech]

What result? qr? qr as well as dbase are just handles for other functions within DAQFactory for traversing through your result set. The handle will change every time you run it and can't be used for anything else. You probably really want to use the db.queryToClass() function instead. This will return an object with the result.

[ledbelly2142]

I'm still doing something wrong, here is what I'm trying:

-------------------------------------

class xSQL

local string maxpower

endclass

global dbase

dbase = db.OpenEx("DSN=xxxx;UID=xxxx;PWD=xxxx;DATABASE=xxxx")

global MaxPowerObject = new(xSQL)

db.QueryToClass(dshandle, "Select Max(PwrDmdTotal) from agpower081012",MaxPowerObject.maxpower)

db.CloseQuery(SQLmax)

db.Close(dbase)

-------------------------------------

My CloseQuery(SQLmax) line is wrong, not sure how to close a QueryToClass line, and I think my dbase variable is not needed.

I don't get any value for MaxPowerObject.maxpower

Thanks,

Greg

[AzeoTech]

First, you don't need to create a class ahead of time. This feature is only used if you want the data to go into a class with other ancillary member variables or member functions. You can just do:

global maxPowerObject = db.QueryToClass(dbase, "select ...")

and it will return a reference to an object that contains the appropriate fields (see the DF user's guide, or do ? maxPowerObject.toJson() to see the contents of the object). You do not need to do db.CloseQuery(). QueryToClass does this internally. Also, not you have a typo as dsHandle doesn't exist in this version of your script.

I'm not sure what it will do with Max() though. That might not work with queryToClass. You might need to use the old db.query() function, but try this first.

[ledbelly2142]

I have some of it working, just not the way I need it....

I get an object back with the following:

---------------------------

global dbase

dbase = db.OpenEx("DSN=xxx;UID=xxx;PWD=xxx;DATABASE=agtest")

global MaxPowerObject=db.QueryToClass(dbase,"Select Max(PwrDmdTotal) from agpower081012")

db.Close(dbase)

----------------------------

For MaxPowerObject I can get MaxPowerObject.FieldName ...which is correct

for MaxPowerObject.RecordCount ... I get 1, which should be correct

But I cant display (variable value) the object with MaxPowerObject ... I see "Object(s)", as well as for MaxPowerObject[0]

I must not be using the object correctly, how can I use it

Thanks,

Greg

[AzeoTech]

What does fieldName return if you do:

? MaxPowerObject.FieldName

in the command alert window?

Or:

? MaxPowerObject.toJson()

[ledbelly2142]

The ? MaxPowerObject.FieldName just turnes blue.

The ? MaxPowerObject.toJson() returns a "null".

Also, if I try the old

global MaxPowerObject=db.Query(dbase,"Select Max(PwrDmdTotal) from agpower081012")

I get an array... not a single max value (and not an object of course). The array did not seem to be in order in the db, so not sure how the Max(PwrDmdTotal) actually returned data.