X

An Oracle blog about BI Publisher

Setting SQL

Got a good one today that had me a little stumped until I RTFM! To be more precise RTFJD - the JD is the javadoc. The data engine we have does not only consume data templates but you can also throw SQL at it and get XML in return. Neat! One of our customers has been struggling with this, they want to be able to pass parameters to the sql at runtime - we support that but they were having pain getting it to work. Our trusty support guys have been nagging me for some help and I finally got to it last night.



The data engine has a setSql method to allow you to pass the sql and you can then pass parameters as a Hashtable or ArrayList thus:



DataProcessor dataProcessor = new DataProcessor();
dataProcessor.setConnection(jdbcConnection);
//Set the query
dataProcessor.setSql("select * from departments where department_id = :p_DeptNo");
//Create hash to hold param val
Hashtable parameters = new Hashtable();
parameters.put("p_DeptNo","10");
dataProcessor.setParameters(parameters);
dataProcessor.setOutput("c:\\temp\\EmpDetails.xml");
dataProcessor.processData();




Looks straight forward enough but it would not compile or run. I scratched my head for a while. I know we dont docunment the setSql method that well. I ended up in the javadoc and found my problem. I was using the java.util.Hashtable. Hey, I know it, I use it regularly so why not. However the data engine actually needs the Collections version of the Hashtable - com.sun.java.util.collections.Hashtable .. dang! A quick update on the import statements et voila, I have XML coming out based on parameter values. A good lesson for me to read all the doc and apologies to support and the customer for taking so danged long about it!



If you're interested here's the complete code for the method:



import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import com.sun.java.util.collections.Hashtable;
import oracle.apps.xdo.dataengine.DataProcessor;
import oracle.jdbc.driver.OracleDriver;




public class DTSql {
    public DTSql() {
   
        try {
         //get connection
        Connection jdbcConnection = getConnection();
         //Initilization//
         DataProcessor dataProcessor = new DataProcessor();
        dataProcessor.setConnection(jdbcConnection);
          //Set the SQL
          dataProcessor.setSql("select * from departments where department_id = :p_DeptNo");
          //Create hash for parameters
          Hashtable parameters = new Hashtable();
          parameters.put("p_DeptNo","10");
          dataProcessor.setParameters(parameters);
          dataProcessor.setOutput("c:\\temp\\EmpDetails.xml");
          dataProcessor.processData();
        }
        catch (Exception e)
         {
         e.printStackTrace();
         }
  
    }
        public static Connection getConnection() throws SQLException {
            String username = "hr";
            String password = "hr";
            String thinConn = "jdbc:oracle:thin:@IPG.US.ORACLE.COM:1525:ora10g";
            DriverManager.registerDriver(new OracleDriver());
            Connection conn =
                DriverManager.getConnection(thinConn, username, password);
            conn.setAutoCommit(false);
            return conn;
        }



    public static void main(String[] args) {
        DTSql dTSql = new DTSql();
    }
}



Join the discussion

Comments ( 1 )
  • Aparna Wednesday, August 4, 2010
    Hi
    Is it possible to use this to pass SQL statement and parameters from an application developed using Oracle Forms?
    Kind regards,
    Aparna
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.

Recent Content