Is there anything planned to give us access to other SQL commands than SELECT…?
We know that it would be checked against security ‘holes’ and that we could use workarounds inside servoy or oustide by using stored procedures, bute having the ability to INSERT, UPDATE and even DELETE would save us, developers, lots of time to focus on solution features.
The main benefit having this inside servoy would be that solutions will keep being compatible with all supported backends (letting the servoy team doing what it is specialized in - compatibility issues).
…and many thanks in advance to take the time to answer to those weird feature requests!
Me, too! Sometimes you need to insert one record and you have to send an SQL statement and have an IT guy execute that for you. It’d be a lot nicer if you could tell him: please push this button…
INSERT, UPDATE and even DELETE
and even DROP TABLE, ALTER TABLE, … In my eyes, this is not more security relevant than importinag and running a Servoy solution. I can delete all data if I make a mistake, why shouldn’t I be trusted to do an alter table.
The reason we don’t let you execute INSERT, UPDATE or DELETE SQL statements directly is because then we won’t “know” what records and IDs were invalidated - and therefore we would have to invalidate ALL CLIENT DATA CACHES - not to mention the inability to “broadcast” the changes to all clients. You can use stored procedures to do that - or you can use controller.deleteAllRecords(), and the foundset updater to do updates.
The main issue is always with an INSERT whenever primary keys are managed by an application layer (as probably occurs in Servoy).
If the application layer has reserved PK’s, how will you update the Servoy PK cache so that it does not fail when it tries to create a new record ?
For that reason, you should only perform SELECT and UPDATE.
Never INSERT.
Rarely DELETE.
Now, if you perform an UPDATE, you must also consider refreshing the form programatically; this is not trivial.
If you want to write INSERT or DELETE code against a particular table, as a bare minimum, you should never show a form representing the table(s) on which you intend to perform such operations.
It works with the db servers defined within Servoy and can perform any SQL command as you wish. It even ‘sort of’ emulates a dataset when you want a result back but that’s really not necessary since you can use the built-in tools for that.
The reason I wrote the plugin is because my application (IT2Print MIS for the graphic arts industry) is to interface with the db of another application. I only want to update a row of that db without the need of a form, controlled stuff or whatsoever. I don’t insert and don’t delete with it because of the above mentioned reasons but you can.
If anybody is interested I can seperate it from my in-house plugin and make it commercially available…