New post
Question
· Oct 9, 2020

Adding a Hidden Date (Date Inserted) column to a RecordMap Generated Cache Table

#ObjectScript #Relational Tables #SQL #Caché #Ensemble

I noticed when creating Record Map's within Ensemble that it is creating a Persistent cache table as it translates the file and puts it into the Record Map data structure.

I was wondering if there was a way to add a column to the cache table that is Hidden from the record map, but is a calculated date value on when that record was inserted? 

From time to time I have gotten questions about what was valued in the incoming data stream, and I thought it would be helpful to add a date so I can sort on by which date that record came in on.

 

Thanks

Scott

 

Cache for UNIX (IBM AIX for System Power System-64) 2018.1.3 (Build 414U) Mon Oct 28 2019 11:24:02 EDT [HealthShare Modules:Core:15.032.9026 + Linkage Engine:15.032.9026]

Discussion (4)2
Log in or sign up to continue
Scott Roth · Oct 9, 2020

I think I figured it out, but could someone verify?

I am able to edit the class file in Studio to enable "DDLAllowed".

1. Then I scripted the following...

Alter table "osuwmc_Workday_DataStructures_FSINT3376ItemMaster"."Record" ADD DateInserted DateTime 

2. After I was able to add the time through SQL I was able to alter the column to set the DEFAULT = getdate()

Alter table osuwmc_Workday_DataStructures_FSINT3376ItemMaster.Record Alter DateInserted DEFAULT(getdate())

In the class file it is now showing...

Property DateInserted As %Library.TimeStamp [ InitialExpression = {$zdt($zu(188),3,1)}, SqlColumnNumber = 24 ];

I realize I can probably combine my SQL statements, but I did not use the Generator.GenerateObject(). Is this ok?

Thanks

Scott

0 0
Scott Roth · Oct 12, 2020

setting the following did not work  InitialExpression = {$zdt($zu(188),3,1)} as the records are being read by EnsLib.RecordMap.Service.FileService into the record map generated table, I can not get the DateInserted column to auto-populate.

Does anyone have any suggestions on getting this DateInserted column to populate as the RecordMap.Service is reading in the file?

Thanks

Scott

0 0
Stephen Canzano · Oct 13, 2020

I dont think your solution is a solution that works long term, someone can regenerate the record map and if your script isn't run then the property would be removed.  To answer your last question I think you would have better success if you define the property like

Property InsertDate As %UTC [ ReadOnly, SqlComputeCode = {set {*}=##class(%UTC).NowUTC()}, SqlComputed, SqlComputeOnChange = %%INSERT ];

I'm not 100% certain but the initial expression may only be executed as part of an object implementation but not part of an SQL statement.  If the RecordMap code is actually doing SQL inserts this may produce better results.

0 0