Patch: Oracle Sequence Trigger Auto Increment Primary Keys

[GoogleCodeExporter]

Blake Niemyjski helped me considerably to develop this additional functionality.

Instructions to use Oracle Sequence Trigger Auto Increment Primary Keys with 
NetTiers (As of 4/11):

1.  Open the CodeSmith Schema Explore [ View | Schema Explored }
2.  Navigate to the table(s) with which you want to use Oracle Sequence Trigger 
Auto Increment Primary Keys
3.  Navigate to the primary key column for the table
4.  Right Click on the column and select Extended Properties
5.  Add a property with Name: CS_Identity – Type: Boolean – Value: True
6.  Open NetTiers.cst
7.  Set 07. CRUD – Advanced | IncludeTriggerOracle to True
8.  Set 07. CRUD – Advanced | IncludeSequenceOracle to True
9.  If desired set any or all 08. Stored procedures – Advanced | 
SequencePrefix, SequenceSuffix, TriggerPrefix, TriggerSuffix

Original issue reported on code.google.com by contactb...@gmail.com on 22 Apr 2011 at 7:25

Attachments:

• OracleSequencePatch110422.patch

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

I forgot to mention an issue you may run into when running procedures.sql to 
create your sequences and triggers. If the sequence already exists you will 
receive a harmless error message while the trigger will simply overwrite 
without an error message. This is a feature not a bug. If the sequence exists 
it may already have a value higher than the initial value of 1 therefore you 
don't want to set it back

Original comment by contactb...@gmail.com on 22 Apr 2011 at 10:04

[GoogleCodeExporter]

These items were in the code at one time but my testing showed they were not 
needed:
OracleEntityProviderBase.generated.cst
Line 1041
<%} else if (IsIdentityColumn(SourceTable.PrimaryKey.MemberColumns[i])){%>
entity.<%=GetPropertyName(SourceTable.PrimaryKey.MemberColumns[i])%> = 
(<%=GetCSType(SourceTable.PrimaryKey.MemberColumns[i])%>)command.Parameters["p_<
%=GetPropertyName(SourceTable.PrimaryKey.MemberColumns[i])%>"].Value;

CommonSqlCodes.cs
Line 2561
string desc = 
column.ExtendedProperties["CS_Description"].Value.ToString().ToLower();
if (desc.Contains("autoinc"))
return true;

Original comment by bniemyjski on 25 Apr 2011 at 12:36

• Changed state: Accepted
• Added labels: Platform-Oracle, Priority-High
• Removed labels: Platform-SQL, Priority-Medium

[GoogleCodeExporter]

Issue 386 has been merged into this issue.

Original comment by bniemyjski on 18 May 2011 at 5:54

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

But how can I get the primary key value of the new Entity after added? My 
testing showed the value of the new entity primary key property is not correct.
Thanks
Edward

Original comment by Rising...@gmail.com on 21 May 2011 at 11:34

[GoogleCodeExporter]

I believe this code has now been put in the main build. Make sure you are up
to date. You should see these options in the configurable options (where you
select your file paths etc.). If you still don't see them apply the patch
using these instructions:
http://tortoisesvn.net/docs/release/TortoiseSVN_en/tsvn-dug-patch.html

Original comment by contactb...@gmail.com on 21 May 2011 at 1:38

[GoogleCodeExporter]

I have merged th patch, all tables have sequence and trigger, but some many to 
many relationship tables do not need sequence and trigger.
If I have a table named "ATable", and after I added a record into the table 
like this:

ATable newEntity = new ATable();
newEntity.Id = 0;
//set the entity properties;
DataRepository.ATablerProvider.Insert(newEntity);
//the newEntity.Id should be 1, but it is 0, how can I get the correct value of 
the newEntity.Id?

by the way, all sequence and trigger statement closed ty "go" in procedures.sql.

Original comment by Rising...@gmail.com on 1 Jun 2011 at 10:01

[GoogleCodeExporter]

by the way, all sequence and trigger statement closed by "go" in procedures.sql.

Original comment by Rising...@gmail.com on 1 Jun 2011 at 10:02

[GoogleCodeExporter]

Hello,

Were you able to debug this and create a patch for the many to many insert and 
go statements? If so, can you please post an updated patch.

Thanks
-Blake 

Original comment by bniemyjski on 9 Jun 2011 at 9:58

[GoogleCodeExporter]

Blake

I am currently assigned to a Cold Fusion project at work. I don't know when
I will have time for this.

I don't totally understand the many to many issue but haven't had time to
look at it.

What is the problem with the go statements anyway?

Bryan

Original comment by contactb...@gmail.com on 9 Jun 2011 at 11:43

[GoogleCodeExporter]

Hello,

Thanks for the heads up, if you get some time to look into this... I may be 
free to pair code on this. Also, I'm not sure about the go statements, I was 
asking @Rising about his comment.

Thanks
-Blake 

Original comment by bniemyjski on 13 Jun 2011 at 6:44