Inserting Data Into LONGVARCHAR and LONGVARBINARY Columns
Show this topic in Library frames
Allowing the use of noncontiguous memory to represent a very large binary or text value, such as a video clip or a large document, improves performance, functionality, and scalability.
Stream objects used to read binary data are derived from the System.IO.Stream object.
The following example shows how to insert data into LONGVARCHAR and LONGVARBINARY columns using randomly generated data. The sample also shows how to use stream objects as inputs to LONGVARCHAR and LONGVARBINARY columns:
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new LongDataTypes());
}
private void LongDataTypes_Load(object sender, System.EventArgs e)
{
}
/// <summary>
/// Writes out all the information during
/// the test run onto the OutputWindow text box.
/// </summary>
/// <param name="writer"></param>
private void flush (StringWriter writer)
{
OutputWindow.AppendText (writer.ToString ());
OutputWindow.Refresh();
writer.GetStringBuilder ().Length = 0;
}
private void Start_Click(object sender, System.EventArgs e)
{
// Clear the OutputWindow
OutputWindow.Clear();
// New String writer for all the output generated
StringWriter writer = new StringWriter();
// Pervasive Command
PsqlCommand DBCmd;
// Change the connection string to match your servers
PsqlConnection Conn = new PsqlConnection("Data Source=DEMODATA");
try
{
try
{
// Open the connection
Conn.Open();
}
catch (PsqlException ex)
{
// Connection failed
writer.WriteLine(ex.Message);
flush(writer);
return;
}
// Prepare an insert command with parameters defined.
DBCmd = new PsqlCommand("insert into longdbtypes values(?,?)", Conn);
DBCmd.Parameters.Add("@id", PsqlDbType.Integer, 10, "id");
DBCmd.Parameters.Add("@clobCol", PsqlDbType.LongVarChar, 32000, "clobCol");
// Build non-unicode string for input. Setting the boolean flag to
// true will build non-unicode string.
string clobInput = buildString(32000, false);
// Insert one row in the table
try
{
DBCmd.Parameters[0].Value = 0;
DBCmd.Parameters[1].Value = clobInput;
DBCmd.ExecuteNonQuery();
writer.WriteLine("Insert Successful!!");
flush(writer);
}
catch (Exception ex)
{
// Insert failed. Display the exception.
writer.WriteLine("Insert Failed.");
writer.WriteLine(ex.Message);
flush(writer);
}
// Fetching long data and checking for the accuracy
DBCmd = new PsqlCommand("select * from longdbtypes order by id", Conn);
try
{
// Call ExecuteReader on the command object
PsqlDataReader dataReader = DBCmd.ExecuteReader();
// Read the result set from the DataReader object
while (dataReader.Read())
{
// Data from long columns
string clobFetch = dataReader["clobCol"].ToString();
if (String.Equals(clobInput, clobFetch))
{
// Compare Passed
writer.WriteLine("CLOB compare was a Sucess!!");
flush(writer);
}
else
{
// Failure is fetching data
writer.WriteLine("DataMiscompare: CLOB");
flush(writer);
}
}
}
catch (Exception ex)
{
// Display any exception
writer.WriteLine(ex.Message);
flush(writer);
}
writer.WriteLine("End of Long Data Type Test!!");
flush(writer);
// Cleanup. Delete the row added.
try
{
DBCmd = new PsqlCommand("delete from longdbtypes where id = 0", Conn);
DBCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
// Display any exception
writer.WriteLine(ex.Message);
flush(writer);
}
}
catch (Exception ex)
{
// Display any exception
writer.WriteLine(ex.Message);
flush(writer);
}
finally
{
// Close the connection
Conn.Close();
}
}
// This method call builds long strings, Unicode and non-Unicode
// (depending in the number of characters specified) to be inserted
// into the LongDbTypes table.
private static string buildString(int numChars, bool unicode)
{
int numTimes = numChars / 10;
char[] ansiValues1252 = new char[8] {'A', 'm', '\u00C6', '\u00E6', '\u00D6', '\u00F6', '4', '7'};
char[] unicodeValues = new char[8] {'A', '\u3044', '\u00C6', '\u00E6', '\u3068','\u00D6', '4', '\u00F6'};
char[] hexValue = new char[2] { '\u0041', '\u0041' };
StringBuilder builder = new StringBuilder (10 * numTimes);
for (int i = 0; i < numTimes; ++i)
{
if (!unicode)
{
builder.Append (ansiValues1252);
}
else
{
builder.Append (unicodeValues);
}
builder.Append (hexValue);
int k = (int) hexValue[0];
int l = (int) hexValue[1];
if (k == 0x50)
{
k = 0x41;
if (l == 0x50)
{
l = 0x41;
}
else
{
++l;
}
}
else
{
++k;
}
hexValue[0] = (char) k;
hexValue[1] = (char) l;
}
return builder.ToString ();
}
private void OutputWindow_TextChanged(object sender, System.EventArgs e)
{
}
private void DoneButton_Click(object sender, System.EventArgs e)
{
this.Close();
}
private void StartStream_Click(object sender, System.EventArgs e)
{
// New String Writer for the output generated
StringWriter writer = new StringWriter();
// Create and Open a connection
PsqlConnection scon = new PsqlConnection("Data Source=DEMODATA");;
scon.Open();
try
{
// Clear the OutputWindow
OutputWindow.Clear();
// Local Variables
string lineCmp = "";
string lineOut = "";
int countText = 0;
bool status = true;
string outText = "..\\..\\outClob.txt";
string outImage = "..\\..\\outBlob.jpg";
bool ifFile = false;
int BUF_SIZE = 100000;
char[] bufferText = new char[BUF_SIZE];
byte[] bufferImage = new byte[BUF_SIZE];
int charsRead = 0;
int bytesRead = 0;
int InChunk;
// Create Input stream objects
string textFile = "..\\..\\clob.txt";
string imageFile = "..\\..\\blob.jpg";
// CLOB input object
StreamReader sr = File.OpenText(textFile);
// BLOB input object
FileStream fs = File.OpenRead(imageFile);
// Create a command object
PsqlCommand scom;
try
{
scom = new PsqlCommand("drop table clobblobtest", scon);
scom.ExecuteNonQuery ();
}
catch
{
// Don't do anything. Exception is expected when the table
// does not exist.
}
try
{
scom = new PsqlCommand("create table clobblobtest (id int, textfile CLOB, imagefile BLOB)", scon);
scom.ExecuteNonQuery ();
writer.WriteLine("Table Created Successfully!!");
flush(writer);
}
catch (Exception ex)
{
// Display the exception
writer.WriteLine("CREATE TABLE failed");
writer.WriteLine(ex.Message);
flush(writer);
}
// Bind the parameters to the input stream objects
try
{
scom = new PsqlCommand("insert into clobblobtest (id, textfile, imagefile) values (?, ?, ?)", scon);
scom.Parameters.Add ("id", PsqlDbType.Integer).Value = 1;
scom.Parameters.Add ("textfile", PsqlDbType.LongVarChar).Value = sr;
scom.Parameters.Add ("imagefile", PsqlDbType.LongVarBinary).Value = fs;
scom.ExecuteNonQuery ();
scom.Parameters.Clear ();
writer.WriteLine("INSERT Successful!!");
flush(writer);
}
catch (Exception ex)
{
// Display the exception
writer.WriteLine("INSERT failed");
writer.WriteLine(ex.Message);
flush(writer);
}
// Close the stream objects
sr.Close ();
fs.Close ();
// Check for output file in the directory. If old files found,
// delete them.
ifFile = File.Exists(outText);
if ( ifFile )
{
File.Delete(outText);
}
ifFile = File.Exists(outImage);
if ( ifFile )
{
File.Delete(outImage);
}
// Create output files for dumping the data retrieved from Fetch
StreamWriter srout = File.CreateText(outText);
FileStream fsout = File.Create(outImage);
// Fetch LONG data
scom = new PsqlCommand("select * from clobblobtest where id = 1", scon);
PsqlDataReader sreader = scom.ExecuteReader ();
sreader.Read ();
do
{
// CLOB Data is the second column in the DataReader resultset
InChunk = (int) sreader.GetChars (1, charsRead, bufferText, 0, bufferText.Length);
if (InChunk != 0)
{
srout.Write (bufferText, 0, InChunk);
charsRead += InChunk;
}
} while (InChunk == BUF_SIZE);
do
{
// BLOB Data is the third column in the DataReader resultset
InChunk = (int) sreader.GetBytes (2, bytesRead, bufferImage, 0, bufferImage.Length);
if (InChunk != 0)
{
fsout.Write (bufferImage, 0, InChunk);
bytesRead += InChunk;
}
} while (InChunk == BUF_SIZE);
// Close the reader and the output files
sreader.Close();
srout.Close ();
fsout.Close ();
// Compare the two output files with the input files
// Input Text File
StreamReader tc = File.OpenText(textFile);
// Get the line count of input text file
while ( (lineCmp = tc.ReadLine()) != null )
{
countText++;
}
tc.Close();
// Compare BLOB
FileStream fsO = new FileStream(outImage, FileMode.Open, FileAccess.Read);
FileStream fsC = new FileStream(imageFile, FileMode.Open, FileAccess.Read);
BinaryReader otC = new BinaryReader(fsC);
BinaryReader otO = new BinaryReader(fsO);
for ( int k = 0; k < fsC.Length; k++ )
{
if ( otC.ReadByte() != otO.ReadByte() )
{
// Compare failed. No need to compare further.
writer.WriteLine("Error in fetching BLOB data to output file. Please check the output file " + outImage);
k = (int)fsC.Length;
status = false;
flush(writer);
}
}
if ( status)
{
try
{
otO.ReadByte();
writer.WriteLine("Error: This should have thrown an exception.");
writer.WriteLine("BLOB Fetch Data bigger than the expected data.");
flush(writer);
}
catch
{
// Do nothing
writer.WriteLine("Fetch BLOB data matched with the inserted data");
flush(writer);
}
}
// Close all the files
otC.Close();
fsC.Close();
otO.Close();
fsO.Close();
// Check the status of the compare
if ( status )
{
// Delete the output file...Clean Up
File.Delete(outImage);
}
// Compare CLOB
status = true;
StreamReader tc1 = File.OpenText(textFile);
StreamReader to = File.OpenText(outText);
for ( int j = 0; j < countText; j++ )
{
lineCmp = tc1.ReadLine();
lineOut = to.ReadLine();
if ( !(String.Equals(lineCmp, lineOut)) )
{
// Compare failed. No need to compare further.
j = countText;
writer.WriteLine("Error in fetching CLOB data to output file. Please check the output file " + outText);
flush(writer);
status = false;
}
}
// Close all the files
tc1.Close();
to.Close();
// Check the status of compare
if ( status )
{
writer.WriteLine("Fetch CLOB data matched with the inserted data");
flush(writer);
}
// Delete the output file...Clean Up
File.Delete(outText);
}
catch (Exception ex)
{
