'****************************************************************************
' (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: DtLob.vb
'
' SAMPLE: How to use the LOB data type with the DB2 .Net Data Provider
'
' Before running this sample, ensure that you set the database
' manager configuration parameter UDF Shared Memory Set Size
' (udf_mem_sz) to at least two pages more than the larger
' of the input arguments or the resulting CLOB being retrieved.
'
' For example, issue: db2 UPDATE DBM CFG USING udf_mem_sz 1024
' to run this sample program against the SAMPLE database.
'
' Stop and restart the server for the change to take effect.
'
' SQL Statements USED:
' SELECT
' INSERT
' DELETE
'
' DB2 .NET Data Provider Classes USED:
' DB2Connection
' DB2Command
' DB2Transaction
'
'
'****************************************************************************
'
' Building and Running the sample program
'
' 1. Compile the DtLob.vb file with bldapp.bat by entering the following
' at the command prompt:
'
' bldapp DtLob
'
' or compile DtLob.vb with the makefile by entering the following at
' the command prompt:
'
' nmake DtLob
'
' 2. Run the DtLob program by entering the program name at the command
' prompt:
'
' DtLob
'
'****************************************************************************
'
' 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 System.IO
Imports Microsoft.VisualBasic
Imports IBM.Data.DB2
Public Class DtLob
Public Shared Sub Main(args() As String)
' Declare a DB2Command and DB2Transaction
Dim conn As DB2Connection
Dim trans As DB2Transaction
Try
Console.Write( _
vbNewLine & " THIS SAMPLE SHOWS HOW TO READ AND WRITE LOB DATA ")
' Connect to a database
Console.WriteLine(vbNewLine)
Console.WriteLine(" Connecting to a database ...")
conn = ConnectDb(args)
' Demonstrate how to use the BLOB data type
trans = conn.BeginTransaction()
BlobUse(conn, trans)
' Demonstrate how to use the CLOB data type
trans = conn.BeginTransaction()
ClobUse(conn, trans)
' Demonstrate how to use the CLOB data type with files
trans = conn.BeginTransaction()
ClobFileUse(conn, trans)
' Demonstrate how to search for a substring within a CLOB object
trans = conn.BeginTransaction()
ClobSearchStringUse(conn, trans)
' Demonstrate how to obtain LOB values from a database
trans = conn.BeginTransaction()
LobRead(conn, trans)
' 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 use the Binary Large Object(BLOB)
' data type
Public Shared Sub BlobUse(conn As DB2Connection, _
trans As DB2Transaction)
Try
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENTS:" & vbNewLine & _
" SELECT" & vbNewLine & _
" INSERT" & vbNewLine & _
" DELETE" & vbNewLine & _
" TO SHOW HOW TO USE THE BINARY LARGE OBJECT (BLOB) DATA TYPE.")
Dim photoFormat As String = "bitmap"
Dim empno As String
' ---------- Read BLOB data type from DB -------------------
Console.WriteLine()
Console.WriteLine( _
" ---------------------------------------------------" & _
vbNewLine & _
" READ BLOB DATA TYPE:")
' Create a DB2Command to execute a query
Console.WriteLine()
Console.WriteLine( _
" Create a DB2Command to execute the SQL statement:" & _
vbNewLine & _
" SELECT picture" & vbNewLine & _
" FROM emp_photo" & vbNewLine & _
" WHERE photo_format = ? AND empno = ?")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = _
"SELECT picture " & _
" FROM emp_photo " & _
" WHERE photo_format = ? AND empno = ?"
cmd.Transaction = trans
' Declare parameters for the SQL statement of the DB2Command
cmd.Parameters.Add("@format", DB2Type.Char, 10)
cmd.Parameters.Add("@empno", DB2Type.Char, 6)
Console.WriteLine()
Console.WriteLine( _
" Execute the SQL statement Imports:" & vbNewLine & _
" photo_format = 'bitmap'" & vbNewLine & _
" empno = '000130'")
empno = "000130"
' Assign values to the parameters
cmd.Parameters("@format").Value = photoFormat
cmd.Parameters("@empno").Value = empno
' Execute the query. CommandBehavior.SequentialAccess is passed to
' the DB2DataReader so that data will be loaded sequentially as it
' is received
Dim reader As DB2DataReader
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
' Create a buffer 'out_picture' to store the BLOB object obtained from
' the DB2DataReader
Dim maxSize As Integer = 102400
Dim out_picture(maxSize - 1) As Byte
If (reader.Read()) Then
' Obtain the BLOB object
reader.GetBytes(0,0,out_picture,0,maxSize-1)
End If
reader.Close()
Console.WriteLine()
Console.WriteLine(" READ BLOB DATA TYPE FROM THE DB SUCCESSFULLY!")
' ---------- Insert the BLOB data into the database -----------
Console.WriteLine()
Console.WriteLine( _
" ---------------------------------------------------" & _
vbNewLine & _
" INSERT THE BLOB DATA TYPE INTO THE DB:")
Console.WriteLine()
Console.WriteLine( _
" Create a DB2Command to execute the SQL statement:" & _
vbNewLine & _
" INSERT INTO emp_photo(photo_format, empno, picture)" & _
vbNewLine & " VALUES (?, ?, ?)")
cmd.CommandText = _
"INSERT INTO emp_photo (photo_format, empno, picture) " & _
" VALUES (?, ?, ?)"
cmd.Parameters.Add("@picture", DB2Type.Blob)
Console.WriteLine()
Console.WriteLine( _
" Execute the SQL statement using:" & vbNewLine & _
" photo_format = 'bitmap'" & vbNewLine & _
" empno = '000137'" & vbNewLine & _
" And the blob object that we got from the" & _
vbNewLine & " database eariler.")
empno = "000137"
cmd.Parameters("@empno").Value = empno
cmd.Parameters("@picture").Value = out_picture
cmd.Parameters("@format").Value = photoFormat
cmd.ExecuteNonQuery()
Console.WriteLine()
Console.WriteLine( _
" INSERTED BLOB DATA TYPE INTO THE DB SUCCESSFULLY!")
' ------------ Delete NEW RECORD from the database ---------
Console.WriteLine()
Console.WriteLine( _
" ---------------------------------------------------" & _
vbNewLine & " DELETE THE NEW RECORD FROM THE DATABASE:")
Console.WriteLine()
Console.WriteLine( _
" Create a DB2Command to execute the SQL statement:" & _
vbNewLine & " DELETE FROM emp_photo WHERE empno = ?")
cmd = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = "DELETE FROM emp_photo WHERE empno = ? "
Console.WriteLine()
Console.WriteLine( _
" Execute the prepared statement Imports:" & vbNewLine & _
" empno = '000137'")
cmd.Parameters.Add("@empno", DB2Type.Char, 6).Value = "000137"
cmd.ExecuteNonQuery()
Console.WriteLine()
Console.WriteLine(" DELETED THE NEW RECORD FROM THE DB SUCCESSFULLY!")
trans.Rollback()
Catch e as Exception
Console.WriteLine(e.Message)
End Try
End Sub ' BlobUse
' This method demonstrates how to use the CLOB data type
Public Shared Sub ClobUse(conn As DB2Connection, trans As DB2Transaction)
Try
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENTS:" & vbNewLine & _
" SELECT" & vbNewLine & _
" INSERT" & vbNewLine & _
" DELETE" & vbNewLine & _
" TO SHOW HOW TO USE CHARACTER LARGE OBJECT (CLOB) DATA TYPE.")
' ----------- Read CLOB data type from DB ----------------
Console.WriteLine()
Console.WriteLine( _
" ---------------------------------------------------" & _
vbNewLine & _
" READ CLOB DATA TYPE:")
Console.WriteLine()
Console.WriteLine( _
" Execute the statement:" & vbNewLine & _
" SELECT resume" & vbNewLine & _
" FROM emp_resume" & vbNewLine & _
" WHERE resume_format = 'ascii' AND empno = '000130'" & _
vbNewLine & vbNewLine & " Note: resume is a CLOB data type!")
' Create a DB2Command to execute a query
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = _
"SELECT resume " & _
" FROM emp_resume " & _
" WHERE resume_format = 'ascii' AND empno = '000130'"
' Execute the query
Dim reader As DB2DataReader = cmd.ExecuteReader()
If (reader.Read()) Then
' Retrieve the CLOB object from the DB2DataReader
Dim clob As String = reader.GetString(0)
Console.WriteLine()
Console.WriteLine(" READ CLOB DATA TYPE FROM DB SUCCESSFULLY!")
' ------------ Display the CLOB data onto the screen -------
Dim clobLength As Int64 = clob.Length
Console.WriteLine()
Console.WriteLine( _
" ---------------------------------------------------" & _
vbNewLine & _
" HERE IS THE RESUME WITH A LENGTH OF " & clobLength & _
" CHARACTERS.")
Console.WriteLine()
Console.WriteLine(clob)
Console.WriteLine(" --- END OF RESUME ---")
End If
reader.Close()
trans.Rollback()
Catch e as Exception
Console.WriteLine(e.Message)
End Try
End Sub ' ClobUse
' This method demonstrates how to use the CLOB data type with files
Public Shared Sub ClobFileUse(conn As DB2Connection, _
trans As DB2Transaction)
Try
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENTS:" & vbNewLine & _
" SELECT" & vbNewLine & _
" TO SHOW HOW TO USE CHARACTER LARGE OBJECT (CLOB) DATA TYPE.")
Dim fileName As String = "RESUME.TXT"
' ----------- Read CLOB data type from DB -----------------
Console.WriteLine()
Console.WriteLine( _
" ---------------------------------------------------" & _
vbNewLine & " READ CLOB DATA TYPE:")
Console.WriteLine()
Console.WriteLine( _
" Execute the statement:" & vbNewLine & _
" SELECT resume" & vbNewLine & _
" FROM emp_resume" & vbNewLine & _
" WHERE resume_format = 'ascii' AND empno = '000130'" & _
vbNewLine & vbNewLine & " Note: resume is a CLOB data type!")
' Create a DB2Command to execute a query
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = _
"SELECT resume " & _
" FROM emp_resume " & _
" WHERE resume_format = 'ascii' AND empno = '000130'"
' Execute the query. CommandBehavior.SequentialAccess is passed to
' the DB2DataReader so that data will be loaded sequentially as it
' is received
Dim reader As DB2DataReader
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
Dim maxSize As Integer = 102400
Dim clob(maxSize - 1) As Char
' Retrieve the CLOB data
If (reader.Read()) Then
reader.GetChars(0, 0, clob, 0, maxSize - 1)
End If
Console.WriteLine()
Console.WriteLine(" READ CLOB DATA TYPE SUCCESSFULLY!")
' ---------- Write CLOB data into file -------------------
Dim clobLength As Int64 = clob.Length
Console.WriteLine( _
" ---------------------------------------------------" & _
vbNewLine & _
" WRITE THE CLOB DATA THAT WE GET FROM ABOVE INTO THE " & _
"FILE '" & fileName & "'")
' Write the CLOB data to a file
Dim fstream As FileStream = New FileStream(fileName, _
FileMode.OpenOrCreate, _
FileAccess.Write)
Dim bwriter As BinaryWriter = New BinaryWriter(fstream)
bwriter.Write(clob)
bwriter.Flush()
bwriter.Close()
fstream.Close()
reader.Close()
Console.WriteLine()
Console.WriteLine(" WROTE CLOB DATA TYPE INTO A FILE SUCCESSFULLY!")
trans.Rollback()
Catch e as Exception
Console.WriteLine(e.Message)
End Try
End Sub ' ClobFileUse
' This method demonstrates how to search for a substring within a CLOB
' object
Public Shared Sub ClobSearchStringUse(conn As DB2Connection, _
trans As DB2Transaction)
Try
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENTS:" & vbNewLine & _
" SELECT" & vbNewLine & _
" TO SHOW HOW TO SEARCH FOR A SUBSTRING WITHIN A CLOB OBJECT.")
' ----------- Read CLOB data from file -------------------
Console.WriteLine()
Console.WriteLine( _
" ---------------------------------------------------" & _
vbNewLine & _
" READ CLOB DATA TYPE:")
Console.WriteLine()
Console.WriteLine( _
" Execute the statement:" & vbNewLine & _
" SELECT resume" & vbNewLine & _
" FROM emp_resume" & vbNewLine & _
" WHERE resume_format = 'ascii' AND empno = '000130'" & _
vbNewLine & vbNewLine & " Note: resume is a CLOB data type!")
' Create a DB2Command to execute a query
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = _
"SELECT resume " & _
" FROM emp_resume " & _
" WHERE resume_format = 'ascii' AND empno = '000130'"
' Execute the query. CommandBehavior.SequentialAccess is passed to
' the DB2DataReader so that data will be loaded sequentially as it
' is received
Dim reader As DB2DataReader
reader = cmd.ExecuteReader()
Dim clob As String = ""
If (reader.Read()) Then
' CLOB object retrieved from the DB2DataReader
clob = reader.GetString(0)
End If
reader.Close()
Console.WriteLine()
Console.WriteLine(" READ CLOB DATA TYPE FROM THE DB SUCCESSFULLY!")
' ------ Display the ORIGINAL CLOB data onto the screen -------
Dim clobLength As Int64 = clob.Length
Console.WriteLine()
Console.WriteLine( _
" ***************************************************" & _
vbNewLine & _
" ORIGINAL RESUME -- VIEW " & _
vbNewLine & _
" ***************************************************")
Dim clobString As String = clob.Substring(1)
Console.WriteLine(clobString)
Console.WriteLine(" -- END OF ORIGINAL RESUME -- ")
' ------ Create and Display the modified CLOB data --------
Console.WriteLine()
Console.WriteLine( _
" ***************************************************" & _
vbNewLine & _
" NEW RESUME -- CREATE " & _
vbNewLine & _
" ***************************************************")
' Find the location of the substring
Dim depPos As Int64 = clob.IndexOf("Department Information", 1)
Dim eduPos As Int64 = clob.IndexOf("Education", CType(depPos, Integer))
Console.WriteLine()
Console.WriteLine(" Create New resume without Department info.")
' Create the new CLOB data
Dim beforeDepString As String
Dim afterDepString As String
beforeDepString = clob.Substring(1, CType(depPos-1, Integer))
afterDepString = clob.Substring(CType(eduPos, Integer), _
CType(clobLength-eduPos, Integer))
Dim newClobString As String = beforeDepString
newClobString = String.Concat(newClobString, _
afterDepString & vbNewLine)
Console.WriteLine()
Console.WriteLine( _
" Append Department Info at the end of the New resume.")
Dim depString As String = clob.Substring(CType(depPos, Integer), _
CType(eduPos-depPos,Integer))
newClobString = String.Concat(NewClobString, " " & depString)
' Insert the modified CLOB data into the database
Console.WriteLine()
Console.WriteLine( _
" Insert the New resume into the database.")
cmd = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = _
"INSERT INTO emp_resume (empno, resume_format, resume) " & _
" VALUES (?, ?, ?)"
cmd.Parameters.Add("@empno", DB2Type.Char, 6).Value = "000137"
cmd.Parameters.Add("@format", DB2Type.Char, 10).Value = "ascii"
cmd.Parameters.Add("@resume", DB2Type.Clob).Value = newClobString
cmd.ExecuteNonQuery()
Console.WriteLine()
Console.WriteLine( _
" ***************************************************" & _
vbNewLine & _
" NEW RESUME -- VIEW " & _
vbNewLine & _
" ***************************************************")
' ----------- Read the NEW RESUME (CLOB) from DB ------------
Console.WriteLine()
Console.WriteLine( _
" ---------------------------------------------------" & _
vbNewLine & " READ CLOB DATA TYPE:")
Console.WriteLine()
Console.WriteLine( _
" Execute the statement:" & vbNewLine & _
" SELECT resume" & vbNewLine & _
" FROM emp_resume" & vbNewLine & _
" WHERE resume_format = 'ascii' AND empno = '000137'")
cmd = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = _
"SELECT resume " & _
" FROM emp_resume " & _
" WHERE resume_format = 'ascii' AND empno = '000137'"
reader = cmd.ExecuteReader()
If (reader.Read()) Then
clob = reader.GetString(0)
Console.WriteLine()
Console.WriteLine( _
" READ NEW RESUME (CLOB) FROM THE DB SUCCESSFULLY!")
' ------ Display the NEW RESUME (CLOB) onto the screen -------
clobLength = clob.Length
Console.WriteLine()
Console.WriteLine( _
" ---------------------------------------------------" & _
vbNewLine & " HERE IS THE NEW RESUME:")
Console.WriteLine(clob)
Console.WriteLine()
Console.WriteLine(" -- END OF NEW RESUME --")
End If
reader.Close()
' ---------- Delete the NEW RESUME from the database ----
Console.WriteLine()
Console.WriteLine( _
" ***************************************************" & _
vbNewLine & _
" NEW RESUME -- DELETE " & _
vbNewLine & _
" ***************************************************")
cmd.CommandText = "DELETE FROM emp_resume WHERE empno = '000137' "
cmd.ExecuteNonQuery()
Console.WriteLine()
Console.WriteLine(" NEW RESUME DELETED")
trans.Rollback()
Catch e as Exception
Console.WriteLine(e.Message)
End Try
End Sub ' clobSearchStringUse
' This method demonstrates how to obtain Large Object(LOB) values from
' a database
Public Shared Sub LobRead(conn As DB2Connection, trans As DB2Transaction)
Try
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENT:" & vbNewLine & _
" SELECT" & vbNewLine & _
" TO SHOW HOW TO USE OBTAIN LARGE OBJECT (LOB) VALUES FROM A " & _
" DATABASE." & vbNewLine & _
" ----------------------------------------------------------")
Dim photoFormat As String = "bitmap"
Dim fileName As String = "photo.BMP"
Dim empno As String
Console.WriteLine()
Console.WriteLine( _
" CREATE A DB2Command TO EXECUTE THE SQL STATEMENT:" & _
vbNewLine & vbNewLine & _
" SELECT picture, resume from emp_photo, emp_resume" & _
vbNewLine & _
" WHERE emp_photo.empno = ? and emp_resume.empno = ?" & _
vbNewLine & _
" AND photo_format = ? AND resume_format = ?")
' Create a DB2Command to execute an SQL statement
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = _
"SELECT picture, resume from emp_photo, emp_resume" & _
" WHERE emp_photo.empno = ? and emp_resume.empno = ?" & _
" AND photo_format = ? AND resume_format = ?"
cmd.Transaction = trans
' Declare parameters for the SQL statement of the DB2Command
cmd.Parameters.Add("@photo_empno", DB2Type.Char, 6)
cmd.Parameters.Add("@resume_empno", DB2Type.Char, 6)
cmd.Parameters.Add("@photo_format", DB2Type.Char, 10)
cmd.Parameters.Add("@resume_format", DB2Type.Char, 10)
Console.WriteLine()
Console.WriteLine( _
" Execute the SQL statement Imports:" & vbNewLine & _
" photo_format = 'bitmap'" & vbNewLine & _
" resume_format = 'ascii'" & vbNewLine & _
" empno = '000130'")
empno = "000130"
cmd.Parameters("@photo_format").Value = photoFormat
cmd.Parameters("@photo_empno").Value = empno
cmd.Parameters("@resume_empno").Value = empno
cmd.Parameters("@resume_format").Value = "ascii"
' Execute the SQL statement. CommandBehavior.SequentialAccess is
' passed to the DB2DataReader so that data will be loaded
' sequentially as it is received
Dim reader As DB2DataReader
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
If (reader.Read()) Then
' LOB values have to be obtained sequentially
Console.WriteLine()
Console.WriteLine(" READING VALUES SEQUENTIALLY")
Console.WriteLine()
Console.WriteLine(" OBTAINING BLOB VALUE AND STORING IT IN A " & _
"FILE: " & fileName)
' First obtain the BLOB value and store it in a file
Dim maxSize As Integer = 102400
Dim out_picture(maxSize - 1) As Byte
reader.GetBytes(0,0,out_picture,0,maxSize-1)
Dim fstream As FileStream = New FileStream(fileName, _
FileMode.OpenOrCreate, _
FileAccess.Write)
Dim bwriter As BinaryWriter = New BinaryWriter(fstream)
bwriter.Write(out_picture)
bwriter.Flush()
' Next obtain and display the CLOB value
Console.WriteLine()
Console.WriteLine(" OBTAINING RESUME(CLOB VALUE) FROM THE DATABASE")
Dim out_resume As String = reader.GetString(1)
Console.WriteLine( _
" ---------------------------------------------------" & _
vbNewLine & " RESUME OBTAINED FORM THE DATABASE:")
Console.WriteLine(out_resume)
Console.WriteLine()
Console.WriteLine(" -- END OF RESUME --")
End If
reader.Close()
Console.WriteLine()
Console.WriteLine(" LOB VALUES OBTAINED FROM DATABASE SUCCESSFULLY!")
trans.Rollback()
Catch e as Exception
Console.WriteLine(e.Message)
End Try
End Sub ' LobRead
End Class ' DtLob