Page 1 of 1

Dataset from in-memory data source?

PostPosted: Fri Jul 19, 2019 11:28 am
by swingman
Hi all,

I have added some in-memory data sources which dramatically speed up and simplify the display of lists with running totals by using SQL-windowing functions.
Now the users would like to export the data and I would like to create a dataset based on the in-memory data source... (which is not shared with other users, so I don't have a database server).
I'd like to do something like:

Code: Select all
var ds = databaseManager.getDataSetByQuery("mem",query,args,maxrows);


This does not work on Servoy 2019.03.1.
I suppose the fallback option is to isolate the query and args as a function and rerun from the original data source...

Re: Dataset from in-memory data source?

PostPosted: Fri Jul 19, 2019 1:34 pm
by mboegem
Hi Christian,

you can retrieve a dataset like this:
Code: Select all
function getInMemDataset() {
   var _fs = datasources.mem.<TABLENAME>.getFoundSet();
   var _aCol = databaseManager.getTable(_fs).getColumnNames();
   
   _fs.loadAllRecords();
   
   return databaseManager.convertToDataSet(_fs, _aCol);
}


Hope this helps

Re: Dataset from in-memory data source?

PostPosted: Fri Jul 19, 2019 2:34 pm
by ROCLASI
Just to add to Marc's answer.

The column names in the databaseManager.convertToDataSet() function are in fact optional.
You can leave it out if you want to get all columns. But it's a nice option to be able to cherry-pick the columns you want in your dataset.

Hope this helps.

Re: Dataset from in-memory data source?

PostPosted: Fri Jul 19, 2019 3:34 pm
by mboegem
ROCLASI wrote:You can leave it out if you want to get all columns. But it's a nice option to be able to cherry-pick the columns you want in your dataset.


Nope.. checked that.
Although leaving out column names is possible, you will only get the pk column returned.

Documentation also points that way:
// converts a foundset pks to a dataset
var dataset = databaseManager.convertToDataSet(foundset);

Re: Dataset from in-memory data source?

PostPosted: Fri Jul 19, 2019 3:36 pm
by ROCLASI
I stand corrected.

Re: Dataset from in-memory data source?

PostPosted: Mon Jul 22, 2019 11:51 am
by swingman
Thanks but it wasn't what I was looking for -- maybe I wasn't clear.
I was hoping to run a query on the in-memory datasource rather than convert the whole thing (or specific columns) into a dataset... :-)

Re: Dataset from in-memory data source?

PostPosted: Mon Jul 22, 2019 8:21 pm
by kwpsd
Hi, Christian.

Something like this:

Code: Select all
    var query = datasources.mem.<IN_MEMORY_TABLE_NAME>.createSelect()
   
    query.where.add( query.columns.<COLUMN_NAME>.eq( 1 ) )
   
    foundset.loadRecords( query )


I hope this helps!

Re: Dataset from in-memory data source?

PostPosted: Wed Jul 24, 2019 5:42 pm
by swingman
Hi Kim,

thanks, I will have to try that. I tend to build queries by hand and not to use the query builder...

Re: Dataset from in-memory data source?

PostPosted: Wed Jul 24, 2019 6:25 pm
by kwpsd
We converted 95% of our raw SQL queries into Query Builder and find it easier to use than raw SQL, especially for complex SQL statements. Also, the QB statements are guaranteed to be cross-compatible with different back-ends. The 5% not converted is due to the lack of QB functionality (e.g. case/when statements), but that is coming. Try QB...you may like it!