Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

'java.sql.SQLException: Out of range value for column' error

 
vysh srini
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I am using MySql 5.1. I created a column in table 'phonenumber' with datatype 'int' of length 15. If I enter 9 digit number it is accepting. But if I enter a value directly in the database, it is entering a value '2147483647'.


If I enter details through insert query in my java code, I am getting the following error:

java.sql.SQLException: Out of range value for column 'phonenumber' at row 1.

Can anyone tell me what could be the error
 
Sridhar Santhanakrishnan
Ranch Hand
Posts: 317
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am not sure of the reason but 2147483647 is the max value a signed integer(-2^31 to 2^31-1) can hold. Maybe, looking into those lines might help.
 
Nicola Garofalo
Ranch Hand
Posts: 308
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Quoting from MySql 5.1 ref manual, Paragraph 10.2 Numeric Types

When asked to store a value in a numeric column that is outside the data type's permissible range, MySQL's behavior depends on the SQL mode in effect at the time. For example, if no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead. However, if strict SQL mode is enabled, MySQL rejects a value that is out of range with an error, and the insert fails, in accordance with the SQL standard.



INT (uses 4 byte, i.e. 32 bits) is probably not enough for your phone number's range (up to 2147483647 as previously written)

With UNSIGNED INT your max number could be 4294967295

If it's not enough, though i imagine it is, MySql allows you to use BIGINT datatype. You can store numbers up to 9223372036854775807 (in the signed version)






 
vysh srini
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nicola Garofalo wrote:Quoting from MySql 5.1 ref manual, Paragraph 10.2 Numeric Types


INT (uses 4 byte, i.e. 32 bits) is probably not enough for your phone number's range (up to 2147483647 as previously written)

With UNSIGNED INT your max number could be 4294967295

If it's not enough, though i imagine it is, MySql allows you to use BIGINT datatype. You can store numbers up to 9223372036854775807 (in the signed version)








Thanks. That helped me. Unsigned int was not present. I used 'big int'
 
Nicola Garofalo
Ranch Hand
Posts: 308
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
UNSIGNED is a flag.
You can use it while creating or modifying a column.
For example:



or

 
vysh srini
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh ok thanks. Since i am using a tool called SQL Yog to work with MySQL, i rarely use those 'create...' commands to create tables.

Thanks anyway.....
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic