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();
    }
}


Comments:

Hi Is it possible to use this to pass SQL statement and parameters from an application developed using Oracle Forms? Kind regards, Aparna

Posted by Aparna on August 03, 2010 at 06:39 PM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« July 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
  
       
Today