Solved

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

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
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…

827 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