Solved

Can someone please see why my database program is not updating the database? code provided.

Posted on 2009-04-08
32
239 Views
Last Modified: 2012-05-06
I am trying to create a java/mysql version of the attached excel file.

Two programs in the code box below... CordCalc.java and Iteration.java
Screenshot of database attached.

CordCalc manipulates the data that is entered in then uses Iteration.java to eighter set the data into the database or retrieve the data.

The programs compile and run fine but they will not populate the database.

Can someone please have a look at my code to see where I am going wrong?

thanks in advance

package DataCalculations;
 

/*

 * To change this template, choose Tools | Templates

 * and open the template in the editor.

 */
 

/**

 *

 * @author Justin

 */
 

import java.sql.*;

import javax.swing.JFrame;

import javax.swing.JOptionPane;

import java.lang.Math;
 

public class CordCalc {

    

    final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

    final String DATABASE_URL = "jdbc:mysql://localhost:3306/rfiddata";

    Connection connection = null; // manages connection

      

     

            JFrame frame = new JFrame();

            String text1 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX1x here");

            float RX1x = Float.parseFloat(text1);

            

            String text2 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX1y here");

            float RX1y = Float.parseFloat(text2);

            

            String text3 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX1z here");

            float RX1z = Float.parseFloat(text3);

            

            String text4 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX2x here");

            float RX2x = Float.parseFloat(text4);

            

            String text5 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX2y here");

            float RX2y = Float.parseFloat(text5);

            

            String text6 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX2z here");

            float RX2z = Float.parseFloat(text6);

            

            String text7 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX3x here");

            float RX3x = Float.parseFloat(text7);

            

            String text8 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX3y here");

            float RX3y = Float.parseFloat(text8);

            

            String text9 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX3z here");

            float RX3z = Float.parseFloat(text9);

            

            String text10 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX4x here");

            float RX4x = Float.parseFloat(text10);

            

            String text11 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX4y here");

            float RX4y = Float.parseFloat(text11);

            

            String text12 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX4z here");

            float RX4z = Float.parseFloat(text12);

            

            String text13 = JOptionPane.showInputDialog(frame,

                        "Please enter the simulated TXx here");

            float TX1x = Float.parseFloat(text13);

            

            String text14 = JOptionPane.showInputDialog(frame,

                        "Please enter the simulated TXy here");

            float TX1y = Float.parseFloat(text14);

            

            String text15 = JOptionPane.showInputDialog(frame,

                        "Please enter the simulated TXz here");

            float TX1z = Float.parseFloat(text15);

            

            String text16 = JOptionPane.showInputDialog(frame,

                        "Please enter the solved TXx here");

            float TX2x = Float.parseFloat(text16);

            

            String text17 = JOptionPane.showInputDialog(frame,

                        "Please enter the solved TXy here");

            float TX2y = Float.parseFloat(text17);

            

            String text18 = JOptionPane.showInputDialog(frame,

                        "Please enter the solved TXz here");

            float TX2z = Float.parseFloat(text18);

               

            

            

            public void database() {

                

                Iteration it = new Iteration();

            

            

            it.setValue(1, "TXx", TX1x);

            it.setValue(1, "TXy", TX1y);

            it.setValue(1, "TXz", TX1z);

            it.setValue(1, "RX1x", RX1x);

            it.setValue(1, "RX1y", RX1y);

            it.setValue(1, "RX1z", RX1z);

            it.setValue(1, "RX2x", RX2x);

            it.setValue(1, "RX2y", RX2y);

            it.setValue(1, "RX2z", RX2z);

            it.setValue(1, "RX3x", RX3x);

            it.setValue(1, "RX3y", RX3y);

            it.setValue(1, "RX3z", RX3z);

            it.setValue(1, "RX4x", RX4x);

            it.setValue(1, "RX4y", RX4y);

            it.setValue(1, "RX4z", RX4z);

            

            it.setValue(2, "TXx", TX2x);

            it.setValue(2, "TXy", TX2y);

            it.setValue(2, "TXz", TX2z);

            

                

            float simd1=(float)Math.sqrt(Math.pow((it.getValue(1, "TX1x")-it.getValue(1,"RX1x")),2)+Math.pow((it.getValue(1, "TX1y")-it.getValue(1,"RX1y")),2)+Math.pow((it.getValue(1, "TX1z")-it.getValue(1,"RX1z")),2));

            it.setValue(2, "RX1x", simd1);

            float simd2=(float)Math.sqrt(Math.pow((it.getValue(1, "TX1x")-it.getValue(1,"RX2x")),2)+Math.pow((it.getValue(1, "TX1y")-it.getValue(1,"RX2y")),2)+Math.pow((it.getValue(1, "TX1z")-it.getValue(1,"RX2z")),2));

            it.setValue(2, "RX2x", simd2);

            float simd3=(float)Math.sqrt(Math.pow((it.getValue(1, "TX1x")-it.getValue(1,"RX3x")),2)+Math.pow((it.getValue(1, "TX1y")-it.getValue(1,"RX3y")),2)+Math.pow((it.getValue(1, "TX1z")-it.getValue(1,"RX3z")),2));

            it.setValue(2, "RX3x", simd3);

            float simd4=(float)Math.sqrt(Math.pow((it.getValue(1, "TX1x")-it.getValue(1,"RX4x")),2)+Math.pow((it.getValue(1, "TX1y")-it.getValue(1,"RX4y")),2)+Math.pow((it.getValue(1, "TX1z")-it.getValue(1,"RX4z")),2));

            it.setValue(2, "RX4x", simd4);

            float toa1=(float)(simd1/0.3);

            it.setValue(3, "RX1x", toa1);

            float toa2=(float)(simd2/0.3);

            it.setValue(3, "RX2x", toa2);

            float toa3=(float)(simd3/0.3);

            it.setValue(3, "RX3x", toa3);

            float toa4=(float)(simd4/0.3);

            it.setValue(3, "RX4x", toa4);

            

            

            float simDTOA1=it.getValue(3, "RX1x")-this.lowValues(it.getValue(3, "RX1x"), it.getValue(3, "RX2x"), it.getValue(3, "RX3x"), it.getValue(3, "RX4x"));

            it.setValue(3, "DTOA1", simDTOA1);

            float simDTOA2=it.getValue(3, "RX2x")-this.lowValues(it.getValue(3, "RX1x"), it.getValue(3, "RX2x"), it.getValue(3, "RX3x"), it.getValue(3, "RX4x"));

            it.setValue(3, "DTOA2", simDTOA2);

            float simDTOA3=it.getValue(3, "RX3x")-this.lowValues(it.getValue(3, "RX1x"), it.getValue(3, "RX2x"), it.getValue(3, "RX3x"), it.getValue(3, "RX4x"));

            it.setValue(3, "DTOA3", simDTOA3);

            float simDTOA4=it.getValue(3, "RX4x")-this.lowValues(it.getValue(3, "RX1x"), it.getValue(3, "RX2x"), it.getValue(3, "RX3x"), it.getValue(3, "RX4x"));

            it.setValue(3, "DTOA4", simDTOA4);

            

                    

            

            float sold1=(float)Math.sqrt(Math.pow((it.getValue(1, "TX2x")-it.getValue(1, "RX1x")),2)+Math.pow((it.getValue(1, "TX2y")-it.getValue(1,"RX1y")),2)+Math.pow((it.getValue(1, "TX2z")-it.getValue(1,"RX1z")),2));

            it.setValue(4, "RX1x", sold1);

            float sold2=(float)Math.sqrt(Math.pow((it.getValue(1, "TX2x")-it.getValue(1, "RX2x")),2)+Math.pow((it.getValue(1, "TX2y")-it.getValue(1,"RX2y")),2)+Math.pow((it.getValue(1, "TX2z")-it.getValue(1,"RX2z")),2));

            it.setValue(4, "RX2x", sold2);

            float sold3=(float)Math.sqrt(Math.pow((it.getValue(1, "TX2x")-it.getValue(1, "RX3x")),2)+Math.pow((it.getValue(1, "TX2y")-it.getValue(1,"RX3y")),2)+Math.pow((it.getValue(1, "TX2z")-it.getValue(1,"RX3z")),2));

            it.setValue(4, "RX3x", sold3);

            float sold4=(float)Math.sqrt(Math.pow((it.getValue(1, "TX2x")-it.getValue(1, "RX4x")),2)+Math.pow((it.getValue(1, "TX2y")-it.getValue(1,"RX4y")),2)+Math.pow((it.getValue(1, "TX2z")-it.getValue(1,"RX4z")),2));

            it.setValue(4, "RX4x", sold4);

            

            float toa5=(float)(sold1/0.3);

            it.setValue(5, "RX1x", toa5);

            float toa6=(float)(sold2/0.3);

            it.setValue(5, "RX2x", toa6);

            float toa7=(float)(sold3/0.3);

            it.setValue(5, "RX3x", toa7);

            float toa8=(float)(sold4/0.3);

            it.setValue(5, "RX4x", toa8);

            

            float solDTOA1=it.getValue(5, "RX1x")-this.lowValues(it.getValue(5, "RX1x"), it.getValue(5, "RX2x"), it.getValue(5, "RX3x"), it.getValue(5, "RX4x"));

            it.setValue(5, "DTOA1", solDTOA1);

            float solDTOA2=it.getValue(5, "RX2x")-this.lowValues(it.getValue(5, "RX1x"), it.getValue(5, "RX2x"), it.getValue(5, "RX3x"), it.getValue(5, "RX4x"));

            it.setValue(5, "DTOA2", solDTOA2);

            float solDTOA3=it.getValue(5, "RX3x")-this.lowValues(it.getValue(5, "RX1x"), it.getValue(5, "RX2x"), it.getValue(5, "RX3x"), it.getValue(5, "RX4x"));

            it.setValue(5, "DTOA3", solDTOA3);

            float solDTOA4=it.getValue(3, "RX4x")-this.lowValues(it.getValue(5, "RX1x"), it.getValue(5, "RX2x"), it.getValue(5, "RX3x"), it.getValue(5, "RX4x"));

            it.setValue(5, "DTOA4", simDTOA4);

            

            float error1=(float)Math.pow((it.getValue(3, "DTOA1")-it.getValue(5, "DTOA1")),2);

            it.setValue(6,"DTOA1",error1);

            float error2=(float)Math.pow((it.getValue(3, "DTOA2")-it.getValue(5, "DTOA2")),2);

            it.setValue(6,"DTOA1",error2);

            float error3=(float)Math.pow((it.getValue(3, "DTOA3")-it.getValue(5, "DTOA3")),2);

            it.setValue(6,"DTOA1",error3);

            float error4=(float)Math.pow((it.getValue(3, "DTOA4")-it.getValue(5, "DTOA4")),2);

            it.setValue(6,"DTOA1",error4);

            

            float totError=it.getValue(6,"DTOA1")+it.getValue(6,"DTOA2")+it.getValue(6,"DTOA3")+it.getValue(6,"DTOA4");

            it.setValue(7,"DTOA1",totError);

            

            }

                 

            public float lowValues(float a,float b,float c,float d) {

               

            float num;

            num = (float)Math.min(a, Math.min(b,Math.min(c,(d))));

            return num;    

            }

            

            

            

            
 

}
 

package DataCalculations;
 

/*

 * To change this template, choose Tools | Templates

 * and open the template in the editor.

 */
 

/**

 *

 * @author Justin

 */
 

import java.sql.*;
 

public class Iteration {

    

    final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

      final String DATABASE_URL = "jdbc:mysql://localhost:3306/iteration";

      Connection connection = null; // manages connection

      

      public void setValue(int a, String column, float value) {

      try {

                  Class.forName(JDBC_DRIVER); // load database driver class

 

                  // establish connection to database

                 connection = DriverManager.getConnection(DATABASE_URL, "root", "");

                   // create Statement for querying database

                  PreparedStatement statement = connection.prepareStatement("UPDATE iteration SET ?=? from WHERE num = ?");

                  statement.setObject(1, column);

                  statement.setFloat(2, value);

                  statement.setInt(3, a);

                  ResultSet rs1 = statement.executeQuery();

                  

            } // end try

            catch (SQLException sqlException) {

                  sqlException.printStackTrace();

                  System.exit(1);

            } // end catch

 

            catch (ClassNotFoundException classNotFound) {

                  classNotFound.printStackTrace();

                  System.exit(1);

            } // end catch

            finally // ensure statement and connection are closed properly

            {

                  try {

 

                        connection.close();

                  } // end try

                  catch (Exception exception) {

                        exception.printStackTrace();

                        System.exit(1);

                  } // end catch

            } // end finally

       

  } 

      public float getValue(int a, String column) {

      try {

                  Class.forName(JDBC_DRIVER); // load database driver class

                  float value=0;

                  // establish connection to database

                 connection = DriverManager.getConnection(DATABASE_URL, "root", "");

                   // create Statement for querying database

                  PreparedStatement statement = connection.prepareStatement("SELECT ? FROM iteration where num=?");

                  statement.setObject(1, column);

                  statement.setObject(2, a);

                  ResultSet rs1 = statement.executeQuery();

                  if (rs1.next()) value = rs1.getFloat(column);

                  return value;

                  

            } // end try

            catch (SQLException sqlException) {

                  sqlException.printStackTrace();

                  System.exit(1);

            } // end catch

 

            catch (ClassNotFoundException classNotFound) {

                  classNotFound.printStackTrace();

                  System.exit(1);

            } // end catch

            finally // ensure statement and connection are closed properly

            {

                  try {

 

                        connection.close();

                  } // end try

                  catch (Exception exception) {

                        exception.printStackTrace();

                        System.exit(1);

                  } // end catch

            } // end finally

            return -1;

       

  }

      

      

 

 

 

}// end class

Open in new window

database-screenshot.png
example.xls
0
Comment
Question by:jtiernan2008
  • 13
  • 12
  • 4
  • +1
32 Comments
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 100 total points
ID: 24100360
Your SQL is wrong at line 236. You can't parameterise a column *name* and you have an erroneous 'from' there. It's also executeUpdate. not executeQuery
0
 
LVL 1

Author Comment

by:jtiernan2008
ID: 24100677
I get a "incompatible types" compile error when I change the line to

ResultSet rs1 = statement.executeUpdate();

Also how can I execute the SQL statement if I can't paramaterise the column name... Is there another way to accomplish this?
0
 
LVL 20

Assisted Solution

by:ChristoferDutz
ChristoferDutz earned 200 total points
ID: 24100890
As far as I understood this, JDBC offers no way of parameterize the column- or table-names.
Unfortunately I ran into this problem now and then.
You could avoid this problem by using this code instead:

PreparedStatement statement = connection.prepareStatement("UPDATE iteration SET " + column + "=? WHERE num = ?");

Regarding the other two problems, I have to confirm CEHJ.
0
 
LVL 1

Author Comment

by:jtiernan2008
ID: 24101026
Thanks ChristoferDutz, I have changed that now but I am unsure if it is working as I still have the compile error from 'ResultSet rs1 = statement.executeUpdate();'

it expects a java.sql.ResultSet and gets an int?
0
 
LVL 20

Expert Comment

by:ChristoferDutz
ID: 24101051
An executeUpdate returns the number of rows affected. So you should change your code to:

int updateCount = statement.executeUpdate();

or leave away the return-stuff

statement.executeUpdate();



0
 
LVL 1

Author Comment

by:jtiernan2008
ID: 24101184
thanks a million lads, you are all right and everything compiles and runs again however the database still does not update :(

please find the updated code below;
package DataCalculations;
 

/*

 * To change this template, choose Tools | Templates

 * and open the template in the editor.

 */
 

/**

 *

 * @author Justin

 */
 

import java.sql.*;
 

public class Iteration {

    

    final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

      final String DATABASE_URL = "jdbc:mysql://localhost:3306/iteration";

      Connection connection = null; // manages connection

      

      public void setValue(int a, String column, float value) {

      try {

                  Class.forName(JDBC_DRIVER); // load database driver class

 

                  // establish connection to database

                 connection = DriverManager.getConnection(DATABASE_URL, "root", "");

                   // create Statement for querying database

                  PreparedStatement statement = connection.prepareStatement("UPDATE iteration SET " + column + " =? WHERE num = ?");

                  statement.setFloat(1, value);

                  statement.setInt(2, a);

                  statement.executeUpdate();

                  

            } // end try

            catch (SQLException sqlException) {

                  sqlException.printStackTrace();

                  System.exit(1);

            } // end catch

 

            catch (ClassNotFoundException classNotFound) {

                  classNotFound.printStackTrace();

                  System.exit(1);

            } // end catch

            finally // ensure statement and connection are closed properly

            {

                  try {

 

                        connection.close();

                  } // end try

                  catch (Exception exception) {

                        exception.printStackTrace();

                        System.exit(1);

                  } // end catch

            } // end finally

       

  } 

      public float getValue(int a, String column) {

      try {

                  Class.forName(JDBC_DRIVER); // load database driver class

                  float value=0;

                  // establish connection to database

                 connection = DriverManager.getConnection(DATABASE_URL, "root", "");

                   // create Statement for querying database

                  PreparedStatement statement = connection.prepareStatement("SELECT "+ column + " FROM iteration WHERE num = ?");

                  statement.setObject(1, a);

                  ResultSet rs1 = statement.executeQuery();

                  if (rs1.next()) value = rs1.getFloat(column);

                  return value;

                  

            } // end try

            catch (SQLException sqlException) {

                  sqlException.printStackTrace();

                  System.exit(1);

            } // end catch

 

            catch (ClassNotFoundException classNotFound) {

                  classNotFound.printStackTrace();

                  System.exit(1);

            } // end catch

            finally // ensure statement and connection are closed properly

            {

                  try {

 

                        connection.close();

                  } // end try

                  catch (Exception exception) {

                        exception.printStackTrace();

                        System.exit(1);

                  } // end catch

            } // end finally

            return -1;

       

  }

      

      

 

 

 

}// end class

      

      

Open in new window

0
 
LVL 20

Expert Comment

by:ChristoferDutz
ID: 24101206
Well if you post the exact Exception it would be a lot easier ;-)
0
 
LVL 1

Author Comment

by:jtiernan2008
ID: 24101272
As I said above "you are all right and everything compiles and runs " therefore there is no exception.
The problem is that when I run the program, the mysql database does not update after I enter the values as per the screenshot
0
 
LVL 20

Assisted Solution

by:ChristoferDutz
ChristoferDutz earned 200 total points
ID: 24101335
Then I would insert this debug code:

int numUpdated = statement.executeUpdate();
System.out.println("num:" + num + " set " + column  + "=" +  a);
System.out.println(numUpdated);

And have a look at how many rows are affected and if your input parameters are as expected. If the number of updates id 0, then possibly the where clause is simply not selecting any rows.

By the way, I would change the
              statement.setObject(1, a);
Of your select code to:
                 statement.setInt(1, a);

0
 
LVL 1

Author Comment

by:jtiernan2008
ID: 24101537
Thanks for your response.

thats strange... I done as you said only it will not update but also those print calls did not show up on the console screen?
0
 
LVL 20

Expert Comment

by:ChristoferDutz
ID: 24101780
Then certainly your code is not executed.

Well I'd debug through the code. Currently you are not catching any RuntimeExceptions so they would not output any debug information.
Normaly I'd expect your programm to crash, but mabe you are gobbling up Exceptions some place else.

Try adding a:
catch(Exception e) {
                  e.printStackTrace();
                  System.exit(1);
}
After the ClassNotFoundException.

I'll bet you are having some kind of NullPointerExceptions or something similar.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24101831
Please post your current code
0
 
LVL 1

Author Comment

by:jtiernan2008
ID: 24101987
entered in the code as requested... no change
program compiles runs but database is not updated also the debug print function calls do not appear at the console screen

updated code in the code box as requested
package DataCalculations;
 

/*

 * To change this template, choose Tools | Templates

 * and open the template in the editor.

 */
 

/**

 *

 * @author Justin

 */
 

import java.sql.*;
 

public class Iteration {

    

    final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

      final String DATABASE_URL = "jdbc:mysql://localhost:3306/iteration";

      Connection connection = null; // manages connection

      

      public void setValue(int a, String column, float value) {

      try {

                  Class.forName(JDBC_DRIVER); // load database driver class

 

                  // establish connection to database

                 connection = DriverManager.getConnection(DATABASE_URL, "root", "");

                   // create Statement for querying database

                  PreparedStatement statement = connection.prepareStatement("UPDATE iteration SET " + column + " =? WHERE num = ?");

                  statement.setFloat(1, value);

                  statement.setInt(2, a);

                  int numUpdated = statement.executeUpdate();

                  System.out.println("num:" + a + " set " + column  + "=" +  a);

                  System.out.println(numUpdated);
 

                  

            } // end try

            catch (SQLException sqlException) {

                  sqlException.printStackTrace();

                  System.exit(1);

            } // end catch

 

            catch (ClassNotFoundException classNotFound) {

                  classNotFound.printStackTrace();

                  System.exit(1);

            } // end catch

      

            catch(Exception e) {

                  e.printStackTrace();

                  System.exit(1);

}

            finally // ensure statement and connection are closed properly

            {

                  try {

 

                        connection.close();

                  } // end try

                  catch (Exception exception) {

                        exception.printStackTrace();

                        System.exit(1);

                  } // end catch

            } // end finally

       

  } 

      public float getValue(int a, String column) {

      try {

                  Class.forName(JDBC_DRIVER); // load database driver class

                  float value=0;

                  // establish connection to database

                 connection = DriverManager.getConnection(DATABASE_URL, "root", "");

                   // create Statement for querying database

                  PreparedStatement statement = connection.prepareStatement("SELECT "+ column + " FROM iteration WHERE num = ?");

                  statement.setInt(1, a);

                  ResultSet rs1 = statement.executeQuery();

                  if (rs1.next()) value = rs1.getFloat(column);

                  return value;

                  

            } // end try

            catch (SQLException sqlException) {

                  sqlException.printStackTrace();

                  System.exit(1);

            } // end catch

 

            catch (ClassNotFoundException classNotFound) {

                  classNotFound.printStackTrace();

                  System.exit(1);

            } // end catch

            finally // ensure statement and connection are closed properly

            {

                  try {

 

                        connection.close();

                  } // end try

                  catch (Exception exception) {

                        exception.printStackTrace();

                        System.exit(1);

                  } // end catch

            } // end finally

            return -1;

       

  }

      

      

 

 

 

}// end class

      

      

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24102018
Try closing the Statement before the connection
0
 
LVL 20

Expert Comment

by:ChristoferDutz
ID: 24102042
Argl. Yeah ... missed that too ...
What's with your commits? If you close the connection before the statement, it might not have the opportunity to commit it's changes.
0
 
LVL 1

Author Comment

by:jtiernan2008
ID: 24102137
but the connection is closed after the statement on line 55?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 86

Expert Comment

by:CEHJ
ID: 24102237
>>but the connection is closed after the statement on line 55?

Close the Statement on line 54
0
 
LVL 20

Expert Comment

by:ChristoferDutz
ID: 24102245
Well You have done your update.
Normally you have AutoCommit sessions, in which the JDBC Driver commits your changes after finisching the update.
Normally you have to close your DB resources in this order:

ResultSets (if there are any)
Statement
Connections

I guess the driver hasn't done it's commit yet. When closing the statement, it would see that it has to commit and do so. By closing the connection first, you give the statmeent no chance of commiting.
0
 
LVL 20

Expert Comment

by:ChristoferDutz
ID: 24102337
If you try the following, it should work.

If not add a

connection.commit();

directly after your executeUpdate();
package DataCalculations;

 

/*

 * To change this template, choose Tools | Templates

 * and open the template in the editor.

 */

 

/**

 *

 * @author Justin

 */

 

import java.sql.*;

 

public class Iteration {

    

    final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

      final String DATABASE_URL = "jdbc:mysql://localhost:3306/iteration";

      Connection connection = null; // manages connection

      

      public void setValue(int a, String column, float value) {

          PreparedStatement statement = null;

          try {

                  Class.forName(JDBC_DRIVER); // load database driver class

 

                  // establish connection to database

                  connection = DriverManager.getConnection(DATABASE_URL, "root", "");

                  // create Statement for querying database

                  statement = connection.prepareStatement("UPDATE iteration SET " + column + " =? WHERE num = ?");

                  statement.setFloat(1, value);

                  statement.setInt(2, a);

                  int numUpdated = statement.executeUpdate();

                  System.out.println("num:" + a + " set " + column  + "=" +  a);

                  System.out.println(numUpdated);

            } // end try

            catch (SQLException sqlException) {

                  sqlException.printStackTrace();

                  System.exit(1);

            } // end catch

 

            catch (ClassNotFoundException classNotFound) {

                  classNotFound.printStackTrace();

                  System.exit(1);

            } // end catch

      

            catch(Exception e) {

                  e.printStackTrace();

                  System.exit(1);

}

            finally // ensure statement and connection are closed properly

            {

                  try {

                        statement.close();

                  } // end try

                  catch (Exception exception) {

                        exception.printStackTrace();

                        System.exit(1);

                  } // end catch

                  try {

                        connection.close();

                  } // end try

                  catch (Exception exception) {

                        exception.printStackTrace();

                        System.exit(1);

                  } // end catch

            } // end finally

       

  } 

      public float getValue(int a, String column) {

          PreparedStatement statement = null;

          ResultSet rs1 = null;

          try {

                  Class.forName(JDBC_DRIVER); // load database driver class

                  float value=0;

                  // establish connection to database

                 connection = DriverManager.getConnection(DATABASE_URL, "root", "");

                   // create Statement for querying database

                  statement = connection.prepareStatement("SELECT "+ column + " FROM iteration WHERE num = ?");

                  statement.setInt(1, a);

                  rs1 = statement.executeQuery();

                  if (rs1.next()) value = rs1.getFloat(column);

                  return value;

                  

            } // end try

            catch (SQLException sqlException) {

                  sqlException.printStackTrace();

                  System.exit(1);

            } // end catch

 

            catch (ClassNotFoundException classNotFound) {

                  classNotFound.printStackTrace();

                  System.exit(1);

            } // end catch

            finally // ensure statement and connection are closed properly

            {

                  try {

                        rs1.close();

                  } // end try

                  catch (Exception exception) {

                        exception.printStackTrace();

                        System.exit(1);

                  } // end catch

                  try {

                        statement.close();

                  } // end try

                  catch (Exception exception) {

                        exception.printStackTrace();

                        System.exit(1);

                  } // end catch

                  try {

                        connection.close();

                  } // end try

                  catch (Exception exception) {

                        exception.printStackTrace();

                        System.exit(1);

                  } // end catch

                  try {

                        connection.close();

                  } // end try

                  catch (Exception exception) {

                        exception.printStackTrace();

                        System.exit(1);

                  } // end catch

            } // end finally

            return -1;

       

  }

      

      

 

 

 

}// end class

Open in new window

0
 
LVL 1

Author Comment

by:jtiernan2008
ID: 24102369
I deleted it fully but the issue persists
please see below
package DataCalculations;
 

/*

 * To change this template, choose Tools | Templates

 * and open the template in the editor.

 */
 

/**

 *

 * @author Justin

 */
 

import java.sql.*;
 

public class Iteration {

    

    final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

      final String DATABASE_URL = "jdbc:mysql://localhost:3306/iteration";

      Connection connection = null; // manages connection

      

      public void setValue(int a, String column, float value) {

      try {

                  Class.forName(JDBC_DRIVER); // load database driver class

 

                  // establish connection to database

                 connection = DriverManager.getConnection(DATABASE_URL, "root", "");

                   // create Statement for querying database

                  PreparedStatement statement = connection.prepareStatement("UPDATE iteration SET " + column + " =? WHERE num = ?");

                  statement.setFloat(1, value);

                  statement.setInt(2, a);

                  int numUpdated = statement.executeUpdate();

                  System.out.println("num:" + a + " set " + column  + "=" +  a);

                  System.out.println(numUpdated);
 

                  

            } // end try

            catch (SQLException sqlException) {

                  sqlException.printStackTrace();

                  System.exit(1);

            } // end catch

 

            catch (ClassNotFoundException classNotFound) {

                  classNotFound.printStackTrace();

                  System.exit(1);

            } // end catch

      

            catch(Exception e) {

                  e.printStackTrace();

                  System.exit(1);

}

           

       

  } 

      public float getValue(int a, String column) {

      try {

                  Class.forName(JDBC_DRIVER); // load database driver class

                  float value=0;

                  // establish connection to database

                 connection = DriverManager.getConnection(DATABASE_URL, "root", "");

                   // create Statement for querying database

                  PreparedStatement statement = connection.prepareStatement("SELECT "+ column + " FROM iteration WHERE num = ?");

                  statement.setInt(1, a);

                  ResultSet rs1 = statement.executeQuery();

                  if (rs1.next()) value = rs1.getFloat(column);

                  return value;

                  

            } // end try

            catch (SQLException sqlException) {

                  sqlException.printStackTrace();

                  System.exit(1);

            } // end catch

 

            catch (ClassNotFoundException classNotFound) {

                  classNotFound.printStackTrace();

                  System.exit(1);

            } // end catch

            finally // ensure statement and connection are closed properly

            {

                  try {

 

                        connection.close();

                  } // end try

                  catch (Exception exception) {

                        exception.printStackTrace();

                        System.exit(1);

                  } // end catch

            } // end finally

            return -1;

       

  }

      

      

 

 

 

}// end class

      

      

Open in new window

0
 
LVL 20

Expert Comment

by:ChristoferDutz
ID: 24102412
No, you are going the wrong way ... you have to close the connection and the statement ... otherwise the handle will get "lost in time and space" and the DB will end up rolling back anyway. It is not the solution to do nothing, you have avtively commit and propperly close the statement and connection.
0
 
LVL 1

Author Comment

by:jtiernan2008
ID: 24102417
sorry I just seen your entry above ChristoferDutz, thank you for updating the code, I appreciate that however it didnt work though :(
0
 
LVL 20

Expert Comment

by:ChristoferDutz
ID: 24102448
Argl ;-)

But I have to admit, It would have confused me a little as it would not explain the missing log output. Nevertheless .. you have to propperly clean up your statements and connections and you have to do it in the right way.

Have you tried debugging your application? If you can't I'd suggest some sort of desperate measure and add one
System.out.println(1)
[one line of original code]
System.out.println(2)
[one line of original code]
System.out.println(3)
[one line of original code]
...

and find out where the thread breaks out.
The last number you see, schould be the last line of code being executed correctly.
0
 
LVL 20

Expert Comment

by:ChristoferDutz
ID: 24102466
But please ... don't tell Experts-Exchange to put this solution in the Knowledge Base if we manage to solve it ;-)
0
 
LVL 92

Expert Comment

by:objects
ID: 24102583
how are you calling it?

0
 
LVL 1

Author Comment

by:jtiernan2008
ID: 24102616
Hi Objects,

DataCalculations is invoked from a GUI event handler.
This is just the same as placing it in a main method.
0
 
LVL 92

Expert Comment

by:objects
ID: 24102635
yes, but what call(s) do you make?
Add some debug to check that you are actiually calling it

0
 
LVL 1

Author Comment

by:jtiernan2008
ID: 24102713
Please find the two java programs, CordCalc and Iteration below;

CordCalc does the calculations and calls Iteration under two methods setValue and getValue to update the database as necessary
package DataCalculations;
 

/*

 * To change this template, choose Tools | Templates

 * and open the template in the editor.

 */
 

/**

 *

 * @author Justin

 */
 

import java.sql.*;

import javax.swing.JFrame;

import javax.swing.JOptionPane;

import java.lang.Math;
 

public class CordCalc {

    

    final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

    final String DATABASE_URL = "jdbc:mysql://localhost:3306/rfiddata";

    Connection connection = null; // manages connection

      

     

            JFrame frame = new JFrame();

            String text1 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX1x here");

            float RX1x = Float.parseFloat(text1);

            

            String text2 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX1y here");

            float RX1y = Float.parseFloat(text2);

            

            String text3 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX1z here");

            float RX1z = Float.parseFloat(text3);

            

            String text4 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX2x here");

            float RX2x = Float.parseFloat(text4);

            

            String text5 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX2y here");

            float RX2y = Float.parseFloat(text5);

            

            String text6 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX2z here");

            float RX2z = Float.parseFloat(text6);

            

            String text7 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX3x here");

            float RX3x = Float.parseFloat(text7);

            

            String text8 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX3y here");

            float RX3y = Float.parseFloat(text8);

            

            String text9 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX3z here");

            float RX3z = Float.parseFloat(text9);

            

            String text10 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX4x here");

            float RX4x = Float.parseFloat(text10);

            

            String text11 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX4y here");

            float RX4y = Float.parseFloat(text11);

            

            String text12 = JOptionPane.showInputDialog(frame,

                        "Please enter the RX4z here");

            float RX4z = Float.parseFloat(text12);

            

            String text13 = JOptionPane.showInputDialog(frame,

                        "Please enter the simulated TXx here");

            float TX1x = Float.parseFloat(text13);

            

            String text14 = JOptionPane.showInputDialog(frame,

                        "Please enter the simulated TXy here");

            float TX1y = Float.parseFloat(text14);

            

            String text15 = JOptionPane.showInputDialog(frame,

                        "Please enter the simulated TXz here");

            float TX1z = Float.parseFloat(text15);

            

            String text16 = JOptionPane.showInputDialog(frame,

                        "Please enter the solved TXx here");

            float TX2x = Float.parseFloat(text16);

            

            String text17 = JOptionPane.showInputDialog(frame,

                        "Please enter the solved TXy here");

            float TX2y = Float.parseFloat(text17);

            

            String text18 = JOptionPane.showInputDialog(frame,

                        "Please enter the solved TXz here");

            float TX2z = Float.parseFloat(text18);

               

            

            

            public void database() {

                

                Iteration it = new Iteration();

            

            

            it.setValue(1, "TXx", TX1x);

            it.setValue(1, "TXy", TX1y);

            it.setValue(1, "TXz", TX1z);

            it.setValue(1, "RX1x", RX1x);

            it.setValue(1, "RX1y", RX1y);

            it.setValue(1, "RX1z", RX1z);

            it.setValue(1, "RX2x", RX2x);

            it.setValue(1, "RX2y", RX2y);

            it.setValue(1, "RX2z", RX2z);

            it.setValue(1, "RX3x", RX3x);

            it.setValue(1, "RX3y", RX3y);

            it.setValue(1, "RX3z", RX3z);

            it.setValue(1, "RX4x", RX4x);

            it.setValue(1, "RX4y", RX4y);

            it.setValue(1, "RX4z", RX4z);

            

            it.setValue(2, "TXx", TX2x);

            it.setValue(2, "TXy", TX2y);

            it.setValue(2, "TXz", TX2z);

            

                

            float simd1=(float)Math.sqrt(Math.pow((it.getValue(1, "TX1x")-it.getValue(1,"RX1x")),2)+Math.pow((it.getValue(1, "TX1y")-it.getValue(1,"RX1y")),2)+Math.pow((it.getValue(1, "TX1z")-it.getValue(1,"RX1z")),2));

            it.setValue(2, "RX1x", simd1);

            float simd2=(float)Math.sqrt(Math.pow((it.getValue(1, "TX1x")-it.getValue(1,"RX2x")),2)+Math.pow((it.getValue(1, "TX1y")-it.getValue(1,"RX2y")),2)+Math.pow((it.getValue(1, "TX1z")-it.getValue(1,"RX2z")),2));

            it.setValue(2, "RX2x", simd2);

            float simd3=(float)Math.sqrt(Math.pow((it.getValue(1, "TX1x")-it.getValue(1,"RX3x")),2)+Math.pow((it.getValue(1, "TX1y")-it.getValue(1,"RX3y")),2)+Math.pow((it.getValue(1, "TX1z")-it.getValue(1,"RX3z")),2));

            it.setValue(2, "RX3x", simd3);

            float simd4=(float)Math.sqrt(Math.pow((it.getValue(1, "TX1x")-it.getValue(1,"RX4x")),2)+Math.pow((it.getValue(1, "TX1y")-it.getValue(1,"RX4y")),2)+Math.pow((it.getValue(1, "TX1z")-it.getValue(1,"RX4z")),2));

            it.setValue(2, "RX4x", simd4);

            float toa1=(float)(simd1/0.3);

            it.setValue(3, "RX1x", toa1);

            float toa2=(float)(simd2/0.3);

            it.setValue(3, "RX2x", toa2);

            float toa3=(float)(simd3/0.3);

            it.setValue(3, "RX3x", toa3);

            float toa4=(float)(simd4/0.3);

            it.setValue(3, "RX4x", toa4);

            

            

            float simDTOA1=it.getValue(3, "RX1x")-this.lowValues(it.getValue(3, "RX1x"), it.getValue(3, "RX2x"), it.getValue(3, "RX3x"), it.getValue(3, "RX4x"));

            it.setValue(3, "DTOA1", simDTOA1);

            float simDTOA2=it.getValue(3, "RX2x")-this.lowValues(it.getValue(3, "RX1x"), it.getValue(3, "RX2x"), it.getValue(3, "RX3x"), it.getValue(3, "RX4x"));

            it.setValue(3, "DTOA2", simDTOA2);

            float simDTOA3=it.getValue(3, "RX3x")-this.lowValues(it.getValue(3, "RX1x"), it.getValue(3, "RX2x"), it.getValue(3, "RX3x"), it.getValue(3, "RX4x"));

            it.setValue(3, "DTOA3", simDTOA3);

            float simDTOA4=it.getValue(3, "RX4x")-this.lowValues(it.getValue(3, "RX1x"), it.getValue(3, "RX2x"), it.getValue(3, "RX3x"), it.getValue(3, "RX4x"));

            it.setValue(3, "DTOA4", simDTOA4);

            

                    

            

            float sold1=(float)Math.sqrt(Math.pow((it.getValue(1, "TX2x")-it.getValue(1, "RX1x")),2)+Math.pow((it.getValue(1, "TX2y")-it.getValue(1,"RX1y")),2)+Math.pow((it.getValue(1, "TX2z")-it.getValue(1,"RX1z")),2));

            it.setValue(4, "RX1x", sold1);

            float sold2=(float)Math.sqrt(Math.pow((it.getValue(1, "TX2x")-it.getValue(1, "RX2x")),2)+Math.pow((it.getValue(1, "TX2y")-it.getValue(1,"RX2y")),2)+Math.pow((it.getValue(1, "TX2z")-it.getValue(1,"RX2z")),2));

            it.setValue(4, "RX2x", sold2);

            float sold3=(float)Math.sqrt(Math.pow((it.getValue(1, "TX2x")-it.getValue(1, "RX3x")),2)+Math.pow((it.getValue(1, "TX2y")-it.getValue(1,"RX3y")),2)+Math.pow((it.getValue(1, "TX2z")-it.getValue(1,"RX3z")),2));

            it.setValue(4, "RX3x", sold3);

            float sold4=(float)Math.sqrt(Math.pow((it.getValue(1, "TX2x")-it.getValue(1, "RX4x")),2)+Math.pow((it.getValue(1, "TX2y")-it.getValue(1,"RX4y")),2)+Math.pow((it.getValue(1, "TX2z")-it.getValue(1,"RX4z")),2));

            it.setValue(4, "RX4x", sold4);

            

            float toa5=(float)(sold1/0.3);

            it.setValue(5, "RX1x", toa5);

            float toa6=(float)(sold2/0.3);

            it.setValue(5, "RX2x", toa6);

            float toa7=(float)(sold3/0.3);

            it.setValue(5, "RX3x", toa7);

            float toa8=(float)(sold4/0.3);

            it.setValue(5, "RX4x", toa8);

            

            float solDTOA1=it.getValue(5, "RX1x")-this.lowValues(it.getValue(5, "RX1x"), it.getValue(5, "RX2x"), it.getValue(5, "RX3x"), it.getValue(5, "RX4x"));

            it.setValue(5, "DTOA1", solDTOA1);

            float solDTOA2=it.getValue(5, "RX2x")-this.lowValues(it.getValue(5, "RX1x"), it.getValue(5, "RX2x"), it.getValue(5, "RX3x"), it.getValue(5, "RX4x"));

            it.setValue(5, "DTOA2", solDTOA2);

            float solDTOA3=it.getValue(5, "RX3x")-this.lowValues(it.getValue(5, "RX1x"), it.getValue(5, "RX2x"), it.getValue(5, "RX3x"), it.getValue(5, "RX4x"));

            it.setValue(5, "DTOA3", solDTOA3);

            float solDTOA4=it.getValue(3, "RX4x")-this.lowValues(it.getValue(5, "RX1x"), it.getValue(5, "RX2x"), it.getValue(5, "RX3x"), it.getValue(5, "RX4x"));

            it.setValue(5, "DTOA4", simDTOA4);

            

            float error1=(float)Math.pow((it.getValue(3, "DTOA1")-it.getValue(5, "DTOA1")),2);

            it.setValue(6,"DTOA1",error1);

            float error2=(float)Math.pow((it.getValue(3, "DTOA2")-it.getValue(5, "DTOA2")),2);

            it.setValue(6,"DTOA1",error2);

            float error3=(float)Math.pow((it.getValue(3, "DTOA3")-it.getValue(5, "DTOA3")),2);

            it.setValue(6,"DTOA1",error3);

            float error4=(float)Math.pow((it.getValue(3, "DTOA4")-it.getValue(5, "DTOA4")),2);

            it.setValue(6,"DTOA1",error4);

            

            float totError=it.getValue(6,"DTOA1")+it.getValue(6,"DTOA2")+it.getValue(6,"DTOA3")+it.getValue(6,"DTOA4");

            it.setValue(7,"DTOA1",totError);

            

            }

                 

            public float lowValues(float a,float b,float c,float d) {

               

            float num;

            num = (float)Math.min(a, Math.min(b,Math.min(c,(d))));

            return num;    

            }

            

            

            

            
 

}
 
 

package DataCalculations;

 

/*

 * To change this template, choose Tools | Templates

 * and open the template in the editor.

 */

 

/**

 *

 * @author Justin

 */

 

import java.sql.*;

 

public class Iteration {

    

    final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

      final String DATABASE_URL = "jdbc:mysql://localhost:3306/iteration";

      Connection connection = null; // manages connection

      

      public void setValue(int a, String column, float value) {

          PreparedStatement statement = null;

          try {

                  Class.forName(JDBC_DRIVER); // load database driver class

 

                  // establish connection to database

                  connection = DriverManager.getConnection(DATABASE_URL, "root", "");

                  // create Statement for querying database

                  statement = connection.prepareStatement("UPDATE iteration SET " + column + " =? WHERE num = ?");

                  statement.setFloat(1, value);

                  statement.setInt(2, a);

                  int numUpdated = statement.executeUpdate();

                  connection.commit();

                  System.out.println("num:" + a + " set " + column  + "=" +  a);

                  System.out.println(numUpdated);

            } // end try

            

            catch (SQLException sqlException) {

                  sqlException.printStackTrace();

                  System.exit(1);

            } // end catch

 

            catch (ClassNotFoundException classNotFound) {

                  classNotFound.printStackTrace();

                  System.exit(1);

}

            finally // ensure statement and connection are closed properly

            {

                 try {

 

                        connection.close();

                  } 

                  catch (Exception exception) {

                        exception.printStackTrace();

                        System.exit(1);

                  } // end catch

                  

            } // end finally

       

  } 

      public float getValue(int a, String column) {

          PreparedStatement statement = null;

          ResultSet rs1 = null;

          try {

                  Class.forName(JDBC_DRIVER); // load database driver class

                  float value=0;

                  // establish connection to database

                 connection = DriverManager.getConnection(DATABASE_URL, "root", "");

                   // create Statement for querying database

                  statement = connection.prepareStatement("SELECT "+ column + " FROM iteration WHERE num = ?");

                  statement.setInt(1, a);

                  rs1 = statement.executeQuery();

                  if (rs1.next()) value = rs1.getFloat(column);

                  return value;

                  

            } // end try

            catch (SQLException sqlException) {

                  sqlException.printStackTrace();

                  System.exit(1);

            } // end catch

 

            catch (ClassNotFoundException classNotFound) {

                  classNotFound.printStackTrace();

                  System.exit(1);

            } // end catch

            finally // ensure statement and connection are closed properly

            {

                   try {

 

                        connection.close();

                  } 

                  catch (Exception exception) {

                        exception.printStackTrace();

                        System.exit(1);

                  } // end catch

            } // end finally

            return -1;

       

  }

}

Open in new window

0
 
LVL 20

Expert Comment

by:ChristoferDutz
ID: 24102768
Well ... I have to quit here now ... it's 1 o'clock in the morgning ... happy Bugtracking :-)
0
 
LVL 92

Accepted Solution

by:
objects earned 200 total points
ID: 24102916
where are you calling the database() method?

add some debug logging to check it gets called

            public void database() {
               System.out.println("database called");
               ...
0
 
LVL 1

Author Comment

by:jtiernan2008
ID: 24102991
thats it!

the method was not called

thanks a million Objects
the figures are not correct but at least I have a connection to the database and I can debug the figures now
0
 
LVL 1

Author Closing Comment

by:jtiernan2008
ID: 31568171
thanks a million lads
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
array6 challenfge 6 64
strCopies  challenge 17 73
Starting to learn JAVA, 7 48
Groovy:unable to resolve class error 2 31
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now