2094 or less, yes…2095 or more, no. I get an error that says ‘Could not sort records, try ShowAll records’ and the detail portion says ‘java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]The DBMS returned an unspecified error.’ This sort is then followed by a print preview, which in the case of 2095 records says ‘Could not retrieve all data for printpreview’ and the detail portion says ‘java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset’
I was able to accept that a user doesn’t need to be able to view a large number of records at one time, but I can’t accept that a user may be unable to print a report with more than 2094 records, so I really hope there is a way around this.
Using: Servoy 2.2rc2, Java 1.5.0_01, MSSQL Server 2000 SP3, Windows 2000 Server
Steve In LA
Try writing the report as a method using a sql statement and returning the results as html into a global field formatted as html.
You can either print this field on a form (although you may have problems with text/page breaks) or export to a file and open in a browser - all of which can be scripted in a method.
I am already retrieving the records using a sql statement and html is not an option in this case. Although this particular report has no sub-summaries, future reports will, and I assume that sub-summaries won’t work unless the foundset is sorted from within Servoy (as opposed to using ‘ORDER BY ’ in the sql query), so not being able to sort is as critical of a problem as not being able to print.
Here is an example method which does sub summaries and a grand summary. It assumes that you have 2 tables…
orders (with field order_id) and
order_lines (with field order_id, order_line_id, qty and net_price)
// set up initial variables
var mr = 1000;
var qs = 0;
var ns = 0;
var qt = 0;
var nt = 0;
var oi = 0;
// create sql query
var query = 'select order_id, order_line_id, qty, net_price'+
' from order_lines'+
' where 1=1'+
' group by order_id, order_line_id, qty, net_price'+
' order by order_id';
// create dataset
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 1000);
// creates html variable and sets style and col headings
var html = '';
html = '<html><head><style>'+
'th{font-family: Verdana, Arial; font-size: 11pt; color: #000000; font-weight: bold; text-decoration: none;}'+
'td{font-family: Verdana, Arial; font-size: 11pt; color: #000000; font-weight: normal; text-decoration: none;}'+
'a{font-family: Verdana, Arial; font-size: 11pt; color: #000000; font-weight: normal; text-decoration: none;}'+
'</style></head>'+
'<table border=0 width=100% cellpadding=1 cellspacing=1>'+
'<tr bgcolor = #cccccc>'+
'<th align=left col width=40><b>Order ID</b></th>'+
'<th align=left col width=40><b>Order Line ID</a></th>'+
'<th align=right col width=40><b>Qty</th>'+
'<th align=right col width=40><b>Net Amount</th>'+
'</tr>';
// loop through dataset and create rows
for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
dataset.rowIndex = i
oi = dataset[1];
html = html + '<tr><td>'+dataset[1]+'</td><td>'+dataset[2]+'</td><td align=right>'+dataset[3]+'</td><td align=right>'+dataset[4]+'</td></tr>';
qs = qs + dataset[3];
ns = ns + dataset[4];
qt = qt + dataset[3];
nt = nt + dataset[4];
// add subtotal if next line is a new order id
dataset.rowIndex = i+1
if (dataset[1] != oi)
{
html = html + '<tr bgcolor = #dedede><td align=left colspan = 2><b>SUB TOTAL</b></td><td align=right><b>'+qs+'</b></td><td align=right><b>'+ns+'</b></td></tr>';
qs = 0;
ns = 0;
}
}
// add report total and complete html tag
html = html + '<tr bgcolor = #cccccc><td align=left colspan = 2><b>GRAND TOTAL</b></td><td align=right><b>'+qt+'</b></td><td align=right><b>'+nt+'</b></td></tr>'+
'</table></body></html>';
var message = dataset.getExceptionMsg();
if (message)
{
plugins.dialogs.showErrorDialog('ERROR', message, 'Please try again');
globals.html_report = query;
}
else
{
globals.html_report = html;
}
I appreciate your efforts, but your method does not help me for two reasons. First, it uses html, which as stated before is not an option for me in this case. Second and most important, your SQL query only returns 1000 rows, and my problem only begins when more than 2094 rows are returned.
Thanks though,
Steve in LA
Hi,
Would be interested to know why html is not an option?
The only reason it stops at 1000 is because I hard coded it. If you set the variable mr (at top of method) to 10000 and then replace 1000 in the dataset statement as follows…
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, mr);
This should work for much larger data sets.
However it does slow down with say 3000 records (takes about 5 min to run on my PC) - not sure whether this is beacuse I am running in developer or because it takes a long time to loop through large data sets in Servoy? Any ideas anyone???
SteveInLA:
…SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]The DBMS returned an unspecified error.’ This sort is then followed by a print preview, which in the case of 2095 records says ‘Could not retrieve all data for printpreview’ and the detail portion says ‘java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset’
I think we can do nothing about this, its is a database or driver issue, we fire standard SQL to the DB. (its also very strange it happens after 2094 rows/records)
You might consider trying another db driver from inetsoftware.de or easysoft.com
My apologies for the long delay in responding, but I was unavailable last week. After installing the inet driver, I now get this message when returning more than 2099 rows/records. This error occurs after the dataset is loaded into the form and upon calling controller.sort()
java.sql.SQLException: [TDS Driver]Connection was closed from the SQL Server site or network at header (0)
Then when trying to print or preview,
java.sql.SQLException: [TDS Driver]The connection is closed
So as far as this being a driver issue, it sure doesn’t feel like one. Seems the problem is more related to Servoy being able to sort and print a large number of records. I will try a different form to see if it is somehow form-related.
SteveInLA:
…[TDS Driver]Connection was closed from the SQL Server site…
Are you running a recent version of MSSQL? it seems to me its the database, not servoy nor driver (Servoy Only closes connections when shutting down)
I have SQL Server 2000 - and I have a table with 3,623 records in it. I made a form (list view) with 5 columns on it and went into print preview.
It showed all 155 pages of the report almost instantly - with ALL 3,623 records in it.
Sounds like a database setting?
Are you doing aggregates on it (i.e. subsummary parts?) IF SO, try creating a new report without the aggregations and see if you get the same problem.
After more testing, I have more information regarding the nature of this problem. Now this gets a little complicated. Lets take the case where a table contains 10,000 records. If I do a Show All from within Servoy, Servoy loads all the records from the table, but only displays the first 200. Lets call the loaded records the dataset and the displayed records the foundset. If I scroll down to the 200th displayed record, Servoy loads an additional 200 records from the dataset into the foundset. At this point, I can sort and print when the dataset exceeds 2094 with no problems. If I load records into a form from a dataset aquired using databaseManager.getDataSetByQuery, Servoy loads all the records of the dataset AND displays all the records from the dataset. In other words dataset = foundset, and when the dataset/foundset exceeds 2094, I get my error when sorting and printing. What seems especially odd is that if I Show All, then keep scrolling down until the number of displayed records is greater than 2094, I DO NOT get the error when sorting. I will send a sample solution that illustrates this issue.
Thanks to Jan, a solution has been found. I was using loadRecords incorrectly. I was creating a pkdataset from a SQL query using databaseManager.getDataSetByQuery, then using loadRecords(pkdataset). What I should have been doing was simply using loadRecords(query). As Jan pointed out, the manual says regarding loadRecords(pkdataset):
NOTE: This function will work best when the dataset that is returned is
approximately 200 rows (records). This function should not be used if the
returned dataset is too large (as in thousands of rows).
My face is red, but my large reports sort and print now, so today I am happy.