Kbase P127306: SQL-92: How to identify the view causing errors when loading multiple views from an SQL-92 script?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
30/11/2007 |
|
Status: Unverified
GOAL:
SQL-92: How to identify the view causing errors when loading multiple views from an SQL-92 script?
GOAL:
How to diagnose errors generated while creating views using a .dfsql that was generated by the SQLSCHEMA Utility?
GOAL:
How to diagnose SQL-92 errors like error (7520) and found (7519)generated when creating views using an .sql script generated by the SQLSCHEMA Utility or another tool?
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
The following ABL/4GL procedure, DiagnoseViewCreationScriptErrors.p, takes the original SQL-92 VIEW creation script file generated by the SQLSCHEMA Utility, OriginalViewsDumpFile.dfsql, and generates a new SQL-92 VIEW creation script file, NewViewsDumpFile.sql.
Executing the new NewViewsDumpFile.sql script file will generate the same views as intended and will help identify the VIEW(s) whose definition(s) is(are) causing the errors.
The procedure logic has the following highlights:
1. Remove the header of the file generated by the SQLSCHEMA Utility.
2. Create a TempTable whose role is to help us identify the ViewName using SELECT statements. The sample code can be easily modified output additional diagnostic messages via the use of this TempTable in conjunction with the SELECT statements.
3. Add DROP VIEW and COMMIT to each VIEW definition. This is done to narrow the individual transactions. The DROP VIEW statements will avoid the error "Table/View/Synonym already exists (7535)" when a VIEW is already in the database and will generate the error "Table/View/Synonym not found (7519)" that identifies the views that are not already there. The DROP VIEW statements will also allow us to run the generated script multiple times as needed. The COMMIT statements limit the scope of the transaction and avoids taxing the system resources during the execution of the script.
4. Once NewViewsDumpFile.sql. is generated, the Progress SQL Explorer Character Client Tool can be used to execute the script using a command along the following lines:
sqlexp -char -db DatabaseName -S 23456 -infile NewViewsDumpFile.sql -outfile NewViewsDumpFile.out -user yshanshi -password progress
5. The above command will generate a file named NewViewsDumpFile.out. Searching for the error numbers in this file will identify by name the views responsible for the errors of interest. For example the following snippet from the file NewViewsDumpFile.out indicates that error:
[JDBC Progress Driver]:Table/View/Synonym not found (7519)
was generated because we attempted to execute the statement:
DROP VIEW "OwnerName"."ViewName";
And that the error:
[JDBC Progress Driver]:Column not found/specified (7520)
was generated because we attempted to execute the statement:
CREATE VIEW "OwnerName"."ViewName" (...);
=== SQL Exception 1 ===
SQLState=42S02
ErrorCode=-20005
[JDBC Progress Driver]:Table/View/Synonym not found (7519)
"OwnerName"."ViewName"
------------------
"OwnerName"."ViewName"
=== SQL Exception 1 ===
SQLState=42S22
ErrorCode=-20006
[JDBC Progress Driver]:Column not found/specified (7520)
/* DiagnoseViewCreationScriptErrors.p */
DEFINE VARIABLE cSourceFileName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cTargetFileName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cLine AS CHARACTER NO-UNDO.
ASSIGN
cSourceFileName = "C:\WRK91E\W711280155\OriginalViewsDumpFile.dfsql"
cTargetFileName = "C:\WRK91E\W711280155\NewViewsDumpFile.sql".
INPUT FROM VALUE ( cSourceFileName ).
OUTPUT TO VALUE ( cTargetFileName ).
/* Create a TempTable */
PUT UNFORMATTED "CREATE TABLE pub.TempTable (iNumber INTEGER NOT NULL );" SKIP.
PUT UNFORMATTED "COMMIT;" SKIP.BR>PUT UNFORMATTED "INSERT INTO pub.TempTable (iNumber) VALUES (1);" SKIP.
PUT UNFORMATTED "COMMIT;" SKIP.
/* Remove header lines from the original dfsql file */
REPEAT:
IMPORT UNFORMATTED cLine.
IF TRIM(cLine) BEGINS 'CREATE VIEW ' THEN LEAVE.
END.
/* Process the rest of the original dfsql file */
RUN ProcessCreateViewLine( INPUT cLine).
REPEAT:
IMPORT UNFORMATTED cLine.
IF TRIM(cLine) BEGINS 'CREATE VIEW ' THEN DO:
RUN ProcessCreateViewLine( INPUT cLine).
NEXT.
END.
IF TRIM(cLine) BEGINS ';' THEN DO:
RUN ProcessSemiColon( INPUT cLine).
NEXT.
END.
PUT UNFORMATTED cLine SKIP.
END.
PUT UNFORMATTED "DROP TABLE pub.TempTable;" SKIP.
PUT UNFORMATTED "COMMIT;" SKIP.
INPUT CLOSE.
OUTPUT CLOSE.
PROCEDURE ProcessCreateViewLine:
DEFINE INPUT PARAMETER ipcCreateViewLine AS CHARACTER NO-UNDO.
DEFINE VARIABLE cViewName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cDropViewLine AS CHARACTER NO-UNDO.
DEFINE VARIABLE cCommitLine AS CHARACTER NO-UNDO.
DEFINE VARIABLE cSelectLine AS CHARACTER NO-UNDO.
ASSIGN
cViewName = REPLACE(ipcCreateViewLine,"create view ","")
cViewName = REPLACE(cViewName,"(","")
cViewName = TRIM(cViewName)
cDropViewLine = "DROP VIEW " + cViewName + ";"
cCommitLine = "COMMIT;"
cSelectLine = "SELECT " + "'" + cViewName + "'" + " FROM pub.TempTable;".
PUT UNFORMATTED cDropViewLine SKIP.
PUT UNFORMATTED cCommitLine SKIP.
PUT UNFORMATTED cSelectLine SKIP.
PUT UNFORMATTED ipcCreateViewLine SKIP.
END PROCEDURE.
PROCEDURE ProcessSemiColon:
DEFINE INPUT PARAMETER ipcSemiColonLine AS CHARACTER NO-UNDO.
DEFINE VARIABLE cSemiColonLine AS CHARACTER NO-UNDO.
DEFINE VARIABLE cCommitLine AS CHARACTER NO-UNDO.
ASSIGN
cSemiColonLine = ipcSemiColonLine
cCommitLine = "COMMIT;".
PUT UNFORMATTED cSemiColonLine SKIP.
PUT UNFORMATTED cCommitLine SKIP.
END PROCEDURE..