When you need to execute SQL but the call needs to come from the client side, you should use a Service Data Object.
NEVER directly execute SQL on the server side that has come from the client side. Service Data Objects are one way to avoid doing so.
Service Data Objects are well suited for situations where you need dynamic data. If you need an SQL statement but the data it generates is relatively static and rarely changes, the best practice is to use a UI Part Metadata Item instead.
SQL Must Be Call Through a Database Object
Starting with Aptify 5.5.4, for security purposes, SQL statements for Service Data Objects must be executed through a database objects. if you have an earlier versions of Aptify with existing service data objects, when Aptify 5.5.4 is applied, the setup will attempt to match the text entered in the SQL field (which is now replaced with a link to the Database Objects service) to a database object with the same name. If a match is not found, the Database Objects field will contain a placeholder that you will need to update with a valid Database Objects record before the SQL Data Object works as expected.
Create a Database Object to hold the SQL you want to execute.
Create a new Service Data Objects record. (The Service Data Objects service is available in the Web Services application.)
- Name: The name to use when accessing the Service Data Object. Generally this will be identical to the Name of the Database Object used by the Service Data Object.
- To apply Security, select the Enable Security option. If security is enabled then add at least one record on the Security tab.
- Database Object: Enter the Database Object you created in step 1 here.
Specify which applications can access the object on the Applications tab.
This will restrict use of the Service Data Object to a particular Service Application.
Applications Filter Required
Starting with Aptify 5.5.4, you must specify at least one service application.
- If you selected Enable Security option on the General tab. Add at least one record on the Security tab.
This will restrict the use of the Service Data Object to the chosen Users, Groups, and/or Web Groups. (This is an additional level of restriction on top of the Grant SQL in the Database Object.)
Enabling security can result in a reduction in system performance. This is most likely going to be noticed if you have users accessing the system that do not have access to a service data object, and groups are being used.
If the Database Object (or other SQL) requires parameters, enter each parameter on the Parameters tab. Use the Name of the parameter as it is defined in the SQL.