Printing: 5 solutions but unfortunately NO WORKING SOLUTION!

Classic Servoy
1

I have already successfully built a form printing about 2500 record from DETTCONTSTRU table (see diagram below) with two very simple selections directly on the SAME table. The working implementation is very simple:
Code sample:
controller.find();
dets_vltz = param1;
dets_ag_competenza = param2;
controller.search()
Et voilà…Hurrah!..because this was a problem before!

Now I have to print about 1800records or 700records from same DETTCONTSTRU table based on selections from STRUTTURE and TSTRUTTURA using defined relationship; these are subsets of the same foudset already correctly implemented and used.

The schema is (very) simple:
±-----------------+
| VALUTAZIONI |
±-----------------+
|
/|
±-------------------+
| DETTCONTSTRU | *** THIS IS THE TABLE TO BE PRINTED ***
±-------------------+
|/
|
±-----------------+
| STRUTTURE |–
±-----------------+ |
|/ |/ |
| ----------/
|
±------------------+
| TSTRUTTURA |
±------------------+
All relationship are between a PrimaryKey (PK) and a related ForeignKey (FK); both keys are a simple integer with positive values only.
The FK in DETTCONTSTRU vs VALUTAZIONI is always available because the FK is mandatory.
The FK in DETTCONTSTRU vs STRUTTURE is always available because the FK is mandatory.
The FK in STRUTTURE vs TSTRUTTURA is always available because the FK is mandatory.
The FK in STRUTTURE vs STRUTTURE (recursive relationship) is NOT always available aka is optional depending from user needs.

The solution written in SQL is very simple:
select dets_id
from dettcontstru, valutazioni, tstrutture, strutture
left outer join strutture unw1 on strutture.stru_unwinding = unw1.stru_id
where dettcontstru.dets_stru = strutture.stru_id
and strutture.stru_tstt = tstruttura.tstt_id
and dettcontstru.dets_vltz = PARAM1
and dets_ag_competenza = PARAM2
and ( (tstrutture.tstt_tope = PARAM3 and strutture.stru_unwinding is null)
or (tstrutture.tstt_tope = PARAM3 and strutture.stru_unwinding is not null and unw1.stru_unwinding is not null)
or (tstrutture.tstt_tope = PARAM4 and strutture.stru_unwinding is not null and unw1.stru_unwinding is null)
)
order by dettcontstru.dets_vltz, valutazioni.vltz_id, dettcontstru.dets_ag_competenza, dettcontstru.dets_stru

Now it is time to implement different possible solutions in Servoy:

SOLUTION1a:
Code sample:
controller.find();
dets_vltz = param1;
dets_ag_competenza = param2;
dettcontstru_to_strutture_inv.strutture_to_tstruttura_inv.tstt_tope = param3
dettcontstru_to_strutture_inv.stru_unwinding = “^”
controller.newRecord();
dets_vltz = param1;
dets_ag_competenza = param2;
dettcontstru_to_strutture_inv.strutture_to_tstruttura_inv.tstt_tope = param3
dettcontstru_to_strutture_inv.stru_unwinding = “!^”
dettcontstru_to_strutture_inv.strutture_to_strutture_inv_unwinding.stru_unwinding = “!^”
controller.newRecord();
dets_vltz = param1;
dets_ag_competenza = param2;
dettcontstru_to_strutture_inv.strutture_to_tstruttura_inv.tstt_tope = param3
dettcontstru_to_strutture_inv.stru_unwinding = “!^”
dettcontstru_to_strutture_inv.strutture_to_strutture_inv_unwinding.stru_unwinding = “!^”
controller.search()
Problem:
Cannot convert null to an object.
Callstack:
firm_VLPSTA06.estrai_dati
firm_VLPSTA06.print_interact
VLPGES02.print_interact
MENQRY00.form_print
TypeError: Cannot convert null to an object. (estrai_dati; line 39)
firm_VLPSTA06.estrai_dati
firm_VLPSTA06.print_interact
VLPGES02.print_interact
MENQRY00.form_print

DESCRIPTION OF IDENTIFIED PROBLEM1a: Servoy QBF is not able to deal with relationship on non mandatory fields…

SOLUTION1b: (simplified version of solution 1a)
Code sample:
controller.find();
dets_vltz = param1;
dets_ag_competenza = param2;
*** dettcontstru_to_strutture_inv.strutture_to_tstruttura_inv.tstt_tope = param3
dettcontstru_to_strutture_inv.stru_unwinding = “^”
controller.search()

Executing this code is getting too much records…with a further analysis on the server log I discovered:

*** This selection field is ignored as it could be seen from server log
select dettcontstru.dets_id from dettcontstru,valutazioni,strutture
where ( (dettcontstru.dets_vltz = ? and dettcontstru.dets_ag_competenza = ? and not ( strutture.stru_unwinding IS NULL ) )
or (dettcontstru.dets_vltz = ? and dettcontstru.dets_ag_competenza = ? and strutture.stru_unwinding IS NULL ) )
and valutazioni.vltz_id = dettcontstru.dets_vltz and strutture.stru_id = dettcontstru.dets_stru
order by dettcontstru.dets_vltz, valutazioni.vltz_id, dettcontstru.dets_ag_competenza, dettcontstru.dets_stru

N.B. The latest query selections as well as the order by clause are generated by the form sort field defined as:
dettcontstru.dets_vltz, valutazioni.vltz_id, dettcontstru.dets_ag_competenza, dettcontstru.dets_stru

DESCRIPTION OF IDENTIFIED PROBLEM1b: QBF ignoring a selection field on two levels of Servoy relations…

SOLUTION2: let’s try to reuse my SQL already written and tested…and do a loadRecords() from it…
Code sample:
var query = 'select dets_id ’ +
’ from dettcontstru, valutazioni, tstrutture, strutture ’ +
'left outer join strutture unw1 on strutture.stru_unwinding = unw1.stru_id ’ +
'where dettcontstru.dets_stru = strutture.stru_id ’ +
'and strutture.stru_tstt = tstruttura.tstt_id ’ +
'and dettcontstru.dets_vltz = PARAM1 ’ +
'and dets_ag_competenza = PARAM2 ’ +
'and ( (tstrutture.tstt_tope = PARAM3 and strutture.stru_unwinding is null) ’ +
'or (tstrutture.tstt_tope = PARAM3 and strutture.stru_unwinding is not null and unw1.stru_unwinding is not null) ’ +
'or (tstrutture.tstt_tope = PARAM4 and strutture.stru_unwinding is not null and unw1.stru_unwinding is null) ’ +
') ’ +
‘order by dettcontstru.dets_vltz, valutazioni.vltz_id, dettcontstru.dets_ag_competenza, dettcontstru.dets_stru’;
var args = new Array();
args[0] = PARAM1
args[1] = PARAM2
args[2] = PARAM3
args[3] = PARAM3
args[4] = PARAM4
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, args, 10000);
controller.loadRecords(dataset)

DESCRIPTION OF IDENTIFIED PROBLEM2: a dataset trasferred with a loadRecords is limited to 1000 records…

SOLUTION3: let’s extract all records as already working in another form and omit later those not valid…
Code sample:
controller.find();
dets_vltz = param1;
dets_ag_competenza = param2;
controller.search()
for (var i = 1; i <= databaseManager.getFoundSetCount(forms.firm_VLPSTA06.foundset); i++ )
{
controller.setSelectedIndex(i);
if (dets_stru != null)
{
unwind = dettcontstru_to_strutture_inv.stru_unwinding;
if (dettcontstru_to_strutture_inv.stru_unwinding != null)
{
unwindunwind = dettcontstru_to_strutture_inv.strutture_to_strutture_inv_unwinding.stru_unwinding;
}
if (dettcontstru_to_strutture_inv.stru_tstt != null)
{
tipoOper = dettcontstru_to_strutture_inv.strutture_to_tstruttura_inv.tstt_tope;
}
}
if (specific condition using unwind, unwindunwind and tipoOper are met)
{
controller.omitRecord(false);
i = i - 1;
}
}
DESCRIPTION OF IDENTIFIED PROBLEM3: when working on large foundset controller.omitRecord() works only on first 200 records…

SOLUTION4: let’s reause working SQL as a dataset doing a serie of find() - search() for each PK…
Code sample:
var query = 'select dets_id ’ +
’ from dettcontstru, valutazioni, tstrutture, strutture ’ +
'left outer join strutture unw1 on strutture.stru_unwinding = unw1.stru_id ’ +
'where dettcontstru.dets_stru = strutture.stru_id ’ +
'and strutture.stru_tstt = tstruttura.tstt_id ’ +
'and dettcontstru.dets_vltz = PARAM1 ’ +
'and dets_ag_competenza = PARAM2 ’ +
'and ( (tstrutture.tstt_tope = PARAM3 and strutture.stru_unwinding is null) ’ +
'or (tstrutture.tstt_tope = PARAM3 and strutture.stru_unwinding is not null and unw1.stru_unwinding is not null) ’ +
'or (tstrutture.tstt_tope = PARAM4 and strutture.stru_unwinding is not null and unw1.stru_unwinding is null) ’ +
') ’ +
‘order by dettcontstru.dets_vltz, valutazioni.vltz_id, dettcontstru.dets_ag_competenza, dettcontstru.dets_stru’;
var args = new Array();
args[0] = PARAM1
args[1] = PARAM2
args[2] = PARAM3
args[3] = PARAM3
args[4] = PARAM4
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, args, 10000);
for ( var i = 1; i <= dataset.getMaxRowIndex(); i++ )
{
dataset.rowIndex = i
controller.find();
dets_id = dataset.dets_id;
if (i == 1)
{
controller.search();
}
else
{
controller.search(false, false);
}
}

DESCRIPTION OF IDENTIFIED PROBLEM4: QBF loop using single PK application freeze after 437 find() - search()…

After a full work day behind this I am simply running out of ideas…

Any hint ?

Tnks,
Gianni

2

Ciao Gianni,

The way I do this kind of thing may not be the ‘Servoy way’, but it works well for me:

I take the SQL query that you say is working fine and get the dataset using

databaseManager.getDataSetByQuery();

Then I use the dataset to construct HTML (and I add proper CSS styling) and write the HTML to a temp file, which I open in the default browser on the computer, and the user can print from there… this way I also get around any limitations such as loading more than 1000 records on older versions of Servoy.

3

I’ve found a WORKING SOLUTION finally!

I’ve adapted SOLUTION4 building foundset in stages of N records each time; this way I’ve overcome the freezing after 437 find() - search()…with a maximum of 10000 records in the dataset 400 find() - search() will be done!
To have the leadingSubsummary correctly working I’ve added a controller.sort replicating the form sort property after the find() - search() series.

Performances are as expected…no penalty from the added code.

This is the working code:

var query = 'select dets_id ’ +
’ from dettcontstru, valutazioni, tstruttura, strutture ’ +
'left outer join strutture unw1 on strutture.stru_unwinding = unw1.stru_id ’ +
'where dettcontstru.dets_stru = strutture.stru_id ’ +
'and strutture.stru_tstt = tstruttura.tstt_id ’ +
'and dettcontstru.dets_vltz = valutazioni.vltz_id ’ +
'and dettcontstru.dets_vltz = ? ’ +
'and dets_ag_competenza = ? ’ +
'and ( (tstruttura.tstt_tope = ? and strutture.stru_unwinding is null) ’ +
'or (tstruttura.tstt_tope = ? and strutture.stru_unwinding is not null and unw1.stru_unwinding is not null) ’ +
'or (tstruttura.tstt_tope = ? and strutture.stru_unwinding is not null and unw1.stru_unwinding is null) ’ +
') ’ +
‘order by dettcontstru.dets_vltz, valutazioni.vltz_id, dettcontstru.dets_ag_competenza, dettcontstru.dets_stru’;
var args = new Array();
args[0] = valutazione_da_stampare;
args[1] = livello_stampa;
args[2] = tipo_primo;
args[3] = tipo_primo;
args[4] = tipo_secondo;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, args, forms.UTIGES00.cursor_def());
var primogiro = true;
var primodelgiro = true;
var dimensio = 25;
if (dataset.getMaxRowIndex() > 0)
{
controller.find();
for ( var i = 1; i <= dataset.getMaxRowIndex(); i++ )
{
dataset.rowIndex = i
if (primodelgiro)
{
dets_id = dataset.dets_id;
primodelgiro = false;
}
else
{
controller.newRecord();
dets_id = dataset.dets_id;
}
if (Math.round(i/dimensio)*dimensio == i || i == dataset.getMaxRowIndex())
{
if (primogiro)
{
controller.search();
primogiro = false;
}
else
{
controller.search(false, false);
}
primodelgiro = true;
if (i != dataset.getMaxRowIndex())
{
controller.find();
}
}
}
}
controller.sort(‘dets_vltz, dettcontstru_to_valutazioni_inv.vltz_id, dets_ag_competenza, dets_stru’);

swingman tnks anyway for your hint, it will be useful in another situation!

Ciao,
Gianni