DBI connection by calling a perl script from external task

Hi all,

I am working on Control Hub on unix platform .I have a workflow in which an external task calls upon a perl script , this script connects to an oracle databse and inserts values into a table. Now this script runs fine when invoked through the command prompt itself , however when it is invoked through the external task , though the perl script executes it is not able to make the database connection .I tried setting the environment variables ( ORACLE_HOME, LD_LIBRARY_PATH ) from the perl script however even that isnt working. Also the perl script is not able to execute any of the CLTs. Is this an unix permission issue (the owner of the external task is 'root') or is ther any othe other way of doing it?
The solution that we came up with for this isssue was to invoke a shell script first , that will set the necessary environment variables and then call execute the perl script to make the DB. connection.This works fine but we are facing call back issues as we are not able to get the task id in the perl script.
Kindly suggest if there is a way out of this issue and how would i be able to invoke the perl script directly instead of using a shell script in between.
Thanks,
PK

Find more posts tagged with

Comments

Adam Stoller

Hi all,

I am working on Control Hub on unix platform .I have a workflow in which an external task calls upon a perl script , this script connects to an oracle databse and inserts values into a table. Now this script runs fine when invoked through the command prompt itself , however when it is invoked through the external task , though the perl script executes it is not able to make the database connection .I tried setting the environment variables ( ORACLE_HOME, LD_LIBRARY_PATH ) from the perl script however even that isnt working. Also the perl script is not able to execute any of the CLTs. Is this an unix permission issue (the owner of the external task is 'root') or is ther any othe other way of doing it?
The solution that we came up with for this isssue was to invoke a shell script first , that will set the necessary environment variables and then call execute the perl script to make the DB. connection.This works fine but we are facing call back issues as we are not able to get the task id in the perl script.
Kindly suggest if there is a way out of this issue and how would i be able to invoke the perl script directly instead of using a shell script in between.
Thanks,
PK

If the setting of the environment variables in the Perl script is not working - you either are not setting the environment variables correctly - or you're not setting the correct environment variables - or both.

With regard to the first notion (not setting them correctly) - the code should look like this:

...
BEGIN {
    $ENV{env_var_name1} = "value1";
    $ENV{env_var_name2} = "value2";
    ...
};
...

My guess is that you are not using the BEGIN{...}; block - (note: all caps, curly braces, semi-colon after closing curly brace) - am I right?

PRK

Hi ghoti,
First of all sorry for this late reply ( was a long weekend for me) . Well, you guessed it right , I wasn't using the BEGIN{};block , thanks a lot for your reply.
Regards,
PK

PRK

Hey I must apologize again , I replied by just looking at the code and not actually trying it out .I put in the BEGIN block but that doesnt seem to work too , my code for setting environment variable is something like this:

BEGIN {
$ENV{ORACLE_HOME} = "value1";
$ENV{LD_LIBRARY_PATH} = "value2";
$ENV{PATH} = "value3";
};

but still its not setting the environment variables though the rest of the script is working fine ,except where the db connection should happen, why isnt CH allowing me to set the environment variables through perl script or is it a unix related issue ?
Thanks ,
PK

ISCBorisB

why isnt CH allowing me to set the environment variables through perl script or is it a unix related issue ?

It's neither Hub nor Unix. You are either setting incorrect values or still missing some Environment variables (ORACLE_BASE? ORACLE_SID?) or both.
Also, make sure that your begin block is coded before "use DBI" command. For better help you may want to post your code.

PRK

the values I am setting are not incorrect ,I can assure that

#!/usr/Interwoven/ControlHub/iw-perl/bin/iwperl

BEGIN {
$ENV{'ORACLE_HOME'} = "value1";
$ENV{'LD_LIBRARY_PATH'} = "value2";
$ENV{'PATH'} = "";
};

use Getopt: Smiley Frustrated

td;
use File::Basename;
use strict;
use TeamSite::WFtask;
use TeamSite::WFworkflow;
use TeamSite::Config;
use TeamSite::XMLparser;
use TeamSite::JavaResourceBundle;
use HTML::Entities;
use DBI;

#DB CONNECTION
my $DEBUG = 1;

my $debug_file ="path";

my ($MapNumber,$SourcePath,$TargetPath,$ServerName,$FileName,$Adapter,$UserName,$GroupName,$Permission,$rownum) = ();
my ($dcr_file_path, $tablename, $wfid, $taskid, $area) = (@ARGV[0..4]);
#my $task1 = "/usr2/Interwoven/ControlHub/bin/iwgettaskbyname $task DCR_to_Database_Deployment";

debug($dcr_file_path);
debug($tablename);
debug($wfid);
debug($taskid);

debug($tablename);

debug("before connection");
#DB Connection. Encode Password in future
my $dbh = DBI->connect('dbi Smiley Surprised

racle:host=name;sid=id','user','password', {RaiseError => 1, PrintError => 1}) or die "Cannot Connect";

print "after connection";

debug("after connection\n");

debug("dbh $dbh\n");

This is the code uptill the db connection , now the problem is when this perl script is called through a shell script (after setting the above mentioned environment variables through the shell script) it runs fine , however when I try to set the variables in the perl script itself , it just doesnt happen and the connection doesnt take place .

Adam Stoller

the values I am setting are not incorrect ,I can assure that

#!/usr/Interwoven/ControlHub/iw-perl/bin/iwperl

BEGIN {
$ENV{'ORACLE_HOME'} = "value1";
$ENV{'LD_LIBRARY_PATH'} = "value2";
$ENV{'PATH'} = "";
};

use Getopt:td;
use File::Basename;
use strict;
use TeamSite::WFtask;
use TeamSite::WFworkflow;
use TeamSite::Config;
use TeamSite::XMLparser;
use TeamSite::JavaResourceBundle;
use HTML::Entities;
use DBI;
...
my ($MapNumber,$SourcePath,$TargetPath,$ServerName,$FileName,$Adapter,$UserName,$GroupName,$Permission,$rownum) = ();
my ($dcr_file_path, $tablename,  $wfid, $taskid, $area) =  (@ARGV[0..4]);
...
my $dbh = DBI->connect('dbiracle:host=name;sid=id','user','password', {RaiseError => 1, PrintError => 1}) or die "Cannot Connect";
...

This is the code uptill the db connection , now the problem is when this perl script is called through a shell script (after setting the above mentioned environment variables through the shell script) it runs fine , however when I try to set the variables in the perl script itself , it just doesnt happen and the connection doesnt take place .

Some notes: