Solved

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

Posted on 2004-08-01
4
655 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

710 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