How to loop through all columns in a table/layer getting all data for a row/feature in MapBasic?

Question

I'm trying to extend this GNU-licensed plugin MI2SVG, which converts MapInfo maps to SVG, so that it writes associated table data into the SVG it creates, so that browser based JS web applications can read map data from MapInfo-created interactive maps.

It's going well except I can't find the MapBasic way to loop through all columns for one row in a layer, in a way that gives the index (column name or name) in each iteration alongside the data.

The only MapBasic loop examples I can find are for looping through table rows using the very specific Do While Not EOT(), which seems to be always used for rows and doesn't appear to give each iteration's index (or if it does, I can't see where).

---

So for example, here's code that loops through rows in a layer table i.e. features in a map and prints the contents of column 1 into a string:

sLayer = LayerInfo (nMap, nLayer, LAYER_INFO_NAME)

'Initialize alias variables
aObj = sLayer + ".obj"
aRow = sLayer + ".rowid"
aCol = sLayer + ".col1"   

'Convert each feature in the layer to SVG
Fetch First From sLayer
Do While Not EOT(sLayer)
    objA = aObj
    nRow = aRow
    sCol = aCol
    'some code that prints SVG like "<path name="""&sCol&...etc

How would you loop through all the columns, getting index and values for each? My ideal output would be, instead of name="""&sCol&""" a string like this: data-col1="[col1data]" data-col2="[col2data]" data-col3="[col3data]" for tables with any number of columns, printing the index (name or number) and value concatenated with some other text for each column.

---

I'm guessing it would be something like this (but with ???s replaced with actual things I can't find in the MapBasic reference and User Guide):

Fetch First From sLayer
Do While Not EOT(sLayer)
    objRow = aObj
    sOutput = ""

    Fetch First From objRow
    Do While Not ???(objRow)
       sColumnName = ????Info ( someArgs?, ????_????_NAME )
       nColumnIndex = ?increment???

       'Not sure what type for this variable since data could be string or numeric?
       ?ColumnData =  objRow.col(nColumnIndex)

       outputString = outputString & " data-"&sColumnName&"="""&?ColumnData&"""

...or maybe instead of Do While Not ???(objRow) a for..next loop :

nColumnCount = objRow.???NumberOfColumns??? 'or' TableInfo( nLayer??, TAB_INFO_NCOLS ) 
for nCounter 1 To nColumnCount
   sColumnName = ????Info ( someArgs?, ????_????_NAME )
   ?ColumnData = objRow.col(nColumnIndex)
   'some code that prints SVG like "<path name="""&sCol&...etc
next

...but despite wading through the MapBasic reference, user guide and trying to fill in the gaps with http://testdrive.mapinfo.com/ I can't find what to use to fill those blanks.

Answer 1

You can use this structure to loop through the columns of a table.

Dim sCol As String
Dim aCol As Alias
Dim nCol As Integer
For nCol = 1 To TableInfo(sLayer, TAB_INFO_NCOLS)
   sCol = ColumnInfo(sLayer, "COL" & nCol, COL_INFO_NAME)
   aCol = sLayer & "." & sCol
Next   

This will loop through the columns of the table sLayer and store the name in an Alias variable. The next step is to transfer the values from the column to a variable and to be able to write these to an SVG file for other systems to read them back.

To be able to do that in a nice manner you need to convert the data to something that will make sense to this other system.

Here is an example of how that can be done including the loop mentioned above:

Dim sCol As String
Dim aCol As Alias
Dim nCol As Integer
For nCol = 1 To TableInfo(sLayer, TAB_INFO_NCOLS)
   sCol = ColumnInfo(sLayer, "COL" & nCol, COL_INFO_NAME)
   aCol = sLayer & "." & sCol
   sValue = ""
    Do Case ColumnInfo(sLayer, sCol, COL_INFO_TYPE)
       Case COL_TYPE_CHAR
          sValue = aCol
       Case COL_TYPE_INTEGER, COL_TYPE_SMALLINT, COL_TYPE_LOGICAL
          sValue = Str$(aCol)
       Case COL_TYPE_FLOAT, COL_TYPE_DECIMAL
          'Converting decimal number to locale formatted numbers  
          '(Windows Regional Settings)
          sValue = FormatNumber$(aCol)
       Case COL_TYPE_DATE
          If aCol Then
             'Converting dates to locale formatted dates
             '(Windows Regional Settings)
        sValue = FormatDate$(aCol)
          End If
       Case COL_TYPE_TIME
          If aCol Then
             'Converting Time to 24 hour clock
             sValue = FormatTime$(acol, "HH:mm:ss")
          End If
       Case COL_TYPE_DATETIME
          If aCol Then
             'Converting dates to locale formatted dates
             '(Windows Regional Settings)
             'Converting Time to 24 hour clock
             sValue = FormatDate$(aCol) & " " & FormatTime$(GetTime(acol), "HH:mm:ss")
          End If
    End Case
    Next  

If you want to add a loop that runs through all the records of the layer too, you will have to wrap the loop above into another Do Until..Loop structure. In this structure, you use the Fetch statements to first fetch the first and then continue to fetch the next record until the cursor is at End Of Table (EOT).

Here's an example of that:

Dim sCol As String
Dim aCol As Alias
Dim nCol As Integer

Fetch First From sLayer
Do Until EOT(sLayer)
   For nCol = 1 To TableInfo(sLayer, TAB_INFO_NCOLS)
      sCol = ColumnInfo(sLayer, "COL" & nCol, COL_INFO_NAME)
      aCol = sLayer & "." & sCol
      sValue = ""
       Do Case ColumnInfo(sLayer, sCol, COL_INFO_TYPE)
          Case COL_TYPE_CHAR
             sValue = aCol
          Case COL_TYPE_INTEGER, COL_TYPE_SMALLINT, COL_TYPE_LOGICAL
             sValue = Str$(aCol)
          Case COL_TYPE_FLOAT, COL_TYPE_DECIMAL
             'Converting decimal number to locale formatted numbers  
             '(Windows Regional Settings)
             sValue = FormatNumber$(aCol)
          Case COL_TYPE_DATE
             If aCol Then
                'Converting dates to locale formatted dates
                '(Windows Regional Settings)
                 sValue = FormatDate$(aCol)
             End If
          Case COL_TYPE_TIME
             If aCol Then
                'Converting Time to 24 hour clock
                sValue = FormatTime$(acol, "HH:mm:ss")
             End If
          Case COL_TYPE_DATETIME
             If aCol Then
                'Converting dates to locale formatted dates
                '(Windows Regional Settings)
                'Converting Time to 24 hour clock
                sValue = FormatDate$(aCol) & " " & FormatTime$(GetTime(acol), "HH:mm:ss")
             End If
       End Case
    Next  
    Fetch Next From sLayer
Loop

Hope that helps