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
    13 years ago
    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's avatar
    shruti
    13 years ago
    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
      8 years ago

      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