#!/usr/bin/perl ########################################################################## # (c) Copyright IBM Corp. 2007 All rights reserved. # # The following sample of source code ("Sample") is owned by International # Business Machines Corporation or one of its subsidiaries ("IBM") and is # copyrighted and licensed, not sold. You may use, copy, modify, and # distribute the Sample in any form without payment to IBM, for the purpose of # assisting you in the development of your applications. # # The Sample code is provided to you on an "AS IS" basis, without warranty of # any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR # IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF # MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do # not allow for the exclusion or limitation of implied warranties, so the above # limitations or exclusions may not apply to you. IBM shall not be liable for # any damages you suffer as a result of using, copying, modifying or # distributing the Sample, even if IBM has been advised of the possibility of # such damages. ######################################################################### # # SOURCE FILE NAME: DB2SampUtil.pm # # SAMPLE: Defines common functions like command line argument checking # Also, functions to prepare and execute an SQL statement, and # roll back if an error occurs. # ########################################################################## # # For more information on the sample programs, see the README file. # # For information on developing Perl applications, see the Application # Development Guide. # # For information on using SQL statements, see the SQL Reference. # # For the latest information on programming, building, and running DB2 # applications, visit the DB2 Information Center: # http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp ##########################################################################/ use strict; use warnings; use DBI; ########################################################################## # 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] = "dbi:DB2:sample"; $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] = "dbi:DB2:sample"; $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) = @_; # declare return code and statement handle my ($rc, $sth); # prepare the SQL statement or call TransRollback() if it fails $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 { # get the database handler into local variables my ($dbh_loc) = @_; # declare return code, statement handler and local variables my ($rc, $sth, $no_handles, $i, $handle); # rollback the transaction print "\n Rolling back the transaction...\n"; $rc = $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 $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 { $handle = "\$sth$i"; # to get the subsequent statement handles eval "$handle->finish"; } } # reset the connection print "\n Disconnecting from the database...\n"; $rc = $dbh_loc->disconnect() || die "Disconnecting from the database failed: $DBI::errstr"; print "\n Disconnected from the database.\n"; die "\nExiting the sample\n"; } # TransRollback 1; # to always return true to the calling function