Removing table columns stored in an array

[Tim Smith]

Hi List,

I used to remove table columns easily, as I knew the column names at
compile time, so they were hard-coded, e.g.

Alter Table Streets_temp (Drop St_LangCode, Num_StNames, StNm_Pref,
StType_Before, StNm_Base, StNm_Suff)

But now I only know how the column names to remove at run-time. I'm
currently removing them one-by-one as I don't know how to do 'Alter
Table' with an array of columns.

This is what I do at present, but my table might need 70 columns
removed which takes ages when removing one-by one. This func removes
all columns other than the ones specified..

Sub keepTheseColumns(ByVal table As String, columnsToKeep() As String)
Dim numColumnsToKeep , numColumns, numColumnsToRemove As Integer
Dim columnsToRemove() As String
numColumnsToKeep = UBound(columnsToKeep)
numColumns = NumCols(table)
Dim currentColumnName As String

Dim i,j As Integer
Dim removeThisColumn As Logical

Print "Num columns to keep " + Str$(numColumnsToKeep)
Print "Num columns " + Str$(numColumns)

' Find columns that should be removed
For i = 1 To numColumns
removeThisColumn = TRUE
For j = 1 To numColumnsToKeep
currentColumnName = ColumnInfo(table,"col" + Str$
(i),COL_INFO_NAME)
If currentColumnName = columnsToKeep(j) Then
removeThisColumn = FALSE
End IF
Next
If removeThisColumn = TRUE Then
numColumnsToRemove = UBound(columnsToRemove) + 1
ReDim columnsToRemove(numColumnsToRemove)
columnsToRemove(numColumnsToRemove) = currentColumnName
End If
Next

Print "Number of columns to remove " + Str$(numColumnsToRemove)

' Remove unwanted columns
For i = 1 To numColumnsToRemove
Print "Removing column " + Str$(i)
Alter Table table (Drop columnsToRemove(i))
Next

End Sub


Any suggestions would be appreciated.

Kind regards

Tim

[Driver, Greg 9434]

Tim,

One possible solution would be to build the 'Alter table' command as a
string adding each column name to it and then using 'Run Command' to
execute it:

Dim CmdStr As String

CmdStr = "Alter Table " + table + " (Drop "

For i = 1 To numColumnsToRemove
Print "Removing column " + Str$(i)

CmdStr = CmdStr + columnsToRemove(i)
If I <> numColumnsToRemove Then
CmdStr = CmdStr + ", "
Else
CmdStr = CmdStr + ")"
End If
Print CmdStr
Run Command CmdStr
Next

End Sub

You might also find it easier to use col1, col3 etc instead of the
actual column name, as this will make the string shorter.

HTH

Greg Driver

System Administrator
Applications Support
ICT
Surrey Police
NOT PROTECTIVELY MARKED

*Internet communications are not secure and therefore Surrey Police does not accept legal responsibility for the contents of this message. This email and any attachments may be confidential. They may contain privileged information and are intended for the named addressee (s) only. They must not be distributed without our consent. If you are not the intended recipient, please notify us immediately and delete the message and any attachments from your computer, do not disclose, distribute, or retain this email or any part of it. Unless expressly stated, opinions in this email are those of the individual sender, and not of Surrey Police. We believe but do not warrant that this e-mail and any attachments are virus free. You must therefore take full responsibility for virus checking. Surrey Police reserves the right to monitor all email communications through their networks.*

[Tim Smith]

Oh, thanks Greg, I didn't know of Run Command. I'm new to Mapbasic -
only been doing it for a couple of weeks in anger now.
Now I could probably re-write my application knowing about that!!!
Cheers

Tim