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

Problem with updatable view when the source field type not exact the field to store the data. (extra trailing spaces are added by firebird) [CORE3597] #3951

Open
firebird-automations opened this issue Sep 13, 2011 · 4 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Sascha Michel (datiscum1)

When you create the database and execute the insert command you will get an error message.

INSERT INTO "VW_Benutzer" ("UserName") VALUES ('ABC');
This insert will not work.

Error Message:
----------------------------------------
The insert failed because a column definition includes validation constraints.
validation error for column UserName, value "ABC ". <-- The extra trailing spaces are added by firebird
At trigger 'VW_Benutzer_BI' line: 5, col: 3.

INSERT INTO "VW_Benutzer" ("UserName") VALUES ('ABCDEFGHIJ')
This insert will work.

The number of spaces appended seems to be arbitrary, it is not related to any field length. Firebird should never append spaces like that.

SET SQL DIALECT 3;
SET NAMES UTF8;

CREATE DATABASE '192.168.1.50:/DB/Check.fdb'
USER 'SYSDBA' PASSWORD 'XXXXXX'
PAGE_SIZE 16384
DEFAULT CHARACTER SET UTF8 COLLATION UTF8;

CREATE DOMAIN "SystemBenutzerNamen" AS
VARCHAR(31) CHARACTER SET UTF8
CHECK (value similar to '[A-Z]{1}[0-9|A-Z|_]{1,30}');

CREATE TABLE "Benutzer" (
"UserName" "SystemBenutzerNamen" NOT NULL,
"Bn_ASP_ID" INTEGER,
"Bn_Paswsort" VARCHAR(20) CHARACTER SET UTF8,
"Bn_DefaultMandantGP_ID" INTEGER,
"Bn_DefaultUserLanguage" VARCHAR(20) CHARACTER SET UTF8
);

/* View: "VW_Benutzer" */
CREATE OR ALTER VIEW "VW_Benutzer"(
"UserName",
"Bn_ASP_ID",
"Bn_Paswsort")
AS
select distinct rdb$user , "Bn_ASP_ID" , "Bn_Paswsort" from "RDB$USER_PRIVILEGES" A
left outer join "Benutzer" B ON A.rdb$user = B."UserName"
;

ALTER TABLE "Benutzer" ADD CONSTRAINT "UNQ_Benutzer_ASP" UNIQUE ("Bn_ASP_ID");

ALTER TABLE "Benutzer" ADD CONSTRAINT "PK_Benutzer" PRIMARY KEY ("UserName");

SET TERM ^ ;

/* Trigger: "VW_Benutzer_BD" */
CREATE OR ALTER TRIGGER "VW_Benutzer_BD" FOR "VW_Benutzer"
ACTIVE BEFORE DELETE POSITION 0
as
begin
delete from "Benutzer"
where ("UserName" = old."UserName");
end
^

/* Trigger: "VW_Benutzer_BI" */
CREATE OR ALTER TRIGGER "VW_Benutzer_BI" FOR "VW_Benutzer"
ACTIVE BEFORE INSERT POSITION 0
as
begin
insert into "Benutzer" (
"UserName",
"Bn_ASP_ID",
"Bn_Paswsort")
values (
upper(new."UserName"),
new."Bn_ASP_ID",
new."Bn_Paswsort");
end
^

/* Trigger: "VW_Benutzer_BU" */
CREATE OR ALTER TRIGGER "VW_Benutzer_BU" FOR "VW_Benutzer"
ACTIVE BEFORE UPDATE POSITION 0
as
begin
update "Benutzer"
set "UserName" = new."UserName",
"Bn_ASP_ID" = new."Bn_ASP_ID",
"Bn_Paswsort" = new."Bn_Paswsort"
where ("UserName" = old."UserName");
end
^

SET TERM ; ^

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

> The number of spaces appended seems to be arbitrary, it is not related to any field length. Firebird should never append spaces like that.

You're not very correct.

Note how is your view:

SQL> show view "VW_Benutzer";
UserName (RDB$USER) CHAR(31) CHARACTER SET UNICODE_FSS Nullable

And

SQL> show domain RDB$USER;
RDB$USER CHAR(31) CHARACTER SET UNICODE_FSS Nullable

When you insert a record in it, the pad is done, as it's a CHAR column, and it's the way it works.

But the correct would be to see it padded as 31 characters. This problem is related to internal storage of data using system domains.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Sascha Michel (datiscum1)

If the field had been padded to 31 spaces, I have not seen a BUG.

I think it should be fixed.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

And it will be padded as 31 spaces when metadata is switched to UTF-8.

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

2 participants