Parsing SQLDate, java.util.Date and LocalDate

Question

I'm very confused working with dates could someone point me in the right direction on for the code below, it throws the following exception:

org.h2.jdbc.JdbcSQLException:Invalid value '11' for parameter "parameterIndex" [90008-193]

 import java.util.logging.Level;
 import java.util.logging.Logger;
 import java.sql.*;
 import javax.swing.*;
 import java.awt.*;
 import java.awt.event.ActionEvent;
 import java.awt.event.ActionListener;
 import java.time.LocalDate;
 import java.time.ZoneId;


 public class Database {

     public static void main(String[] args) throws SQLException {  
        Connection conn = null;
        Statement st = null;
        String URL = "jdbc:h2:~/registDB";
        String USER = "admin";
        String PASSWORD = "password";
        ZoneId z = ZoneId.systemDefault() ;
        LocalDate currentDate = LocalDate.now(z);
        LocalDate expiration = currentDate.plusDays(inputFld.getText()); //JTextField.getText()
        java.sql.Date expirationDate = java.sql.Date.valueOf(expiration);

        try {  
           Class.forName("org.h2.Driver").newInstance();
           conn = DriverManager.getConnection(URL,USER,PASS);
           String sql = "INSERT INTO data
                        (fullName,regNum,itemName,note,zHemjee,fee,
                         time,date,totalPay,expirationDate)"
                         + "VALUES"
                         + "(?,?,?,?,?,?,?,?,?,?)";
           pst = conn.prepareStatement(sql);  
                   // 1st index left not being modified on purpose which is ID auto_increment-ed     
           pst.setString(2, getFullName());   // Get methods are   
                                         // JTextField.getText() casted into proper data types
                                         // except the 11th row which is throwing SQLException
           pst.setString(3, getRegNum());
           pst.setString(4, getItemName());
           pst.setString(5, getNote());
           pst.setInt(6, getzHemjee());
           pst.setInt(7, getFee());
           pst.setInt(8, getTime());
           pst.setDate(9, java.sql.Date.valueOf(LocalDate.now()));
           pst.setDouble(10, getTotalPay());
           pst.setDate(11, expirationDate);
           pst.executeUpdate();
           pst.close();
           conn.close();

        } catch (ClassNotFoundException | SQLException ex) {
         Logger.getLogger(database.class.getName()).log(Level.SEVERE, null, ex);
        }      
    }
 }

Here are my SQL statement creating a table with it's column types:

  CREATE TABLE IF NOT EXISTS data "
            +"(id INT NOT NULL AUTO_INCREMENT,"             //int
            + " fullname varchar(30),"                      //String
            + " regNum varchar(10),"                        //String
            + " itemName varchar(30),"                      //String
            + " note varchar(30),"                          //String
            + " zHemjee int,"                            //int
            + " fee number,"                                //int
            + " time INT,"                                  //int
            + " date DATE,"                                 //Date
            + " totalPay BIGINT,"                           //int
            + " expirationDate DATE);");                    //Date

possible duplicate of stackoverflow.com/questions/1081234/… — Japu_D_Cret
– Japu_D_Cret, Commented Mar 28, 2017 at 8:34
Thank you for your comment but I tried converting it to String and I'm using PreparedStatement for my original version of the code. — Tseveendorj Ganbaatar
– Tseveendorj Ganbaatar, Commented Mar 28, 2017 at 8:48
@AshfordTulgaa 2011 = 2017 - 4 - 2. Your "date" is interpreted as a mathematical expression. The link above solves the problem. If the code you are using is different from what you posted, then please post your actual code... — assylias
– assylias, Commented Mar 28, 2017 at 13:08
Where is the link you told me about? stackoverflow.com/questions/1081234/… This one? — Tseveendorj Ganbaatar
– Tseveendorj Ganbaatar, Commented Mar 28, 2017 at 13:28

Bojan Petkovic · Accepted Answer · 2017-03-28 18:52:27Z

1

The issue is that you do you are entering the wrong index. You should be starting at 1 not at 2.
There are only 10 '?' and you have 11th parameter which does not exist.
This code should work:

String sql = "INSERT INTO data
                    (ovogNer,regNum,itemName,note,zHemjee,hvv,
                     hugatsaa,date,totalPay,expirationDate)"
                     + "VALUES"
                     + "(?,?,?,?,?,?,?,?,?,?)";
pst = conn.prepareStatement(sql);       
pst.setString(1, getFullName());  
pst.setString(2, getRegNum());
pst.setString(3, getItemName());
pst.setString(4, getNote());
pst.setInt(5, getzHemjee());
pst.setInt(6, getFee());
pst.setInt(7,  getTime());
pst.setDate(8, java.sql.Date.valueOf(LocalDate.now()));
pst.setDouble(9, getTotalPay());
pst.setDate(10, expirationDate);
pst.executeUpdate();

also you had weird "+" when setting the Integers.

answered Mar 28, 2017 at 18:52

Bojan Petkovic

2,57417 silver badges28 bronze badges

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

2 Comments

Tseveendorj Ganbaatar Over a year ago

Thank you for your reply but the 1st index is ID and it's AUTO_INCREMENTed so I left that on purpose.

Bojan Petkovic Over a year ago

Your INSERT query does not have the id in it. hence you should not insert it. I just noticed also ovegner should be fullname (guess a typo - please fix it). On Inserts you do not need to add the id at all. It will be auto incremented by the database. feel free to remove the id from the parameters.

Chris Parker · Accepted Answer · 2017-03-28 19:34:07Z

Bojan Petkovic's answer is correct. Having said that, whenever I have more than two or three parameters, I always use an index variable to prevent errors. Like this:

int index = 1;
String sql = "INSERT INTO data
                    (ovogNer,regNum,itemName,note,zHemjee,hvv,
                     hugatsaa,date,totalPay,expirationDate)"
                     + "VALUES"
                     + "(?,?,?,?,?,?,?,?,?,?)";
pst = conn.prepareStatement(sql);       
pst.setString(index++, getFullName());  
pst.setString(index++, getRegNum());
pst.setString(index++, getItemName());
pst.setString(index++, getNote());
pst.setInt(index++, getzHemjee());
pst.setInt(index++, getFee());
pst.setInt(index++,  getTime());
pst.setDate(index++, java.sql.Date.valueOf(LocalDate.now()));
pst.setDouble(index++, getTotalPay());
pst.setDate(index++, expirationDate);
pst.executeUpdate();

Of course that methodology depends on you putting everything in the proper order (which you did). Another option would be to make internal constants that work out to numbers. By the time you reach that point though, you are at a place where you really need to be using a framework for your SQL access. :)

[EDIT] Or at least the code is "correct" in the sense that the code works as described.

Collectives™ on Stack Overflow

Parsing SQLDate, java.util.Date and LocalDate

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related