Selecting from sql and displaying in table

[Andreschrosa]

I'm making a history page in my application, which will allow the user to set a starting and ending date for a given equipment, and display the data stored in the sql database by that equipment.

I'm not sure the sql query is working (as I cant seen to get results from the querytoclass function, possibly I'm using it wrong). It also dosent display on the table.

How you add various rows in the table component with a addcolumn command?

 
private handle
global string sql
private result

handle = DB.Open("DSLeituras","powerview","pwv")
sql = 'Select Thetime, Vm from Leituras order by Thetime'
//result = db.Query(handle, sql)
result = DB.QueryToClass(handle, sql, 'result')
//? result.Vm
page.Historico.Component.tabelahistorico.ClearColumns()
//page.Historico.Component.tabelahistorico.AddColumn(formatdatetime(%c, result.Thetime), 'Data')
page.Historico.Component.tabelahistorico.AddColumn(result.Vm,'Vm')
DB.Close(handle)

[AzeoTech]

OK, you have a few problems here:

1) As far as your concerned, QueryToClass() should only have two parameters, in your case (handle,sql). The third parameter is for if you have any already instantiated class with member functions and want to put the data into it so those member functions can work on it. Its advanced and undocumented at present and by specifying 'result', the function is then not returning a class instance into result as you expect, thus the reason you don't see anything. So, just make it result = db.querytoclass(handle,sql)

2) In AddColumn(), the first parameter also needs to be a string, so: AddColumn('result.vm,'Vm'). If you don't put it in quotes, it looks at the contents of result.vm for what it should use, much the same way you use the sql variable in the QueryToClass() function.

I can understand why this last point sometimes confuses people as DAQFactory is minorly inconsistant with this. The beginseq/endseq function and corresponding ones for logging, export and pid do not require quotes around the sequence/logging/export/pid name, though this will likely change in 6.0. I believe there are a couple other spots where this is also true, but in general, especially when you are filling in a parameter of a component dynamically from script, if you want to specify the contents exactly, you should use quotes, but if you have a variable with the contents you would not use quotes. So, this:

page.history.component.tablehistory.addcolumn("result.Vm","Vm")

and

private string col = "Result.Vm"
page.history.component.tablehistory.addcolumn(col,"Vm")

do the same thing, but:

page.history.component.tablehistory.addcolumn(result.vm,"Vm")

will not.

3) Result is a private variable and therefore only accessible from within the sequence. The table is not going to be able to access the variable. You need to make result a global, or better yet, use another variable and set the table to always display that variable so you don't have to dynamically add columns to the table, then just assign the value of result.vm to that variable. So, if the global variable name was coldata, you could simply do:

coldata = result.Vm

Perhaps this is another point of confusion: tables (and for that matter graphs), don't work like Excel where you fill cells with contents and it works off those contents. Instead, you tell the table or graph what you want to plot, and with every screen refresh it goes to those variables or channels and gets the data fresh. This is why you can put calculations directly into tables and graphs and you don't have to create temporary variables for the results of those calcs. This is also why a new graph or table will automatically display all the historic data even if it is created after some data has been accumulated.

[Andreschrosa]

Ok, all that makes sense now, thanks.

But still there is something I forgot to mention:

I tried db.QueryToClass with two parameters but it returns error:

O1004 Unable to run query: Historico Line 9 - Uncaught error in sequence Historico

private handle
private result
global string sql

handle = DB.Open("DSLeituras","powerview","pwv")
sql = "Select Vm from Leituras"
result = DB.QueryToClass(handle, sql)
page.Historico.Component.tabelahistorico.ClearColumns()
page.Historico.Component.tabelahistorico.AddColumn('result.Vm','Vm')
DB.Close(handle)

Also, how coult I use formatdatetime to get the time from the sql db to display as datetime in the table? I think it will be like this:

//page.Historico.Component.tabelahistorico.AddColumn(formatdatetime('%c, result.Thetime)', 'Date')

[AzeoTech]

Unable to run query usually means there is a problem with your SQL syntax. SQL usually requires a ; at the end, but in ODBC often does not. It depends on the driver. Also, make sure you have a field called Vm and table called Leituras. Not sure if it is case sensitive. Again it probably depends on the database.

You have the right idea on the getting the date, but you have to be careful about your quotes. You do not have matching quotes. You also need quotes inside the string you are passing, so you need to use double quotes as well:

page.Historico.Component.tabelahistorico.AddColumn("formatdatetime('%c', result.Thetime)", 'Date')

Again, result is private in your sequence so this won't work anyway. Make result Global, or use a separate global string variable as I said before.

[Andreschrosa]

Thanks, that worked =)

It really was a SQL syntax error.

[polilies]

Hi DF Team;

i've a syntax problem like "Andreschrosa" i think. The MSSQL 2008 owner had created fields which tables have column names with "[]". i remember from my last post DF don't turn back with any result when i use "()" in the sql string. May it cause something like that?

i want to share a problem with MSSQL ODBC connection which solved. the ODBC user can't access database with "system dsn".when i change it to "user dsn" from ODBC it worked well enough. The "o1002" error gone.

i'll post my string tomorrow cause i'm working a trial version of DF in another PC. it's a raporting job. We've some electrcty analizor datas like voltage,date,max and min points etc.. and it seems there is one table which i have to query and it's pretty huge. Do u know any idea about querying a database fast enough like this databes?

thank you so much ..see you..

[AzeoTech]

I'm pretty sure that DAQFactory won't be able to query for any fields with non alpha-numeric characters (and perhaps the underscore).

As for the speed of the query, if you use queryToClass() it should run quite quickly. I don't know what "huge" is, nor how fast you need it to run, so can't really say if it will do the job. I'd say just try it. The speed is largely data transfer. Any sort of "WITH" or other filtering sql statements execute at the database, not DAQFactory so the time required to do that is outside of DAQFactory. If you do filter, you will reduce the data transfer and thus the DAQFactory portion will run faster. In general, it will always be faster to filter in your SQL statement than to bring the entire table into DAQFactory and filter it there.

[polilies]

yes you're right i've found that the error cause from "turkish" field names like "ş" and "

[AzeoTech]

Yes, you are correct. You can use aliases to convert field names that would be invalid in DAQFactory to names that would work, thus saving you from having to edit your database table.