How to create new table programmatically using REST API?

rajivnalawade · ‎01-09-2017

Hi,

I've created a service application.

While I am able to use the ServiceNow administration platform to create new table(s) for my application, I want to do the same programmatically.

Can I create a new table in system using REST API or any alternate API?

While I could find a discussion thread giving way to get details of an existing table (How to get table schema programatically via API or processor? ), I couldn't find anything serving my purpose.

Appreciate if one can share anything in this direction.

Thanks,

Rajiv.

srinivasthelu · ‎01-10-2017

Hi Rajiv,

There might be ways doing that but to me that does not sound standard.

First of all, Ideas to do that,

1. by searching script includes quickly, I found below function which you can use pretty much.

createTable: function(/*String*/ tableLabel, /*String*/ tableName, /*String?*/ parentTableName, nonSystemExports) {

// Update mode - ensure the table does not already exist

if (this.tableExists(tableName))

return;

var creator = new TableDescriptor(tableName, tableLabel);

if (parentTableName && !parentTableName.nil()) {

creator.setExtends(parentTableName);

}

creator.create();

if (parentTableName == 'sf_sm_flow')

this.addUpdateSynch(tableName, parentTableName);

if (nonSystemExports != undefined)

this._exportTable(tableName, nonSystemExports);

},

2. Create a processor with the above function and call it through REST.

why do i think it is not standard?

1. Dynamic ever changing Logical data model ? , Sure, BIG NO.

2.There are always better ways to use single generic table to handle such scenarios. For example, if you wanna create tables to store different animals listings (each animal in a different table), I would rather create a single table and have animal type as a attribute.

rajivnalawade · ‎01-21-2017

Thanks Srini!

I'll try this and let you know if any issues.

I am trying to see how I can integrate my application with serviceNow.

I want to see if I can export my existing schema to serviceNow using some auto-mechanism (not using GUI) like scripts (like Perl), program (Java) or something similar.

Hence, I am trying to find if there is some way to create tables such as:

table Department {

       number deptID;

       string deptName;

};


table Employee {

       number employeeID;

       string empName;

       datetime joinDate;

       reference deptID;         // foreign reference key on table Department

       choice designation;

       ....

}

Hope this explains my need for programmable approach for table creation.

Anyway, the example above may be able to help me create a table. But it doesn't show how to create a column in the table. Can you extend your help on that too?

I have been trying to go through ServiceNow (wiki) documentation under Script Includes, Server side scripts and Server Side APIs, but couldn't find anything that helps me with this purpose.

Can you suggest particular API or section to explore for help?

Thanks,

Rajiv.

rajivnalawade · ‎01-22-2017

I guess, I found answer to one of my questions above. Realizing that TableDescriptior above is Script Include, I tried to explore it's API.

However, when I tried to use TableDescriptor from my processor, it's not behaving as expected.

Here is my Processor script:

(function process(g_request, g_response, g_processor) {


   g_processor.writeOutput("text/plain", "In processor;");

   var tableName = g_request.getParameter("tableName");

   g_processor.writeOutput("text/plain", "Table Name = " + tableName + ";");

   var tableLabel = "dummyTabLabel";

   g_processor.writeOutput("text/plain", "TableDescriptor;");

   var td = new Global.TableDescriptor(tableName, tableLabel);

   g_processor.writeOutput("text/plain", "FieldDescriptor;");

   var fd1 = new Global.FieldDescriptor("col1");

   g_processor.writeOutput("text/plain", "addField;");

   td.addField(fd1);

   g_processor.writeOutput("text/plain", "create;");

   td.create();

   g_processor.writeOutput("text/plain", "Done!");


})(g_request, g_response, g_processor);

When invoked from my perl client as:

 $client->GET("/CreateTable.do?tableName=$table",

                               {'Authorization' => "Basic $encoded_auth",

                                 'Accept' => 'application/json'});



 my $status = $client->responseCode(); 

 my $response = $client->responseContent();


 print ' Response: ' . $response . "\n";

 print ' Response status: ' . $status . "\n";

This is the output

 Response: In processor;Table Name = dummyTable;TableDescriptor;

 Response status: 200

As we can see, the script doesn't seem to progress beyond the TableDescriptor instantiation.

The behavior didn't change even I exclude Global namespace qualifier to TableDescriptor.

Even when I tried to instantiate FieldDescriptor before TableDescriptor, I noticed the same thing - now script started failing at FieldDescriptor.

In fact, tried a few other script includes (such as ActionUtils and ExportWithRelatedLists), but found script failing at instantiation of any of these script includes.

Tried one of the existing processors , which uses ExportWithRelatedLists, and I didn't see this processor facing any issue. Used from my processor, ExportWithRelatedLists instantiation has been failing the script.

Going by all these experiments, I believe something is blocking the processor that I've created from using the global script includes.

Can you suggest if I need to check / correct anything (such as scope) with my processor?

Thanks,

Rajiv.