| Subject: | Does not work for Oracle |
Attached is a patch so that this can work on Oracle. An option to pass
in a schema and table name prefix is added, along with setting the case
of keys in hashrefs in fetchrow_hashref() (which is capitalized on
Oracle if not explicitly set).
Also, an option to pass in the dbh directly, as it it much easier for me
to get a dbh through internal OO library means and so not have to
specify user and password for this library. It might be easier for
others, too.
What I'm not sure of is if the schema name and table prefix that I have
to specify is the default for Oracle, but at least there will now be a
way to specify them even if they are not the default.
| Subject: | CA-AutoSys.diff |
diff -u -r CA-AutoSys-1.05/lib/CA/AutoSys.pm CA-AutoSys-1.05_tst/lib/CA/AutoSys.pm
--- CA-AutoSys-1.05/lib/CA/AutoSys.pm Mon Feb 11 02:50:28 2008
+++ CA-AutoSys-1.05_tst/lib/CA/AutoSys.pm Thu Sep 20 16:17:01 2012
@@ -52,17 +52,21 @@
$self->{server} = $args{server} ? $args{server} : undef;
$self->{user} = $args{user} ? $args{user} : undef;
$self->{password} = $args{password} ? $args{password} : undef;
+ $self->{dbh} = $args{dbh} ? $args{dbh} : undef;
+ $self->{table_prefix} = $args{table_prefix} ? $args{table_prefix} : undef;
}
- if (!defined($self->{dsn})) {
- if (!defined($self->{server})) {
- $errstr = "no dsn given in new()";
- return undef;
+ if (!defined($self->{dbh})) {
+ if (!defined($self->{dsn})) {
+ if (!defined($self->{server})) {
+ $errstr = "no dsn given in new()";
+ return undef;
+ }
+ # Default to Sybase when no dsn was given...
+ $self->{dsn} = "dbi:Sybase:server=$self->{server}";
}
- # Default to Sybase when no dsn was given...
- $self->{dsn} = "dbi:Sybase:server=$self->{server}";
+ $self->{dbh} = DBI->connect($self->{dsn}, $self->{user}, $self->{password});
}
- $self->{dbh} = DBI->connect($self->{dsn}, $self->{user}, $self->{password});
if (!$self->{dbh}) {
$errstr = "can't connect to dsn ".$self->{dsn}.": ".$DBI::errstr;
return undef;
@@ -75,7 +79,7 @@
sub find_jobs {
my $self = shift();
my $job_name = shift();
- my $job = CA::AutoSys::Job->new(parent => $self, database_handle => $self->{dbh});
+ my $job = CA::AutoSys::Job->new(parent => $self, database_handle => $self->{dbh}, table_prefix => $self->{table_prefix});
return $job->find_jobs($job_name);
} # find_jobs()
@@ -138,6 +142,10 @@
=over 5
+=item B<dbh>
+
+Pass in a database handle. If this is specified, then dsn, user, and password are not required.
+
=item B<dsn>
Specify the DSN of the AutoSys' database server to connect to. If nothing is specified, Sybase will be
@@ -158,6 +166,12 @@
Specify the database password. With an out-of-the-box AutoSys installation, the default password should work.
+=item B<table_prefix>
+
+Specify a prefix to add to table names. E.g. 'ujo_' will make sql refer to table 'ujo_job' instead of 'job'.
+Or, for an Oracle database, you could also specify the schema name in this option. So for a schema of
+'aedbadmin' and a table prefix of 'ujo_', you could just specify prefix 'aedbadmin.ujo_' here.
+
=back
Example:
diff -u -r CA-AutoSys-1.05/lib/CA/AutoSys/Job.pm CA-AutoSys-1.05_tst/lib/CA/AutoSys/Job.pm
--- CA-AutoSys-1.05/lib/CA/AutoSys/Job.pm Mon Feb 11 02:49:37 2008
+++ CA-AutoSys-1.05_tst/lib/CA/AutoSys/Job.pm Thu Sep 20 16:13:15 2012
@@ -46,6 +46,7 @@
$self->{dbh} = $args{database_handle} ? $args{database_handle} : undef;
$self->{sth} = $args{statement_handle} ? $args{statement_handle} : undef;
$self->{parent_job} = $args{parent_job} ? $args{parent_job} : undef;
+ $self->{table_prefix} = $args{table_prefix} ? $args{table_prefix} : undef;
}
if ($debug) { printf("DEBUG: Job(%s) created.\n", $self); }
@@ -57,7 +58,7 @@
sub _fetch_next {
my $self = shift();
if ($debug) { printf("DEBUG: Job(%s): _fetch_next()\n", $self); }
- if (my $h = $self->{sth}->fetchrow_hashref()) {
+ if (my $h = $self->{sth}->fetchrow_hashref('NAME_lc')) {
if (defined($self->{parent_job})) {
$self->{parent_job}->{child_cnt}++;
}
@@ -78,11 +79,13 @@
} # _fetch_next()
sub _query {
+ my $self = shift;
+ my $prefix = $self->{table_prefix} || '';
my $query = qq{
select j.*, s.*, j2.job_name as box_name
- from job j join job_status s
+ from ${prefix}job j join ${prefix}job_status s
on j.joid = s.joid
- left outer join job j2
+ left outer join ${prefix}job j2
on j.box_joid = j2.joid
};
return $query;
@@ -92,7 +95,7 @@
my $self = shift();
if ($debug) { printf("DEBUG: Job(%s): find_jobs()\n", $self); }
my $job_name = shift();
- my $query = _query() . qq{
+ my $query = $self->_query() . qq{
where j.job_name like '$job_name'
order by j.joid
};
@@ -108,7 +111,7 @@
sub find_children {
my $self = shift();
if ($debug) { printf("DEBUG: Job(%s): find_children()\n", $self); }
- my $query = _query() . qq{
+ my $query = $self->_query() . qq{
where j.box_joid = $self->{joid}
order by j.joid
};