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
