Foundset on temporary table

Questions, tips and tricks and techniques for scripting in Servoy

Foundset on temporary table

Postby Adelo Herrero » Fri Dec 17, 2010 1:13 pm

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.
Code: Select all
...
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.
Adelo Herrero
Adelo Herrero
 
Posts: 103
Joined: Sat Jul 25, 2009 9:24 pm
Location: Requena (Valencia) España

Re: Foundset on temporary table

Postby ROCLASI » Fri Dec 17, 2010 1:27 pm

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.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Twitter: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5342
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Foundset on temporary table

Postby Adelo Herrero » Fri Dec 17, 2010 3:31 pm

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.
Adelo Herrero
Adelo Herrero
 
Posts: 103
Joined: Sat Jul 25, 2009 9:24 pm
Location: Requena (Valencia) España

Re: Foundset on temporary table

Postby ROCLASI » Fri Dec 17, 2010 3:36 pm

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.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Twitter: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5342
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Foundset on temporary table

Postby rgansevles » Mon Dec 20, 2010 10:45 am

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
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1899
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: Foundset on temporary table

Postby Adelo Herrero » Mon Dec 20, 2010 2:06 pm

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

Best regards.
Adelo Herrero
Adelo Herrero
 
Posts: 103
Joined: Sat Jul 25, 2009 9:24 pm
Location: Requena (Valencia) España

Re: Foundset on temporary table

Postby lwjwillemsen » Tue Oct 04, 2011 8:12 am

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,
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 655
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Re: Foundset on temporary table

Postby rvanderburg » Tue Oct 04, 2011 9:28 am

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.
rvanderburg
Site Admin
 
Posts: 67
Joined: Wed May 04, 2011 10:28 am

Re: Foundset on temporary table

Postby rgansevles » Tue Oct 04, 2011 10:41 am

lwjwillemsen wrote: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:

Code: Select all
// 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
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1899
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: Foundset on temporary table

Postby lwjwillemsen » Tue Oct 04, 2011 11:01 am

Thanks Rob.

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

Regards,
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 655
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands


Return to Methods

Who is online

Users browsing this forum: No registered users and 2 guests