I am currently working on a solution where the datafile supplied by the client requires some complex re-ordering. One of the approaches I’m deliberating is to use sqlite as an intermediary so I can store the records temporarily and then pull them back out in the sequence required. The repository API is not extensive enough for my needs as I’d like to build the table dynamically from a data dictionary whilst honouring the correct datatypes for each field (otherwise ordering is going to fail for integers, dates and floats).
Given that the repository API is essentially a sqlite wrapper, I wondered whether I could access the sqlite library directly from a javascript in the workflow rather than go via the repository API. Ideally I don’t want to start adding dependencies or installing additional libraries if what I need is already in the product somewhere.
Alternatively, if I’m forced down the repository API route, is it possible to inject function calls into the WHERE clause (eg. ‘CAST(sequenceID AS int) ASC’)?
You can use the Workflow’s SQLLite engine to create a separate repository that will allow you to store whatever information you want without impacting Workflow’s native repo.
To do that, use a script to create a different repository. For instance:
var repo = new ActiveXObject("RepositoryLib.WorkflowRepository");
repo.ConnectionString = "D:\\Test\\MyRepo.Repository";
var today = Date.now().toString();
var users = [
{
FirstName: "John",
LastName : "Doe",
UserID : 14,
Date : today
},{
FirstName: "Jane",
LastName : "Smith",
UserID : 5,
Date : today
}
]
repo.AddGroup("MyTable", '["FirstName", "LastName", "UserID", "Date"]');
repo.AddKeySets("MyTable", JSON.stringify(users));
So now you have a repo stored in D:\Test\MyRepo.Repository that contains a single table named MyTable. The table in this sample script is populated with 2 records. All fields are treated as strings, even though the objects used to create them have different native types (number, date, etc.).
To access this repo with SQL statements, you can then use Workflow’s Database Query task. You will need to first install the SQLLite ODBC-32 driver. Then in the task, you can use any SQLLite-compatible keyword (and CAST happens to be one of them) in your SQL statement:
This can obviously be used in a WHERE clause as well:
Select * from MyTable where CAST(UserID AS int) > 10
By the way, if you are comfortable with SQL statements, you can create your tables and fields with SQL statements with the Database Query task. That would allow you to specify a type for each column.
You still need to create a separate repository with a script:
var repo = new ActiveXObject("RepositoryLib.WorkflowRepository");
repo.ConnectionString = "D:\\Test\\MyRepo.Repository";
which creates an empty database, and then use the DB Query task to populate it:
CREATE TABLE IF NOT EXISTS MyTable (
FirstName varchar, LastName varchar, UserID int, Date date
);
INSERT INTO MyTable VALUES
("John", "Doe",14,date()),
("Jane", "Smith",5,date()) ;
