Perl Scripting Examples

Createuser.pl

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(); 

Createusers.pl

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;

Maketable.pl

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;
}