How to deal with null value coming from Database

[dmarcicLIH]

Hi,
I am testing your ORM, it seems great and pretty well done but i have an issue with null values coming from Table in the database. Specially in the IDatabaseObject_Load method.

For the string i have use this syntaxe with the IIF Test and it's ok :
Me.ShipRegion = IIf(IsNull(objFields("ShipRegion")), vbNullString, objFields("ShipRegion"))

But for other types, as there are no equivalent to vbNullString if i set them directly like that :
Me.Height = objFields("Height")
with objFields("Height") returning null and Height set as Integer, I receive a null exception, which is normal in VB6.

So, please can you tell me how do you deal in the mapping of your null value from Integer, Long, Date variables... in the projects where you used this ORM please ?

Thanks
David

[hisystems]

Have you tried using IsNull - that should work.

I have the following set of functions that convert a NULL to a value:

Public Function CNullBoolean(ByVal vValue As Variant) As Boolean

    If IsNull(vValue) Then
        CNullBoolean = False
    Else
        CNullBoolean = CBool(vValue)
    End If
    
End Function

Public Function CNullLong(ByVal vValue As Variant) As Long

    If IsNull(vValue) Then
        CNullLong = 0
    Else
        CNullLong = CLng(vValue)
    End If

End Function

Public Function CNullStr( _
    ByVal vValue As Variant) As String

    If IsNull(vValue) Then
        CNullStr = vbNullString
    Else
        CNullStr = vValue
    End If
    
End Function

[dmarcicLIH]

Hi,
thank for your answer,
Yes i thought at this possibility but i am scared that this method doesn't preserve the null value on my Db after the save Event. for example, if i have got a null value on my Db for a boolean as the user doensn't choose between true or false for a variable at its first save.
When he will reload the object, the property of my Object will be setted to false accorded to the CNullBoolean function. Then when he will save again this object, the Save method will parse this value and it will stored false in the database, even if the user doen't technically set it.
I just would like to keep the null value on my DB if the property have never been set as I have this particular needs ...
I know i could declared them all as Variant but not very maintenable for me.
I am looking at creating Nullable type object for each Integer, Boolean, Date... But maybe i am going to far and i will simply use your kind of function and conclude that we will wait a migration to vb.net for considering keeping nullable value in DB ;)
thanks a lot for your feedback ;)

[hisystems]

Sure makes sense. Yeah, you could use a NullableBoolean, for each type etc. I guess it somewhat depends on what you're doing, however in my experience that is very rarely needed, it might be worth considering whether it really matters.

Anyway, as far as the library goes, field values (DBO.SQLFieldValues) that are passed to IDatabaseObject_Save and IDatabaseObject_Load are of type Variant to obviously cater for different data types and for when the field is NULL. So, you can still maintain the NULLable state of a database field if that is what you need.

So if you read a NULL value, use the IsNull function. If you want to write a NULL value use Nothing:

Public Enum NullableBoolean
    Yes
    No
    Unset
End Enum

Private fieldValue As NullableBoolean

Private Sub IDatabaseObject_Load(ByVal objFields As DBO.SQLFieldValues)

    If IsNull(objFields("NullableField").Value) Then
        fieldValue = NullableBoolean.Yes
    Else
        fieldValue = IIf(objFields("NullableField").Value, NullableBoolean.Yes, NullableBoolean.No)
    End If

End Sub

Private Function IDatabaseObject_Save() As DBO.SQLFieldValues

    Dim objFields As SQLFieldValues
    Set objFields = New SQLFieldValues
    
    If fieldValue = NullableBoolean.Unset Then
        objFields.Add "NullableField", Nothing
    Else
        objFields.Add "NullableField", fieldValue
    End If

    Set IDatabaseObject_Save = objFields

End Function

[dmarcicLIH]

yes thanks very interesting