Out of range value adjusted for column ...
I'm collecting data from a form and populating the db with it.
mysql describe titles;
+--+-+--+-+-++
| Field| Type| Null | Key | Default | Extra
+--+-+--+-+-++
| MovieID | int(10) unsigned| NO | PRI | NULL| auto_increment
| Title| varchar(100)| NO | | NULL|
| Year | year(4) | NO | | |
| Comment | tinytext| YES | | NULL|
| CaseNum | tinyint(3) unsigned | YES | | NULL|
| Director | varchar(50) | YES | | NULL|
| Genre| varchar(15) | YES | | NULL|
| Language | char(3) | YES | | NULL|
| Actor| varchar(50) | YES | | NULL|
+--+-+--+-+-++
If I only enter a new movie title, leaving the rest of the fields blank,
my script throws an error:
Out of range value adjusted for column 'Year' at row 1
My Perl code for the above action:
my $sql=qq{INSERT INTO titles
(Title,Director,Actor,Genre,Language,CaseNum,Year,Comment)
VALUES
($Title,$Director,$Actor,$Genre,$Language,$CaseNum,$Year,$Notes)};
I'm stumped by the 'out of range' error. Anyone shed some light on this?
--
Amer Neely - Web Mechanic
w: www.webmechanic.softouch.on.ca/
e: [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of Range value adjusted?
Hello.
If you still want to use the decimal(3,2) data type for your table
(even after excellent explanations by Shawn), check that the pure
instance of MySQL doesn't use STRICT sql mode. See:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
Kerry Frater wrote:
I am trying to test some code on the new 5.0 version and am getting
problems.
I currently have two MYSQL's running on two differing machines. Both have
the same schema set up by the my own program. The difference is that Machine
1 was set up on version 4.1 and then upgraded to 5. Machine 2 was a fresh
installation of version 5.
My Data insertion code works perfectly well with the Version 5 instance that
was upgraded from version 4, but fails on the pure version 5. The
Administrator interface tells me that the tables are OK and I cannot see any
difference between the V4 upgraded to 5 table against the pure V5 install.
The code being used is
INSERT INTO Invs
(InvRef,InvDate,Outgoingref,Outgoingref2,BillDescription,BillAmount,VatCode,
VatRate)
VALUES
('1234#6','19991016','C','19990731','\Orig Bill
£728.50\',364.68,'A',17.50);
The table columns are set to
char(12),date,char(1),date,char(40),decimal(12,2),char(1),decimal(3,2). The
table is MyISAM.
There error produced on the pure version 5 installation is:
Out of Range value adjusted for column VATRate at row 1
and then quits. I have let the installation of MySQL use its defaults and is
of charset latin1 if it is an issue. The above statement does not error on
the upgraded version of MySQL but adds the row with no problem.
The set up program is using a function
with SQLBatch.SQL do
begin
Add('CREATE TABLE Invs (');
Add('InvRef char(12) default NULL,');
Add('InvDate date,');
Add('OutgoingRef char(1),');
Add('OutgoingRef2 date,');
Add('BillDescription char(40),');
Add('BillAmount decimal(12,2),');
Add('VATCode char(1),');
Add('VATRate decimal(3,2),');
Add(' index (OutgoingRef2)');
Add(') TYPE=MyISAM;');
end;
SQLBatch.ExecSql;
This seems to work fine. Any ideas on why it works with one and not the
other?
Kerry
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Out of Range value adjusted?
I am trying to test some code on the new 5.0 version and am getting
problems.
I currently have two MYSQL's running on two differing machines. Both have
the same schema set up by the my own program. The difference is that Machine
1 was set up on version 4.1 and then upgraded to 5. Machine 2 was a fresh
installation of version 5.
My Data insertion code works perfectly well with the Version 5 instance that
was upgraded from version 4, but fails on the pure version 5. The
Administrator interface tells me that the tables are OK and I cannot see any
difference between the V4 upgraded to 5 table against the pure V5 install.
The code being used is
INSERT INTO Invs
(InvRef,InvDate,Outgoingref,Outgoingref2,BillDescription,BillAmount,VatCode,
VatRate)
VALUES
('1234#6','19991016','C','19990731','\Orig Bill
£728.50\',364.68,'A',17.50);
The table columns are set to
char(12),date,char(1),date,char(40),decimal(12,2),char(1),decimal(3,2). The
table is MyISAM.
There error produced on the pure version 5 installation is:
Out of Range value adjusted for column VATRate at row 1
and then quits. I have let the installation of MySQL use its defaults and is
of charset latin1 if it is an issue. The above statement does not error on
the upgraded version of MySQL but adds the row with no problem.
The set up program is using a function
with SQLBatch.SQL do
begin
Add('CREATE TABLE Invs (');
Add('InvRef char(12) default NULL,');
Add('InvDate date,');
Add('OutgoingRef char(1),');
Add('OutgoingRef2 date,');
Add('BillDescription char(40),');
Add('BillAmount decimal(12,2),');
Add('VATCode char(1),');
Add('VATRate decimal(3,2),');
Add(' index (OutgoingRef2)');
Add(') TYPE=MyISAM;');
end;
SQLBatch.ExecSql;
This seems to work fine. Any ideas on why it works with one and not the
other?
Kerry
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of Range value adjusted?
Kerry Frater [EMAIL PROTECTED] wrote on 02/02/2006 11:21:44
AM:
I am trying to test some code on the new 5.0 version and am getting
problems.
I currently have two MYSQL's running on two differing machines. Both
have
the same schema set up by the my own program. The difference is that
Machine
1 was set up on version 4.1 and then upgraded to 5. Machine 2 was a
fresh
installation of version 5.
My Data insertion code works perfectly well with the Version 5 instance
that
was upgraded from version 4, but fails on the pure version 5. The
Administrator interface tells me that the tables are OK and I cannot see
any
difference between the V4 upgraded to 5 table against the pure V5
install.
The code being used is
INSERT INTO Invs
(InvRef,InvDate,Outgoingref,Outgoingref2,BillDescription,BillAmount,VatCode,
VatRate)
VALUES
('1234#6','19991016','C','19990731','\Orig Bill
£728.50\',364.68,'A',17.50);
The table columns are set to
char(12),date,char(1),date,char(40),decimal(12,2),char(1),decimal(3,2).
The
table is MyISAM.
There error produced on the pure version 5 installation is:
Out of Range value adjusted for column VATRate at row 1
and then quits. I have let the installation of MySQL use its defaults
and is
of charset latin1 if it is an issue. The above statement does not
error on
the upgraded version of MySQL but adds the row with no problem.
The set up program is using a function
with SQLBatch.SQL do
begin
Add('CREATE TABLE Invs (');
Add('InvRef char(12) default NULL,');
Add('InvDate date,');
Add('OutgoingRef char(1),');
Add('OutgoingRef2 date,');
Add('BillDescription char(40),');
Add('BillAmount decimal(12,2),');
Add('VATCode char(1),');
Add('VATRate decimal(3,2),');
Add(' index (OutgoingRef2)');
Add(') TYPE=MyISAM;');
end;
SQLBatch.ExecSql;
This seems to work fine. Any ideas on why it works with one and not the
other?
Kerry
In older versions, the range checking for DECIMAL() columns was lax (a
bug). The range checking has been fixed, which may be causing your
problem. For example: in the older versions a DECIMAL(12,3) column could
actually contain a positive number with 10 digits to the left of the
decimal place, instead of the 9 allowed by the definition, because the
extra digit was being allowed to reside in the space set aside for the
sign indicator. By fixing the implementation to actually honor the
definition of the column, a few programs that used that extra, illegal
space started having the exact problems you describe.
Now for the example you provided, you defined VATRate as DECIMAL(3,2)
which meant you wanted that field to have the space for 3 digits with 2 of
them to the right of the decimal place. Your potential range of values for
a field defined this way is from -9.99 to +9.99. You tried to insert the
value 17.50 . With the older, buggy, implementation that would have fit
into a (3,2) because the 1 of the 17 would have fit into the space the +
sign would have occupied if it were displayed. In the fixed
implementation, 17 is too large and will no longer fit.
My suggestion is to resize the field to (4,2) so that you have the range
-99.99 to +99.99 .
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
