Call Studio Database Element and Stored Procedures

cdnadmin · ‎01-24-2014

This document was generated from CDN thread

Created by: Sidney Orret on 20-06-2009 07:14:29 AM
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?

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Bill McDonald on 21-07-2009 09:51:27 PM
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";
    }

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Juned Ahsan on 31-07-2009 06:08:39 AM
Hello Bill,

I have used Stored Procedures quite extensively with CVP studio and have never faced a problem with it. You have not mentioned any error/exception you have faced while calling a stored procedure in an action element.
In the code snippet you have attached you are trying to run a simple Statement but not a stored procedure call. According to me the best approach to call a stored procedure is by using a decision element. Keep your DB java code in a
normal java class and call its method from your decision element.

Regards,
Juned

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Manoj Anantha on 05-08-2009 02:12:51 PM
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

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: GEOFFREY THOMPSON on 18-08-2009 02:03:47 AM
Two points to note in the above. I'm not doing this with Oracle, but with SQL Server

1. You should specify the context lookup fully by including the middle layer of your computing environment, like this:

    dataSource = (DataSource)initialContext.lookup("jdbc:/comp/env/jdbc/SomeDSName");
where you have defined the resource through server.xml and context.xml in Tomcat\conf. Otherwise you will see an error in Tomcat - an exception on the context.

2. Compared to the first coding snippet, the SQL jar needs to be in Tomcat\common\lib rather than the more normal spot VXMLServer\common\lib. I've developed custom classes using both techniques.

When developing these more complex applications/classes I like to run the Tomcat server (CVP VXML) in the forground, not as a service. First of all, stop the service. You just need to define a couple of environment variables (put them in a little batch file that you run first)

set JRE_HOME=C:\Cisco\CVP\jre
set BASEDIR=C:\Cisco\CVP\VXMLServer\Tomcat
set AUDIUM_HOME=C:\Cisco\CVP\VXMLServer

then run setclasspath.bat in Tomcat\bin then call startup.bat.

Call shutdown.bat to stop it.

Any exceptions will be right in the big Tomcat window, instead of buried in the err or stdout file.

Regards,
Geoff

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Janine Graves on 18-02-2011 01:04:56 PM
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

Hello Bill,

I have used Stored Procedures quite extensively with CVP studio and have never faced a problem with it. You have not mentioned any error/exception you have faced while calling a stored procedure in an action element.
In the code snippet you have attached you are trying to run a simple Statement but not a stored procedure call. According to me the best approach to call a stored procedure is by using a decision element. Keep your DB java code in a
normal java class and call its method from your decision element.

Regards,
Juned

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Sidney Orret on 18-02-2011 01:48:10 PM
Janines,

As far as Oracle, you can use OracleXE that
works just fine. OracleXE is "light" weight and you can install it on
the same box hosting your lab VXML Server.

As far as
executing stored procedures,below is the java code and element configuration that was built with the valuable input from CDN participants. I
am sure there are better or different ways to code it, but this one
works.I modified the package name a bit because was giving away too much
information, but the code should build as soon as you correct the
package name.

Below is the java code, and the VXML code of the element. I think you can make sense of this looking at both.

Hope it helps.

Sidney
-------------
table.MsoNormalTable { font-size: 10pt; font-family: "Times New Roman","serif"; }
package com.<integrator>.<customer>.elements;

import java.lang.reflect.Method;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.Date;

import java.sql.Timestamp;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.sql.DataSource;

import com.audium.server.AudiumException;

import com.audium.server.session.DecisionElementData;

import com.audium.server.voiceElement.DecisionElementBase;

import com.audium.server.voiceElement.ElementData;

import com.audium.server.voiceElement.ElementException;

import com.audium.server.voiceElement.ElementInterface;

import com.audium.server.voiceElement.ExitState;

import com.audium.server.voiceElement.Setting;

import com.audium.server.xml.DecisionElementConfig;

import com.audium.server.xml.ElementConfig;

public class StoredProcElement extends DecisionElementBase implements
ElementInterface

{

   private String elementName =
"StoredProcedureElement";

   private String elementDescription = "Custom element to
call a stored procedure and store return values in element data";

   private String elementDisplayFolderName = "Custom
Database";



   /**

   * Returns the Exit States

   * @return The custom element Exit States as an array

   * @exception throws Element Exception

   */

   public ExitState[] getExitStates() throws ElementException {

       //Returns Exit States

       ExitState[] exitStateArray = new
ExitState[2];



       exitStateArray[0] = new
ExitState(ExitState.DONE);

       exitStateArray[1] = new
ExitState("error","error","An error occurs when
executing stored procedure call");



       return exitStateArray;

   }





   /**

   * Returns the element Settings

   * @return The custom element settings

   * @exception throws Element Exception

   */

   public Setting[] getSettings() throws ElementException {

       //returns settings

       Setting[] settingArray = new
Setting[8];



        settingArray[0] =

           new Setting(


"Stored_Proc",


"Stored Procedure",


"The statement used to invoke the stored procedure",


true,


true,


true,


Setting.STRING);



        settingArray[1] =

           new Setting(


"JNDI_Name",


"JNDI Variable Name",


"The name of the JNDI variable that holds the DB configuration",


true,


true,


true,


Setting.STRING);



        settingArray[2] =

            new Setting(


"Stored_Proc_Input",


"Stored Proc Input",


"Input value for the stored procedure",


true,


false,


true,


Setting.STRING);



        settingArray[3] =

            new Setting(


"Stored_Proc_InType",


"Proc Input Type",


"The capitalized name of the data type, like String, Int, Double,
etc.",


true,


false,


true,


Setting.STRING);



        settingArray[4] =

            new Setting(


"Stored_Proc_InName",


"Proc Input Name",


"The name of the input parameter",


true,


false,


true,


Setting.STRING);



        settingArray[5] =

            new Setting(


"Stored_Proc_OutType",


"Proc Output Type",


"The types of procedure outputs as an integer, one of the Types or
OracleTypes values. Please see those class docs for the integer value for each
type name",


true,


false,


true,


Setting.INT);



        settingArray[6] =

            new Setting(


"Stored_Proc_OutName",


"Proc Output Name",


"The name of the output parameter",


true,


false,


true,


Setting.STRING);



        settingArray[7] =

            new Setting(


"Stored_Proc_RetType",


"Output Return Type",


"The capitalized name of the data type, like String, Int, Double,
etc.",


true,


false,


true,


Setting.STRING);



       return settingArray;


   }



   /**

   * Returns the element Data

   *

   * @return The custom element Data as an array

   * @exception throws Element Exception

   */

   public ElementData[] getElementData() throws
ElementException {

       return null;

   }



   /**

   * Implements the decision element.

   */

   public String doDecision(String name, DecisionElementData
decisionData) throws AudiumException

   {

       DecisionElementConfig config =
decisionData.getDecisionElementConfig();



       String jndiName =
(String)config.getSettingValue("JNDI_Name",decisionData);

       String statement = "{
"+(String)config.getSettingValue("Stored_Proc",decisionData)+"
}";



       String[] inputTypes =
config.getSettingValues("Stored_Proc_InType", decisionData);

       Object[] inputValues =
config.getSettingValuesAsObjects("Stored_Proc_Input", decisionData);

       String[] inputNames =
config.getSettingValues("Stored_Proc_InName", decisionData);



       String[] outTypes = config.getSettingValues("Stored_Proc_OutType",
decisionData);

       String[] outNames =
config.getSettingValues("Stored_Proc_OutName", decisionData);

       String[] returnTypes =
config.getSettingValues("Stored_Proc_RetType", decisionData);



       if (inputValues == null || inputTypes ==
null || inputNames ==null ||


inputValues.length != inputTypes.length || inputValues.length !=
inputNames.length)

       {

           throw new
AudiumException("number of inputs does not match number of input
types");

       }



       int numInputs = inputValues.length;

       int numOutputs = outTypes.length;



       Connection conn = null;

       CallableStatement cs = null;



       try {

           Context initContext =
new InitialContext();


//System.out.println("LOOKING FOR "+jndiName);

           Context
envContext = (Context)initContext.lookup("java:/comp/env");

           DataSource ds =
(DataSource)envContext.lookup(jndiName);

           //System.out.println("FOUND
"+ds.toString());

           conn =
ds.getConnection();



           cs =
conn.prepareCall(statement);

           String type = null;

           // take the name of
the type (like Int) and its value

           // and set it on the
cs by invoking a method name like

           // setInt

           int i = 0;

           for
(;i<numInputs;i++) {


type = inputTypes;


Class parmType = getArg(type);

               if
(parmType == null) {


    //System.out.println("Unknown parameter type, may
need to update ExecuteStoredProc.java.");


    return "Error";

               }


Class [] args = new Class[2];


args[0] = int.class;


args[1] = parmType;



Method m = CallableStatement.class.getMethod("set"+type,args);


//System.out.println("calling setinput set"+type+" for
input "+(i+1)+" to "+inputValues);


Object [] parms = new Object[2];


parms[0] = i+1;


parms[1] = inputValues;


m.invoke(cs,parms);


           }



           // similarly, set the
output parameter types using the output

           // variable name

           int outType;

           for (int o =
0;o<numOutputs;o++) {


outType = Integer.parseInt(outTypes);


//System.out.println("register output "+outNames+" as
"+outType);


cs.registerOutParameter(o+numInputs+1, outType);


           }




           cs.execute();



           // store the output
return values in variables that have the

           // same name as the
output parameter

           for (int out =
0;out<numOutputs;out++) {


type = returnTypes;


System.out.println("StoredProc calling getoutput
get"+type+" for output "+outNames);


Method m = CallableStatement.class.getMethod("get"+type,int.class);


Object ret = m.invoke(cs,out+numInputs+1);

               int
dataType = 0;

               if
(type.equals("Int")) {


    dataType = ElementConfig.PD_INT;

               }
else if (type.equals("Double") || type.equals("Float")) {


    dataType = ElementConfig.PD_FLOAT;

               }
else {


    dataType = ElementConfig.PD_STRING;


               }

               if
(ret !=null) {


    System.out.println("StoredProc storing
"+outNames+"="+ret);


    config.setElementData(outNames, ret.toString(),
dataType, true, ElementConfig.AFTER_EXITING_ELEMENT);

               }
else {


    System.out.println("StoredProc storing
"+outNames+"=null");


    config.setElementData(outNames, null, dataType,
true, ElementConfig.AFTER_EXITING_ELEMENT);

               }

           }

       } catch (Exception e) {

           System.out.println("Error
creating and calling stored proc");

           e.printStackTrace();

           return
"error";

       } finally {

           try {

               if
(cs != null) {


    cs.close();

               }

               if
(conn != null) {



    conn.close();

               }

           } catch (Exception ex)
{


System.out.println("Error closing connection");


ex.printStackTrace();

               return
"error";

           }

       }

       return "done";

   }



   private Class getArg(String type) {

       Class arg = null;

       if (type.equals("Int"))
{

           arg = int.class;

       }

       else if (type.equals("Boolean"))
{

           arg = boolean.class;

       }

       else if (type.equals("Date")) {

           arg = Date.class;

       }

       else if (type.equals("Double"))
{

           arg = double.class;

       }

       else if (type.equals("Float"))
{

           arg = float.class;

       }

       else if (type.equals("Long")) {

           arg = long.class;

       }

       else if (type.equals("String"))
{

           arg = String.class;

       }

       else if (type.equals("Timestamp"))
{

           arg = Timestamp.class;

       } else {

           return null;

       }

       return arg;

   }

   public String getDescription() {

       return elementDescription;

   }

   public String getDisplayFolderName() {

       return elementDisplayFolderName;

   }

   public String getElementName() {

       return elementName;

   }





}

********************** the VXML of the element, as used in an application ***********************

--

<?xml version="1.0" encoding="UTF-8"?>

<decision_element id="Element@18543025:1248374943265"
name="GetClaimantDetails" version="6.0.1">

<config>

<log>

<data create_when="After"
name="IC_Query.tic">{Data.Element.GetClaimantDetails.tic}</data>

<data create_when="After"
name="IC_Query.iic">{Data.Element.GetClaimantDetails.iic}</data>

<data create_when="After" name="IC_QUery.pin">{Data.Element.GetClaimantDetails.pin}</data>

</log>

<settings>

<setting name="Stored_Proc">call IC_Query(?, ?, ?, ?,
?,?)</setting>

<setting name="JNDI_Name">jdbc/soodb</setting>

<setting
name="Stored_Proc_Input">{Data.Session.Caller.SSN}</setting>

<setting name="Stored_Proc_InType">String</setting>

<setting name="Stored_Proc_InName">ssn</setting>

<setting name="Stored_Proc_OutType">12</setting>

<setting name="Stored_Proc_OutType">12</setting>

<setting name="Stored_Proc_OutType">12</setting>

<setting name="Stored_Proc_OutType">12</setting>

<setting name="Stored_Proc_OutType">12</setting>

<setting name="Stored_Proc_OutName">ret_code</setting>

<setting
name="Stored_Proc_OutName">claim_status</setting>

<setting name="Stored_Proc_OutName">tic</setting>

<setting name="Stored_Proc_OutName">pin</setting>

<setting name="Stored_Proc_OutName">iic</setting>

<setting name="Stored_Proc_RetType">String</setting>

<setting name="Stored_Proc_RetType">String</setting>

<setting name="Stored_Proc_RetType">String</setting>

<setting name="Stored_Proc_RetType">String</setting>

<setting name="Stored_Proc_RetType">String</setting>

</settings>

</config>

</decision_element>

----------------------------

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Luis Yrigoyen on 10-04-2013 08:26:24 AM
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

Janine Graves:
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

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Edward Umansky on 05-06-2013 05:11:18 PM
Unfortunately, this cannot be done with the built-in Call Studio element. The problem is that Oracle stored procs simply don't work the way they do in SQL Server. In SQL Server, a stored proc can return a resultset - a tabular list of values - just like a select statement.

Oracle stored procedures simply don't allow that. Instead, Oracle stored procs return data by defining "output" parameters which you must register and pass into the stored proc itself. Once the procedure is executed, these output parameters hold the results returned. Since the built-in db element depends on resultsets for returned data, and has no facility to define and register outbput parameters, it can't be used to call Oracle stored procedures and retrieve results.

You would need to write a completely custom element to do this. A good example of the code you need can be found here: http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Janine Graves on 06-06-2013 09:56:00 AM

Edward Umansky:
Unfortunately, this cannot be done with the built-in Call Studio element. The problem is that Oracle stored procs simply don't work the way they do in SQL Server. In SQL Server, a stored proc can return a resultset - a tabular list of values - just like a select statement.

Oracle stored procedures simply don't allow that. Instead, Oracle stored procs return data by defining "output" parameters which you must register and pass into the stored proc itself. Once the procedure is executed, these output parameters hold the results returned. Since the built-in db element depends on resultsets for returned data, and has no facility to define and register outbput parameters, it can't be used to call Oracle stored procedures and retrieve results.

You would need to write a completely custom element to do this. A good example of the code you need can be found here: http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/

Hi Ed,

I can't test Oracle, but MySql stored procedures work with Studio Database element, and they also have in, out parameters. With MySql you use one element to execute the Stored Proc, and another element to retrieve the resulting variables from the database! If you have Oracle, perhaps you can test this out and let me know if it works.

--This is how I created the Stored Proc on MySql
delimiter //
create procedure accountbal(in oacctnum int, out obalance int)
begin
select balance1 into obalance from account where acctnum=oacctnum;
end //

And then in Studio, execute the stored procedure from a Studio DB element:
Type: Update
SQL Query: call accountbal(1111, @dbvarBalance)

And then execute another Studio DB element to get the value from the database variable @dbvarBalance into Studio element data named 'acctBalance',
Type: 'Single'
SQL Query: select @dbvarBalance as acctBalance

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Janine Graves on 06-06-2013 10:00:36 AM

Edward Umansky:
Unfortunately, this cannot be done with the built-in Call Studio element. The problem is that Oracle stored procs simply don't work the way they do in SQL Server. In SQL Server, a stored proc can return a resultset - a tabular list of values - just like a select statement.

Oracle stored procedures simply don't allow that. Instead, Oracle stored procs return data by defining "output" parameters which you must register and pass into the stored proc itself. Once the procedure is executed, these output parameters hold the results returned. Since the built-in db element depends on resultsets for returned data, and has no facility to define and register outbput parameters, it can't be used to call Oracle stored procedures and retrieve results.

You would need to write a completely custom element to do this. A good example of the code you need can be found here: http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/

Hi Ed,
Can you also show an example of how to call a Stored Proc when using MS SQL? Is it done using Type: Single (or Multiple, depending on the number of rows returned?) or is it Type:Update?
And for the Sql Query, must you enter something like 'call procName(param1, param2)' or 'exec procName(param1, param2)' ?

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Edward Umansky on 06-06-2013 01:26:33 PM

Janine Graves:
Edward Umansky:
Unfortunately, this cannot be done with the built-in Call Studio element. The problem is that Oracle stored procs simply don't work the way they do in SQL Server. In SQL Server, a stored proc can return a resultset - a tabular list of values - just like a select statement.

Oracle stored procedures simply don't allow that. Instead, Oracle stored procs return data by defining "output" parameters which you must register and pass into the stored proc itself. Once the procedure is executed, these output parameters hold the results returned. Since the built-in db element depends on resultsets for returned data, and has no facility to define and register outbput parameters, it can't be used to call Oracle stored procedures and retrieve results.

You would need to write a completely custom element to do this. A good example of the code you need can be found here: http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/
Hi Ed,
Can you also show an example of how to call a Stored Proc when using MS SQL? Is it done using Type: Single (or Multiple, depending on the number of rows returned?) or is it Type:Update?
And for the Sql Query, must you enter something like 'call procName(param1, param2)' or 'exec procName(param1, param2)' ?

Don't do this! The reason this works is that the "@variable" syntax is a MySQL-specific feature known as user-defined variables. What is unique and useful about these variables is that they retain their scope across the lifetime of a connected session, allowing you to reference them across different statements, as you did in your Call Studio example. The problem is that the db step in VXML Server uses connection pooling. This is important for efficient performance, but it has the side-effect that multiple calls may use the same db connection before it is ever closed. Since a MySQL user-defined variable retains its scope for the lifetime of a connection, this exposes you to some serious race conditions.For example, let's say you have two calls executing the database interaction at about the same time. Call 1 executes the update then returns the connection to the pool. Before Call 1 has a change to execute the query step, Call 2 borrows the same connection from the pool and executes its update step. Now, if Call 1 retrieves the original connection and exectues it's query, it'll get the data for Call 2! Very bad. In fact you don't even need two concurrent calls hitting the exact same step to see issues, the update and select pieces may not even retrieve the same connection if the connection pool is being used by multiple calls.You won't see this in testing because of the low call volume. You may not even catch it in production, but since it's such a serious risk - and a nightmare to debug if you ever do hit it - you should absolutely avoid it.If you are using OUT parameters in MySQL, you'll need to use custom Java code similar to the Oracle example. Unlike Oracle however, MySQL actually lets you return resultsets directly from a stored procedure just like in MSSQL. Your stored proc then can be rewritten like so:delimiter //
create procedure accountbal(in oacctnum int)
begin
select balance1 from account where acctnum=oacctnum;
end // You can then call it like so:call accountbal(1111)You can call this via a single or multiple, depending on how many results are returned. Just think of it as being identical to calling "select balance1 from account where acctnum=oacctnum" directly. The same holds true for MSSQL procedures. The syntax for calling an MSSQL procedure that returns a resultset is like so:EXEC yourProc 'param1', 'param2', 'param3'The lesson here is there isn't a single rule as to how to use stored procedures within Call Studio, it depends entirely on how the stored proc is implemented. In most cases, this is something dictated by the customer, not something you have control over. Otherwise, you could simply use a direct query and avoid the hassle. Here's a rough flow for figuring out what to dooes the proc return results?
If not, you should be able to call it via the "update" query type.
If yes, does the proc return a resultset? (Oracle cannot return a resultset at all, MySQL and MSSQL can)
  If OUT parameters, you will need to write custom Java code.
  If it returns a resultset, you can use it the same way as if you were calling the query directly.

Subject: RE: New Message from Edward Umansky in Customer Voice Portal (CVP) - CVP -
Replied by: Hemal Mehta on 06-06-2013 01:46:44 PM
Why limit yourself with inbuilt studio element for complex DB operations. I would just create my own class and execute it directly or write a custom element to do it.   With inbuilt elements you always get somewhat restricted. Same thing with web services element.
Hemal

From: Cisco Developer Community Forums [mailto:cdicuser@developer.cisco.com]
Sent: Thursday, June 06, 2013 1:40 PM
To: cdicuser@developer.cisco.com
Subject: New Message from Edward Umansky in Customer Voice Portal (CVP) - CVP - All Versions: RE: Call Studio Database Element and Stored Procedures

Edward Umansky has created a new message in the forum "CVP - All Versions": --------------------------------------------------------------
Janine Graves:

--This is how I created the Stored Proc on MySql
delimiter //
create procedure accountbal(in oacctnum int, out obalance int)
begin
select balance1 into obalance from account where acctnum=oacctnum;
end //

And then in Studio, execute the stored procedure from a Studio DB element:
Type: Update
SQL Query: call accountbal(1111, @dbvarBalance)

And then execute another Studio DB element to get the value from the database variable @dbvarBalance into Studio element data named 'acctBalance',
Type: 'Single'
SQL Query: select @dbvarBalance as acctBalance

Don't do this! The reason this works is that the "@variable" syntax is a MySQL-specific feature known as user-defined variables. What is unique and useful about these variables is that they retain their scope across the lifetime of a connected session, allowing you to reference them across different statements, as you did in your Call Studio example. The problem is that the db step in VXML Server uses connection pooling. This is important for efficient performance, but it has the side-effect that multiple calls may use the same db connection before it is ever closed. Since a MySQL user-defined variable retains its scope for the lifetime of a connection, this exposes you to some serious race conditions.

For example, let's say you have two calls executing the database interaction at about the same time. Call 1 executes the update then returns the connection to the pool. Before Call 1 has a change to execute the query step, Call 2 borrows the same connection from the pool and executes its update step. Now, if Call 1 retrieves the original connection and exectues it's query, it'll get the data for Call 2! Very bad. In fact you don't even need two concurrent calls hitting the exact same step to see issues, the update and select pieces may not even retrieve the same connection if the connection pool is being used by multiple calls.

You won't see this in testing because of the low call volume. You may not even catch it in production, but since it's such a serious risk - and a nightmare to debug if you ever do hit it - you should absolutely avoid it.

If you are using OUT parameters in MySQL, you'll need to use custom Java code similar to the Oracle example. Unlike Oracle however, MySQL actually lets you return resultsets directly from a stored procedure just like in MSSQL. Your stored proc then can be rewritten like so:

delimiter //
create procedure accountbal(in oacctnum int)
begin select balance1 from account where acctnum=oacctnum;
end //

Then you can just call it like so:

call accountbal(1111)

You can call this via a single or multiple query, depending on how many results are returned. Just think of it as being identical to calling "select balance1 from account where acctnum=oacctnum" directly. The same holds true for MSSQL procedures. The syntax for calling an MSSQL procedure that returns a resultset is like so:

EXEC yourProc 'param1', 'param2', 'param3'

The lesson here is there isn't a single rule as to how to use stored procedures within Call Studio, it depends entirely on how the stored proc is implemented. In most cases, this is something dictated by the customer, not something you have control over. Otherwise, you could simply use a direct query and avoid the hassle. Here's a rough flow for figuring out what to do:

Does the proc return results?
If not, you should be able to call it via the "update" query type.
If yes, does the proc return a resultset? (Oracle cannot return a resultset at all, MySQL and MSSQL can)
    If OUT parameters, you will need to write custom Java code.
    If it returns a resultset, you can use it the same way as if you were calling the query directly.
--
To respond to this post, please click the following link: http://developer.cisco.com/web/cvp/community/-/message_boards/view_message/15943862 or simply reply to this email.

Subject: RE: New Message from Edward Umansky in Customer Voice Portal (CVP) - CVP -
Replied by: Hemal Mehta on 06-06-2013 01:54:44 PM
Sure, that is true ☺
Hemal

From: Cisco Developer Community Forums [mailto:cdicuser@developer.cisco.com]
Sent: Thursday, June 06, 2013 1:52 PM
To: cdicuser@developer.cisco.com
Subject: New Message from Edward Umansky in Customer Voice Portal (CVP) - CVP - All Versions: RE: New Message from Edward Umansky in Customer Voice Portal (CVP) - CVP -

Edward Umansky has created a new message in the forum "CVP - All Versions": -------------------------------------------------------------- Agreed but Janine is teaching the Cisco CVP class so she needs to be able to give her students guidance on the built-in elements of Call Studio.
--
To respond to this post, please click the following link: http://developer.cisco.com/web/cvp/forums/-/message_boards/view_message/15944300 or simply reply to this email.

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Edward Umansky on 06-06-2013 12:55:20 PM
Janine Graves:

--This is how I created the Stored Proc on MySql
delimiter //
create procedure accountbal(in oacctnum int, out obalance int)
begin
select balance1 into obalance from account where acctnum=oacctnum;
end //

And then in Studio, execute the stored procedure from a Studio DB element:
Type: Update
SQL Query: call accountbal(1111, @dbvarBalance)

And then execute another Studio DB element to get the value from the database variable @dbvarBalance into Studio element data named 'acctBalance',
Type: 'Single'
SQL Query: select @dbvarBalance as acctBalance

Don't do this! The reason this works is that the "@variable" syntax is a MySQL-specific feature known as user-defined variables. What is unique and useful about these variables is that they retain their scope across the lifetime of a connected session, allowing you to reference them across different statements, as you did in your Call Studio example. The problem is that the db step in VXML Server uses connection pooling. This is important for efficient performance, but it has the side-effect that multiple calls may use the same db connection before it is ever closed. Since a MySQL user-defined variable retains its scope for the lifetime of a connection, this exposes you to some serious race conditions.

For example, let's say you have two calls executing the database interaction at about the same time. Call 1 executes the update then returns the connection to the pool. Before Call 1 has a change to execute the query step, Call 2 borrows the same connection from the pool and executes its update step. Now, if Call 1 retrieves the original connection and exectues it's query, it'll get the data for Call 2! Very bad. In fact you don't even need two concurrent calls hitting the exact same step to see issues, the update and select pieces may not even retrieve the same connection if the connection pool is being used by multiple calls.

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Edward Umansky on 06-06-2013 01:21:49 PM
Janine Graves:
...
And then in Studio, execute the stored procedure from a Studio DB element:
Type: Update
SQL Query: call accountbal(1111, @dbvarBalance)

And then execute another Studio DB element to get the value from the database variable @dbvarBalance into Studio element data named 'acctBalance',
Type: 'Single'
SQL Query: select @dbvarBalance as acctBalance

Don't do this! The reason this works is that the "@variable" syntax is a MySQL-specific feature known as user-defined variables. What is unique and useful about these variables is that they retain their scope across the lifetime of a connected session, allowing you to reference them across different statements, as you did in your Call Studio example. The problem is that the db step in VXML Server uses connection pooling. This is important for efficient performance, but it has the side-effect that multiple calls may use the same db connection before it is ever closed. Since a MySQL user-defined variable retains its scope for the lifetime of a connection, this exposes you to some serious race conditions.

For example, let's say you have two calls executing the database interaction at about the same time. Call 1 executes the update then returns the connection to the pool. Before Call 1 has a change to execute the query step, Call 2 borrows the same connection from the pool and executes its update step. Now, if Call 1 retrieves the original connection and exectues it's query, it'll get the data for Call 2! Very bad. In fact you don't even need two concurrent calls hitting the exact same step to see issues, the update and select pieces may not even retrieve the same connection if the connection pool is being used by multiple calls.

You won't see this in testing because of the low call volume. You may not even catch it in production, but since it's such a serious risk - and a nightmare to debug if you ever do hit it - you should absolutely avoid it.
If you are using OUT parameters in MySQL, you'll need to use custom Java code similar to the Oracle example. Unlike Oracle however, MySQL actually lets you return resultsets directly from a stored procedure just like in MSSQL. Your stored proc then can be rewritten like so:

delimiter //create procedure accountbal(in oacctnum int)begin select balance1 from account where acctnum=oacctnum;end //
You can then call it like so:
call accountbal(1111)

You can call this via a single or multiple, depending on how many results are returned. Just think of it as being identical to calling "select balance1 from account where acctnum=oacctnum" directly. The same holds true for MSSQL procedures. The syntax for calling an MSSQL procedure that returns a resultset is like so:

EXEC yourProc 'param1', 'param2', 'param3'

The lesson here is there isn't a single rule as to how to use stored procedures within Call Studio, it depends entirely on how the stored proc is implemented. In most cases, this is something dictated by the customer, not something you have control over. Otherwise, you could simply use a direct query and avoid the hassle. Here's a rough flow for figuring out what to do:

Does the proc return results?If not, you should be able to call it via the "update" query type.If yes, does the proc return a resultset? (Oracle cannot return a resultset at all, MySQL and MSSQL can)If OUT parameters, you will need to write custom Java code.If it returns a resultset, you can use it the same way as if you were calling the query directly.

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Edward Umansky on 06-06-2013 01:22:36 PM
Janine Graves:
Edward Umansky:
Unfortunately, this cannot be done with the built-in Call Studio element. The problem is that Oracle stored procs simply don't work the way they do in SQL Server. In SQL Server, a stored proc can return a resultset - a tabular list of values - just like a select statement.

Oracle stored procedures simply don't allow that. Instead, Oracle stored procs return data by defining "output" parameters which you must register and pass into the stored proc itself. Once the procedure is executed, these output parameters hold the results returned. Since the built-in db element depends on resultsets for returned data, and has no facility to define and register outbput parameters, it can't be used to call Oracle stored procedures and retrieve results.

You would need to write a completely custom element to do this. A good example of the code you need can be found here: http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/
Hi Ed,

I can't test Oracle, but MySql stored procedures work with Studio Database element, and they also have in, out parameters. With MySql you use one element to execute the Stored Proc, and another element to retrieve the resulting variables from the database! If you have Oracle, perhaps you can test this out and let me know if it works.

--This is how I created the Stored Proc on MySql
delimiter //
create procedure accountbal(in oacctnum int, out obalance int)
begin
select balance1 into obalance from account where acctnum=oacctnum;
end //

And then in Studio, execute the stored procedure from a Studio DB element:
Type: Update
SQL Query: call accountbal(1111, @dbvarBalance)

And then execute another Studio DB element to get the value from the database variable @dbvarBalance into Studio element data named 'acctBalance',
Type: 'Single'
SQL Query: select @dbvarBalance as acctBalance

Don't do this! The reason this works is that the "@variable" syntax is a MySQL-specific feature known as user-defined variables. What is unique and useful about these variables is that they retain their scope across the lifetime of a connected session, allowing you to reference them across different statements, as you did in your Call Studio example. The problem is that the db step in VXML Server uses connection pooling. This is important for efficient performance, but it has the side-effect that multiple calls may use the same db connection before it is ever closed. Since a MySQL user-defined variable retains its scope for the lifetime of a connection, this exposes you to some serious race conditions.
For example, let's say you have two calls executing the database interaction at about the same time. Call 1 executes the update then returns the connection to the pool. Before Call 1 has a change to execute the query step, Call 2 borrows the same connection from the pool and executes its update step. Now, if Call 1 retrieves the original connection and exectues it's query, it'll get the data for Call 2! Very bad. In fact you don't even need two concurrent calls hitting the exact same step to see issues, the update and select pieces may not even retrieve the same connection if the connection pool is being used by multiple calls.
You won't see this in testing because of the low call volume. You may not even catch it in production, but since it's such a serious risk - and a nightmare to debug if you ever do hit it - you should absolutely avoid it.
If you are using OUT parameters in MySQL, you'll need to use custom Java code similar to the Oracle example. Unlike Oracle however, MySQL actually lets you return resultsets directly from a stored procedure just like in MSSQL. Your stored proc then can be rewritten like so:
delimiter //create procedure accountbal(in oacctnum int)begin select balance1 from account where acctnum=oacctnum;end //
You can then call it like so:
call accountbal(1111)
You can call this via a single or multiple, depending on how many results are returned. Just think of it as being identical to calling "select balance1 from account where acctnum=oacctnum" directly. The same holds true for MSSQL procedures. The syntax for calling an MSSQL procedure that returns a resultset is like so:
EXEC yourProc 'param1', 'param2', 'param3'
The lesson here is there isn't a single rule as to how to use stored procedures within Call Studio, it depends entirely on how the stored proc is implemented. In most cases, this is something dictated by the customer, not something you have control over. Otherwise, you could simply use a direct query and avoid the hassle. Here's a rough flow for figuring out what to do:
Does the proc return results?If not, you should be able to call it via the "update" query type.If yes, does the proc return a resultset? (Oracle cannot return a resultset at all, MySQL and MSSQL can)If OUT parameters, you will need to write custom Java code.If it returns a resultset, you can use it the same way as if you were calling the query directly.

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Edward Umansky on 06-06-2013 01:22:50 PM
Janine Graves:
Edward Umansky:
Unfortunately, this cannot be done with the built-in Call Studio element. The problem is that Oracle stored procs simply don't work the way they do in SQL Server. In SQL Server, a stored proc can return a resultset - a tabular list of values - just like a select statement.

Oracle stored procedures simply don't allow that. Instead, Oracle stored procs return data by defining "output" parameters which you must register and pass into the stored proc itself. Once the procedure is executed, these output parameters hold the results returned. Since the built-in db element depends on resultsets for returned data, and has no facility to define and register outbput parameters, it can't be used to call Oracle stored procedures and retrieve results.

You would need to write a completely custom element to do this. A good example of the code you need can be found here: http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/
Hi Ed,

I can't test Oracle, but MySql stored procedures work with Studio Database element, and they also have in, out parameters. With MySql you use one element to execute the Stored Proc, and another element to retrieve the resulting variables from the database! If you have Oracle, perhaps you can test this out and let me know if it works.

--This is how I created the Stored Proc on MySql
delimiter //
create procedure accountbal(in oacctnum int, out obalance int)
begin
select balance1 into obalance from account where acctnum=oacctnum;
end //

And then in Studio, execute the stored procedure from a Studio DB element:
Type: Update
SQL Query: call accountbal(1111, @dbvarBalance)

And then execute another Studio DB element to get the value from the database variable @dbvarBalance into Studio element data named 'acctBalance',
Type: 'Single'
SQL Query: select @dbvarBalance as acctBalance

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Edward Umansky on 06-06-2013 01:28:44 PM
Janine Graves:
Edward Umansky:
Unfortunately, this cannot be done with the built-in Call Studio element. The problem is that Oracle stored procs simply don't work the way they do in SQL Server. In SQL Server, a stored proc can return a resultset - a tabular list of values - just like a select statement.

Oracle stored procedures simply don't allow that. Instead, Oracle stored procs return data by defining "output" parameters which you must register and pass into the stored proc itself. Once the procedure is executed, these output parameters hold the results returned. Since the built-in db element depends on resultsets for returned data, and has no facility to define and register outbput parameters, it can't be used to call Oracle stored procedures and retrieve results.

You would need to write a completely custom element to do this. A good example of the code you need can be found here: http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/
Hi Ed,
Can you also show an example of how to call a Stored Proc when using MS SQL? Is it done using Type: Single (or Multiple, depending on the number of rows returned?) or is it Type:Update?
And for the Sql Query, must you enter something like 'call procName(param1, param2)' or 'exec procName(param1, param2)' ?

Don't do this! The reason this works is that the "@variable" syntax is a MySQL-specific feature known as user-defined variables. What is unique and useful about these variables is that they retain their scope across the lifetime of a connected session, allowing you to reference them across different statements, as you did in your Call Studio example. The problem is that the db step in VXML Server uses connection pooling. This is important for efficient performance, but it has the side-effect that multiple calls may use the same db connection before it is ever closed. Since a MySQL user-defined variable retains its scope for the lifetime of a connection, this exposes you to some serious race conditions.
For example, let's say you have two calls executing the database interaction at about the same time. Call 1 executes the update then returns the connection to the pool. Before Call 1 has a change to execute the query step, Call 2 borrows the same connection from the pool and executes its update step. Now, if Call 1 retrieves the original connection and exectues it's query, it'll get the data for Call 2! Very bad. In fact you don't even need two concurrent calls hitting the exact same step to see issues, the update and select pieces may not even retrieve the same connection if the connection pool is being used by multiple calls.
You won't see this in testing because of the low call volume. You may not even catch it in production, but since it's such a serious risk - and a nightmare to debug if you ever do hit it - you should absolutely avoid it.
If you are using OUT parameters in MySQL, you'll need to use custom Java code similar to the Oracle example. Unlike Oracle however, MySQL actually lets you return resultsets directly from a stored procedure just like in MSSQL. Your stored proc then can be rewritten like so:
delimiter //create procedure accountbal(in oacctnum int)begin select balance1 from account where acctnum=oacctnum;end //
You can then call it like so:
call accountbal(1111)
You can call this via a single or multiple, depending on how many results are returned. Just think of it as being identical to calling "select balance1 from account where acctnum=oacctnum" directly. The same holds true for MSSQL procedures. The syntax for calling an MSSQL procedure that returns a resultset is like so:
EXEC yourProc 'param1', 'param2', 'param3'
The lesson here is there isn't a single rule as to how to use stored procedures within Call Studio, it depends entirely on how the stored proc is implemented. In most cases, this is something dictated by the customer, not something you have control over. Otherwise, you could simply use a direct query and avoid the hassle. Here's a rough flow for figuring out what to do:
Does the proc return results?If not, you should be able to call it via the "update" query type.If yes, does the proc return a resultset? (Oracle cannot return a resultset at all, MySQL and MSSQL can)If OUT parameters, you will need to write custom Java code.If it returns a resultset, you can use it the same way as if you were calling the query directly.

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Edward Umansky on 06-06-2013 01:29:14 PM
Janine Graves:
Edward Umansky:
Unfortunately, this cannot be done with the built-in Call Studio element. The problem is that Oracle stored procs simply don't work the way they do in SQL Server. In SQL Server, a stored proc can return a resultset - a tabular list of values - just like a select statement.

Oracle stored procedures simply don't allow that. Instead, Oracle stored procs return data by defining "output" parameters which you must register and pass into the stored proc itself. Once the procedure is executed, these output parameters hold the results returned. Since the built-in db element depends on resultsets for returned data, and has no facility to define and register outbput parameters, it can't be used to call Oracle stored procedures and retrieve results.

You would need to write a completely custom element to do this. A good example of the code you need can be found here: http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/
Hi Ed,
Can you also show an example of how to call a Stored Proc when using MS SQL? Is it done using Type: Single (or Multiple, depending on the number of rows returned?) or is it Type:Update?
And for the Sql Query, must you enter something like 'call procName(param1, param2)' or 'exec procName(param1, param2)' ?

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Edward Umansky on 06-06-2013 01:29:36 PM
Janine Graves:
Edward Umansky:
Unfortunately, this cannot be done with the built-in Call Studio element. The problem is that Oracle stored procs simply don't work the way they do in SQL Server. In SQL Server, a stored proc can return a resultset - a tabular list of values - just like a select statement.

Oracle stored procedures simply don't allow that. Instead, Oracle stored procs return data by defining "output" parameters which you must register and pass into the stored proc itself. Once the procedure is executed, these output parameters hold the results returned. Since the built-in db element depends on resultsets for returned data, and has no facility to define and register outbput parameters, it can't be used to call Oracle stored procedures and retrieve results.

You would need to write a completely custom element to do this. A good example of the code you need can be found here: http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/
Hi Ed,
Can you also show an example of how to call a Stored Proc when using MS SQL? Is it done using Type: Single (or Multiple, depending on the number of rows returned?) or is it Type:Update?
And for the Sql Query, must you enter something like 'call procName(param1, param2)' or 'exec procName(param1, param2)' ?

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Edward Umansky on 06-06-2013 01:30:44 PM
Janine Graves:
Edward Umansky:
Unfortunately, this cannot be done with the built-in Call Studio element. The problem is that Oracle stored procs simply don't work the way they do in SQL Server. In SQL Server, a stored proc can return a resultset - a tabular list of values - just like a select statement.

Oracle stored procedures simply don't allow that. Instead, Oracle stored procs return data by defining "output" parameters which you must register and pass into the stored proc itself. Once the procedure is executed, these output parameters hold the results returned. Since the built-in db element depends on resultsets for returned data, and has no facility to define and register outbput parameters, it can't be used to call Oracle stored procedures and retrieve results.

You would need to write a completely custom element to do this. A good example of the code you need can be found here: http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/
Hi Ed,

I can't test Oracle, but MySql stored procedures work with Studio Database element, and they also have in, out parameters. With MySql you use one element to execute the Stored Proc, and another element to retrieve the resulting variables from the database! If you have Oracle, perhaps you can test this out and let me know if it works.

--This is how I created the Stored Proc on MySql
delimiter //
create procedure accountbal(in oacctnum int, out obalance int)
begin
select balance1 into obalance from account where acctnum=oacctnum;
end //

And then in Studio, execute the stored procedure from a Studio DB element:
Type: Update
SQL Query: call accountbal(1111, @dbvarBalance)

And then execute another Studio DB element to get the value from the database variable @dbvarBalance into Studio element data named 'acctBalance',
Type: 'Single'
SQL Query: select @dbvarBalance as acctBalance

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Edward Umansky on 06-06-2013 01:33:44 PM
Janine Graves:
Edward Umansky:
Unfortunately, this cannot be done with the built-in Call Studio element. The problem is that Oracle stored procs simply don't work the way they do in SQL Server. In SQL Server, a stored proc can return a resultset - a tabular list of values - just like a select statement.

Oracle stored procedures simply don't allow that. Instead, Oracle stored procs return data by defining "output" parameters which you must register and pass into the stored proc itself. Once the procedure is executed, these output parameters hold the results returned. Since the built-in db element depends on resultsets for returned data, and has no facility to define and register outbput parameters, it can't be used to call Oracle stored procedures and retrieve results.

You would need to write a completely custom element to do this. A good example of the code you need can be found here: http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/
Hi Ed,

I can't test Oracle, but MySql stored procedures work with Studio Database element, and they also have in, out parameters. With MySql you use one element to execute the Stored Proc, and another element to retrieve the resulting variables from the database! If you have Oracle, perhaps you can test this out and let me know if it works.

--This is how I created the Stored Proc on MySql
delimiter //
create procedure accountbal(in oacctnum int, out obalance int)
begin
select balance1 into obalance from account where acctnum=oacctnum;
end //

And then in Studio, execute the stored procedure from a Studio DB element:
Type: Update
SQL Query: call accountbal(1111, @dbvarBalance)

And then execute another Studio DB element to get the value from the database variable @dbvarBalance into Studio element data named 'acctBalance',
Type: 'Single'
SQL Query: select @dbvarBalance as acctBalance

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Edward Umansky on 06-06-2013 01:37:48 PM
Janine Graves:

--This is how I created the Stored Proc on MySql
delimiter //
create procedure accountbal(in oacctnum int, out obalance int)
begin
select balance1 into obalance from account where acctnum=oacctnum;
end //

And then in Studio, execute the stored procedure from a Studio DB element:
Type: Update
SQL Query: call accountbal(1111, @dbvarBalance)

And then execute another Studio DB element to get the value from the database variable @dbvarBalance into Studio element data named 'acctBalance',
Type: 'Single'
SQL Query: select @dbvarBalance as acctBalance

Don't do this! The reason this works is that the "@variable" syntax is a MySQL-specific feature known as user-defined variables. What is unique and useful about these variables is that they retain their scope across the lifetime of a connected session, allowing you to reference them across different statements, as you did in your Call Studio example. The problem is that the db step in VXML Server uses connection pooling. This is important for efficient performance, but it has the side-effect that multiple calls may use the same db connection before it is ever closed. Since a MySQL user-defined variable retains its scope for the lifetime of a connection, this exposes you to some serious race conditions.

For example, let's say you have two calls executing the database interaction at about the same time. Call 1 executes the update then returns the connection to the pool. Before Call 1 has a change to execute the query step, Call 2 borrows the same connection from the pool and executes its update step. Now, if Call 1 retrieves the original connection and exectues it's query, it'll get the data for Call 2! Very bad. In fact you don't even need two concurrent calls hitting the exact same step to see issues, the update and select pieces may not even retrieve the same connection if the connection pool is being used by multiple calls.

You won't see this in testing because of the low call volume. You may not even catch it in production, but since it's such a serious risk - and a nightmare to debug if you ever do hit it - you should absolutely avoid it.

If you are using OUT parameters in MySQL, you'll need to use custom Java code similar to the Oracle example. Unlike Oracle however, MySQL actually lets you return resultsets directly from a stored procedure just like in MSSQL. Your stored proc then can be rewritten like so:

delimiter //
create procedure accountbal(in oacctnum int)
begin select balance1 from account where acctnum=oacctnum;
end //

Then you can just call it like so:

call accountbal(1111)

You can call this via a single or multiple query, depending on how many results are returned. Just think of it as being identical to calling "select balance1 from account where acctnum=oacctnum" directly. The same holds true for MSSQL procedures. The syntax for calling an MSSQL procedure that returns a resultset is like so:

EXEC yourProc 'param1', 'param2', 'param3'

The lesson here is there isn't a single rule as to how to use stored procedures within Call Studio, it depends entirely on how the stored proc is implemented. In most cases, this is something dictated by the customer, not something you have control over. Otherwise, you could simply use a direct query and avoid the hassle. Here's a rough flow for figuring out what to do:

Does the proc return results?
If not, you should be able to call it via the "update" query type.
If yes, does the proc return a resultset? (Oracle cannot return a resultset at all, MySQL and MSSQL can)
If OUT parameters, you will need to write custom Java code.
If it returns a resultset, you can use it the same way as if you were calling the query directly.

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Edward Umansky on 06-06-2013 01:40:17 PM
Janine Graves:

--This is how I created the Stored Proc on MySql
delimiter //
create procedure accountbal(in oacctnum int, out obalance int)
begin
select balance1 into obalance from account where acctnum=oacctnum;
end //

And then in Studio, execute the stored procedure from a Studio DB element:
Type: Update
SQL Query: call accountbal(1111, @dbvarBalance)

And then execute another Studio DB element to get the value from the database variable @dbvarBalance into Studio element data named 'acctBalance',
Type: 'Single'
SQL Query: select @dbvarBalance as acctBalance

Don't do this! The reason this works is that the "@variable" syntax is a MySQL-specific feature known as user-defined variables. What is unique and useful about these variables is that they retain their scope across the lifetime of a connected session, allowing you to reference them across different statements, as you did in your Call Studio example. The problem is that the db step in VXML Server uses connection pooling. This is important for efficient performance, but it has the side-effect that multiple calls may use the same db connection before it is ever closed. Since a MySQL user-defined variable retains its scope for the lifetime of a connection, this exposes you to some serious race conditions.

For example, let's say you have two calls executing the database interaction at about the same time. Call 1 executes the update then returns the connection to the pool. Before Call 1 has a change to execute the query step, Call 2 borrows the same connection from the pool and executes its update step. Now, if Call 1 retrieves the original connection and exectues it's query, it'll get the data for Call 2! Very bad. In fact you don't even need two concurrent calls hitting the exact same step to see issues, the update and select pieces may not even retrieve the same connection if the connection pool is being used by multiple calls.

You won't see this in testing because of the low call volume. You may not even catch it in production, but since it's such a serious risk - and a nightmare to debug if you ever do hit it - you should absolutely avoid it.

If you are using OUT parameters in MySQL, you'll need to use custom Java code similar to the Oracle example. Unlike Oracle however, MySQL actually lets you return resultsets directly from a stored procedure just like in MSSQL. Your stored proc then can be rewritten like so:

delimiter //
create procedure accountbal(in oacctnum int)
begin select balance1 from account where acctnum=oacctnum;
end //

Then you can just call it like so:

call accountbal(1111)

You can call this via a single or multiple query, depending on how many results are returned. Just think of it as being identical to calling "select balance1 from account where acctnum=oacctnum" directly. The same holds true for MSSQL procedures. The syntax for calling an MSSQL procedure that returns a resultset is like so:

EXEC yourProc 'param1', 'param2', 'param3'

The lesson here is there isn't a single rule as to how to use stored procedures within Call Studio, it depends entirely on how the stored proc is implemented. In most cases, this is something dictated by the customer, not something you have control over. Otherwise, you could simply use a direct query and avoid the hassle. Here's a rough flow for figuring out what to do:

Does the proc return results?
If not, you should be able to call it via the "update" query type.
If yes, does the proc return a resultset? (Oracle cannot return a resultset at all, MySQL and MSSQL can)
If OUT parameters, you will need to write custom Java code.
If it returns a resultset, you can use it the same way as if you were calling the query directly.

Subject: RE: New Message from Edward Umansky in Customer Voice Portal (CVP) - CVP -
Replied by: Edward Umansky on 06-06-2013 01:51:48 PM
Agreed but Janine is teaching the Cisco CVP class so she needs to be able to give her students guidance on the built-in elements of Call Studio.

Subject: Re: New Message from Hemal Mehta in Customer Voice Portal (CVP) - CVP - All
Replied by: Janine Graves on 06-06-2013 01:54:15 PM
Hemal,
I teach the CVP Studio programming class. So I need to teach students
how to use the Studio DB element. Not everyone knows Java or has a Java
programmer on the payroll. I'm hoping that Cisco improves the existing
Studio DB element (and adds more Studio elements) so that it becomes
more useful out of the box - like many other IVR vendors have done for
years.
Janine

Subject: RE: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - A
Replied by: Hemal Mehta on 06-06-2013 02:06:44 PM
That makes sense. I am sure Cisco will enhance, however there will be always exceptions and out of box scenarios where you will have to write something on your own.   For example, I have a scenario where web service is written in .NET and I need to call from studio. However due to the way it is generated, I cannot just use even the code I generate through Axis directly I need to manually tweak to make it work. The question of using in built element is out of question.
Then I have another case where I switch between different SQL versions on the fly, if I use inbuilt element it is not possible.
If you are good in java, I would say write your own code. If not, you got to use what you have.
Hemal

From: Cisco Developer Community Forums [mailto:cdicuser@developer.cisco.com]
Sent: Thursday, June 06, 2013 1:54 PM
To: cdicuser@developer.cisco.com
Subject: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - All Versions: Re: New Message from Hemal Mehta in Customer Voice Portal (CVP) - CVP - All

Janine Graves has created a new message in the forum "CVP - All Versions": -------------------------------------------------------------- Hemal,
I teach the CVP Studio programming class. So I need to teach students
how to use the Studio DB element. Not everyone knows Java or has a Java
programmer on the payroll. I'm hoping that Cisco improves the existing
Studio DB element (and adds more Studio elements) so that it becomes
more useful out of the box - like many other IVR vendors have done for
years.
Janine
--
To respond to this post, please click the following link: http://developer.cisco.com/web/cvp/forums/-/message_boards/view_message/15944378 or simply reply to this email.

Subject: RE: Re: New Message from Hemal Mehta in Customer Voice Portal (CVP) - CVP -
Replied by: Gerard O'Rourke on 07-06-2013 03:51:37 AM
Janine Graves:
Hemal,
I teach the CVP Studio programming class. So I need to teach students
how to use the Studio DB element. Not everyone knows Java or has a Java
programmer on the payroll. I'm hoping that Cisco improves the existing
Studio DB element (and adds more Studio elements) so that it becomes
more useful out of the box - like many other IVR vendors have done for
years.
Janine

Janine,

I agree, every one that uses a database element would or should have at least an option to set connection timeout and a proper way to handle an error event, such as a failed connection / authentication issue, so that they could then handle this situation within the cvp studio application.

The current DB element is basic to the point that it does not have the capablity in my view for a production quality app, where error handling is critical.
Hopefully Cisco will improve this element, and it would seem straight forward to do so.

Gerry

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Coty Condry on 06-08-2013 11:26:14 AM
Janine Graves:
Edward Umansky:
Unfortunately, this cannot be done with the built-in Call Studio element. The problem is that Oracle stored procs simply don't work the way they do in SQL Server. In SQL Server, a stored proc can return a resultset - a tabular list of values - just like a select statement.

Oracle stored procedures simply don't allow that. Instead, Oracle stored procs return data by defining "output" parameters which you must register and pass into the stored proc itself. Once the procedure is executed, these output parameters hold the results returned. Since the built-in db element depends on resultsets for returned data, and has no facility to define and register outbput parameters, it can't be used to call Oracle stored procedures and retrieve results.

You would need to write a completely custom element to do this. A good example of the code you need can be found here: http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/
Hi Ed,
Can you also show an example of how to call a Stored Proc when using MS SQL? Is it done using Type: Single (or Multiple, depending on the number of rows returned?) or is it Type:Update?
And for the Sql Query, must you enter something like 'call procName(param1, param2)' or 'exec procName(param1, param2)' ?
1import java.sql.*;
2
3String dburl = "jdbc:sqlserver://mssql.test.com:1433;user=myUsername;password=password123;database=myDB";
4Connection con = DriverManager.getConnection(dburl);
5CallableStatement cs = con.prepareCall("EXEC GetCall ?,?");
6
7int ani = 2145701400;
8String filter = "foo";
9
10cs.setInt("ani",ani);
11cs.setString("filter",filter);
12
13ResultSet rs = cs.executeQuery();
14//there is also cs.executeUpdate(), which does not return a result set
15
16if(rs.next()){
17 String customer = rs.getString("customer");
18 java.sql.Date modified = rs.getDate("modified");
19}
20
21if(rs != null) rs.close;
22if(cs != null) cs.close;
23if(con != null) con.close;

Subject: RE: Call Studio Database Element and Stored Procedures
Replied by: Coty Condry on 06-08-2013 11:57:01 AM
The code I just posted (can't figure out how to edit my posts...) is for an MSSQL stored procedure with 2 named input parameters. The CallableStatement also has setters (setString, setInt, etc.) that accept integers for the input parameter index, instead of strings for the name of the parameter. If you use parameter index, note that it starts at 1 instead of 0. For example,
1[font=monospace]CallableStatement cs = con.prepareCall("EXEC GetCall ?,?");[/font]
2
3[font=monospace]cs.setInt(1,[/font]2145701400[font=monospace]);[/font]
4[font=monospace]cs.setString(2,"foo");[/font]
The www.mykong.com link that Edward posted is a great resource for Java and SQL.