#
# -------------------------------------------------------------------------------------------------------------------------------
# --
# -- Mandator: Open MaxDB Group - Data Management.
# -- Legacy System: omdg
# -- Script: omdg_sqlsamples_x_html.pl
# -- Theme: Script to extract data from table hotel.omdg_samples and to transform it to HTML output.
# -- Demonstration of
# -- - Connectivity of MaxDB using perl
# -- - Demonstration of reading multiple line LONG columns
# -- Author: RDa
# -- Synopsis: perl omdg_sqlsamples_x_html.pl
# -- Interface definition: hotel.omdg_sqlsamples
# -- Modify date: 2008.02.02 - RDa - (OMDG, Ralf Dahmen) - Creation for demonstration
# -------------------------------------------------------------------------------------------------------------------------------
#
use strict;
use DBI;
use Time::HiRes;
my @lines;
my $filcnt;
my $head = '' . "\n" .
'' . "\n" .
'' . "\n" .
'' . "\n" .
'' . "\n" .
'' . "\n" .
'' . "\n" .
'' . "\n" .
'OMDG-Examples' . "\n" .
'' . "\n" .
'' . "\n";
# A ODBC system DSN, named admin must be predefined, which refers to a MaxDB instance
# Create a $dbh session, if succeeds continue otherwise die the execution
# Connect to DB User mona,red
my $dbh = DBI->connect("DBI:ODBC:dsn=admin","MONA","RED") or
die "Unable to connect" . $DBI::errstr . "\n";
# You have to set this value for MaxDB/Oracle
$dbh->{LongReadLen}=100000;
# Prepare and Execute the SELECT statement or die
# Store the result (pointer) in variable $sth (statement handler)
my $cmd = "
SELECT groupid || '-' || LFILL(CHR(ident),'0',5) || '-' || categoryid AS filename
,groupid
,categoryid
,LFILL(CHR(ident),'0',5) AS ident
,statement
,result
FROM hotel.omdg
ORDER BY filename
";
my $sth = $dbh->prepare($cmd) or
die "Unable to prepare command: " . $DBI::errstr . "\n";
$sth->execute() or
die "Unable to execute command: " . $DBI::errstr . "\n";
unless (open (IDX, "> htm/_index.htm")) {
printlog('E', "File could not be opened: htm/_index.htm");
return 0;
}
binmode(IDX);
unless (open (ALL, "> htm/_omdg.htm")) {
printlog('E', "File could not be opened: htm/_omdg.htm");
return 0;
}
binmode(ALL);
print ALL $head;
unless (open (BAT, "> raw/_testall.cmd")) {
printlog('E', "File could not be opened: raw/_testall.bat");
return 0;
}
binmode(BAT);
# Fetch the statement handler
while (my $row = $sth->fetchrow_hashref) {
$filcnt++;
# Get the contents of DB column groupd
my $filename = $row->{"FILENAME"};
unless (openfiles($filename)) { return 0; };
my ($time,$date,$now) = gettime();
my $grp = $row->{"GROUPID"};
my $cat = $row->{"CATEGORYID"};
my $ide = $row->{"IDENT"};
my $sql = $row->{"STATEMENT"};
my $res = $row->{"RESULT"};
my $title = $filename . ".html" . ' (Last Generation: ' . $date . ')';
# Read and print LONG column line by line
my $statement;
@lines = split (/\n/, $sql);
for (my $j = 0; $j < @lines; $j++) {
my $line = @lines[$j];
$statement = $statement . $line . "\n";
}
# Substitute special character for proper html display
$statement =~ s/&/&/g;
$statement =~ s//>/g;
$statement =~ s/"/"/g;
my $result;
@lines = split (/\n/, $res);
for (my $j = 0; $j < @lines; $j++) {
my $line = @lines[$j];
$result .= $line . "\n";
}
# Substitute special character for proper html display
$result =~ s/&/&/g;
$result =~ s//>/g;
$result =~ s/"/"/g;
print BAT "call " . $filename . ".cmd\n";
print IDX '' . $filename . ".html" . "\n";
print HTM $head;
print HTM "\n| ". $grp . " | " . $statement . " |
";
print HTM "\n| Result | " . $result . " |
\n";
print CMD "sqlcli -d admin -u mona,red -f -i " . $filename . ".lob -o " . $filename . ".txt\n";
print DEL $grp . "\t" . $cat . "\t" . $ide . "\n";
print LOB $sql;
print ALL "\n| ". $grp . " | " . $statement . " |
";
print ALL "\n| Result | " . $result . " |
";
print ALL "\n | |
";
}
# Release user session with implicit COMMIT
$dbh->disconnect or warn $DBI::errstr;
print ALL "\n\n