Introduction

Purpose The purpose of Service Data Objects (SDOs) is to provide the ability to securely execute SQL on the database server from Aptify Web and Aptify SOA, returning the results to the caller, while controlling what can be executed and who can execute it.  The flexibility of being able to execute SQL statements, including stored procedures, without opening up the service layer to SQL injection attacks is a powerful feature in Aptify SOA.  System administrators and developers have control over which SQL statements can be executed through the Services Data Objects Entity.  Service Data Objects provide a way to identify the SQL statements that can be executed and they will be called are executed by Name, not by the SQL that executes.  Aptify SOA looks up the Service Data Object, confirms that the current platform and User are allowed to execute the Service Data Object, retrieve retrieves the SQL statement from the Service Data Object record, executes it and returns the results to the caller.

Service Data Objects can be any valid SQL statement, which can include stored procedures and/or functions that are defined in Aptify as Database Objects (but Service Data Objects are not limited to Aptify Database Objects).  Service Data Objects can also define parameters that can be passed with the service call and applied to the SQL statement.

Generally Service Data Objects are well suited when need dynamic data. a good choice for providing data when leveraging an Aptify Views is not appropriate. 

How to Create Service Data Object 

1. Create a Database Object to hold the SQL you want to execute.

2. Create a new Service

...

Name: Name given is genrally identical to name of database Object.it is used to accessing service data object.

SQL: it is SQL Object that should be executed. Here you can pass SQL query or any stored procedure name available in database.

Enable Application Filter: if we select enable application filter it will restrict to use SDO to a particular service application. If selected enable application filter option then we need to add alteast one Service Application on Application tab.

...

Enable Security: if you want to apply security you can select enable security option. If selected it will restrict the use of SDO to selected users, groups and web groups. So as to apply security we need to add atleast one record on security Tab.

Image Removed

If database object (Stored Procedure, SQL) require parameters then we need to add each parameter in parameter tab.

...

End Point

Service Data Object uses GET action. parameter defined for the object.

https://<service base>/services/DataObjects/spGetCompanyAccountSummary

Headers

...

1. 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.
   • SQL: The SQL the Service Data Object should execute. Call the Database Object you created in step 1 here.
     
     Image Added 

   Choose whether to apply Application Filters to the Service Data Object. This will restrict use of the Service Data Object to a particular Service Application.

2. To apply Application Filters, select the Enable Application Filters option on the General tab and specify which applications can access the object on the Applications tab.
   
   Image Added
   
   Choose whether to apply Security to the Service Data Object. This will restrict the use of the Service Data Object to the chosen Users, Groups, and/or Web Groups. (Of course, this is an additional level of restriction on top of the Grant SQL in the Database Object.) 

   
   

3. To apply Security, select the Enable Security option on the General tab. If security is enabled then add at least one record on the Security tab.

4. 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.
   
   Image Added

   Image Added
   
   

   Image Added
   
   

   The Service Data Object is now created. You can call it via Aptify Javascript API or via Aptify SOA.

Calling Service Data Object from Aptify SOA:

Header:

you need to pass AptifyAuthorization header in order to receive results.

...

End Point

...

Example

...

Here is sample data from Company Entity

Endpoint

https://<service base>/services/DataObjects/{Service DataObject Name}

Parameters defined in the SDO object need to be appended to the URL as a query string. Parameters should be the same as those defined in the SDO object.

Endpoint Example With Parameters

https://<service base>/services/DataObjects/{Service DataObject Name}?ParameterName1=value1&ParameterName2=Value2&....

Sample End Point(Execute spGetCompanyAccountSummary SDO):

The following example illustrates how to execute the spGetCompanyAccountSummary stored procedure as an endpoint. This example expects two parameters which are defined in the Web Service Application. The parameters are companyId (10) and Convertmulticurrency (false).

 https://<service base>/services/DataObjects/spGetCompanyAccountSummary?CompanyID=10&Convertmulticurrency=false

Sample Response

...

Body 

Here is sample Response data for spGetCompanyAccountSummary data object.

{
"dataObjectName":"spGetCompanyAccountSummary",
 "results":
	[
		{
			"OrderTotal":39414xxxx.6200xxxx,
			"PaymentTotal":4407xxxx.2000xxxx,
			"BalanceTotal":35007xxxx.4200xxxx
		}
	]
}