hello, is there a way to use views? with MS SQL, views don’t show up at all.
Currently you can only view the contents of a view using getDataSetByQuery (under Database manager in the methods). We are investigating native supports on forms, it is a bit tricky as servoy works primary key based to retrieve data and views often don’t have primary keys.
I have asked for views because of one problem I encountered: I have a table that contains data for a value list (e.g. products). I don’t want to see all of its data though, because some of the products are not sold anymore. So I have a field called “active” in products, that marks active products (the ones I want to see in my value list). Now comes the problem: how do I make the value list show only active products?
I have tried this way: I have a relationship from a global containing “1” referencing the active field in products and use that for the value list. That’s nice, but in find mode, I don’t see anything (in FileMaker happens the same thing, by the way).
I have tried to use a view (select product_id, product where active = 1). But then there is no way to use the view in value list…
Any ideas on the problem?
Thanks.
Currently you can only view the contents of a view using getDataSetByQuery (under Database manager in the methods). We are investigating native supports on forms, it is a bit tricky as servoy works primary key based to retrieve data and views often don’t have primary keys.
Wouldn’t a form type “record view (read only)” or “table view (read only)” be possible? When using a view only these two types are possible and you don’t have to worry about primary keys, because editing is disabled by default… For me that would be useful…
I can use getDataSetByQuery to get the contents of a MS SQL view. The view will return many rows and many columns.
How can I display the resultset in a grid type display on a form, i.e. each column returned in the view will go in each column in the grid. What is the best way to do this? By using a TabPanel or something like that?
Also allow vertical and horizontal scrolling - as could have a large resultset.
Roger
Here’s an example of a method that sends two arguments (query + max rows)
to a global method, but you could also adjust the code and send a dataset or what ever arguments you wish.
1)Create a global method and paste the GLOBALSCRIPT code inside it
2) put a global variable on your form that displays as HTMLarea(display type property)
3) create a method on your form that sends arguments and triggers the global method
//FORMSCRIPT
var query = "SELECT firstname, surname, mobile from contacts where company_id = "+companiesid;
globals.globalOnForm = globals.GLOBALSCRIPT(100,query);
//the last line triggers the GLOBAL SCRIPT, which in return sends HTML code back into the global on your form.
//GLOBALSCRIPT
//receiving arguments
var maxReturnedRows = arguments[0]
var query = arguments[1]
var headerBgColor = '#cccccc'
var rowBgcolorLight = '#faf9f9'
var rowBgcolorDark = '#edeaea'
var dataset = databaseManager.getDataSetByQuery(currentcontroller.getServerName(), query, null, maxReturnedRows);
var HTML = '<html>'+
'<HEAD>'+
'<style type="text/css">'+
' .header{'+
' font-family: Verdana, Verdana, Arial, Helvetica, sans-serif;'+
' font-size: 9px;'+
' background-color: #999999;'+
' border: 12px solid #000000;'+
' color: #ffffff; text-decoration: none;}'+
' .body{'+
' font-family: Verdana, Verdana, Arial, Helvetica, sans-serif;'+
' font-size: 9px;'+
' color: #000000; text-decoration: none;}'+
' </style>'+
'</HEAD>'+
'<TABLE border=1 class="body">';
//create headers
HTML += '<TR class="header" >';
for(var i=1 ; i <= dataset.getMaxColumnIndex() ; i++)
{
HTML += '<TD border=0>'+ dataset.getColumnName(i) + '</TD>';
}
HTML += '</TR>';
//create rows and columns
for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
dataset.rowIndex = i;
//set rowBgColor
if(i%2==0)
{
HTML += '<TR bgcolor='+rowBgcolorLight+'>';
}
else
{
HTML += '<TR bgcolor='+rowBgcolorDark+'>';
}
for(var j=1 ; j <= dataset.getMaxColumnIndex() ; j++)
{
HTML += '<TD border=0>'+dataset[j]+'</TD>';
}
HTML += '</TR>';
}
HTML += '</TABLE>';
return HTML
Don’t know whether this belongs here, sorry if it is in the wrong place.
I had trouble getting the CSS styles in the above example to work after pasting the code from Safari into Servoy on Mac OS X 10.3.3. To work out what was wrong, I pasted the HTML produced by the global method into GoLive which didn’t render the styles either.
But after I retyped the CSS definition it now works! Maybe there were some invisible characters in there.
Hello!
Thanks for the cool method. I am using this as a base for my report generator, but found out one performance issue:
As the number of dataset rows increases, the method slows down dramatically. Here are some statistics:
500 rows → a few seconds
1000 rows → under ten seconds
1500 rows → over a minute
2000 rows → several minutes
So the execution time isn’t linear compared to the amount of rows. The time is consumed in the two nested loops that create the HTML table. My test dataset had 5 columns.
Can this be optimised somehow, it could be a memory allocation thing or something? It seems that when the created string hits a certain size border, the handling becomes really slow. I will do some hacking myself, but if anyone has any pointers, they would be welcome!
Thanks!!
are you sure that you are directly using that method?
And not a modified one that uses a global to generating that html ?
And that global is used in a html field that displays the resulting html?
What place is really slowed down?
If you are using that methods
And you do
application.output(new Date());
at the beginning and the end of that method.
Is it really the method that shows this peformance drop?
(because i don’t think this is the case)
jcompagner:
are you sure that you are directly using that method?
And not a modified one that uses a global to generating that html ?
And that global is used in a html field that displays the resulting html?What place is really slowed down?
If you are using that methods
And you doapplication.output(new Date());
at the beginning and the end of that method.
Is it really the method that shows this peformance drop?
(because i don’t think this is the case)
Yes, I am using quite the same method; identical inside the for-loops, except that I had to replace dataset with dataset.getValue[i,j], because the former gave just “undefined” results. Can this slow down performance? Is there a faster way to iterate through a dataset row by row?
I inserted timestamp output lines to the code, and the results are:
- getDataSetByQuery takes only a few seconds
- local var to global var, to a HTMLfield takes only a few seconds
I created a fancy graph showing the time consumed (in seconds) in the nested for-loops:
This is a simple query with four columns. The created HTML size for 500 rows is about 70 KB, 2500 rows 350 KB.
500 rows took maximum of 15 seconds, 2500 rows over 6 minutes. Our reports could have as much as 4000 rows, which I didn’t even dare to try out.
Most interesting is that if the same report is generated again, with the same query and data, it takes even LONGER to run with a large dataset. Under 2000 rows the repeated run is still faster, probably due to caching.
Developer runs in a 3 GHz Linux workstation, MySQL in a separate 3 GHz server connected with 100 Mbit network.
Any ideas appreciated 
i need to see it
Can you make a solution with one form build on that table with that report data that has that method?
And then send it to me with sample data? (so that i have enough rows to test it)
jcompagner:
i need to see it
Can you make a solution with one form build on that table with that report data that has that method?
And then send it to me with sample data? (so that i have enough rows to test it)
I can confirm the performance issue with dataset > 1000 record when loaded into an html global.
I put a simple invoice report and I noticed that the loading of the data becomes very slow (even on a 2,7 PIV client) when the number of invoices is over 500.
If you also add a method linked with ahref inside the html, it could take a lot to retrieve the data (even if they’re less than 1000 records).
I can also provide a solution with sample data, if needed.
i would love to have an example.
Becuase i want to see what realyl goes slow.
Is it the population of the html area or is it really the loading.
For example if the global you set the data in is not shown anywhere on the screen. Or you even don’t set it in a global or other dataprovider. Is it then also slow?
I have the same problem. I am using HTML to print lists. If there is quite some records (10 pages plus, a few 100 records) it really takes loooong to get to see the results.
I am using the same technique to export records, which in my eyes goes a lot faster. I will verify this further but it looks like the time is taken by showing the html component.
patrick:
I have the same problem. I am using HTML to print lists. If there is quite some records (10 pages plus, a few 100 records) it really takes loooong to get to see the results.I am using the same technique to export records, which in my eyes goes a lot faster. I will verify this further but it looks like the time is taken by showing the html component.
I made some tests, months ago: if I remember well, you’re right.
Btw: I just asked my customer to run a simple test (invoices list html formatted with a method inside the code, year 2004): 945 records, 31 seconds…
Ok, after some debugging I found out what is taking so long in the method.
It is the handling of large strings.
I inserted a row that checks, whenever the length of the created HTML string exceeds 32000 characters, it is cleared. The execution time of the method dropped dramatically. I can now loop through 4000 records in 10 seconds. So it supposedly is not really a Servoy issue, but a Java Virtual Machine issue.
It takes more time to concatenate strings if they are LARGE. This probably has to do with JVM memory management or something…
The solution I could come up with now, is to build the large string in chunks of, say, 32k, and then combine the strings. Gonna try it out now and I can share the code if it works.
Ok, did some optimization with the string handling and now the creation of a 4000 row report takes only 7 seconds instead of a hour or two
The HTML text size is 600kb, and loading that into the HtmlField element takes something like 15 seconds, but it is not a problem, as reports this big will not be requested often.
I used a temporary buffer string that never exceeds the size of 32kb, that is concatenated to the big string and then emptied. Here is the fixed code part of the method:
//create rows and columns
var HTMLtemp = String(32000); // get a temporary string buffer
for( var i = 1 ; i <= MaxRowIndex ; i++ )
{
//set rowBgColor
if(i%2==0)
{
HTMLtemp += '<TR bgcolor='+rowBgcolorLight+'>';
}
else
{
HTMLtemp += '<TR bgcolor='+rowBgcolorDark+'>';
}
for(var j=1 ; j <= dataset.getMaxColumnIndex() ; j++)
{
HTMLtemp += '<TD border=0>'+dataset.getValue(i,j)+'</TD>';
}
HTMLtemp += '</TR>';
// concatenate if temp is almost full
if (HTMLtemp.length > 31500)
{
HTML += HTMLtemp;
HTMLtemp = '';
}
}
// concatenate rest of temp
HTML += HTMLtemp;
Enjoy!
For those who are interested, I found out why the concatenation of long strings is very expensive; whenever doing a “+” operation with String objects, the strings are converted to a StringBuffer, appended, and then converted back to String. This takes a lot of time and our report generator does thousands of string + string operations! Reference article is here,
wonder if Servoy could introduce the StringBuffer object to us
Also, this explains why exporting the same data to a text file is much faster.
jcompagner:
i would love to have an example.
Becuase i want to see what realyl goes slow.
Is it the population of the html area or is it really the loading.For example if the global you set the data in is not shown anywhere on the screen. Or you even don’t set it in a global or other dataprovider. Is it then also slow?
If the global isn’t shown I get more or less the same performances.
If I don’t set the global, it takes 1/3 of the time to complete the method.
I’ll try to put an example together and I’ll let you have you asap.
Thanks
Guys,
Thanks for these results! It’s going to be really helpful to everyone.
GREAT STUFF!
please do
I would love to see an example where it gets slow even without using the string anywhere on screen
(so using purely the method/javascript)