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

• sn1ks: this only reads the first line of the table right? how would you go through all lines of a table?
• Peter Horsbøll Møller: I have added such an example to my answer above