Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2001
    Location
    England
    Posts
    426

    Unanswered:

    never used jdbc.
    Does the insert work?

    Try it with just a sp returning a value in a single output parameter.
    Can you access parameters by name?
    Can you get the return value?
    Output parameters are actually input/output - maybe that makes a difference.

    Try putting a set nocount on at the top of the sp - maybe the driver is trying to return the record count first.

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I've never used JDBC, I work with ADO, however in ADO there are 2 types of output parameters adParamOutput and adParamInputOutput. When using stored procedures that have a column defined as OUTPUT I must use adParamInputOutput, even though I'm not inputting a value. I notice you have defined your parameter as 'registerOutParameter', does JDBC have a parameter type 'registerInOutParameter'?

    Since you are using SQL Server (assuming), turn on SQL Profiler and capture the execution of the statement, you should see the '@entryid' argument defined with the OUTPUT statement, something like this:

    declare @P4 int
    set @P4=0
    exec constructEntry 1001, "xyz","abc", @P4 OUTPUT
    SELECT @P4

  3. #3
    Join Date
    Dec 2001
    Posts
    1
    Hi achorozy and nigelrivett

    Thanks for your suggestions. I will try running with ur feedback.

    JDBC does not have a parameter type 'registerInOutParameter'. Just a registerOutParameter.

    The insert does not work as I have a rollBack set to true if I catch a SQLException. But will try setting rollback to false and see what happens.

    Can you access parameters by name?
    Unfortunately, parameters cannot be accessed by name.

    Can you get the return value?
    When I try to get the return value, it throws the SQLException i mentioned about in my first post.

    Thanks
    Amey

  4. #4
    Join Date
    Jan 2002
    Location
    INDIA
    Posts
    9
    I am having the same problem too !!!! I'm not able to get the OUT params in any SP !! same exception !! have you found a solution to it ? If so can you please post it here ?? Thanks !!

  5. #5
    Join Date
    Jan 2002
    Location
    INDIA
    Posts
    9

    Cool OUT parameter probem solution !!!

    Hi !!

    I've solved the problem with OUT parameters !! This seems to be a problem with the ODBC stack of Microsoft !! Problem does not arise with other drivers !! This problem arises not only with the ODBC-JDBC driver but with apps like Visual Basic, VC++, etc with use MS ODBC stack !!

    The solution:

    Do not return Resultsets with OUTPUT parameters !! This does not work. The solution is to split the stored procedures into two procedures, one returning the OUTPUT parameter (if required) and another returning the resultset. Another workaround would be to include the OUTPUT parameter as another Column in the Resultset !! MS ODBC, JDBC client drivers do NOT allow you to return OUTPUT parameters with resultsets !! If you return either one of them alone no problem comes !!

    Hope this helps you people out there facing the same problem

    - madhan

  6. #6
    Join Date
    Feb 2002
    Posts
    2
    Since you are using an INSERT statement, use the .executeUpdate() method call rather than .execute()

    Also, in the stored procedure, use

    RETURN @@identity

    rather than listing a single output parameter alongside your input parameters.

    That should do the trick. (Worked for me.)

    Cordially,
    Erik

  7. #7
    Join Date
    Feb 2002
    Posts
    2
    The text for the callable statement should be:

    "{ ? = call procedureName (?, ?, ? . . ., ?)}"

    where the first ? references the sp's return value and must be indexed as 1, and where the other ?'s represent the input parameters, indexed as 2, 3, 4 . . . , n.

    Assuming cstmt is the callable statement,

    cstmt.registerOutParameter(1, Types.INTEGER);
    cstmt.executeUdate();
    int nIdentity = cstmt.getInt(1);

    Cordially,
    Erik

  8. #8
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    >> Do not return Resultsets with OUTPUT parameters !! This does not work.

    I'm afraid it does work with VB.
    You do have to make sure that all recordsets have been returned to the client thyough - usually setting cursor location to client is enough.
    It also has a habit of losing the parameter binding if you try to access them before the values are returned.

  9. #9
    Join Date
    Mar 2002
    Posts
    1

    Cool I had the exact problem and here's the solution =)

    There is only one thing you need to fix in your code to make this work. Just change 'execute()' to 'executeUpdate()'. That's it. For some reason you can't get at your OUT parameters when using the 'execute()' method of a CallableStatement.

    Cheers,

    Mason

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •