Generated Stored Procedure Not Inserting GUID Primary Key

Bug: Despite taking the uniqueidentifer as a parameter, the generated stored procedure does not USE it. Nor does it return the key actually inserted.

Comments please?

regards,

David

*ping*

Anybody have a comment/suggestion?

The above omission is in the latest source tree. Further investigation has shown that this 'issue' has been introduced in recent checkins as the 'download' version generates stored procedures as I would expect.

If implicitly inserting Primary Keys has been removed by design then please just let me know so I understand the thinking.

It would also appear that whilst previous versions attempted to autogenerate validation rules, the versions of the templates I have tried recently do not.

maybe your table column is defined as identity, so I dont have to use the identifier passed as parameter. Your procedure doesn´t use it, so maybe it´s simple as that.

I dont know if thats netTiers generated procedure, but on the latest version it passes that identifier parameter as OUTPUT. 

vbandrade:

I dont know if thats netTiers generated procedure, but on the latest version it passes that identifier parameter as OUTPUT. 

Thanks for your response. The stored procedures that I refer to are those generated by the netTiers scripts.

Your comment above is exactly the issue, the latest source tree (in svn) does NOT return the generated UNIQUEIDENTIFIER as output. for example (which has been taken from a codebase generated by SVN Rev: 701),

CREATE PROCEDURE dbo.orm_TokenStore_Insert
(
@TokenId uniqueidentifier   ,
@DateOfIssue datetime   ,
@TokenValid bit  
)
AS

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
    INSERT INTO [dbo].[TokenStore]
     (
     [DateOfIssue]
     ,[TokenValid]
     )
    VALUES
     (
     @DateOfIssue
     ,@TokenValid
     )
    
         
       
   

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

David,

 What version of the templates did this work in (version from changes.log would be nice)?  I did a quick browse through the changes to this file (StoredProceduresXml.cst) and I don't anywhere where this would work.  It's definitely a bug, but I just don't see where this would have ever worked.  Identity columns are simple because you can just use the SCOPE_IDENTITY to get the last inserted value.  There is no such equivalent for uniqueidentifier columns...

Something like the following would have to be generated:

SET @TokenIdentifier = NEWID()

INSERT INTO [dbo].[TokenStore]
     (
     [TokenIdentifier],
     [DateOfIssue]
     ,[TokenValid]
     )
    VALUES
     (
     @TokenIdentifier
     ,@DateOfIssue
     ,@TokenValid
     )

Hi!,

OK, Version from changes log (which is current to https://nettiers.svn.sourceforge.net/svnroot/nettiers/source/trunk/Source/changes.log) is .netTiers v2.2.0.699 (01/28/2008).

I agree with your approach, with the addition of should @TokenIdentifier (which would be a parameter) not have a value identified then the SP would use the NewID() method to generate one. So that you can indeed identify the TokenIdentifier from code if you so wish.

The codebase as recent as that associated to the distributable http://www.nettiers.com/files/netTiers2.2.0.zip had the correct stored procedure generating code in it. What I am trying to ascertain here is the thinking behind the change that has been introduced - or to ascertain whether it was  done by mistake.

I just generated code using the 603 release version (original 2.2.0 release) and I still don't see where the stored procedure would have returned the new guid.  The only real difference between the two versions (regarding the Insert proc) is that columns marked as rowguidcol property are not updated.  See this thread to see when this was fixed:

http://community.codesmithtools.com/forums/p/6047/27994.aspx#27994

This would make a great user contribution...  I personally don't have the time to tackle this right now.

Thanks for working on this further. I happen to have old source that I am sure generated what we are discussing and will be happy to contribute. Do you just want a patch file uploaded?

Yes, a patch would be great.

I just started using .netTiers v2.2.0.702 and am having the same issue with the guid not being returned.  Is there a way for me to get this patch also?

 Thanks..

 Jason

Hi,

I have to be honest and confess that I am struggling to find time to decifer nettiers code nevertheless address this specific issue - however it may be of some use explaining how I got round the problem we are having.

If you download the scripts from .nettiers site (as in the released version v2.2.0.603) and generate another copy of the codebase using those scripts, merge the stored procedures generated for tables with UniqueIdentifiers as Identities with the codebase generated by .702.

This way, you can specify the GUID to use on the entity myentity.RecordID = Guid.NewGuid(); , it will be used in the insert stored procedure INSERT statement. You thereafter know the guid that has been stored in the database.

David,

I think I found the problem.  The stored procedure that is created does not use the ID field

ALTER PROCEDURE [dbo].[nt_AddressInfo_Insert]

(
@Id uniqueidentifier ,
@Address1 varchar (100) ,
@Address2 varchar (100) ,
@City varchar (50) ,
@StateId uniqueidentifier ,
@Zip varchar (50) ,
@Latitude float ,
@Longitude float
)AS
INSERT INTO [dbo].[AddressInfo]
(
[Address1]
,[Address2]
,[City]
,[StateID]
,[Zip]
,[Latitude]
,[Longitude]
)
VALUES
(
@Address1
,@Address2
,@City
,@StateId
,@Zip
,@Latitude
,@Longitude
)

As you can see it is not using the ID I am passing in.  I am going to try the .603 version to see if it has the same issue.  I swear everything was working before I upgraded.  I guess that is what I get for upgrading to the nightly build

Jason

Yeah, that's right - the stored procedures generated by the current svn trunk version do not use the ID specified in the parameters - however my last post details a way around this by merging the stored procedures generated by a previous version with those generated by the latest - which allows you to specify your own ID in code and have it saved to the DB.

If I had more time I would learn how nettiers' codesmith scripts work and fix this - it is down to the scripts not recognising the uniqueidentifier as the Identity Column - something as we know it used to do.

Worthy of note, I don't recall it ever returning (via OUTPUT parameter) the DB generated UniqueIdentifier in scenarios where you didn't implicitly specify it in code.

OK, turns out that if you have a uniqueidentifier as a primary key BUT NOT marked as "Is RowGUID" then the scripts will generate Insert and Update stored procs that make use of the uniqueidentifer parameter. If you mark it as "Is RowGUID" then it does not use that column in the insert and update procs.

Ok, so the "fix" that broke your situation revolved around the procs to update columns that were marked as RowGuids (which you can not do).  Unfortunately, this fixed the update proc but broke the Insert proc.  I have attached a quick patch that might fix the issue.  Can you give it a try and let me know if it works?