Skip to Content

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.

8-1-2012 9-42-50 AM.png

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!

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Devesh Sati Prasad

    Vikram,

    This is awesome!!

    I am not sure how I missed it and continued using DbHandle APIS. The most obvious advantage of using Query APIs should be the freedom and ease to use tokens in query definition. This is one area where DbHandle APIs lacked.

    Thanks for the info. Its appreciated.

    ~Devesh

    (0) 
  2. Alan Salgado

    Vikram,

    Thanks for this blog post Vikram. This new IAPI makes access to objects based on the value of an extension much easier and cleaner.

    Alan

    (0) 
  3. Subhasini Chandramohan

    Hi Vikram,

    Thanks for this blog. We are trying to get a user account based on a custom query with filters using these IAPIs.

    We have iterated to the resultset where our conditions match. We can read the individual result column strings. However, is there a way we can get an object reference to the user account with the resultset parameters? getObjectReference() of the complex column type did not do the trick for us. Since there is no home object for user account, we are a little stuck.

    maBean = doc.getParentIBean();

    proc_team = maBean.getExtensionField(“CUST_Proc_Team”).get();

    filterPrompts = new HashMap();
    queryExec = IapiQueryExecFactory.createQueryExec(session, “CUSTOM-SCRIPT-QueryIAPI_WithFilter”);

    paramSet= queryExec.getParameterSet(session, “CUSTOM-SCRIPT-QueryIAPI_WithFilter”);

    resultSet = queryExec.execute(filterPrompts);

    resultSet.getMetaData();

    while (resultSet.next())
    {
    member = resultSet.next();

    ua_proc_team = resultSet.getString(0);
    ua_job_title = resultSet.getString(1);

    if(ua_proc_team.equals(proc_team))
    {
      if(ua_job_title.equals(“Director”))
      {

       appr = resultSet.getString(2);

       addApprover(resultSet.getObjectReference(2)); //this does not get us the user account object reference
      }
    }

    }

    Appreciate your inputs.

    Regards,

    Subhasini

    (0) 

Leave a Reply