ODBC turns back with too much data

[polilies]

Hi,

Now i'm querying an MSSQL database which stores 380 analizors 5 minute interval polling values from them. So

there is approximately 20 GB data.

when i ask this sentences to ODBC;

"select TOP(1) [top aktif guc demand] as maksguc,gun,ay,yil,saat,dakika,saniye from analizor where [top aktif guc demand] = (select MAX([top aktif guc demand]) from analizor where gun BETWEEN '13' and '15' and deviceId = '"+Rap1DevList[x]+"')""

it gives me many data which i didn't imagine cause the result is different in MSSQL Management Studio; just one row..

e.q. NumRows(qr1.gun) -->362

ps: all fields in database defines "nvarchar", casuse of that the number values in '' characters.

[AzeoTech]

Its possible that DAQFactory can't handle this query, at least not with QueryToClass(). This is because in order for queryToClass to work, it has to do two queries. First it edits your query so it can get a count(), then it does the actual query. Try using the regular Query() function instead.

[polilies]

I understood, now i'm trying it via "Query" there is something wrong i think! DF gives me an alert.

? db.Field(qr1,"maksguc")

O1001 Invalid ODBC record handle provided

And My code is something like this,

global qr1 = db.Query(dbase,"select TOP(1) [top aktif guc demand] as

maksguc,gun,ay,yil,saat,dakika,saniye from analizor where [top

aktif guc demand] = (select MAX([top aktif guc demand]) from

analizor where gun BETWEEN 13 and '15' and deviceId = '"+Rap1DevList[x]+"')")

delay(0.01)

private counter = 0

var.strMaksGucTar = doubletostr(db.Field(qr1,"gun"))+"/"+doubletostr(db.Field(qr1,"ay"))

+"/"+doubletostr(db.Field(qr1,"yil"))+""+doubletostr(db.Field(qr1,"saat"))+

":"+doubletostr(db.Field(qr1,"dakika"))+":"+doubletostr(db.Field(qr1,"saniye"))

MaksGucTar.Addvalue(var.strMaksGucTar)

MaksGucGec = db.Field(qr1,"maksguc")

MaksGuc.AddValue(MaksGucGec)

db.CloseQuery(qr1)

i didn't use any while statement cause "db.Field(qr1,"maksguc")" is empty or it should gives me only one row? The values in database are defines at "nvarchar" so is it about ODBC turning back with string values? i'll try it in float defined field database afternoon.

[polilies]

Hi DF team;

do i have to use db.query with db.open database fucnction? or may i use db.query with db.OpenEx database function.

I've two database, one is MSSQL 2005 wich DF can connects via db.Open (windows authentication) fucntion and one is MSSQL2008 wich DF connects db.OpenEx (DSN,user,pssword/authentication), in 2005 my query works fine but turns back with "NaN" values and in 2008 db.OpenEx line script works but the line of db.Query function

gives error like this

"C1086 One of the parameters was empty: Ek1Query Line 1 - Uncaught error in sequence Ek1Query"

so i locked. non of them gives me data which i ask.

querytoclass function was pretty usefull,easy and free of problem till turned back too many data! do u have something which can help me.

global MaksGucGec
global MaksGuc
global string deneme
global dbase = db.Open("medas")
delay(0.01)
global qr1 = db.Query(dbase,"select reaktifenerji,gun,
ay,yil,saat,dakika from  YunakTRA where import = 
(select MAX(reaktifenerji) from YunakTRA where ay = 5 and 
gun = 12 and saat = 5)")	   
delay(0.01)
var.strMaksGucTar = doubletostr(db.Field(qr1,"gun"))+"/"+doubletostr(db.Field(qr1,"ay"))+
"/"+doubletostr(db.Field(qr1,"yil"))+""+doubletostr(db.Field(qr1,"saat"))+
":"+doubletostr(db.Field(qr1,"dakika"))
MaksGucTar.Addvalue(var.strMaksGucTar)
MaksGucGec = db.Field(qr1,"reaktifenerji")
MaksGuc.AddValue(MaksGucGec)
db.CloseQuery(qr1)
db.Close(dbase)

[AzeoTech]

Yes, you always have to use Open or OpenEx to open the connection to the ODBC database before you can do anything. Your script looks ok, but I'm guessing that either qr1 is coming back as 0, meaning it couldn't perform the query. Its also possible that the result set is empty, though that would likely give a different error. You should, at minimum check db.isEOF(qr1) to check if the result set is empty.

I'd also start simple. Get it working with a simple select, something like:

select reaktifenerji,gun,ay,yil,saat,dakika from YunakTRA Limit 1

and see if you can retrieve any data using query().

I should add that the issue with queryToClass() wasn't the amount of data you were requesting, but rather the complexity of the query. In order to work with any sort of speed, QueryToClass() has to find out how many records will be coming back before it actually gets the records. To do that, it substitutes Count(*) in your SELECT statement. This substitution isn't designed to handle complex SELECT statements (namely ones with more than one "SELECT" in them). It was a trade off, I suppose. QueryToClass() was designed to be easy and fast, and in order to be fast, we had to make it so it doesn't work with every query. Since there is a separate Query() function as a fallback, this decision was made.