The following is the simplest example of a Perl script using Essbase.pm. The script establishes a connection to the Essbase Server, creates a user, and disconnects.
# Use the Essbase.pm module. This statement is required to use Essbase within a Perl script. use Essbase; # Create a handle to the Essbase Server by connecting as admin, mypassword to the local machine. my $dbh = Essbase->connect("admin", "mypassword", "localhost"); # Use the do Perl function to pass the MaxL create user statement (enclosed in quotation marks) to the Essbase Server. $dbh->do("create user Essbase identified by mypassword"); # Disconnect from the Essbase Server. $dbh->disconnect();
The following Perl script tests whether Perl is able to use the MaxL Perl Module. If Essbase.pm is loaded, the program establishes a connection to Essbase, creates three users with different passwords, and disconnects.
######################### print on failure. BEGIN { $| = 1; } END {print "ERROR: System NOT Loaded\n" unless $loaded;} use Essbase; $loaded = 1; ######################### sub create_user { # In connect statements, replace the sample login details. my $dbh = Essbase->connect("admin", "pass1", "localhost"); # Create array of users. @user = ( "Fred", "George", "Mary", ); # Create array of passwords. @password = ( "password1", "password2", "password3", ); $i = 0; while ($i le 2) { $username = $user[$i]; $newpassword = $password[$i]; $j = $i + 1; print $dbh->do("create user $username identified by $newpassword") == 0 ? "user$j created\n" : "ERROR: user user$j NOT created\n"; $i = $i + 1; } print $dbh->disconnect() == 0 ? "Essbase database handle released\n" : "ERROR: Essbase database handle NOT released\n"; } # # Create user test. # &create_user;
The following subroutines from a Perl script return a message list that resulted from executing a MaxL statement, and build a table from a result set.
use Essbase;
#
# Returns a message list that resulted from executing
# a MaxL statement.
#
sub msgs
{
my $dbh = shift(@_);
my $msglist;
# dump all messages one thread at a time
while (1)
{
my ($msgno, $level, $msg);
($msgno, $level, $msg) = $dbh->pop_msg();
# gets us out of the loop if a $msg comes back as undef
last if ! $msg;
$msgstr = sprintf " %-8d", $msgno;
$msglist .= "$msgstr - $msg\n";
}
return $msglist;
}
#
# Returns a result set in the form of a table.
#
sub tab
{
my $dbh = shift;
my ($colnum, $rec, $dt, $name, $tab, $line);
# build an output table
# setup the header
($name, $dt) = $dbh->fetch_desc();
for ($col = 0; $col < $dbh->{NUM_OF_FIELDS}; $col++)
{
$str = sprintf " %-19.19s", $name->[$col];
$tab .= $str;
$line .= "+-------------------";
}
$tab .= "\n$line\n";
# now populate the table with data
$rec = $dbh->fetch_row();
while(defined($rec))
{
for ($col = 0; $col < $dbh->{NUM_OF_FIELDS}; $col++)
{
if ($dt->[$col] == 3) {
#format for characters
$str = sprintf " %-19.19s", $rec->[$col];
} elsif ($dt->[$col] == 2) {
#format for numbers
$str = sprintf " %19.19s", $rec->[$col];
} elsif ($dt->[$col] == 1) {
#format for bools
if ($rec->[$col] == 0) {
$str = sprintf " %19.19s", "FALSE";
} else {
$str = sprintf " %19.19s", "TRUE";
}
}
$tab .= $str;
}
$tab .= "\n";
$rec = $dbh->fetch_row();
}
$tab .= "\n";
if ($tab=~s/^\n//)
{
$tab="";
}
return $tab;
}