Can store 11 chars in VARCHAR(10) Field?
Mathias Pannier (unitel)
Hello,
I can store 11 characters in a VARCHAR(10) field which causes an error while reading this field from Delphi/FireDAC.
Firebird: 3.0.4
Database : UTF8
Table:
CREATE TABLE XYZ (TEST VARCHAR(10));
Insert data:
insert into XYZ (TEST) values ('0123456789 ');
Note the “space-char” after the “9”.
Select data:
select TEST, char_length(TEST) from XYZ;
Result:
TEST CHAR_LENGTH
0123456789 11
The following insert raise an error which is correct:
insert into XYZ (TEST) values ('01234567890');
But this works:
insert into XYZ (TEST) values ('0123456789 ');
I can add as many spaces as I want.
I believe the “spaces” are stored in the field. If I select data from this table from my Delphi application, I get a string overflow error. Delphi/FireDAC reads the meta data of the table (10 Characters). While reading the real data it gets 11 characters which will raise an exception.
Is this a bug in Firebird? Should the insert with spaces at the end raise an error?
Regards
Mathias Pannier
Geschaeftsfuehrung Klaus Richter, Olaf Meyer
Amtsgericht Stendal
HRB 26389 FA Bitterfeld Steuernr. 116/107/08597 Ust.identNr. DE815796778
Deutsche Bank IBAN DE53 86070024 0 6143234 00
Kreissparkasse Anhalt-Bitterfeld IBAN DE69 80053722 0 3050326 82
_____________________________________________________________________
Dieses E-Mail ist nur für den Empfänger bestimmt, an den es gerichtet
ist und kann vertrauliches bzw. unter das Berufsgeheimnis fallendes
Material enthalten. Jegliche darin enthaltene Ansicht oder Meinungs-
äußerung ist die des Autors und stellt nicht notwendigerweise die
Ansicht oder Meinung von ub.unitel GmbH dar.
Sind Sie nicht der Empfänger, so haben Sie diese E-Mail irrtümlich
erhalten und jegliche Verwendung, Veröffentlichung, Weiterleitung,
Abschrift oder jeglicher Druck dieser E-Mail ist strengstens untersagt.
_____________________________________________________________________
Dimitry Sibiryakov
> Firebird: 3.0.4
This version is significantly outdated. Latest release is 3.0.11 and testing
on snapshot is always recommended in the case of found bug.
--
WBR, SD.
Stefano Iraci
Hi Mathias,
a workaround for this bug could be a trigger before insert in
which apply the trim to the field value, eg. TEST = TRIM(TEST);
_____________________________________________________________________ --
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/1d6b31f43c2748d7913c2812009efec9%40ubsysteme.de.
Stefano Iraci
Via Carlo Bini, 36
57126 Livorno (LI)
Phone: +39 0586 881 336
www.mccinformatica.it
Mathias Pannier (unitel)
Hello,
thank You for the fast response. This could be a work around. My “fast” solution was to trim the insert in Delphi code.
Regards
Mathias
Von: firebird...@googlegroups.com <firebird...@googlegroups.com>
Im Auftrag von Stefano Iraci
Gesendet: Dienstag, 28. Mai 2024 11:16
An: firebird...@googlegroups.com
Betreff: Re: [firebird-support] Can store 11 chars in VARCHAR(10) Field?
ACHTUNG: Diese E-Mail stammt von einem externen Absender. Bitte vermeiden Sie es, Anhänge oder externe Links zu öffnen.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/83cffe64-4227-47f6-8a09-b1b4dfd94173%40mccinfo.it.
Mathias Pannier (unitel)
thank You for the fast response. You are right. I will try version 3.0.11
Regards
Mathias
-----Ursprüngliche Nachricht-----
Von: 'Dimitry Sibiryakov' via firebird-support <firebird...@googlegroups.com>
Gesendet: Dienstag, 28. Mai 2024 11:10
Betreff: Re: [firebird-support] Can store 11 chars in VARCHAR(10) Field?
ACHTUNG: Diese E-Mail stammt von einem externen Absender. Bitte vermeiden Sie es, Anhänge oder externe Links zu öffnen.
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.
Mathias Pannier (unitel)
tested now with latest 3.0.11. It is possible to write an insert with more than 10 characters (spaces) without errors. But it will store only 10 characters.
Here is my listing from cmd.exe
C:\Program Files (x86)\Firebird\Firebird_3_0>isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> CREATE DATABASE 'localhost:c:\Temp\Test.FDB'
CON> USER 'SYSDBA' PASSWORD 'masterkey'
CON> PAGE_SIZE 16384
CON> DEFAULT CHARACTER SET UTF8 COLLATION UTF8;
SQL> CREATE TABLE XYZ (TEST VARCHAR(10));
SQL> commit;
SQL> insert into XYZ (TEST) values ('0123456789 ');
SQL> commit;
SQL> select TEST, char_length(TEST) from XYZ;
TEST CHAR_LENGTH
========== ============
0123456789 10
SQL> insert into XYZ (TEST) values ('01234567890');
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 10, actual 11
SQL>
Regards
Mathias
-----Ursprüngliche Nachricht-----
Von: firebird...@googlegroups.com <firebird...@googlegroups.com> Im Auftrag von Mathias Pannier (unitel)
Gesendet: Dienstag, 28. Mai 2024 11:21
An: firebird...@googlegroups.com
Betreff: AW: [firebird-support] Can store 11 chars in VARCHAR(10) Field?
Dimitry Sibiryakov
> tested now with latest 3.0.11. It is possible to write an insert with
> more than 10 characters (spaces) without errors. But it will store only
> 10 characters.
trailing spaces are ignored if possible.
--
WBR, SD.
Mark Rotteveel
which means that if it contains 10 ASCII characters, it will be padded
with 30 spaces, and the length check ignores those trailing spaces, so
it cannot distinguish between having 10 non-space digits and 10
non-space digits followed by a space.
Formally it is a bug, because the ignoring of trailing spaces should
only come into play when *comparing* values, but because Firebird pads
the byte length with 0x20 (space), it cannot distinguish that here.
Mark
--
Mark Rotteveel