Happy #MapInfoMonday,
When you open any table into MapInfo Pro, MapInfo Pro will automatically assign an alias to the table. This alias is what the tab file is referred to using in MapInfo Pro. We assign this alias because a file name can contain characters that a table name doesn't support, such as spaces and special characters.
You can see the alias for your tables in the interface of MapInfo Pro, for example in the Table List. What you typically will see is that spaces in the file name have been replaced with underscores (_). Other examples of changes are that special characters have been replaced with underscores and that long file names have been shortened.
With MapInfo Pro v2019.3 we added support for using Variables in the SQL Window. This opens up for using queries and scripts that prompt the user for input before executing the script. Suddenly, your scripts have become more dynamic as you can use one script to behave differently depending on the input from the user.
In this article, I'll show you have you can use the SQL Window to create a very small script that asks the user to select a table to open, and also lets the user specify an alias for the table.
Consider this article as a first introduction to using variables in the SQL Window. The use of variables opens up a wide variety of smart scripts that really can help automate your usage of MapInfo Pro.
Creating the Variables
From the SQL Window, select Edit Variables in the Variables dropdown. Note that you need to be using MapInfo Pro v2019.3 for this dropdown to exist in the SQL Window.
In the dialog Edit Variables, we need to add two variables. Use the Add button to add these. Then select the first from the list and give it a reasonable name. I tend to prefix my variables with typically a single character that describes what values the variable holds. For the first variables which will hold the filename of the table to open, I prefix it with an "s" as it holds strings.
We'll dive into some of settings for the variables below. You can however see that there is a rich set of capabilities that can be exposed by using the variables.
Select the variable from the list, and use the fields below to control the settings of the variable:
-
Name: I name the first variable sFile as it holds a filename in the form of a string variable.
-
Type: The type must be File as it will prompt the user to select an existing tab file.
-
Prompt: This text is shown next to the control in the dialog.
-
Help Text: This is shown when you hold your mouse over the control in the dialog. Can be more descriptive than the prompt.
-
Properties: Through the properties for this control, I can specify what file types to prompt the user to open. In our example, we want the user to open tab files, so I specify Extension=TAB.
Now Add and Select another variable via the Add button.
-
Name: For Name I specify sAlias as this will hold the name the table will be opened using in the form of a string.
-
Type: This time I select String as the type as I want the user to manually enter the alias to open the table under.
-
Prompt: This is the text to show next to the control in the dialog. I keep it simple.
-
Help Text: Here you can add a bit more description and maybe even include some rules like should not hold spaces, be less than 30 characters etc. Some of these are handled by the script other limitations are controlled using the Properties.
- Properties: Here I select Don't allow empty strings from the Add Property list and I also select Limit string length from the same list. I change the MaximumLength value to 30 as that is the maximum length of a table name in MapInfo Pro.
Hit the OK button to save your variable, and you are back in the SQL Window where it is time to put the variable into play.
The Expression
Now it's time to build the expression that opens a table under a given alias. This is straightforward when you know what to do. You only need the MapBasic statement Open Table as it holds all the options we need.
As you write your statement, you can use the Variables dropdown to insert the variables in the expression.
The final expression which is only a single statement looks like this:
Open Table sFile As PathToTableName$(sAlias) Interactive View Automatic
The clever thing about using the Variables is that MapInfo Pro builds the dialog and prompts the user for input for the variables before running the script. Also notice that I have made sure that the Alias entered by the user can be used as an alias by converting it into a table name using the PathToTableName$() function. This function makes sure spaces are converted to underscores, the name is limited to 30 characters and other rules to a table name needs to comply to.
Finally, you need to rename your script, save it and potentially mark it as a favorite script to make it appear in the Favorites section of the SQL dropdown for easy access. All these options can be found on the right side of the Scripts dropdown in the SQL Window.
Using the Open Table As script
To use the script, you can of course run it directly from the SQL Window. This is useful while you are building the script.
Once the script is ready, and you have marked it as a favorite, the script will appear in the Favorites section of the SQL dropdown. All you need to do to run it is to click on it in the list. You can also use the small Edit icon on the right side of the dropdown, to open the script in the SQL Window in case you need to modify it.
When you run the script, you are prompted to select the table to open and to specify an alias for the table. Notice that the
Open Table As dialog won't let you hit the
OK button until you have entered an alias with at least one character. The
Alias field also prevents you from entering an alias longer than 30 characters.
In the example above, you might want to specify an alias without any spaces to make the name shorter and to avoid using spaces.
When you hit the OK button, MapInfo Pro will run the script using the values you have selected/entered in the dialog. In this case, the table will be opened using the alias entered.
I have attached this script as a zip file to this article. You can download it and unpack it into your Saved Scripts folder. Check the Directory preferences, if you aren't sure where that folder is.
I hope this article gave you some ideas on how you can benefit from the Variables in the SQL Window.
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------