Discussions

General J2EE: Transactions and Stored procs.

Bookmark and Share Get thread feed

  1. Transactions and Stored procs. (2 messages)

    • Posted by: Smythe
    • Posted on: January 30 2004 05:11 EST
    Hi,

    I'm updating a legacy application VB and converting it to a web based solution using J2EE (probably just Servlets and JSPs initially.) The Application at the moment uses alot of stored procs so I think I might have to reuse these rather than deplicating this functionality in my JDBC code (Time constraints, etc).

    But I have a question about transactions... (We are on weblogic using a TX data source)

    1. When callling stores procs in succession, that all need to do work within the same transaction (call them A, B and C). Is it possible to do this by using:

    Context ctx = new InitialContext();

    UserTransaction userTx = ctx.lookup("java:comp/UserTransaction");

    userTx.begin();

    //JDBC CODE TO CALL STORED PROC A

    //JDBC CODE TO CALL STORED PROC B

    //JDBC CODE TO CALL STORED PROC C

    userTx.commit();


    Assume that procs A, B anc C update and insert database records. Would they all be called within the same transaction. Or since they are stored procs would they commit their own transactions individually? Would I have to configue the database somehow to disable auto commit for the procs?

    Thanks

    Smythe
    Reply to this

    Threaded Messages (2)

  2. Transactions and Stored procs.[ Go to top ]

    Hello Smythe,

    there's nothing special about stored procedures and I've never heard of a database that has an "autocommit" feature for them. Assuming that you don't have any commit/rollback statements in the procs themselves, your approach will work fine. I've done that several times with Weblogic using either Oracle or SQL Server and never had any problems.

    Regards,
    Stefan
    Reply to this Reply to original

  3. tnsnames.ora and java[ Go to top ]

    recently, we loaded the oracle manager so tnsnames get resolved by dns lookup. So using oracle's jdbc:odbc driver connection params used to look like this:
    ("jdbc:oracle:thin:@server:port:sid", "user", "passwd");

    now blows up because the server ip is "resolved" via tnsnames and not static ip. Being a nubie i found the statement ("jdbc:odbc:thin:scott/tiger@profits") which is the new way ?? to resolve the server name, but it just doesn't work properly? I would like to test this client side, then ultimately load the new method for making the connection in my cocoon instance for an xml-rpc implementation....

    <datasources>
        <jdbc name="test_oracle_pool">
          <pool-controller min="5" max="10" oradb="true"/>
          <auto-commit>true</auto-commit>
          <dburl>jdbc:oracle:thin:@[hostname]:[port]:[tnsname]</dburl>
          <user>[user]</user>
          <password>[password]</password>
        </jdbc>
      </datasources>
    Reply to this Reply to original