Ask a Question

JDBC Stored Procedure Call

roaming
New Contributor

JDBC Stored Procedure Call

Using SOAP UI pro 4.5,

I am trying to make a call to a "Stored Procedure" through a JDBC Request,

I have searched through the websites and have been unable to find documentation that explains (in simple terms) what i need to do

so far i think the call needs to be in the format.....

{call Client..spGetBalance ('2012-07-16', 'param2, 'param3')}

The call returns one value, which is a count, and is not as expected.
If i make the call using "Microsoft SQL Server Management Studio" then i see all the data expected.

Any help/pointers where i can look to see how to call a Stored Procedure?
3 REPLIES 3
jmistrik
Occasional Contributor

Re: JDBC Stored Procedure Call

try:

declare @at datetime
declare @param1 varchar(50)
declare @param2 varchar(50)
set @at = '2012-07-16'
set @param1 = 'XXX'
set @param2 = 'YYY'

exec client..spGetBalance @at, @param1, @param2
shruti
Not applicable

Stored Procedure Call

I want to call the store procedure using SOAP UI.
I am using Oracle database.
I tried to call the procedure through Groovy script and JDBC also but I am not able to call store procedure using both .
My store procedure name TEST1 .It consists two parameters . One input parameter and output parameter.

CREATE procedure test1(abc in varchar,result out varchar)
is
begin
insert into tst1 values(abc);
dbms_output.put_line(result);
end;


I tried following syntax using JDBC:

1st Try :
declare @param1 varchar(50)
set @param1 := 'XXX'
exec client..TEST1 @param1

2nd Try :
execute TEST1('SAM',result)

3rd Try:
DECLARE
ABC VARCHAR2(32767);
BEGIN
ABC := 'Testing';
EMAPP.TEST1 ( ABC );
COMMIT;
END;


I used following combination through Groovy script

1st :
sql.call("{?=call TEST1(?)}",[sql.VARCHAR,'SHRUTI']) {result -> assert result=='OUTPUT'}

2nd :
def first = 'Sam'
sql.call("{$Sql.VARCHAR = call TEST1($first)}") { name ->
assert name == 'Sam Pullara'

Please suggest me that How I can call the store procedure using groovy or JDBC ?

Thanks
Shruti
ANDYtyara
Occasional Contributor

Re: Stored Procedure Call

Hi Shruti,

 

Create one java class file which would establish connection between JDBC and your DB.

 

Make jar file of this class and put it in SOAPUI bin/ext folder.

 

Use this class in groovy step to establish connection with DB  and then call store procedure with syntax as

 

{call SCHEMA_NAME.PACKAGE_NAME.STORE_PROCEDURE_NAME(?,?,?,?,?,?,?,?,?,?,?,?)}";

 

WHERE ?= IN and OUT parameters


@shruti wrote:
I want to call the store procedure using SOAP UI.
I am using Oracle database.
I tried to call the procedure through Groovy script and JDBC also but I am not able to call store procedure using both .
My store procedure name TEST1 .It consists two parameters . One input parameter and output parameter.

CREATE procedure test1(abc in varchar,result out varchar)
is
begin
insert into tst1 values(abc);
dbms_output.put_line(result);
end;


I tried following syntax using JDBC:

1st Try :
@declare @param1 varchar(50)
@set @param1 := 'XXX'
@exec client..TEST1 @param1

2nd Try :
execute TEST1('SAM',result)

3rd Try:
DECLARE
ABC VARCHAR2(32767);
BEGIN
ABC := 'Testing';
EMAPP.TEST1 ( ABC );
COMMIT;
END;


I used following combination through Groovy script

1st :
sql.call("{?=call TEST1(?)}",[sql.VARCHAR,'SHRUTI']) {result -> assert result=='OUTPUT'}

2nd :
def first = 'Sam'
sql.call("{$Sql.VARCHAR = call TEST1($first)}") { name ->
assert name == 'Sam Pullara'

Please suggest me that How I can call the store procedure using groovy or JDBC ?

Thanks
Shruti

 

cancel
Showing results for 
Search instead for 
Did you mean: