We have an interesting error using prodict/load_df.p. It is loading in schema that was exported from an 11.6 database, and loading it into an 11.3 database (I mention the versions although I don't think it is particularly relevant as you will see below).
The error is like so: OpenEdge keywords can not be object names.
It happened when a table was created via a sql92 ddl statement. Then later, for an automated build, the entire database was exported with prodict/dump_df.p, and it was failed to be recreated elsewhere with prodict/load_df.p.
Apparently while the related schema was just fine for most purposes, the "load_df.p" utility doesn't like it. I'm not finding the error message in my google searches so I thought I would ask here in the forums.
Here is the full error output from load_df.p
The following errors and/or warnings occurred while loading ABC.df
into database XYZ on 08/07/18 at 12:00.
** Error during ADD FIELD desc **
** Line 10162 ADD FIELD desc OF brn_bank AS character
OpenEdge keywords can not be object names.
And here is the table, as created via sql92:
CREATE TABLE PUB."brn_bank"(
"bank" VARCHAR (20) DEFAULT '' PRO_ORDER 10 PRO_CASE_SENSITIVE 'N'
PRO_FORMAT 'x(8)' PRO_LABEL 'BankCode' PRO_COL_LABEL 'Bank' PRO_HELP 'Enter
Bank Code' ,
"branch" VARCHAR (20) DEFAULT '' PRO_ORDER 20 PRO_CASE_SENSITIVE 'N' PRO_FORMAT
'x(4)' PRO_LABEL 'Branch' PRO_COL_LABEL 'Branch' ,
"ourrecid" VARCHAR (16) DEFAULT '' PRO_ORDER 30 PRO_CASE_SENSITIVE 'N'
PRO_FORMAT 'x(16)' PRO_LABEL 'OURRECID' PRO_COL_LABEL 'OURRECID' ,
"owner" INTEGER DEFAULT '0' PRO_ORDER 40 PRO_FORMAT '99' PRO_LABEL 'OWNER'
PRO_COL_LABEL 'OWNER' ,
"desc" VARCHAR (20) DEFAULT '' PRO_ORDER 50 PRO_CASE_SENSITIVE 'N' PRO_FORMAT
'x(40)' PRO_LABEL 'Desc' PRO_COL_LABEL 'Desc' )
AREA "misc128" PRO_HIDDEN 'N' PRO_DESCRIPTION 'Bank Branch Table' PRO_DUMP_NAME
GRANT ALL ON PUB."brn_bank" TO PUBLIC;
CREATE INDEX "ourrecid" ON PUB."brn_bank" ("ourrecid" ASC) AREA "misc128_idx"
PRO_DESCRIPTION '' PRO_ACTIVE 'y';
CREATE UNIQUE INDEX "brn_bank_01" ON PUB."brn_bank" ("bank" ASC , "branch" ASC)
AREA "misc128_idx" PRO_DESCRIPTION '' PRO_ACTIVE 'y';
ALTER TABLE PUB."brn_bank" SET PRO_DEFAULT_INDEX "brn_bank_01";
And here is the DF, as generated by dump_df:
ADD TABLE "brn_bank"
DESCRIPTION "Bank Branch Table"
ADD FIELD "bank" OF "brn_bank" AS character
ADD FIELD "branch" OF "brn_bank" AS character
ADD FIELD "ourrecid" OF "brn_bank" AS character
ADD FIELD "owner" OF "brn_bank" AS integer
ADD FIELD "desc" OF "brn_bank" AS character
ADD INDEX "brn_bank_01" ON "brn_bank"
INDEX-FIELD "bank" ASCENDING
INDEX-FIELD "branch" ASCENDING
ADD INDEX "ourrecid" ON "brn_bank"
INDEX-FIELD "ourrecid" ASCENDING
Hope this is all clear. I guess the moral of the story is that we should not use reserved keywords for columns in our OpenEdge database. It would be nice if OpenEdge would restrict us earlier so we wouldn't run into unexpected problems down the road...
OE sql did once have a feature related to what you are describing, checking for ABL keywords. This was quite a few releases ago.
OE sql had an ABL keyword detection (for table/column names) once, many releases ago. We had quite a few comments, from customers and/or TechSupport, that this detection had problems because many ABL users run with a custom “keyword forget list” . So, for those ABL customers, blocking an ABL keyword would be hurtful. And my recollection is that the ABL customer could use the keyword forget list sometimes and not use it othertimes, which makes ABL keyword detection for sql even more complex.
Plus, as Gus described, the sql standard requires OE sql to have delimited identifiers which must allow any identifier as a name.
So, for these reasons , we removed the ABL keyword detection that we had implemented.
Hope this helps.
.....steve pittman [OE sql software architect]
The error is not specific to any OE version. The load_df utility checks if the object name is an ABL keyword, and if so, throws this error. In this case, "desc" is a short-form of DESCENDING and hence the appropriate error.
I will check if the ABL keyword checks should be enforced in the dump_df and SQL utilities also.
An alternative to load_df is to use ANT/PCT. We've got some quite nifty uses for this. Makes DF loading very easy to do.
rkumar, my preference would be that the SQL92 DDL statements would enforce the same validation rules as the corresponding DF. Unfortunately many of our datadictionary operations in Progress are still accomplished via the old-school DF files. (I've found that schema-dumps/reloads rarely work properly with the SQL92 schema.)
In short, it seems unwise that the SQL92 statements allow users to subvert the normal validation rules that have always existed in the DF (presumably keywords have previously been restricted for a reason).
James, doesn't ANT/PCT rely on the same internal components as dump_df and load_df? I was googling a bit and discovered the ADE development tools code, and my understanding is that it is the basis for dump_df and load_df. And it is probably the basis for whatever is happening deep within ANT/PCT. I would bet that if you used my SQL92 ddl, to include the "brn_bank" table in your schema, then your ANT/PCT would probably choke and die on it too. Basically that one column in the table will prevent an entire empty database from being created via intermediate DF.
One workaround for this problem is to have keyword forget list with the keywords that are used in the .df file. For example, create a file ignore.txt with "DESC" as its content. Provide this file during the session startup using the "-k" parameter and then load the .df file:
_progres <dbname> -1 -p _admin.p -k ignore.txt
That's a pretty good find. I had no idea that there was a way to override keywords. Were you able to confirm my suspicion that SQL92 has its own distinct list of keywords which are restricted for data dictionary purposes? It seems unfortunate that it would be a different list. I suppose it may be worth creating a support case for this, in order to get a combined list of keywords enforced on each side. There should probably be a KB for this. Then we could manually restrict the keywords ourselves.
SQL-92 does not have its own list of keywords which are disallowed. The
SQL language does not impose any restrictions on what words can be used
in table and column names. Instead, SQL has the concept of "delimited
identifiers". Whenever you have a name that conflicts with the SQL
language, you can enclose it in double quotes. So you can have a table
named SELECT and use it thusly:
SELECT * FROM "SELECT";
Also, in SQL, hyphens are used differently than in the 4GL. A table or
column name such as A-B must be delimited (enclosed in quotes) because
the SQL parser will treat is a subtraction, reading it as A - B.
I have had to delimit a lot of hyphenated field names. Our legacy OE schema has tons of field names that used hyphens.
Even if the sql language allows delimited keywords, the OE DBMS should protect itself from bad DDL statements. Any DDL statement should be disallowed if it produces a schema which will be internally inconsistent and unacceptable to the rest of the OE DBMS management tools from Progress.
Yes, thanks for the additional information. The "keyword-forget" stuff is news to me. I am somewhat surprised by it. My experience with other products has been different. Typically keywords are protected by software vendors for legitimate reasons and users shouldn't be appropriating them and subverting the original purpose.
It sounds like SQL92 is "keyword-forgetting" and that means users have to manually try to recall and avoid the entire list of keywords. In the very least it seems like there should be a checkbox in the PDSOE schema-design wizards to avoid keywords if they will cause a conflict in the OE data management tools.
Steve, wouldn't it have been a good idea to provide the detection as an option, i.e., like a "strict" mode? And, better yet, make it also pay attention to the keyword forget list for compatibility.
Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice http://www.cintegrity.com
Sorry for the delayed reply!
Yes, the idea of some optional keyword checking is interesting, if requested by the user.
We were told that the keyword forget list might not be used uniformly for all databases/applications, so making any use of a keyword-forget-list by the OE sql server (where list would apply to all) seemed not so feasible.
Thanks, ....steve pittman