Question
Ankur Shah · Nov 5, 2019

Cache DB - How I can access identity column value in SqlComputeCode with property

How I can access identity column value in SqlComputeCode with property?
 

#API #Caché
00
1 0 4 139
Log in or sign up to continue

Replies

Eduard Lebedyuk · Nov 5, 2019

Use {%%ID}.

Here's an example:

Class util.Calc Extends %Persistent
{
Property calc As %String [ Calculated, SqlComputeCode = {set {*} = {%%ID}}, SqlComputed ];

/// do ##class(util.Calc).Test()
ClassMethod Test()
{
    set obj = ..%New()
    set sc = obj.%Save()
    do ..AllFunc().%Display()
}

Query All() As %SQLQuery
{
SELECT *
FROM util.Calc
}
}

Calling:

do ##class(util.Calc).Test()

Returns:

ID      calc
1       1

Docs.

00
Accepted answer
Ankur Shah · Nov 5, 2019

I want to store the LabCode value in database as combination of LabName & LabID property. In LabCode field LabName value stored but LabID not stored. Can you please help me on this.

Please refer below example.

Index LabIDX On LabID [ PrimaryKey ];
Property LabID As %Integer(MAXVAL = 2147483647, MINVAL = -2147483648) [ Identity, SqlColumnNumber = 2 ];
Property LabName As %Library.String(MAXLEN = 128) [ Required, SqlColumnNumber = 3 ];
Property ShortName As %Library.String(MAXLEN = 64) [ SqlColumnNumber = 4 ];
Property LabCode As %Library.String(MAXLEN = 64) [ SqlColumnNumber = 5, SqlComputeCode = { set {*}= $EXTRACT({LabName},0,3)_" "_{LabID}}, SqlComputed ];

00
Eduard Lebedyuk · Nov 5, 2019

If you want it stored, make it triggered computed like this:

Property LabCode As %Library.String(MAXLEN = 64) [
  SqlColumnNumber = 5,
  SqlComputeCode = { set {*}= $EXTRACT({LabName},0,3)_" "_{LabID}},
  SqlComputed,
  SqlComputeOnChange = LabName ];

Docs.

Note that it wouldn't recalc LabCode values for existing rows. If you need that, trigger recalculation with trivial update.

00
Vitaliy Serdtsev · Nov 6, 2019

$EXTRACTdoc

Keep in mind that

Characters are counted from 1.

, so instead

$EXTRACT({LabName},0,3)

should be

$EXTRACT({LabName},1,3)

In this case, it is uncritical, because

If the from value is 0 or a negative number, $EXTRACT returns a null string; however, if from is used with to, a from value of 0 or a negative number is treated as a value of 1.

But in other cases it can lead to the wrong result.

00