Page 2 of 2
Posted: Fri Sep 19, 2008 7:21 am
by 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.
Re: How to determine a null value?
Posted: Thu Oct 21, 2010 2:16 pm
by 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.
Re: How to determine a null value?
Posted: Thu Oct 21, 2010 3:12 pm
by 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().
Re: How to determine a null value?
Posted: Fri Jul 27, 2012 3:35 pm
by 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
Re: How to determine a null value?
Posted: Sat Jul 28, 2012 1:18 pm
by 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
Re: How to determine a null value?
Posted: Mon Jul 30, 2012 12:39 pm
by 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?
Re: How to determine a null value?
Posted: Mon Jul 30, 2012 2:10 pm
by deeproot
Puffolino wrote: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?
Re: How to determine a null value?
Posted: Tue Feb 26, 2013 12:16 pm
by endo
Finally we got CheckDatabaseNull() function on PB v5.10 to check NULL values.
Re: How to determine a null value?
Posted: Tue Feb 26, 2013 3:08 pm
by deeproot
endo wrote: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!
Re: How to determine a null value?
Posted: Wed Feb 27, 2013 11:14 am
by endo
The only thing left is ability to connect a database server using connection string instead of DSN.