Hi,

I would like to insert an excel-file as a blob into a Oracle-table. The workflow is that the user would look up a contact and the add the file to that contact. For looking up the contact a SF business object is used. After the contact and file has been selected the blob insert could then be done in the code.

What would be the best way to do this? For me a straight INSERT SQL-statement would work fine in this, but how can I refer to the already existing SF-connection?
The language is VB.Net.

TIA,

Kari

If you create a business object and map it to the schema on the Oraxle database, you will see that the blob field will be typed in the BO as a Byte() array.  You can then open the file on disk, and read all of the bytes into the field property on the BO and it will take care of the rest as far as writing the data goes.

MyBO.MyBloblField = System.IO.File.ReadAllBytes("C:\MyExcelFile.xls")
MyBO.Save()

Trent,

ok, doing the MyBO approach resulted in a Oracle error ORA-000936

DataLayerSavingException
 ORA-00936: missing expression

OracleException
 ORA-00936: missing expression

Looking at the SQL issued it's this one that causes the error:

SELECT .NEXTVAL FROM DUAL

The Business Object (?) is trying to find the NEXTVAL of a sequence. Why, I don't know. Here is the code :

MyBO.NewRow()
MyBO.COMMUNICATION_INFO = fd.FileName
MyBO.COMMUNICATION_BLOB = System.IO.File.ReadAllBytes(fd.FileName)
MyBO.FILE_TYPE = "XLS"
MyBO.FILE_NAME = fd.FileName

MyBO.Save()

If the primary key is empty the database will populate that. In teh BO I have market every field to "Return Alternate on Null" and NULL replacement value to Nothing. I tryid also populating the tables primary-key but the result was the same.

What could cause the insert to fail...?

Kari

You may want to set the PrimaryKeyIsAutoIncrementing to False on the business object if the primary key is not be managed by the server or your primary key is not integer.  If this doesn't help, turn on debug mode to see what is coming and going to the server.  To do this, open your AppMain.vb or program.cs file and add this code below the SetConnections() call inside of the SetDataSources() method.

MicroFour.StrataFrame.Data.DataBasics.DataSources(0).SetDebugOn("c:\temp\mydebug.html",true)

Hi,

The PrimaryKeyIsAutoIncremented=FALSE helped and took the Oracle error away. Still some problems and I don't quite know what is wrong with the folllowing gode. The error is about "Object reference not set to an instance of an object." when it executes the MyBO.Save(). 

Dim MyBO As New CommunicationBO()

With MyBO
     .NewRow()
     .PrimaryKeyIsAutoIncremented = False
     .COMMUNICATION_BLOB = System.IO.File.ReadAllBytes(fd.FileName)
     .FILE_TYPE = "XLS"
     .FILE_NAME = fd.FileName
End With

MyBO.Save()

But why? Isn't the "Dim MyBO as New CommunicationBO()" enough to create the object or should there be more

Sorry to trouble you with this,

Kari

=== The full error stack  ===
DataLayerSavingException
 Object reference not set to an instance of an object.
NullReferenceException
 Object reference not set to an instance of an object.

Source     : MicroFour StrataFrame Business

Stack Trace:
   at MicroFour.StrataFrame.Data.OracleDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler, AddRowErrorHandler RowErrorHandler, Boolean RecreateCommand)
   at MicroFour.StrataFrame.Data.DbDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler, AddRowErrorHandler RowErrorHandler)
   at MicroFour.StrataFrame.Data.DataLayer.UpdateDataTableThread(Object ThreadParams)
   at MicroFour.StrataFrame.Business.BusinessLayer.Save(Boolean Transactional, String TransactionKey)
   at MicroFour.StrataFrame.Business.BusinessLayer.Save()
   at WinLoadClaimForm.w_SchemeCompanyList.Button1_Click(Object sender, EventArgs e) in D:\FALX\Folksam UK\Custom Applications\WinLoadClaimForm\WinLoadClaimForm\WinLoadClaimForm\w_SchemeCompanyList.vb:line 120
   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.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Hi,
I tried insert data into Oracle using a form that allows me to update existing data ok. When trying to create a new - very simple - record I received the same "Object reference"-error as above.  In summary, updates succeed using the business object , inserts fail.

Below is error stack:

BusinessLayerException
 An error occurred while saving an the data to the server.
DataLayerSavingException
 Object reference not set to an instance of an object.
NullReferenceException
 Object reference not set to an instance of an object.

Source     : MicroFour StrataFrame Business

Stack Trace:
   at MicroFour.StrataFrame.Data.OracleDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler, AddRowErrorHandler RowErrorHandler, Boolean RecreateCommand)
   at MicroFour.StrataFrame.Data.DbDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler, AddRowErrorHandler RowErrorHandler)
   at MicroFour.StrataFrame.Data.DataLayer.UpdateDataTableThread(Object ThreadParams)
   at MicroFour.StrataFrame.Data.DataLayer.SaveByForm(DataTable TableToSave, Boolean Transactional, String TransactionKey)
   at MicroFour.StrataFrame.Business.BusinessLayer.SaveByForm(Boolean Transactional, String TransactionKey)

What does the structure of your table look like?

If you are setting your PrimaryKeyIsAutoIncremented property to False, then you will need to manually specify a primary key for the property.  Also, if you set the PrimaryKeyIsAutoIncremented property to True, then you will need to set the OracleSequenceName property on the business object so the business object knows what sequence to use for the auto-incrementing of the primary key.  It is possible that the OracleSequenceName is nothing and that might be what is throwing the error.  Try setting the sequence and let me know what happens.

Hi,

I have tried both options - that is setting the autoincrement to true and false. No success. I did additional testing using a very simple table from Oracle's SCOTT.EMP using just two columns (EMPNO and ENAME)

As you see - the original error was coming from Oracle but when the autoincrement thing was fixed, then the VB.Net started to give the errors. One more thing, in the debug-window is now SQL-statement for the INSERT.

Would it be possible for you to write a simple application that can be used to enter a row to Oracle's SCOTT.EMP table? Then I could verify that it works in my environment and I could also use that as a reference point whe trying to figure out where it goes wrong. The database version 10g and I'm using VB.Net.

Cheers,

Kari

Ben,

attached is a small application that retrieves data from table EMP.
When you retrieve data and update that's fine.
When you insert a new record (use e.g. 1 for EMPNO and Bart for ENAME) then error appears. Autoincrement is set to false.

Cheers,

Kari