Passing parameters to sql within the designer

[Former Member]

<p>I have an sqlpage with sql in it (obviously), but the sql requires parameters to be passed to it (i.e. start date, end date).</p><p>Within the sql this particular code looks like this (for example):</p><p>AND O.ORDER_DATE >= TO_DATE(?, &#39;dd-mon-yyyy hh24:mi&#39;)</p><p>But on validation of the sqlpage, I get the oracle "not all variables bound" error. And until the sql statement validates I can&#39;t connect it to a report.</p><p>&nbsp;</p><p>How do I specify the parameters the sql requires through the designer? I will want to link these to the parameters specified for the rpt. Is this even possible? </p>

[SJohnson]

Hi,

The code for the class below can be used to add a custom query which contains a parameter to a report. Potentially you could write some UI around this process to pretty it up, but this should point you in the right direction.

---

import java.io.IOException;

import com.crystaldecisions.sdk.occa.report.application.DatabaseController;
import com.crystaldecisions.sdk.occa.report.application.ReportClientDocument;
import com.crystaldecisions.sdk.occa.report.data.CommandTable;
import com.crystaldecisions.sdk.occa.report.data.ConnectionInfo;
import com.crystaldecisions.sdk.occa.report.data.FieldValueType;
import com.crystaldecisions.sdk.occa.report.data.IConnectionInfo;
import com.crystaldecisions.sdk.occa.report.data.IParameterField;
import com.crystaldecisions.sdk.occa.report.data.ParameterField;
import com.crystaldecisions.sdk.occa.report.data.ParameterFieldDiscreteValue;
import com.crystaldecisions.sdk.occa.report.data.TableLinks;
import com.crystaldecisions.sdk.occa.report.data.Values;
import com.crystaldecisions.sdk.occa.report.lib.PropertyBag;
import com.crystaldecisions.sdk.occa.report.lib.PropertyBagHelper;
import com.crystaldecisions.sdk.occa.report.lib.ReportSDKException;

public class AddCustomSQLQuery {

   /**
    * @param args
    * @throws ReportSDKException
    */
    public static void main(String[] args) throws ReportSDKException, IOException {
//Â Â Â Â Â Â Â TODO Auto-generated method stub
      ReportClientDocument rptDoc = new ReportClientDocument();
      String JDBC_URL = null;
      String JDBC_Class = null;
      String UserName = null;
      String Password = null;
      String TableName = null;
      String ParameterName = null;
      String TableAlias = null;
      TableLinks dummyLink = null;

      JDBC_URL="jdbc:derby:classpath:/Xtreme;create=true;upgrade=true";
      JDBC_Class ="org.apache.derby.jdbc.EmbeddedDriver";
      UserName = "bobj";
      Password = "bobj";
      TableName = "Custom_Query";
      TableAlias= "Custom_Query";
      ParameterName = "Country";

      rptDoc.setReportAppServer("inproc:jrc");
      rptDoc.newDocument();
      PropertyBag attrs = new PropertyBag();
      attrs.put(PropertyBagHelper.CONNINFO_DATABASE_DLL, "crdb_jdbc.dll");
      attrs.put(PropertyBagHelper.CONNINFO_SERVER_TYPE, "JDBC (JNDI)");
      attrs.put(PropertyBagHelper.CONNINFO_JDBC_DATABASECLASSNAME, JDBC_Class);
      attrs.put(PropertyBagHelper.CONNINFO_JDBC_CONNECTION_URL, JDBC_URL);
      IConnectionInfo connInfo = new ConnectionInfo();
      connInfo.setAttributes(attrs);
      connInfo.setUserName(UserName);
      connInfo.setPassword(Password);
      CommandTable table = new CommandTable ();
      table.setAlias (TableAlias);
      table.setName (TableName);
      table.setConnectionInfo(connInfo);


      IParameterField param = new ParameterField ();
      param.setType(FieldValueType.stringField);

      param.setName(ParameterName);
      param.setDescription("param");
      Values newParamValues = new Values ();

      ParameterFieldDiscreteValue newValue = new ParameterFieldDiscreteValue ();
      newValue.setValue ("Canada");
      newParamValues.add (newValue);
      param.setCurrentValues (newParamValues);
      table.getParameters ().add (param);
     Â

//Â Â Â Â Â Â Set the SQL query you want to use. The parameter you added in the code above will be referenced
//Â Â Â Â Â Â in the query using the following syntax {?ParameterName}
      table.setCommandText("SELECT * FROM APP.CUSTOMER WHERE COUNTRY = '{?" + ParameterName + "}'");
      DatabaseController dbCtr = rptDoc.getDatabaseController();
      dbCtr.addTable(table, dummyLink);

//Â Â Â Â Â Â if your stored procedure accepts null values and you left the parameter code above
//Â Â Â Â Â Â commented out the Verify Database call will add the parameter to the Parameter list
      rptDoc.verifyDatabase();

//Â Â Â Â Â Â save the report to a local destination. Passing in 1 as the last variable will overwrite
//Â Â Â Â Â Â existing reports, passing in 0 will throw an error if the report already exists
      rptDoc.saveAs("CommandTableNew.rpt", "C:/Samples/Sample Reports", 1);
      System.out.println("Done");
   }

}

---

By the way, this code should work against the sample Derby database which ships with CR4E if you wanted to test it out as is. Just create this class under the src folder of a Crystal Reports Web Project to ensure that the Derby DB and drivers are in the classpath.

Regards,
Sean Johnson (CR4E Product Manager)

[SJohnson]

<p>Hi,</p><p> I am not too sure how to create a SQL statement in the SQL Scrapbook editor to include the option for parameters. This is a default Eclipse editor that we have extended to enable custom queries to be surfaced in the Crystal Reports for Eclipse designer. You can always create a parameter within the designer and add it to the record filter. This would prompt the end-user to pass in the required parameter at runtime.</p><p>Additionally, you can use the original query to get the meta-data into the report for design time purposes. Then at runtime you can modify the SQL statement based on the parameters you want and pass this into the report. The JSP Page Wizard includes an option to generate code to pass in a resultset at runtime. The auto-generated code should include the database connection information you included in the SQL Scrapbook page as well as the SQL query. All you should need to do is modify the WHERE portion of the query based on the new data.</p><p>Try this and let me know how it works for you. </p><p>Regards,<br />Sean Johnson (CR4E Product Manager) </p>

[Former Member]

I tried to the same with MSAccess by ODBC but it doesn´t work.