Utilize custom datatype class for User-defined DDL table Creation |

Article

Ashok Kumar · Aug 31, 2023 2m read

#Databases #Multi-model #Tutorial #HealthShare #InterSystems IRIS #InterSystems IRIS for Health

In this article, I am demonstrating how to create a table column(formerly known as properties) with your custom datatype classes by using User defined DDL. Properties are the crucial member of the persistent class definition. Datatypes are essential to define types of values that are stored in a table column. In general, the datatype names of SQL different from Intersystems datatypes, such as VARCHAR = %String. you have the ability to create or modify a table by using these datatypes. Indeed, you’re creating the tables through DDL and you have your own datatype classes are already defined. Those datatypes are intent to use when execute create/alter in your class definition instead Intersystems standard Datatypes. It's pretty straightforward to use by follow the below steps

Login to the management portal. Navigate to System > Configuration > User-defined DDL Mappings. Create a new user-defined DDL mapping. Add the name of the SQL standard datatype to be used in SQL queries. Incorporate your own datatype class in the Datatype. Press the save button. It's ready for use in DDL now!

Create/Use your dataype class

Class Samples.DT.BoolYN [ ClassType = datatype ]
{
/// Implementation are done
}

Execute the SQL query. - The created user-defined DDL datatype in IsActive field

CREATE TABLE Employees (
       FirstName VARCHAR(30),
       LastName VARCHAR(30),
       StartDate TIMESTAMP,
       IsActive BIT(%1)
   )

Once the query executed. The class definition is created with own datatype class

/// 
Class Sample.Person Extends %Persistent [ ClassType = persistent, DdlAllowed, Final, Owner = {_SYSTEM}, ProcedureBlock, SqlRowIdPrivate, SqlTableName = Person ]
{

Property FirstName As %String(MAXLEN = 50) [ SqlColumnNumber = 2 ];
Property LastName As %String(MAXLEN = 50) [ SqlColumnNumber = 3 ];
Property StartDate As %Library.TimeStamp [ SqlColumnNumber = 4 ];
Property IsActive As Samples.DT.BoolYN [ SqlColumnNumber = 5 ];
Parameter USEEXTENTSET = 1;
/// Bitmap Extent Index auto-generated by DDL CREATE TABLE statement.  Do not edit the SqlName of this index.
Index DDLBEIndex [ Extent, SqlName = "%%DDLBEIndex", Type = bitmap ];
}

You can use these same user defined DDL names in Intersystems IRIS Datatype DDL execution

CREATE TABLE Sample.Person (
FirstName %String(MAXLEN=50),
LastName %String(MAXLEN=50),
StartDate %TimeStamp,
IsActive BIT(%1)
)