'****************************************************************************
' (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: TbSel.vb
'
' 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
'
' DB2 .NET Data Provider Classes USED:
' DB2Connection
' DB2Command
' DB2Transaction
'
'
'****************************************************************************
'
' Building and Running the sample program
'
' 1. Compile the TbSel.vb file with bldapp.bat by entering the following
' at the command prompt:
'
' bldapp TbSel
'
' or compile TbSel.vb with the makefile by entering the following at
' the command prompt:
'
' nmake TbSel
'
' 2. Run the TbSel program by entering the program name at the command
' prompt:
'
' TbSel
'
'****************************************************************************
'
' 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
'
'****************************************************************************
Imports System
Imports System.Data
Imports Microsoft.VisualBasic
Imports IBM.Data.DB2
Public Class TbSel
Public Shared Sub Main(args() As String)
Dim conn As DB2Connection
Dim trans As DB2Transaction
Try
Console.WriteLine()
Console.WriteLine( _
"THIS EXAMPLE SHOWS HOW TO SELECT FROM EACH OF: " & _
"INSERT, UPDATE, DELETE.")
' Connect to database.
conn = ConnectDb(args)
trans = conn.BeginTransaction()
Create(conn,trans)
trans = conn.BeginTransaction()
Print(conn,trans)
trans = conn.BeginTransaction()
Buy_Company(conn, trans)
trans = conn.BeginTransaction()
Print(conn,trans)
trans = conn.BeginTransaction()
Drop(conn,trans)
' Disconnect from database.
conn.Close()
Catch e As Exception
Console.WriteLine(e.Message)
If Not (conn is System.DBNull.value) Then
conn.Close()
End If
End Try
End Sub ' Main
' The ConnectDB function establishes a database connection.
Public Shared Function ConnectDb(argv() As String) As DB2Connection
Dim server As String
Dim dbalias As String
Dim userId As String
Dim password As String
Dim portNumber As Int32 = -1
Dim connectString As String
If (argv.Length > 5) Then
Throw new Exception( _
"Usage: prog_name [dbAlias] [userId passwd]" & vbNewLine & _
" prog_name [dbAlias] server portNum userId passwd")
Else
If (argv.Length = 1) Then
If( String.Compare(argv(0),"?") = 0 Or _
String.Compare(argv(0),"-?") = 0 Or _
String.Compare(argv(0),"/?") = 0 Or _
String.Compare(argv(0),"-h",true) = 0 Or _
String.Compare(argv(0),"/h",true) = 0 Or _
String.Compare(argv(0),"-help",true) = 0 Or _
String.Compare(argv(0),"/help",true) = 0 ) Then
Throw new Exception( _
"Usage: prog_name [dbAlias] [userId passwd]" & vbNewLine & _
" prog_name [dbAlias] server portNum userId passwd")
End If
End If
End If
Select Case (argv.Length)
Case 0 ' Use all defaults
dbalias = "sample"
userId = ""
password = ""
Case 1 ' dbAlias specified
dbalias = argv(0)
userId = ""
password = ""
Case 2 ' userId & passwd specified
dbalias = "sample"
userId = argv(0)
password = argv(1)
Case 3 ' dbAlias, userId & passwd specified
dbalias = argv(0)
userId = argv(1)
password = argv(2)
Case 4 ' use default dbAlias
dbalias = "sample"
server = argv(0)
portNumber = Convert.ToInt32(argv(1))
userId = argv(2)
password = argv(3)
Case 5 ' everything specified
dbalias = argv(0)
server = argv(1)
portNumber = Convert.ToInt32(argv(2))
userId = argv(3)
password = argv(4)
End Select
If(portNumber = -1) Then
connectString = "Database=" & dbalias
Else
connectString = "Server=" & server & ":" & portNumber & _
";Database=" & dbalias
End If
If (userId <> "")
connectString += ";UID=" & userId & ";PWD=" & password
End If
Dim conn As DB2Connection = new DB2Connection(connectString)
conn.Open()
Return conn
End Function ' ConnectDb
' The Create function creates and populates tables used by this sample.
Public Shared Sub Create(conn As DB2Connection, trans As DB2Transaction)
Try
' 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.
Console.WriteLine(vbNewLine & _
"CREATE TABLE company_a " & _
vbNewLine & _
" (ID SMALLINT NOT NULL UNIQUE, " & _
vbNewLine & _
" NAME VARCHAR(9), " & _
vbNewLine & _
" DEPARTMENT SMALLINT, " & _
vbNewLine & _
" JOB CHAR(5), " & _
vbNewLine & _
" YEARS SMALLINT, " & _
vbNewLine & _
" SALARY DECIMAL(7,2))")
Console.WriteLine()
' Company A is being bought out.
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = _
"CREATE TABLE company_a " & _
" (ID SMALLINT NOT NULL UNIQUE, " & _
" NAME VARCHAR(9), " & _
" DEPARTMENT SMALLINT, " & _
" JOB CHAR(5), " & _
" YEARS SMALLINT," & _
" SALARY DECIMAL(7,2))"
cmd.ExecuteNonQuery()
Console.WriteLine("CREATE TABLE company_b " & _
vbNewLine & _
" (ID SMALLINT GENERATED BY DEFAULT AS IDENTITY (START WITH 2000, " & _
"INCREMENT BY 1) NOT NULL, " & _
vbNewLine & _
" NAME VARCHAR(9), " & _
vbNewLine & _
" DEPARTMENT SMALLINT, " & _
vbNewLine & _
" JOB CHAR(5), " & _
vbNewLine & _
" YEARS SMALLINT, " & _
vbNewLine & _
" SALARY DECIMAL(7,2), " & _
vbNewLine & _
" BENEFITS VARCHAR(50), " & _
vbNewLine & _
" OLD_ID SMALLINT)")
Console.WriteLine()
' 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.
cmd.CommandText = _
"CREATE TABLE 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)"
cmd.ExecuteNonQuery()
Console.WriteLine("CREATE TABLE salary_change " & _
vbNewLine & _
" (ID SMALLINT NOT NULL UNIQUE, " & _
vbNewLine & _
" OLD_SALARY DECIMAL(7,2), " & _
vbNewLine & _
" SALARY DECIMAL(7,2))")
Console.WriteLine()
' 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).
cmd.CommandText = _
"CREATE TABLE salary_change " & _
" (ID SMALLINT NOT NULL UNIQUE, " & _
" OLD_SALARY DECIMAL(7,2), " & _
" SALARY DECIMAL(7,2)) "
cmd.ExecuteNonQuery()
Console.WriteLine("INSERT INTO company_a VALUES(5275, 'Sanders', " & _
"20, 'Mgr', 15, 18357.50), " & _
vbNewLine & _
" (5265, 'Pernal', 20, 'Sales', 1, 18171.25), " & _
vbNewLine & _
" (5791, 'O''Brien', 38, 'Sales', 10, 18006.00)")
Console.WriteLine()
' Populate table company_a with data.
cmd.CommandText = _
"INSERT INTO company_a VALUES(5275, 'Sanders', 20, 'Mgr', 15, " & _
"18357.50), " & _
" (5265, 'Pernal', 20, 'Sales', NULL, 18171.25), " & _
" (5791, 'O''Brien', 38, 'Sales', 9, 18006.00)"
cmd.ExecuteNonQuery()
Console.WriteLine("INSERT INTO company_b VALUES(default, " & _
"'Naughton', 38, 'Clerk', 0, 12954.75, 'No Benefits', 0), " & _
vbNewLine & _
" (default, 'Yamaguchi', 42, 'Clerk', 6, 10505.90, " & _
"'Basic Health Coverage', 0), " & _
vbNewLine & _
" (default, 'Fraye', 51, 'Mgr', 6, 21150.00, " & _
"'Basic Health Coverage', 0), " & _
vbNewLine & _
" (default, 'Williams', 51, 'Sales', 6, 19456.50, " & _
"'Basic Health Coverage', 0), " & _
vbNewLine & _
" (default, 'Molinare', 10, 'Mgr', 7, 22959.20, " & _
"'Basic Health Coverage', 0)")
' Populate table company_b with data.
cmd.CommandText = _
"INSERT INTO company_b VALUES(default, 'Naughton', 38, " & _
"'Clerk', NULL, 12954.75, 'No Benefits', NULL), " & _
" (default, 'Yamaguchi', 42, 'Clerk', 5, 10505.90, " & _
"'Basic Health Coverage', NULL), " & _
" (default, 'Fraye', 51, 'Mgr', 8, 21150.00, " & _
"'Basic Health Coverage', NULL), " & _
" (default, 'Williams', 51, 'Sales', 10, 19456.50, " & _
"'Advanced Health Coverage', NULL), " & _
" (default, 'Molinare', 10, 'Mgr', 15, 22959.20, " & _
"'Advanced Health Coverage and Pension Plan', NULL)"
cmd.ExecuteNonQuery()
' Commit
trans.Commit()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' 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 Shared Sub Buy_Company(conn As DB2Connection, trans As DB2Transaction)
Dim conn2 As DB2Connection
Dim trans2 As DB2Transaction
Dim reader as DB2DataReader
Dim reader2 as DB2DataReader
Try
conn2 = ConnectDb(New String() {})
trans2 = conn2.BeginTransaction()
Dim cmd as DB2Command
Dim cmd2 as DB2Command
Dim id as integer ' Employee's ID
Dim department as integer ' Employee's department
Dim years as integer ' Number of years employee has worked
' with the company
Dim new_id as integer = 0 ' Employee's new ID when they switch
' companies
Dim name as string ' Employee's name
Dim job as string ' Employee's job title
Dim benefits as string ' Employee's benefits
Dim salary as decimal ' Employees current salary
Dim old_salary as decimal ' Employees old salary
' 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 DB2DataReader reader.
cmd = conn.CreateCommand()
cmd.CommandText = "SELECT ID, NAME, DEPARTMENT, JOB, YEARS, SALARY " & _
"FROM OLD TABLE (DELETE FROM company_a)"
cmd.Transaction = trans
reader = cmd.ExecuteReader()
' The following while loop iterates through each employee of table
' company_a.
while (reader.Read())
id = reader.GetInt16(0)
name = reader.GetString(1)
department = reader.GetInt16(2)
job = reader.GetString(3)
if(reader.IsDBNull(4))
years = 0
else
years = reader.GetInt16(4)
end if
salary = reader.GetDecimal(5)
' The following if statement sets the new employee's benefits based
' on their years of experience.
if(years > 14)
benefits = "Advanced Health Coverage and Pension Plan"
else if(years > 9)
benefits = "Advanced Health Coverage"
else if(years > 4)
benefits = "Basic Health Coverage"
else
benefits = "No Benefits"
end if
' 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
' cursor is selected into new_id via the DB2DataReader reader2. 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.
cmd2 = conn2.CreateCommand()
cmd2.CommandText = "SELECT ID FROM FINAL TABLE (INSERT INTO " & _
"company_b VALUES(default, ?, ?, ?, ?, ?, ?, ?))"
cmd2.Parameters.Add("@name", DB2Type.VarChar, 9)
cmd2.Parameters.Add("@department", DB2Type.SmallInt, 6)
cmd2.Parameters.Add("@job", DB2Type.Char, 5)
cmd2.Parameters.Add("@years", DB2Type.SmallInt, 6)
cmd2.Parameters.Add("@salary", DB2Type.Decimal, 7)
cmd2.Parameters.Add("@benefits", DB2Type.VarChar, 50)
cmd2.Parameters.Add("@id", DB2Type.SmallInt, 6)
cmd2.Parameters("@name").Value = name
cmd2.Parameters("@department").Value = department
cmd2.Parameters("@job").Value = job
cmd2.Parameters("@years").Value = years
cmd2.Parameters("@salary").Value = salary
cmd2.Parameters("@benefits").Value = benefits
cmd2.Parameters("@id").Value = id
cmd2.Transaction = trans2
reader2 = cmd2.ExecuteReader()
reader2.Read()
new_id = reader2.GetInt16(0)
reader2.Close()
' 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 reader2 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 reader object.
cmd2 = conn2.CreateCommand()
cmd2.CommandText = "SELECT ID, OLD_SALARY, SALARY " & _
" FROM FINAL TABLE (UPDATE company_b INCLUDE " & _
" (OLD_SALARY DECIMAL(7,2)) " & _
" SET OLD_SALARY = SALARY, " & _
" SALARY = SALARY * 1.05 " & _
" WHERE ID = ?)"
cmd2.Parameters.Add("@new_id", DB2Type.SmallInt, 6)
cmd2.Parameters("@new_id").Value = new_id
cmd2.Transaction = trans2
reader2 = cmd2.ExecuteReader()
reader2.Read()
id = reader2.GetInt16(0)
old_salary = reader2.GetDecimal(1)
salary = reader2.GetDecimal(2)
reader2.Close()
' This INSERT statement inserts an employee's id, old salary and
' current salary into the salary_change table.
cmd2 = conn2.CreateCommand()
cmd2.CommandText = " INSERT INTO salary_change VALUES(?, ?, ?)"
cmd2.Parameters.Add("@id", DB2Type.SmallInt, 6)
cmd2.Parameters.Add("@old_salary", DB2Type.Decimal, 6)
cmd2.Parameters.Add("@new_salary", DB2Type.Decimal, 6)
cmd2.Parameters("@id").Value = new_id
cmd2.Parameters("@old_salary").Value = old_salary
cmd2.Parameters("@new_salary").Value = salary
cmd2.Transaction = trans2
cmd2.ExecuteNonQuery()
end while
reader.Close()
' 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.
cmd2 = conn2.CreateCommand()
cmd2.CommandText = "DELETE FROM (SELECT * FROM company_b ORDER BY " & _
" SALARY DESC FETCH FIRST ROW ONLY)"
cmd2.Transaction = trans2
cmd2.ExecuteNonQuery()
' 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.
cmd2 = conn2.CreateCommand()
cmd2.CommandText = "UPDATE (SELECT MAX(YEARS) OVER() AS max_years, " & _
"YEARS, " & _
"SALARY " & _
"FROM company_b) " & _
"SET SALARY = SALARY + 10000 " & _
"WHERE max_years = YEARS "
cmd2.Transaction = trans2
cmd2.ExecuteNonQuery()
' Commit
trans.Commit()
trans2.Commit()
' Disconnect from the database
conn2.Close()
Catch e As Exception
Console.WriteLine(e.Message)
conn2.Close()
End Try
End Sub ' 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 reader object are
' used to fetch and display row data.
Public Shared Sub Print(conn As DB2Connection, trans As DB2Transaction)
Dim reader as DB2DataReader
Dim cmd as DB2Command
Try
Dim id as integer ' Employee's ID
Dim department as integer ' Employee's department
Dim years as integer ' Number of years employee has worked with
' the company
Dim new_id as integer ' Employee's new ID when they switch
' companies
Dim name as string ' Employee's name
Dim job as string ' Employee's job title
Dim benefits as string ' Employee's benefits
Dim salary as decimal ' Employee's current salary
Dim old_salary as decimal ' Employee's old salary
cmd = conn.CreateCommand()
cmd.CommandText = "SELECT ID, NAME, DEPARTMENT, JOB, YEARS, SALARY " & _
"FROM company_a"
cmd.Transaction = trans
reader = cmd.ExecuteReader()
Console.WriteLine()
Console.WriteLine("SELECT * FROM company_a")
Console.WriteLine()
Console.WriteLine("ID NAME DEPARTMENT JOB YEARS SALARY")
Console.WriteLine("------ --------- ---------- ----- ------ ----------")
while (reader.Read())
id = reader.GetInt16(0)
name = reader.GetString(1)
department = reader.GetInt16(2)
job = reader.GetString(3)
if(reader.IsDBNull(4))
years = 0
else
years = reader.GetInt16(4)
end if
salary = reader.GetDecimal(5)
Console.WriteLine("{0,6} {1,9} {2,10} {3,5} {4,6} {5,9:C}", id, name, department, job, years, salary)
end while
reader.Close()
Console.WriteLine()
cmd.CommandText = "SELECT ID, NAME, DEPARTMENT, JOB, YEARS, SALARY, " & _
"BENEFITS, OLD_ID FROM company_b"
cmd.Transaction = trans
reader = cmd.ExecuteReader()
Console.WriteLine("SELECT * FROM company_b")
Console.WriteLine()
Console.WriteLine("ID NAME DEPARTMENT JOB YEARS SALARY ")
Console.WriteLine("BENEFITS OLD_ID")
Console.WriteLine("------ --------- ---------- ----- ------ ---------- ")
Console.WriteLine(" -------------------------------------------------- ------")
while (reader.Read())
id = reader.GetInt16(0)
name = reader.GetString(1)
department = reader.GetInt16(2)
job = reader.GetString(3)
if(reader.IsDBNull(4))
years = 0
else
years = reader.GetInt16(4)
end if
salary = reader.GetDecimal(5)
benefits = reader.GetString(6)
if(reader.IsDBNull(7))
new_id = 0
else
new_id = reader.GetInt16(7)
end if
Console.WriteLine("{0,6} {1,9} {2,10} {3,5} {4,6} {5,9:C}", id, name, department, job, years, salary)
Console.WriteLine("{0,51} {1,6}", benefits, new_id)
Console.WriteLine()
end while
reader.Close()
cmd.CommandText = "SELECT ID, OLD_SALARY, SALARY FROM salary_change"
cmd.Transaction = trans
reader = cmd.ExecuteReader()
Console.WriteLine("SELECT * FROM salary_change")
Console.WriteLine()
Console.WriteLine("ID OLD_SALARY SALARY")
Console.WriteLine("------ ---------- ----------")
while (reader.Read())
id = reader.GetInt16(0)
old_salary = reader.GetDecimal(1)
salary = reader.GetDecimal(2)
Console.WriteLine("{0,6} {1,10:C} {2,9:C}", id, old_salary, salary)
end while
reader.Close()
' Commit
trans.Commit()
Catch e as Exception
Console.WriteLine(e.Message)
End Try
End Sub ' Print
' The Drop function drops the tables used by this sample.
Public Shared Sub Drop(conn As DB2Connection, trans As DB2Transaction)
Try
Console.WriteLine()
Console.WriteLine("DROP TABLE company_a " & _
vbNewLine)
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = _
"DROP TABLE company_a "
cmd.Transaction = trans
cmd.ExecuteNonQuery()
Console.WriteLine("DROP TABLE company_b" & _
vbNewLine)
cmd = conn.CreateCommand()
cmd.CommandText = _
"DROP TABLE company_b"
cmd.Transaction = trans
cmd.ExecuteNonQuery()
Console.WriteLine("DROP TABLE salary_change")
cmd = conn.CreateCommand()
cmd.CommandText = _
"DROP TABLE salary_change"
cmd.Transaction = trans
cmd.ExecuteNonQuery()
' Commit
trans.Commit()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' Drop
End Class ' TbSel