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

Using domains in psql dont permit implicit casts as with standard types [CORE1353] #736

Closed
firebird-automations opened this issue Jul 10, 2007 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Alexandre Moradell (amoradellc)

I tried to use this new feature with procedure all_langs. see below standard

SET TERM ^ ;
ALTER PROCEDURE ALL_LANGS
RETURNS (
CODE Varchar(5),
GRADE Varchar(5),
COUNTRY Varchar(15),
LANG Varchar(15) )
AS
BEGIN
FOR SELECT JOB_CODE, JOB_GRADE, JOB_COUNTRY FROM JOB
INTO :CODE, :GRADE, :COUNTRY
DO
BEGIN
FOR SELECT LANGUAGES FROM SHOW_LANGS
(:CODE, :GRADE, :COUNTRY) INTO :LANG DO
SUSPEND;
/* PUT NICE SEPARATORS BETWEEN ROWS */
CODE = '=====';
GRADE = '=====';
COUNTRY = '===============';
LANG = '==============';
SUSPEND;
END
END^
SET TERM ; ^

I change CODE type from varchar(5) to JOBCODE and COUNTRY from varchar(15) to COUNTRYNAME and it's ok

ALTER PROCEDURE ALL_LANGS
RETURNS (
CODE JOBCODE,
GRADE Varchar(5),
COUNTRY COUNTRYNAME,
LANG Varchar(15) )
AS
BEGIN
..
End

but when I change GRADE from varchar(5) to JOBCODE, I got an error because the implicit cast from smallint (jobgrade type) to varchar(5) doesnt work

ALTER PROCEDURE ALL_LANGS
RETURNS (
CODE JOBCODE,
GRADE JOBCODE,
COUNTRY COUNTRYNAME,
LANG Varchar(15) )

SQL> select * from all_langs;

CODE GRADE COUNTRY LANG
====== ====== =============== ===============
Statement failed, SQLCODE = -625
validation error for variable GRADE, value "1"
-At procedure 'ALL_LANGS' line: 10, col: 3
SQL>

@firebird-automations
Copy link
Collaborator Author

Commented by: Alexandre Moradell (amoradellc)

update job set jobgrade2=job_grade;
commit;

ALTER PROCEDURE ALL_LANGS
RETURNS (
CODE JOBCODE,
GRADE JOBCODE,
COUNTRY COUNTRYNAME,
LANG Varchar(15) )
AS
BEGIN
FOR SELECT JOB_CODE, JOBGRADE2, JOB_COUNTRY FROM JOB
INTO :CODE, :GRADE, :COUNTRY

SQL> select * from all_langs;

CODE GRADE COUNTRY LANG
====== ====== =============== ===============
Statement failed, SQLCODE = -625
validation error for variable GRADE, value " "
-At procedure 'ALL_LANGS' line: 10, col: 3
SQL>

I create a domain similar to JOBCODE but without check

CREATE DOMAIN JOBCODEVC5 AS VARCHAR(5);

ALTER PROCEDURE ALL_LANGS
RETURNS (
CODE JOBCODE,
GRADE JOBCODEVC5,
COUNTRY COUNTRYNAME,
LANG Varchar(15) )
AS
BEGIN
FOR SELECT JOB_CODE, JOB_GRADE, JOB_COUNTRY FROM JOB
INTO :CODE, :GRADE, :COUNTRY

and no problem !

the error is throwed by JOBCODE check clause : CHECK (VALUE > '99999')

so I don't know if it's really a bug !
when you choose a domain for a variable, you have to be careful.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

This is as designed.
If you don't want to apply check constraints, use TYPE OF syntax. Ex:
GRADE TYPE OF JOBCODE

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Alexandre Moradell (amoradellc)

Ok thanks, I should have read the release notes more thoroughly !
Using domains could be a real time saver when you verify data.
Just add check constraint (evident ones : positive number, dates after 01/01/1900) and execute procedures, if it fails, there's something wrong.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 12533 ] => Firebird [ 14158 ]

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