[GIS] MapBasic – Updating blank rows in column with the value above

mapbasicmapinfosql

I am writing a MapBasic tool to correct some incorrect values in a set of tables. So far I have managed to pull out the values I need in a new column:

What I need is to update any blank rows in the OBSERVATION column with the value above it (the L*** text).

How would I go about doing this in MapBasic?

Best Answer

Something along these lines:

Dim nRowID As Integer,
    sValue As String

Fetch First From MY_TABLE
Do Until EOT(MY_TABLE)
   If MY_TABLE.OBSERVATION = "" Then
      nRowID = MY_TABLE.ROWID
      Update MY_TABLE
         Set OBSERVATION = sValue
         Where ROWID = nRowID
   Else
      sValue = MY_TABLE.OBSERVATION
   End If

   Fetch Next From MY_TABLE
Loop

Note that the loop remembers the last read value from the column OBSERVATION. This also means that the value used might not be taken from the record before – it could be a record before that in case you have multiple row without any value.

Related Solutions

MapBasic – How to Loop Through All Columns in a Table/Layer Getting All Data for a Row/Feature in MapBasic

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

[GIS] How to write to Sql server from MapBasic and insert rows

Make sure that you have specified a unique primary key in the table in SQL Server. Also you could take advantage of the IDENTITY type so that you don't have to manage the ID's yourself.
Make sure your text columns are defined as varchar and that they aren't wider than 254. Otherwise you might have an issue editing the data
Once the table is opened in MapInfo Professional your spatial column is always called OBJ. So use this insert statement:

Insert into TestGeom1 (OBJ, RouteId, Desc) VALUES (oPline, rId, t)

If you have a statement that works in the MapBasic window but doesn't work in the MapBasic application, you should have a look at your variable and function names. Maybe you have a variable using the same name as a table or column.

Also try to create the table in the database from MapInfo Professional using File > New, when specifying the location of the table, pick an open connection to your database. In this way you can create a new table directly in your database

If you use the Per Row Style option, the width of the column might prevnet you from editing the object. If the column is wider than 254, MapInfo Pro can't edit the column and so the spatial object also gets read-only

Best Answer

Related Solutions

MapBasic – How to Loop Through All Columns in a Table/Layer Getting All Data for a Row/Feature in MapBasic

[GIS] How to write to Sql server from MapBasic and insert rows

Related Question