Treasures of Cache 2010

Ian Cargill

Development Manager

Dendrite Clinical Systems

Before We Start

• Terminal Window Tricks

• ! To run an OS Shell command

• D $System.SQL.Shell()

• q(uit) to exit back to terminal

New Dynamic SQL (New in Caché 2010)

• This is based on the new %SQL.Statement and %SQL.StatementResult (and related) classes.

• %SQL.Statement - implements an interface to prepare and execute dynamic SQL statements.

• %SQL.StatementResult - Every dynamic statement execution returns a result object.

New Dynamic SQL

• Chapter in online Documents: "Using Dynamic SQL"

• Superficially similar to %ResultSet (%Prepare, %Execute, etc), but many additional features and advantages.

• Can set DisplayMode as argument to %New• More flexible Prepare of query (e.g. can pass

multiline query as array!)• %ExecDirect combines %Prepare and

%Execute• %Display method (for debug)• %Print (with optional delimiter) - e.g. to

produce CSV files•  

• but as Louise is doing an entire session on this - that is all I will say…

%SYSTEM.Encryption

• This class provides class functions to perform data encryption, Base64 encoding, hashing, and generation of message authentication codes.

• Some complex encryption support for experts, but a number of useful applications for the more humble programmer:

%SYSTEM.Encryption

• Encrypting passwords for storage– e.g. MD5Hash, SHA1Hash

• Encoding binary data for 'plain text' transmission. E.g. SOAP messages.– Base64Encode– Base64Decode

• ClassMethod Base64ToFile(ByRef Data64 As %String, FileName As %Library.String) As %Library.String

{Set res="OK"try {Set dat=##class(%SYSTEM.Encryption).Base64Decode(Data64)

Set file=##class(%File).%New(FileName)Do file.Open("WN")Do file.Write(dat)Do file.Close()Kill file

} catch {Set res=$ZError

}    Quit "OK“

• }

GUIDs• If a persistent class is marked as

GUIDENABLED, Caché will assign Globally Unique IDentifiers (GUIDs) to each object when it is created. A later call to delete the object via %Delete on an object will no longer delete the GUID for that object.

• There is history in the GUID global in each namespace where a GUIDENABLED object has been created. Users are responsible for removing entries from ^OBJ.GUID that are no longer needed.

GUIDs• Primarily for Synchronization. But can be

other uses. ^OBJ.GUID(0,

"B43096CB-D24E-4A79-9A3D-D998E56C6B78")=$lb("4","Guid.Test")

"B810DCE6-5BE9-4CC7-8E37-38A281BA2B67")=$lb("5","Guid.Test")

"FE3D5287-03D6-4E84-AD46-6E027506A6F9")=$lb("6","Guid.Test")

^OBJ.GUID(1,

$lb("4","Guid.Test"))="B43096CB-D24E-4A79-9A3D-D998E56C6B78"

$lb("5","Guid.Test"))="B810DCE6-5BE9-4CC7-8E37-38A281BA2B67"

$lb("6","Guid.Test"))="FE3D5287-03D6-4E84-AD46-6E027506A6F9"

• Class Guid.Test Extends %Persistent{Parameter GUIDENABLED = 1;

Property Name As %Library.String [ SqlColumnNumber = 2 ];

Property GUID As %Library.String [ Calculated, SqlComputeCode = { SET {GUID}=##class(Guid.Test).GetGUID({ID})}, SqlComputed, SqlColumnNumber =3 ];

Method GUIDGet() As %Library.String{    Set key=..GetGUID(..%Id())}

• ClassMethod GetGUID(Id As %Library.String) As %Library.String{    Set key=$lb(Id,"Guid.Test")    Quit ^OBJ.GUID(1,key)}}

GUID

• Add three rows and SELECT * FROM Guid.Test gives:

• # ID Name GUID1 1 61600,69382 7A0429A7-C555-4025-9DB2-CCF11AB6BCB12 2 61600,69479 9480278C-D784-484C-AD33-A90E31D821543 3 61600,69568 54720852-6496-44FB-AEF1-DBE6A6FD0662

Referential Integrity on ID field• Post Meeting Update:

After the meeting, I raised this with InterSystems, and it seems there IS a way to do it, but no-one knew!!

You can’t use the Wizard, but write the FK by hand, and simply OMIT THE INDEX NAME from the final argument. I.e.

• ForeignKey FKey(FieldName) References Sample.Parent()

Referential Integrity on ID field • Some parts of ObjectScript are definitely

brain-damaged. One such is how you define Referential Integrity.

• Typical Database: e.g. SQL Server

• ADD CONSTRAINT FK_XX FOREIGN KEY (ORDERNUMBER) REFERENCES DBO.ORDERHEADER(ORDERNUMBER)

>> TABLE(FIELD)

Referential Integrity

• You define RI in a child class as, say…

• ForeignKey FKey(FieldName) References Sample.Parent(IName)

• Where Iname is the name of an index in IndexName in the parent class.

• Question? What is the name of the index on the default ID field??

Referential Integrity

• Because ID doesn't have an explicit index, you CANNOT directly reference the Primary Key field of the parent!!!

• Only in Caché !!• There is, however, a workaround,

based on the fact that you can index calculated fields. Try this…

• Class Reference.Parent Extends %Persistent{Index IPKId On PKId [unique];

Property PKId As %Library.Integer [ Calculated, SqlColumnNumber = 2,

SqlComputeCode = { SET {PKId}={id}}, SqlComputed ];

Method PKIdGet() As %Library.Integer [ CodeMode = expression ]{ ..%Id()}Property ThisProperty As %Library.String;

Property ThatProperty As %Library.Integer;

}

Class Reference.Child Extends %Persistent{

Property MyParentId As %Library.Integer;

ForeignKey FkParent(MyParentId) References Reference.Parent(IPKId);

Property ThisProperty As %Library.String;

Property ThatProperty As %Library.Integer;

}

$ZOBJ<xxx> Functions Replaced • The $ZOBJ<xxx> functions have been

replaced with standard Caché functions. The $ZOBJ<xxx> functions are still available to applications, but they are no longer documented.

• The calling sequences for the replacement functions remain the same as before; a textual substitution is all that is required to update the application usage. The replacements are given in the following table:

Was Now use$ZOBJCLASSMETHOD $CLASSMETHOD$ZOBJMETHOD $METHOD$ZOBJPROPERTY $PROPERTY$ZOBJCLASS $CLASSNAME

•  • Note: The function, $ZOBJCLASS,

was not documented in prior versions.

$ZOBJ<xxx> Functions Replaced

Old style

Set oDat=$ZObjClassMethod(Class, "%New")

Set oDat=$ZObjClassMethod(Class, "%OpenId", ObjId)

Set CurrentVal=$ZOBJPROPERTY(oDat, FldName)

Set $ZOBJPROPERTY(oDat, FldName)=NewValue

New Style

Set oDat=$CLASSMETHOD(Class, "%New")

Set oDat=$CLASSMETHOD(Class, "%OpenId", ObjId)

Set CurrentVal=$PROPERTY(oDat, FldName)

Set $PROPERTY(oDat, FldName)=NewValue

Private Classmethods Now Supported

• Caché now supports private class methods. Private classmethods cannot be called from outside of the class (or a subclass).

• For backward compatibility, all class methods have been marked “public” so customer code will continue to work correctly.

• The descriptions of these methods have been altered to add the string, “[Previously private]”, to them.

• New private classmethods added to classes in the future will be inaccessible from outside the class.