Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Firebird 2.5.1 UTF8 database, WIN1250 client problem [CORE4404] #4726

Open
firebird-automations opened this issue Apr 25, 2014 · 4 comments
Open

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

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 (http://NEW.id IS NULL) THEN http://NEW.id = 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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Commented by: @asfernandes

Cannot reproduce in latest 2.5. Please test it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant