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

Bjoern. · Jun 21st 2017, 11:39am

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

// adapt protocol, host, port here to match your setup
define('ETL_URL','http://10.59.231.53:7775');
// wait for job to finish prior returning status
define('WAIT_FOR_FINISH', true);
// max execution time of script in seconds used in combination with WAIT_FOR_FINISH
define('MAX_EXECUTION_TIME', 10);
function getStatus()
{
$s = activesheet();
$id = $s->range('B15')->value;
return getStatusFromId($id);
}
// fetch status and write it into cell
function getStatusFromId($id)
{
$s = activesheet();
$server = @new SoapClient(ETL_URL . '/etlserver/services/ETL-Server?wsdl', array('exceptions' => true) );
$response = $server->getExecutionStatus(array('id' => $id, 'waitForTermination' => false));
$return = $response->return;
$s->range('D16')->value = $return->status;
return $return->statusCode;
}
// fetch list of projects
function getProjects()
{
$server = new SoapClient(ETL_URL . '/etlserver/services/ETL-Server?wsdl', array('exceptions' => true) );
$res = $server->getNames();
if(count($res->return) == 0) {
return '';
}
return $res->return;
}
// fetch list of jobs
function getJobs($job)
{
if( empty($job) ) return '';
$server = new SoapClient(ETL_URL . '/etlserver/services/ETL-Server?wsdl', array('exceptions' => true) );
$res = $server->getNames(array('locator' => $job.'.jobs'));
if( !$res->return ) return '';
return $res->return;
}

in comparison to extracts from the knowledgebase article

Source Code

$wsdl_url = 'http://127.0.0.1:7775/etlserver/services/ETL-Server?wsdl';
$server = @new SoapClient($wsdl_url, array('exceptions' => true, 'location' => $wsdl_url));
// do login attempt on soap object
$login_attempt = $server->login(array('user' => 'admin', 'password' => 'admin'))->return;
$session = $login_attempt->result;
// set the returned session as soap header
$header = new SoapHeader('http://ns.jedox.com/ETL-Server/', 'etlsession', $session);
$server->__setSoapHeaders($header);
// get list of available integrator projects
$result = $server->getNames();
// set empty string if no projects exist
if(count($result->return) == 0) {
$project_list = '';
}
$project_list = $result->return;
// get list of jobs in a project
$project_name = 'sampleBiker';
$result = $server->getNames(array('locator' => $project_name.'.jobs'));
if(count($result->return) == 0) {
$job_list = '';
}
$job_list = $result->return;
// execute integrator job
// set the locator to be used for the execution, job with name "default" on project "sampleBiker"
$locator = 'sampleBiker.jobs.default';
// set the variables to be used for the execution
$variables = array(array('name' => 'variable_year', 'value' => 2016), array('name' => 'variable_datatype', 'value' => 'Budget'));
// execute the job
$result = $server->execute(array('locator' => $locator, 'variables' => $variables));
$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.

Seaben · Jun 21st 2017, 12:20pm

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.

Bjoern. · Jun 21st 2017, 1:52pm

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).

Seaben · Jun 21st 2017, 2:58pm

From the API documentation (Range -> Properties -> value):

Source Code

// this call returns a single value
$value = activesheet()->range('A1')->value;

Bjoern. · Jun 21st 2017, 3:01pm

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

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

Seaben · Jun 22nd 2017, 9:39am

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.

Bjoern. · Jun 22nd 2017, 11:13am

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

// for starting an ETL Job
function _btn_start_job_Click ()
{
$ws = activesheet(); // get activesheet
$project = $ws->range('C14')->value; // cell where Project name should be retrieved from
$job = $ws->range('C15')->value; // cell where Job name should be retrieved from
//start the Job given in cell C15 (via Combobox) in Integrator project given in cell C14 (via Combobox)
$id = integrator_start_job($project,$job);
//assign the teturn job id to a @variable
define_variable('INTERGRATOR_JOB_ID',$id);
//show the returned job id in a message box
return __msgbox('Job gestarted mit ID '.$id,'info');
//write job id in cell
$returnid = retrieve_variable('id');
$ws->range('C17')->value = $returnid; //cell where Job ID should be returned to
// write job status in cell
$ws->range('C18')->value = $jobstatus;
}

Seaben · Jun 26th 2017, 12:13pm

Just two things I'm noticing are that I don't see an initialisation of "$jobstatus" or the line "require library('integrator');".

Bjoern. · Jun 29th 2017, 4:06pm

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

require library('integrator');
// for starting an ETL Job
function _btn_start_job_Click ()
{
$ws = activesheet(); // get activesheet
$project = $ws->range('C14')->value; // cell where Project name should be retrieved from
$job = $ws->range('C15')->value; // cell where Job name should be retrieved from
$status = $ws->range('C18')->value; //cell where status is written to
//start the Job given in cell C15 (via Combobox) in Integrator project given in cell C14 (via Combobox)
$id = integrator_start_job($project,$job);
//assign the return job id to a @variable
define_variable('INTERGRATOR_JOB_ID',$id);
// get the status code and message (array) of the job
$id = retrieve_variable('INTEGRATOR_JOB_ID');
$status = integrator_set_job_status($id);
//show the returned job id in a message box
return __msgbox('Job gestarted mit ID '.$id,'info');
//write job id in cell
$returnid = $id;
$ws->range('C17')->value = $returnid; //cell where Job ID should be returned to
// write job status in cell
$ws->range('C18')->value = $status;
}

Seaben · Jul 1st 2017, 6:53pm

Oh, I misunderstood that the job isn't starting and overlooked the obvious. The return statement ends a function and your's is not at the end.

Bjoern. · Jul 20th 2017, 5:55pm

Hi,

not sure what you mean exactly.
I have no time at the moment to try and fiddle around. Will write back when I have an update on the situation.

Seaben · Jul 24th 2017, 10:40am

Just move line 21 to the end of the function.

Bjoern. · Jul 26th 2017, 7:59am

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.

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

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

Source Code

Source Code

Source Code

Source Code

Source Code

Source Code

Share

Tags

Similar Threads