java sql empty fields

Question

Hi i am trying to execute query

Statement stmt = conn.createStatement();
stmt.executeUpdate(location_query);

location_query is

insert into table(col, col2) value("2", "");

Both col and col2 are double(12,2) type, i get error Data truncated for column 'col2' at row 1, but if I print my query and copy, and paste it to PMA (PhpMyAdmin) its correct.

I guesst that its becouse value of col2 is empty, but is there chance to solve this in other way not to checking all variables which im trying to put to my query ??

no its not default null, but I can change it

kskaradzinski
– kskaradzinski

2011-06-01 09:56:53 +00:00
Commented Jun 1, 2011 at 9:56 — kskaradzinski
– kskaradzinski, Commented Jun 1, 2011 at 9:56

Tim Büthe · Accepted Answer · 2011-06-01 11:44:52Z

5

Well, an empty String is not a valid number, therefore different database systems might handle that differently. When you just want that column NULL or filled with the default, you should leave it out like this:

insert into table(col) value(2);

OR set it NULL explicitly:

insert into table(col, col2) value(2, NULL);

Besides, numbers are written without quotes in SQL. Furthermore, you should cosider using PreparedStatements only. PreparedStatements give you type saftly and protect from SQL injection and might be faster when reused.

Take a look at google "statement vs. preparedstatement"

EDIT

Your example would look something like this, when using prepared statements:

String yourFirstString = "2.34";
String yourSecondString = "";

double yourFirstDouble = 0;
double yourSecondDouble = 0;

if(yourFirstString  != null && yourFirstString.trim().length() > 0){
  try {
     yourFirstDouble = Double.parseDouble(yourFirstString); 
  }catch(NumberFormatException e){
    // handle Exception here
  }
}

// same parsing for the second double

PreparedStatement stmt = conn.prepareStatement("insert into table (col, col2) value(?, ?)");
stmt.setDouble(1, yourFirstDouble);
stmt.setDouble(2, yourSecondDouble);
stmt.executeUpdate();

edited Jun 1, 2011 at 11:44

answered Jun 1, 2011 at 9:46

Tim Büthe

63.9k17 gold badges96 silver badges130 bronze badges

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

3 Comments

kskaradzinski Over a year ago

Ok, but will preparedStatements protect me against empty values ?? and secound thing is that i never know which value is empty thats why I have to put all of them

Tim Büthe Over a year ago

Since ´setDouble(int, double)´ is type save you can only use valid doubles as argument. That means, you can not apply an empty String to that Statement. You should test if the String is empty and and if so, set 0 or NULL. Otherwise, you should try to parse the String into a double and use it. I'll edit my answer and add an example.

Tim Büthe Over a year ago

I added some parsing example. However, all that logic should not be in the same place. The parsing of the values, user entries I would guess, should be validated when entered somewhere near the GUI logic. The database layer however, should only get doubles and not do string parsing, validation and the like.

Collectives™ on Stack Overflow

java sql empty fields

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related