Foundset as database field

Classic Servoy
1

Hello all.

I want to store searches and forms history by user. Is it possible to store a foundset into a database field?

Best regards. Roberto Blasco.

2

Perhaps if you stored the foundset in a binary field …

3

Thanks a lot Adelo :-)

Best regards. Roberto Blasco.

4

Hi Roberto,

just create a textfield with length -1 in the table, Servoy converts this to max possible length in DB.
Then just write the foundset there…

Just interested: why do you want to keep the whole foundset? This can end up with a lot of data being written to your DB > slow!
If you want to recall a fixed set of data, I’d suggest you store a dataset with PK’s this way > less data!
If you want to recall a set of data fitting the current arguments, I’d suggest you store the SQL and SQLparams (databaseManager.getSQL(Params))

Hope this helps.

5

Hi mboegem

I like it :D

If you want to recall a set of data fitting the current arguments, I'd suggest you store the SQL and SQLparams (databaseManager.getSQL(Params))

Thank you very much. Best regards. Roberto Blasco.

6

Hi Roberto,

Roberto Blasco:
I like it :D

If you want to recall a set of data fitting the current arguments, I'd suggest you store the SQL and SQLparams (databaseManager.getSQL(Params))

Just be aware you might get SQL back that won’t work the second time.
In some use cases Servoy will use a temp table and that only exists for that one session, replaying the same SQL will fail after that.

7

Hi ROCLASI

So may be, the best solution is to store the dataset with PK’s

If you want to recall a fixed set of data, I’d suggest you store a dataset with PK’s this way > less data!

Best regards. Roberto Blasco.

8

Hi Roberto,

the temp table is indeed something to watch out for.
You could catch the exception doing something like this:

var _sql		= databaseManager.getSQL(_myFoundset);
var _pars		= databaseManager.getSQLParameters(_myFoundset);
var _tmpDs	= /TEMP/.test(_sql) ? databaseManager.convertToDataSet(_myFoundset) : null;

if(_tmpDs) {
// store dataset}
else{
// store SQL
}

Please note the difference storing the sql or dataset.
A recall based on the dataset will always give you the same foundset, even if new records have been added to the database which mach the original criteria.
Recalling the foundset based on sql will include those new records.

Hope this helps