Sqlite: Is this assumption Safe?

Databases
1

I have some code:

  Dim RS As RecordSet = DB.SQLSelect("SELECT COUNT(PeakNo), SUM(area) FROM Peaks WHERE SampleKey = "+StrKey)
  Dim TotalPeaks as Integer  = RS.IdxField(1).IntegerValue
  Dim TotalArea as Double = RS.IdxField(2).DoubleValue

If I set a beak after the select, in the debugger, for the case where no Peaks are found, the debugger says that COUNT(PeakNo) = 0 But also that SUM(area) = NIL.

But I DO NOT get a NIL object exception on the TotalAreaLine and instead get 0 (which is exactly what I want and makes sense here). I assume that would be the same for any NULL field in in a result set… I wrote a lot of code that depends on that behavior without thinking a lot about it…

But thinking about it now, I’m not sure that is a safe assumption. i worry that it might throw a NIL object exception in some future version (or in the new supper picky framework) in the future.

The current behavior makes sense as it is what is wanted most of the time IMO., and if one wants to, one can check for NIL to determine if the field was actually NULL… but with the pickier ( and less RAD IMO) direction Xojo is going, I wonder if this assumption is safe to make.

Can I count on this behavior continuing, or should I modify my code (which will be time consuming)?

Thanks

  • karen
2

Not sure what the problem is. As usual you have to check your results if they are nil or not if you don’t want your code to bomb at your user.

3

Another option might be to rewrite the query so that it returns 0 instead of NULL.

For example:

"SELECT COUNT(PeakNo), COALESCE(SUM(area),0) FROM Peaks WHERE SampleKey = "+StrKey

That way, regardless of how Xojo handles the NULL value in the future, you’re guaranteed to get zero as the result.

4

In all versions of the the SQLIte database classes in Xojo &RS a NULL field returns 0 for a numeric value (and I assume “” for String values) and does NOT throw a NIL object exception so there is no need to check if the recordSet field is NIL despite teh fax that it shows up that way in the debugger. Because it was intuitive behavior (and I never noticed the NIL in the debugger until now) I never gave it a second thought…

I suspect it was originally an intended behavior (which IMO is intuitive), but given the seeming change in philosophy at Xojo Inc It occurred to me to worry about it

Tim,

Your suggestion is a good one for teh future,
But I have a lot of code that depends on the current behavior … It would be nice to hear from some at Xojo if I need to worry about changing it.

  • karen
5

You change the schema and rename some fields but you forget to change all queries. Your cat walks over the keyboard and does a typo. Therefore, always do your nil checks.

6

There is no way for that code to return a nil object. I wouldn’t worry about it.

7

This has to do with Variants and how they handle Nil for the intrinsic types, i.e., they will return the equivalent of “no value” that type.

dim v as variant = nil
dim b as boolean = v // False
dim d as double = v = // 0.0
dim i as integer = v  // 0
dim s as string = v // ""

In short, you don’t have to worry about it.

8

Kem is right. The DatabaseField accessed by IdxField or by Field property of RecordSet is a Variant.

My applications support NULL values, so the users know if a value has been entered at all. To check for this I do:

Dim s As String Dim v As Variant = rst.Field("...") If v Is Nil Then s = NilString Else s = v.StringValue.DefineEncoding(...) End
NilString is – depending on the sort order the end user has chosen – a value lower than the lowest printable character or higher than the highest printable character. In controls these values are shown in light grey as “(Null)”. Same for Integers, Doubles, Dates, etc.