CVP MSSQL migration- The statement did not return a result set.

cdnadmin · ‎01-25-2014

This document was generated from CDN thread

Created by: CLAUDIO RIVAS on 17-12-2012 04:00:38 PM
Hi, currently i have an issue running stored procedures from MSSQL Server, i’m trying to migrate the standalone outbound script to use MSSQL, but return the following error - The statement did not return a result set. On the MSSQL Management Studio seems to run fine this stored procedure.
I attach the code and logs, i'll appreciate your help.

Regards,
Claudio.

USE
GO
/****** Object: StoredProcedure . Script Date: 12/16/2012 18:31:40 ******/
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
--EXEC 3
ALTERPROCEDURE .
@max_attempts
--WITH EXECUTE AS CALLER
AS
BEGIN

declare @dnis_value varchar(20)
SET @dnis_value ='xxxx'
declare @id_value integer
SET @id_value = 0
declare @rna_value integer
SET @rna_value = 0
declare @cli_value varchar(20)
SET @cli_value = ''
declare @uui_value varchar(256)
SET @uui_value = ''

--start transaction;
BEGIN transaction
select TOP 1 @dnis_value=dnis,
@id_value =id,
@rna_value = rna,
@cli_value = cli,
@uui_value = uui
--into @dnis_value, @id_value, @rna_value, @cli_value, @uui_value
from outdials
where
(current_status='OUTDIAL_PENDING')AND
(attempts < @max_attempts) AND
(next_retry < getdate())
order by next_retry ASC

if @dnis_value !='xxxx'
BEGIN
update outdials set current_status='OUTDIAL_IN_PROGRESS', attempts=attempts + 1
where id = @id_value

END

COMMIT

select
@dnis_value as dnis,
@id_value as id,
@rna_value as rna,
@cli_value as cli,
@uui_value as uui

END

11.11.11.11.1355777435406.5049.outbound,12/17/2012 14:50:35.640, The error was: A built-in element encountered an exception of type com.audium.server.AudiumException. The statement did not return a result set. The root cause was: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
com.audium.server.AudiumException: A built-in element encountered an exception of type com.audium.server.AudiumException.
at com.audium.server.voiceElement.ActionElementBase.service(ActionElementBase.java:413)
at com.audium.server.controller.Controller.goToAction(Controller.java:2911)
at com.audium.server.controller.Controller.goToElement(Controller.java:2643)
at com.audium.server.controller.Controller.continueCall(Controller.java:2468)
at com.audium.server.controller.Controller.doPost(Controller.java:696)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:879)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Thread.java:662)
Caused by: com.audium.server.AudiumException: The statement did not return a result set.
at com.audium.server.action.database.DatabaseAction.doAction(DatabaseAction.java:239)
at com.audium.server.voiceElement.ActionElementBase.service(ActionElementBase.java:387)
... 20 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:408)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at com.audium.server.action.database.DatabaseAction.doAction(DatabaseAction.java:199)



Subject: RE: New Message from CLAUDIO RIVAS in Customer Voice Portal (CVP) - CVP - A
Replied by: Asher Schweigart on 17-12-2012 04:23:00 PM
I had the same issue; I do not know what Cisco says about it, but I could not get Call Studio to return a result from a stored procedure either. The stored procedure is run; I had no problem using it to update a row, the issue was then trying to return something.

I ended up just restructuring the way I wanted to do things, and only used stored procedures for inserts.
The other option would be to write your own DB element in Java. I plan on doing that to allow for stored procedures at some point, but haven’t yet as I didn’t have a big enough need for it.

Subject: RE: New Message from Asher Schweigart in Customer Voice Portal (CVP) - CVP
Replied by: Asher Schweigart on 17-12-2012 04:33:00 PM
I only replied once, my sent folder confirms that. Is something broken, or was that a glitch?

Subject: RE: CVP MSSQL migration- The statement did not return a result set.
Replied by: CLAUDIO RIVAS on 17-12-2012 04:33:11 PM
I have the exact same issue, the update part of the procedure seems to run because modify the value of the corrects collum and row. but seems to be something about the MSSQL driver.

Subject: RE: New Message from CLAUDIO RIVAS in Customer Voice Portal (CVP) - CVP - A
Replied by: Asher Schweigart on 17-12-2012 04:23:02 PM
I had the same issue; I do not know what Cisco says about it, but I could not get Call Studio to return a result from a stored procedure either. The stored procedure is run; I had no problem using it to update a row, the issue was then trying to return something.

I ended up just restructuring the way I wanted to do things, and only used stored procedures for inserts.
The other option would be to write your own DB element in Java. I plan on doing that to allow for stored procedures at some point, but haven’t yet as I didn’t have a big enough need for it.

Subject: RE: New Message from CLAUDIO RIVAS in Customer Voice Portal (CVP) - CVP - A
Replied by: Hemal Mehta on 17-12-2012 05:53:39 PM
What MSSQL library do you have ? I would suggest not to use the Cisco DB element.    Just call it from within java code.
Hemal
________________________________
From: Cisco Developer Community Forums [cdicuser@developer.cisco.com]
Sent: Monday, December 17, 2012 4:00 PM
To: cdicuser@developer.cisco.com
Subject: New Message from CLAUDIO RIVAS in Customer Voice Portal (CVP) - CVP - All Versions: CVP MSSQL migration- The statement did not return a result set.

CLAUDIO RIVAS has created a new message in the forum "CVP - All Versions": -------------------------------------------------------------- Hi, currently i have an issue running stored procedures from MSSQL Server, i’m trying to migrate the standalone outbound script to use MSSQL, but return the following error - The statement did not return a result set.








I attach the code and logs, help needed.

Regards,
Claudio.

USE
GO
/****** Object: StoredProcedure .    Script Date: 12/16/2012 18:31:40 ******/
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
--EXEC 3
ALTERPROCEDURE .
      @max_attempts
--WITH EXECUTE AS CALLER
AS
BEGIN

      declare @dnis_value     varchar(20)
      SET @dnis_value ='xxxx'
    declare @id_value   integer
    SET @id_value = 0
    declare @rna_value integer
    SET @rna_value = 0
    declare @cli_value varchar(20)
    SET @cli_value = ''
    declare @uui_value varchar(256)
    SET @uui_value = ''

    --start transaction;
    BEGIN transaction
    select TOP 1 @dnis_value=dnis,
    @id_value =id,
    @rna_value = rna,
    @cli_value = cli,
    @uui_value = uui
    --into @dnis_value, @id_value, @rna_value, @cli_value, @uui_value
      from outdials
      where
            (current_status='OUTDIAL_PENDING')AND
             (attempts < @max_attempts) AND
             (next_retry < getdate())
      order by next_retry ASC

    if @dnis_value !='xxxx'
    BEGIN
         update outdials set current_status='OUTDIAL_IN_PROGRESS', attempts=attempts + 1
         where id = @id_value

    END

    COMMIT

    select
      @dnis_value as dnis,
      @id_value   as id,
      @rna_value as rna,
      @cli_value as cli,
      @uui_value as uui

END

11.11.11.11.1355777435406.5049.outbound,12/17/2012 14:50:35.640, The error was: A built-in element encountered an exception of type com.audium.server.AudiumException. The statement did not return a result set. The root cause was: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
com.audium.server.AudiumException: A built-in element encountered an exception of type com.audium.server.AudiumException.
at com.audium.server.voiceElement.ActionElementBase.service(ActionElementBase.java:413)
at com.audium.server.controller.Controller.goToAction(Controller.java:2911)
at com.audium.server.controller.Controller.goToElement(Controller.java:2643)
at com.audium.server.controller.Controller.continueCall(Controller.java:2468)
at com.audium.server.controller.Controller.doPost(Controller.java:696)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:879)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Thread.java:662)
Caused by: com.audium.server.AudiumException: The statement did not return a result set.
at com.audium.server.action.database.DatabaseAction.doAction(DatabaseAction.java:239)
at com.audium.server.voiceElement.ActionElementBase.service(ActionElementBase.java:387)
... 20 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:408)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at com.audium.server.action.database.DatabaseAction.doAction(DatabaseAction.java:199)

--
To respond to this post, please click the following link: http://developer.cisco.com/web/cvp/community/-/message_boards/view_message/9488329 or simply reply to this email.

Subject: RE: CVP MSSQL migration- The statement did not return a result set.
Replied by: Janine Graves on 17-12-2012 07:36:25 PM
Hi Claudio,
I don't have MSSQL to test this out, but I've been able to execute stored procs using MySql. While there might be a problem with the MSSQL driver, we can first try to rule out some studio problems before you resort to writing your own DB element.
1. In the DB element that you're using to call the stored proc, what do you have in the 'Type' setting?
2. And what do you have in the Sql Query setting?

Subject: RE: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - A
Replied by: Asher Schweigart on 17-12-2012 07:42:39 PM
Janine,

Were you able to execute stored procedures in MySQL that returned a data set?

Subject: RE: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - A
Replied by: Janine Graves on 17-12-2012 08:06:07 PM
I can execute stored procs and stored functions that return data.
But, the way the data is retrieved might be different that with MSSQL.
I have to use one DB element to exec the stored proc and then another DB element to retrieve the results.

The code below is also in the attached file (in case it looks like crap when I post this). I copy the entire contents of the attached file right into my MySql command line client.

Things that start with dash dash '--' are comments, which explain how to call these from Studio DB element.

--paste all this into MySql to create the procedures and functions

-- ------------------------------------
-- ------------------------------------
-- Procedure to schedule the pickup

-- ------------------------------------
-- -----------------------------------
--Procedure with one in and one out parameter

delimiter ;
drop procedure if exists accountbal;
delimiter //
create procedure accountbal(in oacctnum int, out obalance int)
begin
select balance1 into obalance from account where acctnum=oacctnum;
end //

delimiter ;

-- from Studio, execute this as an 'Update' to call the procedure
call accountbal(1111, @balance);
-- from Studio, execute this as 'Single' to get balance as element data
select @balance as balance;

-- -----------------------------------
-- -----------------------------------
--Procedure with 2 in and 2 out parameters
-- Get back NULL if acctnum doesn't exist
delimiter ;
drop procedure if exists withdraw;
delimiter //
create procedure withdraw(in oacctnum int, in oamount int, out onewbalance int(12), out osuccess varchar(6))
begin
declare balance int(12);
select 'false' into osuccess;
select balance1 into balance from account where acctnum=oacctnum;
select balance into onewbalance;
if balance > oamount then
         update account set balance1 = balance1 - oamount where acctnum=oacctnum;
         select balance1 into onewbalance from account where acctnum=oacctnum;
         select 'true' into osuccess;
end if;
end //

delimiter ;
--
-- In studio, execute this as an 'Update' by pasting this into the sql query:
call withdraw(1111, 1000, @newbalance,@success);
-- In studio, execute this as 'Single' by pasting this into the sql query:
select @newbalance newbalance, @success success;

-- ---------------------------------------
-- ---------------------------------------
-- Stored function
-- This function takes an account number and a transfer amount and moves that amount from balance1 to balance2
-- Returns 'true' if successful, else returns 'false'
delimiter ;
drop function if exists transfer;
delimiter //
create function transfersuccess(oacctnum int, oamount int) returns varchar(6) deterministic
begin
declare bal1 int;
declare bal2 int;
declare success varchar(6);
set success = 'false';
select balance1 into bal1 from account where acctnum=oacctnum;

select balance2 into bal2 from account where acctnum=oacctnum;
if bal2 is null then
    update account set balance2=0 where acctnum=oacctnum;
end if;

if bal1 >= oamount then
         update account set balance1 = balance1 - oamount, balance2=balance2+oamount where acctnum=oacctnum;
         set success = 'true';
end if;
return success;
end
//
delimiter ;

-- In studio, execute this as 'Single' to call the function, and to get
-- back element data named 'success' with value true or false.
select transfersuccess(2222,1) as success;
-- then execute this as a 'Single' to get the new account information
select * from account where acctnum=2222;

Subject: RE: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - A
Replied by: Janine Graves on 17-12-2012 08:08:48 PM
Oops, now I'll post the attachment on the forum.

Subject: RE: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - A
Replied by: Hemal Mehta on 18-12-2012 07:41:39 AM
Have you executed stored procs on MS SQL without errors ?

From: Cisco Developer Community Forums [mailto:cdicuser@developer.cisco.com]
Sent: Monday, December 17, 2012 8:09 PM
To: cdicuser@developer.cisco.com
Subject: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - All Versions: RE: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - A

Janine Graves has created a new message in the forum "CVP - All Versions": -------------------------------------------------------------- Oops, now I'll post the attachment on the forum.
--
To respond to this post, please click the following link: http://developer.cisco.com/web/cvp/forums/-/message_boards/view_message/9495749 or simply reply to this email.

Subject: Re: New Message from Hemal Mehta in Customer Voice Portal (CVP) - CVP - All
Replied by: Janine Graves on 18-12-2012 07:55:39 AM
I don't have MSSQL, so no, I haven't.

Subject: RE: CVP MSSQL migration- The statement did not return a result set.
Replied by: CLAUDIO RIVAS on 18-12-2012 03:20:12 PM
Hi Janine,
Seems there is something on the MSSQL stored procedures that CVP or MSSQL JDBC driver understand on a diferent way, we have changed the order on the stored procedure to have a response, we had an update first and a select after that, so thats why i dind't had an answer form MSSQL, fortunately changing the order of execution worked for us, so first execute a select this return something (for this time defaults), seems the result that MSSQL return to the query and the MSSQL JDBC driver is the first execution of the store.
The way i execute is this:
set nocount on;
EXEC . 3;
Hope this helps, if is helpful, please rate.
Regards,

Subject: RE: CVP MSSQL migration- The statement did not return a result set.
Replied by: Hemal Mehta on 18-12-2012 03:51:58 PM
If something changes with the way DB element works in future versions, you will be tied to it and will need to make changes again in your code. If you were to use JDBC , hibernate you will have very standard implementation that is more dependent on core java than how Cisco were to implement their underlying code.
Hemal

Subject: Re: New Message from Hemal Mehta in Customer Voice Portal (CVP) - CVP - All
Replied by: Janine Graves on 18-12-2012 03:56:39 PM
Hi Hemal, Can you post a sample of the source code for a simple custom element that execs a MSSQL stored proc? Thanks, Janine -- Janine Graves

Subject: RE: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - A
Replied by: Hemal Mehta on 18-12-2012 06:34:57 PM
Janine,
      I avoid custom elements unless really necessary. I focus my attention on having robust and well defined java code. Just use action class. Also all the variable etc are defined in a property file which makes it very configurable and I can change it on the fly without recompiling code.
I am on the road, but can post the sample code in a day or two.
Hemal
________________________________
From: Cisco Developer Community Forums [cdicuser@developer.cisco.com]
Sent: Tuesday, December 18, 2012 3:56 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": -------------------------------------------------------------- Hi Hemal, Can you post a sample of the source code for a simple custom element that execs a MSSQL stored proc? Thanks, Janine -- Janine Graves
--
To respond to this post, please click the following link: http://developer.cisco.com/web/cvp/forums/-/message_boards/view_message/9533839 or simply reply to this email.

Subject: RE: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - A
Replied by: Hemal Mehta on 18-12-2012 06:34:56 PM
Janine,
      I avoid custom elements unless really necessary. I focus my attention on having robust and well defined java code. Just use action class. Also all the variable etc are defined in a property file which makes it very configurable and I can change it on the fly without recompiling code.
I am on the road, but can post the sample code in a day or two.
Hemal
________________________________
From: Cisco Developer Community Forums [cdicuser@developer.cisco.com]
Sent: Tuesday, December 18, 2012 3:56 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": -------------------------------------------------------------- Hi Hemal, Can you post a sample of the source code for a simple custom element that execs a MSSQL stored proc? Thanks, Janine -- Janine Graves
--
To respond to this post, please click the following link: http://developer.cisco.com/web/cvp/forums/-/message_boards/view_message/9533839 or simply reply to this email.