Examples for the SAP::DBTech::loader Module

To call the sample.pl perl script and log on with Loader to the DEMODB database instance as database user MONA with the password RED, enter the following command on the command line:

perl sample.pl MONA RED DEMODB

Generating a Connection

# To reference Perl libraries:

# ----------------------------

use SAP::DBTech::loader;

# To parse the call arguments

# --------------------------

my $user_name = $ARGV [0];

my $password = $ARGV [1];

my $database_name = $ARGV [2];

 

# To create a Loader session: (There is still no connection

# to the database instance)

# ------------------------------------------------------

my $session = new Loader ();

 

# Log on to the database instance

# -------------------------------------

$session->cmd ("use user $user_name $password serverdb $database_name");

 

# Log off:

# -------------------------------------

$session->release ();

Variant

To prevent the user name and password from being converted into uppercase, enclose "use user" within double quotes in your command.

$session->cmd ("use user \”$user\”_name \”$password\” serverdb $database_name");

 

The following examples show a shortened login process:

my $session = new Loader ()

$session->cmd ("use user ${ARGV [0]} ${ARGV [1]} serverdb ${ARGV [2]}");

Executing Loader Commands

With the method cmd you can execute both Loader commands and SQL statements.

use SAP::DBTech::loader;

# To generate a Loader session:

my $session = new Loader ();

$session->cmd ("use user ${ARGV [0]} ${ARGV [1]} serverdb ${ARGV [2]}");

# To execute an SQL statement:

$session->cmd("

     CREATE TABLE customer2

     (

      cno    FIXED(4,0)  PRIMARY KEY,

       title              CHAR(7),

       firstname          CHAR(10),

       name               CHAR(10)    NOT NULL,

       zip                CHAR(5),

      address     CHAR(25) NOT NULL)");

# To execute a DATALOAD statement:

$session->cmd("

DATALOAD TABLE customer

      cno        1

      title      2

      firstname  3

      name       4

      zip        5

      address    6

      INFILE 'customer.dat'");

Handling Error Situations

If an error occurs within the cmd method, an exception object of the LoaderError class is generated. This exception object can be identified and handled within a try-except statement.

The sql method provides simplified access to errors within SQL statements. If an SQL error occurs within an sql method, an exception is not generated, instead an error number is returned to the caller. This error number can be queried in the program.

use SAP::DBTech::loader;

# To generate a Loader session:

my $session = new Loader ();

$session->cmd ("use user ${ARGV [0]} ${ARGV [1]} serverdb ${ARGV [2]}");

# To query whether a table exists:

my $sqlrc = $session->sql ("EXISTS TABLE customer2");

# The table is created only if it

# does not already exist

if ($sqlrc == -4004) {

    $session->cmd("

        CREATE TABLE customer2

         (

            cno    FIXED(4,0)  PRIMARY KEY,

             title              CHAR(7),

             firstname          CHAR(10),

             name               CHAR(10)    NOT NULL,

             zip                CHAR(5),

            address     CHAR(25) NOT NULL)");

}

# To execute a DATALOAD statement:

my $filename = 'customer.dat';

eval {

    $session->cmd("

        DATALOAD TABLE customer2

        cno        1

        title      2

        firstname  3

        name       4

        zip        5

        address    6

        INFILE '$filename'");

};

# To output the error message:

if ($@) {

    print "DATALOAD of file $filename failed: $@\n";

}

Sample Data

The content of the customer.dat file used in the examples is as follows:

"3000","Mrs","Jenny","Porter","10580","1340 N.Ash Street, #3"

"3100","Mr","Peter","Brown","48226","1001 34th Str., APT.3"

"3200","Company","?","Datasoft","90018","486 Maple Str."

"3300","Mrs","Rose","Brian","75243","500 Yellowstone Drive, #2"

"3400","Mrs","Mary","Griffith","20005","3401 Elder Lane"

"3500","Mr","Martin","Randolph","60615","340 MAIN STREET, #7"

"3600","Mrs","Sally","Smith","75243","250 Curtis Street"

"3700","Mr","Mike","Jackson","45211","133 BROADWAY APT. 1"

"3800","Mrs","Rita","Doe","97213","2000 Humboldt Str., #6"

"3900","Mr","George","Howe","75243","111 B Parkway, #23"

"4000","Mr","Frank","Miller","95054","27 5th Str., 76"

"4100","Mrs","Susan","Baker","90018","200 MAIN STREET, #94"

"4200","Mr","Joseph","Peters","92714","700 S. Ash Str., APT.12"

"4300","Company","?","TOOLware","20019","410 Mariposa Str., # 10"

"4400","Mr","Antony","Jenkins","20903","55 A Parkway, #15"