1 Reply Latest reply: Aug 29, 2013 1:37 PM by a1a5a1f5-6208-4fc2-ba75-719e3136326b RSS

    OATS - Open Script: How to capture data from the SQL query

    988362
      Hi Everyone,

      I am trying to gather data for the Open Script tool using SQL query.

      As an example:
      SELECT CONTAINER_ID FROM available_containers WHERE CONTAINER_TYPE = ? AND STATUS = ?

      The next step of the script will be using the value of the CONTAINER_ID for inputting it in a Form.

      Can anyone tell me how I can script that?

      Thank you!
        • 1. Re: OATS - Open Script: How to capture data from the SQL query
          a1a5a1f5-6208-4fc2-ba75-719e3136326b

          First Step connect to your DB either by using the below function or can use the code simply, but do not forget to pass HOSTNAME, ServiceName, DBUserName, DBPassword from your testing database.

           

          public Connection dbconnect() throws Exception

          {

               java.sql.Connection dbconnection = null;

               java.sql.SQLException jsqlexcep;

          try

          {

               Class.forName("oracle.jdbc.driver.OracleDriver");

               dbconnection = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@"<<HOSTNAME (replace)>>":1521/"<<ServiceName(replace)>>"", "<<DBUserName (replace)>>",""+

               <<DBPassword (replace)>>");

          }

               catch (SQLException e)

          {

               System.err.print(e.getMessage());

          }

               finally

          {

               return dbconnection;

          }

           

          }

           

          Second step is to execute the query, you can pass your above SQL statement as a string, which returns result set.

           

          public ResultSet executequery(Connection connection, String sql_stmt) throws Exception

          {

               try

               {

                    Statement stmt;

                    ResultSet rset;

                    stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

                    rset = stmt.executeQuery(sql_stmt);

                    return rset;

               }

               catch (SQLException e)

               {

                    System.err.print(e + "\n" + sql_stmt);

                   return null;

               }

          }

           

          Third loop through your result set

          Example:

          ResultSet.next();

          CONTAINER_ID = ResultSet.getString(1);