Global Navigation Bar

MapInfo Products Knowledge Base


Product: MapBasic
Version: 4.x\5.x\6.x
Platform: Not Platform Related
Category: Sample Applications

Summary:
Tips and tricks for using string variables and run commands

Question:

Reasons for using string variables and run command statements include:

1. Code maintenance: The names of the tables and columns may be known at development time, however, there may be an instance in the future where a table or column name needs to be changed. If variables are used then only the names of the variables need to be changed in the code, rather than every single statement that references the table.

2. User interaction: If the user is to be selecting tables and columns dynamically through dialogs. e.g. Select a table, then x and y columns from which to make points for that table.

Answer:

Using variables for column or table names
  • Always, always, always use a string variable for a table name in a MapBasic program.
    Hard-coded table names are treated exactly like string variables by the compiler and at run time, so exercise a little control over them.

    A simplified scheme for opening a table follows:
      function OpenTable (ByVal p as string, t as string) as logical

      OnError goto notthere
      open table p
      t = TableInfo (0, TAB_INFO_NAME)
      OpenTable = TRUE
      exit function
      notthere: resume fail
      fail: OpenTable = FALSE
      end function

    This way, if it is desired to open a table named "FOO.TAB" and a different "FOO.TAB" is open, t will be set to "foo_2", allowing work to be done on the correct table.
  • Use a string variable for a column name unless there's a good reason not to (i.e. "Obj").
  • Extract row values using alias variables.
      dim a as alias
      dim o as object

      a = t + ".obj"
      fetch first from t
      do while not EOT (t)
      if (a)
      then o = a
      ' then process the object
      end if
      fetch next from t

  • For all SQL and DML, build command strings and execute them via Run Command.
      cmd = "Update "+t+" set "+c+"="+newval+" where rowid="+r
      Run Command (cmd)
    The exception is updating graphic objects:
    Update t set obj=o where rowid=r
  • This can also be done using aliases, see below. When using aliases instead of command string, it will be possible to avoid figuring out whether a certain column is numerical or not. If the column for instance is holding chars, it is necessary to put "quotations marks" around the value to be inserted.
    Only the alias is needed if it is desired to refer to a column on the right side of the expression (szCol = aCol + "2").

----------------------------------------
    Using Command String:

      Dim szCmdStr, szTab, szCol As String

      szTab = "ADDRESSES"
      szCol = "ADDRESS"
      szCmdStr = "Update " & szTab & " Set " & szCol & " = " & szCol " + " &
      Chr$(34) & "2" & Chr$(34)
      Run Command szCmdStr

    -----------------------------------------

    Using Alias:
      Dim szTab, szCol As String,
      aCol As Alias

      szTab = "ADDRESSES"
      szCol = "ADDRESS"
      aCol = szTab & "." & szCol

      Update szTab
      Set szCol = aCol + "2"
    -----------------------------------------

    Another problem with the command string is that it isn't possible to use object variables within these, unless the object variable is dimmed using a command string as well. Remember that the command string is executed in another scope than the MapBasic application. When using the command string, it's more or less like executing a command thru the MapBasic window in MapInfo Professional.

    Of course, the command string method is used widely, especially when creating an SQL statement that can change from session to session:

    Imagine this situation where it is desired to create an application where a road can be selected by entering a zipcode (postcode) and/or a roadname.

    Several SQL statements can be built that covers all the possibilities:

      Select * From ROADS Where ZIP = nUserZip And ROADNAME = szUserRoadName
      Select * From ROADS Where ROADNAME = szUserRoadName
      Select * From ROADS Where ZIP = nUserZip
      Select * From ROADS Where ZIP = nUserZip And ROADNAME Like szUserRoadName
      Select * From ROADS Where ROADNAME Like szUserRoadName
      Select * From ROADS
    ...

    Or, build the SQL statement at runtime when it is known which conditions have been given:
          If nUserZip > 0 Then
      szCond1 = "ZIP = " nUserZip
      End If
      If szUserRoadName <> "" Then
      If InStr(1, szRoadName, "%") Then
      szCond2 = "ROADNAME Like " & Chr$(34) & szUserRoadName & Chr$(34)
      Else
      szCond2 = "ROADNAME = " & Chr$(34) & szUserRoadName & Chr$(34)
      End If
      End If

      If szCond1 <> "" And szCon2 <> "" Then
      szCond = "Where " & szCond1 & " And " & szCond2
      ElseIf szCond1 <> "" Then
      szCond = "Where " & szCond1
      ElseIf szCond2 <> "" Then
      szCond = "Where " & szCond2
      End If

      szCmdStr = "Select * From ROADS " & szCond



    Last Modified:
    Global Navigation Bar