Shared Connection Info

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

Leave a Comment