MapInfo Pro

Expand all | Collapse all

Trying to understand inherited code

Jump to Best Answer
  • 1.  Trying to understand inherited code

    Posted 02-08-2019 18:06
    I am trying to understand some inherited mapbasic code that has 0 documentation. I've added in comments and would really appreciate feedback on whether I am correct or not...based on this I should be able to do the modifications that are now required.

    The main purpose of this code is to nightly/routinely refresh a list of tables from various inhouse and external databases and it's run via Windows Server Task Scheduler using a .bat file that has

    Start "Refresh.." /MAX D:\GIS_Tools\Scheduled_Tasks\4_Refresh_SQL_Tables\RefreshODBC.MBX
    ' Is this an IBM program that simply runs the .mbx ( or something similar in Microsoft BI (Configure scheduled refresh - Power BI). When I run Start "Refresh.." / MAX without the .mbx reference I just get a new command prompt.

    The .tab it refers to is like

    The .mbx is

    Include "Mapbasic.def"

    Declare Sub Main

    Dim NumTabs,CurrDay,NumTabsfinal as Smallint

    Dim CmdString As String

    Sub Main

    Close All

    Open Table ApplicationDirectory$() & "RefreshList.TAB"

    '1. ApplicationDirectory$() is simply MB code to get the current path of the .mbx and it's assuming the .tab is in the same folder.

    CurrDay = WeekDay(Curdate())

    CmdString = "SELECT FullPath,LastRun FROM RefreshList WHERE Col" & CurrDay & " AND Active INTO ProcessThese"

    '2. Simple SQL to select the .tab file to process that puts the file name of any files that have process Active as "T" and is (older than the current day?) in a temp memory table called ProcessThese

    Run Command CmdString

    NumTabsfinal = TableInfo(ProcessThese,TAB_INFO_NROWS)

    '3. In a small int field put in the value for ProcessThese and the row number.
    Q. Is this an array/list then? i.e a single value or all the values?

    For NumTabs = 1 to TableInfo(ProcessThese,TAB_INFO_NROWS)

    '4. Start a loop but just read the first record?

    Fetch Rec NumTabs From ProcessThese

    5. Get the first record?

    CmdString = "Open Table " & Chr$(34) & ProcessThese.FullPath & Chr$(34) & " AS RefreshMe"

    Print ProcessThese.FullPath

    '6. Print the file path/name from ProcessThese

    Run Command CmdString

    OnError GoTo ErrLoop

    Server Refresh RefreshMe

    Close Table RefreshMe

    Update ProcessThese Set LastRun = CurDate() Where RowID = NumTabs

    '7 Update the LastRun column in the with the current date if the process completed correctly.

    Commit Table RefreshList


    Close All

    End Mapinfo


    OnError GoTo 0

    Print Error$()

    Resume Next

    End Sub

    The major thing (except for the Q's above) that I don't understand is where is it actually updating the files and what's the source of the updated file that ends up in the D:\GIS\Data... folder?

    The same folder has the following files including another .mb file but this program isn't called from the .bat or the above mapbasic?

    George Corea

  • 2.  RE: Trying to understand inherited code
    Best Answer

    Posted 02-10-2019 18:45
    Hi George,

    Start "Refresh.." /MAX is a  dos command, specifying that your mbx program is to be run in a command window titled "Refresh.." and that the window is to be maximised.  See here for more:

    3.  Retrieve the number of records within temp table 'ProcessThese' - store this value in variable NumTabsfinal
    4.  Start looping through all records in 'ProcessThese'.  Variable NumTabs will equal 1 when first entering the loop, and will increase by one each time the loop is executed.
    5.  Get the current record - NumTabs contains the number of the record that will be "got"
    6.  Within the current record, field "FullPath" contains the path and name of a table that is to be refreshed.  Construct the string that will be used to open this table.  Also print the path and name of this table to the message window.
    7.  'Run Command CmdString' opens the table that is to be refreshed. It will be given the name of 'RefreshMe' within MapInfo.  'Server Refresh RefreshMe' refreshes the table by downloading from the linked remote database.  The 'RefreshMe' table is then closed, and the 'LastRun' field of the current record in 'ProcessThese' is updated with the current date.  Note that the 'NumTabs' variable contains the number of the current record.  Because 'ProcessThese' is a temporary query, the base table 'RefreshList' is also updated.
    8. Changes to 'RefreshLIst' are saved.  The 'Next' keyword means that execution returns to the start of the loop (where NumTabs will be increased by one).  If NumTabs = the number of records in 'ProcessThese', then no more looping will occur.

    To find the source of the updated tab files withn the D:\GIS\Data\ folder, open each TAB file within a text editor (e.g. NotePad) and take a look at the 'ConnectionString' item within the metadata section at the end of the file.

    I hope this helps!

    James Nolet
    Dooley Mitchell & Morrison Pty Ltd

  • 3.  RE: Trying to understand inherited code

    Posted 02-14-2019 16:31
    Thanks for this.

    It really helped me to work out the reason for this code.

    Am I right in assuming that the requirement for these DB based tab files is simply because it's the way that mapinfo (and mapinfo based products like SSA/Exponare) works with mssql?

    In QGIS for example, these .tab files aren't required as it connects directly to the db without the need for a sepeate file to make the connection (it's just part of the project file).


    George Corea

  • 4.  RE: Trying to understand inherited code

    Posted 02-14-2019 17:43
    Yes.  MapInfo requires the TAB file.  In your case, the data from the remote database is then downloaded when the "Refresh" takes place.  You can also have dynamically-linked MapInfo tables that don't require a refresh.  These will still have a TAB file, but will read directly from the live data.

    Although Exponare relies on TAB files for spatial data also, SSA can link directly to the remote db and display spatial data without the use of a TAB file.

    James Nolet
    Dooley Mitchell & Morrison Pty Ltd

  • 5.  RE: Trying to understand inherited code

    Posted 02-14-2019 19:08
    Great... and Easyloader does the reverse - load data from edited tab files to the mssql right?

    George Corea

  • 6.  RE: Trying to understand inherited code

    Posted 02-14-2019 19:40
    Yes, that's correct George.

    James Nolet
    Dooley Mitchell & Morrison Pty Ltd