How to load In Memory datasource from query

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

How to load In Memory datasource from query

Postby dfernandez » Sun Jun 11, 2017 5:02 am

Hi,

I have created an In Memory datasource and want to load it with data from a SQL Query.

I am trying with this:
var q = 'SELECT customer, COUNT(document) as TotalInvoices, SUM(subtotal) AS TotalSales FROM Docs GROUP BY customer';
var uuri = databaseManager.createDataSourceByQuery('SalesByCustomer', 'mem', q, null, 999);

And uuri returns "Server mem not found". What should I place in server for a an in memory datasource? Or should I use a different approach to load it?

Thank you
dfernandez
 
Posts: 60
Joined: Wed Feb 29, 2012 4:04 pm

Re: How to load In Memory datasource from query

Postby ROCLASI » Sun Jun 11, 2017 12:14 pm

Hi,

dfernandez wrote:I am trying with this:
var q = 'SELECT customer, COUNT(document) as TotalInvoices, SUM(subtotal) AS TotalSales FROM Docs GROUP BY customer';
var uuri = databaseManager.createDataSourceByQuery('SalesByCustomer', 'mem', q, null, 999);


The syntax for the createDataSourceByQuery is
Code: Select all
databaseManager.createDataSourceByQuery(name:String, server_name:String, sql_query:String, arguments:Object[], max_returned_rows:Number)


So if 'mem' is your In-Memory datasource name and 'SalesByCustomer' your connection name then you have switched them around.
So it should look like this:
Code: Select all
var q = 'SELECT customer, COUNT(document) as TotalInvoices, SUM(subtotal) AS TotalSales FROM Docs GROUP BY customer';
var uuri = databaseManager.createDataSourceByQuery('mem', 'SalesByCustomer', q, null, 999);


Also I see your table name has caps in them. If they are indeed defined with caps in your database (normally it defaults to all lower- or all uppercase, depending on your db vendor) then you need to quote these database objects with double quotes in your SQL ( i.e."TotalSales").

Hope this helps.
Robert Ivens
ROCLASI Software Solutions / JBS Group, Partner
SAN Developer / Servoy Valued Professional / Servoy Certified Developer
Twitter: @roclasi / @servoyforge
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5205
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: How to load In Memory datasource from query

Postby dfernandez » Sun Jun 11, 2017 5:33 pm

Thank you Robert,

inmem.jpg
inmem.jpg (12.51 KiB) Viewed 225 times


So if 'mem' is your In-Memory datasource name and 'SalesByCustomer' your connection name then you have switched them around.

Please take a look at the attached image. My In-Memory datasource name is 'SalesByCustomer'. I tried with mem as server name because I do not know what to place there.

databaseManager.createDataSourceByQuery(name:String, server_name:String, sql_query:String, arguments:Object[], max_returned_rows:Number)


So SalesByCustomer is my name. What should I place in server_name?

Thank you!
dfernandez
 
Posts: 60
Joined: Wed Feb 29, 2012 4:04 pm

Re: How to load In Memory datasource from query

Postby dfernandez » Sun Jun 11, 2017 5:51 pm

Hi,

In server_name I now placed the name of the server where I am doing the query and now works. The data is not loading in a servoy-extra table but it shows in a standard form.
Thank you!!!
dfernandez
 
Posts: 60
Joined: Wed Feb 29, 2012 4:04 pm


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 9 guests