How to determine a null value?

[endo]

Yeah I got the point, thanks a lot. Now we'll wait for Fred, he may add/find a way to handle null values in PB.
If I have enough time I'll also try to do it by using ODBC APIs.
Thanks again.

[deeproot]

Very sorry to bump this 2yr old topic, but I have the same problem as endo.

I also need a reasonably simple test for a NULL value column in an SQLite database. Has there been any more recent update on this? Have I missed something?

In my case, testing within the SQL query is not practical - yes it would work, but the additional complexity for this already very large database project would be extremely difficult.

I am planning to use only native PB SQLite if possible. In another development wrapper (for mobile devices) there is a "IsDBNull(column)" function that works perfectly. The original product uses IsNull in VB6.


BTW this is my first post and I'm still a PB newbie - please be gentle if I ask some stupid questions! I have so far only written a couple of simple programs. Now it is time to start work on the big one. I have already had very many questions - all answered just by searching this fantastic forum.

[endo]

Hi, I guess this issue is still open. There is no way to determine if a db field value is null or not.
Btw, I read the whole thread again, we actually don't need a NULLable variable type in PB. We just need to test if a db field value is null or not.

I mean s.s = GetDatabaseString(0, "col") can return empty string (0 for numeric types) for null values, but what we need is

Code: Select all

if IsDatabaseNull(0, "col")
  debug "null"
else
  debug GetDatabaseString(0, "col") ;this can be empty-string also.
endif

So there is no need to IsNull() function for all PB variables, just IsDatabaseNull().

[Puffolino]

Any chance to have something like that...

Code: Select all

HeartRate=GetDatabaseLong(0,#TrackPointsHeartRate)
If GetDatabaseState()=#PB_Database_ValueNotNil
	:
EndIf

I am doing things like that now, which can only be done easily for integer values, not for floats...

Code: Select all

String.s=GetDatabaseString(0,#TrackPointsHeartRate)
If String
	HeartRate=Val(String)
	:
EndIf

[deeproot]

This appears to work OK for me:-

Code: Select all

Protected.f MyNum
If Len(GetDatabaseString(#dbPM,32)) > 0
    MyNum = GetDatabaseFloat(#dbPM,32)                                       
    Debug "height = " + StrF(MyNum)
    ; ..... do something with float value
Else
    Debug "height is Null"
   ; ..... do something else
EndIf

(using SQLite, Winx86, PB 4.40 through 4.61)

As previously mentioned, it's also possible to use an SQL statement to test for Null, but this can be tricky when the SQL is already complex.

String columns with Nulls are still an issue though - I already made a post under Requests and Wishlists - viewtopic.php?f=3&t=49860

[Puffolino]

deeproot, what do you think about the help file statement about using GetDataBase() statements only a single time per column – could there be a problem using GetDataBaseString followed by GetDataBaseDouble (Float, Long etc.) for accessing the same database element?

[deeproot]

using GetDataBase() statements only a single time per column – could there be a problem using GetDataBaseString followed by GetDataBaseDouble

As I understand it (correction welcome!) that would indeed be a problem if using an ODBC connection, but repeat references to a column do not appear an issue with the built-in SQLite.

The Help file says "This is an ODBC limitation" and as SQLite doesn't use it, I just tried it - never been a problem so far! I don't know what the situation is with PostgreSQL?

[endo]

Finally we got CheckDatabaseNull() function on PB v5.10 to check NULL values.

[deeproot]

Finally we got CheckDatabaseNull() function on PB v5.10 to check NULL values.

Yes - it's perfect for my purposes! I have already used CheckDatabaseNull in many places and it has helped the code a lot.

With the release of 5.10 I'm hoping to go live with my own main project very soon!

[endo]

The only thing left is ability to connect a database server using connection string instead of DSN.