Hi George,
You can do something like the following:
First, declare new variable sValue:
Dim sValue As StringDim sValue As String
Then, before your update statement, use this:
Do Case ColumnInfo("temptable", sColName, COL_INFO_TYPE)
' Handle String Fields
Case COL_TYPE_CHAR
sValue = "99999"
' Handle Number Fields
Case COL_TYPE_DECIMAL, COL_TYPE_INTEGER, COL_TYPE_SMALLINT, COL_TYPE_FLOAT
sValue = 99999
Case Else
' Should report an error here
End Case
Then you update statement becomes:
Update "g_"+sColName set aColName=sValue
------------------------------
James Nolet
Dooley Mitchell & Morrison Pty Ltd
Mentone, VIC, Australia
------------------------------
Original Message:
Sent: 03-18-2020 21:23
From: George Corea
Subject: Get Field type in mapbasic
I am working on a script that checks for values of 0 or null or "" and replaces them with "9999" and "Unknown" but I am stuck at separating the float/integer fields from the string fields so I get type mismatch errors. How can I work out what type a field is?
This is built to run on many tables without specifying the table name so it has to be an automated way to get the field table info
'Updates 0 and null values to 9999 or Unknown.
' Add to the following list {("As_con_2_hyperlink","As_con_3_hyperlink")} in line that starts with 'if (sColName not in' to ignore additional fields
Include "MapBasic.def"
Include "Menu.def"
Declare Sub Main()
Dim filename, printtext,WFolder as string
Dim rcount,i as Integer
Sub Main()
Close All
'OnError GoTo CaughtEx '// Turn Error handling on
WFolder=ApplicationDirectory$()+"\"
Register Table WFolder+"List.txt" TYPE ASCII Delimiter 44 Titles Charset "WindowsLatin1" Into WFolder+"~MAP0001.TAB"
Open Table WFolder+"~MAP0001.TAB" Hide
Commit Table ~MAP0001 As WFolder+"List.TAB"
Close Table ~MAP0001
Open Table WFolder+"List.TAB" as List Interactive
Browse * From List
rcount=TableInfo(List,TAB_INFO_NROWS)
i=1
While i<=rcount
Select * from List where List.RowID=i into RSelection
Browse * from RSelection
'print RSelection.col1
Open Table WFolder+RSelection.col1 as temptable
'////
'START Unique per table process
'Get Column Name list
dim x as integer
dim sColName as string
dim aColName as Alias
For x=1 to TableInfo(temptable, TAB_INFO_NCOLS)
sColName = ColumnInfo(temptable, "col"+str$(x), COL_INFO_NAME)
if (sColName not in ("Asset_ID","GID","GID_New","Pipe_ID","Spiceworks_ID","Date_Asset_added","Date_Asset_Modified","As_con_2_hyperlink","As_con_3_hyperlink")) then
aColName = sColName
Select * from temptable where aColName=0 into "g_"+sColName
Browse * from "g_"+sColName
print sColName
Update "g_"+sColName set aColName="99999"
Commit Table temptable
End If
Next
'Browse * From temptable
'Select Pipe_Type from temptable group by Pipe_Type into temptable_group_PipeType
'Browse * From temptable_group_PipeType
'END Unique per table process
'////
i=i+1
close table temptable
Wend
CaughtEx: '// error handling routine
'// Log error here...
print Chr$(13)+Chr$(13)+"///////MAIN///////"+Chr$(13)+"FAILED due to:"+Chr$(13)+Error$()+Chr$(13)+"\\\\\\\\\\\\\\\"+Chr$(13)
Resume Next '// resume code at line after the one which threw the error
End Sub
So where it has
' Select * from temptable where aColName=0 into "g_"+sColName
I would like to check if string or float/int and then something like
if fieldtype=string
then
Select * from temptable where aColName="" or aColName=Null into "g_"+sColName
Update ... ="Unknown"
else
Select * from temptable where aColName=0 into "g_"+sColName
Update...=9999
The input list.txt file is created by running a .bat file with
echo File >List.txt
dir *.tab /w/b >>List.txt
Pause
------------------------------
George Corea
Mangoesmapping
------------------------------