Negative values in INTEGER column?

[fatabbot]

Hi,

I'm trying to add a value of -100 in an integer field, like this:


insert into mytable (field1, field2) values ("x", -100);


But everytime the value converts to 0.
Value out of range, data truncated ....

When i manually enter -100 in the field, it works. But not via an sql query...
What could be the problem here?

[ForumJoiner]

There is a free program, called SQL Manager Lite. You can find a link here (http://www.lunlun.com/lunlunlinks/2007/01/26/ems-sql-manager-for-mysql-3771/).

Open your database using this program and try to do a query. If it still does not work, please export the table structure and post it here, to replicate the problem.

[drewbe121212]

1.) Make sure the table column is not "UNSIGNED". Unsigned columns can increase their maximum size by starting at the value of 0 and above rather then going negative.

2.) Make sure the integeter you are trying to insert has the quotes around it. '-100'.

[LeopardAt1]

Can I see your exact sql statement that produces the '0' value.

I had the same exact problem last night programming a script of mine.

I fixed it by putting double quotee around the number vs. single quotes.

So :

INSERT INTO db (my_int) VALUES ("-100")


Cheers

[fatabbot]

Can I see your exact sql statement that produces the '0' value.

I had the same exact problem last night programming a script of mine.

I fixed it by putting double quotee around the number vs. single quotes.

So :

INSERT INTO db (my_int) VALUES ("-100")


Cheers

I found the problem, the field was checked as UNSIGNED. That way the absolute value was stored |-5| = 5 ...

[ForumJoiner]

When I manually enter -100 in the field, it works. However, not via an sql query...
How did you manage to store -100 in an unsigned field?


That way the absolute value was stored |-5| = 5 ...
That means that it actually did not work correctly even manually in the first place, because you could not store -100, but 100, which is very different?

[fatabbot]

How did you manage to store -100 in an unsigned field?



That means that it actually did not work correctly even manually in the first place, because you could not store -100, but 100, which is very different?

The field was unsigned so i was unable to store negative values. -100 became 100.
Now i changed the field to SIGNED, and i can store -100.