ORM identity column query VS stored proc

[IanOSullivanIreland]

Hi there

Just started using the ORM functionality in CF9.

Just came across an error when I was trying to save a new entity to a table;

Cannot insert explicit value for identity column in table 'ip_bw_list' when IDENTITY_INSERT is set to OFF.

I realise this is because the Identity Specification of the column in SQL Server is set to Yes. So turning this off all is Okay.

So this leads me to my question; Before I used ORM I used stored procs. Because the identity column was specified in SQL as Identity Specification = Yes, I never passed a variable in through a cfprocparam.

But now for ORM to work I must turn it off and therefore If I now use a stored proc I have to pass in the identity column by doing something like;

SELECT IDENT_CURRENT ('my_table') + 1 AS Next_Identity

and passing this into the stored proc.

Am I missing something here? Does anyone have any recommendations about using ORM and stored procs in the same app? 

Thank you

Ian

[Dave Watts]

In your entity CFC, you should specify that the primary key is an identity, and in your application's ORM settings, you should specify the "flavor" of database you're using. That's all you should have to do - you might not even have to do the second thing.

<cfproperty name="id" column="ARTISTID" generator="identity"> 

http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSB7BEC0B4-8096-498d-8F9B-77C88878AC6C.html

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

[IanOSullivanIreland]

Thanks Dave

Sorry, I probably didn't explain myself fully. I'm having no problem with my

my ORM code. I had my cfproperty set exactly as you specified. But for my code to work

I had to turn off Identity Increment in SQL in my column specification.

Otherwise when doing an insert through ORM I was getting the ColdFusion error:

Cannot insert explicit value for identity column in table 'ip_bw_list' when IDENTITY_INSERT is set to OFF

So my question is; Do I have to set the Identity Increment to false of the

Primary key ID field of my table for ORM to work?

If the answer is yes it just means that if/when I use a stored proc I'll have to insert the ID value manually

(using SELECT IDENT_CURRENT ('my_table') + 1 AS Next_Identity) instead of allowing SQL to automatically do it.

[IanOSullivanIreland]

Thanks Dave

Sorry, I probably didn't explain myself fully. I'm having no problem with my

my ORM code. I had my cfproperty set exactly as you specified. But for my code to work

I had to turn off Identity Increment in SQL in my column specification.

Otherwise when doing an insert through ORM I was getting the ColdFusion error:

Cannot insert explicit value for identity column in table 'ip_bw_list' when IDENTITY_INSERT is set to OFF

So my question is; Do I have to set the Identity Increment to false of the

Primary key ID field of my table for ORM to work?

If the answer is yes it just means that if/when I use a stored proc I'll have to insert the ID value manually

(using SELECT IDENT_CURRENT ('my_table') + 1 AS Next_Identity) instead of allowing SQL to automatically do it.

[IanOSullivanIreland]

UPDATE :: I've resolved this myself. To be totally honest I'm not 100% sure how because I was playing around with the cfproperty attributes so much.

So now my SQL table has identity increment set to YES and my cfproperty tag for the ID is: <cfproperty name="id" fieldtype="id" generator="native"> .

Ian.