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
Non-flexible dependance of procedures using TYPE OF COLUMN [CORE3505] #3863
Comments
Commented by: @aafemt Changing definition of domain must be denied if this domain is used anywhere (including via TYPE OF). |
Commented by: @dyemanov Does this issue exist for TYPE OF COLUMN *input* parameters? |
Commented by: Michal Dohnal (mixerito) Dimitry S: Dmitry Y: |
Modified by: @asfernandesassignee: Adriano dos Santos Fernandes [ asfernandes ] |
Commented by: @hvlad I'm sure that all you need is to reconnect, assuming you run DDL in single attachment as required by IB6 docs. |
Modified by: Sean Leyne (seanleyne)description: All dependant procedures using TYPE OF COLUMN, where column is defined by domain, need to be manually recompiled, when the definition of domain is changed. Example: -- Let's have 2 different domains CREATE DOMAIN D_VARCHAR10 AS CREATE DOMAIN D_VARCHAR40 AS -- In time of creating table, we presume 10 characters should be enough CREATE TABLE NEW_TABLE ( -- We create some read procedure CREATE OR ALTER PROCEDURE NEW_PROC -- Now, we need more space ALTER TABLE NEW_TABLE ALTER FIELD TYPE D_VARCHAR40; -- Let's insert some long record INSERT INTO NEW_TABLE -- And then we try to read all records of the table using the read procedure SELECT * FROM NEW_PROC; -- So we get this exception /*Arithmetic overflow or division by zero has occurred. I can mention that, Backup and Restore doesn't help. I am aware of that, some software like IB Expert offer to recompile all depenadant procedures, => All dependant procedures using TYPE OF COLUMN, where column is defined by domain, need to be manually recompiled, when the definition of domain is changed. Example: -- Let's have 2 different domains CREATE DOMAIN D_VARCHAR40 AS -- In time of creating table, we presume 10 characters should be enough -- We create some read procedure -- Now, we need more space -- Let's insert some long record -- And then we try to read all records of the table using the read procedure -- So we get this exception I can mention that, Backup and Restore doesn't help. I am aware of that, some software like IB Expert offer to recompile all depenadant procedures, but it gets complicated in some situations like differential updating on customers databases. |
Commented by: Michal Dohnal (mixerito) Reconnecting was the first desperate step, followed by the Backup and Restore, I had tried before i published this issue. |
Commented by: @asfernandes > Reconnecting was the first desperate step The issue is not reproducible by me. |
Commented by: Michal Dohnal (mixerito) It's strange. Has anybody else tried to reproduce this issue ? I and co-workers can raise it repeatedly. Would you need some another information necessary to resolve ? |
Commented by: @dyemanov I've just tried the provided test case. The issue is reproducible if everything runs in the same connection. If you reconnect after getting the mentioned error, the problem disappears. I was testing with Classic Server. |
Commented by: Michal Dohnal (mixerito) Ok, sorry, I've simplified the example. In the mentioned situation, where the reconnect and Backup and Restore doesn't help, was a little change. Instead of using ALTER TABLE NEW_TABLE ALTER FIELD TYPE D_VARCHAR40; i really used direct change in RDB table (as provided by IB Expert during manual change): update RDB$RELATION_FIELDS set So this is the exact problem. |
Commented by: @dyemanov I'd say that we shouldn't care about direct system table updates ;-) |
Submitted by: Michal Dohnal (mixerito)
All dependant procedures using TYPE OF COLUMN, where column is defined by domain, need to be manually recompiled, when the definition of domain is changed.
Example:
-- Let's have 2 different domains
CREATE DOMAIN D_VARCHAR10 AS
VARCHAR(10) CHARACTER SET WIN1250
COLLATE PXW_CSY ;
CREATE DOMAIN D_VARCHAR40 AS
VARCHAR(40) CHARACTER SET WIN1250
COLLATE PXW_CSY ;
-- In time of creating table, we presume 10 characters should be enough
CREATE TABLE NEW_TABLE (
ID INTEGER,
FIELD D_VARCHAR10);
-- We create some read procedure
CREATE OR ALTER PROCEDURE NEW_PROC
RETURNS(
FIELD TYPE OF COLUMN NEW_TABLE.FIELD)
AS
BEGIN
FOR SELECT FIELD
FROM NEW_TABLE
INTO :FIELD
DO
SUSPEND;
END;
-- Now, we need more space
ALTER TABLE NEW_TABLE ALTER FIELD TYPE D_VARCHAR40;
-- Let's insert some long record
INSERT INTO NEW_TABLE
(ID,FIELD) VALUES (1,'I am longer than 10 characters');
-- And then we try to read all records of the table using the read procedure
SELECT * FROM NEW_PROC;
-- So we get this exception
/*Arithmetic overflow or division by zero has occurred.
arithmetic exception, numeric overflow, or string truncation.
string right truncation.
At procedure 'NEW_PROC' line: 6, col: 3.*/
I can mention that, Backup and Restore doesn't help.
I am aware of that, some software like IB Expert offer to recompile all depenadant procedures, but it gets complicated in some situations like differential updating on customers databases.
The text was updated successfully, but these errors were encountered: