MapInfo Pro Developers User Group

Expand all | Collapse all

Get Field type in mapbasic

Jump to Best Answer
  • 1.  Get Field type in mapbasic

    Posted 17 days ago
    Edited by George Corea 17 days ago
    I am working on a script that checks for values of 0 or null or "" and replaces them with "9999" and "Unknown" but I am stuck at separating the float/integer fields from the string fields so I get type mismatch errors. How can I work out what type a field is?

    This is built to run on many tables without specifying the table name so it has to be an automated way to get the field table info

    'Updates 0 and null values to 9999 or Unknown.
    ' Add to the following list {("As_con_2_hyperlink","As_con_3_hyperlink")} in line that starts with 'if (sColName not in' to ignore additional fields
    
    Include "MapBasic.def"
    Include "Menu.def"
    
    Declare Sub Main()
    Dim filename, printtext,WFolder as string
    Dim rcount,i as Integer
    
    Sub Main()
    Close All
    'OnError GoTo CaughtEx '// Turn Error handling on
    WFolder=ApplicationDirectory$()+"\"
    Register Table WFolder+"List.txt"  TYPE ASCII Delimiter 44 Titles Charset "WindowsLatin1" Into WFolder+"~MAP0001.TAB"
    Open Table WFolder+"~MAP0001.TAB" Hide
    Commit Table ~MAP0001 As WFolder+"List.TAB"
    Close Table ~MAP0001
    Open Table WFolder+"List.TAB"  as List Interactive
    Browse * From List
    rcount=TableInfo(List,TAB_INFO_NROWS)
    
    i=1
    While i<=rcount
    	Select * from List where List.RowID=i into RSelection
    	Browse * from RSelection
    	'print RSelection.col1 
    	Open Table WFolder+RSelection.col1 as temptable
    
    
    '////
    'START Unique per table process
    
    'Get Column Name list
    dim x as integer
    dim sColName as string
    dim aColName as Alias
    For x=1 to TableInfo(temptable, TAB_INFO_NCOLS)
        sColName = ColumnInfo(temptable, "col"+str$(x), COL_INFO_NAME)
        if (sColName not in ("Asset_ID","GID","GID_New","Pipe_ID","Spiceworks_ID","Date_Asset_added","Date_Asset_Modified","As_con_2_hyperlink","As_con_3_hyperlink")) then
    	    aColName = sColName	    
    	    Select * from temptable where aColName=0 into "g_"+sColName
    	    Browse * from "g_"+sColName
    	    print sColName
    	    Update "g_"+sColName set aColName="99999"
    		Commit Table temptable
        End If
    Next
    
    
    'Browse * From temptable
    'Select Pipe_Type from temptable group by Pipe_Type into temptable_group_PipeType
    'Browse * From temptable_group_PipeType
    
    'END Unique per table process
    '////
    
    	i=i+1
    	close table temptable
    
    Wend
    CaughtEx:  '// error handling routine
        '// Log error here...
        print Chr$(13)+Chr$(13)+"///////MAIN///////"+Chr$(13)+"FAILED due to:"+Chr$(13)+Error$()+Chr$(13)+"\\\\\\\\\\\\\\\"+Chr$(13)
        Resume Next  '// resume code at line after the one which threw the error
    End Sub​

    So where it has
    ' Select * from temptable where aColName=0 into "g_"+sColName

    I would like to check if string or float/int and then something like

       if fieldtype=string
     then
          Select * from temptable where aColName="" or aColName=Null into "g_"+sColName
          Update ... ="Unknown"
    else
          Select * from temptable where aColName=0 into "g_"+sColName
          Update...=9999


    The input list.txt file is created by running a .bat file with 
    echo File >List.txt
    dir *.tab /w/b >>List.txt
    Pause​


    ------------------------------
    George Corea
    Mangoesmapping
    ------------------------------


  • 2.  RE: Get Field type in mapbasic
    Best Answer

    Posted 17 days ago
    Hi George,

    You can do something like the following:

    First, declare new variable sValue:
    Dim sValue As StringDim sValue As String

    Then, before your update statement, use this:
    Do Case ColumnInfo("temptable", sColName, COL_INFO_TYPE)

    ' Handle String Fields
    Case COL_TYPE_CHAR
    sValue = "99999"

    ' Handle Number Fields
    Case COL_TYPE_DECIMAL, COL_TYPE_INTEGER, COL_TYPE_SMALLINT, COL_TYPE_FLOAT
    sValue = 99999

    Case Else
    ' Should report an error here
    End Case

    Then you update statement becomes:
    Update "g_"+sColName set aColName=sValue

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



  • 3.  RE: Get Field type in mapbasic

    Posted 17 days ago
    Thanks. As the issue with the type of column impacts the selection as well as the update statement -I had to run it as

    		Do Case ColumnInfo("temptable", sColName, COL_INFO_TYPE)
    		
    		' Handle String Fields
    		Case COL_TYPE_CHAR
    		Select * from temptable where aColName="" into "g_"+sColName
    		sValue = "Unknown"
    		
    		' Handle Number Fields
    		Case COL_TYPE_DECIMAL, COL_TYPE_INTEGER, COL_TYPE_SMALLINT, COL_TYPE_FLOAT
    		Select * from temptable where aColName=0 into "g_"+sColName
    		sValue = 99999
    		
    		Case Else
    		' Should report an error here
    		End Case
    	    
    	    Browse * from "g_"+sColName
    	    Update "g_"+sColName set aColName=sValue
    		Commit Table temptable​


    ------------------------------
    George Corea
    Mangoesmapping
    ------------------------------



  • 4.  RE: Get Field type in mapbasic

    Posted 17 days ago
    Hi George

    In case you are using MapInfo Pro v2019, you can now add the condition to your Update statement and so avoid the Select statement:

    Do Case ColumnInfo("temptable", sColName, COL_INFO_TYPE)

      ' Handle String Fields
      Case COL_TYPE_CHAR
        Update temptable set aColName = "Unknown"

          where aColName=""

        Browse * from temptable
        Set Browse Filter Where aColName = "Unknown"

      ' Handle Number Fields
      Case COL_TYPE_DECIMAL, COL_TYPE_INTEGER, COL_TYPE_SMALLINT, COL_TYPE_FLOAT
        Update temptable set aColName = 99999
          Where aColName=0

        Browse * from temptable
        Set Browse Filter Where aColName = 99999

      Case Else

        ' Should report an error here
    End Case

    Set Window Title "g_"+sColName
    Commit Table temptable​

    Not sure how much better it will be in your case as you also are showing the affected records in a browser. In my example above, I have done this by applying a filter to the browser and changing the title of the window.

    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------



  • 5.  RE: Get Field type in mapbasic

    Posted 17 days ago
    Thanks. The browse statements were just for the testing and would be removed in production use...but thanks for this note and example as it will be helpful in the future.

    ------------------------------
    George Corea
    Mangoesmapping
    ------------------------------