Using SQL statements in the code

StrataFrame Forum

Using SQL statements in the code

http://forum.strataframe.net/Topic3206.aspx

By Kari Paukku - 10/1/2006

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

By Trent L. Taylor - 10/1/2006

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()

By Kari Paukku - 10/2/2006

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

By Trent L. Taylor - 10/2/2006

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)

By Kari Paukku - 10/2/2006

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)

By Kari Paukku - 10/2/2006

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

By StrataFrame Team - 10/3/2006

What does the structure of your table look like?

By StrataFrame Team - 10/3/2006

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.

By Kari Paukku - 10/3/2006

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

By Kari Paukku - 10/3/2006

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

By StrataFrame Team - 10/4/2006

OK, Kari, there is no sequence created that you can use for inserting records into the Scott.Emp table. So, I tried to manually assign one and got the same error you did. I tracked it down and corrected the problem and we will be posting an update before the end of the week with some changes to the activation wizard, so this will be included as well. Thanks for your patience.

By Kari Paukku - 10/4/2006

Great!

Just in case if the update gets delayed, would it be possible to get the "insert"-fix so that I could work on this project during the week-end. Now I'm sort of stuck....

Cheers,

Kari

By StrataFrame Team - 10/4/2006

Well, not really... there were several places within the OracleDataSourceItem that changed in order for it to pass the new unit tests, but what I can do is post a new install here by the end of today.

By Kari Paukku - 10/4/2006

Ben,

thank you very much.

Kari

By Kari Paukku - 10/14/2006

Hi,
just as information, after downloading the latest version of SF (dated 10-10-2006) the attached sample worked and allowed to create a new record into the EMP table.

Cheers,
Kari

ps. Incase somebody tries the example - add the DEPTNO field to the form. Otherwise you get a referential integrity error from Oracle.

By StrataFrame Team - 10/16/2006

Good to know about that foreign key that exists on the EMPS table. Thanks for the heads up and I'm glad you're working Smile