Hello

I have a problem to resolve: I wrote a Perlscript which caches data from a
server (local on my machine) I would like to have a other connection to a
remote server but I don't know how to define the servername / hostname in
my Perl Progrem..

Here is the code:

#!/usr/bin/perl

#############################################################################
# Code to connect to a database (local and get / put somme parameters in
it
#

#############################################################################

select STDERR; $ |= 1 ;
select STDOUT; $ |= 1 ;

#!/usr/bin/perl -w

use DBI;
use DBD::DB2;
use DBD::DB2::Constants;

my $dsn = 'DBI:DB2:LOGONDB';

my $uid = 'testusr';
my $pwd = 'test';

# connect to the database
print "----------------------------------------------------------\n";
print "Connect to Database LOGONDB \n";
print "----------------------------------------------------------\n";
print "Connecting to database...  \n\n";
my  $dbh = DBI->connect( $dsn, $uid, $pwd , {AutoCommit => 0 })
           || die "Can't connect to $database: $DBI::errstr";
print "Connected to database.  \n\n\n";

print "Program Version Information \n\n";
print "Operating System = $^O\n";
print "Perl Binary      = $^X\n";
print "Perl Version     = $]\n";
print "DBI Version      = $DBI::VERSION\n";
print "DBD::DB2 Version = $DBD::DB2::VERSION\n\n";

print "Database Connection Information \n\n";
printf( "Server Port     : %s\n", $dbh->get_info( SQL_SERVER_PORT ) );
printf( "Server Instance     : %s\n", $dbh->get_info( SQL_SERVER_NAME ) );
printf( "Database Server     : %s\n", $dbh->get_info( SQL_DBMS_NAME ) );
printf( "Database Version    : %s\n", $dbh->get_info( SQL_DBMS_VER ) );
printf( "Database Alias      : %s\n", $dbh->get_info( SQL_DATA_SOURCE_NAME
) );
printf( "Database Codepage   : %s\n", $dbh->get_info( 2519 ) );
printf( "Application Codepage: %s\n", $dbh->get_info( 2520 ) );
printf( "Authorization Id    : %s\n", $dbh->get_info( SQL_USER_NAME ) );
printf( "Max Identifier Len  : %s\n", $dbh->get_info(
SQL_MAX_IDENTIFIER_LEN ) );
printf( "Max Table Name Len  : %s\n", $dbh->get_info(
SQL_MAX_TABLE_NAME_LEN ) );
printf( "Max Index Size      : %s\n", $dbh->get_info( SQL_MAX_INDEX_SIZE )
);
printf( "Max Columns in Table: %s\n", $dbh->get_info(
SQL_MAX_COLUMNS_IN_TABLE ) );
printf( "Max Columns in Index: %s\n", $dbh->get_info(
SQL_MAX_COLUMNS_IN_INDEX ) );

# insert rows into the 'staff' table
BasicInsert();

# perform a query with the 'org' table
BasicQuery();

# update a set of rows in the 'staff' table
BasicUpdate();

# delete a set of rows from the 'staff' table
BasicDelete();

# no more data to be fetched from the statement handle
$sth ->finish;

 # rollback the transaction
 printf "Rollback the transaction.  \n\n";
 $dbh ->rollback;

# disconnect from the database
print "Disconnecting from database...\n\n";
$dbh ->disconnect
 || die "Can't disconnect from database: $DBI::errstr";
print "Disconnected from database.  \n\n";

#############################################################################
# Description: This subroutine demonstrates how to insert rows into a
table.
# Input      : None
# Output     : Returns 0 on success, exits otherwise.

#############################################################################
sub BasicInsert
{
 # display contents of the 'staff' table before inserting rows
 DisplayStaffTable();

 print "----------------------------------------------------------  \n";
 print "BasicInsert Staff Table \n";
 print "----------------------------------------------------------  \n";

 # use the INSERT statement to insert data into the 'staff' table.
 print "Execute the Statement:  \n";
 print "  INSERT INTO staff(id, name, dept, job, year, salary)  \n";
 print "    VALUES(380, 'Pearce', 35, 'Clerk', 5 , 13217.50),  \n";
 print "          (390, 'Hachey', 39, 'Mgr', 3 ,21270.00),  \n";
 print "          (400, 'Wagland', 45, 'Clerk', 10 , 14575.00)  \n";

 my  $sql = qq(INSERT INTO staff(id, name, dept, job, year, salary)
                VALUES ( 380 , 'Pearce' , 38 , 'Clerk' , 5 , 13217.50 ),
                       ( 390 , 'Hachey' , 38 , 'Mgr' , 3 ,21270.00 ),
                       ( 400 , 'Wagland' , 38 , 'Clerk' , 10 , 14575.00
));

 # execute the insert statement
 $dbh -> do ( $sql );

 # display the content in the 'staff' table after the INSERT.
 DisplayStaffTable();

 # rollback the transaction
#  printf "Rollback the transaction.  \n\n";
#  $dbh ->rollback;

 return  0 ;
} # BasicInsert

#############################################################################
# Description: This subroutine demonstrates how to perform a standard
query.
# Input      : None
# Output     : Returns 0 on success, exits otherwise.

#############################################################################
sub BasicQuery
{
 print "----------------------------------------------------------  \n" ;
 print "BasicQuery Staff Table \n";
 print "----------------------------------------------------------  \n" ;

 # set up and execute the query
 print "Execute the Statement:  \n";
 print "  SELECT * FROM staff WHERE salary > 14000  \n";

 my  $sql = qq(SELECT * FROM staff WHERE salary > 14000 );

 # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm
 $sth = PrepareExecuteSql( $dbh , $sql );

 # output the results of the query
 while (( $deptnumb , $location ) = $sth ->fetchrow_array)
 {
   printf "      %-8d %-14s  \n  " , $deptnumb , $location ;
 }

 return  0 ;
} # BasicQuery

#############################################################################
# Description: This subroutine demonstrates how to update rows in a
table.
# Input      : None
# Output     : Returns 0 on success, exits otherwise.

#############################################################################
sub BasicUpdate
{
 # display contents of the 'staff' table before updating
 DisplayStaffTable();

 print "----------------------------------------------------------  \n";
 print "BasicUpdate Staff Table \n";
 print "----------------------------------------------------------  \n";

 # update the data of table 'staff' by using a subquery in the SET clause
 print "Execute the Statement:  \n";
 print "  UPDATE staff  \n";
 print "    SET salary = (SELECT MIN(salary)  \n";
 print "                    FROM staff  \n";
 print "                    WHERE id >= 310)  \n";
 print "    WHERE id = 310  \n";

 my  $sql = qq(UPDATE staff
                SET salary = (SELECT MIN(salary)
                                FROM staff
                                WHERE id >= 310 )
                WHERE id = 310 );

 # execute the update statement
 $dbh -> do ( $sql );

 # display the final content of the 'staff' table
 DisplayStaffTable();

 # rollback the transaction
#  printf "Rollback the transaction.  \n\n";
#  $dbh ->rollback;

 return  0 ;
} # BasicUpdate

#############################################################################
# Description: This subroutine demonstrates how to delete rows from a
table.
# Input      : None
# Output     : Returns 0 on success, exits otherwise.

#############################################################################
sub BasicDelete
{
 # display contents of the 'staff' table
 DisplayStaffTable();

 print "----------------------------------------------------------  \n";
 print "BasicDelete Staff Table \n";
 print "----------------------------------------------------------  \n";

 # delete rows from the 'staff' table where id >= 310 and salary > 20000
 print "Execute the Statement:  \n";
 print "  DELETE FROM staff WHERE id >= 310 AND salary > 20000  \n";

 my  $sql = qq(DELETE FROM staff
                WHERE id >= 310
                AND salary > 20000 );

 # execute the delete statement
 $dbh -> do ( $sql );

 # display the final content of the 'staff' table
 DisplayStaffTable();

 # rollback the transaction
#  printf "Rollback the transaction.  \n\n";
#  $dbh ->rollback;

 return  0 ;
} # BasicDelete

#############################################################################
# Description: This subroutine displays the contents from the 'staff'
table.
# Input      : None
# Output     : Returns 0 on success, exits otherwise.

#############################################################################
sub DisplayStaffTable
{
 print "----------------------------------------------------------  \n";
 print "Display Staff Table \n";
 print "----------------------------------------------------------  \n";
 print "SELECT * FROM staff WHERE id >= 310  \n\n";
 print "ID  NAME     DEPT JOB   YEARS SALARY  \n";
 print "--- -------- ---- ----- ----- --------\n";

 my  $sql = qq(SELECT * FROM staff WHERE id >= 310 );

 # prepare the sql statement
 $sth = $dbh ->prepare( $sql );

 # execute the sql statement
 $sth ->execute;

 while (( $id , $name , $dept , $job , $years , $salary , $comm )
                                                     = $sth
->fetchrow_array)
 {
   printf "    %3d %-8.8s %4d" , $id , $name , $dept ;
   if ( $job  ne  " " )
   {
     printf " %-5.5s" , $job ;
   }
   else
   {
     print "     -" ;
   }

   if ( $years != 0 )

   {
     printf " %5d" , $years ;
   }
   else
   {
     print "     -" ;
   }

   printf " %7.2f" , $salary ;
   if ( $comm != 0 )
   {
     printf " %7.2f  \n  " , $comm ;
   }
   else
   {
     print "       -  \n  " ;
   }
 }

 return  0 ;
} # DisplayStaffTable

##########################################################################
# Description : Checks and parses the command line arguments
# Input       : An array containing the command line arguments that was
#               passed to the calling function
# Output      : Database name, user name and password

###########################################################################
sub CmdLineArgChk
{
my  $arg_c = @_; # number of arguments passed to the function
my @arg_l; # arg_l holds the values to be returned to calling function

if ( $arg_c > 3 || $arg_c == 1 && ( ( $_ [ 0 ] eq  "?"  ) ||
                                 ( $_ [ 0 ] eq  "-?"  ) ||
                                 ( $_ [ 0 ] eq  "/?"  ) ||
                                 ( $_ [ 0 ] eq  "-h"  ) ||
                                 ( $_ [ 0 ] eq  "/h"  ) ||
                                 ( $_ [ 0 ] eq  "-help"  ) ||
                                 ( $_ [ 0 ] eq  "/help"  ) ) )
{
 die "Usage: prog_name [dbAlias] [userId passwd]  \n  "  ;
}

# Use all defaults
if ( $arg_c == 0 )
{
 $arg_l [ 0 ] = $database ;
 $arg_l [ 1 ] = "" ;
 $arg_l [ 2 ] = "" ;
}

# dbAlias specified
if ( $arg_c == 1 )
{
 $arg_l [ 0 ] = "dbi:DB2:" . $_ [ 0 ];
 $arg_l [ 1 ] = "" ;
 $arg_l [ 2 ] = "" ;
}

# userId & passwd specified
if ( $arg_c == 2 )
{
 $arg_l [ 0 ] = $database ;
 $arg_l [ 1 ] = $_ [ 0 ];
 $arg_l [ 2 ] = $_ [ 1 ];
}

# dbAlias, userId & passwd specified
if ( $arg_c == 3 )
{
 $arg_l [ 0 ] = "dbi:DB2:" . $_ [ 0 ];
 $arg_l [ 1 ] = $_ [ 1 ];
 $arg_l [ 2 ] = $_ [ 2 ];
}

return @arg_l;
} # CmdLineArgChk

##########################################################################
# Description : Prepares and Exectes the SQL statement
# Input       : Datbase handler, SQL statement
# Output      : Statement Handler.

##########################################################################
sub PrepareExecuteSql
{
 # get the database handler and sql into local variables
 my ( $dbh_loc , $sql_loc ) = @_;

 # prepare the SQL statement or call TransRollback() if it fails
 my  $sth = $dbh_loc ->prepare( $sql_loc )
   || &TransRollback( $dbh_loc );

 # execute the prepared SQL statement or call TransRollback() if it fails
 $rc = $sth ->execute()
   || &TransRollback( $dbh_loc );

 return  $sth ;   # return the statement handler
} # PrepareExecuteSql

##########################################################################
# Description : Rollback the transaction and reset the database
connection
# Input       : Database handler
# Output      : None

##########################################################################
sub TransRollback
{

 my ( $dbh_loc ) = @_;

 # rollback the transaction
 print "  \n     Rolling back the transaction...  \n  " ;

 my  $rv = $dbh_loc ->rollback()
   || die "The transaction couldn't be rolled back: $DBI::errstr" ;

 print "  \n     The transaction was rolled back.  \n  " ;

 # get the number of active statement handles currently used
 my  $no_handles = $dbh_loc ->{ActiveKids};

 # close all the active statement handles
 for ( $i = 0 ; $i < $no_handles ; $i ++)
 {
    if ( $i == 0 )
    {
      # no more data to be fetched from the first statement handle
      $sth ->finish;
    }
    else
    {
      my  $handle = "  \$  sth$i" ;  # to get the subsequent statement
handles
      eval "$handle->finish" ;
    }
 }

 # reset the connection
 print "  \n     Disconnecting from the database...  \n  " ;

my  $rv = $dbh_loc ->disconnect()
   || die "Disconnecting from the database failed: $DBI::errstr" ;

 print "  \n     Disconnected from the database.  \n  " ;

 die "  \n  Exiting the sample  \n  " ;
} # TransRollback
1 ; # to always return true to the calling function