Results 1 to 13 of 13

Thread: VB application Uploading files(Blobs) into Oracle

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jun 2017
    Posts
    27

    VB application Uploading files(Blobs) into Oracle

    I have a simple application that uploads text files into oracle as blobs.

    I just noticed that if the text has french accent characters in it then when I upload the blobs those characters get corrupt.

    How to I go about adding UTL 8 encoding when I am uploading a blob.
    Last edited by jr_greatwhite; Jan 13th, 2021 at 04:12 PM.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    Re: VB application Uploading files(Blobs) into Oracle

    IF they are true blobs, then they are binary and it shouldn't matter, as it's binary... if you're storing them as clobs, which is the text version of blobs... then it's up to the encoding of the database... and that's a bit more difficult to deal with as that has to be set at the time the database is created (been there, done that, have a couple of those t-shirts).

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jun 2017
    Posts
    27

    Re: VB application Uploading files(Blobs) into Oracle

    I just checked the database and the field is a BLOB. The files we are uploading are text files in to Oracle.
    When we upload them into the database the french characters are being corrupted.

    Am I suppose to set something in VB to turn on encoding?

    I also doubled check the Encoding for the database and it appears correct.

    NLS_CHARACTERSET AL32UTF8 0
    NLS_NCHAR_CHARACTERSET AL16UTF16 0

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    Re: VB application Uploading files(Blobs) into Oracle

    Maybe it's not the DB but rather the reading of the file in the first place?


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: VB application Uploading files(Blobs) into Oracle

    If you're using code to do something and it doesn't work, there's a fair chance that the code is wrong. You should show us the code and we shouldn't have to ask.

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Jun 2017
    Posts
    27

    Re: VB application Uploading files(Blobs) into Oracle

    Sorry I will post the code. At first I thought it might be a simple question.

    conn.Open()


    Dim command1 As New OracleCommand(SQLStr, conn)

    Try

    'conn.Open()
    If conn.State = ConnectionState.Open Then
    Console.WriteLine("Connected to database!")


    ' provide read access to the file
    Dim Fs As FileStream = New FileStream(SourceLoc,
    FileMode.Open, FileAccess.Read)

    ' Create a byte array of file stream length
    Dim ImageData As Byte()
    ReDim ImageData(Fs.Length)

    'Read block of bytes from stream into the byte array
    Fs.Read(ImageData, 0, System.Convert.ToInt32(Fs.Length))

    'Close the File Stream
    Fs.Close()


    SQLStr = SQLStr & " Update " & TableName & " Set BIN_DATA = :1 where FILENAME = :2"


    ' Set command to create Anonymous PL/SQL Block

    Dim command As New OracleCommand() '(StrSQL, conn)

    command.CommandText = SQLStr
    command.Connection = conn
    ' Since executing an anonymous PL/SQL block, setting the command type
    ' as Text instead of StoredProcedure
    command.CommandType = CommandType.Text

    Dim param1 As OracleParameter = command.Parameters.Add("BIN_DATA", OracleDbType.Blob) 'BIN_DATA (BLOB)
    param1.Direction = ParameterDirection.Input
    ' Assign Byte Array to Oracle Parameter
    param1.Value = ImageData

    Dim param2 As OracleParameter = command.Parameters.Add("FILENAME", OracleDbType.Varchar2) 'FILENAME
    param2.Size = 50
    param2.Direction = ParameterDirection.Input
    ' Assign Byte Array to Oracle Parameter
    param2.Value = File



    command.ExecuteNonQuery()
    command.Dispose()

    'Console.WriteLine("Image file inserted to database from " + SourceLoc)
    lblValidateSuccessfull.Text = "File has been uploaded to " & RBOracleServer.SelectedValue & " database. "
    'Next

    End If
    Catch ex As Exception
    Console.WriteLine(ex.ToString)
    End Try

    Quote Originally Posted by jmcilhinney View Post
    If you're using code to do something and it doesn't work, there's a fair chance that the code is wrong. You should show us the code and we shouldn't have to ask.

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: VB application Uploading files(Blobs) into Oracle

    Please don't post unformatted code. It is unnecessarily hard to read, mainly due to a lack of indenting.
    Code:
            conn.Open()
    
    
            Dim command1 As New OracleCommand(SQLStr, conn)
    
            Try
    
                'conn.Open()
                If conn.State = ConnectionState.Open Then
                        Console.WriteLine("Connected to database!")
    
    
                    ' provide read access to the file
                    Dim Fs As FileStream = New FileStream(SourceLoc,
                                                   FileMode.Open, FileAccess.Read)
    
                        ' Create a byte array of file stream length
                        Dim ImageData As Byte()
                        ReDim ImageData(Fs.Length)
    
                        'Read block of bytes from stream into the byte array
                        Fs.Read(ImageData, 0, System.Convert.ToInt32(Fs.Length))
    
                        'Close the File Stream
                        Fs.Close()
    
    
                    SQLStr = SQLStr & " Update " & TableName & " Set BIN_DATA = :1 where FILENAME = :2"
    
    
                    ' Set command to create Anonymous PL/SQL Block
    
                    Dim command As New OracleCommand() '(StrSQL, conn)
    
                        command.CommandText = SQLStr
                        command.Connection = conn
                    ' Since executing an anonymous PL/SQL block, setting the command type
                    ' as Text instead of StoredProcedure
                    command.CommandType = CommandType.Text
    
                    Dim param1 As OracleParameter = command.Parameters.Add("BIN_DATA", OracleDbType.Blob) 'BIN_DATA (BLOB)
                    param1.Direction = ParameterDirection.Input
                    ' Assign Byte Array to Oracle Parameter
                    param1.Value = ImageData
    
                    Dim param2 As OracleParameter = command.Parameters.Add("FILENAME", OracleDbType.Varchar2) 'FILENAME
                    param2.Size = 50
                    param2.Direction = ParameterDirection.Input
                    ' Assign Byte Array to Oracle Parameter
                    param2.Value = File
    
    
    
                    command.ExecuteNonQuery()
                        command.Dispose()
    
                        'Console.WriteLine("Image file inserted to database from " + SourceLoc)
                        lblValidateSuccessfull.Text = "File has been uploaded to " & RBOracleServer.SelectedValue & " database. "
                        'Next
    
                    End If
                Catch ex As Exception
                    Console.WriteLine(ex.ToString)
                End Try

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Jun 2017
    Posts
    27

    Re: VB application Uploading files(Blobs) into Oracle

    Sorry I did not realize there was a way to format the code on here.

    Well thanks for your help everyone. Seems everyone is more interested in the mistakes I made posting.

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: VB application Uploading files(Blobs) into Oracle

    Quote Originally Posted by jr_greatwhite View Post
    Well thanks for your help everyone. Seems everyone is more interested in the mistakes I made posting.
    We spend quite a lot of time on here, volunteering our time to help people like you. Do you think that we should not point out your mistakes and let you keep making them, thus wasting some of that time? You think that we should help you but you shouldn't help us to do so? That doesn't inspire me all that much.

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Jun 2017
    Posts
    27

    Re: VB application Uploading files(Blobs) into Oracle

    Look Sir,

    I offer help to people in other types of work but have never critiqued them on the way they asked. I'm sorry I made the mistakes but that does seem to be the issue everyone wants to dwell on it. Whatever I will move on it is obvious that nobody was interested in helping.

    Thank you for your time

  11. #11
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,106

    Re: VB application Uploading files(Blobs) into Oracle

    Well, if they were, they certainly aren't now.

    Good luck.

  12. #12
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: VB application Uploading files(Blobs) into Oracle

    Quote Originally Posted by jr_greatwhite View Post
    it is obvious that nobody was interested in helping.
    That was false but I suspect that it's true now. I've certainly lost any interest I had, but I did have interest.

  13. #13
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    Re: VB application Uploading files(Blobs) into Oracle

    Well, it's the difference between readingaquesitonthatlookslikethis and a question that looks like this.

    It's a problem exists because people think it's just them. But it's not. It's a lot of people. We saw dozens of unformatted code yesterday, we'll see more today, tomorrow, the day after that.... and quite frankly, it gets harder each time... If indenting and formatting didn't matter, IDEs wouldn't do it. But it does matter... that's why Tab v Spaces and 2 v 4 spaces wars exist.... Otherise we'd all jsut write our code using minified, liner, non-spaced javascript.

    Indenting and formatting matters because it sets up mental blocks to those of us that have been doing this for a long time, it helps us find potential problem spots right from the get-go as it helps to break up the code and see logical points, groups the code together.

    That being said...

    I think it looks ok... how are you determining that it's being corrupted?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width