JDBC Stored Procedure Call
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-16-2012
08:06 AM
07-16-2012
08:06 AM
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?
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
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-18-2012
06:29 AM
07-18-2012
06:29 AM
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
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
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-19-2012
11:06 PM
07-19-2012
11:06 PM
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
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
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-04-2017
06:46 AM
07-04-2017
06:46 AM
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
