Foundset on temporary table

Classic Servoy
1

Hi all:

I created with rawSql a temporary table (create table TEMP XXX …) containing a series of data for then print it from Jasper.

As Jasper only supports foundset and not dataset, I tried to create a temporary table and load it the result of the dataset.

...
var _dataset=databaseManager.getDataSetByQuery(_SQL);
fsTmp = databaseManager.getFoundset (szServer, "XXX"); 
fsTmp.loadRecords (_dataset) 
...

The problem is that it fsTmp=null and fsTmp.loadRecords (_dataset) returns an error.

Is there no way to create a temporary table foundset? Can we send to Jasper a dataset instead of a foundset?

Thanks.

2

My guess is that JasperReports doesn’t see the temp table because it uses another db connection in the connection pool.
You could wrap your code up in a database transaction, this would force Servoy to use (for your session only) only 1 connection in the connection pool.
Do be aware that this connection can’t be used by other clients as long as you have this database connection open so you might want to make your connection pool a bit larger.

Hope this helps.

3

Thanks for reply;

I think it’s Servoy who can not read the table, since fsTmp=databaseManager.getFoundset … is null and not an empty foundset.

Best regards.

4

Yes, when it uses another connection then the one you created the temp table in then Servoy doesn’t see the temptable.
Temp tables are bound to a database connection (i.e. a session).
Using a database transaction forces Servoy to use the same connection.

5

Adelo,

The reason that the foundset fsTmp is null is that Servoy does not look for new tables every time databaseManager.getFoundset() is called.
Only at startup the list of tables is scanned.

There is an exception when the table name starts with ‘TEMP_’.
In that case Servoy will check if there is a new table with that name.

If you don’t need the foundset and you just want to pass the table to jasper reports, you can create an sql-based report where the table name is a parameter and have the table name in a parameter of the report.

Rob

6

Thanks Rob for the reply, very interesant the prefix “TEMP_” in the name of the table.

Best regards.

7

Rob,

Where can I find documentation about :

There is an exception when the table name starts with ‘TEMP_’.
In that case Servoy will check if there is a new table with that name.

Regards,

8

The Servoy way of doing this (Servoy 6) is using databasemanager.creatDatasourceByQuery(yourSQL). It creates an in-memory table in a in-memory database on de application server. You can base a foundset on that datasource.

9

lwjwillemsen:
Rob,

Where can I find documentation about :

There is an exception when the table name starts with ‘TEMP_’.
In that case Servoy will check if there is a new table with that name.

Regards,

I have added some documentation on this in the raw sql plugin sample code:

// Note that when this function is used to create a new table in the database, this table will only be seen by 
// the Servoy Application Server when the table name starts with 'temp_', otherwise a server restart is needed.

Rob

10

Thanks Rob.

The TEMP_ table gives us something like the Foxpro cursor (exclusive temp table, fast as lightning).

Regards,