Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006

    Unanswered: Need help with transactions in java


    I cant figure out what is wrong with this code.
    What is does is basicly this.

    First the connection is set to autocommit= false
    1. Run a stored procedure to delete some data
    2. Generate a long insertstring from arrays sent to this method.
    3. Extract some vital data from the arrays for later use.
    4. Execute the insertstring from pt. 2
    5. Execute a stored procedure with the vital data extracted in pt. 3.
    6. Commit the changes.

    There is a try catch around all this so that only if all goes well it will do a commit in the end.
    if an exception occurs a rollback will be executed.

    last the connection is set back to autocommit=true again.

    The problem is that when i run this method i dont get any exceptions and all the debug System.out.prints is showing the correct information.
    However when i check the database (SQL SERVER 2000), it does not contain any of the data sent.

    My best guess is that there is something wrong with the way it is commited.

    Any help and comments will be greatly appreciated!

    Code follows below, never mind the norwegian comments.


    public void postBioData(Connection conn, int mp_user_id, long bio_id[], long bio_value_id[], String version) throws SQLException, IndexOutOfBoundsException, Exception{
    System.out.println("Call mottatt av dbm");
    //når man kommer hit skal det være sjekket for likt antall bioid og biovalueid

    //setter først connection til å ikke være auto-commit som den er pr. default

    //sletter gammel bio_data i web_user_attributes fra brukeren
    CallableStatement dbcall = conn.prepareCall("{call DELETE_BIO_FROM_USER(?)}");
    dbcall.setInt(1, mp_user_id);
    System.out.println("Kjørt 1");

    //bygger streng for å sette inn nye biodata i web_user_attributes
    String finalsqlbatch = "";

    //variabler for attributter som skal ligge direkte i web_user
    int sex =0;
    int province=0;
    int year=0;
    int day=0;
    int month=0;
    int age=0;

    for(int i=0;i<bio_id.length;i++){

    17 sex
    18 province
    19 year
    20 month
    21 day

    sex = (int)bio_value_id[i];
    else if(bio_id[i]==18){
    province = (int)bio_value_id[i];
    else if(bio_id[i]==19){
    year = (int)bio_value_id[i];
    else if(bio_id[i]==20){
    month = (int)bio_value_id[i];
    else if(bio_id[i]==21){
    day = (int)bio_value_id[i];
    //kjører streng for å legge inn biodata i web_user_attributes
    PreparedStatement ps = conn.prepareStatement(finalsqlbatch);
    System.out.println("Kjørt 2");

    //konverterer fødselsdata til java.sql.Date
    Calendar cal = Calendar.getInstance();
    java.sql.Date birthdate = null;
    cal.set(year, month-1, day);
    birthdate = new java.sql.Date(cal.getTime().getTime());

    //finner alder fra calenderen
    // Create a calendar object with today's date
    Calendar today = Calendar.getInstance();

    // Get age based on year
    age = today.get(Calendar.YEAR) - cal.get(Calendar.YEAR);

    // Add the tentative age to the date of birth to get this year's birthday
    cal.add(Calendar.YEAR, age);

    // If this year's birthday has not happened yet, subtract one from age
    if (today.before(cal)) {
    System.out.println("Age: "+ age + "\nSex :" + sex + "\nProvince: "+ province + "\nBirthdate: "+birthdate);

    //setter inn variabler som skal ligge direkte i web_useren
    CallableStatement dbcall2 = conn.prepareCall("{call INSERT_BIO_BASICS(?,?,?,?,?)}");
    dbcall2.setInt(1, mp_user_id);
    dbcall2.setInt(2, sex);
    dbcall2.setInt(3, province);
    dbcall2.setInt(4, age);
    dbcall2.setDate(5, birthdate);
    System.out.println("Kjørt 3");
    //committer endringer i db og setter connectionen tilbake til autocommit.
    //stenger preparedstatement og callablestatement

    }catch(Exception e){
    //dropper alle endringer gjort i denne transaksjonen

    //setter autocommit tilbake til true

    //kaster exception videre
    throw e;


  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    close() the statements after commit.

  3. #3
    Join Date
    May 2006
    There is a problem by closing all the statements at the end.
    You get SQL exception:
    Can't start a cloned connection while in manual transaction mode.

  4. #4
    Join Date
    May 2006
    Eagan, MN , USA

    Resolutions on your issue

    Hi Daheri,

    Please do the following :
    1. As you are using, SQL Server as a database, when you use manual transaction mode, you must set the SelectMethod property of the driver to "Cursor" ("Direct" is default). This should hit the bullet on the head.

    I may suggest that you use only one active statement on each connection.

    You know what I mean,

    1. As far as possible, do n pass Connection object in argument to your method. You should create a new instance within a method with
    Connection conn = null;

    2. Keep your code small, simple and generic. Let "postBioData" call methods internally, one each for type of statement (PreparedStatement / CallableStatement) etc.

    3. Have the following, else your code will face performance issues on the server creating bottolneck at stress / load usage. Close what is open. Because it gives error, you should not skip it.

    if(con != null)
    catch(SQLException e)

    Please keep the quality and beauty in mind. All the best !

    Thanks & regards,
    Prasad H. Salaskar

  5. #5
    Join Date
    May 2006
    Thanks for the tips Prasad,

    I'm getting my Connections from a connection pool and that is why a connection is passed to the method. It is also the reason why I don't close the connection in the end. (The connection is returned to the pool outside this method).

    Selectmetod = cursor has been there all along, and does not seem to solve my problem.
    I dont think I metioned this before, but this method works perfectly when I'm running it locally on my computer. The problem is when i'm deploying it on a development server.

    Since i dont get any exceptions and all my system.out.prints show the right values, i'm starting to suspect that the problem may be that the SQL-server running locally is differently configured than the server running as a development server.
    Somehow the data posted to the sql-server is not committed, but locally everything is committed like a charm.

    Since i'm on some strict deadlines I will have to reconfigure the whole code if i dont get this to work soon.
    My options as I see it:
    A: Run everything with autocommit=true. (Very bad if something goes wrong halfway through the code)
    B: Create one big Stored procedure to handle all the three calls as one. (Bad since the sql-server dont take arrays and I will have to preform varchar splits inside a stored procedure.)

    If you have some more ideas I'd be very happy to hear them!

    Thanks in advance!

Posting Permissions

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