Forum Discussion

roaming's avatar
roaming
New Contributor
13 years ago

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?
  • jmistrik's avatar
    jmistrik
    Occasional Contributor
    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
  • 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's avatar
      ANDYtyara
      Occasional Contributor

      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