Introduction
When writing a script in SAP Sourcing/CLM if there is a need to query the database tables most of us are used to using the DBHandle class which is a powerful IAPI. The script developer would have to first open a database transaction, execute the SQL SELECT statement and finally close the database transaction. While this approach works fine, there are very specific coding protocols to be followed to ensure proper transaction management. Not following these protocols could result in too many open transactions and may lead to undesirable side effects or even an unusable system. In SAP Sourcing/CLM 7.0, SAP has introduced new IAPIs that allow the script developer to easily and safely query the database and get the result set. These Query IAPIs are quite powerful and in this blog I will explain how we can take advantage of them.
How to use the Query IAPI
Create Query Definition
I will use a simple example to explain the usage of Query IAPIs. Let’s say the script developer wants to query the database to get a list of all the project ids in the system from within the script. To accomplish this, the first step is to develop a custom Query Definition that lists all the projects. The Query Definition will look like the one below.
SELECT
<%RESULTS%>
FROM
<%SCHEMA%>.FCI_PRO_PROJECTS T1
WHERE
T1.CONTEXTID=<%CONTEXT(projects.projects)%> AND
T1.IS_TEMPLATE = 0 AND
T1.INACTIVE = 0
<%ORDERBY%>
I won’t be getting into the details of writing a Query Definition in this blog, however, if you are not familiar with the query syntax I would encourage you to review the Query section of online help of SAP Sourcing/CLM which can be accessed from http://help.sap.com/saphelp_sourcing_90_p/helpdata/en/39/8ee60a3d74482e9e0111c46dc1e23d/frameset.htm
Execution of Query Definition via Query IAPI
There are a handful of Query IAPI classes there are available to us which can be accessed from the java docs from Reference Guide as shown in the screenshot below.
Now let’s see how to use these IAPIs to execute the Query Definition we saw earlier to get the result set. The code snippet below executes a Query Definition that returns a single result set which is the ID of the project. The query used in the example may not be very useful; however, it should give you a good idea of how to use the Query IAPIs.
Line 1: filterPrompts = new HashMap();
Line 2: queryExec = IapiQueryExecFactory.createQueryExec(session, "CUSTOM-SCRIPT-QueryIAPIDemo");
Line 3: paramSet= queryExec.getParameterSet(session, "CUSTOM-SCRIPT-QueryIAPIDemo");
Line 4: resultSet = queryExec.execute(filterPrompts);
Line 5: metaData = resultSet.getMetaData();
Line 6: while (resultSet.next()) {
Line 7: projectID = resultSet.getString(0);
Line 8: }
The code snippet above should be quite self explanatory; however, I’ll point out some of the nuances. On Line 2, the Query Definition Internal Name is passed to the IapiQueryExecFactory.createQueryExec method.
On Line 3, the getParameterSet method is called and is needed for successfully executing the query.
The execute method on Line 4 takes in a Map as an input argument. In cases where there are no filter parameters involved, an empty Map can be passed as shown in the code snippet above.
resultSet.getMetaData() on line 5 should be present to get the result set back from the Query Definition.
Closing result set
It is very important to use the IapiQueryResultSetIfc.close() method to close the result set when it is no longer being used. It is recommended to use the try/finally block to ensure that the close method is called. Also, make sure all the exceptions are logged.
try {
resultSet = queryExec.execute(filterPrompts);
…
}
catch(…) {
…
}
finally {
if(resultSet != null) {
resultSet.close();
}
}
With Filter Prompts
Now let’s take a look at an example of a query with a filter parameter. The query below contains one filter parameter to filter by project owner.
SELECT
<%RESULTS%>
FROM
<%SCHEMA%>.FCI_PRO_PROJECTS T1
WHERE
T1.CONTEXTID=<%CONTEXT(projects.projects)%> AND
T1.IS_TEMPLATE = 0 AND
T1.INACTIVE = 0
<%AND("T1.DOC_OWNER_USER_OBJECT_ID = <%?( T1.DOC_OWNER_USER)%>")%>
<%ORDERBY%>
In the script below, the filter parameter is passed to the filterPrompts HashMap. Note that the “owner” is the name of the filter parameter defined in the Query Definition. It is case sensitive.
try {
HashMap filterPrompts = new HashMap();
filterPrompts.put("owner",doc.getDocumentOwnerUserReference());
queryExec = IapiQueryExecFactory.createQueryExec(session, "CUSTOM-SCRIPT-QueryIAPI_WithFilter");
paramSet= queryExec.getParameterSet(session, "CUSTOM-SCRIPT-QueryIAPI_WithFilter");
resultSet = queryExec.execute(filterPrompts);
metaData = resultSet.getMetaData();
while (resultSet.next()) {
displayName = resultSet.getString(0);
...
}
}
catch(ApplicationException ae) {
...
}
finally {
resultSet.close();
}
General Guidelines/Other Useful information
- If there are complex column types in the Query Definition the result set will contain one field value for the complex type. So for instance, in the query definition if you have Object ID, Class ID, Display Name column types, the result set will contain just one column. Same rule applies to other complex column types as well.
- It is recommended to create separate Query Definition for the ones called by the Query IAPIs. Sharing the same Query Definition for Reports and for script may not be a good idea. The implementation team should agree upon a name for such queries (CUSTOM-SCRIPT-QueryIAPIDemo). Likewise, create a localized resource like “All Projects (script use only)” or something similar.
- It is recommended to use a specialized Report Developer group to ensure query developers do not have access to change those queries.
- When iterating through the result set store the results to a java List or something similar and call the close() method. Now, iterate through the List and apply the business logic which may require editing the business document.
- Transaction handling is handled by the tool when the IapiQueryExecIfc.execute() method is called, however, it is important to call the close() method so the system resources are released. Always check to ensure the result set is not null prior to calling close() method.
- Query Definition access list restrictions apply when using query IAPIs. For instance, if a Query Definition is restricted access to buyer_a and buyer_b, only those 2 users will be able to execute the query via the Query IAPI. Other users will get a hard error message when the script is triggered.
- In the examples above we have already seen the methods used in Query IAPIs but there are other useful methods that might be handy.
- IapiQueryResultSetMetaDataIfc.getFieldCount() – Returns the number of fields (columns) returned by the Query Definition. Note the count includes the hidden fields returned by the query
- IapiQueryResultSetMetaDataIfc. getFieldName(int aFieldIndex) – Returns the display name of field(n), ‘n’ being the index to the field, not the column. The field count is typically lower than the column count because of complex fields.
- IapiQueryResultSetMetaDataIfc.isHidden(int aFieldIndex) – Returns information on whether a particular field is hidden on the Query Definition
- IapiPromptParamSetIfc. getParamNames() – Returns the list of filter parameters associated with the Query Definition
Advantages of using Query IAPIs
- Allows the script developer to have access to any data that can be queried via a Query Definition
- SQL query becomes part of the system data and not something that is buried in a script
- Take full advantage of the Query framework, so things like contexts, sorting of columns can be handled in the query definition rather than in script.
- Transaction handling is handled by the tool, so there is less risk involved
Even though using Query IAPIs requires creation of a Query Definition, it is still a better approach to query the database when compared to DbHandle. I hope you found this blog useful. Please feel free to provide your comments and feedback on the comments area. Enjoy!
- SAP Managed Tags:
5 Comments
