Oracle Lob (blob) Read / Write woes

microsoft.public.dotnet.framework.adonet

29 Mar 2006 1:13 PM

_BigDawg_

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.

--
JonSteng ~ Making Magic in Sunny Central Florida ~

29 Mar 2006 2:10 PM

MrSmersh

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();

Show quote

"_BigDawg_" wrote:

> 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.
>
> --
> JonSteng ~ Making Magic in Sunny Central Florida ~

29 Mar 2006 2:33 PM

Paul Clement

On Wed, 29 Mar 2006 05:13:03 -0800, _BigDawg_ <BigD***@discussions.microsoft.com> wrote:

¤ 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)

29 Mar 2006 7:29 PM

_BigDawg_

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.