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

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.
LVL 1
tuckAsked:
Who is Participating?
 
moduloCommented:
PAQed, with points refunded (500)

modulo
Community Support Moderator
0
 
tovvenkiCommented:
Hi,
did you tried using OleDbType.VarBinary like
addMSG.Parameters.Add("MAPXML", OleDbType.VarBinary,msg.length -1).Value  

regards,
venki
0
 
tuckAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.