Sterling OMS Custom Table Creation
In this post we are going to see how to do Sterling OMS Custom Table Creation
Sterling OMS Custom Table Creation
Requirement: Create custom table with given below data structure
Other Table Creation Requirements
1) Table should have history table
2) Table should have audit records for insert, update, delete operations
3) Table should generate API’s (CRUD Operations) automatically.
What is difference between Entitydeployer vs Dbverify?
Entitydeployer: Use this tool to build database extensions. The entitydeployer calls dbverify, applies all the SQLs generated by dbverify, and then rebuilds the entities.jar file.
Command : <Install Directiry>/bin/deployer.cmd -t entitydeployer
Note : Quick Question what does -t means in above line: Ant script target name from <Install Directory>\properties\deployer.xml
DBVerify: Dbverify performs database schema creation, verification, and correction. Dbverify is used to ensure database schema integrity. Dbverify is used during the following tasks:
- Installation
- Upgrade
- Customization
Command : <Install Directiry>/bin/dbverify.cmd
Lets learn about few installation properties values related to database
sandbox.cfg Parameter | What happens when entitydeployer command called | Remarks |
NO_DBVERIFY=false
REINIT_DB=false |
New resources.jar file gets created
DB Verify program called and DDL statements gets created New entities.jar gets created DB will NOT be updated with newly added column/changes |
From Development point of view this option is good. Make change in XML file get the DDL by running entitydeployer command and validate the changes and manually run the DDL on database. |
NO_DBVERIFY=false
REINIT_DB=true |
New resources.jar file gets created
DB Verify program called and DDL statements gets created New entities.jar gets created DB will be directly updated with DDL created part of DB verify program |
This option is good with controlled environment (Quality testing, User Acceptance testing) |
NO_DBVERIFY=true
REINIT_DB=false |
New resources.jar file gets created
DB Verify program NOT called and DDL statements not created New entities.jar gets created No change to DB because DDL statements not created |
Manually need to run dbverify.cmd to generate DDL statements and manually execute in the database. |
How to modify database related properties in sandbox.cfg?
- From <Install Directory>/properties folder take a copy of sandbox.cfg as sandbox.cfg.original
- Search for following values in sandbox.cfg
- LOAD_FACTORY_SETUP=true
- NO_DBVERIFY=false
- REINIT_DB=true
- First make change in sandbox.cfg file
- LOAD_FACTORY_SETUP=true (no change)
- NO_DBVERIFY=false (DDL scripts created automatically)
- REINIT_DB=false (Database update will not happen automatically; Have to run the DDL script manually)
- Go to <Install Directory>/bin in command prompt
- Run <Install Directory>/bin/setupfiles.cmd file
How to add new custom table
- Create new file install_dir/extensions/global/etc/datatypes.xml with below content
<?xml version="1.0" encoding="UTF-8"?> <DataTypes> <DataType Name="PersonRoleName" Size="20" Type="NVARCHAR"> <Enumeration> <Enum Description="" Value="Admin"/> <Enum Description="" Value="Developer"/> <Enum Description="" Value="Manager"/> <Enum Description="" Value="Team Lead"/> <Enum Description="" Value="Business Analyst"/> </Enumeration> </DataType> <DataType Name="Boolean" Size="1" Type="NCHAR"> <Enumeration> <Enum Description="" Value="Y"/> <Enum Description="" Value="N"/> </Enumeration> </DataType> <DataType Size="5" Name="Pincode" Type="NUMBER"> <Validation> <Regex MaxLength="200" MinLength="3" JavaPattern="^[a-zA-Z0-9.,!\-/+=:]*$" JSPattern="^[a-zA-Z0-9.,!\-/+=_ :]*$" allowNull="true"/> </Validation> </DataType> </DataTypes>
- Create new file install_dir/extensions/global/entities/Custom_Table_Demo.xml with below content
<DBSchema> <Entities> <Entity ApiNeeded="Y" AuditRequired="Y" Description="Custom Person Info" Prefix="XYZ" TableName="XYZ_PERSON_INFO" XMLName="PersonInfo" HasHistory="Y" KeyType="Random" > <Attributes> <Attribute XMLName="PersonInfoKey" ColumnName="XYZ_PERSON_INFO_KEY" DataType="Key" Nullable="false" DefaultValue=" " Description="Person Info Key" /> <Attribute XMLName="FirstName" ColumnName="FIRST_NAME" DataType="UserName" Nullable="false" Description="First Name" /> <Attribute XMLName="LastName" ColumnName="LAST_NAME" DataType="UserName" Nullable="false" Description="Last Name" /> <Attribute XMLName="EmpID" ColumnName="EMP_ID" DataType="Integer" Nullable="false" Description="Employee ID" /> <Attribute XMLName="Salary" ColumnName="SALARY" DataType="Money" Nullable="true" DefaultValue="0" Description="Employee Salary" /> <Attribute XMLName="RoleName" ColumnName="ROLE_NM" DataType="PersonRoleName" Nullable="false" DefaultValue=" " Description="Employee Role" /> <Attribute XMLName="DateOfBirth" ColumnName="DOB" DataType="Date" Nullable="false" Description="Employee DOB" /> <Attribute XMLName="Active" ColumnName="ACTIVE" DataType="Boolean" DefaultValue="Y" Description="Employee Status" /> <Attribute ColumnName="CREATETS" DataType="TimeStamp" DefaultValue="sysdate" Description="Create TimeStamp" /> <Attribute ColumnName="MODIFYTS" DataType="TimeStamp" DefaultValue="sysdate" Description="Modify TimeStamp" /> <Attribute ColumnName="CREATEUSERID" DataType="UserId" DefaultValue="' '" Description="Creating User ID" /> <Attribute ColumnName="MODIFYUSERID" DataType="UserId" DefaultValue="' '" Description="Modifying User ID" /> <Attribute ColumnName="CREATEPROGID" DataType="ProgramID" DefaultValue="' '" Description="Creating Program ID" /> <Attribute ColumnName="MODIFYPROGID" DataType="ProgramID" DefaultValue="' '" Description="Modifying Program ID" /> <Attribute ColumnName="LOCKID" DataType="Lockid" DefaultValue="0" Description="Lock ID" /> </Attributes> <PrimaryKey Name="XYZ_PERSON_IN_PK"> <Attribute ColumnName="XYZ_PERSON_INFO_KEY" /> </PrimaryKey> <Indices> <Index Name="XYZ_PERSON_INFO_I1" Unique="True"> <Column Name="EMP_ID" /> </Index> </Indices> </Entity> </Entities> </DBSchema>
- Run command from bin folder : deployer.cmd -t entitydeployer
- Query your database to see if column added? (No; because we updated the sandbox properties updated with REINIT_DB=false)
- Open file \repository\scripts\EFrame_TableChanges.sql
CREATE TABLE XYZ_PERSON_INFO_H ( XYZ_PERSON_INFO_KEY CHAR(24) NOT NULL ,FIRST_NAME VARCHAR2(50) NOT NULL ,LAST_NAME VARCHAR2(50) NOT NULL ,EMP_ID NUMBER(9) NOT NULL ,SALARY NUMBER(15,2) DEFAULT 0 NULL ,ROLE_NM VARCHAR2(20) NOT NULL ,DOB DATE NOT NULL ,ACTIVE CHAR(1) DEFAULT 'Y' NOT NULL ,CREATETS DATE DEFAULT sysdate NOT NULL ,MODIFYTS DATE DEFAULT sysdate NOT NULL ,CREATEUSERID VARCHAR2(40) DEFAULT ' ' NOT NULL ,MODIFYUSERID VARCHAR2(40) DEFAULT ' ' NOT NULL ,CREATEPROGID VARCHAR2(40) DEFAULT ' ' NOT NULL ,MODIFYPROGID VARCHAR2(40) DEFAULT ' ' NOT NULL ,LOCKID NUMBER(5) DEFAULT 0 NOT NULL ) / CREATE TABLE XYZ_PERSON_INFO ( XYZ_PERSON_INFO_KEY CHAR(24) NOT NULL ,FIRST_NAME VARCHAR2(50) NOT NULL ,LAST_NAME VARCHAR2(50) NOT NULL ,EMP_ID NUMBER(9) NOT NULL ,SALARY NUMBER(15,2) DEFAULT 0 NULL ,ROLE_NM VARCHAR2(20) NOT NULL ,DOB DATE NOT NULL ,ACTIVE CHAR(1) DEFAULT 'Y' NOT NULL ,CREATETS DATE DEFAULT sysdate NOT NULL ,MODIFYTS DATE DEFAULT sysdate NOT NULL ,CREATEUSERID VARCHAR2(40) DEFAULT ' ' NOT NULL ,MODIFYUSERID VARCHAR2(40) DEFAULT ' ' NOT NULL ,CREATEPROGID VARCHAR2(40) DEFAULT ' ' NOT NULL ,MODIFYPROGID VARCHAR2(40) DEFAULT ' ' NOT NULL ,LOCKID NUMBER(5) DEFAULT 0 NOT NULL ) / ALTER TABLE XYZ_PERSON_INFO_H ADD ( CONSTRAINT XYZ_PERSON_IN_H_PK PRIMARY KEY (XYZ_PERSON_INFO_KEY ) ) / ALTER TABLE XYZ_PERSON_INFO ADD ( CONSTRAINT XYZ_PERSON_IN_PK PRIMARY KEY (XYZ_PERSON_INFO_KEY ) ) /
- Run the above script in database and make sure new table created successfully
- Perform build and deploy the ear
How to test ?
Need to create 5 different services to perform database operations.
Insert Record : Using HTTP API tester call createXYZPersonInfo Service with below input
<PersonInfo FirstName="FirstName1" LastName="LastName1" EmpID="1" Salary="500" RoleName="Admin" DateOfBirth="20180210" Active="Y" />
Fetch Record : Using HTTP API tester call below Services with below input and template xml.
Service Name | Input XML | Template XML |
getXYZPersonInfo | <PersonInfo EmpID=’1′ /> | <PersonInfo /> |
getXYZPersonInfoList | <PersonInfoList /> | <PersonInfoList TotalNumberOfRecords=”> <PersonInfo /> </PersonInfoList> |
Interview Questions Related to Custom Table Creation
- Why extended database API can not be called directly ? Why to create services to access extended database API ? Answer : YIFApi interface does not extend APIs for custom/hang-off tables
- How to generate Extended API’s automatically for custom table ? Answer : ApiNeeded=Y
- How to make sure audit records gets generated for any activity on custom table ? Answer : AuditRequired=Y
- How to generate history table (table_name_h) ? Answer : HasHistory=Y
- How to generate primary key for column ? Which datatype has to be used ? Answer : DataType=Key
- How to override or create new datatype ? Answer : Add new file install_dir/extensions/global/etc/datatypes.xml
- How to create Unique Index ?
<Indices> <Index Name='XYZ_PERSON_INFO_I1' Unique='True'> <Column Name='EMP_ID' /> </Index> </Indices>
Conclusion : This post helps to create new custom table into existing OMS database easily. Please share your feedback at support@activekite.com OR comment in the post.
good topic.
Thanks Ravi for your comments.
Well written 👍🏻
Thanks lot for your appreciation. Please keep sharing your feedback to improve us better.
This is very important feature that every sterling OMS resource should know about. Well explained by ActiveKite.
Thanks Bala for your comment. Please share you valuable feedback on each post.
Thanks for the nice & informatice topics. I have a question on this topic.
1.How do you define purge criteria for Custom tables ?
Does Sterling provides configuration options for this? Or Should purging data from custom tables be part of custom implementation?
For custom tables we have to write custom purge. Purge criteria has to be defined part of Criteria Parameter configuration (Agent Configuration)
Thank you!
Can you please post an article on how to write custom purge for custom table ? It would be really helpful.
Thank you so much
This is very good information is provided here, this would be more helpful for learners.
Thanks so much Venkat. Please share your feedback on each post. This helps every one. Happy learning !!!
What all steps to be followed if I want to delete this custom table?
Thanks lot for your question. To remove custom table or extended column
1) First remove the entry from entity XML (extensions\global\entities)
2) Do entity build
3) Based on the configuration script will generated to remove the custom table / new column
4) Run the DDL script into DB
5) Create new smscfs.ear
6) Deploy the EAR
7) New table or added column removed
Note: Even after we remove the custom table entities.jar has reference to custom table. Old Class files not completely removed from entities.jar
Hope this helps !!! Happy Learning
Hi Team,
Can you also explain the below topics:
1. sourcing and scheduling
2. How to expose webservice in OMS
3. How to write complex query
4. How to read attribute value from Java Code for TaskQ or non-TaskQ table
Yogesh
Thanks for your request. We will update few post with answer to your questions. Keep learning !!!
This is very helpful, sometime you work with these commands without knowing exactly what they do, thanks for shedding light on them.
Navni,
Thanks for nice comment. Happy to know that our posting helps. These comments Keeps us motivated. Keep learning. Cheers !!!
Is there a way to get total number of records that match input criteria when using get List API for any custom/hang-off table? like we have for OOB List Apis (getOrderList/getItemList/getShipmentList).
I tried by setting TotalNumberOfRecords in output template but it didn’t work.
I got this working by setting the output template on the service itself and not via API tester.
Thanks a lot for this post. When we create a new Extension table, where will these API’s store? I believe we will have 5 API’s(create, modify, get, change, delete) generated when we have this new table created.
these api created part of entities.jar file. we don’t believe this information stored in any table.
Cann someone help me to understand this……As we made the changes for existing table or creating new table will ru n entitydeployer command but after executing entitydeployer command why New resources.jar file will create…??
What is the Input to delete a Row from Custom Table
To delete row you will be calling custom service created for delete. internally service configured with Extended database API component with delete API. Input for this request will be Primary key for the row delete or unique values which helps to identify the row.
For example,
In Student table if you had student id as primary key and email address as unique key
Above both can be used as input xml for delete. Hope this helps !!!
Happy learning !!!
How can we deploy custom tables using the CDT ?
Yes we can. Rename file\resources\ydkresources\cdt_custom.xml.sample to \resources\ydkresources\cdt_custom.xml and add your table name on this xml file.