Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. And see here for current known issues.

New Member

SQL Stored Procedures in the DBRead Step

I am trying to execute a Stored Procedure on a remote database server from a CRA script in the DB Read step using the following syntax:

exec [SRWebServiceID.prcCiscoSaveMessage] $strMessageID

But I get an error "java.lang.NullPointerException". I know that Stored Procedures are not officially supported by IPCC Express, but I have also heard on this forum that some people have gotten them to work. Are there any tricks to calling a stored procedure in the DBRead step? Thanks!

13 REPLIES
New Member

Re: SQL Stored Procedures in the DBRead Step

From My previous experice I can tell ypu this.

Stored procedures cna be made to work with CRA, but the format in which you call them and send parameters is what will decide if this will work or no.

I do not remeber on top of my head exaact formats used, but i will try to find out

New Member

Re: SQL Stored Procedures in the DBRead Step

Not to bother you or anything, but have you found anything? =)

New Member

Re: SQL Stored Procedures in the DBRead Step

Here is one example try this format

EXEC Storedproc1 @SvcDate = $Service_Date , @TestNumber = $Test_No , @CertNum = $Cert_No

New Member

Re: SQL Stored Procedures in the DBRead Step

Hmm, well, that seemed to work, partially. It looks like the database is updating the way it should, however, I'm still getting the java.lang.NullPointerException error in the IPCC script, which halts my script. I tried addeding an "On Exception Goto" step to handle the problem, but it looks like java.lang.NullPointerException is not an exception listed in the "On Exception goto" step. Any ideas? Thanks!

New Member

Re: SQL Stored Procedures in the DBRead Step

I it may also be related to the way you have defined your varibles in the Script. It may not like the variables type you have define.

What type of varibels you have and can you past the varivles and the type

p.s: rate the posting if it helps

New Member

Re: SQL Stored Procedures in the DBRead Step

I think I have an issue resolved, I added a variable for the return code ($intReturnCode)sent back from the SP and added that variable to the SP call:

EXEC $intReturnCode = dbo.prcCiscoChangePIN @Mailbox = $strBoxNumber , @PIN = $strCurrentPINNumber , @NewPIN = $strNewPINNumber

I'm not getting the Java Exception anymore, but I am getting sent to the SQL error branch now. Getting closer!

New Member

Re: SQL Stored Procedures in the DBRead Step

good, let me know if you need more help

New Member

Re: SQL Stored Procedures in the DBRead Step

Well, still having issues, and still have not had any success with this. I am now attempting to use a DB Write step to INSERT a record into a table that will have a Trigger on it that will run the Stored Procedure instead, but it looks like the DB Write only accepts hard coded VALUEs, not String Variables from the script like I need. Has anyone had any luck using the DB Write step with Variables?

New Member

Re: SQL Stored Procedures in the DBRead Step

Any luck getting this to work? I am trying to get a stored procedure to return data in a Read step and I am not haveing any luck so far.

New Member

Re: SQL Stored Procedures in the DBRead Step

As I recall, what I ended up doing was using a web server as an intermediary, sending an HTTP Post or Get from the IPCC Express server to the web server, which would then in turn write the values to the SQL server. I had the help of some very good SQL and Web programmers from the company I was collaborating with who suggested the idea and implemented the web and SQL side.

New Member

Re: SQL Stored Procedures in the DBRead Step

Thanks for the info. I think I figured out how to correctly issue a stored procedure call, but the problem was that I needed the return values, and there was no way to obtain them in the DB Get step.

What we ended up doing was breaking out into a java class and issuing a jdbc call.

Thanks.

Silver

Re: SQL Stored Procedures in the DBRead Step

Any change you can post the script and class?

I'll be very interested ;-)

Thanks,

Leo

New Member

Re: SQL Stored Procedures in the DBRead Step

yes i know how!!

at the end of the procedure, get the date..

it never fails, and will return one record..

which satisfies the dbread

create procedure [dbo].[p_update_optout]

(@p_account_number char(16)) as

update t_ivr_optout

set optout_flag = 'Y',

update_date = getdate ()

where account_number = @p_account_number;

select getdate();

so you would call it as

exec $intResultCode=dbo.p_update_optout p_account_number = $somevariable

this works on mssql.. i am having issues with the oracle version..

324
Views
19
Helpful
13
Replies
CreatePlease login to create content