webMethods

 View Only
  • 1.  Jdbc stored Procedure

    Posted Thu September 09, 2004 03:56 PM

    hi ,
    I am using Jdbc adapter services to call stored procedure ,i created stored procedure adapter service to call stored procedure in SQL server.
    this is my first experience using jdbc service,
    my stored procedure is taking one parameter as input
    so i have defined one variable in
    Parameter tab
    >Param Jdbc type :Param name :param type: expression
    and that stored procedure is returning me 10 values so for that i defined
    call tab
    Jdbc type & Return Field name
    (i can define only one field i cant define 10 fields )

    my question is if my adapter service is returning 10 fields
    where i need to define those 10 fields.

    any help will be highly appreciated
    Thanks


    #webMethods
    #Integration-Server-and-ESB
    #Adapters-and-E-Standards


  • 2.  RE: Jdbc stored Procedure

    Posted Thu September 09, 2004 04:15 PM

    Kitt,

    you can define any number of out values depends on the SP returns OUT.

    So under the parameter tab you define those 10 fields and set
    Param Type to OUT,so this will automatically adds the OutputName and OutputType and ultimately you will see these fields in the Input/Output tab of the SP AdapterService.

    HTH,
    RMG


    #webMethods
    #Adapters-and-E-Standards
    #Integration-Server-and-ESB


  • 3.  RE: Jdbc stored Procedure

    Posted Thu September 09, 2004 04:29 PM

    thanks RMG for the quick reply
    I gave the 1 input value(IN) and 2 output value (OUT) (just to test)
    and i can see 1 input and 2 output in input/output tab.
    but when i run the service i can see the error ::

    com.wm.app.b2b.server.ServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service XXX.XXX.XXX:SimstoredProc.
    [ADA.1.316] Cannot execute the SQL statement “{call
    XXX.XX.XXXXXXXX(?, ?, ?) }”. "
    (HY000/8144) [wm-cjdbc33-0009][SQLServer JDBC Driver][SQLServer]Procedure or function XXXXXXXXXXX has too many arguments specified."
    [wm-cjdbc33-0009][SQLServer JDBC Driver][SQLServer]Procedure or function XXXXXXXXX has too many arguments specified.

    thanks


    #Integration-Server-and-ESB
    #webMethods
    #Adapters-and-E-Standards


  • 4.  RE: Jdbc stored Procedure

    Posted Thu September 09, 2004 04:37 PM

    Kitt,

    Did you tested this SP call using any client TOAD or SQL plus…etc…if SP is returning different out values then the SP AdapterService should definetely work.

    Since the error indicates too many arguments,test the SP using client and later debug the AS.

    HTH,
    RMG


    #Integration-Server-and-ESB
    #Adapters-and-E-Standards
    #webMethods


  • 5.  RE: Jdbc stored Procedure

    Posted Thu September 09, 2004 05:10 PM

    RMG,
    I tryed to run that procedure through SQL analyser
    and i could not run that procedure I got the error message that

    CREATE PROCEDURE permission denied in database ‘DatabaseSis’.

    I hope that user dosen’t have execute permission to run that stored procedure.or it might be some other resons.I need to check with DB guys .

    Thanks for the help RMG


    #Adapters-and-E-Standards
    #webMethods
    #Integration-Server-and-ESB


  • 6.  RE: Jdbc stored Procedure

    Posted Tue September 14, 2004 02:53 PM

    Here is another thing to look for. I caused me problems. This information came from [url=“http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm#34_05”]http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm#34_05[/url]

    Do Oracle’s JDBC drivers support PL/SQL tables/result sets/records/booleans?
    No. It is not feasible for Oracle JDBC drivers to support calling arguments or return values of the PL/SQL types TABLE (now known as indexed-by tables), RESULT SET, RECORD, or BOOLEAN. There are currently no plans to change this. Instead people are encouraged to use RefCursor, Oracle Collections and Structured Object Types.

    As a workaround, you can create wrapper procedures that handle the data as types supported by JDBC.

    For example, to wrap a stored procedure that uses PL/SQL booleans, you can create a stored procedure that takes a character or number from JDBC and passes it to the original procedure as BOOLEAN, or, for an output parameter, accepts a BOOLEAN argument from the original procedure and passes it as a CHAR or NUMBER to JDBC. Similarly, to wrap a stored procedure that uses PL/SQL records, you can create a stored procedure that handles a record in its individual components (such as CHAR and NUMBER). To wrap a stored procedure that uses PL/SQL tables, you can break the data into components or perhaps use Oracle collection types.

    Here is an example of a PL/SQL wrapper procedure MY_PROC for a stored procedure PROC that takes a BOOLEAN as input:

    PROCEDURE MY_PROC (n NUMBER) IS
    BEGIN
    IF n=0
    THEN proc(false);
    ELSE proc(true);
    END IF;
    END;

    PROCEDURE PROC (b BOOLEAN) IS
    BEGIN

    END;


    #webMethods
    #Adapters-and-E-Standards
    #Integration-Server-and-ESB