#!/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