Results 1 to 9 of 9
  1. #1
    Jack_Tauson_Sr is offline Senior Member
    Join Date
    Nov 2011
    Posts
    114
    Rep Power
    0

    Default Am I using transaction correctly?

    Please consider the following code. Since, my main question revolves around the usage of transaction, I haven't bothered to include full code here. The following code just gives an overwiew of what I am doing and what results I am getting after running the program. If you would like to look at an actual code, please take a look here. However, I feel that the following code snippet is sufficient for understanding my question. Thanks

    So, I have something like the following inside the try block:

    Java Code:
        try{
         
          Connection connRemote = DriverManager.getConnection("jdbc:mysql:// connecting to MySQL database located at 11.11.1.111"); 
          connRemote.setAutoCommit(false); // Starting Transaction here
          maindbsql = "SELECT IP_vch FROM mytable "; // These are the possible IP's that 
          //gets selected :22.22.2.222 , 33.33.3.333,44.44.4.444
        
          Map<String,Connection> connections = new HashMap<>();
          DeviceStmt = connRemote.createStatement();
          DeviceRS = DeviceStmt.executeQuery(maindbsql);
        
          while(DeviceRS.next()){
              final String ip_address = DeviceRS.getString("IP_vch");
              System.out.println("Value of IP_vch Field:"+ip_address);
              connections.put(ip_address,DriverManager.getConnection("jdbc:mysql://" + ip_address + ":3306/test",User,Pass));
            // I am connecting to the following connections above 22.22.2.222 , 33.33.3.333,44.44.4.444
            }//END Of while(DeviceRS.next())
         
           for(final String ip : connections.keySet())
           {
             String QueryString = "SELECT DTSId_int & more fields from test.selectiontable WHERE DTSStatustype_int = 1 
            // here I am selecting some data (10 records at a time) from the MySQL located at 11.11.1.111 "
             String QueryInsertRemote = " INSERT INTO test.insertiontable // MySQL table located at each of the following locations 22.22.2.222 , 33.33.3.333,44.44.4.444
        
        	  // Here I am updating one specific field called `DTSStatustype_int of the 10 records I selected in `QueryString` to number `3` so that next time when my loop starts, I don't select already copied records and t
             //  insert into test.insertiontable
         
        	 StringBuilder sqlSelect = new StringBuilder(1024);
        	 sqlSelect.append("UPDATE test.selectiontable ");
        	 sqlSelect.append("SET DTSStatusType_ti = 3,");
        	 sqlSelect.append("Queued_DialerIP_vch = ? ");
        	 sqlSelect.append("WHERE DTSId_int IN ( "); 
         
             PreparedStatement pst = connRemote.prepareStatement( sqlSelect.toString() );
        	 pst.executeUpdate()'
             connRemote.commit(); // Transaction Commited
            System.out.println("Checking Prepared Statement:"+pst);
           } // END Of For each loop 
         }// end of try block

    More information:

    - DTSId_int is a field that exists in the MySQL table for all the IP addresses above. IT's a primary key and set to autoincrement.

    - DTSStatusType_ti is initially set to 1 in test.selectiontable and then getting updated to 3



    **My Question:**



    When I check the results of prepared statement, I get the following, when the for loop runs for the first time:


    Java Code:
     Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@1052ebec:UPDATE test.selectiontable  SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '22.22.2.222'
    [CODE]WHERE DTSId_int IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)[CODE]

    Java Code:
     Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@1052ebec:UPDATE test.selectiontable  SET DTSStatusType_ti = 3,Queued_DialerIP_vch = ''
    [CODE]WHERE DTSId_int IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)[CODE]

    Java Code:
    Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@1052ebec:UPDATE test.selectiontable  SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '44.44.4.444'
    Java Code:
    WHERE DTSId_int IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
    When for loop runs for the second time, I get the following results:


    [CODE] Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@10 52ebec:UPDATE test.selectiontable SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '22.22.2.222' WHERE DTSId_int IN ( 11,12, 13, 14, 15, 16, 17, 18, 19, 20)

    Java Code:
     Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@1052ebec:UPDATE test.selectiontable  SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '33.33.3.333'
    Java Code:
     WHERE DTSId_int IN ( 11,12, 13, 14, 15, 16, 17, 18, 19, 20)
    Java Code:
    Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@1052ebec:UPDATE test.selectiontable  SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '44.44.4.444'

    Java Code:
    WHERE DTSId_int IN ( 11,12, 13, 14, 15, 16, 17, 18, 19, 20)

    **Problem I am facing:**

    The problem here is, the first 10 values in `test.selectiontable` is getting updated three times at a time. Eventually, it get's updated with the most recent value, which is 44.44.4.444.

    I want something like following results which makes sense:
    Java Code:
        Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@1052ebec:UPDATE test.selectiontable  SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '22.22.2.222'

    Java Code:
    WHERE DTSId_int IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
    Java Code:
    Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@1052ebec:UPDATE test.selectiontable  SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '33.33.3.333'
    Java Code:
    WHERE DTSId_int IN ( 11,12, 13, 14, 15, 16, 17, 18, 19, 20)
    Java Code:
     Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@1052ebec:UPDATE test.selectiontable  SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '44.44.4.444'
    Java Code:
    WHERE DTSId_int IN ( 21, 22, 23, 24, 25, 26, 27, 28, 29, 30)
    Please let me know what am I doing wrong in my transaction related code.

    Thanks
    Last edited by Jack_Tauson_Sr; 04-10-2014 at 06:29 PM.

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,044
    Rep Power
    20

    Default Re: Am I using transaction correctly?

    Can you break up those "Checking" lines, possibly not putting them in code tags, as the relevant parts of them are vanishing off the page?
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    Jack_Tauson_Sr is offline Senior Member
    Join Date
    Nov 2011
    Posts
    114
    Rep Power
    0

    Default Re: Am I using transaction correctly?

    Done.

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,044
    Rep Power
    20

    Default Re: Am I using transaction correctly?

    I don't know what it looks like on your screen,. but code tags really aren't helping at all for the SQL.
    Unless you plan on putting in line breaks of your own.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  5. #5
    Jack_Tauson_Sr is offline Senior Member
    Join Date
    Nov 2011
    Posts
    114
    Rep Power
    0

    Default Re: Am I using transaction correctly?

    I have deliberately done that to make things look clear after you wrote to break off the statement. I hope you can imagine the whole SQL statement as a single one. I don't know any other way to show it here as this is this website's problem and not mine.
    Quote Originally Posted by Tolls View Post
    I don't know what it looks like on your screen,. but code tags really aren't helping at all for the SQL.
    Unless you plan on putting in line breaks of your own.

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,044
    Rep Power
    20

    Default Re: Am I using transaction correctly?

    Quote Originally Posted by Jack_Tauson_Sr View Post
    When I check the results of prepared statement, I get the following, when the for loop runs for the first time:


    Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@10 52ebec:
    UPDATE test.selectiontable
    SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '22.22.2.222'
    WHERE DTSId_int IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)


    Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@10 52ebec:
    UPDATE test.selectiontable
    SET DTSStatusType_ti = 3,Queued_DialerIP_vch = ''
    WHERE DTSId_int IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

    Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@10 52ebec:
    UPDATE test.selectiontable
    SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '44.44.4.444'
    WHERE DTSId_int IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)


    When for loop runs for the second time, I get the following results:

    Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@10 52ebec:
    UPDATE test.selectiontable
    SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '22.22.2.222'
    WHERE DTSId_int IN ( 11,12, 13, 14, 15, 16, 17, 18, 19, 20)

    Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@10 52ebec:
    UPDATE test.selectiontable
    SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '33.33.3.333'
    WHERE DTSId_int IN ( 11,12, 13, 14, 15, 16, 17, 18, 19, 20)

    Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@10 52ebec
    UPDATE test.selectiontable
    SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '44.44.4.444'
    WHERE DTSId_int IN ( 11,12, 13, 14, 15, 16, 17, 18, 19, 20)


    **Problem I am facing:**

    The problem here is, the first 10 values in `test.selectiontable` is getting updated three times at a time. Eventually, it get's updated with the most recent value, which is 44.44.4.444.

    I want something like following results which makes sense:

    Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@10 52ebec:
    UPDATE test.selectiontable
    SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '22.22.2.222'
    WHERE DTSId_int IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

    Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@10 52ebec:
    UPDATE test.selectiontable
    SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '33.33.3.333'
    WHERE DTSId_int IN ( 11,12, 13, 14, 15, 16, 17, 18, 19, 20)

    Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@10 52ebec:
    UPDATE test.selectiontable
    SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '44.44.4.444'
    WHERE DTSId_int IN ( 21, 22, 23, 24, 25, 26, 27, 28, 29, 30)

    Please let me know what am I doing wrong in my transaction related code.

    Thanks
    I did suggest not using code tags.
    The above is now readable without having to quote the post.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  7. #7
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,044
    Rep Power
    20

    Default Re: Am I using transaction correctly?

    As for your problem, print out the keyset.
    Also I don't see where the values for the DTSId IN clause are coming from.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  8. #8
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    4,050
    Rep Power
    6

    Default Re: Am I using transaction correctly?

    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  9. #9
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,044
    Rep Power
    20

    Default Re: Am I using transaction correctly?

    Not sure, as for that one the OP had a problem getting the SQL to execute.
    I'm guessing that's been solved and now there's a problem with incorrect parameters for the queries.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

Similar Threads

  1. Hi Need help to build transaction
    By helpmelearn in forum New To Java
    Replies: 3
    Last Post: 02-08-2013, 05:57 AM
  2. ATM machine Transaction
    By udscrick in forum Advanced Java
    Replies: 1
    Last Post: 12-18-2011, 01:49 PM
  3. Error for second transaction
    By anilkumar_vist in forum Advanced Java
    Replies: 0
    Last Post: 12-29-2010, 03:22 AM
  4. EJB Transaction Problem
    By kembljoe in forum Enterprise JavaBeans (EJB)
    Replies: 0
    Last Post: 12-17-2009, 12:04 PM
  5. jsp:include in a sql:transaction
    By sam2020 in forum JavaServer Pages (JSP) and JSTL
    Replies: 0
    Last Post: 07-31-2008, 08:11 PM

Posting Permissions

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