Wednesday, April 8, 2009

Script your ODBC Data Sources (MySQL)

Ok... admittedly this has to do with Windows scripting and not Java nor Javascript but is something that affects many of my projects. If your computer needs ODBC Data Sources, you can script their creation (particularly useful for configuring new developers' computers).

This script should provide a fairly useful start to customizing your own version. I am going to assume that it is fairly self-explanatory but you can always post a response if you do not agree or have a question.


'================================================================
' This script creates one or more ODBC Data Sources.

' Usage: >cscript crystal.odbc.test.vbs
'
' Notes:
' * Search for 'configure-here' for all customization options
'================================================================
Const HKEY_LOCAL_MACHINE = &H80000002

strPrefix = "winmgmts:{impersonationLevel=impersonate}!\\"
strComputer = "."
strSuffix = "\root\default:StdRegProv"
strObjRef = strPrefix & strComputer & strSuffix ': wscript.echo strObjRef
Set objReg = GetObject(strObjRef) 'The Set keyword is mandatory

DriverNames = Array("SQL Server", "MySQL ODBC 3.51 Driver") 'Generally, do not modify these
Drivers = Array("C:\WINDOWS\System32\SQLSRV32.dll", "C:\WINDOWS\system32\myodbc3.dll") 'Generally, do not modify these
'================================================================
' Configuration Section
'================================================================
Const Chosen = 1 'configure-here: Zero-based index into the following two arrays; i.e. which DB are you using
Const Server = "xxxwebtest" 'configure-here: the machine where the DB is hosted
Const Port = "3307" 'configure-here: the port where the DB is hosted
Const Uid = "root" 'configure-here: the username to the DB
Const Pwd = "admin" 'configure-here: the password to the DB
ConnectionList = Array("dbone", "dbtwo", "etcetera") 'configure-here: see Note 1 below

REM *** Note 1 *** This is the array of ODBC Names which you will be creating;
REM also is the name of the database which the odbc connects to
'=== End : Configuration Section ================================

for each connection in ConnectionList
wscript.echo "*** Start *** Creating ODBC Data Source: " & connection

' *** Step 1 *** Create new Data Source Entry in system folder ...\ODBC Data Sources
strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
strValueName = connection
strValue = DriverNames(Chosen)
call WriteToRegistry

' *** Step 2 *** Create Data Source configuration in new user-defined folder {connection}
strKeyPath = "SOFTWARE\ODBC\ODBC.INI\" & connection
objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath

' *** Step 2a *** Database
strValueName = "DATABASE"
strValue = connection
call WriteToRegistry

' *** Step 2b *** Driver
strValueName = "DRIVER"
strValue = Drivers(Chosen)
call WriteToRegistry

' *** Step 2c *** Server
strValueName = "SERVER"
strValue = Server
call WriteToRegistry

' *** Step 2d *** Port
strValueName = "PORT"
strValue = Port
call WriteToRegistry

' *** Step 2e *** UID
strValueName = "UID"
strValue = Uid
call WriteToRegistry

' *** Step 2f *** PWD
strValueName = "PWD"
strValue = Pwd
call WriteToRegistry

' *** Step 2g *** Trusted_Connection : SQL Server only?
strValueName = "Trusted_Connection"
strValue = "Yes"
' call WriteToRegistry

' *** Step 2h *** Description
strValueName = "DESCRIPTION"
strValue = "EditMeThroughRegEdit"
call WriteToRegistry

wscript.echo "*** Finished *** Creating ODBC Data Source.\n"
next 'connection
'===== End of Script =====

'================================================================
' Subroutines and Functions
'================================================================
Sub WriteToRegistry
Const debugDesired = false
If debugDesired Then
wscript.echo "Simulating name/value to: HKEY_LOCAL_MACHINE\" & strKeyPath & ": " & strValueName & "/" & strValue
Else
wscript.echo "Modifying name/value to: HKEY_LOCAL_MACHINE\" & strKeyPath & ": " & strValueName & "/" & strValue
objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
End If
End Sub

No comments: