With the table definition below my sig I try and alter column _Bands and get this error message

---------------------------
ElevateDB Manager
---------------------------
Field '_xAllText' cannot be modified.
---------------------------
OK  
---------------------------

_xAllText is a computed column. After the warning the data is actually altered - anyone have a suggestion.

I thought it might be that I'm not allowed to catenate CLOBs but a simple select _xAllText works on the data that's there.

Just tried entering data into _Band1. First click on post I get the warning, second click it posts.

Roy Lambert



CREATE TABLE "QSC"
(
"_ID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"_Name" VARCHAR(50) COLLATE "ANSI_CI" NOT NULL,
"_Indicator" VARCHAR(10) COLLATE "ANSI_CI" DEFAULT 'Skill' NOT NULL DESCRIPTION 'Qualifier
Skill
Competency',
"_Description" CLOB COLLATE "ANSI_CI" COMPRESSION 6,
"_Bands" INTEGER DEFAULT 4 NOT NULL,
"_Questions" CLOB COLLATE "ANSI_CI" COMPRESSION 6,
"_Band0" VARCHAR(50) COLLATE "ANSI_CI",
"_Band1" VARCHAR(50) COLLATE "ANSI_CI",
"_Band2" VARCHAR(50) COLLATE "ANSI_CI",
"_Band3" VARCHAR(50) COLLATE "ANSI_CI",
"_Band4" VARCHAR(50) COLLATE "ANSI_CI",
"_Guidance0" CLOB COLLATE "ANSI_CI" COMPRESSION 6,
"_Guidance1" CLOB COLLATE "ANSI_CI" COMPRESSION 6,
"_Guidance2" CLOB COLLATE "ANSI_CI" COMPRESSION 6,
"_Guidance3" CLOB COLLATE "ANSI_CI" COMPRESSION 6,
"_Guidance4" CLOB COLLATE "ANSI_CI" COMPRESSION 6,
"_Development0" CLOB COLLATE "ANSI_CI" COMPRESSION 6,
"_Development1" CLOB COLLATE "ANSI_CI" COMPRESSION 6,
"_Development2" CLOB COLLATE "ANSI_CI" COMPRESSION 6,
"_Development3" CLOB COLLATE "ANSI_CI" COMPRESSION 6,
"_Development4" CLOB COLLATE "ANSI_CI" COMPRESSION 6,
"_xGlyph" INTEGER COMPUTED ALWAYS AS CASE
WHEN _Indicator = 'Qualifier' THEN 0
WHEN _Indicator = 'Skill' THEN 1
WHEN _Indicator = 'Competency' THEN 2
WHEN _Indicator = 'Group' THEN 3
ELSE -1
END NOT NULL,
"_xAllText" CLOB COLLATE "ANSI_CI" COMPRESSION 6 COMPUTED ALWAYS AS CAST(
COALESCE(_Questions,'')+' '+COALESCE(_Name,'')+' '+ COALESCE(_Description,'')+' ' +
COALESCE(_Band0,'')+' '+COALESCE(_Guidance0,'')+' '+COALESCE(_Development0,'')+' '+
COALESCE(_Band1,'')+' '+COALESCE(_Guidance1,'')+' '+COALESCE(_Development1,'')+' '+
COALESCE(_Band2,'')+' '+COALESCE(_Guidance2,'')+' '+COALESCE(_Development2,'')+' '+
COALESCE(_Band3,'')+' '+COALESCE(_Guidance3,'')+' '+COALESCE(_Development3,'')+' '+
COALESCE(_Band4,'')+' '+COALESCE(_Guidance4,'')+' '+COALESCE(_Development4,'') AS CLOB),
"_xBand" CLOB COLLATE "ANSI_CI" COMPRESSION 6 COMPUTED ALWAYS AS CAST(
COALESCE(_Band0,'')+' '+
COALESCE(_Band1,'')+' '+
COALESCE(_Band2,'')+' '+
COALESCE(_Band3,'')+' '+
COALESCE(_Band4,'') AS CLOB),
"_xGuidance" CLOB COLLATE "ANSI_CI" COMPRESSION 6 COMPUTED ALWAYS AS CAST(
COALESCE(_Guidance0,'')+' '+
COALESCE(_Guidance1,'')+' '+
COALESCE(_Guidance2,'')+' '+
COALESCE(_Guidance3,'')+' '+
COALESCE(_Guidance4,'') AS CLOB),
"_xDevelopment" CLOB COLLATE "ANSI_CI" COMPRESSION 6 COMPUTED ALWAYS AS CAST(
COALESCE(_Development0,'')+' '+
COALESCE(_Development1,'')+' '+
COALESCE(_Development2,'')+' '+
COALESCE(_Development3,'')+' '+
COALESCE(_Development4,'') AS CLOB),
CONSTRAINT "PK" PRIMARY KEY ("_ID"),
CONSTRAINT "MaxBands" CHECK ((_Bands >=1 AND _Bands <=4))
,
CONSTRAINT "Names" UNIQUE ("_Name")
)
VERSION 1.00
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 8192
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768

Extra bit of info.

I emptied the table and started entering data into a blank row. As I tabbed between fields I received the error message


Field ... cannot be modified

with the field name advancing as I entered data and tabbed on to the next field so:

1. alter _Name, tab  - _xAllText cannot be modified
2. alter _Descrption tab _xBand cannot be modified
3. alter _Band0 tab - _xGuidance cannot be modified
4. alter Band1 tab - _xDevelopment cannot be modified

Once _xDevelopment has been passed things are fine.

Same applies if I fill in columns in a different sequence. The sequence of "cannot be modified" is the same.

Roy Lambert

Thought it might be down to one of the two triggers on the table - removed them and the problem is still there.

Roy Lambert

Hi Roy & Tim,

I removed all the calculated fields and added a CLOB field which is computed as a string constant.

ALTER TABLE "QSC"
ALTER COLUMN "_xAllText" AS CLOB COLLATE "ANSI_CI" COMPUTED ALWAYS AS 'A' NOT NULL

and received the same error as you did.  It works as expected if the field is changed from a CLOB to a VARCHAR.

This appears to a a Tim issue.

Richard

Richard


>and received the same error as you did. It works as expected if the field is changed from a CLOB to a VARCHAR.

I suspected VARCHAR would work but since this is merging free text entered by users into a dbmemo component a limit of 1024 may not be viable.

Thanks for confirming the problem.

Roy

Roy,

Yeah, that's a bug.  A fix will be in the next build, and I should be getting one out this week.

Tim Young
Elevate Software
www.elevatesoft.com

Tim

Thank you.

Roy Lambert