Best way to create database tables/columns from Thingworx

[STullberg]

What is the best practice way to create/design external database tables and columns in Thingworx?

BR/ Simon

[Arun_C]

Hi @STullberg ,

If you want to create the external tables using TWX - SQL Command in Database Thing means kindly find the below given sample SQL Code. You can modify it based on you requirement.

IF OBJECT_ID('dbo.DemoExternalTable_T', 'U') IS NULL
CREATE TABLE DemoExternalTable_T (
uid int IDENTITY NOT NULL
,Order_Number varchar(100)
,Ordered_Quantity int
,Actual_Start_Date datetime
,Actual_End_Date datetime
,Comments varchar(max)
,Status bit
,Sequence_Number float
,PRIMARY KEY (uid));

Also kindly find the attached link FYR. It may gives you basic understanding of how to utilize and incorporate an external database with the ThingWorx Platform.

Working with External Databases - Part 1 and Part 2 

Thanks & Regards,

Arun C

[STullberg]

Thank you for your answer. But is that the best way to create a database table/columns when working with Thingworx? To just script the whole database? Or can you for example create datashapes and use some Thingworx functionallity to create your database from them?

[Arun_C]

Hi @STullberg , 

As per my experiance, If we want to create the tables in external database thorugh TWX, We have to define the table schema becasue the BaseType of TWX datashape feilds may not be same in external database.

The example which you have given based on datashape, we can able to create the custom JS service code but in this case also we have to design table schema also if required primary key feild. If you have mulitple feilds as primary key in datashape the code will fail.

// result: INFOTABLE dataShape: "FieldDefinition"
let dataShapeInfo = DataShapes[IP_DS_Name].GetFieldDefinitions();
var inpBaseType = "";

function getExtTableBaseType(inpBaseType) {
	var extbaseType = "";
	switch (inpBaseType) {
		case 'NUMBER':
			extbaseType = 'float';
			break;
		case 'INTEGER':
			extbaseType = 'int';
			break;
		case 'STRING':
			extbaseType = 'varchar(max)';
			break;
		case 'INFOTABLE':
			extbaseType = 'varchar(max)';
			break;
		case 'DATETIME':
			extbaseType = 'datetime';
			break;
		case 'BOOLEAN':
			extbaseType = 'bit';
			break;
		default:
			extbaseType = 'varchar(500)';
	}
	return extbaseType;
}
var primaryKeyField = "uid";
var primaryKeyType = "int";
var a = 0;
var fieldCreate = "";
for (; dataShapeInfo.length > a; a++) {
	var dataShapeInfoRows = dataShapeInfo.rows[a];
	var baseType = dataShapeInfoRows.baseType;
	var name = dataShapeInfoRows.name;
	fieldCreate = fieldCreate + " " + name + " " + getExtTableBaseType(baseType) + ",";
}
var finalCode = "IF OBJECT_ID('dbo." + IP_Ext_TableName + "', 'U') IS NULL CREATE TABLE " + IP_Ext_TableName + "(" + primaryKeyField +" "+ primaryKeyType +" IDENTITY NOT NULL,"+ fieldCreate + "PRIMARY KEY (" + primaryKeyField + "));";

result = finalCode;

Note: Its an sample JS code with MSSQL database fields it will be different for other database. Need to be careful in primary key, foreign key setup also with sql feilds in service.

Output :

If till you have any other queries means we will wait for other experts suggestions.

Thanks & Regards,

Arun C

[VladimirRosu · Accepted solution]

Note we usually create the tables directly in the SQL editor, and then just use the SQL statements to do Read/Update/Delete.

Trying to understand better, do you have an use-case where it's needed to create dynamically tables, to create too many of them or they have a large column count that manual creation is not doable? I usually find out that in many cases creating manually stuff takes less time than implementing a service (here applies the usual "if you need to do it three times then automate it" )