Apparently, ‘… using databaseManager.getDataSetByQuery in a calculation is not supported.’
I could not find this documented in the Wiki. Where is this documented?
I had to find it here: viewtopic.php?f=2&t=11554&p=57590&hilit=unstored+calculation+getDataSetByQuery#p57590
Also, shouldn’t I also be able to see the calculation when executing a ‘SELECT * FROM table_with_calculations’ in the results just when executing the sql command within the SQL Editor?
Thanks!
–Joe.
Hi Joe,
Since calculations are triggered more than you are expecting these getDataSetByQuery calls can turn out to be very expensive.
What is your use case?
Usually there are better ways to handle these things like using a method to fill the column the moment you save the record (which you will know will only trigger ONCE).
joe26:
Also, shouldn’t I also be able to see the calculation when executing a ‘SELECT * FROM table_with_calculations’ in the results just when executing the sql command within the SQL Editor?
Servoy calculations, when they are un-stored, are in-memory columns and only available inside the Servoy server and thus any SQL you send to the backend database has no knowledge of these. So calculations will not be available anywhere you use SQL, like in getDataSetByQuery and SQL Editor.
Hope this helps.
I was not differentiating between servoy calculations and database calculations.
The use case was simply an item’s weight * quantity. I am able to use the SQL 'SELECT sum(weightquantity) as total_weight FROM item_table’ as well as sum the quantity. It was not apparent why I was not getting the calculations in the returned dataset, even in the SQL Editor. Certainly the weightquantity can be a stored calculation but isn’t used frequently.
The uuid list comes from another table that is again related to another table, and the relations did not aggregate the data for the final item list, so I resorted to the getDataSetByQuery method. SQL had to be used for outer joins on the tables to retrieve a complete list of items.
Hi Joe,
You do realize that SQL Editor is not Servoy but a third-party Eclipse-based SQL-editor that, hence the name, does only SQL.
Servoy calculations are in Javascript, not SQL, even though any data being accessed will result in SQL being fired (or being accessed from cache).
So you can’t equate Servoy calculations to SQL calculations in that sense. Servoy uses an Object Relational Mapper (ORM) for accessing the database which might generate different SQL than you might expect if you would write the SQL yourself. Such is life using any ORM.
As for not being able to use getDataSetByQuery in calcs, the code completion might not work but the code certainly will (last I checked). But again, calculations trigger way more than you expect and therefor it’s better to stay away from stored-calculations using SQL. Even non-stored calcs could suffer from it and you could use methods instead.
Hope this explains things a bit more.
Thanks, Robert!
I knew I was using raw SQL outside of Servoy. Took Calculations for granted as being on the table itself. I usually run the profiler to determine performance constraints. Just doggedly kept at the SQL Editor queries believing I’d missed a setting somewhere so was testing possibilities. The wrapper makes all the difference.
Again, Thank You!
–Joe.