BO Java SDK-Getting SQL of WEBI SQl

gistfile1.txt

<%
/*
 * Applies to Version: Business Objects Enterprise XI Release 3.1
 * Date Created      : March 8, 2012
 * Description       : This page is displays each of the queries used in a
 *                     WebI report.
 * Author            : BDB
 */
%>

<%@ page import = "com.crystaldecisions.sdk.framework.*" %>
<%@ page import = "com.crystaldecisions.sdk.occa.infostore.*" %>
<%@ page import = "com.crystaldecisions.sdk.plugin.CeKind" %>
<%@ page import = "com.crystaldecisions.sdk.exception.SDKException"%>
<%@ page import = "com.businessobjects.rebean.wi.*" %>

<%
   //
   //  Get Parameters and Logon
   //
   
   // Retrieve the logon information
   String username = request.getParameter("username") ;
   String password = request.getParameter("password") ;
   String cmsName  = request.getParameter("cmsName" ) ;
   String authType = request.getParameter("authType") ;  

   String  resPmpts       = request.getParameter("resPmpts") ;
   Boolean resolvePrompts = resPmpts.equals("values")        ;

   String               webiDocName          = null ;
   IEnterpriseSession   boEnterpriseSession  = null ;
   IInfoStore           boInfoStore          = null ;
   IInfoObjects         boInfoObjects        = null ;
   IInfoObject          infoObject           = null ;
   ReportEngines        boReportEngines      = null ;
   ReportEngine         boReportEngine       = null ;
   DocumentInstance     boDocumentInstance   = null ;
   DataProviders        boDataProviders      = null ;
        
   try {

      out.println( "<h1>View WebI Queries</h1>" ) ;


      // Retrieve the name of the Web Intelligence document to be used in the sample
      webiDocName = request.getParameter("webiDocName");
      out.println( "for \"" + webiDocName + "\"<br /><br />" ) ;
      
      // Logon to the enterprise
      boEnterpriseSession = CrystalEnterprise.getSessionMgr().logon( username, password, cmsName, authType);
      
      //
      //  Get and open the WebI report
      //
      
      // Retrieve the IInfoStore object
      boInfoStore = (IInfoStore) boEnterpriseSession.getService("InfoStore");  
      
      // Build query to retrieve the InfoObjects for Web Intelligence document
      String query = "SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND='" + CeKind.WEBI + "' AND  SI_INSTANCE=0 AND SI_NAME='" + webiDocName + "'";
      
      // Execute the query
      boInfoObjects = (IInfoObjects) boInfoStore.query(query);

      if ( boInfoObjects.isEmpty() ) {
         out.println( "<b>ERROR: </b>report not found<br />" ) ;
         throw( null ) ; // bail
      }
      
      // Retrieve the first InfoObject instance of the Web Intelligence document
      infoObject = (IInfoObject) boInfoObjects.get(0);
      
      // Get the Report Engine
      boReportEngines = (ReportEngines) boEnterpriseSession.getService("ReportEngines");
      boReportEngine  = boReportEngines.getService( ReportEngines.ReportEngineType.WI_REPORT_ENGINE );
      
      // Retrieve the document instance for the Web Intelligence document
      boDocumentInstance = boReportEngine.openDocument( infoObject.getID() );
      
      
      //
      //  Get the Queries
      //
      
      boDataProviders = boDocumentInstance.getDataProviders() ;

      if ( boDataProviders == null ) {
         out.println( "<br />No queries found" ) ;
         throw( null ) ; // bail
      }
      out.println( "Queries found: " + boDataProviders.getCount() + "<br />" ) ;

      // step through each Data Provider
      for ( int i=0; i < boDataProviders.getCount(); i++ ) {

         // make sure we're just dealing with queries
         if ( !(boDataProviders.getItem(i) instanceof SQLDataProvider) ) {
            continue ; 
         }

         SQLDataProvider boDataProvider = (SQLDataProvider) boDataProviders.getItem(i) ;

         // Which one we got?
         out.println( "<h3>\"" + boDataProvider.getName()   + "\" - " + boDataProvider.getSource() + "</h4>" ) ;

         // Skip it if it won't let us read its SQL
         if ( !boDataProvider.isSupported(DataProviderFeature.VIEW_SQL) ) {
            out.println( "This data provider doesn't support viewing its SQL.<br />" ) ;
            continue;
         }

         // One query or many?
         if ( boDataProvider.hasCombinedQueries() ) {

            // Combined Queries
            out.println( "Combined queries<br /><br />" ) ;

            // To access the filters, process the Query Container
            // To get the SQL without using deprecated methods, process the SQL Container

            out.println( processQueryContainer(boDataProvider.getCombinedQueries()) + "<br />" ) ;
//            out.println( processSQLContainer(boDataProvider.getSQLContainer(resolvePrompts)) + "<br />" ) ;

         } else {

            // Just one query
            out.println( processSQLContainer(boDataProvider.getSQLContainer(resolvePrompts)) + "<br />" ) ;
         }
      }


   } catch ( REException e ) {
      out.println( "<br />REBEAN ERROR: " + e.getMessage() + "<br />" ) ;

   } catch ( SDKException e ) {
      out.println( "<br />SDK ERROR: " + e.getMessage() + "<br />" ) ;

   } catch ( Exception e ) {
      if ( e.getMessage() != null ) {
         out.println( "<br />ERROR: " + e.getMessage() + "<br />" ) ;
      }

   } finally {

      //
      //  Done
      //

      if( boDocumentInstance != null )
      {
         boDocumentInstance.closeDocument();
         boDocumentInstance = null;
      }

      if( boReportEngine != null )
      {
         boReportEngine.close();
         boReportEngine = null;
      }

      if( boEnterpriseSession != null )
      {
         boEnterpriseSession.logoff();
         boEnterpriseSession = null;
      }

   } // finally

%>

<%!
public String processQueryContainer( QueryContainer queryContainer )
// Recursive method
// Allows processing of filters
{
   String   ret_val ;

   // safety check
   if ( queryContainer == null ) {
      return ( "ERROR: Could not find Query Container" ) ;
   }

   ret_val  = queryContainer.getChildCount() + " query node(s)<br />" ;


   for ( int i=0; i < queryContainer.getChildCount(); i++ ) {

      QueryNode childNode = (QueryNode) queryContainer.getChildAt( i ) ;

      if ( i > 0 ) {
         ret_val += "<br />" + queryContainer.getQueryContainerOperator() + "<br />" ;
      }

      if ( childNode instanceof Query ) {

         /** recursion base **/
         Query query = (Query) childNode ;

         ret_val += query.getSQL() + "<br />" ;

         if ( query.hasCondition() ) {
            ret_val += "<font color='blue'>(has conditions)</font><br />" ;
            ret_val += processFilterConditionContainer( (FilterConditionContainer) query.getCondition() ) ;
         }

      } else { // QueryContainer

         ret_val += "(recursing)<br />" ;
         ret_val += processQueryContainer( (QueryContainer) childNode ) ;
      }
   }

   return ret_val ;
}
%>

<%!
public String processFilterConditionContainer( FilterConditionContainer filterConditionContainer )
// Recursive method
{
   String   ret_val ;

   // safety check
   if ( filterConditionContainer == null ) {
      return ( "ERROR: Could not find Condition Container" ) ;
   }

   ret_val  = filterConditionContainer.getChildCount() + " FilterCondition<br />" ;


   for ( int i=0; i < filterConditionContainer.getChildCount(); i++ ) {

      FilterConditionNode childNode = (FilterConditionNode) filterConditionContainer.getChildAt( i ) ;

      if ( i > 0 ) {
         ret_val += "<br />" + filterConditionContainer.getOperator() + "<br />" ;
      }

      if ( childNode instanceof FilterConditionObject ) {

         /** recursion base **/
         FilterConditionObject filterConditionObject = (FilterConditionObject) childNode ;

         ret_val += filterConditionObject.getName() + "<br />" ;


      } else { // FilterConditionContainer

         ret_val += "(recursing)<br />" ;
         ret_val += processFilterConditionContainer( (FilterConditionContainer) childNode ) ;
      }
   }

   return ret_val ;
}
%>

<%!
public String processSQLContainer( SQLContainer sqlContainer )
// Recursive method
// Allows viewing of prompt definitions / values
{
   String   ret_val ;

   // safety check
   if ( sqlContainer == null ) {
      return ( "ERROR: Could not find SQL Container" ) ;
   }

   ret_val  = sqlContainer.getChildCount() + " SQL node(s)<br />" ;


   for ( int i=0; i < sqlContainer.getChildCount(); i++ ) {

      SQLNode childNode = (SQLNode) sqlContainer.getChildAt( i ) ;

      if ( i > 0 ) {
         ret_val += "<br />" + sqlContainer.getOperator() + "<br />" ;
      }

      if ( childNode instanceof SQLSelectStatement ) {

         /** recursion base **/
         SQLSelectStatement   sqlStatement = (SQLSelectStatement) childNode ;

         if ( sqlStatement.isCustomSQL() ) {
            ret_val += "<br /><font color='red'>(Custom SQL)</font><br />" ;
         } else {
            ret_val += "<br /><font color='blue'>(WebI generated SQL)</font><br />" ;
         }

         ret_val += sqlStatement.getSQL() + "<br />" ;

      } else { // SQLContainer

         ret_val += "(recursing)<br />" ;
         ret_val += processSQLContainer( (SQLContainer) childNode ) ;
      }
   }

   return ret_val ;
}
%>

Can you please update the same for 4x version..