Discussions

General J2EE: Transactions and Stored procs.

  1. Transactions and Stored procs. (2 messages)

    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

    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
  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>