Often but not always when I revisit old code I realise there are room for improvements. This time I had reason to review the SAP connector code I done for importing data from SAP into an relational database, the basic structure is very simple:
<job>
<rfc>
SAP extraction code goes here…
</rfc> <sql> SQL import code goes here...
</sql>
</job>
|
I looked into this because I came across a job that runs frequently, but seldom finds any new data to export from SAP, (the job is the first in a workflow that creates an alarm that signals ‘incoming bulk order’). It annoyed me when I realized there is no way to stop the SQL part nomatter of the result from the SAP rfc part of the job. The rest of the workflow was stopped by a prereq:
<prereq type='sql'>
SELECT IF(COUNT(*)>0,1,0) FROM import_table;
</prereq>
|
Prereqs are booleans saying stop or go, I decided I would implement support for prereqs in the sql part of the job like this:
<job>
<rfc>
SAP extraction code goes here…
</rfc>
<sql>
<prereq>got data from rfc</prereq>
SQL import code goes here...
</sql>
</job>
|
This is a pretty neat design. I’m reusing the prereq which is a very commonly used instruction of my data tag language and it’s self explanatory. The rfc part already creates a file with the number of rows found, so it’s just a matter of making the number accessible to the sql part. And very easy to evaluate the prereq, if you just feed the extracted rows to the prereq it will be evaluated as a boolean, 0 results in false and anything above to true. I decided to call the extracted rows count TABLE_ROWS and put it into the jobs symbolic table for accessibility that was easy I only changed
file_put_contents('TABLE_ROWS.CSV',trim(trim(file_get_contents('TABLE_ROWS.CSV')),"$Zsfd"));
|
Into this:
$TABLE_ROWS = trim(trim(file_get_contents('TABLE_ROWS.CSV')),"$Zsfd"); file_put_contents('TABLE_ROWS.CSV',$TABLE_ROWS);
$job['_xlate']['TABLE_ROWS'] = $TABLE_ROWS; // create TABLE_ROWS @symbolic
|
And now I can add a prereq like this:
<prereq>@TABLE_ROWS</prereq>
|
Next was to add support for a prereq in the sql part so I took a look at the real job’s sql:
<sql>
<fieldclosure>"</fieldclosure>
<autoload>replace</autoload><database>@C_DB</database><truncate>yes</truncate>
<table>
<name>@TABLE1=@DWTABLE1</name>
<idxdef>PRIMARY KEY `VBELN`,`POSNR`),INDEX(`MATNR`,`WERKS`)</idxdef>
<comment>This table is derived from SAP table @TABLE1</comment>
</table>
</sql>
|
The line beginning with <autoload is parameters for automagically load a table into a RDBMS, and it looks like crap. I decided a facelift was needed this is what I would like it to look:
<sql>
<prereq>@TABLE_ROWS</prereq>
<fieldclosure>"</fieldclosure>
<autoload duplicate='replace' database='@C_DB' truncate='yes'>
<table>
<name>@TABLE1=@DWTABLE1</name>
<idxdef>PRIMARY KEY (`VBELN`,`POSNR`),INDEX(`MATNR`,`WERKS`)</idxdef>
<comment>This table is derived from SAP table @TABLE1</comment>
</table>
</autoload>
</sql>
|
Not pretty maybe, but less ugly and support for sql prereqs. Now I only had implement the new functionality. I started with the new XML syntax. Just to keep things simple I decided to downgrade the new syntax to the old syntax. I had a function that scans for <autoload> and if found pass it over to a function that does the autoload of SAP data into tables.
function autoloadSapTables($context,$job,$sapParms,$driverIndex) {
$log = $GLOBALS['log'];
$sqlarr = array_key_exists('script',$job) ? $job['script']['sql'] : $job['sql'];
$log->enter('Info',"");
for($i = 0; $i<count($sqlarr); $i++){
if (array_key_exists('autoload',$sqlarr[$i])) {
$log->logit(info',"autoload found i=$i");
$sqlxml = parseAutoload($context,$job,$sqlarr[$i],$sapParms,$driverIndex);
}
if ($sqlxml === FALSE) return FALSE;
$sqlarr[$i] = $sqlxml['sql'][0]; // Note this is our copy of the job control block
}
}
return array('sql' => $sqlarr);
}
|
This is the function with downgrading code inserted:
function autoloadSapTables($context,$job,$sapParms,$driverIndex) {
$log = $GLOBALS['log'];
$sqlarr = array_key_exists('script',$job) ? $job['script']['sql'] : $job['sql'];
$log->enter('Info',"");
for($i = 0; $i<count($sqlarr); $i++){
if (array_key_exists('autoload',$sqlarr[$i])) {
$al = $sqlarr[$i]['autoload'][0];
if (array_key_exists('duplicate',$al)
or array_key_exists('database',$al)
or array_key_exists('truncate',$al)
or array_key_exists('table',$al[0]) ){
// This is the new restructured autoload, downgrade to old format
$log->logit('note',"autoload new format found downgrading i=$i");
$al['autoload'] = [['value' => $al['duplicate']]];
$al['database'] = [['value' => $al['database']]];
$al['truncate'] = [['value' => $al['truncate']]];
$sqlxml = parseAutoload($context,$job,$al,$sapParms,$driverIndex);
}
else {
$log->logit('note',"autoload old format i=$i");
$sqlxml = parseAutoload($context,$job,$sqlarr[$i],$sapParms,$driverIndex);
}
if ($sqlxml === FALSE) return FALSE;
$sqlarr[$i] = $sqlxml['sql'][0]; // Note this is our copy of the job control block
}
}
return ['sql' => $sqlarr];
}
|
And that is where I am now, I didn’t do what I originally wanted to accomplish. I suppose that is what happen to most of us when start looking into old code where you only intend to add or change something minor. Next weekend I hope to get some time to finalish the task.
P.s.
You may rightly wonder why is this bulk order alarm not implemented directly in SAP? I do not know, probably it was too expensive or took too long time to get into production.
No comments:
Post a Comment