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

[vysh srini]

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]

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]

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]

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]

UNSIGNED is a flag.
You can use it while creating or modifying a column.
For example:

CREATE TABLE TEST_TABLE(
   ...
    number  INT UNSIGNED NOT NULL 
  ...
)

or

ALTER TABLE TEST_TABLE MODIFY COLUMN number INT UNSIGNED NOT NULL;

[vysh srini]

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.....