UCCX Call to a MS SQL Stored Procedure in DB Read or Java?

[joshuamarsh]

Hello,

I'm running UCCX Premium 7.x.  I am trying to call a stored procedure from a DB Read step in a CCX script.  The stored procedure takes a few variables and inserts a row into a table.  From the SQL Query Analyzer, I can run a simple, "EXECUTE myStoredProcedure 'variable1' , 'variable2'."  So I assumed that I could do the same in a DB Read step, "EXECUTE myStoredProcedure $variable1 , $variable2."  However, this does not work.  Although it will correctly update the table when hitting the test button in the editor, it returns a "Remote Error: java.lang.NullPointerException" error which causes the script to error out when actually running it.  This doesn't appear to be a value I can select in an On Exception step either to keep moving forward in the script.  Is there something wrong in my syntax, or another way to pull this off?

If not, do any of you Java wizards out there have a snippet from a Do Step where you do an ODBC connection and run a bit of SQL?

Thanks much!

Joshua

[joshuamarsh]

I had searched before, but didn't find the answer.  After I posted, I finally found it in another post: https://supportforums.cisco.com/thread/268641?tstart=0. The trick was to check the return status, even though I didn't care what it was (just to have a value), and as Randy points out in answer to the original post, the carriage returns, or lack thereof, matter.

The following is executed in a DB Read step.

DECLARE @return_status int
EXECUTE @return_status = myStoredProcedure $variable1 , $variable2; SELECT 'Return_Status' = @return_status

Sorry to repost something that had already been solved, but this was a tough one to find.

I'd still like to see the Java solution to this one, if anyone cares to share.

Thanks,

Joshua

[mwadam]

Joshua,

    You seem to have this figured out so I will run my issue by you.

    I have a similar issue where I have to run a stored procedure to retrieve the data.  I need to be able to use a variable such as $CustClaimNumber which is retrieved from the caller via the CRS script and pass it to the stored procedure in order to get the Claim Status.  When I run the script using the SQL syntax below I hit the DB Error step.  If I hard code the claim ID instead of using a variable the store procedure works.  Using my query against your solution I can see that you pass (my variable) of Claim Status, but what I need to pass is the Claim ID as collected in the script as $CustClaimNumber.

    We already have a view created which allows us to store the retrieved result and that works in the DB Get step, I just need to figure out how to change the claim id to a variable that I can pass to the procedure.

    Any suggestions?

    Thanks!!!

DECLARE @Claim_Status nvarchar(5);
EXEC IVR_GetStatusByClaimID_OUTPUT
  @ClaimID = $CustClaimNumber ;
  @Status = @Claim_Status OUTPUT;
SELECT @Claim_Status as N'Claim_Status'

[mwadam · Accepted solution]

So one of our DB gurus figured it out.  Basically the string variable statement needed to have a comma following it instead of a semi-colon.

Here is what we finally wound up with.

DECLARE @Claim_Status nvarchar(5);
EXEC IVR_GetStatusByClaimID_OUTPUT
  @ClaimID = $CustClaimNumber ,
  @Status = @Claim_Status OUTPUT;
SELECT @Claim_Status as N'Claim_Status'

[Guillermo_87]

Hello @mwadam

Could you help me? The same procedure, can I use for Oracle DB?

In another site I get this information:

DECLARE @return_status int
EXECUTE @return_status = BEGIN.BEPA $strAcctNum,
$strCustNum,
$decBalance,
$strFinalExtension,
'Entered through IVR',
0;
SELECT 'Return_Status' = @return_status

Regards

Guillermo

[Simon Edwards]

How did you manage to then use the variable it passed back in your UCCX script?  I'm trying something very similar, and although I get a success when I run the Stored Procedure, I simply can't work out how to get access tot the returned value.

[hleyva]

---

@mwadam wrote:

Joshua,

    You seem to have this figured out so I will run my issue by you.

    I have a similar issue where I have to run a stored procedure to retrieve the data.  I need to be able to use a variable such as $CustClaimNumber which is retrieved from the caller via the CRS script and pass it to the stored procedure in order to get the Claim Status.  When I run the script using the SQL syntax below I hit the DB Error step.  If I hard code the claim ID instead of using a variable the store procedure works.  Using my query against your solution I can see that you pass (my variable) of Claim Status, but what I need to pass is the Claim ID as collected in the script as $CustClaimNumber.

    We already have a view created which allows us to store the retrieved result and that works in the DB Get step, I just need to figure out how to change the claim id to a variable that I can pass to the procedure.

    Any suggestions?

    Thanks!!!

DECLARE @Claim_Status nvarchar(5);
EXEC IVR_GetStatusByClaimID_OUTPUT
  @ClaimID = $CustClaimNumber ;
  @Status = @Claim_Status OUTPUT;
SELECT @Claim_Status as N'Claim_Status'

---

How and where do you configure this "view" to store the retrieved value from the Store Procedure?

[geoff · Accepted solution]

Well, it's hard to give an example because every SQL stored procedure is different.

But the basics are:

1. Create your connection URL. Server, port, user, password, DB.

2. Establish the connection - here we are using the microsoft driver
   
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(connectionUrl);

3. Declare the right type of SQL object for this exercise. Here we need a CallableStatement.

CallableStatement proc = null;

4. Set it up on the connection with the stored procedure name and the appropriate positional parameters (you have 2). Note the syntax.

String storedProcName = "myStoredProcedure";

proc = con.prepareCall("{ call " + storedProcName + "(?, ?) }");

5. Set the real values into those positional params. Are they strings?

proc.setString(1, variable1);

proc.setString(2, variable2);

6. Now execute

proc.execute();

If you have a return value you may be writing { ? = call myStoredProc(?,?)}

If your stored procedure returns data as an output parameter you will need something like proc.registerOutParameter(3, Types.CHAR);

I assume you know that this must all be in a try-catch - and you need to use finally to make sure you nicely tidy up and close all resources. It sounds like you know this part.

Good luck.

Regards,

Geoff

[joshuamarsh]

Great info.  Thank you both for the contribution.  Rated appropriately.

J

[indumathiIVR]

Hi,

From the thread I can understand that through DB Read step Stored Procedure  can be accessed.

Could any body eloborate how the result of Stored procedure can be set into Script variable by DBGet Step.

For example I am using following SQL Query in DB Read step.

DECLARE         @return_value int,

                           @Result int;

EXEC   @return_value = [dbo].[SP_ValidateUser]

            @ContactPersonId =$input1 ,

            @ContactPersonTypeid = $input2  ,

            @Result = @Result OUTPUT;

SELECT            @Result as '@Result'

What would be the Table/view available in field selection tab of DBGet step?

If any body can post the uccx script which access stored procedure, that would be really helpful.

Thanks in advance.

[joshuamarsh]

Script provided via private msg.

J

[indumathiIVR]

Hi Joshua,

Your script was so helpful. But still I want how to set the output put parameter of a stored procedure to a script variable.

If you could post a sample for it, it would be really helpful.

Thanks,

Indumathi.P

[indumathiIVR]

Hi,

As I have not get solution to get the output parameter from stored procedure through UCCX script steps,  I ended up with using custom java class.

For accessing custom java through java, faced few security violation issues. To overcome that, we need root level access and have to add some previleges in java.policy file(located in /usr/local/thirdparty/java/jdk1.6.0_20/jre/lib/java.policy of UCCX 8.0 server.)

In java.policy file following are the list of previleges I added.

permission java.lang.RuntimePermission "accessClassInPackage.sun.jdbc.odbc";

permission java.lang.RuntimePermission "modifyThreadGroup";

permission java.lang.RuntimePermission "modifyThread";

permission java.net.SocketPermission "SQLDB:1433", "connect, accept, resolve, listen";

permission java.io.FilePermission "/tmp", "read";

permission java.util.PropertyPermission "java.io.tmpdir", "read";

Whenever you are doing modifications, do validate through policy tool.

Thanks for all your support.

[HumzaFaqi39861]

Can you please upload your custom java class to get the output parameter from stored procedure

[davidlhoffman]

I feel like an idiot reviving this topic but I think I've followed all the guidelines and have had zero success.  If I paste this code directly into MS Management Studio, it runs perfectly, but fails when embedded in a DB Read step in UCCX 8.5 Premium:

DECLARE @ret int;EXECUTE @ret = asp_LogWriter;SELECT 'ret' = @ret

1. The stored procedure simply inserts 1 record into a table

2. I've removed all parameters and hardwired the inserted values

3. I've removed carriage returns from the ends of lines

4. I declare and select a return value

5. I've matched upper/lower-case usage to follow examples here, just to be superstitious

I cannot get this code to execute from within UCCX no matter how many variations I try.  The corresponding SQL code (not in a stored procedure) runs just fine.

Many thanks for any feedback-

-David