0

I need to run a table update command on every column in a table. The code works fine when I manually define the column to work in but not when it is part of a for loop that gets all the column names I get enter image description here

If I take the update table command out of the loop and into the code after the for loop, it works fine.

What's different with defining a declare for use in a loop?

'64 bit regex based on https://community.pitneybowes.com/communities/communityhome/digestviewer/viewthread?MessageKey=a92a0fcd-5348-4eae-aa93-a47d5a1e6bbe&CommunityKey=3c2aca7a-c3ae-4602-a142-9ee956769d55&tab=digestviewer#bma92a0fcd-5348-4eae-aa93-a47d5a1e6bbe'
Include "MapBasic.def"

'64 bit regex'
Declare Method RegExReplace Class "WindowHelper.Controller" Lib "WindowHelper.dll" (ByVal sInput As String, ByVal sPattern As String, ByVal sReplace As String) As String

Declare Method RegExIsMatch Class "WindowHelper.Controller" Lib "WindowHelper.dll" (ByVal sInput As String, ByVal sPattern As String) As Logical

Declare Method RegExFirstMatch Class "WindowHelper.Controller" Lib "WindowHelper.dll" (ByVal sInput As String, ByVal sPattern As String) As String

Declare Sub Main

Sub Main
    Dim inFile, regexString as String
    Dim inColumn as Alias

    inFile= "Z:\My Drive\Mangoesmapping\Spatial Projects\2018\DSC\007_DSC_Asset_Identification_and_Geocoding\Working\Phase3_DataEnrichment\Working\DataUpdates\Scratch\Water_Hydrants.TAB"

    'Perform Regex and text Replace'
    Open Table inFile as tempTable
    'Alter Table tempTable (add ScratchField Char(254) ) Interactive
    Browse * From tempTable
    regexString ="^\s\|\s(.*)"

    ' Perform on all fields -ref: https://gis.stackexchange.com/questions/75494/how-to-loop-through-all-columns-in-a-table-layer-getting-all-data-for-a-row-feat    
    Dim sCol As String
    Dim aCol As Alias
    Dim nCol As Integer
    For nCol = 1 To TableInfo(tempTable, TAB_INFO_NCOLS)
        sCol = ColumnInfo(tempTable, "COL" & nCol, COL_INFO_NAME)
        aCol = tempTable & "." & sCol
    'Update every column as it's found --- GIVES ERROR          
        'Update tempTable Set ScratchField = RegExReplace(aCol,regexString,"")      
        Next        
    'Update a known column
    inColumn = tempTable & ".As_con_3_hyperlink"
    Update tempTable Set ScratchField = RegExReplace(inColumn,regexString,"")           
End Sub

UPDATED CODE based on T_Bacon's input.

Had to change scratchField to aCol as it is now checking every column.

Sub Main
    Dim inFile, inField, regexString, strValue as String

    inFile = "Z:\My Drive\Mangoesmapping\Spatial Projects\2018\DSC\007_DSC_Asset_Identification_and_Geocoding\Working\Phase3_DataEnrichment\Working\DataUpdates\Scratch\Water_Hydrants.TAB"
    inField = ".As_con_3_hyperlink"
    'Perform Regex and text Replace'
    Open Table inFile as tempTable
    'Alter Table tempTable (add ScratchField Char(254) ) Interactive
    Browse * From tempTable
    'Pattern for starting | without anything before it
    regexString ="(.*)[\s]+\|\s(.*)"

    ' Perform on all fields -ref: https://gis.stackexchange.com/questions/75494/how-to-loop-through-all-columns-in-a-table-layer-getting-all-data-for-a-row-feat    
    Dim sCol As String
    Dim aCol As Alias
    Dim nCol As Integer
    For nCol = 1 To TableInfo(tempTable, TAB_INFO_NCOLS)
        sCol = ColumnInfo(tempTable, "COL" & nCol, COL_INFO_NAME)
        aCol = tempTable & "." & sCol
    'Update every column as it's found --- GIVES ERROR ref: https://gis.stackexchange.com/questions/316048/get-code-to-run-on-every-column-in-a-table-error-could-not-find-a-public-stati       
        strValue = aCol
        Update tempTable Set aCol = RegExReplace(strValue, regexString, "")         
        Next

End Sub
Improve this question

1 Answer 1

Reset to default
2

It is probably not working because you are passing it the alias variables acol and inColumn and it is expecting a string. Try storing the required parameters into a string variable and passing that to the function.

e.g.

strValue = aCol
Update tempTable Set sCol = RegExReplace(strValue, regexString, "")
Improve this answer
4
  • The issue is that aCol and inColumn (which are basically the same) have to be alias values as they are the names of a column. You can't pass just the name of a column as a string into an update table command (I tried earlier) - see support.pitneybowes.com/… Also it works fine as an alias (inColumn) when you run the update command outside of the for loop (line before End Sub). So the issue is probably something in the declare for the regex command not being recognized in the for loop...
    – GeorgeC
    Commented Mar 20, 2019 at 8:35
  • 1
    Yes, but you are not using the alias column directly in the update statement, you are passing it as a parameter to a function that returns a value to the update statement. The update is only concerned with the value returned by the function, it has no visibility of the alias variable. The function doesn't want an alias variable, but a string, so it would seem to me that's where your problem lies. I've updated my answer to show how it could be written.
    – T_Bacon
    Commented Mar 20, 2019 at 8:57
  • I now get Expression doesn't evaluate to column or table name. I'll put the modified code in the Q.
    – GeorgeC
    Commented Mar 20, 2019 at 9:19
  • 1
    Instead of using aCol in the update statement, try using sCol. I think the update statement just wants the column name as you've already specified what table with tempTable. I've updated my answer with the change.
    – T_Bacon
    Commented Mar 21, 2019 at 8:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.