how to save data

Classic Servoy
1

Hi All,
I have problem with saving data back to table with following code:

var query = "SELECT customer_id, customer_name, num_order FROM customer;";
var resultSet = databaseManager.getDataSetByQuery(controller.getServerName(), query, null , 10);

var numRow = resultSet.getMaxRowIndex();
if (numRow > 0){
    var totalNumOrder = resultSet.getValue(1, 3);
    resultSet.setValue(1,3, totalNumOrder + 1);
}

After running, I can see the value of column 3 changed but I do not know how to save this value back to customer table. I’ve tried to use databaseManager.saveData() but it doesn’t work.

Please give me an advice how to sort it out or other ways to do the same job.

Thanks you very much

C Tr.

2

A dataset is the result of a query in memory and not lively hooked to the database. If you change values in a dataset, you change that in memory. The database will never notice that change.

When you want to deal with records that are really tied to the database in methods, you have to look at the foundset of a table. Changes you make there are automatically updated in the DB.

3

Hi Patrick,

Thanks for your answer. But I do not know how to use foundset here.
Because databaseManager.getDataSetByQuery() method returns JSDataSet.
Could you please tell me how to convert from dataset to foundset.

And I do not understand the logic here we get the data out why we don’t have any method to save it back.

Thanks
C Tr.

4

Let’s say you have a form based on “customer”. Your query receives all records of customer, so on that form you can call

forms.customerForm.controller.loadAllRecords()

to get a foundset with all records in that form.

Then you can loop over the records like this

for (var i = 1; i <= foundset.getSize(); i++) {
   var record = foundset.getRecord(i); // gives you record # i from the foundset
   record.num_order = record.num_order + 1; // increases the num_order by one; shorter is record.num_order ++;
}

In your example, you are only trying to update one record. My example goes over all records and updates that value…

5

Because I don’t have the customer form. I have an order form and in its methods I need to access to customer table.

Do you think I need to create customer form, as you did in your example, in order to access to customer records even I do not need customer form.

Thanks Patrick
C Tr.

6

If you are on an order, you could access the customer via a relation, no?

Then you could write something like

orders_to_customer.num_order ++;

But since I finally start to understand what you are after, it sounds more like you should create an aggregation in orders and a calculation in customer.

For example, go to dataproviders and select the orders table. Go to the aggregations tab and create a new aggregation called total_orders. Make that a count on orders_id. Then go to the customer table and create a calculation with the same name as your num_order column, for example “num_order”. That calculation could look like this

if (utils.hasRecords(customer_to_orders)) {
   // customer has orders; get the aggregate value
   return customer_to_orders.total_orders;
}
else {
   // customer has no orders
   return 0;
}

Servoy will calculate the total orders and return the result into the num_order column. So you do not have to take of that value manually.

Hope this helps.

7

Hi Patrick,

Re:

Let’s say you have a form based on “customer”. Your query receives all records of customer, so on that form you can call

Code:
forms.customerForm.controller.loadAllRecords()

to get a foundset with all records in that form.

I was wondering about this issue myself as well. So once a set of records is in memory (eg - via a dataset) then one can perform an implicit load into a foundset via controller.loadAllRecords()? I would assume that one needs to have the pk’s loaded in the dataset.

I also was looking around for a function that would do a conversion from a dataset to a foundset and couldn’t find one so was kinda scratching my head as well on this topic.

Thx, Michael