Metastorm BPM : Dynamically determine the Metastorm database name


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.

Advertisements

9 comments

  1. You can also get this from the database:
    “select eDatabaseType from eServer”

    It is another database call, but it is not noticeable one you are accessing the database we find.

  2. Terrific work! That is the kind of information that are meant to be shared around the web.
    Shame on the seek engines for not positioning this publish upper!

    Come on over and visit my site . Thank you =)

  3. Hello, I just dropped by to look at this site. It appears really full of great content
    and I enjoyed browsing it, thank you very much for the great post!

  4. Hi, I do think this is an excellent site. I stumbledupon it 😉 I will revisit once again since i have
    bookmarked it. Money and freedom is the best way to change, may you be rich and continue to guide others.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s