Examples: Connecting to an external database

This agent declares a new ODBCConnection object and displays the name of the available data sources. You must include the Uselsx "*LSXODBC" statement in the (Options) script of the agent.

Uselsx "*LSXODBC"
Sub Initialize
  Dim con As New ODBCConnection
  Dim msg As String
  Dim dsnList as Variant
  dsnList = con.ListDataSources
  For n% = Lbound(dsnList) To Ubound(dsnList)
    msg = msg & dsnList(n%) & Chr(10)
  Next
  Messagebox msg,,"List of DSNs"
End Sub

This agent connects to a data source specified by the user. If the connection fails, IsConnected is False and the agent exits. (Alternatively, you can test the return value of ConnectTo, as shown in the code that is remarked out, rather than test IsConnected.) If the connection is good, the agent lists the tables for the data source by looping through the string array returned by ListTables, then disconnects before terminating.

Uselsx "*LSXODBC"
Sub Initialize
  Dim con As New ODBCConnection
  Dim dsn As String
  Dim msg As String
  dsn = Inputbox("ODBC data source name", "DSN")
  REM If Not con.ConnectTo(dsn) Then
  con.ConnectTo(dsn)
  If Not con.IsConnected Then
    Messagebox "Could not connect to " & dsn,, _
    "Error"
    Exit Sub
  End If
  tables = con.ListTables(dsn)
  msg = dsn & " contains the following _
  tables:" & Chr(10)
  For n% = Lbound(tables) To Ubound(tables)
    msg = msg & Chr(10) & tables(n%)
  Next
  Messagebox msg,, "Tables for " & dsn
  con.Disconnect
End Sub

This example is a form that contains two text fields named dataSource and Table, two buttons with the same names, and two actions named "List tables" and "List procedures." Notice the use of global declarations so that all the scripts on the form can access the same objects and the data. The form's Postopen script sets the objects, gets the names of the available data sources, writes the first one to the dataSource field, gets the names of the tables for the data source, and writes the first one to the Table field. The "Data source" button writes the name of the next data source to the dataSource field, gets the tables for the new data source, and writes the first one to the Table field. The "Table" button writes the name of the next table to the Table field. The "List fields" action displays the names of all the fields for the current data source and table. The "List procedures" action displays the name of all the procedures for the current data source.

Uselsx "*LSXODBC"
(Globals) (Declarations)
Dim con As ODBCConnection
Dim dataSources As Variant
Dim tables As Variant
Dim thisdsn As Integer
Dim thistable As Integer
Dim workspace As NotesUIWorkspace
Dim uidoc As NotesUIDocument
Sub Postopen(Source As Notesuidocument)
  Set workspace = New NotesUIWorkspace
  Set uidoc = workspace.CurrentDocument
  Set con = New ODBCConnection
  con.SilentMode = True
  dataSources = con.ListDataSources
  thisdsn = Lbound(dataSources)
  Call uidoc.FieldSetText("dataSource", & _
  dataSources(thisdsn))
  tables = con.ListTables(dataSources(thisdsn))
  If Ubound(tables) <> 0 Then
    thistable = Lbound(tables)
    Call uidoc.FieldSetText("Table", & _
    tables(thistable))
  End If
End Sub
Sub Queryclose(Source As Notesuidocument, Continue As Variant)
  If con.IsConnected Then
    con.Disconnect
  End If
End Sub
REM "Data source" button
Sub Click(Source As Button)
  If thisdsn = Ubound(dataSources) Then
    thisdsn = Lbound(dataSources)
  Else
    thisdsn = thisdsn + 1
  End If
  Call uidoc.FieldSetText("dataSource", & _
  dataSources(thisdsn))
  tables = con.ListTables(dataSources(thisdsn))
  If Ubound(tables) <> 0 Then
    thistable = Lbound(tables)
    Call uidoc.FieldSetText("Table", & _
    tables(thistable))
  End If
End Sub
REM "Table" button
Sub Click(Source As Button)
  If Ubound(tables) <> 0 Then
    If thistable = Ubound(tables) Then
      thistable = Lbound(tables)
    Else
      thistable = thistable + 1
    End If
    Call uidoc.FieldSetText("Table", tables(thistable))
  End If
End Sub
REM "List tables" action
Sub Click(Source As Button)
  Dim msg As String
  con.ConnectTo(dataSources(thisdsn))
  If con.IsConnected Then
    fields = con.ListFields(tables(thistable))
    If Ubound(fields) <> 0 Then
      msg = tables(thistable) & _
      " contains the following fields:" & Chr(10)
      For o% = Lbound(fields) To Ubound(fields)
        msg = msg & Chr(10) & fields(o%)
      Next
      Messagebox msg,, con.DataSourceName
    Else
      Messagebox "No fields in "  _
      & tables(thistable),, _
      con.DataSourceName
    End If
    con.Disconnect
  End If
End Sub
REM "List procedures" action
Sub Click(Source As Button)
  Dim msg As String
  con.ConnectTo(dataSources(thisdsn))
  If con.IsConnected Then
    procs = con.ListProcedures
    If Ubound(procs) <> 0 Then
      msg = con.DataSourceName & _
      " contains the following procedures:" _
      & Chr(10)
      For o% = Lbound(procs) To Ubound(procs)
        msg = msg & Chr(10) & procs(o%)
      Next
      Messagebox msg,, con.DataSourceName
    Else
      Messagebox "No procedures",, _
      con.DataSourceName
    End If
    con.Disconnect
  End If End Sub