Update for Demos ETL Tools // start ETL Job from web report

    Update for Demos ETL Tools // start ETL Job from web report

    Hello everybody,

    for a current feature request I am trying to make a web spreadsheet similar to the one in Jedox Demo Spreadsheets "ETL Tools".


    The goal is to select Jobs and start them from within the web.

    Unfortunately I cannot even make the demo work, since it cannot generate the Project and Job list. ||
    I've seen that there is no login in the code, but it is required. The problem is, that I have no knowledge in PHP. I had a look at the knowledgebase article but the code is considerably different in the examples given in the article compared to the demo version.

    Does anybody have an update on the code?

    here is the original from the Demo Spreadsheet "ETL Tools":

    Source Code

    1. // adapt protocol, host, port here to match your setup
    2. define('ETL_URL','http://10.59.231.53:7775');
    3. // wait for job to finish prior returning status
    4. define('WAIT_FOR_FINISH', true);
    5. // max execution time of script in seconds used in combination with WAIT_FOR_FINISH
    6. define('MAX_EXECUTION_TIME', 10);
    7. function getStatus()
    8. {
    9. $s = activesheet();
    10. $id = $s->range('B15')->value;
    11. return getStatusFromId($id);
    12. }
    13. // fetch status and write it into cell
    14. function getStatusFromId($id)
    15. {
    16. $s = activesheet();
    17. $server = @new SoapClient(ETL_URL . '/etlserver/services/ETL-Server?wsdl', array('exceptions' => true) );
    18. $response = $server->getExecutionStatus(array('id' => $id, 'waitForTermination' => false));
    19. $return = $response->return;
    20. $s->range('D16')->value = $return->status;
    21. return $return->statusCode;
    22. }
    23. // fetch list of projects
    24. function getProjects()
    25. {
    26. $server = new SoapClient(ETL_URL . '/etlserver/services/ETL-Server?wsdl', array('exceptions' => true) );
    27. $res = $server->getNames();
    28. if(count($res->return) == 0) {
    29. return '';
    30. }
    31. return $res->return;
    32. }
    33. // fetch list of jobs
    34. function getJobs($job)
    35. {
    36. if( empty($job) ) return '';
    37. $server = new SoapClient(ETL_URL . '/etlserver/services/ETL-Server?wsdl', array('exceptions' => true) );
    38. $res = $server->getNames(array('locator' => $job.'.jobs'));
    39. if( !$res->return ) return '';
    40. return $res->return;
    41. }


    in comparison to extracts from the knowledgebase article

    Source Code

    1. $wsdl_url = 'http://127.0.0.1:7775/etlserver/services/ETL-Server?wsdl';
    2. $server = @new SoapClient($wsdl_url, array('exceptions' => true, 'location' => $wsdl_url));
    3. // do login attempt on soap object
    4. $login_attempt = $server->login(array('user' => 'admin', 'password' => 'admin'))->return;
    5. $session = $login_attempt->result;
    6. // set the returned session as soap header
    7. $header = new SoapHeader('http://ns.jedox.com/ETL-Server/', 'etlsession', $session);
    8. $server->__setSoapHeaders($header);
    9. // get list of available integrator projects
    10. $result = $server->getNames();
    11. // set empty string if no projects exist
    12. if(count($result->return) == 0) {
    13. $project_list = '';
    14. }
    15. $project_list = $result->return;
    16. // get list of jobs in a project
    17. $project_name = 'sampleBiker';
    18. $result = $server->getNames(array('locator' => $project_name.'.jobs'));
    19. if(count($result->return) == 0) {
    20. $job_list = '';
    21. }
    22. $job_list = $result->return;
    23. // execute integrator job
    24. // set the locator to be used for the execution, job with name "default" on project "sampleBiker"
    25. $locator = 'sampleBiker.jobs.default';
    26. // set the variables to be used for the execution
    27. $variables = array(array('name' => 'variable_year', 'value' => 2016), array('name' => 'variable_datatype', 'value' => 'Budget'));
    28. // execute the job
    29. $result = $server->execute(array('locator' => $locator, 'variables' => $variables));
    30. $job_execution = $result->return;

    I would basically just like to have the demo working, since there is a need to start jobs by people who don't or at least shouldn't have access to the integrator.

    Any help is very much appreciated.
    Thank you very much in advance.
    If you don't know much about PHP and you just want to start jobs, then just read section 2 in the KB article and use the two functions "integrator_start_job" and "integrator_set_job_status". Then you have to provide a valid list of job names and projects in the report, but you do not have to learn details about the SOAP calls.
    I will give it a try.

    My original idea was to be able to select a job from a combobox (which has the function to list all jobs of one project).
    Like that I would have to at least manually maintain a list of the jobs (which of course can change in the course of time).

    Post was edited 3 times, last by “Bjoern.” ().

    I tried it, but somehow I seemingly cannot refer to the respective cell.

    If I go the following route, it runs through but without a result. It seems
    as if it doesn't get the project name and job name from the respective
    cells. According to the Macro Engine API, it should be relatively straight
    forward, or did I miss something PHP-specific?


    EDIT: I added the value, but do I need it as well, if I want the script just to retrieve the string-value within the cell?
    Either way it doesn't work

    Source Code

    1. //load the integrator library for the wrapper functions
    2. require library('integrator');
    3. // for starting an ETL Job
    4. function _btn_start_job_Click ()
    5. {
    6. $ws = activesheet(); // get activesheet
    7. $returnid = $ws->range('C17')->value; //for cell where Job ID should be returned to
    8. $jobstatus = $ws->range('C18'); // for cell where Job Status should be retrieved from and written to
    9. $project = $ws->range('C14'); // cell where Project name should be retrieved from
    10. $job = $ws->range('C15'); // cell where Job name should be retrieved from
    11. //start the Job given in cell C15 (via Combobox) in Integrator project given in cell C14 (via Combobox)
    12. $id = integrator_start_job($project,$job);
    13. //assign the teturn job id to a @variable
    14. define_variable('INTERGRATOR_JOB_ID',$id);
    15. //show the returned job id in a message box
    16. return __msgbox('Job gestarted mit ID '.$id,'info');
    17. //write job id in cell
    18. $returnid = $id;
    19. }

    Post was edited 1 time, last by “Bjoern.” ().

    Oh yeah, the problem is PHP specific. By using the equal sign, you take a value from the right and put it to the left. So $project and $job need the cell value on the right side and to write into a cell it's on the left side. This also means that retrieving and writing are two different lines. And you never write range() without "->value" when it's about the cell's value.

    Regarding line 23, you have a different notation in PHP to pass "by reference". I think you just reassign the variable's value here. To keep things simple, don't use a variable on the left side.

    Post was edited 1 time, last by “Seaben” ().

    Ok,

    so if I get you right $project and $job was already correct then, or am I confusing it?
    I just had to reverse the $returnid line so that the value of the variable is written to the cell.
    EDIT: And I put that line to the very end so that it is in order of executing the code from top to bottom.
    Like this I have the same problem though. I guess that I'm still making something wrong or that I mixed it up.

    Source Code

    1. // for starting an ETL Job
    2. function _btn_start_job_Click ()
    3. {
    4. $ws = activesheet(); // get activesheet
    5. $project = $ws->range('C14')->value; // cell where Project name should be retrieved from
    6. $job = $ws->range('C15')->value; // cell where Job name should be retrieved from
    7. //start the Job given in cell C15 (via Combobox) in Integrator project given in cell C14 (via Combobox)
    8. $id = integrator_start_job($project,$job);
    9. //assign the teturn job id to a @variable
    10. define_variable('INTERGRATOR_JOB_ID',$id);
    11. //show the returned job id in a message box
    12. return __msgbox('Job gestarted mit ID '.$id,'info');
    13. //write job id in cell
    14. $returnid = retrieve_variable('id');
    15. $ws->range('C17')->value = $returnid; //cell where Job ID should be returned to
    16. // write job status in cell
    17. $ws->range('C18')->value = $jobstatus;
    18. }

    Post was edited 1 time, last by “Bjoern.” ().

    The require library('integrator'); wasn't in the excerpt, since I only copied the function.
    I added the initialisation of "@jobstatuts" but am running into the same issue, that it starts but cannot give any return values
    I took that line from the Macro Engine API

    Source Code

    1. require library('integrator');
    2. // for starting an ETL Job
    3. function _btn_start_job_Click ()
    4. {
    5. $ws = activesheet(); // get activesheet
    6. $project = $ws->range('C14')->value; // cell where Project name should be retrieved from
    7. $job = $ws->range('C15')->value; // cell where Job name should be retrieved from
    8. $status = $ws->range('C18')->value; //cell where status is written to
    9. //start the Job given in cell C15 (via Combobox) in Integrator project given in cell C14 (via Combobox)
    10. $id = integrator_start_job($project,$job);
    11. //assign the return job id to a @variable
    12. define_variable('INTERGRATOR_JOB_ID',$id);
    13. // get the status code and message (array) of the job
    14. $id = retrieve_variable('INTEGRATOR_JOB_ID');
    15. $status = integrator_set_job_status($id);
    16. //show the returned job id in a message box
    17. return __msgbox('Job gestarted mit ID '.$id,'info');
    18. //write job id in cell
    19. $returnid = $id;
    20. $ws->range('C17')->value = $returnid; //cell where Job ID should be returned to
    21. // write job status in cell
    22. $ws->range('C18')->value = $status;
    23. }
    Thanks,
    no it seems to run through.
    It still doesn't give an ID though and has an value error in the cell then, where the job id should be written to.
    I checked the monitor and the job didn't run.

    Given the deadline, I think I have to give up on this and just start the jobs the normal way.

    Thanks for your help though.