• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

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

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
jtiernan2008
Asked:
jtiernan2008
  • 13
  • 12
  • 4
  • +1
4 Solutions
 
CEHJCommented:
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
 
jtiernan2008Author Commented:
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
 
ChristoferDutzCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jtiernan2008Author Commented:
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
 
ChristoferDutzCommented:
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
 
jtiernan2008Author Commented:
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
 
ChristoferDutzCommented:
Well if you post the exact Exception it would be a lot easier ;-)
0
 
jtiernan2008Author Commented:
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
 
ChristoferDutzCommented:
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
 
jtiernan2008Author Commented:
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
 
ChristoferDutzCommented:
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
 
CEHJCommented:
Please post your current code
0
 
jtiernan2008Author Commented:
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
 
CEHJCommented:
Try closing the Statement before the connection
0
 
ChristoferDutzCommented:
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
 
jtiernan2008Author Commented:
but the connection is closed after the statement on line 55?
0
 
CEHJCommented:
>>but the connection is closed after the statement on line 55?

Close the Statement on line 54
0
 
ChristoferDutzCommented:
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
 
ChristoferDutzCommented:
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
 
jtiernan2008Author Commented:
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
 
ChristoferDutzCommented:
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
 
jtiernan2008Author Commented:
sorry I just seen your entry above ChristoferDutz, thank you for updating the code, I appreciate that however it didnt work though :(
0
 
ChristoferDutzCommented:
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
 
ChristoferDutzCommented:
But please ... don't tell Experts-Exchange to put this solution in the Knowledge Base if we manage to solve it ;-)
0
 
objectsCommented:
how are you calling it?

0
 
jtiernan2008Author Commented:
Hi Objects,

DataCalculations is invoked from a GUI event handler.
This is just the same as placing it in a main method.
0
 
objectsCommented:
yes, but what call(s) do you make?
Add some debug to check that you are actiually calling it

0
 
jtiernan2008Author Commented:
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
 
ChristoferDutzCommented:
Well ... I have to quit here now ... it's 1 o'clock in the morgning ... happy Bugtracking :-)
0
 
objectsCommented:
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
 
jtiernan2008Author Commented:
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
 
jtiernan2008Author Commented:
thanks a million lads
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 13
  • 12
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now