Updatable view

[Sofija Blazevski]

Hello, everyone!

I'm new to NHibernate, and I would like to use it together with
FluentNHibernate.
I've been trying to use them together for an updatable view scenario.

This is the problem:

None of the following Id generator classes
<generator class="identity" />
<generator class="native" />

allows inserting into updatable view. Updatable view (more precisely
database server) takes care of assigning id.
Each time I get "Null id" (or is it "Null identifier" ) thrown from
ISession.SaveOrUpdate method.
While, on the other hand, same scenario with inserting into table
works perfectly.

On database everything is tested to work.
NHibernate version is 3.2.0.4000

What is correct way to setup NHibernate to allow inserting into a view
without providing value for whatever field is set as Id?

I would like to use NHibernate for an application with intensive usage
of views and stored procedures.
Now, I know you probably don't like usage of updatable views, and I
would not like to get into discussion on why do I use them, I simply
need to use them - if I'm gonna use any ORM framework as there is no
painless way to just use stored procedures (I mean in any ORM in any
technology I came across so far).

Thanks in advance for helping.

- Sofija

[Michael Charalambous]

Hi Sofija

Both identity and native are post insert generators, so it sounds like NHibernate is not able to get the newly inserted ID from the database when updating the view.

Please can you provide some further information? Ideally the DBMS are you using, and a failing test showing your scenario including: the table and view schema, your fluent mapping of the view, test code and the exception.

Also please can you give the issues you are having with stored procedures and NHibernate? I've used them to insert data into a legacy database and found NHibernate works quite well in my scenario.

Thanks

Michael

[Sofija Blazevski]

Hi Michael,


Thank you so much for answering!

DBMS is MS SQL Server 2008 R2, plain insert on view works, but when
you mentioned I'm not sure if returning ID from database server is
working - maybe that is the problem - can I customize this feature
somehow?. That also makes me wonder, how would NH get inserted ID in
case of a Firebird DBMS ?

I'm not sure if I should paste entire db code here, I'll see if I can
prepare something that makes sense and upload it somewhere with
everything else.

As for stored procedures, I don't have any issue, I must admit I
didn't really even try it with Fluent & NH, I just don't like the way
it is supposed to be used. I need client implementation to be as
simple and clean as possible. I'm not saying it is bad, I'm just used
to much cleaner approach I have setup in Delphi, based on naming
conventions of course. That's why I would like to use NH in
combination with Fluent conventions based automapping, they are really
thrilling in every way except when it comes to stored procedures.

NH error details

NHibernate.AssertionFailiure == "null identifier"

This is Stack Trace, its long...

   at NHibernate.Engine.EntityKey..ctor(Object identifier, String
rootEntityName, String entityName, IType identifierType, Boolean
batchLoadable, ISessionFactoryImplementor factory, EntityMode
entityMode) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Engine
\EntityKey.cs:line 42
   at NHibernate.Engine.EntityKey..ctor(Object id, IEntityPersister
persister, EntityMode entityMode) in d:\CSharp\NH\NH\nhibernate\src
\NHibernate\Engine\EntityKey.cs:line 28
   at
NHibernate.Event.Default.AbstractSaveEventListener.PerformSaveOrReplicate(Object
entity, EntityKey key, IEntityPersister persister, Boolean
useIdentityColumn, Object anything, IEventSource source, Boolean
requiresImmediateIdAccess) in d:\CSharp\NH\NH\nhibernate\src\NHibernate
\Event\Default\AbstractSaveEventListener.cs:line 263
   at
NHibernate.Event.Default.AbstractSaveEventListener.PerformSave(Object
entity, Object id, IEntityPersister persister, Boolean
useIdentityColumn, Object anything, IEventSource source, Boolean
requiresImmediateIdAccess) in d:\CSharp\NH\NH\nhibernate\src\NHibernate
\Event\Default\AbstractSaveEventListener.cs:line 187
   at
NHibernate.Event.Default.AbstractSaveEventListener.SaveWithGeneratedId(Object
entity, String entityName, Object anything, IEventSource source,
Boolean requiresImmediateIdAccess) in d:\CSharp\NH\NH\nhibernate\src
\NHibernate\Event\Default\AbstractSaveEventListener.cs:line 119
   at
NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.SaveWithGeneratedOrRequestedId(SaveOrUpdateEvent
event) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Event\Default
\DefaultSaveOrUpdateEventListener.cs:line 162
   at
NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.EntityIsTransient(SaveOrUpdateEvent
event) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Event\Default
\DefaultSaveOrUpdateEventListener.cs:line 148
   at
NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.PerformSaveOrUpdate(SaveOrUpdateEvent
event) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Event\Default
\DefaultSaveOrUpdateEventListener.cs:line 76
   at
NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.OnSaveOrUpdate(SaveOrUpdateEvent
event) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Event\Default
\DefaultSaveOrUpdateEventListener.cs:line 53
   at NHibernate.Impl.SessionImpl.FireSaveOrUpdate(SaveOrUpdateEvent
event) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl
\SessionImpl.cs:line 2662
   at NHibernate.Impl.SessionImpl.SaveOrUpdate(Object obj) in d:\CSharp
\NH\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 541
   at DAL_FluentNHibernate.FluentNHibernateEngine.SaveAll[T](IList`1
instances) in C:\Project\WebStartup\DAL_FluentNHibernate
\FluentNHibernateEngine.cs:line 56
   at UI_WEBFORMS._Default.ASPxButton1_Click(Object sender, EventArgs
e) in C:\Project\WebStartup\UI_WEBFORMS\Default.aspx.cs:line 83
   at DevExpress.Web.ASPxEditors.ASPxButton.OnClick(EventArgs e)
   at DevExpress.Web.ASPxEditors.ASPxButton.RaisePostBackEvent(String
eventArgument)
   at
DevExpress.Web.ASPxClasses.ASPxWebControl.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument)
   at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument)
   at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection
postData)
   at System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)


On Mar 22, 5:01 pm, Michael Charalambous

[Michael Charalambous]

Hi Sofija

If you could provide a sample mapping file and the test it would be very useful.

In the meantime can you post the SQL that NHibernate is generating? If you put the line <property name="show_sql">true</property> in the session-factory section of your configuration then the SQL will be written to Console.Out.

As far as my understanding goes Firebird uses the same appoach to an Identity PK as SQL Server and many other DBMS, see http://www.firebirdfaq.org/faq243/ for further information.

Thanks

Michael

[Sofija Blazevski]

Hi Michael,

I was away last week but I hope you'll still be here to help me out :)

This is SQL of current test, I will prepare a better example:

INSERT INTO client.v_Product_Supplier (Name, Supplier) VALUES (?, ?);
select SCOPE_IDENTITY()

It does involve inserting/updating into 2 test tables, so it might be
there is a problem with select SCOPE_IDENTITY() ?
Sample I would like to prepare, that makes more sense and that I'll
probably use later, would have 4 tables inserting/updating over a
simple view interface.

This is the view of this example (I just don't have instead of
triggers implemented yet )

CREATE VIEW [client].[v_activityLog]
AS
select
al.ActivityLogId,
app.ApplicationName,
al.Username,
sc.ScreenName,
so.[Option],
al.LogTime
from client.ActivityLog al
inner join client.[Application] app ON app.ApplicationId =
al.ApplicationId
inner join client.ScreenOption so ON so.ScreenOptionId =
al.ScreenOptionId
inner join client.Screen sc ON sc.ScreenId = so.ScreenId

Insert/update would be only on these fields ApplicationName, Username,
ScreenName, [Option] . C# implementation will be very simple too.

In Firebird you can't do select SCOPE_IDENTITY() , you need to know
sequence name, that's different, that's why I asked.

if SCOPE_IDENTITY() is a problem, I do have a workaround I'm not sure
if I mentioned, I would just like to have a cleaner way if possible.

Thanks,
Sofija


On Mar 23, 1:14 pm, Michael Charalambous
<michael.charalamb...@gmail.com> wrote:
> Hi Sofija
>

> If you could provide a sample mapping file and the test it would be very
> useful.
>
> In the meantime can you post the SQL that NHibernate is generating? If you
> put the line <property name="show_sql">true</property> in the
> session-factory section of your configuration then the SQL will be written
> to Console.Out.
>
> As far as my understanding goes Firebird uses the same appoach to an

> Identity PK as SQL Server and many other DBMS, seehttp://www.firebirdfaq.org/faq243/for further information.

> ...
>
> read more »

[Michael Charalambous]

Hi Sofia

I suspect your scenario might be affected by the SCOPE_IDENTITY()  bug as outlined in the link below:

http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value

This bug is fixed in Sql Server 2012 but not in 2005 or 2008.

The posting "Posted by Microsoft on 18/03/2008 at 13:10" has some workarounds for this issue which might help.

Please let me know if this helps.

Michael

On Friday, 16 March 2012 23:03:59 UTC, Sofija Blazevski wrote:

[Sofija Blazevski]

Hi Michael,

Thank you for the link, it is informative and helpful. I'll look more
into suggested workarounds.

Best,
Sofija


On Apr 5, 11:50 am, Michael Charalambous

<michael.charalamb...@gmail.com> wrote:
> Hi Sofia
>
> I suspect your scenario might be affected by the SCOPE_IDENTITY()  bug as
> outlined in the link below:
>

> http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-...

>
> This bug is fixed in Sql Server 2012 but not in 2005 or 2008.
>

> The posting "Posted by *Microsoft* on 18/03/2008 at 13:10" has some

[Michael Charalambous]

Hi Sofia

Had a further look and the issue I referred to in the previous post causes incorrect IDs to returned not nulls, therefore I suspect it may be something else. So I have the following questions for you

1. Does the sql insert statement generated by NHibernate work when you try it against your db directly?
2. Does the statement try to update/insert fields from more than one table? Since as I understand this is not allowed in SQL Server.

It would be really good if you could email a sample project to me with a failing test, as I have pretty much run out if ideas and this would really help me to identify the exact cause of your problem

Mike

[Fabio Maulo]

Use Persist instead SaveOrUpdate; Persist will make persistent the whole graph (all entities will have its POID).

If you are using FNH take care with cascade setting because FNH does not use "persist" in cascades.

[Sofija Blazevski]

Hi Michael,

1. insert statement works fine, however select SCOPE_IDENTITY();
doesn't (returns null) so I suppose it really is some Microsoft
limitation or bug, I was planning to install SQL Server 2012 Express
today and check if its still same
2. It does insert/update more then one table, and you are correct that
it is not allowed, but Microsoft has a special triggers for that
"instead of triggers".

I started a sample project, but I didn't finish as I supposed link you
sent me was enough. Do you use NUnit for tests?

Sofija


On Apr 7, 2:59 am, Michael Charalambous

[Sofija Blazevski]

Hi Fabio,

I tried Persist but result is same, it is probably due to incorrect
select scope_identity() on the database server side.

Sofija

[Sofija Blazevski]

Hi Mike,

I prepared database side for the example we talked about, and I tested
scope_identity throughout stored procedures and instead of insert
trigger on a view.
Scope_identity works well within stored procedures but It seems it
doesn't work well within instead of triggers, and it doesn't work
after insert statement on a view .. probably due to instead of trigger
limitation. I'll check on 2012 as well.

I wouldn't want to bother you with this any more as at the end it
seems it is a database issue, it just wasn't clear from the start as
insert on a view is working fine.

Thank you for trying to help me!

Best,
Sofija

On Apr 7, 2:59 am, Michael Charalambous