/****************************************************************************
** (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.cs
**
** 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
** DB2 Transaction
**
**
*****************************************************************************
**
** Building and Running the sample program
**
** 1. Compile the DtLob.cs file with bldapp.bat by entering the following
** at the command prompt:
**
** bldapp DtLob
**
** or compile DtLob.cs 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
**
****************************************************************************/
using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;
class DtLob
{
public static void Main(String[] args)
{
// Declare a DB2Command and DB2Transaction
DB2Connection conn = null;
DB2Transaction trans = null;
try
{
Console.WriteLine("\n THIS SAMPLE SHOWS HOW TO READ AND WRITE" +
" LOB DATA");
// Connect to a database
Console.WriteLine("\n 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("\n Disconnect from the database.");
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
conn.Close();
}
} // Main
// Helper method: This method establishes a connection to a database
public static DB2Connection ConnectDb(String[] argv)
{
String server = "";
String alias = "";
String userId = "";
String password = "";
Int32 portNumber = -1;
String connectString;
if( argv.Length > 5 ||
( argv.Length == 1 &&
( String.Compare(argv[0],"?") == 0 ||
String.Compare(argv[0],"-?") == 0 ||
String.Compare(argv[0],"/?") == 0 ||
String.Compare(argv[0],"-h",true) == 0 ||
String.Compare(argv[0],"/h",true) == 0 ||
String.Compare(argv[0],"-help",true) == 0 ||
String.Compare(argv[0],"/help",true) == 0 ) ) )
{
throw new Exception(
"Usage: prog_name [dbAlias] [userId passwd] \n" +
" prog_name [dbAlias] server portNum userId passwd");
}
switch (argv.Length)
{
case 0: // Use all defaults
alias = "sample";
userId = "";
password = "";
break;
case 1: // dbAlias specified
alias = argv[0];
userId = "";
password = "";
break;
case 2: // userId & passwd specified
alias = "sample";
userId = argv[0];
password = argv[1];
break;
case 3: // dbAlias, userId & passwd specified
alias = argv[0];
userId = argv[1];
password = argv[2];
break;
case 4: // use default dbAlias
alias = "sample";
server = argv[0];
portNumber = Convert.ToInt32(argv[1]);
userId = argv[2];
password = argv[3];
break;
case 5: // everything specified
alias = argv[0];
server = argv[1];
portNumber = Convert.ToInt32(argv[2]);
userId = argv[3];
password = argv[4];
break;
}
if(portNumber==-1)
{
connectString = "Database=" + alias;
}
else
{
connectString = "Server=" + server + ":" + portNumber +
";Database=" + alias;
}
if(userId != "")
{
connectString += ";UID=" + userId + ";PWD=" + password;
}
DB2Connection conn = new DB2Connection(connectString);
conn.Open();
Console.WriteLine(" Connected to the " + alias + " database");
return conn;
} // ConnectDb
// This method demonstrates how to use the Binary Large Object(BLOB)
// data type
public static void BlobUse(DB2Connection conn, DB2Transaction trans)
{
try
{
Console.WriteLine();
Console.WriteLine(
" ----------------------------------------------------------\n" +
" USE THE SQL STATEMENTS:\n" +
" SELECT\n" +
" INSERT\n" +
" DELETE\n" +
" TO SHOW HOW TO USE THE BINARY LARGE OBJECT (BLOB) DATA TYPE.");
String photoFormat = "bitmap";
String empno;
// ---------- Read BLOB data type from DB -------------------
Console.WriteLine();
Console.WriteLine(
" ---------------------------------------------------\n" +
" READ BLOB DATA TYPE:");
// Create a DB2Command to execute a query
Console.WriteLine();
Console.WriteLine(
" Create a DB2Command to execute the SQL statement:\n" +
" SELECT picture\n" +
" FROM emp_photo\n" +
" WHERE photo_format = ? AND empno = ?");
DB2Command cmd = 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 using:\n" +
" photo_format = 'bitmap'\n" +
" 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
DB2DataReader reader;
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
// Create a buffer 'out_picture' to store the BLOB object obtained from
// the DB2DataReader
int maxSize = 102400;
Byte[] out_picture = new Byte[maxSize];
if(reader.Read())
{
// Obtain the BLOB object
reader.GetBytes(0,0,out_picture,0,maxSize);
}
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(
" ---------------------------------------------------\n" +
" INSERT THE BLOB DATA TYPE INTO THE DB:");
Console.WriteLine();
Console.WriteLine(
" Create a DB2Command to execute the SQL statement:\n" +
" INSERT INTO emp_photo(photo_format, empno, picture)\n" +
" 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:\n" +
" photo_format = 'bitmap'\n" +
" empno = '000137'\n" +
" And the blob object that we got from the\n" +
" 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(
" ---------------------------------------------------\n" +
" DELETE THE NEW RECORD FROM THE DATABASE:");
Console.WriteLine();
Console.WriteLine(
" Create a DB2Command to execute the SQL statement:\n" +
" 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 using:\n" +
" 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 (Exception e)
{
Console.WriteLine(e.Message);
}
} // BlobUse
// This method demonstrates how to use the CLOB data type
public static void ClobUse(DB2Connection conn, DB2Transaction trans)
{
try
{
Console.WriteLine();
Console.WriteLine(
" ----------------------------------------------------------\n" +
" USE THE SQL STATEMENTS:\n" +
" SELECT\n" +
" INSERT\n" +
" DELETE\n" +
" TO SHOW HOW TO USE CHARACTER LARGE OBJECT (CLOB) DATA TYPE.");
// ----------- Read CLOB data type from DB ----------------
Console.WriteLine();
Console.WriteLine(
" ---------------------------------------------------\n" +
" READ CLOB DATA TYPE:");
Console.WriteLine();
Console.WriteLine(
" Execute the statement:\n" +
" SELECT resume\n" +
" FROM emp_resume\n" +
" WHERE resume_format = 'ascii' AND empno = '000130'\n" +
"\n" +
" Note: resume is a CLOB data type!");
// Create a DB2Command to execute a query
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"SELECT resume " +
" FROM emp_resume " +
" WHERE resume_format = 'ascii' AND empno = '000130'";
// Execute the query
DB2DataReader reader = cmd.ExecuteReader();
if(reader.Read())
{
// Retrieve the CLOB object from the DB2DataReader
String clob = reader.GetString(0);
Console.WriteLine();
Console.WriteLine(" READ CLOB DATA TYPE FROM DB SUCCESSFULLY!");
// ------------ Display the CLOB data onto the screen -------
Int64 clobLength = clob.Length;
Console.WriteLine();
Console.WriteLine(
" ---------------------------------------------------\n" +
" HERE IS THE RESUME WITH A LENGTH OF " + clobLength +
" CHARACTERS.");
Console.WriteLine();
Console.WriteLine(clob);
Console.WriteLine(" --- END OF RESUME ---");
}
reader.Close();
trans.Rollback();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // ClobUse
// This method demonstrates how to use the CLOB data type with files
public static void ClobFileUse(DB2Connection conn, DB2Transaction trans)
{
try
{
Console.WriteLine();
Console.WriteLine(
" ----------------------------------------------------------\n" +
" USE THE SQL STATEMENTS:\n" +
" SELECT\n" +
" TO SHOW HOW TO USE CHARACTER LARGE OBJECT (CLOB) DATA TYPE.");
String fileName = "RESUME.TXT";
// ----------- Read CLOB data type from DB -----------------
Console.WriteLine();
Console.WriteLine(
" ---------------------------------------------------\n" +
" READ CLOB DATA TYPE:");
Console.WriteLine();
Console.WriteLine(
" Execute the statement:\n" +
" SELECT resume\n" +
" FROM emp_resume\n" +
" WHERE resume_format = 'ascii' AND empno = '000130'\n" +
"\n" +
" Note: resume is a CLOB data type!");
// Create a DB2Command to execute a query
DB2Command cmd = 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
DB2DataReader reader;
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
int maxSize = 102400;
char[] clob = new char[maxSize];
// Retrieve the CLOB data
if(reader.Read())
{
reader.GetChars(0, 0, clob, 0, maxSize - 1);
}
Console.WriteLine();
Console.WriteLine(" READ CLOB DATA TYPE SUCCESSFULLY!");
// ---------- Write CLOB data into file -------------------
Int64 clobLength = clob.Length;
Console.WriteLine(
" ---------------------------------------------------\n" +
" WRITE THE CLOB DATA THAT WE GET FROM ABOVE INTO THE " +
"FILE '" + fileName + "'");
// Write the CLOB data to a file
FileStream fstream = new FileStream(fileName,
FileMode.OpenOrCreate,
FileAccess.Write);
BinaryWriter bwriter = 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 (Exception e)
{
Console.WriteLine(e.Message);
}
} // ClobFileUse
// This method demonstrates how to search for a substring within a CLOB
// object
public static void ClobSearchStringUse(DB2Connection conn,
DB2Transaction trans)
{
try
{
Console.WriteLine();
Console.WriteLine(
" ----------------------------------------------------------\n" +
" USE THE SQL STATEMENTS:\n" +
" SELECT\n" +
" TO SHOW HOW TO SEARCH FOR A SUBSTRING WITHIN A CLOB OBJECT.");
// ----------- Read CLOB data from file -------------------
Console.WriteLine();
Console.WriteLine(
" ---------------------------------------------------\n" +
" READ CLOB DATA TYPE:");
Console.WriteLine();
Console.WriteLine(
" Execute the statement:\n" +
" SELECT resume\n" +
" FROM emp_resume\n" +
" WHERE resume_format = 'ascii' AND empno = '000130'\n" +
"\n" +
" Note: resume is a CLOB data type!");
// Create a DB2Command to execute a query
DB2Command cmd = 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
DB2DataReader reader;
reader = cmd.ExecuteReader();
String clob = "";
if(reader.Read())
{
// CLOB object retrieved from the DB2DataReader
clob = reader.GetString(0);
}
reader.Close();
Console.WriteLine();
Console.WriteLine(" READ CLOB DATA TYPE FROM THE DB SUCCESSFULLY!");
// ------ Display the ORIGINAL CLOB data onto the screen -------
Int64 clobLength = clob.Length;
Console.WriteLine();
Console.WriteLine(
" ***************************************************\n" +
" ORIGINAL RESUME -- VIEW \n" +
" ***************************************************");
String clobString = clob.Substring(1);
Console.WriteLine(clobString);
Console.WriteLine(" -- END OF ORIGINAL RESUME -- ");
// ------ Create and Display the modified CLOB data --------
Console.WriteLine();
Console.WriteLine(
" ***************************************************\n" +
" NEW RESUME -- CREATE \n" +
" ***************************************************");
// Find the location of the substring
Int64 depPos = clob.IndexOf("Department Information", 1);
Int64 eduPos = clob.IndexOf("Education", (int)depPos);
Console.WriteLine();
Console.WriteLine(" Create new resume without Department info.");
// Create the new CLOB data
String beforeDepString = clob.Substring(1, (int)depPos-1);
String afterDepString = clob.Substring((int)eduPos,
(int)(clobLength-eduPos));
String newClobString = beforeDepString;
newClobString = String.Concat(newClobString, afterDepString + "\n");
Console.WriteLine();
Console.WriteLine(
" Append Department Info at the end of the new resume.");
String depString = clob.Substring((int)depPos,
(int)(eduPos-depPos));
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(
" ***************************************************\n" +
" NEW RESUME -- VIEW \n" +
" ***************************************************");
// ----------- Read the NEW RESUME (CLOB) from DB ------------
Console.WriteLine();
Console.WriteLine(
" ---------------------------------------------------\n" +
" READ CLOB DATA TYPE:");
Console.WriteLine();
Console.WriteLine(
" Execute the statement:\n" +
" SELECT resume\n" +
" FROM emp_resume\n" +
" 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())
{
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(
" ---------------------------------------------------\n" +
" HERE IS THE NEW RESUME:");
Console.WriteLine(clob);
Console.WriteLine();
Console.WriteLine(" -- END OF NEW RESUME --");
}
reader.Close();
// ---------- Delete the NEW RESUME from the database ----
Console.WriteLine();
Console.WriteLine(
" ***************************************************\n" +
" NEW RESUME -- DELETE \n" +
" ***************************************************");
cmd.CommandText =
"DELETE FROM emp_resume WHERE empno = '000137' ";
cmd.ExecuteNonQuery();
Console.WriteLine();
Console.WriteLine(" NEW RESUME DELETED");
trans.Rollback();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // ClobSearchStringUse
// This method demonstrates how to obtain Large Object(LOB) values from
// a database
public static void LobRead(DB2Connection conn, DB2Transaction trans)
{
try
{
Console.WriteLine();
Console.WriteLine(
" ----------------------------------------------------------\n" +
" USE THE SQL STATEMENT:\n" +
" SELECT\n" +
" TO SHOW HOW TO USE OBTAIN LARGE OBJECT (LOB) VALUES FROM A" +
" DATABASE.\n" +
" ----------------------------------------------------------");
String photoFormat = "bitmap";
String fileName = "photo.BMP";
String empno;
Console.WriteLine();
Console.WriteLine(
" CREATE A DB2Command TO EXECUTE THE SQL STATEMENT:\n\n" +
" SELECT picture, resume from emp_photo, emp_resume\n" +
" WHERE emp_photo.empno = ? and emp_resume.empno = ?\n" +
" AND photo_format = ? AND resume_format = ?");
// Create a DB2Command to execute an SQL statement
DB2Command cmd = 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 using:\n" +
" photo_format = 'bitmap'\n" +
" resume_format = 'ascii'\n" +
" 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
DB2DataReader reader;
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
if(reader.Read())
{
// 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
int maxSize = 102400;
Byte[] out_picture = new Byte[maxSize];
reader.GetBytes(0,0,out_picture,0,maxSize);
FileStream fstream = new FileStream(fileName,
FileMode.OpenOrCreate,
FileAccess.Write);
BinaryWriter bwriter = 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");
String out_resume = reader.GetString(1);
Console.WriteLine(
" ---------------------------------------------------\n" +
" RESUME OBTAINED FORM THE DATABASE:");
Console.WriteLine(out_resume);
Console.WriteLine();
Console.WriteLine(" -- END OF RESUME --");
}
reader.Close();
Console.WriteLine();
Console.WriteLine(" LOB VALUES OBTAINED FROM DATABASE SUCCESSFULLY!");
trans.Rollback();
}
catch(Exception e)
{
Console.WriteLine(e.Message);
}
} // LobRead
} // DtLob