#!/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: dbuse.pl # # SAMPLE: How to use a database # # SQL STATEMENTS USED: # CREATE TABLE # DROP TABLE # DELETE # # ########################################################################## # # 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 ##########################################################################/ select STDERR; $|=1; select STDOUT; $|=1; use strict; use warnings; use DBI; # access the module for DB2 Sample Utility functions use DB2SampUtil; # check and parse the command line arguments # call the subroutine CmdLineArgChk from DB2SampUtil.pm my ($database, $user, $password) = CmdLineArgChk(@ARGV); # declare return code, statement handler, database handler and local variable my ($rc, $sth, $dbh, $i); print "THIS SAMPLE SHOWS HOW TO USE A DATABASE.\n"; # connect to the database print "\n Connecting to database...\n"; $dbh = DBI->connect($database, $user, $password, {AutoCommit => 0}) || die "Can't connect to $database: $DBI::errstr"; print "\n Connected to database.\n"; # call the subroutine StaticStmtInvoke $rc = StaticStmtInvoke(); if ($rc != 0) { print "\nStatic statement execution failed\n"; } # call the subroutine StaticStmtWithHostVarsInvoke $rc = StaticStmtWithHostVarsInvoke(); if ($rc != 0) { print "\nExecuting sql with host variables failed\n"; } # call the subroutine StmtEXECUTE $rc = StmtEXECUTE(); if ($rc != 0) { print "\nExecuting sql with 'do' interface failed\n"; } # disconnect from the database print "\n Disconnecting from database.\n"; $dbh->disconnect || die $DBI::errstr; print " Disconnected from database.\n"; ####################################################################### # Description : How to use static SQL statements # Input : None # Output : Returns 0 on success, exits otherwise ####################################################################### sub StaticStmtInvoke { print "\n-----------------------------------------------------------"; print "\nUSE THE SQL STATEMENTS:\n"; print " CREATE TABLE\n"; print " DROP TABLE\n"; print "TO SHOW HOW TO USE STATIC SQL STATEMENTS.\n"; # create a table print "\n Execute the statement\n"; print " CREATE TABLE table1(col1 INTEGER)\n"; my $sql = qq(CREATE TABLE table1(col1 INTEGER)); # prepare and execute the SQL statement. # call the subroutine PrepareExecuteSql() from DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction or call TransRollback() from DB2SampUtil.pm # if it fails $dbh->commit() || TransRollback($dbh); # drop the table print "\n Execute the statement\n"; print " DROP TABLE table1\n"; $sql = qq(DROP TABLE table1); # prepare and execute the SQL statement $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction or call TransRollback() from DB2SampUtil.pm # if it fails $dbh->commit() || TransRollback($dbh); # no more data to be fetched from statement handle $sth->finish; return 0; } # StaticStmtInvoke ####################################################################### # Description : How to use host variables to execute SQL statements # Input : None # Output : Returns 0 on success, exits otherwise ####################################################################### sub StaticStmtWithHostVarsInvoke { # declare the variables being used my $hostVar1; my $hostVar2; print "\n-----------------------------------------------------------"; print "\nUSE THE SQL STATEMENTS:\n"; print " DELETE\n"; print "TO SHOW HOW TO USE HOST VARIABLES.\n"; # execute a statement with host variables print "\n Execute\n"; print " DELETE FROM org\n"; print " WHERE deptnumb = \$hostVar1 AND\n"; print " division = \$hostVar2\n"; print " for\n"; print " hostVar1 = 15\n"; print " hostVar2 = 'Eastern'\n"; $hostVar1 = 15; $hostVar2 = 'Eastern'; my $sql = qq(DELETE FROM org WHERE deptnumb = ? AND division = ? ); # prepare the sql statement my $sth = $dbh->prepare($sql); # execute the sql statement by passing the hostvariables $sth->execute($hostVar1, $hostVar2); # rollback the transaction print "\n Rollback the transaction.\n"; $dbh->rollback(); return 0; } # StaticStmtWithHostVarsInvoke ####################################################################### # Description : How to execute SQL statements with 'do' interface # Input : None # Output : Returns 0 on success, exits otherwise ####################################################################### sub StmtEXECUTE { my $hostVarStmt; print "\n-----------------------------------------------------------"; print "\nUSE THE SQL STATEMENTS:\n"; print " PREPARE\n"; print " EXECUTE\n"; print "TO SHOW HOW TO USE SQL STATEMENTS WITH 'EXECUTE'.\n"; # sql statement to be executed $hostVarStmt = qq(DELETE FROM org WHERE deptnumb = 15); printf "\n Execute the statement\n"; printf " DELETE FROM org WHERE deptnumb = 15\n"; # execute the sql statement $dbh->do($hostVarStmt); # rollback the transaction print "\n Rollback the transaction.\n"; $dbh->rollback(); return 0; } # StmtEXECUTE