Oracle Lob (blob) Read / Write woes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

VS 2005, VB, .Net Frameword 2.0, Oracle 9i Database, System.Data.OracleClient

I am trying to insert documents (word, pdf, images) into a BLOB data column
in an Oracle 9i database using the System.Data.OracleClient. I have
permissions on the database and the connection works to retrieve records.

There appears to be very little help or documentation for using the
System.Data.OracleClient in Visual Basic 2005 so I took a C# example from
(.NET Framework Developer's Guide / Working with Oracle LOBs) and converted
it to VB.

I am getting stumped by the CreateTempLob function with an Oracle error
(#01036: illegal variable name/number) when trying to execute the
blobCmd.ExecuteNonQuery. Ugh.

My code is below, again converted from C# to VB. Any suggestions greatly
appreciated and eagerly attempted. Thank you.

***** BEGIN CODE CLASS *****

Imports System
Imports System.Data
Imports System.Data.OracleClient
Imports System.Windows.Forms
Imports System.IO
Imports System.Collections.Generic

Public Class BlobImports

Public Sub DoBlob(ByVal blobPath As String, ByVal blobFilename As String,
ByVal blobID As String)

'create connection
Dim cnBlob As New OracleClient.OracleConnection("Data
Source=database;Persist Security Info=True;User
ID=user;Password=pword;Unicode=True")
Try
With cnBlob
' open connection
.Open()

'create command object
Dim cmdBlob As OracleClient.OracleCommand = cnBlob.CreateCommand()

'select text
cmdBlob.CommandText = "SELECT BLOB_ID, BLOB_DESC FROM BLOBS WHERE
BLOB_DESC = :pBlobDesc"
'add parameter
cmdBlob.Parameters.Add(":pBlobDesc", OracleType.VarChar)
cmdBlob.Parameters(0).Value = blobFilename

'read records
ReadBlobRecords(cmdBlob)

'update command text for update
cmdBlob.CommandText = _
"SELECT BLOB_DESC, BLOB FROM BLOBS WHERE BLOB_DESC = :pBlobDesc
FOR UPDATE"

'update record with blob item - causes error in createtempblob
function
WriteBlob(cmdBlob, blobPath, blobFilename)

End With
Catch ex As Exception
MsgBox(ex.ToString & vbCrLf & ex.Message, MsgBoxStyle.OkOnly, "Foo")
Finally
cnBlob.Close()
End Try
End Sub

Private Sub ReadBlobRecords(ByVal blobCommand As OracleClient.OracleCommand)
Dim blobTrans As OracleClient.OracleTransaction =
blobCommand.Connection.BeginTransaction
blobCommand.Transaction = blobTrans
Dim blobReader As OracleClient.OracleDataReader =
blobCommand.ExecuteReader()
While blobReader.Read
lbBlobs.Items.Add(blobReader.GetOracleString(1))
End While
blobTrans.Commit()
End Sub

Private Sub WriteBlob(ByVal blobCommand As OracleClient.OracleCommand,
ByVal blobPath As String, ByVal blobName As String)

'read file into binary
Dim fi As New FileInfo(blobpath & "\" & blobname)
Dim sr As New StreamReader(fi.FullName)
Dim tempBuffer As String = sr.ReadToEnd
sr.Close()

'begin transaction to post blobs
Dim blobTrans As OracleClient.OracleTransaction =
blobCommand.Connection.BeginTransaction
blobCommand.Transaction = blobTrans
Dim blobReader As OracleClient.OracleDataReader =
blobCommand.ExecuteReader
While blobReader.Read
Dim oBlobFile As OracleClient.OracleLob = blobReader.GetOracleLob(1)
' call to create temp blob throws oracle error
Dim inBlobFile As OracleClient.OracleLob = CreateTempLob(blobCommand,
oBlobFile.LobType)
Dim lngBlob As Long = oBlobFile.CopyTo(inBlobFile)
blobTrans.Commit()
End While

End Sub

Private Function CreateTempLob(ByVal blobCmd As
OracleClient.OracleCommand, ByVal blobType As OracleClient.OracleType)
blobCmd.CommandText = _
"DECLARE A " & blobType & "; BEGIN DBMS_LOB.CREATETEMPORARY(A, FALSE);
:tmpBlob = A;"
Dim pBlobTemp As OracleParameter = blobCmd.Parameters.Add("tmpBlob",
blobType)
''pBlobTemp.Value = Nothing
pBlobTemp.Direction = ParameterDirection.Output
' here is where the error is occuring
blobCmd.ExecuteNonQuery()
Return pBlobTemp.Value
End Function

***** END CODE CLASS *****

Thank you again for assistance.
 
A C# pseudocode oriented of how to store blobs on Oracle
Hope is good enough for you if not ask for more ;)
//method params
string tableName,
string columnContainingBlobName,
string whereClause,
byte[] blobValue)
/////////////////////////////////

string SelectString = null;

SelectString=String.Format(_cultureInfoInvariant,"UPDATE {0} SET {1} =
:blobdata {2} ",tableName,columnContainingBlobName,whereClause);

DbCommand Command = CreateCommand(…);
Command.CommandText = SelectString;
Command.Connection = _Connection;
Command.Transaction = _Transaction;
Command.CommandTimeout=CommandTimeout;

DataParameter dataParameter = CreateDBDataParameter(…);



dataParameter.ParameterName = "blobdata";
dataParameter.DbType = System.Data.DbType.Binary;
dataParameter.Direction=ParameterDirection.Input;

if (blobValue==null)
{
dataParameter.Value = DBNull.Value;
}
else
{
dataParameter.Value = blobValue;
dataParameter.Size=blobValue.Length;
}

Command.Parameters.Add(dataParameter);

Result=Command.ExecuteNonQuery();
 
¤ VS 2005, VB, .Net Frameword 2.0, Oracle 9i Database, System.Data.OracleClient
¤
¤ I am trying to insert documents (word, pdf, images) into a BLOB data column
¤ in an Oracle 9i database using the System.Data.OracleClient. I have
¤ permissions on the database and the connection works to retrieve records.
¤
¤ There appears to be very little help or documentation for using the
¤ System.Data.OracleClient in Visual Basic 2005 so I took a C# example from
¤ (.NET Framework Developer's Guide / Working with Oracle LOBs) and converted
¤ it to VB.
¤
¤ I am getting stumped by the CreateTempLob function with an Oracle error
¤ (#01036: illegal variable name/number) when trying to execute the
¤ blobCmd.ExecuteNonQuery. Ugh.
¤
¤ My code is below, again converted from C# to VB. Any suggestions greatly
¤ appreciated and eagerly attempted. Thank you.
¤
<snip>

¤ Private Function CreateTempLob(ByVal blobCmd As
¤ OracleClient.OracleCommand, ByVal blobType As OracleClient.OracleType)
¤ blobCmd.CommandText = _
¤ "DECLARE A " & blobType & "; BEGIN DBMS_LOB.CREATETEMPORARY(A, FALSE);
¤ :tmpBlob = A;"
¤ Dim pBlobTemp As OracleParameter = blobCmd.Parameters.Add("tmpBlob",
¤ blobType)
¤ ''pBlobTemp.Value = Nothing
¤ pBlobTemp.Direction = ParameterDirection.Output
¤ ' here is where the error is occuring
¤ blobCmd.ExecuteNonQuery()
¤ Return pBlobTemp.Value
¤ End Function

I don't see and END statement in your Oracle CommandText. Also you probably need to change the
tmpBlob assignment operator. Below is an example:

declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Thank you for the replies - both were very helpful. What I ended up doing was
creating a stroed procedure to do the actual database insert / update. My
updated code is below, if anyone is interested.

Thanks again, much appreciated.
--
JonSteng ~ Making Magic in Sunny Central Florida ~

***** BEGIN CODE BLOCK *****

Imports System
Imports System.Data
Imports System.Data.OracleClient
Imports System.Windows.Forms
Imports System.IO
Imports System.Collections.Generic


Public Class frmBlobs

Private Sub cmdBrowse_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdBrowse.Click
Dim folderPath As String
Dim blobName As String
Dim blobID As String
'call folder browse dialog
Dim result As DialogResult = fbdBlob.ShowDialog
'get folder information if selected
If (result = Windows.Forms.DialogResult.OK) Then
folderPath = fbdBlob.SelectedPath
If (Not folderPath = "") Then
Try
lblBlobDir.Text = folderPath
Dim di As New System.IO.DirectoryInfo(folderPath)
Dim fi As System.IO.FileInfo() = di.GetFiles()
' retrieve the names of the files in the current directory
' and call the insert blob procedure to load to database
Dim fiBlob As System.IO.FileInfo
For Each fiBlob In fi
blobName = fiBlob.Name
blobID = blobName.Substring(0, 11)
lbBlob.Items.Add(blobName & " - [" & blobID & "]")
Insert_Blob(folderPath, blobName)
Next fiBlob
Catch ex As Exception
MsgBox(ex.ToString & vbCrLf & vbCrLf & ex.Message,
MsgBoxStyle.OkOnly, "Error retrieving files")
End Try
End If 'If (Not folderPath = "")
End If '(result = Windows.Forms.DialogResult.OK)
End Sub

Private Sub Insert_Blob(ByVal blobPath As String, ByVal blobFilename As
String)

'read file into binary
Dim blobBuffer As New System.IO.FileStream(blobPath & "\" &
blobFilename, FileMode.OpenOrCreate, FileAccess.Read)
Dim blobData(blobBuffer.Length) As Byte
blobBuffer.Read(blobData, 0, blobBuffer.Length)
'create and open connection
Dim bConn As OracleConnection = New OracleConnection("Data
Source=database;Persist Security Info=True;User
ID=userid;Password=password;Unicode=True")
bConn.Open()
'create transaction and command / begin transaction
Dim bTrans As OracleTransaction = bConn.BeginTransaction
Dim bCmd As OracleCommand = bConn.CreateCommand
bCmd.Transaction = bTrans
Try
'create temporary blob with blob parameter
bCmd.CommandText = "DECLARE Ax blob; BEGIN
DBMS_LOB.CREATETEMPORARY(Ax, FALSE, 0); :tmpBlob := Ax; END;"
bCmd.Parameters.Add(New OracleParameter("tmpBlob",
OracleType.Blob)).Direction = ParameterDirection.Output
bCmd.ExecuteNonQuery()
'create temp lob and assign to temp blob value
Dim tempLob As OracleLob = bCmd.Parameters(0).Value
'assign byte array to temp lob object
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite)
tempLob.Write(blobData, 0, blobBuffer.Length)
tempLob.EndBatch()
'clear parameters from command and run stored procedure
bCmd.Parameters.Clear()
bCmd.CommandText = "INSERT_BLOB"
bCmd.CommandType = CommandType.StoredProcedure
'add required paramters for stroed procedure and execute / commit
bCmd.Parameters.Add(New OracleParameter("v_update_table",
OracleType.VarChar)).Value = "BLOBS"
bCmd.Parameters.Add(New OracleParameter("v_key_in",
OracleType.VarChar)).Value = blobFilename
bCmd.Parameters.Add(New OracleParameter("v_lob_in",
OracleType.Blob)).Value = tempLob
bCmd.Parameters.Add(New OracleParameter("v_user",
OracleType.VarChar)).Value = "IMA_USER"
bCmd.ExecuteNonQuery()
bTrans.Commit()
Catch ex As Exception
MsgBox(ex.ToString & vbCrLf & vbCrLf & ex.Message, MsgBoxStyle.OkOnly,
"Error importing files")
bTrans.Rollback()
Finally
bTrans.Dispose()
bConn.Close()
End Try
End Sub

End Class

***** BEGIN CODE BLOCK *****

Now I just need to get them out and let the user read them.
 
Back
Top