Dataset from in-memory data source?

Questions and answers regarding general SQL and backend databases

Dataset from in-memory data source?

Postby swingman » Fri Jul 19, 2019 11:28 am

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...
Christian Batchelor
Certified Servoy Developer
Batchelor Associates Ltd, London, UK
http://www.batchelorassociates.co.uk

http://www.postgresql.org - The world's most advanced open source database.
User avatar
swingman
 
Posts: 1472
Joined: Wed Oct 01, 2003 10:20 am
Location: London

Re: Dataset from in-memory data source?

Postby mboegem » Fri Jul 19, 2019 1:34 pm

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
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1742
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: Dataset from in-memory data source?

Postby ROCLASI » Fri Jul 19, 2019 2:34 pm

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

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

Re: Dataset from in-memory data source?

Postby mboegem » Fri Jul 19, 2019 3:34 pm

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);
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1742
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: Dataset from in-memory data source?

Postby ROCLASI » Fri Jul 19, 2019 3:36 pm

I stand corrected.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

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

Re: Dataset from in-memory data source?

Postby swingman » Mon Jul 22, 2019 11:51 am

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... :-)
Christian Batchelor
Certified Servoy Developer
Batchelor Associates Ltd, London, UK
http://www.batchelorassociates.co.uk

http://www.postgresql.org - The world's most advanced open source database.
User avatar
swingman
 
Posts: 1472
Joined: Wed Oct 01, 2003 10:20 am
Location: London

Re: Dataset from in-memory data source?

Postby kwpsd » Mon Jul 22, 2019 8:21 pm

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!
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: Dataset from in-memory data source?

Postby swingman » Wed Jul 24, 2019 5:42 pm

Hi Kim,

thanks, I will have to try that. I tend to build queries by hand and not to use the query builder...
Christian Batchelor
Certified Servoy Developer
Batchelor Associates Ltd, London, UK
http://www.batchelorassociates.co.uk

http://www.postgresql.org - The world's most advanced open source database.
User avatar
swingman
 
Posts: 1472
Joined: Wed Oct 01, 2003 10:20 am
Location: London

Re: Dataset from in-memory data source?

Postby kwpsd » Wed Jul 24, 2019 6:25 pm

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!
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 3 guests