How to load In Memory datasource from query

Classic Servoy
1

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

2

Hi,

dfernandez:
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

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:

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.

3

Thank you Robert,

[attachment=0]inmem.jpg[/attachment]

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!

inmem.jpg
inmem.jpg248×150 11.2 KB

4

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