Firebird 3.0.11: arithmetic exception, numeric overflow, or string truncation

[Luciano Rodrigues Nunes Mendes]

Hi Guys,

The following issue is happening when I try to query a table in my database:

arithmetic exception, numeric overflow, or string truncation

string right truncation
expected length 8191, actual 8337

The issue is happening in 2 records of this table in a VARCHAR(32765) WIN1252 column.

The database's Default Character Set is UTF8.

When I cast this column to BLOB SUB_TYPE 1 the data is shown, and the issue does not happen.

Could you say what could be happening? When I backup and restore the database, no corruption issues are shown in the logs.

Could you help me fix this issue in my database?

Thanks in advanced,

Luciano

[Mark Rotteveel]

My guess is that you used UTF8 as a connection character set. You cannot
get a VARCHAR bigger than 8191 characters in UTF8. When you connect with
UTF8, Firebird attempts to convert that VARCHAR(32765) from WIN1252 to
UTF8, and that fails because it's too long. That is what causes the error.

There is no corruption or anything.

Your options are either to use WIN1252 as the connection character set,
replace the column with a VARCHAR(8191) or BLOB SUB_TYPE TEXT, or, *if*
your driver supports it, use connection character NONE, and let it read
the column as WIN1252.

Note that this last thing only works correctly if
1. All string columns have a character set other than NONE
2. The driver actually uses the character set communicated in the column
and parameter metadata

Unfortunately, especially older drivers don't always do this last thing,
but instead blanket apply the default client character set for all
columns, even if they communicate an explicit column character set other
than NONE.

Mark
--
Mark Rotteveel

[Luciano Rodrigues Nunes Mendes]

Hi Mark, thanks so much for your quick response!

I am using Firebird ADO.NET data provider v10.0.0 to communicate with the server, but the issue also happens with FlameRobin using fbclient.dll v3.0.11.

I believe I need to use Charset=UTF8 in the connection string because this is the database's default charset and there are some table columns using UTF8, although the vast majority of table columns in this database are WIN1252, correct?

Under these conditions, as I understand it, any VARCHAR WIN1252 column with more than 8191 characters, the best way to work around it is to cast it to BLOB TEXT before the query, correct?

Could I use charset=NONE in the connection string to update WIN1252 and UTF8 columns without data loss?

Thanks in advance,

Luciano

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/3b33f98f-4a9e-4dc2-82f8-4bb0dac6de8d%40lawinegevaar.nl.

[Mark Rotteveel]

On 20/02/2024 20:20, Luciano Rodrigues Nunes Mendes wrote:
> I am using Firebird ADO.NET <http://ADO.NET> data provider v10.0.0 to

> communicate with the server, but the issue also happens with FlameRobin
> using fbclient.dll v3.0.11.
>
> I believe I need to use Charset=UTF8 in the connection string because
> this is the database's default charset and there are some table columns
> using UTF8, although the vast majority of table columns in this database
> are WIN1252, correct?

The default character set technically is irrelevant for that decision,
as that only specifies what character set a string column will use if
you don't specify one explicitly when creating it.

However, if most of your columns are UTF8, then yes, using UTF8 as the
connection character set, or NONE if the driver supports using the
actual character set, is the way to go (unless you only store the
WIN1252 subset of characters in the those columns).

> Under these conditions, as I understand it, any VARCHAR WIN1252 column
> with more than 8191 characters, the best way to work around it is to
> cast it to BLOB TEXT before the query, correct?

Any column defined as VARCHAR(n) or CHAR(n) with n > 8191, the actual
character count has no influence on this error. And yes, casting to BLOB
TEXT is the only workaround if you cannot structurally modify the
database to replace those columns with VARCHAR(8191).

> Could I use charset=NONE in the connection string to update WIN1252 and
> UTF8 columns without data loss?

Yes, that should work. If I recall correctly, the Firebird ADO.NET
provider implements the right character set behaviour when connecting
with character set NONE (though I would recommend you double-check
this), but do make sure to that you have no string columns that have
character set NONE specified, otherwise you may logically corrupt data
in those columns by using multiple/different character set encodings
when reading and writing data.

I'm not sure if FlameRobin does the right thing to be honest.

Mark
--
Mark Rotteveel

[Luciano Rodrigues Nunes Mendes]

Hi Mark

Using NONE as a charset in the database connection string I was able to insert, read and update records in WIN1252 and UTF8 columns without character identification errors. The Firebird ADO.NET Provider is able to correctly identify the column charset under these conditions.

Once again, thank you very much for your support.

Best Regards,

Luciano

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/a18c7506-b46b-48a1-b6b4-1afaaf25a486%40lawinegevaar.nl.