JDBC Stored Procedure Call

[roaming]

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?

[jmistrik]

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]

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]

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

---