Problem with BLOB

[handsomed]

How could insert in Oracle DB one BLOB object with a given name from a Vb .NET form?

Currently the BLOB object is stored but i'd like to store it with a name

any ideas? Thanks

 

[MattP]

Any reason you can't store this information in additional columns?

Here's a method that I'm using where I'm inserting a document name, mime type, size in bytes, blob, file creation time, and file upload time. You should be able to easily change it for Oracle and the column data you'd like to provide.

The only thing not readily evident is that MimeType is a Dictionary.

Dim MimeType As New Dictionary(Of String, String)
MimeType.Add(".txt", "text/plain")
...

    Private Sub File2SqlBlob(ByVal FileInformation As FileInfo)
        Dim cn As New SqlConnection(My.Settings.DocStoreConnectionString.ToString())
        Dim cmd As New SqlCommand("INSERT INTO Documents (DocName, DocContentType, DocSize, DocData, CreateDate, UploadDate)" & _
            "VALUES (@DocName, @DocContentType, @DocSize, @DocData, @CreateDate, @UploadDate)", cn)

        Dim fs As FileStream = New FileStream(FileInformation.FullName, FileMode.Open, FileAccess.Read)
        Dim b(fs.Length - 1) As Byte
        fs.Read(b, 0, b.Length)
        fs.Close()

        cmd.Parameters.AddWithValue("@DocName", FileInformation.Name)
        cmd.Parameters.AddWithValue("@DocContentType", MimeType(FileInformation.Extension))
        cmd.Parameters.AddWithValue("@DocSize", b.Length)
        cmd.Parameters.AddWithValue("@DocData", b)
        cmd.Parameters.AddWithValue("@CreateDate", FileInformation.CreationTime)
        cmd.Parameters.AddWithValue("@UploadDate", Date.Now)

        cn.Open()
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub

 

[simonyong]

Hello, MattP

May i ask where to get sqlcommand?
after i write :
dim cmd as new sqlcommand

blue line will be appeared at the bottom of sqlcommand
state that 'type sqlcommand is not defined'

sorry for asking stupid question, im newbie to .net

* im using 1.1 framework with microsoft visual studio .NET 2003

 

[MattP]

sorry for asking stupid question, im newbie to .net

This is a learning environment. Nobody will give you a hard time for asking beginner questions though you may be directed to use the forum search feature if it is a topic that is covered regularly.

SqlCommand is part of the System.Data.SqlClient {} namespace.

To avoid any potential confusion my answer was using code Microsoft SQL Server and not Oracle.

I'm not familiar with the imports for Oracle but a quick Google search shows adding a reference to Oracle.DataAccess.dll and then import statements for System.Data and Oracle.DataAccess.Client.

cjard regularly frequents these boards and should be able to give you further information about all things Oracle.

 

[simonyong]

Thanks, MattP
u giv me a clear idea..
thanks a lots..

 

[cjard]

i'm not 100% clear on what you mean by naming your blob.
If your blob is a picture of someone's cat, each record being a different cat, then you would call your blob column CAT_PICTURE
If you also wanted to record that the cat's name was Jess, you'd have a VARCHAR2 column called CAT_NAME and store "Jess" in it

Oracle Data Access isnt 100% necessary because the .net framework includes System.Data.OracleClient which is MS' provider. It's slower for some things but mostly people get on fine with it. To read and write blobs, you can search this forum for help i've given others.. It comes up reasonably often as a question