Firebird 2.5.1 UTF8 database, WIN1250 client problem [CORE4404]

[firebird-automations]

Submitted by: Semi (semi)

Stored procedure created by WIN1250 client uses incorrect size requirements for calling another procedure declared with UTF8 parameters:

create domain D_VARCHAR16 varchar(16) character set UTF8 collate UNICODE_CI;

set term ^;

create procedure Test1(A type of D_VARCHAR16) as begin if (A='') then begin end end^

create procedure Test2a as begin -- this works execute procedure Test1('Test'); end^

create procedure Test2b as begin -- this does not work execute procedure Test1('Test1'); end^

set term ;^

execute procedure Test2a; execute procedure Test2b;

Error message: arithmetic exception, numeric overflow, or string truncation, string right truncation, At procedure 'TEST2B' line: 5, col: 3

in procedure BLR there is: blr_literal, blr_text2, 51,0, 5,0, 'T','e','s','t','1',

This requires 4x the amount of characters for converting from WIN1250 (charset 51) to UTF8, so it requires 20 characters, while there is 16 characters available. Actually it needs 5 characters only ! The very same problem occurs with "execute block" used instead of Test2b procedure...

This makes UTF8 character set practically unusable by WIN1250 client application, even for plain ascii values !

(Server version 2.5.1, client has got fixed lc_ctype WIN1250 ...)

Surprisingly, using client with UTF8 charset causes the same bug. BLR then reads: blr_literal, blr_text2, 4,0, 5,0, 'T','e','s','t','1',

Error message is same: string truncation in Test2b, and no error in Test2a ... This makes UTF8 charset unusable at all in FB 2.5.1 ...

[firebird-automations]

Modified by: Semi (semi)

description: Hi\! I've got two tables, and one of them has a trigger, which insert or update data to the other table in case of inserting\. The followong SQL gives error when I connect with WIN1250, but works fine with UTF8\. If I'm not mistaken the client side charset sould not affect the result\. INSERT INTO sz\_felirat\_szall \(id, nyelv\_kod, mezonev, ertek\) VALUES \(2065664, '00', 'LEV\_SKOMP', 'Szállítói tartozásunk kiegyenlítése'\) Error : "arithmetic exception, numeric overflow, or string truncation\. string right truncation\. At trigger 'INSERTSZ\_FELIRAT\_SZALL' line: 17, col: 3\." CREATE TABLE SZ\_FELIRAT \( NYELV\_KOD XVAR2N /\* XVAR2N = VARCHAR\(2\) NOT NULL \*/, \.\.\. LEV\_SKOMP XVAR40 /\* XVAR40 = VARCHAR\(40\) \*/, \.\.\. \); CREATE TABLE SZ\_FELIRAT\_SZALL \( ID XIDN /\* XIDN = INTEGER NOT NULL \*/, NYELV\_KOD XVAR2N DEFAULT '00' /\* XVAR2N = VARCHAR\(2\) NOT NULL \*/, MEZONEV XVAR30N /\* XVAR30N = VARCHAR\(30\) NOT NULL \*/, ERTEK XVAR100N /\* XVAR100N = VARCHAR\(100\) NOT NULL \*/ \); CREATE OR ALTER TRIGGER insertsz\_felirat\_szall FOR sz\_felirat\_szall ACTIVE BEFORE INSERT POSITION 0 AS DECLARE VARIABLE ins VARCHAR\(300\); DECLARE VARIABLE db INTEGER DEFAULT 0; BEGIN IF \( IS NULL\) THEN = GEN\_ID\(xid\_gen, 1\); IF \(NEW\.nyelv\_kod IS NULL\) THEN NEW\.nyelv\_kod = '00'; SELECT COUNT\(1\) FROM sz\_felirat WHERE nyelv\_kod = NEW\.nyelv\_kod INTO :db; IF \(db = 0\) THEN INSERT INTO sz\_felirat \(nyelv\_kod\) VALUES \(NEW\.nyelv\_kod\); ins = 'update sz\_felirat set ' \|\| NEW\.mezonev \|\| '=''' \|\| REPLACE\(NEW\.ertek, '''', ''''''\) \|\| ''' where nyelv\_kod = ''' \|\| NEW\.nyelv\_kod \|\| ''' and coalesce\(' \|\| NEW\.mezonev \|\| ', ''''\) = '''''; EXECUTE STATEMENT ins; END =\> Stored procedure created by WIN1250 client uses incorrect size requirements for calling another procedure declared with UTF8 parameters: create domain D\_VARCHAR16 varchar\(16\) character set UTF8 collate UNICODE\_CI; set term ^; create procedure Test1\(A type of D\_VARCHAR16\) as begin if \(A=''\) then begin end end^ create procedure Test2a as begin \-\- this works execute procedure Test1\('Test'\); end^ create procedure Test2b as begin \-\- this does not work execute procedure Test1\('Test1'\); end^ set term ;^ execute procedure Test2a; execute procedure Test2b; Error message: arithmetic exception, numeric overflow, or string truncation, string right truncation, At procedure 'TEST2B' line: 5, col: 3 in procedure BLR there is: blr\_literal, blr\_text2, 51,0, 5,0, 'T','e','s','t','1', This requires 4x the amount of characters for converting from WIN1250 \(charset 51\) to UTF8, so it requires 20 characters, while there is 16 characters available\. Actually it needs 5 characters only \! This makes UTF8 character set practically unusable by WIN1250 client application, even for plain ascii values \! \(Server version 2\.5\.1, client has got fixed lc\_ctype WIN1250 \.\.\.\)

[firebird-automations]

Modified by: Semi (semi)

description: Stored procedure created by WIN1250 client uses incorrect size requirements for calling another procedure declared with UTF8 parameters: create domain D\_VARCHAR16 varchar\(16\) character set UTF8 collate UNICODE\_CI; set term ^; create procedure Test1\(A type of D\_VARCHAR16\) as begin if \(A=''\) then begin end end^ create procedure Test2a as begin \-\- this works execute procedure Test1\('Test'\); end^ create procedure Test2b as begin \-\- this does not work execute procedure Test1\('Test1'\); end^ set term ;^ execute procedure Test2a; execute procedure Test2b; Error message: arithmetic exception, numeric overflow, or string truncation, string right truncation, At procedure 'TEST2B' line: 5, col: 3 in procedure BLR there is: blr\_literal, blr\_text2, 51,0, 5,0, 'T','e','s','t','1', This requires 4x the amount of characters for converting from WIN1250 \(charset 51\) to UTF8, so it requires 20 characters, while there is 16 characters available\. Actually it needs 5 characters only \! This makes UTF8 character set practically unusable by WIN1250 client application, even for plain ascii values \! \(Server version 2\.5\.1, client has got fixed lc\_ctype WIN1250 \.\.\.\) =\> Stored procedure created by WIN1250 client uses incorrect size requirements for calling another procedure declared with UTF8 parameters: create domain D\_VARCHAR16 varchar\(16\) character set UTF8 collate UNICODE\_CI; set term ^; create procedure Test1\(A type of D\_VARCHAR16\) as begin if \(A=''\) then begin end end^ create procedure Test2a as begin \-\- this works execute procedure Test1\('Test'\); end^ create procedure Test2b as begin \-\- this does not work execute procedure Test1\('Test1'\); end^ set term ;^ execute procedure Test2a; execute procedure Test2b; Error message: arithmetic exception, numeric overflow, or string truncation, string right truncation, At procedure 'TEST2B' line: 5, col: 3 in procedure BLR there is: blr\_literal, blr\_text2, 51,0, 5,0, 'T','e','s','t','1', This requires 4x the amount of characters for converting from WIN1250 \(charset 51\) to UTF8, so it requires 20 characters, while there is 16 characters available\. Actually it needs 5 characters only \! The very same problem occurs with "execute block" used instead of Test2b procedure\.\.\. This makes UTF8 character set practically unusable by WIN1250 client application, even for plain ascii values \! \(Server version 2\.5\.1, client has got fixed lc\_ctype WIN1250 \.\.\.\)

[firebird-automations]

Modified by: Semi (semi)

description: Stored procedure created by WIN1250 client uses incorrect size requirements for calling another procedure declared with UTF8 parameters: create domain D\_VARCHAR16 varchar\(16\) character set UTF8 collate UNICODE\_CI; set term ^; create procedure Test1\(A type of D\_VARCHAR16\) as begin if \(A=''\) then begin end end^ create procedure Test2a as begin \-\- this works execute procedure Test1\('Test'\); end^ create procedure Test2b as begin \-\- this does not work execute procedure Test1\('Test1'\); end^ set term ;^ execute procedure Test2a; execute procedure Test2b; Error message: arithmetic exception, numeric overflow, or string truncation, string right truncation, At procedure 'TEST2B' line: 5, col: 3 in procedure BLR there is: blr\_literal, blr\_text2, 51,0, 5,0, 'T','e','s','t','1', This requires 4x the amount of characters for converting from WIN1250 \(charset 51\) to UTF8, so it requires 20 characters, while there is 16 characters available\. Actually it needs 5 characters only \! The very same problem occurs with "execute block" used instead of Test2b procedure\.\.\. This makes UTF8 character set practically unusable by WIN1250 client application, even for plain ascii values \! \(Server version 2\.5\.1, client has got fixed lc\_ctype WIN1250 \.\.\.\) =\> Stored procedure created by WIN1250 client uses incorrect size requirements for calling another procedure declared with UTF8 parameters: create domain D\_VARCHAR16 varchar\(16\) character set UTF8 collate UNICODE\_CI; set term ^; create procedure Test1\(A type of D\_VARCHAR16\) as begin if \(A=''\) then begin end end^ create procedure Test2a as begin \-\- this works execute procedure Test1\('Test'\); end^ create procedure Test2b as begin \-\- this does not work execute procedure Test1\('Test1'\); end^ set term ;^ execute procedure Test2a; execute procedure Test2b; Error message: arithmetic exception, numeric overflow, or string truncation, string right truncation, At procedure 'TEST2B' line: 5, col: 3 in procedure BLR there is: blr\_literal, blr\_text2, 51,0, 5,0, 'T','e','s','t','1', This requires 4x the amount of characters for converting from WIN1250 \(charset 51\) to UTF8, so it requires 20 characters, while there is 16 characters available\. Actually it needs 5 characters only \! The very same problem occurs with "execute block" used instead of Test2b procedure\.\.\. This makes UTF8 character set practically unusable by WIN1250 client application, even for plain ascii values \! \(Server version 2\.5\.1, client has got fixed lc\_ctype WIN1250 \.\.\.\) Surprisingly, using client with UTF8 charset causes the same bug\. BLR then reads: blr\_literal, blr\_text2, 4,0, 5,0, 'T','e','s','t','1', Error message is same: string truncation in Test2b, and no error in Test2a \.\.\. This makes UTF8 charset unusable at all in FB 2\.5\.1 \.\.\.

[firebird-automations]

Commented by: @asfernandes

Cannot reproduce in latest 2.5. Please test it.