Dataset from in-memory data source?

[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...

[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

[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.

[mboegem]

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);

[ROCLASI]

I stand corrected.

[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...

[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!

[swingman]

Hi Kim,

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

[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!