Ok, so I can use the following SQL statement directly to my MS SQL backend and produce the correct results:
select product_name, SUM (qty) FROM order_details WHERE core = 'RCS' and mac_pc= 'MAC' GROUP BY product_name
However when I plug this into getDataSetByQuery I get nothing but errors. Here is the code:
var maxReturnedRows = 10000
var query = "select product_name, SUM (qty) FROM order_details WHERE core = 'RCS' and mac_pc= 'MAC' GROUP BY product_name";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, maxReturnedRows);
What I am trying to do is build a list of software by name and then have a second column that shows the quantity that needs to be ordered.
I have this code in a method which is triggered by the onShow of the layout.
Where am I going wrong?
Servoy 3.1.5 build 409
Java Version 1.6.0_01-b06 (Windows XP)
MS SQL 2000
davidaarong:
However when I plug this into getDataSetByQuery I get nothing but errors.
And what errors are those?
Also I see you use Java 1.6.
I am not sure if the 3.1.5 works correctly with 1.6. But I might be mistaken.
But you could try to run it under Java 1.5 and see if it does work.
But lets concentrate on the errors first. Can you post them here?
ok… no more errors, but now i get all the records. what do i need to do to make it return the foundset organized by group as specified in my SQL statement?
var maxReturnedRows = 10000
var query = "select product_name, SUM (qty) FROM order_details WHERE core = 'RCS' and mac_pc= 'MAC' GROUP BY product_name";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
controller.loadAllRecords(dataset)
You should use controller.loadRecords(dataset/query), not controller.loadAllRecords(dataset) as this will load all the records into the foundset.
You can use controller.loadRecords(query) to load a primary key dataset based on a query, sorted in any way you like as determined by the syntax of your query, but you cannot use aggregations in the query.
Check the developers reference guide for explanations of how it works.
SELECT product_name, SUM (qty) FROM order_details WHERE core = 'RCS' and mac_pc= 'MAC' GROUP BY product_name
I have used it as follows, but I do not get an results, just a blank layout
var maxReturnedRows = 10000
var query = "select product_name, SUM (qty) FROM order_details WHERE core = 'RCS' and mac_pc= 'MAC' GROUP BY product_name";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
controller.loadRecords(dataset)
If you want to load that data into the foundset controller, yes. Only the PK is expected and required.
But you could use the InMemDataGrid bean and show the dataset in there.
Of course it’s in no way connected to any other foundsets/forms then but if you just want to show the query result this is a good way to go.
To load the results into a foundset you would need to use it something like this:
var query = "select order_details.product_id FROM order_details WHERE order_details.core = 'RCS' and order_details.mac_pc= 'MAC' ORDER BY order_details.product_name";
controller.loadRecords(query)
Modify to suit your setup.
You cant use ‘group by’ or ‘sum’ in your query, you must specify any columns used in [tablename.columnname] format for this to work.
davidaarong:
I Tried InMemDataGrid, but I could not even rename the column header nor could I figure out how to add a check box for the Purchased column.
Any ideas?
You can set the names in the SQL query like so:```
SELECT column1 AS Product, SUM(column2) AS Quantity, column3 AS Purchased FROM tablename…etc
And to set a column to show as checkbox you use setCheckBoxRenderer function of the InMemDataGrid object like so:```
elements.gridObjectName.setCheckBoxRenderer(3); // for the third column in the grid
I looked at an older post where you should some syntax on how to use the bean and i modified it. However, i do not know exactly how to correctly use this bean since i can not find any documentation on it.
how would you modify the following:
var sQuery ="select column1 AS product_name, SUM (column2) AS qty, column3 AS purchased FROM order_details WHERE core = 'RCS' and mac_pc= 'MAC' and purchased IS NULL or purchased ='0' GROUP BY product_name ORDER BY product_name",
sServername = controller.getServerName(),
ds = databaseManager.getDataSetByQuery(sServername, sQuery, null, -1);
forms.Home.elements.objGrid.setCheckBoxRenderer(3); // for the third column in the grid
forms.Home.elements.objGrid.setModel(ds.getAsTableModel());
var sQuery ="select column1 AS product_name, SUM (column2) AS qty, column3 AS purchased FROM order_details WHERE core = 'RCS' and mac_pc= 'MAC' and purchased IS NULL or purchased ='0' GROUP BY product_name ORDER BY product_name",
forms.Home.elements.objGrid.setCheckBoxRenderer(3); // for the third column in the grid
forms.Home.elements.objGrid.setModel(ds.getAsTableModel());
There are 2 problems in the above code.
First there is a SQL error. When using SUM/COUNT, etc. functions you need to use GROUP BY on all the other columns you query.
So it should be GROUP BY product_name,purchased .
The other problem is the order of setting the grid properties.
An empty grid doesn’t have any columns you can set. So setCheckBoxRenderer(3) will cause an error when the grid is empty.
Also adding the data to the grid will reset the setCheckBoxRenderer setting.
So by reversing the order of the 2 last lines it will work.
var sQuery ="select column1 AS product_name, SUM (column2) AS qty, column3 AS purchased FROM order_details WHERE core = 'RCS' and mac_pc= 'MAC' and purchased IS NULL or purchased ='0' GROUP BY product_name,purchased ORDER BY product_name",
sServername = controller.getServerName(),
ds = databaseManager.getDataSetByQuery(sServername, sQuery, null, -1);
// --------------------------------------
// To see if the back-end database returns an error you can add the following code
// --------------------------------------
if ( ds.getException() )
{
application.output( ds.getException() ); // debug message shown in the Servoy editor
}
// --------------------------------------
forms.Home.elements.objGrid.setModel(ds.getAsTableModel());
forms.Home.elements.objGrid.setCheckBoxRenderer(3);
select product_name, SUM (qty), purchased FROM order_details WHERE core = 'RCS' and mac_pc= 'MAC' and purchased IS NULL or purchased ='0' GROUP BY product_name,purchased ORDER BY product_name
now it works. Thanks Robert.
Also, is there any documentation on other functions of the bean? Where can i find more info?
so the exact method looks like this:
var sQuery ="select product_name, SUM (qty), purchased FROM order_details WHERE core = 'RCS' and mac_pc= 'MAC' and purchased IS NULL or purchased ='0' GROUP BY product_name,purchased ORDER BY product_name",
sServername = controller.getServerName(),
ds = databaseManager.getDataSetByQuery(sServername, sQuery, null, -1);
// --------------------------------------
// To see if the back-end database returns an error you can add the following code
// --------------------------------------
if ( ds.getException() )
{
application.output( ds.getException() ); // debug message shown in the Servoy editor
}
// --------------------------------------
forms.Home.elements.objGrid.setModel(ds.getAsTableModel());
forms.Home.elements.objGrid.setCheckBoxRenderer(3);
i also had to modify the SQL statement as follows to add my own Custome column names
select product_name as [Product], SUM (qty)as [QTY], purchased AS [Purchased] FROM order_details WHERE core = 'RCS' and mac_pc= 'MAC' and purchased IS NULL or purchased ='0' GROUP BY product_name,purchased ORDER BY product_name
Now that I have check boxes, how would I go about triggering a selection? In other words, once a user has a placed a check in one row of products in the bean how can I get just those records that are grouped?
The InMemDataGrid is pretty much just for display purposes only although it does allow you to get/set the selected row. It doesn’t have events where you can hook up any Servoy methods.
If you want more interaction then you might want to use HTML.
Of course HTML has it’s own set of limitations as well.
As for your question about any documentation on this grid.
Just select the InMemDataGrid object in the node tree in Servoy editor and you see all the properties and functions this object has.
I do admit the available documentation is pretty spars.
I suggest if you want to have more interaction in the InMemDataGrid bean you send in some feature requests.