At the moment, both of my Metastorm BPM clients have multiple Metastorm servers running on different physical servers that operate as independent Development, QA (or test) and Live systems. Now, this isn’t so rare, most companies that implement Metastorm BPM environments have this type of setup. You will generally also have three Metastorm databases running for these three servers and if you’re like both my current clients, all three of those Metastorm databases run on the same Sql Server Instance and as such are named differently. If you have for example MetastormDev, MetastormQA, MetastormLive databases setup then accessing the data in these from one single procedure file isn’t a big hassle as the procedure just references a DSN (data source name) that is setup during installation. This DSN is called Metastorm by default, so provided the procedure references that name in it grids, sql executions etc, then all is well.
When you are creating server side scripts you can still reference this DSN name by using the built in ework.SelectSQL or ework.ExecSQL functions via the script designer. You would create a SQL statement as you normally would in Metastorm Designer and then specify the DSN properties in the second parameter like this :
var sql : String = “SELECT myColumn FROM myTable”
ework.SelectSQL(sql,”DSN=Metastorm;UID=;PWD=”) //Uses windows authentication hence no Sql Server password
The above illustrates a simple select of a single column, but take the scenario of selecting multiple columns of a database that you want to apply a JScript.NET for loop to and loop each record performing logic such as raising a flag or checking a secondary data source. SelectSQL is no good for this unless you want to start breaking the data down into sub strings and use arrays.
It is far easier to use ADO.NET in your server side script to load the SQL data into a dataset and loop that dataset, referring to the columns by name in your for loop statements. I always much prefer the use of an in memory dataset for column/row manipulation. The issue that arises using this method however is that we will need to specify a database connection string to pass to the SqlDataAdapter class constructor when you are filling your data set. But what is the database name? Our DSN becomes of little use to us now.
The answer, or at least the method I normally use is to read the Metastorm registry keys for a database value for the server you are running the procedure on. Specifically we want to grab the local machines sub key value at this location : \\HKEY_LOCAL_Machine\SOFTWARE\ODBC\ODBC.INI\Metastorm\Database. Here is a JScript.NET method that checks the local servers Metastorm database name (click for full size image):
For those who would want to implement this method in C# (i.e. for supporting utility assemblies that you can place in the Metastorm engines dotnetbin server folder):
You can call these methods from anywhere in your server side code and have a string return naming the database being used by the current Metastorm server.