querytoclass /string variable

[polilies]

Hi there!

i can't execute querytoclass() function with string variable. DF gives an error something like this :

"1001 Invalid ODBC record handle provided: SQLexport Line 12 - Uncaught error in sequence SQLexport "

may you explain for me?

private.bit = 0
private string day =(FormatDateTime("%d",SysTime()))
private string hour =(FormatDateTime("%H",SysTime()))
private string sqlscript = "select * from enerji3 where day="+day+" and hour="+hour
private dbase 
private qr 
//////QUERY//////////
dbase = db.Open("energy")
delay(0.1) 
qr = db.QueryToClass(dbase,sqlscript) //it's not working with "querytoclass". but it's fine with "query" function.  
delay(0.1) 
if(!db.IsEOF(qr)) 
   private.bit=0
else
   private.bit=1
endif 
db.CloseQuery(qr) 
db.Close(dbase)
///////////QUERY/////////
global  nexttime = floor(systime()/3600)*3600+3600 

if(private.bit == 1)   

   v.year =strtodouble(FormatDateTime("%y",SysTime()))

   v.month = strtodouble(FormatDateTime("%m",SysTime()))

   v.day = strtodouble(FormatDateTime("%d",SysTime()))

   v.weekday = strtodouble(FormatDateTime("%w",SysTime()))

   v.yearday = strtodouble(FormatDateTime("%j",SysTime()))

   v.hour = strtodouble(FormatDateTime("%H",SysTime()))

   registry.Tkw = IS[0]

   beginexport(enerji)

   delay(0.1)

   endexport(enerji)
endif

"IsEOF()" and the other query function. what do they mean? i assune something but need to know right one!

[AzeoTech]

I'm not quite sure. First, with queryToClass, you don't need any of the record set stuff, so no isEOF, no closeQuery. QueryToClass returns an object with the full result of the query, so you don't have to navigate through it. As to why queryToClass fails, it might have something to do with the query itself, but I'd really have to see the code exactly as it appears when it fails.

OK, to explain:

Doing just Query() returns what is called a record set. The record set is a group of records that are the result of the query. You have to use all those other functions to then retrieve values from the record set and navigate through it. Then, when you are done, you have to close the record set, or you leak memory. Also, despite the functions available, most databases support forward only record sets, meaning you can only move forward through it, not back.

isEOF() is a function that returns true if you've reached the end of the file.

QueryToClass() does everything for you. It gets the record set, then reads all the values in it and sticks the entire result into an object, which is basically just a structure with a bunch of arrays, one for each field in the result. This is MUCH, MUCH faster than using the Query() function, and much easier. I always use queryToClass and never use Query. I can't think of a good reason to use query() now that queryToClass() is available, though maybe for some of the more obscure queries, or totalizing queries (like SELECT count(*) from x)

[polilies]

Hi;

it is solved when i removed the "closeQuery()" function.Works fine now. I understood what "querytoclass"

exactly mean and use. Thank you so much again. See you.

[polilies]

Hi!

Another qestion about "querytoclass" function so cause of that i'm writing it under this post.

A few time ago i start to use the "querytoclass" function and it's pretty fast and handy. now, i need something

to find max and min points which i queried values. Firstly i use this script to profit Mysql functions :

 queryscript1 = "select MAX(KW) from enerji3 where Yearday="+sday+" and hour between "+firsttarif+" 

and "+secondtarif   qr1 = db.QueryToClass(dbase,queryscript1)

with this code i get no fault message from DF. But also there is no data in "qr1.KW". If i erase "MAX()" fucntion

from the query, i get all values which i ask for. and also there is no max() or MIN() function in DF. Maybe

NUMROWS() function usable for my query but not every project. Do you have something about it? Thank you

[AzeoTech]

First, there is a min/max function in DAQFactory. You can just do: max(qr1.kw) after doing your query. But, it is more efficient usually to let the database do it. I'm not exactly sure what it will return, but you can use the fieldNames property to find out what the names of the fields in the returned object are. There is one for the number of records too.

[polilies]

You're right! when i check the name of field which query object has one name with "qr.MAX(KW)" i did not

know about "fieldNames" property so thanks for information about it. But there is still something wrong! when

i query with Mysql workbench application i'll get one value as like this photo

so i know there is a value which real. As the same time i use the same script for query in DF and i get no

response or null i don't know. then i changed the script without

MAX() function, query turns back with captures DF. I can't find

where am i doing wrong thing. Can you help. Thank you. Here is my script

private dbase = db.Open("janitza")   
delay(0.1)
private string str = "select MAX(KW) from enerji3 where Mounth = 4 and Day = 2 and Hour Between 17 and 19"
global qr = db.QueryToClass(dbase,str)
delay(0.1)
global deger = qr.MAX(KW) 
delay(0.1)
db.Close(dbase)

and without Max() function

private dbase = db.Open("janitza")   
delay(0.1)
private string str = "select KW from enerji3 where Mounth = 4 and Day = 2 and Hour Between 17 and 19"
global qr = db.QueryToClass(dbase,str)
delay(0.1)
global deger = qr.KW 
delay(0.1)
db.Close(dbase)

[AzeoTech]

Alas, despite the fact that max(kw) shows in the fieldNames list, you can't really access it because the () are invalid characters. You are going to have to adjust your query to name the field result something else using an alias. Usually this is done with the "AS" keyword in the select, but check your database's SQL documentation.

[polilies]

Okey i'll look for that cause i don't know pretty good MYsql as u understood. i've finished the project in another

way (more scripty way ) but we've an alternative that DF's Max() function. i'll try. Thanks.

[polilies]

Heyo! i've just discover "AS" expressions working style.. Here :

private string str = "select [u]MAX(KW) AS KW[/u] from enerji3 where Mounth = 4 and Day = 4 and Hour 

Between 17 and 20"

so it worked. i can ask for it as original column name.

global deger = qr.KW