#!/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: tbuse.pl # # SAMPLE: How to manipulate table data # # SQL STATEMENTS USED: # SELECT # INSERT # UPDATE # DELETE # # ############################################################################# # # For more information on the sample programs, see the README file. # # For information on developing applications, see the Application # Development Guide. # # For information on using SQL statements, see the SQL Reference. # # For the latest information on programming, compiling, and running DB2 # applications, visit the DB2 Information Center at # 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 my ($rc, $sth, $dbh); print "\n THIS SAMPLE SHOWS HOW TO MANIPULATE TABLE DATA\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 " Connected to database.\n"; # perform a query with the 'org' table BasicQuery(); # insert rows into the 'staff' table BasicInsert(); # 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; # disconnect from the database print "\n Disconnecting from database..."; $dbh->disconnect || die "Can't disconnect from database: $DBI::errstr"; print "\n Disconnected from database.\n"; ############################################################################# # Description: This subroutine demonstrates how to perform a standard query. # Input : None # Output : Returns 0 on success, exits otherwise. ############################################################################# sub BasicQuery { my ($deptnumb, $location); print " ----------------------------------------------------------\n"; print " USE THE SQL STATEMENT:\n"; print " SELECT\n"; print " TO QUERY DATA FROM A TABLE.\n"; # set up and execute the query print "\n Execute Statement:\n"; print " SELECT deptnumb, location FROM org WHERE deptnumb < 25\n"; my $sql = qq(SELECT deptnumb, location FROM org WHERE deptnumb < 25); # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); print "\n Results:\n"; print " DEPTNUMB LOCATION\n"; print " -------- --------------\n"; # 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 insert rows into a table. # Input : None # Output : Returns 0 on success, exits otherwise. ############################################################################# sub BasicInsert { print " ----------------------------------------------------------\n"; print " USE THE SQL STATEMENT:\n"; print " INSERT\n"; print " TO INSERT DATA INTO A TABLE USING VALUES.\n"; # display contents of the 'staff' table before inserting rows DisplayStaffTable(); # use the INSERT statement to insert data into the 'staff' table. print "\n Invoke the statement:\n"; print " INSERT INTO staff(id, name, dept, job, salary)\n"; print " VALUES(380, 'Pearce', 38, 'Clerk', 13217.50),\n"; print " (390, 'Hachey', 38, 'Mgr', 21270.00),\n"; print " (400, 'Wagland', 38, 'Clerk', 14575.00)\n"; my $sql = qq(INSERT INTO staff(id, name, dept, job, salary) VALUES (380, 'Pearce', 38, 'Clerk', 13217.50), (390, 'Hachey', 38, 'Mgr', 21270.00), (400, 'Wagland', 38, 'Clerk', 14575.00)); # execute the insert statement $dbh->do($sql); # display the content in the 'staff' table after the INSERT. DisplayStaffTable(); # rollback the transaction printf "\n Rollback the transaction.\n\n"; $dbh->rollback; return 0; } # BasicInsert ############################################################################# # Description: This subroutine demonstrates how to update rows in a table. # Input : None # Output : Returns 0 on success, exits otherwise. ############################################################################# sub BasicUpdate { print " ----------------------------------------------------------\n"; print " USE THE SQL STATEMENT:\n"; print " UPDATE\n"; print " TO UPDATE TABLE DATA USING A SUBQUERY IN THE 'SET' CLAUSE.\n"; # display contents of the 'staff' table before updating DisplayStaffTable(); # update the data of table 'staff' by using a subquery in the SET clause print "\n Invoke 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 "\n 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 { print " ----------------------------------------------------------\n"; print " USE THE SQL STATEMENT:\n"; print " DELETE\n"; print " TO DELETE TABLE DATA.\n"; # display contents of the 'staff' table DisplayStaffTable(); # delete rows from the 'staff' table where id >= 310 and salary > 20000 AND job != 'Sales' print "\n Invoke the statement:\n"; print " DELETE FROM staff WHERE id >= 310 AND salary > 20000 AND job != 'Sales'\n"; my $sql = qq(DELETE FROM staff WHERE id >= 310 AND salary > 20000 AND job != 'Sales'); # execute the delete statement $dbh->do($sql); # display the final content of the 'staff' table DisplayStaffTable(); # rollback the transaction printf "\n 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 { my ($id, $name, $dept, $job, $years, $salary, $comm); print " SELECT * FROM staff WHERE id >= 310\n\n"; print " ID NAME DEPT JOB YEARS SALARY COMM\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(defined $job) { printf " %-5.5s", $job; } else { print " -"; } if(defined $years) { printf " %5d", $years; } else { print " -"; } printf " %7.2f", $salary; if(defined $comm) { printf " %7.2f\n", $comm; } else { print " -\n"; } } return 0; } # DisplayStaffTable