return error code with deffun fucntion ?

[ludovic]

Hello,

In a job control, i used the function DEFFUN to create a function based on a routine. But i have a problem with this when the routine no more exist or his name has changed : then the job control abort, when i run it, with an ununderstand error :
Warning Attempting to Cleanup after ABORT raised in stage Batch::BSRTP_AlimBSRTP1..JobControl
Control Job Batch::BSRTP_AlimBSRTP1 aborted.

This error is generated only when i use the function define with the deffun, not when i declare the deffun line.

This behaviour is my questions aim : is it possible to test the result of a deffun line (example of my deffun line :

deffun LectureParam (Arg1, Arg2) Calling "DSU.FWKCommunLectureParam"


Or another way, is it possible in a job control testing that the routine used is or not in the datastage project

thanks for your help

[chulett]

Welcome. :D

I don't believe so as DEFFUN doesn't return a status. Or at least not easily. I suppose one could query the repository to verify it exists but then what? Abort if it can't be found? End result seems pretty much the same to me. And I would wager if you checked deeper in your logs the missing routine would be named somewhere so that the problem becomes less 'ununderstandable'.

Note that you may need to Reset the aborted job and check the "From previous run..." entry to see this information.

[ArndW]

The DEFFUN line is used at compile time, not at runtime.
Do you use the LectureParam() function anywhere in the job?

[ray.wurlod]

You can test whether the catalog entry exists with a query such as

Code: Select all

SELECT COUNT(*) FROM VOC WHERE F0 = 'DSU.FWKCommunLectureParam';

executed from the Administrator client.

[ludovic]

The solution SELECT COUNT(*) FROM VOC WHERE F0 = 'DSU.FWKCommunLectureParam' can be a great solution.

Works in the adminstator, but i want use it in a job control.

I test this :

call DSExecute ("TCL","SELECT COUNT(*) FROM VOC WHERE F0 = 'DSU.FWKCommunLectureParam' ", Output, ReturnCode)


but if the 'DSU.FWKCommunLectureParam' is or not here, the result is the same
Output = SQL
ReturnCode =-1

Is anyway to have in the output variable the result of the commande SELECT COUNT(*) FROM VOC WHERE F0 = 'DSU.FWKCommunLectureParam'

thanks for your help

[ray.wurlod]

You must have the terminator on the SQL. Return code of less than zero indicates an error (in your case an SQL syntax error).

Code: Select all

Call DSExecute ("TCL","SELECT COUNT(*) FROM VOC WHERE F0 = 'DSU.FWKCommunLectureParam' ;", Output, ReturnCode)
                                                                                       ^ 

[ludovic]

Thanks with the ";" character at the end of my SQL code works fine.
But need to transform the output to have only the number of line.

with this command
call DSExecute ("TCL", "SELECT COUNT(*) FROM VOC WHERE F0 = 'DSU.FWKCommunRunOrJumpSavePointV2' ; " , Output, ReturnCode)

output is equal to :
COUNT ( * )

0

1 records listed.



then with these lines :

call DSExecute ("TCL", "SELECT COUNT(*) FROM VOC WHERE F0 = 'DSU.FWKCommunRunOrJumpSavePointV2' ; " , Output, ReturnCode)

OutputExploitable=Trim( (Ereplace ( Ereplace(Output, "COUNT ( * )" ," "), "1 records listed." , " ")))[3,2]



my variable OutputExploitable has the value 0



At conclusion to detect a routine in the project when you want use it in a job control, this code works

deffun RunOrJumpSavePointV2(Arg1,Arg2) calling "DSU.FWKCommunRunOrJumpSavePointV2"

call DSExecute ("TCL", "SELECT COUNT(*) FROM VOC WHERE F0 = 'DSU.FWKCommunRunOrJumpSavePointV2' ; " , Output, ReturnCode)

OutputExploitable=Trim( (Ereplace ( Ereplace(Output, "COUNT ( * )" ," "), "1 records listed." , " ")))[3,2]

if OutputExploitable = 0
then
Call DSLogFatal("la routine FWKCommunRunOrJumpSavePointV2 n est pas presente dans le projet",'')
Goto EndProcess
end



Where EndProcess is a label at the end of my jobcontrol to quit the program.

[chulett]

Add COUNT.SUP to the end of your TCL query.

[ray.wurlod]

You require line number 3 of the output.

Code: Select all

OutputExploitable = Output<3> + 0

(The arithmetic removes leading/trailing space.)