SQL Stored Procedures in the DBRead Step

[swright]

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!

[ciscogini]

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

[swright]

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

[ciscogini]

Here is one example try this format

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

[swright]

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!

[ciscogini]

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

[swright]

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!

[ciscogini]

good, let me know if you need more help

[swright]

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?

[kevin.buckley]

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.

[swright]

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.

[k.b.buckley]

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.

[l.mourits]

Any change you can post the script and class?

I'll be very interested ;-)

Thanks,

Leo

[dbrandon]

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..