business objects java sdk - com.businessobjects.rebean.wi.QueryException: You cannot run an empty query. (Error: RWI 00701)

[Former Member]

Hi all!

I have a question regarding dataproviders which are depending on the result of other dataproviders. Here a small example

This filters use the result of the "Date Reference" Dataprovider.

When I try to access the SQL for this report I receive following error

com.businessobjects.rebean.wi.QueryException: You cannot run an empty query. (Error: RWI 00701)

    at com.businessobjects.wp.om.OMQuery.checkResultPartCombinedQueries(Unknown Source)

    at com.businessobjects.wp.om.OMQuery.checkValid(Unknown Source)

    at com.businessobjects.wp.om.OMDataProviders.makeCommandFromQuery(Unknown Source)

    at com.businessobjects.wp.om.OMDataProviders.createCommands(Unknown Source)

    at com.businessobjects.wp.om.OMDataProviders.runCommandOnDPs(Unknown Source)

    at com.businessobjects.wp.om.OMDataProviders.runCommand(Unknown Source)

    at com.businessobjects.wp.om.OMQuery.loadSQL(Unknown Source)

    at com.businessobjects.wp.om.OMQuery.getSQLRoot(Unknown Source)

    at com.businessobjects.rebean.wi.SQLDataProviderImpl.resetSQL(Unknown Source)

...

Here the line which is causing the trouble

wiSDP.resetSQL();

Is there a way to get the SQL statement used exported or to prepare the dataprovider depending on, so that I can retrieve the end SQL statement?

Thanks for the help. (BO XI 3.1 SP 3.5)

ciao Hakan

[Former Member]

Hi Hakan,

Below is the sample jsp code to retrieve the SQL statements used in a Data Provider on a Web Intelligence document using the Java Report Engine SDK:

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

<%
boolean loginSuccessful = false;
IEnterpriseSession oEnterpriseSession = null;
String username = "username";
String password = "password";
String cmsname  = "cms_name";
String authenticationType = "secEnterprise";

try
{
//Log in.
oEnterpriseSession = CrystalEnterprise.getSessionMgr().logon( username, password, cmsname, authenticationType);
if (oEnterpriseSession == null)
{ 
out.print("<FONT COLOR=RED><B>Unable to login.</B></FONT>");
}
else
{ 
loginSuccessful = true;
}
}
catch (SDKException sdkEx)
{
out.print("<FONT COLOR=RED><B>ERROR ENCOUNTERED</B><BR>" + sdkEx + "</FONT>");
}
//----------------------------------------------------------------------------------------
if (loginSuccessful)
{
IInfoObject oInfoObject = null;
String docname = "WebI document name";

//Grab the InfoStore from the httpsession
IInfoStore oInfoStore = (IInfoStore) oEnterpriseSession.getService("", "InfoStore"); 
//Query for the report object in the CMS.  See the Developer Reference guide for more information the query language.  
String query = "SELECT TOP 1 * FROM CI_INFOOBJECTS WHERE SI_INSTANCE = 0 And SI_Kind = 'Webi' AND SI_NAME='" + docname +"'";

IInfoObjects oInfoObjects = (IInfoObjects) oInfoStore.query(query);

if (oInfoObjects.size() > 0)
{ 
//Retrieve the latest instance of the report 
oInfoObject = (IInfoObject) oInfoObjects.get(0);
  // Initialize the Report Engine 
ReportEngines oReportEngines = (ReportEngines) oEnterpriseSession.getService("ReportEngines");  ReportEngine oReportEngine = (ReportEngine)

oReportEngines.getService(ReportEngines.ReportEngineType.WI_REPORT_ENGINE);
  // Opening the document 
DocumentInstance oDocumentInstance = oReportEngine.openDocument(oInfoObject.getID());   
DataProvider oDataProvider = null; 
SQLDataProvider oSQLDataProvider = null; 
SQLContainer oSQLContainer_root = null; 
SQLNode oSQLNode = null; 
SQLSelectStatement oSQLSelectStatement = null; 
String sqlStatement = null;
  out.print("<TABLE BORDER=1>"); 
for (int i=0; i<oDocumentInstance.getDataProviders().getCount(); i++)
{  
oDataProvider = oDocumentInstance.getDataProviders().getItem(i);  
out.print("<TR><TD COLSPAN=2 BGCOLOR=KHAKI>Data Provider Name: " + oDataProvider.getName() + "</TD></TR>");
   if (oDataProvider instanceof SQLDataProvider)
{   
oSQLDataProvider = (SQLDataProvider) oDataProvider;
oSQLContainer_root = oSQLDataProvider.getSQLContainer();
if (oSQLContainer_root != null)
{    
for (int j=0; j<oSQLContainer_root.getChildCount(); j++)
{     
oSQLNode = (SQLNode) oSQLContainer_root.getChildAt(j);
oSQLSelectStatement = (SQLSelectStatement) oSQLNode;             
sqlStatement = oSQLSelectStatement.getSQL();     
out.print("<TR><TD>" + (j+1) + "</TD><TD>" + sqlStatement + "</TD></TR>");    
}   
}  
}
else
{   
out.print("<TR><TD COLSPAN=2>Data Provider is not a SQLDataProvider.  SQL Statement can not be retrieved.</TD></TR>");   }  }  out.print("</TABLE>");
oDocumentInstance.closeDocument(); }
oEnterpriseSession.logoff();
}
%>

Hope it helps.

Regards,

Anchal

[Former Member]

Hi Anchal!

Thanks for the code, but did not change anything as soon as the "getSQLContainer" function is called, I receive the error. For me it looks like that the query "checkValid" function is trying to get details, but fails.

com.businessobjects.rebean.wi.QueryException: You cannot run an empty query. (Error: RWI 00701)

    at com.businessobjects.wp.om.OMQuery.checkResultPartCombinedQueries(Unknown Source)

    at com.businessobjects.wp.om.OMQuery.checkValid(Unknown Source)

    at com.businessobjects.wp.om.OMDataProviders.makeCommandFromQuery(Unknown Source)

    at com.businessobjects.wp.om.OMDataProviders.createCommands(Unknown Source)

    at com.businessobjects.wp.om.OMDataProviders.runCommandOnDPs(Unknown Source)

    at com.businessobjects.wp.om.OMDataProviders.runCommand(Unknown Source)

    at com.businessobjects.wp.om.OMQuery.loadSQL(Unknown Source)

    at com.businessobjects.wp.om.OMQuery.getSQLRoot(Unknown Source)

    at com.businessobjects.rebean.wi.SQLDataProviderImpl.getSQLContainer(Unknown Source)

    at org.drei.reports.Document.processSQLDP(Document.java:149)

    at org.drei.reports.Document.processDataProvider(Document.java:142)

    at org.drei.reports.Document.processDocument(Document.java:68)

    at org.drei.reports.Document.main(Document.java:266)

Any idea how I can prevent the checkvalid test?

Thx,

Hakan