MapInfo Pro

 View Only

  • 1.  Variables in a Run Command String (Queries not saving to workspaces)

    Posted 01-25-2023 06:43
    Edited by Ryan Cook 01-25-2023 07:47
    A number of tools I have created help users develop workspace files with maps providing visual information based upon queries. When the workspace is closed and re-opened, these queries have vanished and the important information is lost. Ticking "save queries in workspaces" under preferences makes no difference: if you create a query via a MapBasic tool, MapInfo just ignores it when saving the content of that workspace on shutdown. It really blows me away how poor this is - its a glaring oversight that I can't believe hasn't been addressed. In the meantime, what workaround is there for this baffling bug? 

    There is no MapBasic method for saving queries to workspaces that you can include in your program to address this. You could of course force queries to be saved as full tables, but cluttering up our network with unnecessary files seems daft when MapInfo is perfectly capable of saving the exact same queries inside workspaces if they were entered by the user. We could just instruct the user to run the query themselves, but isn't the point of MapBasic programming to preform these tasks so people don't have to?

    Apparently, we can force MapInfo to 'remember' to save the queries if we pass them through the Run Command statement. Except it seems to stumble on queries that use variables. An example query I would like MapInfo to understand is:

    Select * From tAreas where tName  =  tYourArea Into _YOURAREA

    So how does one go about converting that syntax into the new syntax required for it to work with Run Command? Just enclosing the full query in quotes does not work. Enclosing everything in quotes except the variables:

    Run Command "Select * From " + tAreas + " where " + tName + " = " + tYourArea + "Into _YOURAREA"

    ...did not work for me either. MapInfo rather aimlessly complains that it: "Found [=] while searching for [a string constant]"

    Any help would be gratefully appreciated. And if MapInfo can fix this bug that would be great too. We really shouldn't really need to find a workaround for this. 

    ------------------------------
    Ryan Cook
    Knowledge Community Shared Account
    ------------------------------


  • 2.  RE: Variables in a Run Command String (Queries not saving to workspaces)

    Posted 01-25-2023 08:51
    Hi Ryan,

    I understand your frustration!  For Mapbasic-executed queries to be ignored when saving the workspace seems quite strange.  Furthermore, any subsequent queries or windows created from the original query are also ignored.

    Anyway, in your example query above, try the following:
    Run Command "Select * From " + tAreas + " where " + tName + " = """ + tYourArea + """ Into _YOURAREA"

    The triple-quotes are required because you are comparing strings in your expression.

    In your original post you specified that the following doesn't work:
    Run Command "Select * From " + OgreTab + " Where _100_base = 100 Into _Opt"

    I think in this case I would check the contents of the OgreTab variable via a print statement, to make sure it contains the name of the open MapInfo table that you are querying.

    ------------------------------
    James Nolet
    Dooley Mitchell & Morrison Pty Ltd
    Mentone, VIC, Australia
    ------------------------------

    Original Message


  • 3.  RE: Variables in a Run Command String (Queries not saving to workspaces)

    Posted 01-25-2023 09:23
    Edited by Ryan Cook 01-25-2023 09:24
    Hey James, thanks for your help and for tolerating the change to my initial post - I manged to get the OgreTab query to work but the above one still eludes me, and sadly the triple quote doesn't change the outcome. I still receive the same error:

    Found [=] while searching for [a string constant]

    I'm afraid I don't really understand the purpose of the triple quotes either!

    ------------------------------
    Ryan Cook
    Knowledge Community Shared Account
    ------------------------------

    Original Message


  • 4.  RE: Variables in a Run Command String (Queries not saving to workspaces)

    Posted 01-25-2023 19:04
    Hi Ryan,

    In that case, check the contents of tName (e.g. via a Print statement).  Is it blank?

    In fact, if you assign the whole expression to a string variable:
    Dim sStr As String
    sStr = "Select * From " + tAreas + " where " + tName + " = """ + tYourArea + """ Into _YOURAREA"

    Then print it to the Message window:
    Print sStr

    You will be able to double-check your entire expression.

    ------------------------------
    James Nolet
    Dooley Mitchell & Morrison Pty Ltd
    Mentone, VIC, Australia
    ------------------------------

    Original Message


  • 5.  RE: Variables in a Run Command String (Queries not saving to workspaces)

    Employee
    Posted 01-26-2023 01:29
    I can never remember if I should double or triple quotes so I tend to use the Chr$(34) instead.
    That will insert a double quote around the string you are comparing the values in the column to.

    sCmd = "Select * From " + tAreas + " where " + tName + " = " + Chr$(34) + tYourArea + Chr$(34) + " Into _YOURAREA"
    Print sCmd
    Run Command sCmd

    It doesn't necessarily make the command easier to write but it works better for me.

    And as James said, I also tend to print out the command before executing it to see if it looks as expected.

    As for making the queries available or not, it's a matter of scope. If you run them directly, they will only run in the scope of the application and so not clutter the interface - even though they do appear in the Table List if they don't get closed or executed with the Hide keyword.

    With the new SQL Window, they would also appear in the list of recent queries.

    Maybe we could consider adding a keyword to the Select statement to control if the query should be savable to a workspace. Would that help?

    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------

    Original Message


  • 6.  RE: Variables in a Run Command String (Queries not saving to workspaces)

    Posted 01-26-2023 05:55
    James - I tried entirely/partially assigning the statement to string variables and it still failed. Note, it works perfectly as intended when left as a normal query statement but I'll whack a note in there to see what is coming out it when Run Command is applied. 

    Peter - Thanks for the tips - I'm not sure what you mean by adding a keyword to the select statement, though? To be honest, if I can avoid mucking about with Run Command syntax that would be great.

    ------------------------------
    Ryan Cook
    Knowledge Community Shared Account
    ------------------------------

    Original Message


  • 7.  RE: Variables in a Run Command String (Queries not saving to workspaces)

    Posted 01-26-2023 06:07
    I might try to explain the triple quotes.  Your expression without using variables would be:
    Select * From table1 where field1 = "Area1" Into _YOURAREA

    When assigning the expression to a string, the string itself uses quote characters to encapsulate the string.  First, let's say "Area1" is assigned to a variable:
    sAreaName = "Area1"

    Then, assigning the entire expression to a string might look like this:
    sStr = "Select * From table1 where field1 = " + sAreaName + " Into _YOURAREA"

    But printing this to the Message window will display this:
    Select * From table1 where field1 = Area1 Into _YOURAREA

    As you can see, we have lost the quotes around "Area1".  We need to include them when assigning the expression to sStr.  But MapBasic is already interpreting a quote character as representing the start or end of a string.  We need to tell MapBasic that the extra quote character is just to be part of the string.  This is done by escaping the quote character with an extra quote character; i.e. double quotes: "".  MapBasic will see the "" as a single quote that is to be included in the expression.

    So the final expression is:
    sStr = "Select * From table1 where field1 = """ + sAreaName + """ Into _YOURAREA"

    That should now make """perfect""" sense!

    ------------------------------
    James Nolet
    Dooley Mitchell & Morrison Pty Ltd
    Mentone, VIC, Australia
    ------------------------------

    Original Message


  • 8.  RE: Variables in a Run Command String (Queries not saving to workspaces)

    Posted 01-26-2023 06:13
    Ha, thanks James - so we are basically quoting the quote marks! That makes sense!

    ------------------------------
    Ryan Cook
    Knowledge Community Shared Account
    ------------------------------

    Original Message


  • 9.  RE: Variables in a Run Command String (Queries not saving to workspaces)

    Employee
    Posted 02-21-2023 05:02
    Edited by Peter Møller 02-21-2023 07:36

    I wonder if you could get around your issue by setting the query to persist.

    Set Table tablename Persist { On | Off }

    From the MapBasic Help File:

    The Persist Off clause marks a table, so that it will not be written to the workspace when a workspace is saved. The Persist or Persist On clause marks a table, which was previously marked as Persist Off, so that it will be written back to the workspace when a workspace is saved. 

    PS: I created an idea on Precisely Ideas to improve this.

    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------

    Original Message