Arithmetic exception, numeric overflow, or string truncation

This error message can be caused by various things, and it's isn't always easy to determine the cause. The best method is to remove parts of the query one-by-one until you discover the problematic one. This error message is actually the same for different kind of errors. Here are the causes and explanations:

1. Division by zero

Take a look and see if you have any divisions in your equasions. Is it possible that some of the values can be zero? If this is the case, you can use the CASE statement to replace the zero with something else. Or you can use DZERO function from rFunc UDF library. Examples:

select 1/0 from rdb$database;
select dzero(1, 0, 0) from rdb$database;

Please note that DZERO would return zero even for NULL input. Depending on your requirements, it might be better to use CASE (which is available since Firebird 1.5). Here's an example showing how to use CASE:

CREATE TABLE test ( x integer );
COMMIT;
INSERT INTO TEST (X) VALUES (NULL);
INSERT INTO TEST (X) VALUES (1);
INSERT INTO TEST (X) VALUES (0);

SELECT x, CASE when x = 0 then 0 else 10/x END
FROM test;


2. Arithmetic overflow

If you use fixed precision datatypes (smallint, integer, bigint, decimal and numeric), it is possible that the result of calculation doesn't fit the datatype. Try casting the values in complex expressions as double precision and see whether the error goes away. If it works and you don't care about being too precise, you can leave it at that. Otherwise you need to check every operation and calculate the result.

Here's an example: if you multiply 9.12 with 8.11 (both numeric(18,2)) you would get 73.9632. If Firebird would store that into numeric(18,2) datatype, we would lose 0.0032. Doesn't look much, but when you have complex calculations, you can easily loose thousands (dollars or euros). Therefore, the result is stored in numeric(18,4).

Problems are rarely seen with such low precision as 2. Let's use some bigger precision. For example, numeric(18,6) times numeric(18,6) yields numeric(18,12) result, meaning that maximal value it can store is 9223372.036854775807. If (for example) you wish to keep only 6 digits of precision, you could use something like:

cast(value1 as numeric(18,3)) * cast(value2 as numeric(18,3))

which would yield numeric(18,6) result, but it is quite possible that you would get more accurate result by casting to double:

cast(cast(value1 as double precision) * cast(value2 as double precision) as numeric(18,6))


Also, if you have mixed multiplications and divisions it helps to change the order of operations, so that the overflow doesn't happen.


3. String truncation

It happens when the concatenated string doesn't fit the underlying CHAR or VARCHAR datatype size. If the result goes into a table column, perhaps it's a valid error. Or maybe you really need to increase the column size. Similar goes for intermediary values stored in stored procedure or trigger variables.


4. Character transliteration failed

This happens when you have data in database stored in one character set, but the transliteration to required character set fails. There are various points where character set transliteration occurs. There is an automatic one:

Every piece of data you retrieve from database (via SELECT or otherwise) is transliterated from character set of database table's column to connection character set. If character sets are too different, there will be two traslations: first from column charset to Unicode and then from Unicode to the connection charset.

Also, you can request transliteration manually by CASTing the column to another charset, example:

CAST(column_name AS varchar(100) character set WIN1251).

The reason that transliteration can fail is that simply some characters don't exist in certain character sets. For example, WIN1252 doesn't contain any Cyrillic characters, so if you use connection charset WIN1252 and try to SELECT from a column with Cyrillic characters, you may get such error.

In modern internationalized computer world, it is best to use Unicode or UTF8 in your applications and UTF8 connection character - if your connectivity library has provisions for it. And make sure you use at least Firebird 2.0, which has excellent UTF8 support.


5. Wrong order of parameters when using DotNetFirebird

The order in which Parameters are added to a FbCommand when using DotNetFirebird might cause the -303 exception with the hint "Arithmetic exception, numeric overflow, or string truncation". The order of the parameters has to fit the order of the params in the stored procedure - otherwise the exception will be thrown. Example (.NET, C#, DotNetFirebird (using FirebirdSql.Data.FirebirdClient;)):

FbCommand CMD = new FbCommand("TBLTEXT_ADDTEXT", cnn);
CMD.Parameters.Add("TEXT1", FbDbType.VarChar, 600).Value = strText1;
CMD.Parameters.Add("TEXT2", FbDbType.VarChar, 600).Value = strText2;
CMD.CommandType = CommandType.StoredProcedure;
CMD.ExecuteNonQuery();

If the order of the parameters inside the procedure "TBLTEXT_ADDTEXT" differ from the order in which you´re adding parameters to the FbCommand-Object, you´ll receive the -303 error.


Note: Section 5 of this FAQ was contributed by Andre Bonin.