The problem.
 
From CVP need to retrieve data from an Oracle database. Some data is available through stored procedures.
 
Call Studio Database Element works with simple SELECT statements, but it doesn't have the ability to execute stored procedures and retrieve the data cursors. SELECT * FROM <stored_proc> is not possible
 
I am considering attacking the problem through an Action Element with a custom Java class. To keep things consistent I would like to be able to pull the db connection info from the Tomcat JNDI configuration, rather than defining the db conn parameters inside the custom class.
 
 
Any insights? Somebody with similar problems that already have a solution?


 
I'm not a real big fan of the Call Studio Database Elementor or the Tomcat JNDI.  I think the best method is to create a custom decision element and not utilize the JNDI.  I did this with MySQL is it is far easier than messing with the out of the box element.  I'm still working cleaning the code up but below is a hacked version of what I am doing.
 
 
 
import java.util.ArrayList;
import java.sql.*;

import com.audium.server.AudiumException;
import com.audium.server.session.DecisionElementData;
import com.audium.server.voiceElement.*;


public class ghtsCountyCheck extends DecisionElementBase implements ElementInterface {

    public ghtsCountyCheck(){
    }

    public String getElementName(){
        return "mysqlDummy";
    }

    public String getDisplayFolderName(){
        return "Custom Elements";
    }

    public String getDescription(){
        return "Check entry prior to building menu";
    }

    public Setting[] getSettings() throws ElementException {
        return null;
    }

    public ExitState[] getExitStates() throws ElementException {
        ExitState exitStateArray[] = new ExitState[4];
        exitStateArray[0] = new ExitState("match", "match", "");
        exitStateArray[1] = new ExitState("select", "select", "");
        exitStateArray[2] = new ExitState("notFound", "notFound", "");
        exitStateArray[3] = new ExitState("error", "error", "");
        return exitStateArray;
    }

    public ElementData[] getElementData() throws ElementException {
        ElementData elementDataArray[] = new ElementData[1];
        elementDataArray[0] = new ElementData("NULL", "NULL");
        return elementDataArray;
    }

    public String doDecision(String name, DecisionElementData decisionData) throws ElementException {
        Connection conn = null;
        ResultSet rs;
       
        int _rowcount = 0;
        ArrayList _resultset = new ArrayList();
       
       
        try {
            String sLastEntry = (String)decisionData.getSessionData("lastEntry");

            decisionData.setSessionData("_rowcount", Integer.toString(_rowcount));
            decisionData.setSessionData("_resultset", _resultset);
           
            try {
                String userName = "XXXXXXX";
                String password = "XXXXXX";
                String url = "jdbc:mysql://XXXXXXXX.com";

                Class.forName ("com.mysql.jdbc.Driver").newInstance ();
                conn = DriverManager.getConnection (url, userName, password);
                System.out.println ("Database connection established");
           
                Statement s = conn.createStatement ();
                s.executeQuery ("SELECT distinct * FROM dbxxxx.XXXXXX where substring(XXXX,1,4)='"+sLastEntry+"' LIMIT 9;");
                rs = s.getResultSet ();

                while (rs.next ()) {
                    _resultset.add(rs.getString("COUNTYNAME").toLowerCase());
                    ++_rowcount;
                }
                rs.close ();
                s.close ();

           
            }
            catch (Exception e) {
                System.err.println ("Cannot connect to database server\n");
                System.err.println(e);
                try {
                    String userName = "XXXXXXX";
                    String password = "XXXXXXX";
                    String url = "jdbc:mysql://YYYYYYYYY.com";

                    Class.forName ("com.mysql.jdbc.Driver").newInstance ();
                    conn = DriverManager.getConnection (url, userName, password);
                    System.out.println ("Database connection established");
           
                    Statement s = conn.createStatement ();
                    s.executeQuery ("SELECT distinct *  FROM dbYYYY.XXXXX where substring(XXXXXX,1,4)='"+sLastEntry+"' LIMIT 9;");
                    rs = s.getResultSet ();

                    while (rs.next ()) {
                        _resultset.add(rs.getString ("FIELD"));
                        ++_rowcount;
                    }
                    rs.close ();
                    s.close ();
           
                }
                catch (Exception ex) {
                    System.err.println ("Cannot connect to database server\n");
                    System.err.println(ex);
                }
          
           
            }
            finally {
                if (conn != null) {
                    try    {
                        conn.close ();

                        if (_rowcount == 1) {
                            return "match";
                        } else if (_rowcount > 0) {
                            decisionData.setSessionData("_rowcount", Integer.toString(_rowcount));
                            decisionData.setSessionData("_resultset", _resultset);
                            return "select";
                        } else {
                            return "notfound";
                        }
                   
                    }
                    catch (Exception e) { /* ignore close errors */ }
                } else {
                    return "error";
                }
            }
        }
        catch(RuntimeException e){
            e.printStackTrace();
        }
        catch(AudiumException e){
            e.printStackTrace();
        }
        return "error";
    }

JNDI is the right choise for the DB connectivity as the connection pool will be shared accross all the application.
 
Action or decision custom element is some thing your choise. You need to see which best suits your requirment. As far as u JNDI is concerned. Assuming you have already configured JNDI resource name.

//required class files that needs to imported:
import java.sql.Connection;
import javax.naming.InitialContext;
import javax.naming.Context;
import javax.sql.DataSource;
 
//Note: For SQL datasource you can use the oracle provide classes but ensure to load those jar files and import appropriately.
 
//Under doAction or doDecision
    Connection connection =null;
    // Create the initial context. No JNDI properties are to be supplied here
    Context  initialContext = new InitialContext();
    DataSource dataSource = null;                // datasource object
    // Look up the datasource using the logical name  specified in the ejb-location tag in data-sources.xml
    // You must always cast or narrow the object that JNDI returns to the  DataSource, because the
    // JNDI lookup() method returns a Java object.
    dataSource = (DataSource)initialContext.lookup("jdbc/OracleDS");
    // Establishing db connection
    connection =  dataSource.getConnection();
// once the connection is establised you know what to do i suppose

Hi,
 
I Don't have an Oracle DB, but I'd like to tell my CVPD students the details on executing an ORACLE stored procedure from within the Studio DB element.

So, I'm hoping someone can help me.
 
Let's say the stored proc is named MYPROC and I pass it one variable,
and it gives me back one row of data (variables named RETURN1, RETURN2).
 
In Studio, how exactly do I invoke this procedure?
1a. Would I use a DB element (Database_01) , select the operation 'Single'
1b  Then do I the following in as the command:  call MYPROC({Data.Session.var1})


1c. And then how would I access the return values? Would they be stored as element data named
{Data.Element.Database_01.RETURN1}, etc.



Much thanks!
Janine
www.TrainingTheExperts.com
 
 
 
 
 

Janine,
Did you figure this out? I never had to deal with a stored procedures in CVP before but now I need to call an Oracle CP.
 
thanks