ODBC connection setup

Hi Morgan,



The 'vbscript create system DSN' google request returned quite a few results. The first two of them were

http://blogs.technet.com/b/heyscriptingguy/archive/2004/11/10/can-i-create-and-delete-a-dsn-using-a-script.aspx

and

http://www.visualbasicscript.com/Create-an-ODBC-system-DSN-for-connecting-m39361.aspx



I did not check if they operate as expected, but hopefully they will provide you with the general idea at least.

Here is the procedure I use to configure a DSN. Hope this helps. (Names changed to protect the guilty. ;) )



Sub ConfigureDsn



  Log.AppendFolder "ConfigureDsn"       



     

  set prc = Sys.WaitProcess("ODBCAD32", STD_WAIT )

  if prc.Exists then

    prc.Terminate

  end if



  TestedApps.Items("odbcad32").Run

   



  Set frmOdbcAdmin = Sys.Process("ODBCAD32").WaitWindow("#32770", "ODBC Data Source Administrator", -1, STD_WAIT)

 

  frmOdbcAdmin.Window("SysTabControl32", "", 1).ClickTab "System DSN"

  ' Does the desired DSN exist?

  set lstDsn = frmOdbcAdmin.Window("#32770", "Dialog", 1).Window("SysListView32", "List1", -1)

  bFound = false

  For i = 0 to lstDsn.wItemCount - 1

    If DSN_NAME = lstDsn.wItem(i) then

      bFound = true

    end if         

  Next

  If bFound then

    lstDsn.ClickItem DSN_NAME

    frmOdbcAdmin.Window("#32770", "Dialog", 1).Window("Button", "&Configure...", -1).Click

  else

    frmOdbcAdmin.Window("#32770", "Dialog", 1).Window("Button", "A&dd...", -1).Click  

    set frmNew = Sys.Process("ODBCAD32").WaitWindow("#32770", "Create New Data Source", -1, STD_WAIT)

    frmNew.Window("#32770", "Create New Data Source", 1).Window("SysListView32", "List1", -1).ClickItem DRIVER

    frmNew.Window("Button", "Finish", -1).Click

        

  end if

  set frmConfig = Sys.Process("ODBCAD32").WaitWindow("#32770", "XXXXXXXXXX", -1, STD_WAIT)

  frmConfig.Window("Edit", "", 1).wText = DSN_NAME   ' name

  frmConfig.Window("Edit", "", 3).wText = "127.0.0.1" ' ip

  frmConfig.Window("Edit", "", 4).wText = "1972" ' port

  frmConfig.Window("Edit", "", 5).wText = "MyApp" ' mnamespace

  frmConfig.Window("Edit", "", 6).wText =  "LOGIN" ' user name

  frmConfig.Window("Button", "Static Cursors").ClickButton cbChecked  '' I needed a static cursor, you might choose otherwise. 

  frmConfig.Window("Button", "&Test Connection", 18).Click  

 

  ' The windows are all named the same. Find the one I want.

  sPropNames = "WndCaption,WndClass"

  aPropNames = Split(sPropNames, ",")

  sPropValues = "XXXXXXXXXXXXXX,#32770"

  aPropValues = Split(sPropValues, ",")  

  aForms = Sys.Process("ODBCAD32").FindAll(aPropNames, aPropValues)

   

  bFound = false

  For i = 0 to UBound(aForms)

    set frmTest = aForms(i)

    if frmTest.WaitWindow("Static", "XXXXXXXX, -1, STD_WAIT).Exists then

      bFound = true

      Exit for ' desired form is found

    end if             

  Next

 

 

  sText = frmTest.Window("Edit", "", 1).wText

  If Instr(1,sText, "Connectivity test completed successfully!", 1) = 0 then

    Log.Error "Dsn Connection failed. The rest of the test cannot run without a working Dsn."

  end if  

  Sys.Process("ODBCAD32").Window("#32770", "InterSystems Caché ODBC Data Source Setup", 1).Window("Button", "&OK", 1).Click

  Sys.Process("ODBCAD32").Window("#32770", "InterSystems Caché ODBC Data Source Setup", 1).Window("Button", "&OK", 16).Click

  Sys.Process("ODBCAD32").Window("#32770", "ODBC Data Source Administrator", 1).Window("Button", "OK", 1).Click      

  Log.PopLogFolder  



End Sub