3.1,Adapter,SqlServer

Have a view with an instead of trigger. The view inserts the header record into the master table. Then if other fields are not null, it "may" also create child records.

Originally, with scope identity, and no child records getting created, this worked, save would return true. As soon as we pass in addl data to have child records created, it's returning false, probably because it's getting the identity of whatever the last child record was.

So I switched the statement in PersistenceInfoProvider from scope to IDENT_CURRENT('Orders') and now when I save, I see from the llbl trace log that it is correctly adding to the end of the sql: SELECT @p1=IDENT_CURRENT('Orders'). Save still returns false even though all the records are correctly getting saved in the db.

I traced through llbl all the way to the query execute and the output parameter p1 is still null after a successful execute.

BUT, if I copy paste the sql into SMS, and run there, p1 is correctly getting set to the correct identity from the master table.

Any ideas what is going wrong?

Is this the same question / related to the other thread you posted?

No. The other question is why don't sql statements get logged to the trace if there's a sql exception.

For this question, I just now finally got it to work, I had to put the FULLY qualified name of the table into the IDENT_CURRENT call. Which will now make it hard to change the database name when deployed.

Still wish there was another way to solve this problem of an instead of trigger on a view with multiple internal inserts (all have identities) and I need to return a specific identity.

BTW, why doesn't the LLBL designer offer the ident_current as a dropdown option? Only offers identity and scopeidentity.

IDENT_CURRENT has the problem that it returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value. Therefore you might have concurrency problems.

If it is really needed I think you can manually set the identity sequence in the project file in a text editor, or using the 'Search element' tab. Then you need to modify the persistenceInfoProvider.template.

Microsoft recommends SCOPE_IDENTITY() and if that doesn't work, @@IDENTITY. That covers all situations: SCOPE_IDENTITY() is the most reliable one, and if you have an insert trigger which creates an identity value, you should pick @@IDENTITY. Both are supported.

I've read of possible concurrency issues with IDENT_CURRENT. But it looks like LL wraps everything with a transaction even if I don't. So if I am using IDENT_CURRENT within an instead of trigger, how would anybody else be able to insert a row into the table until my transaction commits?

The reason we can't use either of the other two identity calls, is that our instead of trigger is creating child records AFTER the master record, so they return the wrong identity value.

Any other ideas on how to handle this?

On a side note, I'd thought of just switching to a stored proc, but we've always relied on LL's dynamic sql generation. In reading the docs, it doesn't seem like there's any built in way to have LL redirect saves to a stored proc (and auto generate all the parameters, etc). Is there any feature or templates to help with that?

On a side note, I'd thought of just switching to a stored proc, but we've always relied on LL's dynamic sql generation. In reading the docs, it doesn't seem like there's any built in way to have LL redirect saves to a stored proc (and auto generate all the parameters, etc). Is there any feature or templates to help with that?

No this is not supported. But since you are taking the database route (triggers) then I don't see a reason not to use a stored procedure.

Otherwise you may have built your logic in code (client side).

I regenerataed and my manual change to PersistenceInfoProvider to use IDENT_CURRENT is gone.

How can i get this change to stick? Why can't I type into that dropdown in your designer? Is there a way for me to add more options to that dropdown?

happyfirst wrote:

The reason we can't use either of the other two identity calls, is that our instead of trigger is creating child records AFTER the master record, so they return the wrong identity value.

Any other ideas on how to handle this?

Well, that depends on what does your trigger do exactly. Maybe you can create those child records on code side.

happyfirst wrote:

On a side note, I'd thought of just switching to a stored proc, but we've always relied on LL's dynamic sql generation. In reading the docs, it doesn't seem like there's any built in way to have LL redirect saves to a stored proc (and auto generate all the parameters, etc). Is there any feature or templates to help with that?

You have to pass the parameters manually to the SP call.

happyfirst wrote:

I regenerataed and my manual change to PersistenceInfoProvider to use IDENT_CURRENT is gone.

How can i get this change to stick? Why can't I type into that dropdown in your designer? Is there a way for me to add more options to that dropdown?

Even if you add a new sequence, you have to modify the template to recognize it and write the corresponding code:

... "<[IdentityValueSequenceName]>('<[ElementTargetObjectName]>')" ...

If you go with this option you should be careful. You can create your own version of the template and then move it up in the precedence tab, at the generation code wizard. To add the sequence so it appears on the combobox, you could open the .llblgenproj file in a notepad and add it to the <SystemSequences> node.

But, as I said above, we would recommend to evaluate your trigger and see whether you can move the child records creation to your .net code.

Additionally, you can create a custom SqlServer driver, which simply subclasses the existing SQL server driver's SqlServerDBDriver class and override 'GetAllSystemSequences'. First call the base' method, and then append IDENT_CURRENT to the list. You can then select it in the designer.

btw, llblgen pro only wraps things in a transaction if there are multiple inserts/updates. It otherwise doesn't wrap it, so it uses the implicit transaction of the insert/update statement itself. Please be aware of that. (if you start a transaction yourself, it of course executes within that transaction)

One problem with modifying the template is that I need the full database table name in that call in order for it to work.

All the other options for using the designer seem even more extreme.

It's frustrating cause what we have now works, just that I need to keep fixing that line after every code generation.

Is it possible for me to change the data in FieldPersistenceInfo AFTER it's been loaded? I was thinking I could add some custom user section to the PersistenceInfoProvider class and then just fix up that value.

But, AAAARGGGH, you've made that property read only.

Is there any other way to get inside the framework and change that value? I don't understand why it had to be readonly.

happyfirst wrote:

One problem with modifying the template is that I need the full database table name in that call in order for it to work.

You can construct that full name. You have all elements at that time (schema, catalog, targetObjectName). Using templates has the ability that you don't have to modify the generated code every time. You just have to adjust the template everytime you update/upgrade LLBLGen designer.

happyfirst wrote:

Is it possible for me to change the data in FieldPersistenceInfo AFTER it's been loaded? I was thinking I could add some custom user section to the PersistenceInfoProvider class and then just fix up that value.

But, AAAARGGGH, you've made that property read only.

That is something people should not change, not even through inheritance. I will ask Frans about this, anyway.

happyfirst wrote:

One problem with modifying the template is that I need the full database table name in that call in order for it to work.

All the other options for using the designer seem even more extreme.

It's frustrating cause what we have now works, just that I need to keep fixing that line after every code generation.

Is it possible for me to change the data in FieldPersistenceInfo AFTER it's been loaded? I was thinking I could add some custom user section to the PersistenceInfoProvider class and then just fix up that value.

But, AAAARGGGH, you've made that property read only.

Is there any other way to get inside the framework and change that value? I don't understand why it had to be readonly.

because it is not meant to be changed. Things which are not meant to be changed are set to readonly. Sorry.

(edit) suggestion which is not working, removed.

Before this thread I hadn't heard of IDENT_CURRENT before, hence it's not in the driver. I'll look into what can be done for you.

After reading up on IDENT_CURRENT and re-checking what the other 2 do (see this for a nice overview), I don't see why SCOPE_IDENTITY doesn't work: the identity values created in child rows are outside the scope, so the identity value in the root instead of trigger should be the value returned.

Please check this at the SQL Server level with T-SQL executed on the db to see what's really returned.

Otis wrote:

I don't see why SCOPE_IDENTITY doesn't work: the identity values created in child rows are outside the scope, so the identity value in the root instead of trigger should be the value returned.

Please check this at the SQL Server level with T-SQL executed on the db to see what's really returned.

I thought this too, but what exactly defines a change of scope? I don't know what you mean by "root instead of trigger". Remember, this is a view with an instead of trigger so all the inserts are happening there. So the one single trigger inserts the master record (for which I want the indentity). Then, depending on if/how other columns were set, child records are created. Whenever the trigger has to create child records, the identity returned is that of the last child.

happyfirst wrote:

Otis wrote:

I don't see why SCOPE_IDENTITY doesn't work: the identity values created in child rows are outside the scope, so the identity value in the root instead of trigger should be the value returned.

Please check this at the SQL Server level with T-SQL executed on the db to see what's really returned.

I thought this too, but what exactly defines a change of scope? I don't know what you mean by "root instead of trigger".

Frans meant "root" trigger as the 1st level trigger fired hen you save the entity/view. Since there are no other triggers, you just have the "root" one, thus the insertion of the childrows indeed messed the SOPE_IDENTITY. So, indeed, in this special case, the IDENT_CURRENT maybe is the only one you can use, unless of course you can insert the childrows on code side.

I don't think the custom driver + custom template is a extreme solution. You can do this and still be safe between code generations and LLBLGen build updates/upgrades. Do you want help on this? Maybe we can cook that custom stuff for you.

Ok. Here is the solution we are proposing: attached is a zip file that contains two folders: \driver and \templates. To make it work, follow these steps:

Installation 1. Unzip everything in a temp folder. 2. Open the \driver\MySqlServerDBDriver\MySqlServerDBDriver.csproj, fix the references and compile it. 3. Place the compiled assembly and driver.config into <LLBLGen installation folder>\Drivers\MySqlServer\ (note that MySqlServer is a new folder but you can name it as you want). 4. Place the contents of \template folder into your preferred folder where you store your additional templates. For instance C:\MyCustomLLBLGenTemplates.

Project creation 5. Create a new LLBLGen project. Add a "New Relational Model From a Database". Select the driver that says "SQL Server 2000/2005 ... With Custom Sequences" which is our custom driver, then finish the wizard. 6. Select your special entity that you want to use IDENT_CURRENT. Go to the Mappings sub-tab and select **IDENT_CURRENT **for the identity column. 7. Go to Project->Settings->General and set the "Additional templates folder" setting to C:\MyCustomLLBLGenTemplates. This will made possible to LLBLGen to locate the new custom templates.

Code Generation 8. Hit [F7] (Generate code). 9. At that wizard, click on "Advance" button. Then go to "Template bindings" subtab, then locate the "SD.TemplateBindings.SharedTemplates.NET20.CustomSequences" binding and move it up to the top of the list. This will make that LLBLGen to prefer your new **SD_PersistenceInfoProviderTemplate **over the original one. Then finish the generation wizard.

Some advantages of this approach: - You don't have to modify anything in your generated code over and over. - You don't have to worry if you update the LLBLGen built-in templates by re-installing it. The only template you are overriding is SD_PersistenceInfoProviderTemplate, so you just have to look whether or not there is a major change in this template. If it's then incorporate the changes in the template. The template is tiny, so you won't face any extreme task here. - You can still use the other sequence values (SCOPE_IDENTITY, @@IDENTITY). - You can share those files/steps with your colleagues.

I tested the files, and they seem to work well, this is an example of a Northwind's Order insert:

INSERT INTO [Northwind].[dbo].[Orders] ([OrderDate]) VALUES (@p1) ;SELECT @p2=IDENT_CURRENT('Northwind.dbo.Orders')
Parameter: @p1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 6/30/2012 1:46:00 AM.
Parameter: @p2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: <undefined value>.

Let us know how it works on your side

daelmo wrote:

Ok. Here is the solution we are proposing: attached is a zip file that contains two folders: \driver and \templates. To make it work, follow these steps:

Installation 1. Unzip everything in a temp folder. 2. Open the \driver\MySqlServerDBDriver\MySqlServerDBDriver.csproj, fix the references and compile it. 3. Place the compiled assembly and driver.config into <LLBLGen installation folder>\Drivers\MySqlServer\ (note that MySqlServer is a new folder but you can name it as you want). 4. Place the contents of \template folder into your preferred folder where you store your additional templates. For instance C:\MyCustomLLBLGenTemplates.

Project creation 5. Create a new LLBLGen project. Add a "New Relational Model From a Database". Select the driver that says "SQL Server 2000/2005 ... With Custom Sequences" which is our custom driver, then finish the wizard. 6. Select your special entity that you want to use IDENT_CURRENT. Go to the Mappings sub-tab and select **IDENT_CURRENT **for the identity column. 7. Go to Project->Settings->General and set the "Additional templates folder" setting to C:\MyCustomLLBLGenTemplates. This will made possible to LLBLGen to locate the new custom templates.

Code Generation 8. Hit [F7] (Generate code). 9. At that wizard, click on "Advance" button. Then go to "Template bindings" subtab, then locate the "SD.TemplateBindings.SharedTemplates.NET20.CustomSequences" binding and move it up to the top of the list. This will make that LLBLGen to prefer your new **SD_PersistenceInfoProviderTemplate **over the original one. Then finish the generation wizard.

Some advantages of this approach: - You don't have to modify anything in your generated code over and over. - You don't have to worry if you update the LLBLGen built-in templates by re-installing it. The only template you are overriding is SD_PersistenceInfoProviderTemplate, so you just have to look whether or not there is a major change in this template. If it's then incorporate the changes in the template. The template is tiny, so you won't face any extreme task here. - You can still use the other sequence values (SCOPE_IDENTITY, @@IDENTITY). - You can share those files/steps with your colleagues.

I tested the files, and they seem to work well, this is an example of a Northwind's Order insert:

INSERT INTO [Northwind].[dbo].[Orders] ([OrderDate]) VALUES (@p1) ;SELECT @p2=IDENT_CURRENT('Northwind.dbo.Orders')
Parameter: @p1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 6/30/2012 1:46:00 AM.
Parameter: @p2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: <undefined value>.

Let us know how it works on your side

I took your advice and when I looked inside my PersistenceInfoProvider.cs file, I saw that the identityValueSequenceName was something like:

IDENT_CURRENT('DB.SCHEMA.TABLE')

I was saving a new record, and I got the error:

An exception was caught during the execution of an action query: Invalid object name 'IDENT_CURRENT('DB.SCHEMA.TABLE')'

And the beginning of the query executed looks like:

Query: SELECT @p4 =NEXT VALUE FOR [IDENT_CURRENT('DB].[SCHEMA].[TABLE')]; INSERT INTO [DB].[SCHEMA].[TABLE] ...

There were brackets inserted into the object name, from DB.SCHEMA.TABLE to DB].[SCHEMA].[TABLE and I don't understand why this is and it makes me think this is part of my problem.

Then I tried to add brackets to the object name so it looks like IDENT_CURRENT('[DB.SCHEMA.TABLE]') to get IDENT_CURRENT('[DB].[SCHEMA].[TABLE]'), but then I get a different error:

Unclosed quotation mark after the character string ')]; INSERT INTO [DB].[SCHEMA].[TABLE] ... when the query executed looks like Query: SELECT @p4 =NEXT VALUE FOR [IDENT_CURRENT('[DB].[SCHEMA].[TABLE]')]; INSERT INTO [DB].[SCHEMA].[TABLE]

and I don't understand why that error is coming up because the quotes are matched up in the query

This is an 8 years old thread. Please create a new thread of your own, and refer to this one if you want. But please provide more details about your environment, especially the LLBLGen Pro runtime library version you are using.