Teradata BTEQ : IF Conditon , Validate SP Compilation With Runtime Actual Parameters & Export DDL
BTEQ related questions - I am compiling a list of SP's via BTEQ like this
.compile file=sp1 ; .IF ERRORCODE <> 0 THEN .GOTO SQLERROR; compile file=sp2 ; .IF ERRORCODE <> 0 THEN .GOTO SQLERROR; .logoff .quit .LABEL SQLERROR .logoff ; .quit ;
I have the following Q's regarding these
If The SP compilation fails, the following
.IFcondition, should catch it and rest of the file should be ignored as per what the
But that does not happen
.compile file=sp1 ; One Error/Warning. Warning: 5526 Stored Procedure is not created/replaced due to error(s). Total elapsed time was 1 second. Errors reported during compilation `--------------------------------------------------------------------------- SPL1076:E(L3), The right parenthesis in parameter declaration is missing.
.IF ERRORCODE <> 0 THEN .GOTO SQLERROR; +---------+---------+---------+---------+---------+---------+---------+---- .compile file=sp2 ;
Procedure has been replaced. Total elapsed time was 1 second.
Why is the
IF not working out ?
The stored procedure replaces a view. The entire
replace stored procstatement is generated via
showthat is exported . I will come to that later. Inside the SP the code is something like this
REPLACE PROCEDURE "DB"."TB" (IN L_N2 VARCHAR(500), IN L_N1 VARCHAR(500) ) BEGIN
DECLARE returncode INTEGER DEFAULT 0; DECLARE mystat VARCHAR(60000);
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET RETURNCODE=1/0; END SET mystat='REPLACE VIEW '||L_N2||'.TBname ( VCol1,Vcol2) As sel col1,col2 From '||L_N1||'.Tbname'; CALL dbc.sysexecsql(mystat);
Here even if Col1, Col2 have an issue e.g. instead of Col1 I'd put Co l1 the SP will compile . Understandably the run-time outcome of the Stored Procedure Call will not be evaluated when its compiled. This leaves a situation where any kind of error in the generated
replace view DDL cannot be detected. Is there any way I can get the
replace view to validate at the time of SP compilation ?
- Lastly- regards exporting DDL via BTEQ
there is an extra space between
"db". "tb"now when I do the same show in SQL assistant - all is well- there's no space. I have to write another sed to del the extra space .
The view and SP code is very long and often on UNIX the code gets split across new lines like
sel foo_ bar, foobar,Col. tb from db.tb foo_bar is split across multiple lines . # std BTEQ options are used. The \n is because I am echoing all this to file .SET ECHOREQ OFF \n .set width 500 .set titledashes off \n .set format off \n .set rtitle '' \n .export report file="$ph" \n show procedure $db.$tb ; \n .export reset ; \n
Q1: BTEQ simply ignores any error/warning returned by the DBMS (don't know why). If you want to stop after any error/warning you could simply use
ACTIVITYCOUNT instead of
.compile file=sp1 ; .IF ACTIVITYCOUNT > 0 THEN .GOTO SQLERROR;
If you want to ignore warnings I don't know a reliable way without dropping the SP first:
DROP PROCEDURE whatever; .compile file=sp1 ; -- creates SP whatever HELP PROCEDURE whatever ATTR; .IF ERRORCODE = 5495 THEN .GOTO SQLERROR; -- 5495 = SP doesn't exist
Q2: There's no way to validate Dynamic SQL, it's dynamic as the name implies and unknown to a DBMS before it's actually submitted.
Q3: Whitespace around a period in a qualified name don't result in an error,
db . td is happily accepted by the parser.
foo_bar should not be split across lines unless the line is too wide, simply increase the maximum length using
.set width 30000;
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key