Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: JDBC Driver and SQL 2k5

    I've ALWAYS managed transaction inside of sprocs...now that we upgraded one app to 2k5 (we have others on 2k5) we are getting errors...gotta go dig them up...but IBM responded below...Is this completely smoke?

    It looks like you are beginning and committing transactions via SQL within your stored procedures. The DataDirect Connect for JDBC driver has never supported this type of behavior on the grounds that in JDBC, transaction management must always be done through the JDBC driver. However, in older versions of their driver, they (DataDirect) did nothing to prevent this sort of misuse. But in more recent versions of the DataDirect Connect for JDBC driver, their code is designed such that these types of transactions unknown to the JDBC driver will cause errors. This issue has previously been escalated to DataDirect, and they have stated it is impossible (and contrary to the JDBC specification) to reverse their behavior.

    Our first recommendation is to remove all transactions from the stored procedures and always use JDBC for transactions instead. If you are unable (or unwilling) to do this, and the best workaround that we can offer is to provide you with a backlevel DataDirect Connect for JDBC driver, that is prior to when DataDirect changed their code, which is what the IBM link that you referenced in your email is for). We should emphasize that even when using the backlevel driver, the usage of transactions inside of stored procedures is still considered unsupported by DataDirect Technologies, and so you would be doing this at your own risk as we would be unable to get any fixes from DataDirect in this scenario.

    I believe you would have to have a pre-6.1.0.15 level to get the older DataDirect driver out of the fixpacks, since I believe it was changed in 6.1.0.15 (hence the 6.1.0.17 level didn't work for you). If needed, I can work with the team to get you the back-level driver if you want to go that route either temporarily (while you make the changes required in your app) or permanently (but remember doing so is pretty much unsupported by DataDirect).


    Kind Regards,
    Susan

    Susan M. Hanson
    Service Release Architect, WebSphere Application Server
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Provided no transactions "bridge" stored procedures (you begin a transaction in one procedure, and commit in another), then the JDBC driver should not care, or even have the ability to notice. The JDBC driver may tag on the end a statement like this;
    Code:
    if @@trancount > 0 commit transaction
    If the transactions are not entirely within the stored procedures, then this would cause you problems, since the commit transaction that you give would then have no begin transaction to pair with.

    WebLogic (and maybe Java in general) has a transactional system in it that will allow you to have transactions across multiple databases and/or database systems. The support person you are talking to may be getting confused about what sort of transactions you are trying to do.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    well that was my take

    I always have 1 BEGIN, 1 COMMIT in the fall through logic to the exit, and 1 ROLLBACK in the ERROR handling routine


    BEGIN

    ...code


    COMMIT

    EXIT:

    RETURN

    ERROR:

    ROLLBACK

    GOTO EXIT
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's it?

    No one else?

    Maybe I need to talk to M$
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Great...so all it was, was that a sproc had 2 result sets, that the front end didn't know about, and didn't manage, and the new jdbc driver was "fixed" to through an error that the developer wasn't aware of

    It had nothing to do with transactions in sql server
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Two actual result sets, or was it using a temp table? I have seen the temp table thing give an error until SET NOCOUNT ON was set in the procedure.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    2 actual result sets...I always have SET NOCOUNT ON in my sprocs

    Don't you?

    Has bean town?

    SOX making it in this year?

    Seen Brady hobbling around?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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