I cannot seem to get the insert statement right somehow, select works fine, but here is the code if anyone can help, oh and i'm using DAO,
JDBC.
note, this is an extract from some of the classes i'm having trouble with, and i'm using mySQL.
Thanks
Jamie
///////////////////code below///////////////
public class Db_Connection {
Connection myConnection;
Rate rateSQL;
}
public Db_Connection()
{
rateSQL = new Rate();
}
public void testsomething() throws SQLException
{
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
}
catch (Exception e)
{
System.out.println("Failed to load MySQL driver");
System.exit(-1);
}
//initial connection to the database
System.out.println("My conn");
myConnection = DriverManager.getConnection(URL, USER, PASSWORD);
insertRate();
closeStatements();
}
public void insertRate() throws SQLException {
RateData rate = new RateData();
//Parameters:- timestamp in format yyyy-mm-dd hh:mm:ss.fffffffff
Timestamp ts = new Timestamp(0);
ts = Timestamp.valueOf("2001-07-06 14:25:29.9") ;
rate.setDate(ts);
rate.setLocalrate(new Integer(3));
rate.setUSDrate(new Integer(2));
rateSQL.insert(myConnection, rate);
}
//from rateSQL class
private PreparedStatement myInsertPreparedStatement;
public void insert(Connection theConnection, RateData theInsertData) throws SQLException
{
SQLException exception = null;
if (myInsertPreparedStatement == null)
{
myInsertPreparedStatement = theConnection.prepareStatement("INSERT INTO `microtech`.`rate` ( `Date`, `Local_rate`, `USD_rate`) VALUES (?, ?, ?)");
}
// Set Date
if (theInsertData.getDate() == null)
{
throw new SQLException("Error
ate has not been set, and cannot be null");
}
myInsertPreparedStatement.setTimestamp(1, theInsertData.getDate());
// Set Local_rate
if (theInsertData.getLocalrate() == null)
{
myInsertPreparedStatement.setNull(2, java.sql.Types.INTEGER);
}
else
{
myInsertPreparedStatement.setInt(2, theInsertData.getLocalrate().intValue());
}
// Set USD_rate
if (theInsertData.getUSDrate() == null)
{
myInsertPreparedStatement.setNull(3, java.sql.Types.INTEGER);
}
else
{
myInsertPreparedStatement.setInt(3, theInsertData.getUSDrate().intValue());
}
myInsertPreparedStatement.executeUpdate();
}