Java SQL Query not working with Insert?

Question

Why is this not adding more data into the Table? what is wrong with the code/query? The database is connected and works with Select * ... but when i try to use Insert into, it gives the error shown below, why is that? how can i correct this error?

 public void RegisterNewUser(String username, String password, String firstname, String surname, String dateOfBirth, int currentWeight) {

    //This code will connect the database to the java program

    Connection myconObj = null; //allows to connect to database
    Statement mystatObj = null; // create statement (Execute queries)
    ResultSet myresObj = null; // get result
    ResultSetMetaData mymeta = null;

    try {

        String query = "INSERT INTO JACOVANSTRYP.MAINUSERDATA(USERNAME, PASSWORD, FIRSTNAME, LASTNAME, DATEOFBIRTH, CURRENTWEIGHTKG, ACTIVEPOINTS) VALUES(\"" + username + "\", \"" + password + "\", \"" + firstname + "\", \"" + surname + "\",\"" + dateOfBirth + "\", \"" + currentWeight + "0 )";

        myconObj = DriverManager.getConnection("jdbc:derby://localhost:1527/MainUserData", "jacovanstryp", "Eduplex1234");
        mystatObj = myconObj.createStatement();
        Statement add = myconObj.createStatement();
        add.executeUpdate(query);
        mymeta = myresObj.getMetaData();





        System.out.println("User Successfuly created");
    } catch (SQLException e) {
      e.printStackTrace();
    }

This is the error message its giving, i have tried to do this without the values that it gets from another class, and it still gives the same error even if the query is written in a plane string without external values.

    java.sql.SQLSyntaxErrorException: VALUES clause must contain at least one element. Empty elements are not allowed. 
    at org.apache.derby.client.am.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
    at org.apache.derby.client.am.ClientStatement.executeUpdate(Unknown Source)
    at com.vanstryp.backend.commonMethods.RegisterNewUser(commonMethods.java:143)
    at com.vanstryp.GUI.Secure.Register.jButton1ActionPerformed(Register.java:389)
    at com.vanstryp.GUI.Secure.Register.access$400(Register.java:6)
    at com.vanstryp.GUI.Secure.Register$4.actionPerformed(Register.java:139)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
    at java.awt.Component.processMouseEvent(Component.java:6533)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
    at java.awt.Component.processEvent(Component.java:6298)
    at java.awt.Container.processEvent(Container.java:2236)
    at java.awt.Component.dispatchEventImpl(Component.java:4889)
    at java.awt.Container.dispatchEventImpl(Container.java:2294)
    at java.awt.Component.dispatchEvent(Component.java:4711)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4525)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)
    at java.awt.Container.dispatchEventImpl(Container.java:2280)
    at java.awt.Window.dispatchEventImpl(Window.java:2746)
    at java.awt.Component.dispatchEvent(Component.java:4711)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
    at java.awt.EventQueue.access$500(EventQueue.java:97)
    at java.awt.EventQueue$3.run(EventQueue.java:709)
    at java.awt.EventQueue$3.run(EventQueue.java:703)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:90)
    at java.awt.EventQueue$4.run(EventQueue.java:731)
    at java.awt.EventQueue$4.run(EventQueue.java:729)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)
Caused by: ERROR 42X80: VALUES clause must contain at least one element. Empty elements are not allowed. 
    at org.apache.derby.client.am.ClientStatement.completeSqlca(Unknown Source)
    at org.apache.derby.client.am.ClientStatement.completeExecuteImmediate(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.parseEXCSQLIMMreply(Unknown Source)
    at org.apache.derby.client.net.NetStatementReply.readExecuteImmediate(Unknown Source)
    at org.apache.derby.client.net.StatementReply.readExecuteImmediate(Unknown Source)
    at org.apache.derby.client.net.NetStatement.readExecuteImmediate_(Unknown Source)
    at org.apache.derby.client.am.ClientStatement.readExecuteImmediate(Unknown Source)
    at org.apache.derby.client.am.ClientStatement.flowExecute(Unknown Source)
    at org.apache.derby.client.am.ClientStatement.executeUpdateX(Unknown Source)
    ... 41 more

try using prepared statement and use Execute query instead of Update — Jayanth
– Jayanth, Commented Feb 17, 2018 at 16:14

hd1 · Accepted Answer · 2018-02-17 18:37:00Z

2

There is more wrong with the chosen approach than the query itself. I've rewritten your code to use the PreparedStatement functionality:

public void RegisterNewUser(String username, String password, String firstname, String surname, String dateOfBirth, int currentWeight) {

    //This code will connect the database to the java program

    Connection myconObj = null; //allows to connect to database
    ResultSet myresObj = null; // get result
    ResultSetMetaData mymeta = null;

    try {

        String query = "INSERT INTO JACOVANSTRYP.MAINUSERDATA(USERNAME, PASSWORD, FIRSTNAME, LASTNAME, DATEOFBIRTH, CURRENTWEIGHTKG, ACTIVEPOINTS) VALUES(?,?,?,?,?,?,?)";

        myconObj = DriverManager.getConnection("jdbc:derby://localhost:1527/MainUserData", "jacovanstryp", "Eduplex1234");
        PreparedStatement add = myconObj.prepareStatement(query);
        add.setString(1, username);
        add.setString(2, password);
        add.setString(3, firstname);
        add.setString(4, lastname);
        add.setString(5, dateOfBirth); // might need setDate() depending on your table structure
        add.setString(6, currentWeight);
        add.setString(7, "0");
        add.executeUpdate();
        mymeta = myresObj.getMetaData();
        System.out.println("User Successfuly created");
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Hope that helps...

@MarkRotteveel wants an explanation as to what is wrong with OPs code. Really, nothing in particular. It may work, but needs more care in construction. Also, constructing SQL as OPs doing opens him up to injection attacks. Again, not an issue for hobby projects, but in a professional context, you don't want to write code open to SQL injection.

edited Feb 17, 2018 at 18:37

answered Feb 17, 2018 at 16:41

hd1

34.9k5 gold badges83 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mark Rotteveel Over a year ago

Could you add an explanation of what is wrong with the original approach.

jaco Over a year ago

mystatObj = myconObj.createStatement(); This line says it needs to cast PreparedStatement to it " mystatObj = (PreparedStatement) myconObj.createStatement();" When i do that, it returns this error "Exception in thread "AWT-EventQueue-0" java.lang.ClassCastException: org.apache.derby.client.am.ClientStatement cannot be cast to java.sql.PreparedStatement" Any idea why?

hd1 Over a year ago

Sorry, @jaco, just nix the mystatObj line

hd1 Over a year ago

@MarkRotteveel was that in line with what you would have liked?

Mark Rotteveel Over a year ago

I wouldn't have started it with "Really, nothing in particular.", as the SQL injection in the original code is a gaping security bug. And I wouldn't have mentioned that it is "not an issue for hobby projects", because habits and techniques learned in hobby projects are usually also applied without thinking in a professional context.

Cap Barracudas · Accepted Answer · 2018-02-17 16:28:38Z

0

You insert 6 paramenters for a 7 parameters query(check your last zero). This is how you should do it:

  "INSERT INTO JACOVANSTRYP.MAINUSERDATA(USERNAME, PASSWORD, FIRSTNAME, LASTNAME, DATEOFBIRTH, CURRENTWEIGHTKG, ACTIVEPOINTS) VALUES(param1, param2, etc)"

Reference: How to use java variable to insert values to mysql table?

So try something like this:

String query = "INSERT INTO JACOVANSTRYP.MAINUSERDATA(USERNAME, PASSWORD, FIRSTNAME, LASTNAME, DATEOFBIRTH, CURRENTWEIGHTKG, ACTIVEPOINTS) VALUES(\"" + username + "\", \"" + password + "\", \"" + firstname + "\", \"" + surname + "\",\"" + dateOfBirth + "\", \"" + currentWeight + ",0 )";

edited Feb 17, 2018 at 16:28

answered Feb 17, 2018 at 16:20

Cap Barracudas

2,5156 gold badges31 silver badges58 bronze badges

1 Comment

Mark Rotteveel Over a year ago

-1 For not suggesting using prepared statements, but instead promoting the unsafe approach that keeps the code open to SQL injection, and the fact your suggestion doesn't even work, as the original problem is with the use of a double quote (which is for quoting object names) instead of single quotes (for literals).

Collectives™ on Stack Overflow

Java SQL Query not working with Insert?

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related