on 01-24-2014 12:09 PM
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
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
Hi Ed,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,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/
Janine Graves:Hi Ed,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/
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 PMJanine 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 PMJanine 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 PMJanine Graves:Hi Ed,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/
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 PMJanine Graves:Hi Ed,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/
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 PMJanine Graves:Hi Ed,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/
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 PMJanine Graves:Hi Ed,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/
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 PMJanine Graves:Hi Ed,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/
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 PMJanine Graves:Hi Ed,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/
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 PMJanine Graves:Hi Ed,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/
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 PMJanine 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 PMJanine 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 AMJanine 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 AMJanine Graves:Hi Ed,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/
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]The www.mykong.com link that Edward posted is a great resource for Java and SQL.
2
3[font=monospace]cs.setInt(1,[/font]2145701400[font=monospace]);[/font]
4[font=monospace]cs.setString(2,"foo");[/font]
Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: