I'll start by saying what I need to achieve and then explain how I'm currently going about doing it and the problem I'm having.
I'm replacing a VB application which currently has an oracle connection. The application allows the user to upload an image (amongst other things) which is then stored as a blob in a oracle table. However for reasons that are too confusing to explain we can't use the oracle connection any longer. So what I decided to do is call a SQL server stored procedure from the VB application which passes the image as a varbinary(max) parameter. Within the SQL Server stored procedure I am then updating the original Oracle table using OPENQUERY.
The connections between VB to SQL Server and then oracle work fine and the oracle table is being updated. However the blob value that is being inserted is completely wrong. The blob values currently in the table inserted from the current oracle version of the application are roughly 4,500 charaters long and seem to be a hex value. However the value that is being inserted from the new SQL SERVER application is just a integer and it is exactly the same length. I know this value is wrong because I have a viewer in the application that displays the image. The images from the current oracle application display fine but any images from the new application result in an error. I can't change the way the image is displayed since the blob value feeds into other applications which also display the image.
I intercepted the SQL SERVER stored procedure command that is being sent to Oracle when the stored procedure is being executed and the blob value is different to what is saved in the table. It is roughly 44,000 characters long and is a hex value, but the value saved in the table is just a integer as is the same lenght as the correct values in the table.
I'm also not sure what part is going wrong. Is the long hex value that is being sent to SQL server wrong so my VB code needs changing? or is the SQL SERVER code that is passing the value to oracle wrong? Or is Oracle handing the value wrong and updating it with a wrong type?
Here is the related code:
VB SQL SERVER SP CALL:
Code:
Dim bPhoto As Byte()
Dim br As BinaryReader = New BinaryReader(System.IO.File.OpenRead(strFilePath))
bPhoto = br.ReadBytes(br.BaseStream.Length)
Dim myConnection As New SqlConnection(ConfigurationManager.AppSettings("app_vwarehouse_smartcard"))
Dim cmdExists As SqlCommand
cmdExists = New SqlCommand("[OC].CardReaderStaffBlobDetails", myConnection)
cmdExists.CommandType = CommandType.StoredProcedure
cmdExists.Parameters.AddWithValue("@Percode", strPercode)
cmdExists.Parameters.AddWithValue("@FileType", strFileType)
cmdExists.Parameters.AddWithValue("@CreatedBy", Environment.UserName.ToString)
cmdExists.Parameters.Add("@BlobValuelDbType.VarBinary).Value = bPhoto
'Dim blobImageParameter As New SqlParameter("BlobValue", SqlDbType.VarBinary)
'blobImageParameter.Value = bPhoto
'blobImageParameter.Direction = ParameterDirection.Input
'cmdExists.Parameters.AddWithValue("@BlobValue", bPhoto) myConnection.Open()
cmdExists.ExecuteNonQuery()
myConnection.Close()
GetPhoto()
I know the bphoto part is correct since I haven't changed that at all from the oracle version.
SQL SERVER Updating Oracle:
Code:
SET @SQL = N'UPDATE OPENQUERY(EBSLIVE, '' SELECT BINARY_OBJECT
FROM BLOBS
WHERE DOMAIN = ''''PEOPLE''''
AND BLOB_NAME = ''''PERSON_PICTURE''''
AND OWNER_REF= ' + CAST(@Percode AS varchar(10)) + ''')
SET BINARY_OBJECT = ''' + CONVERT(varchar(max), @BlobValue, 2) + ''''
EXEC (@SQL)
This is the actual string that is being executed against oracle:
Code:
UPDATE OPENQUERY(EBSLIVE, ' SELECT BINARY_OBJECT
FROM BLOBS
WHERE DOMAIN = ''PEOPLE''
AND BLOB_NAME = ''PERSON_PICTURE''
AND OWNER_REF= 490615')
SET BINARY_OBJECT = <45000 characters here>