'****************************************************************************
' (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.vb
'
' SAMPLE: How to manipulate table data with the DB2 .Net Data Provider
' and connect to/disconnect from a database
'
' SQL Statements USED:
' SELECT
' INSERT
' UPDATE
' DELETE
'
' DB2 .NET Data Provider Classes USED:
' DB2Connection
' DB2Command
' DB2Transaction
'
'
'****************************************************************************
'
' Building and Running the sample program
'
' 1. Compile the TbUse.vb file with bldapp.bat by entering the following
' at the command prompt:
'
' bldapp TbUse
'
' or compile TbUse.vb with the makefile by entering the following at
' the command prompt:
'
' nmake TbUse
'
' 2. Run the TbUse program by entering the program name at the command
' prompt:
'
' TbUse
'
'****************************************************************************
'
' 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 IBM.Data.DB2
Imports Microsoft.VisualBasic
Public Class TbUse
Public Shared Sub Main(args() As String)
' Declare a DB2Command, a DB2Transaction and a DB2Connection
Dim conn As DB2Connection
Dim trans As DB2Transaction
Dim cmd As DB2Command
Try
Console.WriteLine()
Console.WriteLine(" THIS SAMPLE SHOWS HOW TO CONNECT" & _
" TO/DISCONNECT FROM A DATABASE" & vbNewLine & _
" AND PERFORM BASIC DATABASE OPERATIONS.")
Console.WriteLine()
' Connect to a database
Console.WriteLine(" Connecting to a database ...")
conn = ConnectDb(args)
' Instantiate the DB2Command
trans = conn.BeginTransaction()
cmd = conn.CreateCommand()
cmd.Connection = conn
cmd.Transaction = trans
' Perform a query with the 'org' table
BasicQuery(conn,trans,cmd)
' Insert rows into the 'staff' table
BasicInsert(conn,trans,cmd)
trans=conn.BeginTransaction()
cmd.Transaction = trans
' Update a set of rows in the 'staff' table
BasicUpdate(conn,trans,cmd)
trans=conn.BeginTransaction()
cmd.Transaction = trans
' Delete a set of rows from the 'staff' table
BasicDelete(conn,trans,cmd)
' Disconnect from the database
Console.WriteLine(vbNewLine & _
" Disconnect from the 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
' This method establishes a connection to a database
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()
Console.WriteLine(" Connected to the " & dbalias & " database")
Return conn
End Function ' ConnectDb
' This method demonstrates how to perform a standard query
Public Shared Sub BasicQuery(conn As DB2Connection, _
trans As DB2Transaction, _
cmd As DB2Command)
Try
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENT:" & vbNewLine & _
" SELECT" & vbNewLine & _
" TO QUERY DATA FROM A TABLE.")
' Set up and execute the query
Console.WriteLine( _
vbNewLine & _
" Execute Statement:" & vbNewLine & _
" SELECT deptnumb, location FROM org WHERE deptnumb < 25")
cmd.CommandText = "SELECT deptnumb, location " & _
" FROM org " & _
" WHERE deptnumb < 25"
Dim reader As DB2DataReader = cmd.ExecuteReader()
Console.WriteLine()
Console.WriteLine(" Results:" & vbNewLine & _
" DEPTNUMB LOCATION" & vbNewLine & _
" -------- --------------")
Dim deptnum As Int16 = 0
Dim location As String
' Output the results of the query
Do While reader.Read()
deptnum = reader.GetInt16(0)
location = reader.GetString(1)
Console.WriteLine(" " & _
Format(deptnum, 8) & " " & _
Format(location, 14))
Loop
reader.Close()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' BasicQuery
' This method demonstrates how to insert rows into a table
Public Shared Sub BasicInsert(conn As DB2Connection, _
trans As DB2Transaction, _
cmd As DB2Command)
Try
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENT:" & vbNewLine & _
" INSERT" & vbNewLine & _
" TO INSERT DATA INTO A TABLE USING VALUES.")
' Display contents of the 'staff' table before inserting rows
DisplayStaffTable(conn,cmd)
' Use the INSERT statement to insert data into the 'staff' table.
Console.WriteLine()
Console.WriteLine( _
" Invoke the statement:" & vbNewLine & _
" INSERT INTO staff(id, name, dept, job, salary)" & vbNewLine & _
" VALUES(380, 'Pearce', 38, 'Clerk', 13217.50)," & vbNewLine & _
" (390, 'Hachey', 38, 'Mgr', 21270.00)," & vbNewLine & _
" (400, 'Wagland', 38, 'Clerk', 14575.00)")
cmd.CommandText = "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)"
cmd.ExecuteNonQuery()
' Display the content in the 'staff' table after the INSERT.
DisplayStaffTable(conn,cmd)
' Rollback the transaction
trans.Rollback()
Catch e As Exception
Console.WriteLine(e.Message)
trans.Rollback()
End Try
End Sub ' BasicInsert
' This method demonstrates how to update rows in a table
Public Shared Sub BasicUpdate(conn As DB2Connection, _
trans As DB2Transaction, _
cmd As DB2Command)
Try
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENT:" & vbNewLine & _
" UPDATE" & vbNewLine & _
" TO UPDATE TABLE DATA USING SUBQUERY IN THE 'SET' CLAUSE.")
' Display contents of the 'staff' table before updating
DisplayStaffTable(conn,cmd)
' Update the data of the table 'staff' by using subquery in the SET
' clause
Console.WriteLine()
Console.WriteLine( _
" Invoke the statement:" & vbNewLine & _
" UPDATE staff" & vbNewLine & _
" SET salary = (SELECT MIN(salary)" & vbNewLine & _
" FROM staff" & vbNewLine & _
" WHERE id >= 310)" & vbNewLine & _
" WHERE id = 310")
cmd.CommandText = "UPDATE staff " & _
" SET salary = (SELECT MIN(salary) " & _
" FROM staff " & _
" WHERE id >= 310) " & _
" WHERE id = 310"
cmd.ExecuteNonQuery()
' Display the final content of the 'staff' table
DisplayStaffTable(conn,cmd)
trans.Rollback()
Catch e As Exception
Console.WriteLine(e.Message)
trans.Rollback()
End Try
End Sub ' BasicUpdate
' This method demonstrates how to delete rows from a table
Public Shared Sub BasicDelete(conn As DB2Connection, _
trans As DB2Transaction, _
cmd As DB2Command)
Try
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENT:" & vbNewLine & _
" DELETE" & vbNewLine & _
" TO DELETE TABLE DATA.")
' Display contents of the 'staff' table
DisplayStaffTable(conn,cmd)
' Delete rows from the 'staff' table where id >= 310 and
' salary > 20000
Console.WriteLine()
Console.WriteLine( _
" Invoke the statement:" & vbNewLine & _
" DELETE FROM staff WHERE id >= 310 AND salary > 20000")
cmd.CommandText = "DELETE FROM staff " & _
" WHERE id >= 310 " & _
" AND salary > 20000"
cmd.ExecuteNonQuery()
' Display the final content of the 'staff' table
DisplayStaffTable(conn,cmd)
trans.Rollback()
Catch e As Exception
trans.Rollback()
Console.WriteLine(e.Message)
End Try
End Sub ' BasicDelete
' Helping method: Display content from the 'staff' table
Public Shared Sub DisplayStaffTable(conn As DB2Connection, _
cmd As DB2Command)
Try
Dim id As Int16 = 0
Dim name As String
Dim dept As Int16 = 0
Dim job As String
Dim years As Int16 = 0
Dim salary As Decimal = 0
Dim comm As Decimal = 0
Console.WriteLine()
Console.WriteLine( _
" SELECT * FROM staff WHERE id >= 310" & vbNewLine & vbNewLine & _
" ID NAME DEPT JOB YEARS SALARY COMM" & vbNewLine & _
" --- -------- ---- ----- ----- -------- --------")
cmd.CommandText = "SELECT * FROM staff WHERE id >= 310"
Dim reader As DB2DataReader = cmd.ExecuteReader()
Do While reader.Read()
id = reader.GetInt16(0)
name = reader.GetString(1)
dept = reader.GetInt16(2)
job = reader.GetString(3)
If (reader.IsDBNull(4)) Then
years = 0
Else
years = reader.GetInt16(4)
End If
salary = reader.GetDecimal(5)
If ( reader.IsDBNull(6) ) Then
comm = 0
Else
comm = reader.GetDecimal(6)
End If
Console.Write(" " & Format(id, 3) & _
" " & Format(name, 8) & _
" " & Format(dept, 4))
If (job is System.DBNull.value) Then
Console.Write(" -")
Else
Console.Write(" " & Format(job, 5))
End If
If (years <> 0) Then
Console.Write(" " & Format(years, 5))
Else
Console.Write(" -")
End If
Console.Write(" " & Format(salary, 7, 2))
if (comm <> 0) Then
Console.Write(" " & Format(comm, 7, 2))
Else
Console.Write(" -")
End If
Console.WriteLine()
Loop
reader.Close()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' DisplayStaffTable
' Helping method: This method takes a String and returns it with
' length 'finalLen'
Public Shared Function Format(strData As String, _
finalLen As Integer) As String
Dim finalStr As String
If (finalLen <= strData.Length) Then
finalStr = strData.Substring(0, finalLen)
Else
finalStr = strData
Dim i As Integer
For i = strData.Length to (finalLen-1)
finalStr = finalStr & " "
Next i
End If
Return finalStr
End Function ' Format(String , Integer)
' Helping method: This method takes an Int16 and returns it as a String
' with length 'finalLen'
Public Shared Function Format(IntData As Int16, _
finalLen As Integer) As String
Dim strData As String = IntData.ToString()
Dim finalStr As String
If (finalLen <= strData.Length) Then
finalStr = strData.Substring(0, finalLen)
Else
finalStr = ""
Dim i As Integer
For i = 0 to (finalLen - strData.Length - 1)
finalStr = finalStr & " "
Next i
finalStr = finalStr & strData
End If
Return finalStr
End Function ' Format(Int16, Integer)
' Helping method: This method takes a Decimal and returns it as a String
' with a specified precision and scale
Public Shared Function Format(doubData As Decimal, _
precision As Integer, _
scale As Integer) As String
Dim dataRound As Decimal = Decimal.Round(doubData,scale)
Dim strData As String = String.Format("{0:f" & scale & "}",dataRound)
' Prepare the final string
Dim finalLen As Integer = precision + 1
Dim finalStr As String
If (finalLen <= strData.Length) Then
finalStr = strData.Substring(0, finalLen)
Else
finalStr = ""
Dim i As Integer
For i = 0 to (finalLen - strData.Length-1)
finalStr = finalStr & " "
Next i
finalStr = finalStr & strData
End If
Return finalStr
End Function ' Format(Decimal, Integer, Integer)
End Class ' TbUse