<?php
/***************************************************************************
* (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: TblSelect_DB2.php
*
* SAMPLE: How to select from each of: insert, update, delete.
*
* SQL Statements USED:
* INCLUDE
* CREATE TABLE
* INSERT
* SELECT FROM INSERT
* SELECT FROM UPDATE
* SELECT FROM DELETE
* PREPARE
* DROP TABLE
*
****************************************************************************
*
* For more information on the sample programs, see the README file.
*
***************************************************************************/
require_once "UtilIOHelper.php";
require_once "UtilConnection_DB2.php";
class Select extends DB2_Connection
{
public $SAMPLE_HEADER =
"
echo '
THIS EXAMPLE SHOWS HOW TO SELECT FROM EACH OF: INSERT, UPDATE, DELETE.
';
";
function __construct($initialize = true)
{
parent::__construct($initialize);
$this->make_Connection();
}
public function create_Tables()
{
$toPrintToScreen = "
Creating Tables for this sample:
";
$this->format_Output($toPrintToScreen);
$toPrintToScreen = "
The context for this sample is that of a Company B taking over a Company
A. This sample illustrates how company B incorporates data from table
company_b into table company_a.
";
$this->format_Output($toPrintToScreen);
$query = "
CREATE TABLE {$this->schema}company_a
(ID SMALLINT NOT NULL UNIQUE,
NAME VARCHAR(9),
DEPARTMENT SMALLINT,
JOB CHAR(5),
YEARS SMALLINT,
SALARY DECIMAL(7,2))
";
$this->format_Output($query);
//Removing Excess white space.
$query = preg_replace('/\s+/', " ", $query);
// Execute the query
if(db2_exec($this->dbconn, $query) === false)
{
$this->format_Output(db2_stmt_errormsg() . "\n");
}
else
{
$this->format_Output("Succeeded \n");
}
$toPrintToScreen = "
Company B is buying out Company A. This table has a few additional
columns and differences from the previous table. Specifically, the ID
column is generated.
";
$this->format_Output($toPrintToScreen);
$query = "
CREATE TABLE {$this->schema}company_b
(ID SMALLINT GENERATED BY DEFAULT AS IDENTITY (START WITH 2000,
INCREMENT BY 1) NOT NULL,
NAME VARCHAR(9),
DEPARTMENT SMALLINT,
JOB CHAR(5),
YEARS SMALLINT,
SALARY DECIMAL(7,2),
BENEFITS VARCHAR(50),
OLD_ID SMALLINT)
";
$this->format_Output($query);
//Removing Excess white space.
$query = preg_replace('/\s+/', " ", $query);
// Execute the query
if(db2_exec($this->dbconn, $query) === false)
{
$this->format_Output(db2_stmt_errormsg() . "\n");
}
else
{
$this->format_Output("Succeeded \n");
}
$toPrintToScreen = "
This table can be used by the management of Company B to see how much
of a raise they gave to employees from Company A for joining Company B
(in a dollar amount, as opposed to a 5% increase).
";
$this->format_Output($toPrintToScreen);
$query = "
CREATE TABLE {$this->schema}salary_change
(ID SMALLINT NOT NULL UNIQUE,
OLD_SALARY DECIMAL(7,2),
SALARY DECIMAL(7,2))
";
$this->format_Output($query);
//Removing Excess white space.
$query = preg_replace('/\s+/', " ", $query);
$this->format_Output("-- Executing Query: ");
// Execute the query
if(db2_exec($this->dbconn, $query) === false)
{
$this->format_Output(db2_stmt_errormsg() . "\n");
}
else
{
$this->format_Output("Succeeded \n");
}
$toPrintToScreen = "
Finished Creating Tables.
Now Populating the tables:
";
$this->format_Output($toPrintToScreen);
$toPrintToScreen = "
Populating table company_a with data:
";
$this->format_Output($toPrintToScreen);
$EmployeesOfCompanyA = array(
array (5275, "Sanders", 20, "Mgr" , 15, 52357.50),
array (5265, "Pernal", 20, "Sales", NULL, 36171.25),
array (5791, "O'Brien", 38, "Sales", 9, 32006.00),
);
$query = "
INSERT INTO {$this->schema}company_a VALUES(?, ?, ?, ?, ?, ?)
";
$this->format_Output($query);
//Removing Excess white space.
$query = preg_replace('/\s+/', " ", $query);
$InsertEmployeesInToCompanyA_Stmt = db2_prepare($this->dbconn, $query);
$this->format_Output(
"
| Num. | Name | Department | Job | Years | Salary | Insert Successful
-------------------------------------------------------------------------------
");
foreach($EmployeesOfCompanyA as $Employee)
{
$this->format_Output(sprintf("| %4s | %8s | %10s | %5s | %5s | %9s | ",
$Employee[0], $Employee[1], $Employee[2], $Employee[3], $Employee[4], $Employee[5]));
if(db2_execute($InsertEmployeesInToCompanyA_Stmt, $Employee))
{
$this->format_Output("True\n");
}
else
{
$this->format_Output("False \n");
}
}
db2_free_stmt($InsertEmployeesInToCompanyA_Stmt);
$toPrintToScreen = "
Populating table {$this->schema}company_b with data:
";
$this->format_Output($toPrintToScreen);
$EmployeesOfCompanyB = array(
array( 'Naughton', 38, 'Clerk', NULL, 24954.75, 'No Benefits'),
array('Yamaguchi', 42, 'Clerk', 5, 20505.00, 'Basic Health Coverage'),
array( 'Fraye', 51, 'Mgr', 8, 63150.00, 'Basic Health Coverage'),
array( 'Williams', 51, 'Sales', 10, 42456.50, 'Advanced Health Coverage'),
array( 'Molinare', 10, 'Mgr', 15, 74959.20, 'Advanced Health Coverage and Pension Plan')
);
$query = "
INSERT INTO {$this->schema}company_b(NAME, DEPARTMENT, JOB, YEARS, SALARY, BENEFITS)
VALUES (?, ?, ?, ?, ?, ?)
";
$this->format_Output($query);
//Removing Excess white space.
$query = preg_replace('/\s+/', " ", $query);
$InsertEmployeesInToCompanyB_Stmt = db2_prepare($this->dbconn, $query);
$this->format_Output(
"
| Name | Dept. | Job | Years | Salary |
| -> | Benefits | Insert Successful
-------------------------------------------------------------------------------
");
foreach($EmployeesOfCompanyB as $Employee)
{
$this->format_Output(sprintf("| %9s | %7s | %7s | %9s | %13s |\n| -> | %45.45s | ",
$Employee[0], $Employee[1], $Employee[2], $Employee[3], $Employee[4], $Employee[5]));
if(db2_execute($InsertEmployeesInToCompanyB_Stmt, $Employee))
{
$this->format_Output("True\n");
}
else
{
$this->format_Output("False \n");
}
}
db2_free_stmt($InsertEmployeesInToCompanyB_Stmt);
// Commit
$this->commit();
return true;
} // Create
/***************************************************************************
* The buy_Company function encapsulates the table updates after Company
* B takes over Company A. Each employees from table company_a is
* allocated a benefits package. The employee data is moved into table
* company_b. Each employee's salary is increased by 5%. The old and
* new salaries are recorded in a table salary_change.
**************************************************************************/
public function buy_Company()
{
$toPrintToScreen = "
------------------------------------------------------------------------------
Company B takes over Company A. Each employees from table company_a is
allocated a benefits package. The employee data is moved into table
company_b. Each employee's salary is increased by 5%. The old and
new salaries are recorded in a table salary_change.
";
$this->format_Output($toPrintToScreen);
/*****************************************************************************/
$toPrintToScreen = "
The following SELECT statement references a DELETE statement in its
FROM clause. It deletes all rows from company_a, selecting all
deleted rows into the ResultSet rs.
";
$this->format_Output($toPrintToScreen);
$query = "
SELECT ID, NAME, DEPARTMENT, JOB, YEARS, SALARY
FROM OLD TABLE (DELETE FROM {$this->schema}company_a)
";
$this->format_Output($query);
//Removing Excess white space.
$query = preg_replace('/\s+/', " ", $query);
$EmployeesFromCompanyA_result = db2_exec($this->dbconn, $query);
/*****************************************************************************/
$toPrintToScreen = "
The following SELECT statement references an INSERT statement in
its FROM clause. It inserts an employee record from host
variables into table company_b. The current employee ID from the
ResultSet is selected into the host variable new_id. The
keywords FROM FINAL TABLE determine that the value in new_id is
the value of ID after the INSERT statement is complete.
Note that the ID column in table company_b is generated and
without the SELECT statement an additional query would have to be
made in order to retreive the employee's ID number.
";
$this->format_Output($toPrintToScreen);
$query = "
SELECT ID
FROM FINAL TABLE (
INSERT INTO {$this->schema}company_b( OLD_ID,
NAME,
DEPARTMENT,
JOB,
YEARS,
SALARY,
BENEFITS
) VALUES(?, ?, ?, ?, ?, ?, ?)
)
";
$this->format_Output($query);
//Removing Excess white space.
$query = preg_replace('/\s+/', " ", $query);
$InsertNewEmployee_stmt = db2_prepare($this->dbconn, $query);
/*****************************************************************************/
$toPrintToScreen = "
The following SELECT statement references an UPDATE statement in
its FROM clause. It updates an employee's salary by giving them
a 5% raise. The employee's id, old salary and current salary are
all read into host varibles via a ResultSet for later use in this
function.
The INCLUDE statement works by creating a temporary column to
keep track of the old salary. This temporary column is only
available for this statement and is gone once the statement
completes. The only way to keep this data after the statement
completes is to read it into a host variable.
";
$this->format_Output($toPrintToScreen);
$query = "
SELECT ID, OLD_SALARY, SALARY
FROM FINAL TABLE (UPDATE {$this->schema}company_b INCLUDE (OLD_SALARY DECIMAL(7,2))
SET OLD_SALARY = SALARY, SALARY = SALARY * 1.05
WHERE ID = ?)
";
$this->format_Output($query);
//Removing Excess white space.
$query = preg_replace('/\s+/', " ", $query);
$GiveNewEmployeeARaise_stmt = db2_prepare($this->dbconn, $query);
/*****************************************************************************/
$toPrintToScreen = "
This INSERT statement inserts an employee's id, old salary and
current salary into the salary_change table.
";
$this->format_Output($toPrintToScreen);
$query = "
INSERT INTO {$this->schema}salary_change VALUES(?, ?, ?)
";
$this->format_Output($query);
//Removing Excess white space.
$query = preg_replace('/\s+/', " ", $query);
$RecordSalaryChange_stmt = db2_prepare($this->dbconn, $query);
/*****************************************************************************/
$toPrintToScreen = "
_______________________________________________________________________________
Now Processing Employees from Company A,
First we will move the Employees to Company B
and assign benefits as required
Then giving them a 5% raise
And last record the raise.
_______________________________________________________________________________
";
$this->format_Output($toPrintToScreen);
if($EmployeesFromCompanyA_result !== false)
{
// retrieve and display the result from the xquery
while($Employee = db2_fetch_assoc($EmployeesFromCompanyA_result))
{
$this->format_Output("\n");
/* The following if statement sets the new employee's benefits based
on their years of experience.
*/
$benefits = "No Benefits";
if($Employee['YEARS'] > 14)
$benefits = "Advanced Health Coverage and Pension Plan";
else if($Employee['YEARS'] > 9)
$benefits = "Advanced Health Coverage";
else if($Employee['YEARS'] > 4)
$benefits = "Basic Health Coverage";
$Employee['BENEFITS'] = $benefits;
//Adding a new Employee to company B from company A
if(db2_execute($InsertNewEmployee_stmt, $Employee) !== false)
{
$this->format_Output("Employee: " . $Employee['NAME'] . " added to Company b\n");
//Giving the Employee a raise
if(db2_execute($GiveNewEmployeeARaise_stmt, db2_fetch_array($InsertNewEmployee_stmt)) !== false)
{
$this->format_Output("Employee: " . $Employee['NAME'] . " was given a 5% raise\n");
//Recording the raise
if(db2_execute($RecordSalaryChange_stmt, db2_fetch_array($GiveNewEmployeeARaise_stmt)) !== false)
{
$this->format_Output("Employee: " . $Employee['NAME'] . " raise was recorded\n");
}
else
{
$this->format_Output("Employee: " . $Employee['NAME'] . " raise WAS NOT recorded\n");
$this->format_Output(db2_stmt_errormsg());
$this->rollback;
return false();
}
}
else
{
$this->format_Output("Employee: " . $Employee['NAME'] . " WAS NOT given a 5% raise\n");
$this->format_Output(db2_stmt_errormsg());
$this->rollback();
return false;
}
}
else
{
$this->format_Output("Employee: " . $Employee['NAME'] . " WAS NOT added to Company b\n");
$this->format_Output(db2_stmt_errormsg());
$this->rollback();
return false;
}
}
db2_free_result($EmployeesFromCompanyA_result);
db2_free_stmt($InsertNewEmployee_stmt);
db2_free_stmt($GiveNewEmployeeARaise_stmt);
db2_free_stmt($RecordSalaryChange_stmt);
}
else
{
$this->format_Output(db2_stmt_errormsg());
$this->rollback;
return false;
}
$toPrintToScreen = "
_______________________________________________________________________________
DONE!
_______________________________________________________________________________
";
$this->format_Output($toPrintToScreen);
$toPrintToScreen = "
The following DELETE statement references a SELECT statement in its
FROM clause. It lays off the highest paid manager. This DELETE
statement removes the manager from the table company_b.
";
$this->format_Output($toPrintToScreen);
$query = "
DELETE FROM (SELECT * FROM {$this->schema}company_b
WHERE JOB = 'Mgr'
ORDER BY SALARY DESC FETCH
FIRST ROW ONLY)
";
$this->format_Output($query);
//Removing Excess white space.
$query = preg_replace('/\s+/', " ", $query);
$this->format_Output("-- Executing Query: ");
// Execute the query
if(db2_exec($this->dbconn, $query) === false)
{
$this->format_Output(db2_stmt_errormsg() . "\n");
$this->rollback();
return false;
}
else
{
$this->format_Output("Succeeded \n");
}
$toPrintToScreen = "
The following UPDATE statement references a SELECT statement in its
FROM clause. It gives the most senior employee a $10000 bonus.
This UPDATE statement raises the employee's salary in the table
company_b.
";
$this->format_Output($toPrintToScreen);
$query = "
UPDATE (SELECT MAX(YEARS) OVER() AS max_years, YEARS, SALARY
FROM {$this->schema}company_b)
SET SALARY = SALARY + 10000
WHERE max_years = YEARS
";
$this->format_Output($query);
//Removing Excess white space.
$query = preg_replace('/\s+/', " ", $query);
$this->format_Output("-- Executing Query: ");
// Execute the query
if(db2_exec($this->dbconn, $query) === false)
{
$this->format_Output(db2_stmt_errormsg() . "\n");
$this->rollback();
return false;
}
else
{
$this->format_Output("Succeeded \n");
}
// Commit
$this->commit();
return true;
} // buy_Company
/******************************************************************
* The Print function outputs the data in the tables: company_a,
* company_b and salary_change. For each table, a while loop and
* ResultSet are used to fetch and display row data.
******************************************************************/
public function print_Table_Contents()
{
$toPrintToScreen = "
The Printing the data in the tables: company_a, company_b and salary_change
------------------------------------------------------------------------------
";
$this->format_Output($toPrintToScreen);
$query = "
SELECT ID, NAME, DEPARTMENT, JOB, YEARS, SALARY FROM {$this->schema}company_a
";
$this->format_Output($query);
//Removing Excess white space.
$query = preg_replace('/\s+/', " ", $query);
// Execute the query
$DataFromTableCompanyA = db2_exec($this->dbconn, $query);
if($DataFromTableCompanyA)
{
$toPrintToScreen = "
ID | Name | Dept | Job | Years | Salary
----------------------------------------------------------
";
$this->format_Output($toPrintToScreen);
// retrieve and display the result from the xquery
while($Employee = db2_fetch_assoc($DataFromTableCompanyA))
{
$this->format_Output(sprintf("| %6s | %10s | %5s | %5s | %5s | %10s\n",
$Employee['ID'],
$Employee['NAME'],
$Employee['DEPARTMENT'],
$Employee['JOB'],
$Employee['YEARS'],
$Employee['SALARY']
)
);
}
db2_free_result($DataFromTableCompanyA);
}
else
{
$this->format_Output(db2_stmt_errormsg());
}
$query = "
SELECT ID, NAME, DEPARTMENT, JOB, YEARS, SALARY, BENEFITS, OLD_ID
FROM {$this->schema}company_b
";
$this->format_Output($query);
//Removing Excess white space.
$query = preg_replace('/\s+/', " ", $query);
// Execute the query
$DataFromTableCompanyB = db2_exec($this->dbconn, $query);
if($DataFromTableCompanyB)
{
$toPrintToScreen = "
|ID | Name |Dept. | Job | Years | Salary
| Benefits
| OLD_ID
-----------------------------------------------------------------------------";
$this->format_Output($toPrintToScreen);
// retrieve and display the result from the xquery
while($Employee = db2_fetch_assoc($DataFromTableCompanyB))
{
$this->format_Output(sprintf("
| %4s | %10s | %5s | %5s | %5s | %10s
| %s
| %s",
$Employee['ID'],
$Employee['NAME'],
$Employee['DEPARTMENT'],
$Employee['JOB'],
$Employee['YEARS'],
$Employee['SALARY'],
$Employee['BENEFITS'],
$Employee['OLD_ID']
)
);
}
db2_free_result($DataFromTableCompanyB);
}
else
{
$this->format_Output(db2_stmt_errormsg());
}
$query = "
SELECT ID, OLD_SALARY, SALARY FROM {$this->schema}salary_change
";
$this->format_Output($query);
//Removing Excess white space.
$query = preg_replace('/\s+/', " ", $query);
// Execute the query
$DataFromTableSalaryChange = db2_exec($this->dbconn, $query);
if($DataFromTableSalaryChange)
{
$toPrintToScreen = "
| ID | Old Salary | SALARY
--------------------------------------
";
$this->format_Output($toPrintToScreen);
// retrieve and display the result from the xquery
while($SalaryEntry = db2_fetch_assoc($DataFromTableSalaryChange))
{
$this->format_Output(sprintf("| %5s | %11s | %s\n",
$SalaryEntry['ID'],
$SalaryEntry['OLD_SALARY'],
$SalaryEntry['SALARY']
)
);
}
db2_free_result($DataFromTableSalaryChange);
}
else
{
$this->format_Output(db2_stmt_errormsg());
}
} // Print
/* The Drop function drops the tables used by this sample. */
public function drop_Tables()
{
$toPrintToScreen = "
Dropping Created Tables used for this sample:
";
$this->format_Output($toPrintToScreen);
$query = "
DROP TABLE {$this->schema}company_a
";
$this->format_Output($query);
//Removing Excess white space.
$query = preg_replace('/\s+/', " ", $query);
// Execute the query
if(db2_exec($this->dbconn, $query) === false)
{
$this->format_Output(db2_stmt_errormsg() . "\n");
$this->rollback();
return false;
}
else
{
$this->format_Output("Succeeded \n");
}
$query = "
DROP TABLE {$this->schema}company_b
";
$this->format_Output($query);
//Removing Excess white space.
$query = preg_replace('/\s+/', " ", $query);
// Execute the query
if(db2_exec($this->dbconn, $query) === false)
{
$this->format_Output(db2_stmt_errormsg() . "\n");
$this->rollback();
return false;
}
else
{
$this->format_Output("Succeeded \n");
}
$query = "
DROP TABLE {$this->schema}salary_change
";
$this->format_Output($query);
//Removing Excess white space.
$query = preg_replace('/\s+/', " ", $query);
// Execute the query
if(db2_exec($this->dbconn, $query) === false)
{
$this->format_Output(db2_stmt_errormsg() . "\n");
$this->rollback();
return false;
}
else
{
$this->format_Output("Succeeded \n");
}
// Commit
$this->commit();
return true;
} // drop_Tables
} // TbSel
$Run_Sample = new Select();
$Run_Sample->create_Tables();
$Run_Sample->print_Table_Contents();
$Run_Sample->buy_Company();
$Run_Sample->print_Table_Contents();
$Run_Sample->drop_Tables();
/*******************************************************
* We rollback at the end of all samples to ensure that
* there are not locks on any tables. The sample as is
* delivered does not need this. The author of these
* samples expects that you the read of this comment
* will play and learn from them and the reader may
* forget commit or rollback their action as the
* author has in the past.
* As such this is here:
******************************************************/
$Run_Sample->rollback();
// Close the database connection
$Run_Sample->close_Connection();
?>