Solved

Inserting a blob into an oracle database using OLEDB provider fails when file gets larger

Posted on 2004-08-01
4
653 Views
Last Modified: 2012-08-13
Hi,

I have
msg = "A large string from a text file"

        Dim hl7msg(msg.Length - 1) As Byte
        hl7msg = Encoding.ASCII.GetBytes(msg)
        Dim addMSG As OleDbCommand = New OleDbCommand _
        ("INSERT INTO PFCTINT.PFMAP(MAPNAME, MAPXML,CREATEDATE,EDITDATE,CREATEDBY, STATUS, COMMENTS ) VALUES (?,?,?,?,?,?,?)", dbcon)
        Try
            addMSG.Parameters.Add("MAPNAME", OleDbType.VarChar, 255).Value = getXMLMessage(_fileDetail, "MAPNAME")
            addMSG.Parameters.Add("MAPXML", OleDbType.LongVarBinary).Value = hl7msg
            addMSG.Parameters.Add("CREATEDATE", OleDbType.Date, 255).Value = Date.Parse(getXMLMessage(_fileDetail, "MAPDATE"), ci.DateTimeFormat)
            addMSG.Parameters.Add("EDITDATE", OleDbType.Date, 255).Value = Date.Parse(getXMLMessage(_fileDetail, "MAPDATE"), ci.DateTimeFormat)
            addMSG.Parameters.Add("CREATEDBY", OleDbType.VarChar, 255).Value = getXMLMessage(_fileDetail, "CREATEDBY")
            addMSG.Parameters.Add("STATUS", OleDbType.Integer, 255).Value = 1
            addMSG.Parameters.Add("COMMENTS", OleDbType.VarChar, 255).Value = getXMLMessage(_fileDetail, "MAPDESCRIPTION")

            addMSG.ExecuteNonQuery()
            addMSG = Nothing
            WriteBlob = True
        Catch ex As Exception
            MsgBox("Error occurred(Redundancy(WriteBlob)) " & vbCrLf & ex.Message.ToString)
            WriteBlob = False

Now when this executes the query, it falls over, if I remove a line from my msg it works fine,  what I cant work out it that the msg string is not so large 4114 charcters in length.

Even if I change the line  addMSG.Parameters.Add("MAPXML", OleDbType.LongVarBinary).Value = hl7msg to  addMSG.Parameters.Add("MAPXML", OleDbType.LongVarBinary,msg.length -1).Value   ie to include the length it produces the same result.    If I shrink the file to around 2999 charcters it works.

THe error returned is an end of file on communication channel.

Any ideas.

Thanks.
0
Comment
Question by:tuck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 21

Expert Comment

by:tovvenki
ID: 11691335
Hi,
did you tried using OleDbType.VarBinary like
addMSG.Parameters.Add("MAPXML", OleDbType.VarBinary,msg.length -1).Value  

regards,
venki
0
 
LVL 1

Author Comment

by:tuck
ID: 11700141
HI tovvenki,

Yes I tried that, but the same result happens.  Thanks for that.

I sort have worked around it by pushing it through a dataset, but I had to change the oracle datatype to long raw as BLOBS are not supported by datasets. directly.

Will keep this open for a bit, and see...

Cheers
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 11927443
PAQed, with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Modal Popup Extender control 1 51
Winform Module - What is the ASP.Net equiv 2 39
Filtering a datagrid view 8 34
VB.Net  Exporting Excel Woorkbook to PDF 7 59
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question