Sterling OMS Custom Table Creation

By | 02/15/2018

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

Sterling OMS Custom Table Creation

Requirement: Create custom table with given below data structure

XYZ_PERSON_INFO

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?

  1. From <Install Directory>/properties folder take a copy of sandbox.cfg as sandbox.cfg.original
  2. Search for following values in sandbox.cfg
    • LOAD_FACTORY_SETUP=true
    • NO_DBVERIFY=false
    • REINIT_DB=true
  1. 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)
  1. Go to <Install Directory>/bin in command prompt
  2. 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

  • Create new file install_dir/extensions/global/entities/Custom_Table_Demo.xml with below content

  • 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

How to test ?

Need to create 5 different services to perform database operations.

createXYZPersonInfo

createXYZPersonInfo

getXYZPersonInfo

getXYZPersonInfo

getXYZPersonInfoList

getXYZPersonInfoList

deleteXYZPerfonInfo

deleteXYZPerfonInfo

Insert Record : Using HTTP API tester call createXYZPersonInfo Service with below input

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

  1. 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
  2. How to generate Extended API’s automatically for custom table ? Answer : ApiNeeded=Y
  3. How to make sure audit records gets generated for any activity on custom table ? Answer : AuditRequired=Y
  4. How to generate history table (table_name_h) ? Answer : HasHistory=Y
  5. How to generate primary key for column ? Which datatype has to be used ? Answer : DataType=Key
  6. How to override or create new datatype ? Answer : Add new file  install_dir/extensions/global/etc/datatypes.xml
  7. How to create Unique Index ?

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.

Register with us to get more OMS updates

OMS Interview Questions

19 thoughts on “Sterling OMS Custom Table Creation

    1. admin Post author

      Thanks lot for your appreciation. Please keep sharing your feedback to improve us better.

      Reply
  1. Bala V S Satyam

    This is very important feature that every sterling OMS resource should know about. Well explained by ActiveKite.

    Reply
    1. admin Post author

      Thanks Bala for your comment. Please share you valuable feedback on each post.

      Reply
  2. Satheeshkumar Thangaraj

    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?

    Reply
    1. admin Post author

      For custom tables we have to write custom purge. Purge criteria has to be defined part of Criteria Parameter configuration (Agent Configuration)

      Reply
  3. Venkat

    Thank you so much

    This is very good information is provided here, this would be more helpful for learners.

    Reply
    1. admin Post author

      Thanks so much Venkat. Please share your feedback on each post. This helps every one. Happy learning !!!

      Reply
  4. ABC

    What all steps to be followed if I want to delete this custom table?

    Reply
    1. admin Post author

      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

      Reply
  5. Yogesh

    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

    Reply
    1. admin Post author

      Yogesh

      Thanks for your request. We will update few post with answer to your questions. Keep learning !!!

      Reply
  6. Navni

    This is very helpful, sometime you work with these commands without knowing exactly what they do, thanks for shedding light on them.

    Reply
    1. admin Post author

      Navni,
      Thanks for nice comment. Happy to know that our posting helps. These comments Keeps us motivated. Keep learning. Cheers !!!

      Reply
  7. Satheeshkumar Thangaraj

    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.

    Reply
    1. Satheeshkumar Thangaraj

      I got this working by setting the output template on the service itself and not via API tester.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *