Using MySQL and database and having this stacktrace

Question

I have these two classes in my database package:(DBManager and TaskManager class) and I create a new object in my main frame which is in the other package and I also imported the database package for my main frame and I call addBirth() on my object,and I want to insert these arguments in my table="birthsql" in MySQL but I found this stacktrace and also was written "SERVER = NULL".

creating an object--> TaskManager tm = new TaskManager(); calling addBirth() method on my object-->, tm.addBirth(3, "Neda","Rahmani", "Mansour", "Sima","December","Tehran");

My TaskManager class:

public class TaskManager {
private int BirthID = 2;
Logger logger = Logger.getLogger(this.getClass().getName());
private Connection conn = DBManager.getConnection();

public int getID()
{
    return BirthID++;
}

public void addBirth(int BirthID, String name, String family, String fatherName, String motherName, String DateOfBirth, String PlaceOfBirth) {
    try {
        Statement stm = conn.createStatement();

        stm.executeUpdate("INSERT INTO birthsql (name," + "family," + "fatherName," + "motherName," + "DateOfBirth, " + "PlaceOfBirth)" + "VALUES (" + BirthID + ", '" + name + "', '" + family + "', '" + fatherName + "', '" + DateOfBirth + "', '" + PlaceOfBirth + "')");
    } catch (SQLException ex) {
        Logger.getLogger(TaskManager.class.getName()).log(Level.SEVERE, null, ex);
    }


}}

My DBManager class:

public class DBManager {

private static Logger log = Logger.getLogger(DBManager.class.getName());
private static Connection connection = null;
private final static String DB_URL = "jdbc:mysql://localhost:3306/assignment_2";
private final static String DB_USERID = "root";
private final static String DB_PASSWORD = "123";

public static Connection getConnection()
{
    if (connection == null)
    {
        try {
            /* Your code here */
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(DB_URL, DB_USERID, DB_PASSWORD);
        } catch (SQLException ex) {
            Logger.Dec 10, 2009 6:44:05 AM database.DBManager getConnection

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    return connection;
}}

STACKTRACE----->

  Dec 10, 2009 11:13:20 AM database.Manager addBirth
        SEVERE: null
        com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name,mother name,date of birth,place of birth) VALUES('Neda','rahmani','Mansour'' at line 1
                at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
                at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
                at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
                at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
                at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
                at com.mysql.jdbc.Util.getInstance(Util.java:381)
                at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
                at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
                at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
                at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
                at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
                at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
                at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)
                at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1564)
                at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1485)
                at database.Manager.addBirth(Manager.java:34)
                at AdminGUI.MainFrame.<init>(MainFrame.java:51)
                at AdminGUI.MainFrame$86.run(MainFrame.java:1884)
                at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:209)
                at java.awt.EventQueue.dispatchEvent(EventQueue.java:597)
                at   java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
                at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
                at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
                at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
                at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
                at java.awt.EventDispatchThread.run(EventDispatchThread.java:121)

Answer 1

Are you sure the right version of your code is executed? Your code looks "INSERT INTO birthsql (name,family,fatherName,motherName,DateOfBirth, PlaceOfBirth)" (i removed the senseless " + " in the string constant) but your stacktrace shows name,mother name,date of birth,place of birth which is completely different (and no valid SQL because of the spaces in the column names).

The other problem is the number of parameters. The values start with BirthID before the name, but the columns do not include a column for this value!

And last, the output is not SERVER = NULL, but SEVERE: null. I think the Logger call of the catch SQLException is the same as for ClassNotFoundException, so that is your log output (Level SEVERE, message null, stacktrace of throwable).

PS: it is very confusing to name a static variable same as a parameter name - don't do so!

Answer 2

the problem seems to be a column mismatched. the BirthID doesn't seem to correspond to the columns you set in the INTO clause.

Answer 3

The answer is at the beginning of the stack trace:

  You have an error in your SQL syntax; ....
  'name,mother name,date of birth,place of birth) 
  VALUES('Neda','rahmani','Mansour''

The problem is your field names. They may not contain spaces. Also, your column count is off, you forgot BirthID in the front and motherName in the values section.

The stack output doesn't seem to have come from this code snippet. I don't see how you could have created those bad (space containing) names from the "INSERT INTO birthsql" section. The quotes are balanced properly:

   Should be more like:  (pseudo-code)
   INSERT INTO
      birthsql
        (BirthID, name,  family,  fatherName,  motherName, 
         DateOfBirth,   PlaceOfBirth)
      VALUES
        (BirthID,'name','family','fatherName','motherName',
         'DateOfBirth','PlaceOfBirth')

Try to format your code like above so its easy to keep things straight.

Heres a quick stab at cleanup. I'd use some two arrays and some joins, but this is qnd: Also, I don't know if this'll work. Might need line continuation characters.

   stm.executeUpdate("
    INSERT INTO birthsql (
     birthid,           name,              family,
     fatherName,        motherName,        DateOfBirth,        PlaceOfBirth)
    VALUES ("+
     BirthID     +",'"+ name       +"','"+ family      +"','"+ 
     fatherName +"','"+ motherName +"','"+ DateOfBirth +"','"+ PlaceOfBirth +"')"
   );

Answer 4

Also: you don't want to use String concatenation to create SQL statements containing values.

You must use a PreparedStatement instead, or you will be suspectible to SQL injection attacks!