Re: Stored proc returning result set (and output params) with MS SQL server

From: Andrus Adamchik (andru..bjectstyle.org)
Date: Sun Apr 20 2008 - 11:10:16 EDT

  • Next message: Kevin Menard: "Re: Mysql and usernames"

    I think I found the problem. It was not a stored procedure call per
    se, but a transaction surrounding it. When wrapping a JDBC failing SP
    call with "connection.setAutoCommit(false)" and "conneciton.commit()"
    I get the same error as with Cayenne. Here is how to handle this in
    Cayenne workaround - use manual transaction handling, doing rollback
    instead of commit when the SP return values indicate a failure:

    Transaction tx = context.getParentDataDomain().createTransaction();
    Transaction.bindThreadTransaction(tx);

    QueryResponse result;
    try {
            result = context.performGenericQuery(query);
    } finally {
            try {
                    // here you can be smarter and do a commit/rollback
                    // based on the SP output if you care
                    tx.rollback();
            } catch (Exception e) {
                    throw new RuntimeException(e);
            }
            Transaction.bindThreadTransaction(null);
    }

    Andrus

    On Apr 17, 2008, at 3:52 PM, Andrus Adamchik wrote:

    > So Cayenne calls the sp in the following format:
    >
    >
    >> {? = call spXXX(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}
    >
    > In your JDBC test you do it like this:
    >
    >> { call spXXX(?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
    >
    >
    >
    > I.e. no return value is specified. So what if you uncheck "returning
    > value" checkbox for this stored procedure? Would you still get the
    > same error? (Sorry if my questions sound a bit random, I personally
    > haven't used stored procedures with Cayenne or JDBC for a couple of
    > years already, so my memory of all the quirks is a bit blurry).
    >
    > If this doesn't work, could you send me a stored procedure
    > definition so that I could test that locally. If you don't want to
    > send it to the public mailing list, you can email it to me directly.
    >
    > Andrus
    >



    This archive was generated by hypermail 2.0.0 : Sun Apr 20 2008 - 11:10:50 EDT