package RDA::Macro::Dbi; # $Id: Dbi.pm,v 1.10 2009/08/05 12:07:51 mschenke Exp $ # ARCS: $Header: /u02/webapps/workbench/cvs/RDA_4/src/scripting/lib/RDA/Macro/Dbi.pm,v 1.10 2009/08/05 12:07:51 mschenke Exp $ # # Change History # 20090804 LDE Extend the error detection for DBD. =head1 NAME RDA::Macro::Dbi - Class Used for Database Macros =head1 SYNOPSIS require RDA::Macro::Dbi; =head1 DESCRIPTION The objects of the C class are used to interface with database-related macros. The following methods are available: =cut use strict; BEGIN { use Exporter; use File::Spec; use IO::Handle; use RDA::Object::Dbd; use RDA::Object::Sqlplus; } # Define the global public variables use vars qw($VERSION); $VERSION = sprintf("%d.%02d", q$Revision: 1.10 $ =~ /(\d+)\.(\d+)/); # Define the global private variables my $ERR = qr/(^ERROR at line \d+:$|error possibly near <\*> indicator)/i; my $EXT = qr/^[^:]+:\d+:{1,2}[^:]+$/; my $LOG = qr/^((ORA|SP2)-\d{4,}):\s*(.*)/; my $MAC = qr/^___Macro_(\w+)\((\d+)\)___$/; my $SID = qr/^([^:]+):(\d+):([^:]+)$/; my $SVC = qr/^([^:]+):(\d+):([^:]*):([^:]+)$/; my %tb_fct = ( 'clearLastDb' => [\&_m_clear_last, 'N'], 'clearDbColumns' => [\&_m_clear_columns, 'N'], 'getDbColumns' => [\&_m_get_columns, 'L'], 'getDbDesc' => [\&_m_get_desc, 'L'], 'getDbHits' => [\&_m_get_hits, 'L'], 'getDbLines' => [\&_m_get_lines, 'L'], 'getDbMessage' => [\&_m_get_message, 'T'], 'getDbName' => [\&_m_get_name, 'T'], 'getDbPrelim' => [\&_m_get_prelim, 'N'], 'getDbTimeout' => [\&_m_get_timeout, 'N'], 'getDbVersion' => [\&_m_get_version, 'T'], 'getDrivers' => [\&_m_get_drivers, 'L'], 'grepLastDb' => [\&_m_grep_last, 'L'], 'grepDb' => [\&_m_grep_sql, 'L'], 'hasDbPassword' => [\&_m_has_password, 'N'], 'isDbEnabled' => [\&_m_is_enabled, 'N'], 'isDriverAvailable' => [\&_m_is_available, 'T'], 'loadDb' => [\&_m_load_sql, 'N'], 'resetDbTimeout' => [\&_m_reset_timeout, 'N'], 'setDbAccess' => [\&_m_set_access, 'N'], 'setDbColumns' => [\&_m_set_columns, 'N'], 'setDbError' => [\&_m_set_error, 'N'], 'setDbFailure' => [\&_m_set_failure, 'N'], 'setDbHeader' => [\&_m_set_header, 'N'], 'setDbPassword' => [\&_m_set_password, 'N'], 'setDbPrelim' => [\&_m_set_prelim, 'N'], 'setDbTimeout' => [\&_m_set_timeout, 'N'], 'setDbTrace' => [\&_m_set_trace, 'N'], 'setDbType' => [\&_m_set_type, 'N'], 'switchDb' => [\&_m_switch, 'N'], 'testDb' => [\&_m_test_sql, 'T'], 'writeLastDb' => [\&_m_write_last, 'N'], 'writeDb' => [\&_m_write_sql, 'N'], ); my %tb_jus = ( FLOAT => 'L', NUMBER => 'L', ); my %tb_typ = ( CHAR => '%s', DATE => 'TO_CHAR(%s,\'DD-Mon-YYYY HH24:MI:SS\')', FLOAT => '%s', NCHAR => '%s', NUMBER => '%s', NVARCHAR2 => '%s', TIMESTAMP => 'TO_CHAR(%s,\'DD-Mon-YYYY HH24:MI:SSxFF\')', VARCHAR2 => '%s', ); # Report the module version number sub Version { $VERSION; } =head2 S<$h = RDA::Macro::Dbi-Enew($agt)> The object constructor. It takes the agent object reference as an argument. C is represented by a blessed hash reference. The following special keys are used: =over 12 =item S< B<'_agt'> > Reference to the agent object =item S< B<'_dbh'> > Current database handle =item S< B<'_dbi'> > Available DBI drivers =item S< B<'_dft'> > Default database handle =item S< B<'_dsc'> > Table description hash =item S< B<'_err'> > Number of SQL request errors =item S< B<'_frk'> > Fork indicator =item S< B<'_hit'> > Lines captured when executing SQL statements =item S< B<'_not'> > Statistics note =item S< B<'_out'> > Number of SQL requests timed out =item S< B<'_req'> > Number of SQL requests =item S< B<'_skp'> > Number of SQL requests skipped =item S< B<'_sql'> > Last SQL result =back Internal keys are prefixed by an underscore. =cut sub new { my $slf = shift; my $class = ref($slf) || $slf; my $agt = shift; my ($fil, $grp, $rpt, $sid, $typ, $usr); # Create the macro object $slf = bless {}, $class; # Setup some parameters by default $slf->{'_agt'} = $agt; $slf->{'_dsc'} = {}; $slf->{'_hit'} = []; $slf->{'_not'} = ''; $slf->{'_req'} = $slf->{'_err'} = $slf->{'_out'} = $slf->{'_skp'} = 0; $slf->{'_sql'} = []; # Determine the available DBI drivers unless ($agt->get_setting('NO_DBI')) { eval { require DBI; $slf->{'_dbi'} = {map {lc($_) => $_} DBI->available_drivers}; $slf->{'_dbh'} = RDA::Object::Dbd->new('Oracle', $agt) if exists($slf->{'_dbi'}->{'oracle'}); }; } $slf->{'_dbh'} = RDA::Object::Sqlplus->new($agt) unless exists($slf->{'_dbh'}); $slf->{'_frk'} = $agt->can_fork > 0; # Adapt the environment if ($agt->get_setting('DATABASE_LOCAL',1)) { $sid = $agt->get_setting('ORACLE_SID', ''); $ENV{'ORACLE_SID'} = $sid if $sid && $sid !~ $EXT; } # Delete previous temporary files $rpt = $agt->get_setting('RPT_DIRECTORY', File::Spec->curdir); $grp = $agt->get_setting('RPT_GROUP'); if (opendir(DIR, $rpt)) { while (defined($fil = readdir(DIR))) { next unless $fil =~ m/^tmp_$grp\_\d+_\d+.txt$/i; $fil = File::Spec->catfile($rpt, $fil); 1 while unlink($fil); } closedir(DIR); } # Register the macros $agt->register($slf, [keys(%tb_fct)], qw(stat thread)); # Return the object reference $slf; } =head2 S<$h-Ecall($name,...)> This method executes the macro code. =cut sub call { my ($slf, $nam, @arg) = @_; &{$tb_fct{$nam}->[0]}($slf, @arg); } =head2 S<$h-Eclr_stats($name)> This method resets the statistics and clears corresponding module settings. =cut sub clr_stats { my ($slf, $nam) = @_; my $agt = $slf->{'_agt'}; $slf->{'_not'} = ''; $slf->{'_req'} = $slf->{'_err'} = $slf->{'_out'} = $slf->{'_skp'} = 0; } =head2 S<$h-Eget_stats($name)> This method reports the library statistics in the specified module. =cut sub get_stats { my ($slf, $nam) = @_; # Add last database handle contribution _restore_dbh($slf); # Generate the statistics if ($slf->{'_req'}) { my $agt = $slf->{'_agt'}; # Get other macro library contributions $slf->{'_req'} += $agt->get_setting("SQL_STAT_$nam\_REQ", 0); $slf->{'_err'} += $agt->get_setting("SQL_STAT_$nam\_ERR", 0); $slf->{'_out'} += $agt->get_setting("SQL_STAT_$nam\_OUT", 0); $slf->{'_skp'} += $agt->get_setting("SQL_STAT_$nam\_SKIP", 0); # Generate the module statistics $agt->set_setting("SQL_STAT_$nam\_REQ", $slf->{'_req'}, 'N', 'Number of SQL requests'); $agt->set_setting("SQL_STAT_$nam\_ERR", $slf->{'_err'}, 'N', 'Number of SQL request errors'); $agt->set_setting("SQL_STAT_$nam\_OUT", $slf->{'_out'}, 'N', 'Number of SQL requests timed out'); $agt->set_setting("SQL_STAT_$nam\_SKIP", $slf->{'_skp'}, 'N', 'Number of SQL requests skipped'); $agt->set_setting("SQL_STAT_$nam\_NOTE", $slf->{'_not'}, 'T', 'SQL statistics note'); $agt->log('s', $nam, 'DB', $VERSION, $slf->{'_req'}, $slf->{'_err'}, $slf->{'_out'}, $slf->{'_skp'}, $slf->{'_not'}); } } sub _add_usage { my ($slf) = @_; my ($rec); # Get the database handle contribution $rec = $slf->{'_dbh'}->get_usage; # Increment the couters $slf->{'_req'} += $rec->{'req'}; $slf->{'_err'} += $rec->{'err'}; $slf->{'_out'} += $rec->{'out'}; $slf->{'_skp'} += $rec->{'skp'}; # Manage the note if (exists($rec->{'not'})) { $slf->{'_not'} = $rec->{'not'}; } elsif (!exists($slf->{'_not'}) && $rec->{'lim'} > 0) { $slf->{'_not'} = 'SQL execution limited to '.$rec->{'lim'}.'s' } } =head2 S<$h-Ereset> This method resets the object for its new environment to allow a thread-save execution. =cut sub reset { my ($slf) = @_; $slf->{'_dbh'}->reset; } =head2 S<$h-Erun($name,$arg,$ctx)> This method executes the macro with the specified argument list in a given context. =cut sub run { my ($slf, $nam, $arg, $ctx) = @_; my ($fct, $ret, $typ); $fct = $tb_fct{$nam}; $typ = $fct->[1]; # Treat an array context return RDA::Value::new_list(&{$fct->[0]}($slf, $ctx, $arg->eval_as_array)) if $typ eq 'L'; # Treat a scalar context $ret = &{$fct->[0]}($slf, $ctx, $arg->eval_as_array); $typ = 'U' unless defined($ret); return RDA::Value->new($typ, $ret); } =head1 DATABASE MACROS =head2 S This macro clears the last SQL result. =cut sub _m_clear_last { shift->{'_sql'} = []; } =head2 S This macro clears all information associated with the specified table identifier. =cut sub _m_clear_columns { my ($slf, $ctx, $tid) = @_; delete($slf->{'_dsc'}->{$tid}) if $tid; 0; } =head2 S This macro determines if the specified columns are present in the table and generates the header string and the select list accordingly. You can provide specific headers or select contributions through the C and C macros. RDA supports predefined data types only unless an explicit select contribution or an extra conversion format is specified. You can manage the data types list with the C macro. When no columns are specified, all table columns are considered. This macro returns a list containing the corresponding header and select list. If the table is not found or if the query identifier is missing, then the header and select list are undefined. =cut sub _m_get_columns { my ($slf, $ctx, $tid, $obj, @arg) = @_; my ($col, $dsc, $hdr, $jus, $row, $typ, @hdr, @sel, @tbl); # Get the table description and reject an unknown table return () unless $tid; $slf->{'_dsc'}->{$tid} = {} unless exists($slf->{'_dsc'}->{$tid}); unless (exists($slf->{'_dsc'}->{$tid}->{'typ'})) { $dsc = $slf->{'_dbh'}->describe($ctx, $obj); $slf->{'_dsc'}->{$tid}->{'row'} = $dsc->{'row'}; $slf->{'_dsc'}->{$tid}->{'typ'} = $dsc->{'typ'}; } $dsc = $slf->{'_dsc'}->{$tid}; return () unless scalar(@{$row = $dsc->{'row'}}); # Generate the row $row = \@arg if scalar(@arg); foreach my $nam (@$row) { $col = lc($nam); # Reject unknown column next unless exists($dsc->{'typ'}->{$col}) && defined($typ = $dsc->{'typ'}->{$col}); # Determine how to justify the column $jus = exists($dsc->{'jus'}->{$col}) ? $dsc->{'jus'}->{$col} : exists($tb_jus{$typ}) ? $tb_jus{$typ} : 'R'; # Identify the header contribution if (exists($dsc->{'hdr'}->{$col})) { $hdr = $dsc->{'hdr'}->{$col}; } else { $hdr = "*$col*"; $hdr =~ s#_# #g; $hdr =~ s#\b([a-z])#\U$1#g; $hdr = " $hdr" if $jus =~ m/L/; $hdr = "$hdr " if $jus =~ m/R/; } # Identify the select contribution $col = exists($dsc->{'col'}->{$col}) ? $dsc->{'col'}->{$col} : _desc_typ($dsc, $col, $typ); next unless $col; $col = "' ||\n $col || '"; $col = " $col" if $jus =~ m/L/; $col = "$col " if $jus =~ m/R/; push(@sel, $col); push(@hdr, $hdr); } # Return the header and select strings scalar(@sel) ? ('|'.join('|', @hdr).'|', "'|".join('|',@sel)."|'") : (); } sub _desc_typ { my ($dsc, $col, $typ) = @_; exists($dsc->{'fmt'}->{$typ}) ? sprintf($dsc->{'fmt'}->{$typ}, $col) : exists($tb_typ{$typ}) ? sprintf($tb_typ{$typ}, $col) : ''; } =head2 S This macro returns the description of the column as a list containing the column position and its type. Column positions start from 1. Unless the flag is set, only eligible columns are considered. The list is empty when the column is not found. =cut sub _m_get_desc { my ($slf, $ctx, $tid, $col, $flg) = @_; my ($cnt, $dsc, $val); # Get the query entry return () unless $col && $tid && exists($slf->{'_dsc'}->{$tid}); $dsc = $slf->{'_dsc'}->{$tid}; # Validate the column $col = lc($col); return () unless exists($dsc->{'typ'}->{$col}) && defined($dsc->{'typ'}->{$col}); # Search for the column $cnt = 0; foreach my $nam (@{$dsc->{'row'}}) { # Identify the select contribution unless ($flg) { $val = exists($dsc->{'col'}->{$nam}) ? $dsc->{'col'}->{$nam} : _desc_typ($dsc, $nam, $dsc->{'typ'}->{$nam}); next unless $val; } # Check the column ++$cnt; return ($cnt, $dsc->{'typ'}->{$col}) if $col eq $nam; } # Indicate that the column has not been found (); } =head2 S This macro returns the list of lines captured during the last C. =cut sub _m_get_hits { @{shift->{'_hit'}}; } =head2 S This macro returns a range of the lines of the last SQL result. It assumes the first and last line as the default for the range definition. You can use negative line numbers to specify lines from the buffer end. =cut sub _m_get_lines { my ($slf, $ctx, $min, $max) = @_; my $buf; # Validate the range $buf = $slf->{'_sql'}; $min = (!defined($min) || ($#$buf + $min) < -1) ? 0 : ($min < 0) ? $#$buf + $min + 1 : $min; $max = (!defined($max)) ? $#$buf : (($#$buf + $max) < -1) ? 0 : ($max < 0) ? $#$buf + $max + 1 : ($max > $#$buf) ? $#$buf : $max; # Return the line range @$buf[$min..$max]; } =head2 S This macro returns the error message of the last SQL execution. If no error is detected, it returns C. =cut sub _m_get_message { shift->{'_dbh'}->get_message; } =head2 S This macro returns the database name. =cut sub _m_get_name { my ($slf, $ctx) = @_; $slf->{'_dbh'}->get_name($ctx); } =head2 S This macro indicates whether the C option is set. =cut sub _m_get_prelim { shift->{'_dbh'}->get_prelim; } =head2 S This macro returns the current duration of the SQL timeout. If this mechanism is disabled, it returns 0. =cut sub _m_get_timeout { shift->{'_dbh'}->get_timeout; } =head2 S This macro returns the database version. =cut sub _m_get_version { my ($slf, $ctx) = @_; $slf->{'_dbh'}->get_version($ctx); } =head2 S This macro returns the list of available drivers. =cut sub _m_get_drivers { my ($slf) = @_; return () unless exists($slf->{'_dbi'}); (sort values(%{$slf->{'_dbi'}})); } =head2 S This macro returns the lines of the last SQL result that match the regular expression. It supports the same options as C. =cut sub _m_grep_last { my ($slf, $ctx, $re, $opt) = @_; my ($flg, $inv, $one, @tbl); if ($re) { # Determine the options $opt = '' unless defined($opt); $one = index($opt, 'f') >= 0; $re = (index($opt, 'i') < 0) ? qr#$re# : qr#$re#i; $inv = index($opt, 'v') >= 0; # Check the last SQL result foreach my $lin (@{$slf->{'_sql'}}) { $flg = ($lin =~ $re); if ($inv ? !$flg : $flg) { push(@tbl, $lin); last if $one; } } } @tbl; } =head2 S This macro returns the lines that match the regular expression. The following options are supported: =over 9 =item B< 'f' > Stops scanning on the first match =item B< 'i' > Ignores case distinctions in both the pattern and the results =item B< 'v' > Inverts the sense of matching to select non-matching lines =back =cut sub _m_grep_sql { my ($slf, $ctx, $sql, $re, $opt) = @_; my ($flg, $inv, $one, @tbl); # Determine the options $opt = '' unless defined($opt); $one = index($opt, 'f') >= 0; $re = (index($opt, 'i') < 0) ? qr#$re# : qr#$re#i; $inv = index($opt, 'v') >= 0; # Check the SQL output $slf->{'_dbh'}->execute($ctx, $sql, 1, \&_grep_sql, [$re, \@tbl, $inv, $one]) if $re; @tbl; } sub _grep_sql { my ($dbh, $rec, $lin) = @_; my $flg; # Interrupt when a SQL error is encountered die "RDA-00507: SQL error\n" if $lin =~ $ERR && $dbh->{'err'}; # Check if the line matches the pattern $flg = ($lin =~ $rec->[0]); if ($rec->[2] ? !$flg : $flg) { push(@{$rec->[1]}, $lin); return $rec->[3]; } # Continue the search 0; } =head2 S This macro indicates whether a password hash entry exists for the specified user already. You can provide an alternative Oracle system identifier as the second argument. =cut sub _m_has_password { my ($slf, $ctx, $usr, $typ, $sid) = @_; return 0 unless $usr; # Parse the arguments if ($usr =~ m/^([^@]*)@((\w+)\@)?(\S+)/) { $usr = $1; $typ = $3 if defined($3); $sid = $4; } else { $usr = $1 if $usr =~ m/^([^@]*)@/; $sid = '' unless defined($sid); } $typ = (defined($typ) && $typ ne '+') ? lc($typ) : 'oracle'; # Normalize the arguments if ($typ eq 'oracle') { $usr = uc($usr); $sid = _chk_sid($sid); } # Indicate if a password has been provided for that user $slf->{'_agt'}->has_password($typ, $sid, $usr); } =head2 S This macro indicates whether a SQL statement will be executed. =cut sub _m_is_enabled { shift->{'_dbh'}->is_enabled; } =head2 S This macro indicates whether a driver is available for the specified database type. =cut sub _m_is_available { my ($slf, $ctx, $typ) = @_; exists($slf->{'_dbi'}) && exists($slf->{'_dbi'}->{lc($typ)}) ? $slf->{'_dbi'}->{lc($typ)} : undef; } =head2 S This macro loads the output of the SQL statement as the last SQL result. It clears the previous result unless the flag is set. It returns 1 for a successful completion. If the execution time exceeds the limit or if the maximum number of attempts has been reached, then it returns 0. It is possible to increase the execution limit by specifying an increasing factor as an argument. A negative value disables any timeout. Only lines between C<___Cut___> lines are inserted in the last SQL result. =cut sub _m_load_sql { my ($slf, $ctx, $sql, $flg, $inc) = @_; $slf->{'_sql'} = [] unless $flg; $slf->{'_dbh'}->execute($ctx, $sql, $inc, \&_load_sql, [$ctx, $slf->{'_sql'}]); } sub _load_sql { my ($dbh, $rec, $lin) = @_; # Interrupt when a SQL error is encountered die "RDA-00507: SQL error\n" if $lin =~ $ERR && $dbh->{'err'}; # Save the line in the last SQL result push(@{$rec->[1]}, $lin); # Continue the result processing 0; } =head2 S This macro resets the remaining alarm time to the SQL timeout value. To allow more time for executing statements, you can specify a factor as an argument. 1 is the default. For a positive value, the maximum execution time is obtained by multiplying the SQL timeout value by this factor. Otherwise, it disables the alarm mechanism. The effective value is returned. =cut sub _m_reset_timeout { my ($slf, $ctx, $inc) = @_; $slf->{'_dbh'}->reset_timeout($inc); } =head2 S This macro manages the access/connect error flag. When the flag is set, it generates an error when an access/connect error is detected. It returns the previous value of the flag. =cut sub _m_set_access { my ($slf, $ctx, $flg) = @_; $slf->{'_dbh'}->set_access($flg); } =head2 S This macro specifies the select list contribution for one or more columns. An undefined value deletes an existing contribution. When no columns are specified, all previous declarations are deleted. =cut sub _m_set_columns { my $slf = shift; my $ctx = shift; my $tid = shift; my ($dsc, $key, $val); # Get the query entry return 0 unless $tid; $slf->{'_dsc'}->{$tid} = {} unless exists($slf->{'_dsc'}->{$tid}); $dsc = $slf->{'_dsc'}->{$tid}; # Manage select list contributions if (scalar(@_)) { while (($key, $val) = splice(@_, 0, 2)) { if (defined($val)) { $dsc->{'col'}->{lc($key)} = $val; } else { delete($dsc->{'col'}->{lc($key)}); } } } else { delete($dsc->{'col'}); } 1; } =head2 S This macro manages the SQL error flag. When the flag is set, it generates an error when a SQL error is detected. It returns the previous value of the flag. =cut sub _m_set_error { my ($slf, $ctx, $flg) = @_; $slf->{'_dbh'}->set_error($flg); } =head2 S This macro manages the number of SQL script failures. A negative value disables any further database connection. It returns the previous value of the counter. =cut sub _m_set_failure { my ($slf, $ctx, $cnt) = @_; $slf->{'_dbh'}->set_failure($cnt); } =head2 S This macro specifies the header contribution for one or more columns. The justification is deduced from the presence of leading and/or trailing spaces. An undefined value deletes an existing contribution. When no columns are specified, all previous declarations are removed. =cut sub _m_set_header { my $slf = shift; my $ctx = shift; my $tid = shift; my ($dsc, $key, $val); # Get the query entry return 0 unless $tid; $slf->{'_dsc'}->{$tid} = {} unless exists($slf->{'_dsc'}->{$tid}); $dsc = $slf->{'_dsc'}->{$tid}; # Manage header contributions if (scalar(@_)) { while (($key, $val) = splice(@_, 0, 2)) { $key = lc($key); if (defined($val)) { $dsc->{'hdr'}->{$key} = $val; $dsc->{'jus'}->{$key} = ($val =~ m/^\s+\*/ ? 'L' : '') .($val =~ m/\*\s+$/ ? 'R' : ''); } else { delete($dsc->{'hdr'}->{$key}); delete($dsc->{'jus'}->{$key}); } } } else { delete($dsc->{'hdr'}); delete($dsc->{'jus'}); } 1; } =head2 S This macro adds the specified login information in the password table. =cut sub _m_set_password { my ($slf, $ctx, $usr, $pwd, $typ, $sid) = @_; my ($suf, $old); return 0 unless defined($usr); # Parse the arguments if ($usr =~ m/^([^@]*)@((\w+)\@)?(\S+)/) { $usr = $1; $typ = $3 if defined($3); $sid = $4; } else { $usr = $1 if $usr =~ m/^([^@]*)@/; $sid = '' unless defined($sid); } ($usr, $pwd) = ($1, $2) if $usr =~ m/^(.*?)\/(.*)$/; $typ = (defined($typ) && $typ ne '+') ? lc($typ) : 'oracle'; # Normalize the arguments if ($typ eq 'oracle') { $usr = uc($usr); $sid = _chk_sid($sid); } # Store the login information return 0 unless defined($pwd); $pwd =~ s/\r\n//g; $slf->{'_agt'}->set_password($typ, $sid, $usr, $pwd); 1; } =head2 S This macro controls the C option. When the flag is set, it activates the option. Otherwise, it removes the option. It returns the previous status. =cut sub _m_set_prelim { my ($slf, $ctx, $flg) = @_; $slf->{'_dbh'}->set_prelim($flg); } =head2 S This macro sets the SQL timeout, specified in seconds, only if the value is greater than zero. Otherwise, the timeout mechanism is disabled. It is disabled also if the alarm function is not implemented. It returns the effective value. =cut sub _m_set_timeout { my ($slf, $ctx, $val) = @_; $slf->{'_dbh'}->set_timeout($val); } =head2 S This macro manages the SQL trace flag. When the flag is set, it prints all SQL lines to the screen. It remains unchanged if the flag value is undefined. It returns the previous value of the flag. =cut sub _m_set_trace { my ($slf, $ctx, $flg) = @_; $slf->{'_dbh'}->set_trace($flg); } =head2 S This macro specifies how to format data types. You can use an empty string to reject a predefined data type. An undefined value deletes an existing declaration. When no types are specified, all previous declarations are deleted. =cut sub _m_set_type { my $slf = shift; my $ctx = shift; my $tid = shift; my ($dsc, $key, $val); # Get the query entry return 0 unless $tid; $slf->{'_dsc'}->{$tid} = {} unless exists($slf->{'_dsc'}->{$tid}); $dsc = $slf->{'_dsc'}->{$tid}; # Manage select list contributions if (scalar(@_)) { while (($key, $val) = splice(@_, 0, 2)) { if (defined($val)) { $dsc->{'fmt'}->{uc($key)} = $val; } else { delete($dsc->{'fmt'}->{uc($key)}); } } } else { delete($dsc->{'fmt'}); } 1; } =head2 S This macro switches the current database handle. It restores the default the database handle when no database type is specified or when the corresponding driver is not available. Using C<+> as database type forces SQL*Plus use. The default database handle is restores at data collection module completion. When the password is not specified, it is asked interactively at the first statement execution. By default, the user is derived from the C setting. =cut sub _m_switch { my ($slf, $ctx, $typ, $usr, $pwd, $sid, $dba) = @_; !defined($typ) ? _restore_dbh($slf) : exists($slf->{'_dbi'}->{$typ = lc($typ)}) ? _save_dbh($slf, RDA::Object::Dbd->new($slf->{'_dbi'}->{$typ}, $slf->{'_agt'}, $usr, $pwd, $sid, $dba)) : ($typ eq 'oracle' || $typ eq '+') ? _save_dbh($slf, RDA::Object::Sqlplus->new($slf->{'_agt'}, $usr, $pwd, $sid, $dba)) : _restore_dbh($slf); 1; } sub _restore_dbh { my ($slf) = @_; if (exists($slf->{'_dft'})) { _add_usage($slf); $slf->{'_dbh'}->delete; $slf->{'_dbh'} = delete($slf->{'_dft'}); } 1; } sub _save_dbh { my ($slf, $dbh) = @_; _add_usage($slf); if (exists($slf->{'_dft'})) { $slf->{'_dbh'}->delete; } else { $slf->{'_dft'} = $slf->{'_dbh'}; } $slf->{'_dbh'} = $dbh; 1; } =head2 S This macro tests the database connection. In case of problems, further access is disabled. =cut sub _m_test_sql { my ($slf, $ctx) = @_; $slf->{'_dbh'}->test($ctx); } =head2 S This macro writes a line range from the last SQL result to the output file. It assumes respectively the first and last line as the default for the range definition. You can use negative line numbers to specify lines from the buffer end. It returns 1 for a successful completion. Otherwise, it returns 0. =cut sub _m_write_last { my ($slf, $ctx, $min, $max) = @_; my ($buf, $rec); # Validate the range $buf = $slf->{'_sql'}; $min = (!defined($min) || ($#$buf + $min) < -1) ? 0 : ($min < 0) ? $#$buf + $min + 1 : $min; $max = (!defined($max)) ? $#$buf : (($#$buf + $max) < -1) ? 0 : ($max < 0) ? $#$buf + $max + 1 : $max; $rec = [$ctx, undef, 0]; foreach my $lin (@$buf[$min..$max]) { return 0 if _write_sql($slf, $rec, $lin); } 1; } =head2 S This macro writes the output of the SQL statements in the output file. The request job is composed of following directives: =over 4 =item * #CALL EnameE(EnE) It executes the specified macro before treating the next directive. =item * #CUT It inserts a C<___Cut___> line in the result flow. =item * #ECHO EstrE It inserts the string as a line in the result flow. =item * #EXIT It disconnects from the database and aborts the job. =item * #LONG(EnE) It indicates the maximum length of C type fields that the driver can read. It resets the default length at job completion. =item * #MACRO EnameE(EnE) It inserts a C<___Macro_EnameE(EnumE)___> line in the result flow. Those lines are replaced by the execution of the specified macro with CnumE> as argument. A positive return value resets the alarm. =item * #PLSQL or #PLSQLEnE It extract all lines until it finds a C line. It considers them as a PL/SQL block and inserts its output in the result flow. A number can be included in the directive to better locate instructions causing timeouts. =item * #QUIT It disconnects from the database and aborts the job. =item * #SLEEP(EnE) It makes a pause of the specified number of seconds. =item * #SQL or #SQLEnE It extract all lines until it finds a C line. It considers them as a SQL statement and inserts its result in the result flow. A number can be included in the directive to better locate instructions causing timeouts. =back Only lines between C<___Cut___> lines are inserted in the output file. It is possible to increase the execution limit by specifying an increasing factor as an argument. A negative value disables timeout. It returns 1 for a successful completion. If the execution time exceeds the limit or if the maximum number of attempts has been reached, it returns 0. =cut sub _m_write_sql { my ($slf, $ctx, $sql, $inc, @arg) = @_; my ($tbl); # Get the regular expressions foreach my $str (@arg) { next unless defined($str); $tbl = [] unless ref($tbl); push(@$tbl, qr#$str#); } # Execute the SQL statement $slf->{'_dbh'}->execute($ctx, $sql, $inc, \&_write_sql, [$ctx, undef, $slf->{'_frk'} ? $slf->{'_dbh'}->get_alarm($inc) : 0, $tbl, $slf->{'_hit'} = []]); } sub _write_sql { my ($dbh, $rec, $lin) = @_; if ($lin =~ $MAC) { my ($val); # Suspend alarm $dbh->{'dur'} = $rec->[2] ? alarm(0) + 1 : 0; # Execute a macro $val = RDA::Value->new('N', $2); $val = RDA::Value->new('L', [$val]); $val = RDA::Value->new('M', $rec->[0], $1, $val); eval { $dbh->reset_timeout($val) if ($val = $val->eval_as_number) > 0; }; # Must clear it, to execute a prefix block on next write $rec->[1] = undef; # Restart the alarm when suspended alarm($dbh->{'dur'}) if $dbh->{'dur'}; } elsif ($lin =~ $ERR && $dbh->{'err'}) { # Generate a SQL error die("RDA-00507: SQL error\n"); } else { my ($lim, $ofh, $val); # Get the output file handle, with the alarm suspended unless ($ofh = $rec->[1]) { $lim = $rec->[2] ? alarm(0) + 1 : 0; $rec->[1] = $ofh = $rec->[0]->get_handle; alarm($lim) if $lim; } # Write the line to the output file print $ofh "$lin\n"; if ($rec->[3]) { foreach my $re (@{$rec->[3]}) { if ($lin =~ $re) { push(@{$rec->[4]}, $lin); last; } } } } # Continue the result processing 0; } # --- Internal functions ------------------------------------------------------ # Check the SID sub _chk_sid { my ($sid, $dft) = @_; return $dft unless $sid; ($sid =~ $SID) ? join(':', $1, $2, uc($3)) : ($sid =~ $SVC) ? join(':', $1, $2, $3, uc($4)) : uc($sid); } 1; __END__ =head1 SEE ALSO L, L, L, L, L =head1 COPYRIGHT NOTICE Copyright (c) 2002-2009, Oracle. All rights reserved. =head1 TRADEMARK NOTICE Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. =cut