Problem with BLOB while using Oracle & VB.NET

sridhar79
sridhar79 used Ask the Experts™
on
Hi,
 I am trying to insert pdf's and zip files into oracle using vb.net But I keep getting this error which I cannot figure out. I declared my column variable that is to be inserted as file in Oracle as BLOB.

this is code I have written:

Dim file_path As String
       file_path = Me.txtDocFile.Text
       Dim db_file As FileStream
       db_file = New FileStream(file_path, FileMode.Open, FileAccess.Read)
       Dim file_data(db_file.Length() - 1) As Byte
       db_file.Read(file_data, 0, db_file.Length)

       Dim send_dat As String
       send_dat = "INSERT INTO PROJECT_FILES(PROJECT_NUMBER, DOC_TYPE, DETAIL_DESCRIPTION, FILE_DOC) VALUES ('" & _
                   Me.cmbProjectNumber.Text & "', '" & Me.cmbDocType.Text & "', '" & Me.txtDocDescription.Text & "', ?)"
       Dim cmdsend_dat As OleDb.OleDbCommand
       cmdsend_dat = New OleDb.OleDbCommand(send_dat, oleconn)
       Dim p As New OleDb.OleDbParameter("@FILE_DOC", OleDb.OleDbType.Binary, file_data.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, file_data)
       cmdsend_dat.Parameters.Add(p)
       cmdsend_dat.ExecuteNonQuery()
       db_file.Close()
   End Sub


this is the error i keep getting:


Unhandled Exception: System.Data.OleDb.OleDbException: ORA-01461: can bind a LONG value only for insert into a LONG column
  at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
  at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
  at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
  at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
  at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
  at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
  at Soil_Tests_Manager.frmImportFiles.cmdFileAdd_Click(Object sender, EventArgs e) in C:\WINNT\Desktop\Soil_Tests_Manager\Import Project Files.vb:line 543
  at System.Windows.Forms.Control.OnClick(EventArgs e)
  at System.Windows.Forms.Button.OnClick(EventArgs e)
  at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
  at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
  at System.Windows.Forms.Control.WndProc(Message& m)
  at System.Windows.Forms.ButtonBase.WndProc(Message& m)
  at System.Windows.Forms.Button.WndProc(Message& m)
  at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
  at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
  at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
  at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
  at System.Windows.Forms.ComponentManager.System.Windows.Forms.UnsafeNativeMethods+IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
  at System.Windows.Forms.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
  at System.Windows.Forms.Application.Run(Form mainForm)
  at Soil_Tests_Manager.frmMain.Main() in C:\WINNT\Desktop\Soil_Tests_Manager\SplashForm.vb:line 8

I am pretty much stuck with my work from going further. Any kind of help would be great.
Thanks,
Yash
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Doesn't this say it all?

Unhandled Exception: System.Data.OleDb.OleDbException: ORA-01461: can bind a LONG value only for insert into a LONG column

Looks like your data is LONG datatype but your column is BLOB.

try changing your column to a LONG datatype.

Total guess though.

Author

Commented:
Hi markag,
    I declared my datatype in vb.net as oledbdatatype's longvarbinary which is supposed to be a matching data type for oracle's blob datatype. But it's generating the error you saw before. I am a newbie to oracle. So do you think that blob is the correct datatype to store a file, say a pdf?
Thanks,
Yash

Author

Commented:
Hi markag,
    I declared my datatype in vb.net as oledbdatatype's longvarbinary which is supposed to be a matching data type for oracle's blob datatype. But it's generating the error you saw before. I am a newbie to oracle. So do you think that blob is the correct datatype to store a file, say a pdf?
Thanks,
Yash
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
All I can tell you is that I played with BLOB's and .PDF's  and .JPG's (reading from / writing to tables) in Oracle months ago using the OO4O and got it to work no prob on a BLOB column in Oracle, so it is possible.

Have you tried declaring the oledatatype as something *other than* longvarbinary that may possibly be more in tune with BLOB's? I am not familiar at all with that object so...can't help there.



Commented:
I'd try OleDb.OleDbType.VarBinary instead of OleDb.OleDbType.Binary in your OleDbParameter call.

Commented:
More info:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataOleDbOleDbTypeClassTopic.asp

You are going to want to use something that relates to stream of data, and not a value, as LongVarBinary is for a Long Value.

Your choices are:
Binary: A stream of binary data (DBTYPE_BYTES). This maps to an Array of type Byte.

VarBinary: A variable-length stream of binary data (OleDbParameter only). This maps to an Array of type Byte.

That's my best guess.
Helena Markováprogrammer-analyst

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Accept markag's comment as answer.

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Henka
EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial