Results 1 to 25 of 25

Thread: [RESOLVED] Sqlite how to check if a field is empty?

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Resolved [RESOLVED] Sqlite how to check if a field is empty?

    Hello VbForums
    I'm using Sqlite and RC5
    I want to chek if a Text type field is empty.
    I'm using;
    Code:
    If  IsNull(Rs!Fieldname)
    If Rs!Fieldname = ""
    but with no result.
    Though I could easil see the fileld as Null
    Name:  0pic.png
Views: 2516
Size:  1.8 KB
    thanks

  2. #2
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,412

    Re: Sqlite how to check if a field is empty?

    Try IsEmpty(Rs(FieldName))

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Jan 2016
    Posts
    753

    Re: Sqlite how to check if a field is empty?

    Quote Originally Posted by jpbro View Post
    Try IsEmpty(Rs(FieldName))
    Thank you. It worked

  4. #4
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Sounds like these pseudo-Recordsets are broken. This isn't compatible behavior.

    Perhaps a bug?

  5. #5
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,997

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Why "pseudo"? They are recordsets of a different technology/implementation.

    If they wanted to make them backward compatible with MS ones, perhaps it is a bug because MS uses null instead of empty for unset values.

  6. #6
    Addicted Member Wolfgang Enzinger's Avatar
    Join Date
    Apr 2014
    Location
    Munich, Germany
    Posts
    160

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Quote Originally Posted by Eduardo- View Post
    Why "pseudo"? They are recordsets of a different technology/implementation.

    If they wanted to make them backward compatible with MS ones, perhaps it is a bug because MS uses null instead of empty for unset values.
    SQLite itself indeed does use NULL, there is no such thing as Empty in SQLite.

    The behaviour we see here is specific to RC5. I discussed this with Olaf a while ago; it was meant to be a handy feature to avoid runtime errors when you assign a field value to a variable type other than Variant without checking for NULL first.

    Assigning a NULL Variant to an Integer or String will result in an error, while doing the same with an Empty Variant will result in a zero value resp. an empty string.

    While this is endeed comfortable, I for one am not too happy with this behaviour. In my case, it made porting applications from DAO to SQLite/RC5 fairly error-prone in this regard. Had to replace lots of IsNull()-Tests with IsEmpty(), and, of course, overlooked many of them in the first run.

    Overall, I find the RC5 wrapper around SQLite very useful. But this is indeed a point that one has to keep in mind permanently.

    Wolfgang

  7. #7
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,997

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Quote Originally Posted by Wolfgang Enzinger View Post
    SQLite itself indeed does use NULL, there is no such thing as Empty in SQLite.

    The behaviour we see here is specific to RC5. I discussed this with Olaf a while ago; it was meant to be a handy feature to avoid runtime errors when you assign a field value to a variable type other than Variant without checking for NULL first.

    Assigning a NULL Variant to an Integer or String will result in an error, while doing the same with an Empty Variant will result in a zero value resp. an empty string.

    While this is endeed comfortable, I for one am not too happy with this behaviour. In my case, it made porting applications from DAO to SQLite/RC5 fairly error-prone in this regard. Had to replace lots of IsNull()-Tests with IsEmpty(), and, of course, overlooked many of them in the first run.

    Overall, I find the RC5 wrapper around SQLite very useful. But this is indeed a point that one has to keep in mind permanently.

    Wolfgang
    I also had big issues of null values causing errors in DAO recordsets in an old program when I was still not aware of the problem.
    I think that it was not a good idea from MS to use null as default values (or not converting them to some default value for each variable type instead of raising an error). But now, backward compatibility is important too IMO.
    Perhaps, if I can suggest, I would say that a configuration for the engine could be useful to set optionally null as the default.
    .DefaultToNull = True
    or .CompatibleMode = True ' it could set also other compatibilities issues as well

    Just my opinion.

  8. #8
    Addicted Member Wolfgang Enzinger's Avatar
    Join Date
    Apr 2014
    Location
    Munich, Germany
    Posts
    160

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Quote Originally Posted by Eduardo- View Post
    I also had big issues of null values causing errors in DAO recordsets in an old program when I was still not aware of the problem.
    Well, this is a lesson I'm sure everybody had to learn. And thus Olaf's idea to make it easier. I'm sure it is easier for new users; old users, however, have to learn another lesson now, which is almost the reverse of lesson #1.

    Quote Originally Posted by Eduardo- View Post
    I think that it was not a good idea from MS to use null as default values (or not converting them to some default value for each variable type instead of raising an error). But now, backward compatibility is important too IMO.
    Perhaps, if I can suggest, I would say that a configuration for the engine could be useful to set optionally null as the default.
    .DefaultToNull = True
    or .CompatibleMode = True ' it could set also other compatibilities issues as well
    Actually this has nothing to do with default values; other than that the "default default value" in every database engine I'm aware of is NULL. But you can define that to be a different value; e.g.

    Code:
    CREATE TABLE t(x INTEGER DEFAULT 0);
    What we're talking about here is automatic type conversion. As said, comfortable in one regard, however, inconsistent in the other. For example, when I execute a query like this:

    Code:
    SELECT * FROM t WHERE x IS NULL;
    ... then it's a little awkward that in the resulting recordset the x column doesn't contain Null but Empty values.

    Wolfgang

  9. #9
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,997

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Quote Originally Posted by Wolfgang Enzinger View Post
    Actually this has nothing to do with default values;
    It does, or if you want, call them unset values.
    I don't know why you say it does not. The type conversion is needed because the default value is set to another type in order to flag the value as unset and out of any valid value of the other type.
    But if you are converting it to empty then you are undermining that rule anyway, because you do that just to be able to convert it to a default value while avoiding errors.
    Then it has much to do with default values.

    Quote Originally Posted by Wolfgang Enzinger View Post
    [workarounds]
    Of course you can handle the issue in many other ways, I was talking about offering the developers an smooth upgrade from the older technology.
    In my opinion backward compatibility is an important feature, but I know that not everybody share this view.

    I wanted to share my opinion. Take it or leave it.

  10. #10
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Quote Originally Posted by Wolfgang Enzinger View Post
    For example, when I execute a query like this:

    Code:
    SELECT * FROM t WHERE x IS NULL;
    This is the problem -- you don't execute WHERE x = NULL, why do you expect x to be null? Why is there IS operator in SQL in first place?

    The whole notion of NULL in SQL is plain wrong. It has never been a good idea and even E.F.Codd (the one who invented RDBMS) was against it. Nevertheless it sneaked in and brought the 3-valued logic to language predicates which is very different logic than the boolean logic we have in VB6 and every other programming language outside RDBMS.

    Olaf attempted to rectify the problem in the "front-end" with a simple hack and while succeeding at first brought many problems to porting legacy RDBMS code to sqlite.

    For the Null plague I myself have to use custom conversion functions based on VariantChangeType API (ones similar to CStr, CLng which do *not* fail/raise error on Null values) all the time and everywhere in my code dealing with recordsets, which is less than optimal to say the least.

    cheers,
    </wqw>

  11. #11
    Addicted Member Wolfgang Enzinger's Avatar
    Join Date
    Apr 2014
    Location
    Munich, Germany
    Posts
    160

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Quote Originally Posted by Eduardo- View Post
    It does, or if you want, call them unset values.
    I'd rather call them undefined values. Because NULL values in database tables don't only appear because the column value wasn't set; rather, it could have been set to NULL intentionally, meaning: the value is unknown, or has no meaning in this context.

    Quote Originally Posted by Eduardo- View Post
    I don't know why you say it does not. The type conversion is needed because the default value is set to another type in order to flag the value as unset and out of any valid value of the other type.
    But if you are converting it to empty then you are undermining that rule anyway, because you do that just to be able to convert it to a default value while avoiding errors.
    Then it has much to do with default values.
    We're probably talking about different things here. What I am talking about are values in the database. You seem to be talking about default values regarding the frontend, in the sense of: "if the value is undefined in the database, what should the frontend display?".

    Quote Originally Posted by Eduardo- View Post
    I wanted to share my opinion. Take it or leave it.
    I'll do both.

    Wolfgang

  12. #12
    Addicted Member Wolfgang Enzinger's Avatar
    Join Date
    Apr 2014
    Location
    Munich, Germany
    Posts
    160

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Quote Originally Posted by wqweto View Post
    This is the problem -- you don't execute WHERE x = NULL, why do you expect x to be null?
    It took me some minutes to get that - but yes, valid point.

    Quote Originally Posted by wqweto View Post
    The whole notion of NULL in SQL is plain wrong. It has never been a good idea and even E.F.Codd (the one who invented RDBMS) was against it. Nevertheless it sneaked in and brought the 3-valued logic to language predicates which is very different logic than the boolean logic we have in VB6 and every other programming language outside RDBMS.
    First: I don't think the concept is that wrong. Granted, it takes a while to get familiar with it, but then ... I actually find it quite useful these days.

    Second: maybe it's not such a bad thing that the concept trickled into languages. I, at least, am oftenly in the need to tell defined from undefined values. I think it's not such a bad thing to have a value at hand which means exactly that - undefined. Instead of using conventions like "0 means undefined" or the like. Zero is different from undefined or unknown, after all.

    Wolfgang

  13. #13
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,997

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Quote Originally Posted by Wolfgang Enzinger View Post
    I'd rather call them undefined values. Because NULL values in database tables don't only appear because the column value wasn't set; rather, it could have been set to NULL intentionally, meaning: the value is unknown, or has no meaning in this context.
    Yes, being strict you are right, but it is semantic (you understood it anyway).

    Quote Originally Posted by Wolfgang Enzinger View Post
    We're probably talking about different things here. What I am talking about are values in the database. You seem to be talking about default values regarding the frontend, in the sense of: "if the value is undefined in the database, what should the frontend display?".
    I thought we were always talking about the front-end, that what is the thread about and the OP.

    I'm not sure if the engine could set the default values in the database anyway, probably not.

    I know whatever you do is always perfect, sorry that I shared an opinion.

  14. #14
    Addicted Member Wolfgang Enzinger's Avatar
    Join Date
    Apr 2014
    Location
    Munich, Germany
    Posts
    160

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Quote Originally Posted by Eduardo- View Post
    I thought we were always talking about the front-end, that what is the thread about and the OP.
    Probably the topic was shifting a bit ...

    Quote Originally Posted by Eduardo- View Post
    I know whatever you do is always perfect, [...]
    Again you are wrong.

    Wolfgang

  15. #15
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,997

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Quote Originally Posted by Wolfgang Enzinger View Post
    Probably the topic was shifting a bit ...
    No the topic, but you were shifting the topic.
    I shared an opinion about the importance of backward compatibility and you didn't answer anything to that but focused in a definition about things that were not the point (or of any importance).

    But it doesn't matter, because I was trying to help for a component that I'll probably never use.

  16. #16
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Quote Originally Posted by Wolfgang Enzinger View Post
    Second: maybe it's not such a bad thing that the concept trickled into languages. I, at least, am oftenly in the need to tell defined from undefined values. I think it's not such a bad thing to have a value at hand which means exactly that - undefined. Instead of using conventions like "0 means undefined" or the like. Zero is different from undefined or unknown, after all.
    With the risk of going way off-topic most languages are backing off on Null/Nothing nowadays.

    Most prefered way of dealing with errors and unknown values is by using sum types so called Maybe monad which promises to get us rid of both Object required and Invalid use of null errors not without the help of the compilers.

    cheers,
    </wqw>

  17. #17
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Quote Originally Posted by Eduardo- View Post
    I thought we were always talking about the front-end, that what is the thread about and the OP.
    Yep, when working with DBs, one should (IMO) draw a clear line between these two things:
    1) working with "DB-Null-Values" in the context of the SQL-dialect of the given DB-engine (here SQLites SQL-Dialect, which is similar to PostgreSQL regarding Null-Values)
    2) working with these "unknown DB-Values" in the context of the FrontEnd (e.g. when you retrieve them from ResultSet-Object-representations)

    And as for 2), the behaviour of the RC5-COM-wrapper for SQLite in regards to DBNull-Value-representation was deliberately choosen
    (the choice of Variant-Empty-represenations in instead of Variant-Null-representations was not "a mistake or oversight" on my part).

    First of all, testing for a DB-Null-Value is still possible at FrontEnd-Level via the already mentioned:
    If IsEmpty(Rs.Fields("SomeNullableField").Value)

    Also the backtransport of a DB-Null-Value into a Nullable-DBTable-Field will work:
    Rs.Fields("SomeNullableField").Value = Empty
    or alternatively (in write-direction) also via:
    Rs.Fields("SomeNullableField").Value = Null
    In both cases, a follow-up via Rs.UpdateBatch will write the correct "DB-Null-Value" into the appropriate DBTable-Field.

    A DB-Null-Value, represented as a Variant-Empty-Value simply shows "better, more logical behaviour" (in terms of "conversions and visualizing") in a Frontend-context.
    ? CBool(Empty) ... will give a proper False
    ? CStr(Empty) ... will give a proper EmptyString (and not a "0")
    ? CLng(Empty), CDbl(Empty)... will give a proper 0-Integer-Value or 0-Dbl-Value respectively

    In other words - in normal (GUI)Frontend-contexts (no matter if there's a DB involved or not),
    I've never resorted to explicitely set (or use) any Variant-Null-Values (to mark an "unknown" or "not yet set" Value) -
    instead a Variant-Empty-Value was always entirely sufficient to "mark" such "not yet set" Values
    (with all the just mentioned above "conversion-advantages", compared to a Variant-Null-Value).

    In my opinion, MS made a mistake, not to "map" DB-Null-Values to Variant-Empty in their ADO-Rs-frontend representations of selected "DB-Sets".

    Quote Originally Posted by Eduardo- View Post
    I'm not sure if the engine could set the default values in the database anyway, probably not.
    Not really sure, what you really mean there, with "set the default values in the database".

    As already mentioned further above, the backtransport (of an explicitely set Variant-Empty Rs.Field-Value) into a nullable DBTable-Field will work as it should.

    In case you meant DBTable-Field-default-values, these are ususally set in the Field-definition of an SQL-DDL "Create Table ..."-statement.

    E.g. the following example shows, how SQLite (and then later on also the Frontend-Rs-representation) -
    will handle such explicitely defined "Default-Values" on DBTable-Fields:

    Code:
    Option Explicit
    
    Private Cnn As cConnection
    
    Private Sub Form_Load()
      Set Cnn = New_c.Connection(, DBCreateInMemory)
      
      With Cnn.NewFieldDefs
        .Add "ID Integer Primary Key"
        .Add "Txt Text default 'ABC'"
        .Add "Dbl Double default 1.23"
        .Add "Bln Boolean default True"
        
        Cnn.CreateTable "T" 'creates a new table "T" with an ID-PK-AutoIncrement Field,
                            'followed by 3 Text, Double and Boolean-Fields, all with default-values
      End With
    End Sub
     
    Private Sub Form_Click()
      Dim Rs As cRecordset, Fld As cField
      Set Rs = Cnn.OpenRecordset("Select * From T")
      Debug.Print Rs("Txt").DefaultValue, Rs("Dbl").DefaultValue, Rs("Bln").DefaultValue
     
      Rs.AddNew 'prepare for an Insert (adding a new Record)... Field Defaults are automatically applied
      Debug.Print vbLf; "Rs-Fld-content before UpdateBatch:"
      For Each Fld In Rs.Fields: Debug.Print Fld.Name, Fld.Value, TypeName(Fld.Value): Next
      
      Rs.UpdateBatch
      Debug.Print vbLf; "Fld-content after performing UpdateBatch:"
      For Each Fld In Rs.Fields: Debug.Print Fld.Name, Fld.Value, TypeName(Fld.Value): Next
    End Sub
    If you click the above defined Form, you will get the following output:
    (showing, that DBTable-Field-default-values are transported (already at "Select-time") "into the Rs-Frontend-representation":
    - sitting there behind the Rs.Fields("SomeField").DefaultValue-Prop
    - and then these default-values are "taken over" into the new records "normal Values" already at Rs.AddNew time
    (I've implemented the latter behaviour, to reflect the current contents of a newly added record properly in potentially "bound Controls",
    even in disconnected DataBinding-scenarios).

    Code:
    ABC           1.23          True
    
    Rs-Fld-content before UpdateBatch:
    ID                          Empty
    Txt           ABC           String
    Dbl            1,23         Double
    Bln           Wahr          Boolean
    
    Fld-content after performing UpdateBatch:
    ID             1            Decimal
    Txt           ABC           String
    Dbl            1,23         Double
    Bln           Wahr          Boolean
    Olaf

  18. #18
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,997

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Ok, what I wanted to say is on post #7.
    So, good luck.

  19. #19
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Quote Originally Posted by Eduardo- View Post
    Ok, what I wanted to say is on post #7.
    So, good luck.
    Ok, so you meant (with "default-value"), how the COM-wrapper "should behave by default" (when it comes to DBNull-Value-representation in derived Rs).

    That's not a bad idea, and I will probably introduce such a new Property into the Connection-Object of the new RC6
    (to make the behaviour consistent for all Rs-Objects, retrieved from that Cnn-object).

    This new Boolean-type Property then in all likelyhood named:
    MapDbNullValuesToVariantEmpty (defaulting to True, to not break existing code).

    Olaf

  20. #20
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,997

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Risking to trigger another useless discussion, I suggest a more friendly and shorter name like MapNullToEmpty.

  21. #21
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Quote Originally Posted by Eduardo- View Post
    Risking to trigger another useless discussion, I suggest a more friendly and shorter name like MapNullToEmpty.
    Ok, I guess a compromise is in order...

    Have just integrated this feature into the upcoming RC6-release, as a new cConnection-Property (Boolean) -
    finally named to: cConnection.MapDbNullToEmpty (defaulting to True)

    Olaf

  22. #22
    Addicted Member Wolfgang Enzinger's Avatar
    Join Date
    Apr 2014
    Location
    Munich, Germany
    Posts
    160

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Quote Originally Posted by Schmidt View Post
    Ok, I guess a compromise is in order...

    Have just integrated this feature into the upcoming RC6-release, as a new cConnection-Property (Boolean) -
    finally named to: cConnection.MapDbNullToEmpty (defaulting to True)
    Thanks!

    (though in my case it's actually too late, everything is ported by now, and all IsNulls are switched to IsEmpty; but who knows, maybe another forgotten DAO project will pop up out of the dark eventually ... )

    Wolfgang

  23. #23
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Just out of curiosity, how do y'all handle cases where there's a difference between a null number and a 0?
    Example: Grade books, a null means no grade has been recorded - it's an unknown. A 0 on the other hand is a grade. One can't assume that null should be made 0 nor the other way around.
    Not looking to pick a fight, jsut wondering how those that advocate for always having default values deal with this kind of thing. I come from a world where nulls cannot be defaulted because they have a distinct purpose for being in the data. It's a pain sometimes, but it's all part of the domain I deal with.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  24. #24
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Nullable columns are implemented by most RDBMs with a bit which indicates if the column is null or it has actual value.

    If all possible non-null values of a column have business meaning then a separate bit column like IsXxxSpecified acts like "manual" null.

    In your particular case probably -1 will be good out-of-range value that can be used instead of null.

    I use reimplementations of Nz and (its reverse) Zn functions from Access origin all the time.

    cheers,
    </wqw>

  25. #25
    PowerPoster
    Join Date
    Feb 2017
    Posts
    4,997

    Re: [RESOLVED] Sqlite how to check if a field is empty?

    Quote Originally Posted by techgnome View Post
    Just out of curiosity, how do y'all handle cases where there's a difference between a null number and a 0?
    IsNull Function (or IsEmpty is case of RC5).

    Quote Originally Posted by techgnome View Post
    those that advocate for always having default values
    I'm not sure that someone here is really saying that.

    The point was to avoid the default behavior to be an error, because in most cases you don't care if some of the values are not set. When you do, use the Function.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width