Java & MySQL - Error with setBoolean() method

Question

I'm getting the error: "Column count doesn't match value count at row 1", yet I have counted and I have the right number of columns and I feel that the error is with the setBoolean() method. I have 8 columns and you will see that I'm passing 7 arguments in my method as one of the columns is an AUTO_INCREMENTE. My coding works perfectly fine if I don't use the setBoolean method replacing it for a setString, setInt or whatever else, but not when I do it with setBoolean().

Here is my code, which I hope makes my problem clearer:

I have an automatic code in my Java that creates the Database & its respective table/s if it doesn't exist:

DB.createDB("userSecInfo");
DB.createTable("accountInfo", "userSecInfo", "("+
                                              "Username VARCHAR(40) NOT NULL, "+
                                              "Pass VARCHAR(40) NOT NULL, "+
                                              "chkPwChange BOOLEAN NOT NULL DEFAULT FALSE, "+
                                              "chkPwCntChange BOOLEAN NOT NULL DEFAULT FALSE, "+
                                              "chkPwNoExpires BOOLEAN NOT NULL DEFAULT FALSE, "+
                                              "chkPwExpires BOOLEAN NOT NULL DEFAULT FALSE, "+
                                              "expirydays INT, "+
                                              "UserID INT NOT NULL AUTO_INCREMENT, "+
                                              "PRIMARY KEY (UserID))");

I have tried defining all the boolean variables for MySQL in different ways such as BOOL, also as BOOLEAN (as it's above) and as TINYINT & TINYINT(1); but the variable definition does not seem to be the problem.

Then the programme, once it has created the Database and the tables, it proceeds to extract the information that the user has inputted and inserts it into the Database:

DB.insertUserSecAccountInfo("userSecInfo", "accountInfo", tFUsername.getText(),
                                            String.copyValueOf(pFPassword.getPassword()), chckbxUserMustChange.isSelected(), 
                                            chckbxUserCannotChange.isSelected(), chckbxPasswordNeverExpires.isSelected(), 
                                            chckbxPasswordExpiresAnd.isSelected(), Integer.parseInt(tFdays.getText()));

The insertUserSecAccountInfo method as below:

public void insertUserSecAccountInfo(String db_name,String table_name, String Username, String Pass,
             boolean chkPwChange,  boolean chkPwCntChange, boolean chkPwNoExpires,boolean chkPwExpires,
             int expirydays) throws Exception {
            try {
                openConnection("whatever","password",db_name);
                String Query ="INSERT into "+ table_name +" values(?,?,?,?,?,?,?)";
                ps = Conexion.prepareStatement(Query);
                ps.setString(1, Username);              
                ps.setString(2, Pass);
                ps.setBoolean(3, chkPwChange);              
                ps.setBoolean(4, chkPwCntChange);       
                ps.setBoolean(5, chkPwNoExpires);   
                ps.setBoolean(6, chkPwExpires);
                ps.setInt(7, expirydays);
                ps.executeUpdate();
            } catch (Exception ex) {
                JOptionPane.showMessageDialog(null, "Error saving data"));
                ex.printStackTrace();
            } finally {
                closeConnection();
            }
        }

When I run it, it shows me the error: "Column count doesn't match value count at row 1". But then I run basically the same line directly in MySQL as below, and it inserts it perfectly fine:

insert into accountInfo(Username,Pass,chkPwChange,chkPwCntChange,chkPwNoExpires,chkPwExpires,expirydays) values ('Hola','No',true,true,true,true,56);

So it makes me feel it's a problem with the setBoolean() method, which might not be mapping or passing correctly the boolean variables from Java to MySQL; has anyone had any similar issue? I would very much appreciate any guidance with this.

Thank you in advance.

WARNING: Do not use plain-text passwords. Always use a password-specific hash like Bcrypt to store them. — tadman
– tadman, Commented Feb 19, 2020 at 2:03
Try naming all the columns you're providing values for in the INSERT statement. — user207421
– user207421, Commented Feb 19, 2020 at 2:11
I mean, after all the setBoolean(blah,blah) lines. If setBoolean is the problem, then the String that is executed should not be the same as the one you sent to MySQL directly. — Pepi
– Pepi, Commented Feb 19, 2020 at 6:14

rzwitserloot · Accepted Answer · 2020-02-19 02:26:29Z

1

Because you did not name every column explicitly, the implicit default of all columns applies. The fact that one of them has a default value is irrelevant (and autocounting is no more and no less than the script of: "Make a sequence, now set the default value for this column to NEXTVAL(nameOfGeneratedSequence)).

This has nothing whatsoever to do with setBoolean.

As some of the commenters have said, you need to always explicitly name the columns you're setting, so: `INSERT INTO " + tableName + " (Username, Pass, etc) VALUES (?,?,... etc)".

SECURITY NOTE: Storing passwords in plain text in a database does not fit with minimal security best practices. If deployed like this and it ever leaks out, you will likely receive a hefty fine or worse (In europe via the GDPR; other jurisdictions often have similar legislation), not to mention a heck of a lot of deserved media fallout. Do not do it this way. – use bcrypt and store the hash instead.

answered Feb 19, 2020 at 2:26

rzwitserloot

107k6 gold badges74 silver badges104 bronze badges

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

4 Comments

James Gallego Over a year ago

Thank you @rzwitserloot; I'm using preparedStatements for the Query and so the variables are added with the ps.setString(1, Username); and so forth till ps.setInt(7, expirydays); and that's why I did not specify them in the query. The reason I said I feel is the setBoolean is because if I change it to a different one, my code as is works fine. P.D.: Thanks for the tip on the security, I have that on the radar, but just wanted to have the basics working first before I moved into that.

user207421 Over a year ago

@JamesGallego You could always try it. It's a good idea for another reason: your code should not rely on an external ordering of the columns.

rzwitserloot Over a year ago

@JamesGallego The variable name in java does not translate. In the code ps.setString(1, Username);, the name of the variable ('Username') has no effect on the code. All the java ends up seeing is: Set the first question mark to "Joe". It is not possible to tell java: Can you use the names of my variables to figure out which columns I meant. If you could have, there'd have been no need for that '1' there.

James Gallego Over a year ago

Thank you all, I specified the column names after tableName and it worked well. It still intrigues why it works the way I have it with different other methods (setString, setInt) & not with setBoolean; but it worked with the change that was done, so thanks.

Collectives™ on Stack Overflow

Java & MySQL - Error with setBoolean() method

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related