Connect to other databases

Sometimes when working in OScript, you need to connect to a database other than the default Livelink one. There are some methods, such as SQL Server's Linked Servers concept, that can provide access within the database tier, this code snippet shows you how to connect to another database.

To do this we will use the $DBWizAPI.ConnectPkg.Connect function, this function takes parameters as follows :

Parameter Comments
Connection Type This is the constant for your database type, the constant name can be found in the $LLIAPI.DbInfoPkg.MapServerTypeFromString function, or if it is the same type as your Livelink database you can use the following $LLIAPI.DbInfoPkg.MapServerType($LLIAPI.DbInfoPkg.Findrec($Kernel.SystemPreferences.GetPrefGeneral( 'dftConnection' ))).
Server Name For SQL Server databases this is the server name, for Oracle databases this is the TNS entry for the database.
Database Name This is the name of the database that you wish to connect to.
Username This is the username that should be used to connect to the database.
Password This is the password for the above username.

Putting this together into some OScript we could have something like the following :

				dynamic cnctVal1 // dynamic storing the database connection

				// This code assumes that the DB is the same type as the Livelink DB
				// Parameters are as follows :
				//	ConnectionType 	Integer Constant
				// 	Server Name 	String e.g. myOracleServer
				//	Database Name 	String e.g. myDB
				//	Username	String e.g. myUser
				// 	Password	String e.g. myPassword
				cnctVal1 = $DBWizAPI.ConnectPkg.Connect(\
				    $LLIAPI.DbInfoPkg.MapServerType($LLIAPI.DbInfoPkg.Findrec($Kernel.SystemPreferences.GetPrefGeneral( 'dftConnection' ))),\
				    'myOracleServer',\
				    'myDB',\
				    'myUser',\
				    'myPassword' )
			
We can now use this function as our connection object to execute SQL as you would normally do. Once we have finished using the database connection we will need to close it correctly, this can be done using the following code :
				// disconnect from the DB
$DBWizAPI.ConnectPkg.Disconnect(cnctVal1.connect)
Website Designed by Adservio Consulting Valid HTML 4.01 Strict    Valid CSS!    Level A conformance icon, W3C-WAI Web Content Accessibility Guidelines 1.0