if rs.Column( "storedID" ) <> Nil then // <-- throws InvalidArgumentException when the column is Nil
rows(k).Column("storedID").IntegerValue = rs.Column( "storedID" ).IntegerValue
else
rows(k).Column("storedID").IntegerValue = rs.Column( "ID" ).IntegerValue
end if
This code used to work, using RecordSet. If the column is there, read its value; if not, use another value that we know exists.
Using RowSet, this throws a InvalidArgumentException when the column doesn’t exist.
Can someone please show me how this supposed to be done using RowSet? Thanks.
Thanks but no. I didn’t show it above but rs has already been checked for Nil. It’s the COLUMN that’s Nil.
RowSet requires using try … catch blocks for everything, but I can’t see how a try … catch block is going to work for what needs to be done here. This is why I’m asking for help.
try
if rs.Column( "storedID" ) <> Nil then
rows(k).Column("storedID").IntegerValue = rs.Column( "storedID" ).IntegerValue
end if
catch e as InvalidArgumentException
rows(k).Column("storedID").IntegerValue = rs.Column( "ID" ).IntegerValue
end try
This is happening in a loop which may be checking a million records and the debugger breaks every time this happens, which is … just slightly annoying. Am I supposed to wrap this in #Pragma BreakOnExceptions False ?? I can’t imagine that would be the right way to do it.
The Message is: “No column with name storedID”, which is obvious, that’s why I’m checking it for Nil, to see if it exists. Totally absurd. There must be some other way I’m supposed to be checking if a column exists?
Seems to me that either the column exists for all rows, or for none. Perhaps you should check for its existance once before entering any loop, set a flag to say yes/no, then enter your loop.
The thing is, what you’re doing (selecting unknown columns) is the exception, not the norm. I haven’t once needed to nil check a column in all my time. I haven’t seen this change in design come up here in the forum since API 2.0 launched 5 years ago.
Nil checking the column does seem like a fun trick to do, but I see the value in having an InvalidArgumentException that tells you what column is missing. A NilObjectException, especially when you’re not expecting a column to be missing, will be a difficult hunt with no clues.
It leads me to ask “Why are you selecting * from a table where you don’t know what you’re going to get?” - but that doesn’t make the design cleaner.
There speaks reason … this is old code and I was trying not to change anything, but you’re right.
dim useID as Boolean
#Pragma BreakOnExceptions False
try
dim j as integer = rs.Column( "storedID" ).IntegerValue
catch e as InvalidArgumentException
useID = true
end try
#Pragma BreakOnExceptions True
Then check the flag in the loop and read the value accordingly.
@Aaron_Hunt, if you’re refactoring, consider checking the columns of the RowSet directly. You’ve got ColumnCount, ColumnAt which returns a DatabaseColumn, which gives you access to the name, type and value.
I can see why it seems a bit weird. The method is actually an Extends on rs as Rowset (used to be RecordSet), so the rows can be coming from many places, the origin isn’t known (isn’t passed) and the column may be there or not.
Public Function HasField(extends aRecordSet as RecordSet, aFieldName as String, dontCheckForNull as Boolean=False) As Boolean
' checks if the given RecordSet contains a field with the given name
' and that the field is not empty or null
dim res as Boolean = False
dim maxCount as Integer = aRecordSet.FieldCount
for i as Integer=1 to maxCount
If aRecordSet.IdxField(i).Name=aFieldName Then
If Not dontCheckForNull Then
if aRecordSet.IdxField(i).StringValue<>"" then
res = True
exit For
End If
Else
res = True
Exit For
End If
End If
next
Return res
End Function
Public Function HasColumn(extends rs as RowSet, columnName as String) As Boolean
' checks if the given RowSet contains a column with the given name
dim found as Boolean
dim lastIndex as Integer = rs.ColumnCount - 1
for i as integer = 0 to lastIndex
if rs.ColumnAt( i ).Name = columnName then
found = true
exit for
end if
next
return found
End Function
NOTE: I didn’t need / want the “check for null” part so removed it.
A good way to avoid the clumsy exception handling. Greg O had pointed me also in this direction. Allows me to do this instead:
dim useID as Boolean = not rs.HasColumn( "storedID" )
