One of the common problems in a production environment relies on having a shared connection information between all of the scripts that operate in that environment. In searching for a solution, I developed the following approach for our maintenance scripts written in VBScript.
For all connection information, a common XML file is created that stores all connection information. This file is then protected using the appropriate controls within the operating system. The structure is similar to a App.config for Web.config file for most .Net solutions.
<?xml version="1.0"> <appSettings> <!-- db connection settings --> <rmdbconnection key="rmDbConnection" value="DSN=MART;UID=userDatabase;PWD=userPassword;" /> </appSettings>
Then, from the VB script, the database connection can be retrieved via the following function:
Function retrieveDBConnection( sPasswordFile, sNodeName ) 'parse dbpassword file and get connectivity info Set objXML = CreateObject("MSXML2.DOMDocument") objXML.Async = False objXML.Load(sPasswordFile) 'find root node <appSettings> Set node = objXML.documentElement.selectSingleNode("/appSettings") ' check to see that the root node was found, if not throw a quit if node is nothing then WScript.Quit 1 else for each xNode in node.childNodes if xNode.nodename = sNodeName then Dim atttr for each attr in xNode.attributes 'cycling through each attribute name, find the 'value' attribute if attr.name = "value" then 'return the found 'value' attribute retrieveDBConnection = attr.text end if next end if next end if End Function
The above can then be called via the snippet:
'validate existence of password file if filesys.FileExists(sPasswordFile) then WScript.Echo FormatDateTime(Now(),0) & vbTab &"Password file found, proceeding.." 'parse dbpassword file and get connectivity info sODBC = retrieveDBConnection(sPasswordFile, "rmdbconnection" ) else WScript.Echo FormatDateTime(Now(),0) & vbTab &"Missing required password file for database connectivity." WScript.Quit 1 end if