Platform: Not Platform Related
Category: Sample Applications
Tips and tricks for using string variables and run commands
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.
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
notthere: resume fail
fail: OpenTable = FALSE
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)
then o = a
' then process the object
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
Dim szTab, szCol As String,
aCol As Alias
szTab = "ADDRESSES"
szCol = "ADDRESS"
aCol = szTab & "." & szCol
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
If szUserRoadName <> "" Then
If InStr(1, szRoadName, "%") Then
szCond2 = "ROADNAME Like " & Chr$(34) & szUserRoadName & Chr$(34)
szCond2 = "ROADNAME = " & Chr$(34) & szUserRoadName & Chr$(34)
If szCond1 <> "" And szCon2 <> "" Then
szCond = "Where " & szCond1 & " And " & szCond2
ElseIf szCond1 <> "" Then
szCond = "Where " & szCond1
ElseIf szCond2 <> "" Then
szCond = "Where " & szCond2
szCmdStr = "Select * From ROADS " & szCond