Steven Swafford: Overworked and Underpaid

A friend of mind brought up the idea of getting the upload file's checksum. Here is the VB.NET code he provided.

In the UploadButton_Click:
<code>
Byte[] ImageContent = new byte[imgLength];
int intStatus;
int Status = imgStream.Read(ImageContent, 0, imgLength);

Dim myChecksum as string = Me.GetMD5Hash(ImageContent)
// provides a string like "9B-08-7E-2C-6F-DF-4C-EE-B9-7D-BF-47-D4-32-45-79"

// define the sql to perform the database insert
sqlStmt = "INSERT INTO smstestblob (id, photo, author," + "description ) VALUES (smstestblobid_seq.nextval, :1, :2, :3)";
<code>

Separate function so can be called from wherever:
Private Function GetMD5Hash(ByVal RawData() As Byte) As String
Dim myMD5Provider As New System.Security.Cryptography.MD5CryptoServiceProvider
Dim myMD5Hash() As Byte
' i subtract 1 from the length as this is an array that base 0
myMD5Hash = myMD5Provider.ComputeHash(RawData, 0, RawData.Length - 1)
GetMD5Hash = BitConverter.ToString(myMD5Hash)
End Function

As well I created a C# version:

In the UploadButton_Click:
GetCheckSum(ImageContent);

Public static string GetCheckSum(byte[] ImageContent)
{
MD5 md5 = new MD5CryptoServiceProvider();
byte[] hash = md5.ComputeHash(ImageContent);
string checkSum = BitConverter.ToString(hash);
return checkSum;
}

The following question was presented to me.

Regarding the following line of code,

sqlStmt = "INSERT INTO smstestblob (id, photo, author," +
"description ) VALUES (smstestblobid_seq.nextval, :1, :2, :3)";

What do the :1, :2, etc represent? When I am executing the SQL I receive an ORA-12704 Char Set Mismatch error.

Well the short answer I provided is these are nothing more than placeholders. I did want to provide a more in depth answer so I turned to an outstanding DBA named Roger Rowe and here is what he had to say.

Suggestion: Don't use numbers as parameter names, use useful names that make sense. I have had problems when these names didn't match the parameter names in procedures. Also, I am not sure, but I believe that oracle has a requirement of variables beginning with alpha characters (I've never seen numbers used in examples from oracle):

sqlStmt = "INSERT INTO smstestblob (id, photo, author,description ) VALUES (smstestblobid_seq.nextval, :pphoto, :pauthor, :pdescription )";

OracleParameter paramImage = new OracleParameter("pphoto", OracleDbType.Blob);
OracleParameter paramAuthor = new OracleParameter("pauthor",OracleDbType.Varchar2, 100);
OracleParameter paramDescription = new OracleParameter("pdescription ", OracleDbType.Varchar2, 500);

TIP: Keep the oracle SQL code (where possible) in packages/procedures so that the oracle dba can tweak the SQL for performance. Also, can assist in problem resolution if all the DBA needs is the parameter values passed.

TIP: Primary Keys should be generated from a database trigger and not rely upon the interface to generate them (especially when they are from a sequence).

However, the email from Chris does not really refer to the question posed. The "ORA-12704" points me to a language difference between the database and the client. This is what may be referred to as the globalization settings (e.g., NLS_LANG) in the ODP.Net documentation.

Suggestion regarding the "ORA-12704" issue identified below:

Remove "not null" requirements on the "smstestblob" table columns (especially the blob column) - if they exist.
Run the code and see if you can get the following SQL to work (i.e., I take out the parameters and add them one at a time):

sqlStmt = "INSERT INTO smstestblob (id, photo, author,description ) VALUES (smstestblobid_seq.nextval, null, :pauthor, :pdescription )";

At this point it becomes a matter of resolving byte translations (i.e., NLS_LANG or OracleDbType value selection).

what abouth writing it to an image control and have ather tings on the page

Read and Write BLOB Data to a Database Table with ODP.NET Trackback