aspose file tools*
The moose likes JDBC and the fly likes 'java.sql.SQLException: Out of range value for column' error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark " Watch " New topic
Author

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

vysh srini
Greenhorn

Joined: Apr 25, 2009
Posts: 25
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

Joined: Mar 20, 2007
Posts: 317
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

Joined: Apr 10, 2010
Posts: 308
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)







Bye,
Nicola
vysh srini
Greenhorn

Joined: Apr 25, 2009
Posts: 25
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

Joined: Apr 10, 2010
Posts: 308
UNSIGNED is a flag.
You can use it while creating or modifying a column.
For example:



or

vysh srini
Greenhorn

Joined: Apr 25, 2009
Posts: 25
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.....
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: 'java.sql.SQLException: Out of range value for column' error