I know that a boolean in LO Basic is not allowed to be null.
I have oQueryResult which is the result of a query, where oQueryResult(1) corresponds to a boolean that in the database is allowed to be null. I know that dumping the value of oQueryResult(1) into a boolean macro variable can be problematic. But what if I’m simply performing comparisons directly on the result, as in
If oQueryResult(1)<>0 and oQueryResult(1)<>1 Then 'I know that field in the database is null
We do not know which database you are using. Is it embedded HSQL? Anyhow, this is not a spreadsheet. You can’t mix types in the same column. There can not be a string in a column of booleans.
Why is that boolean field nullable? Is it wanted to be like that? If not, fix it.
P.S. Your snippet is not valid Basic code.
@Villeroy Thanks for your feedback!
It’s MySQL. Is a nullable boolean in a database not acceptable?
What is it about my snippet that makes it not valid?
And what gave you the impression I was mixing types in a column?
Depends on what you need. For instance, you may want to store triple-state options like this:
[ ] Male
[ ] Female
[ ] Trans
or
[ ] Certified
[ ] Not Certified
[ ] Who cares?
Yes, in this case, it’s especially helpful in the way we have to keep track of things in the database to have tri-state options (and specifically, to have null as the default. That’s a flag that no decision has been made.)
Misinterpretation of your invalid Basic as SQL. If this then 1 elseif that then 0 else “Must be Null” or something like that.
Again, what makes my basic invalid?
OK, it is valid but incomplete. I read the comment as single-quoted string. What was your question again?
Is it problematic to use the tri-state boolean results in comparisons like I did, since Basic booleans are not allowed to be null?
You’re right, I didn’t include the entire if statement. I didn’t think it would be necessary to convey my question. I guess the way I did it was too distracting. How much context is required to make a snippet of code not incomplete. As a wiz, you’re probably used to thinking in complete, compilable or executable chunks. I would love to go back and edit the question so that it is presented that way, but I don’t see where I can edit it.
Check the symbols below your question. There should be a pencil. If you don’t see this, but 3 dots click on them to get more symbols shown.
Click on the pencil usually leads to editing your question or comment.
Does it work this way? Certainly there is some method to test if a value is Null. What do you want to do if the value is Null?
That is what I’m asking. In a language wher bool’s can be null, I would expect my method to be an effective way to check for it. Since I’m not dumping the boolean value into its own variable with a type, I’m not really sure what methods/properties are available to me. I’ll have to run a couple tests of my own.
Based on context, if it’s null, I want to prompt the user for whether or not to mark it true or whether or not to mark it false. I want to give a recommendation, but let the user make the final decision, and then give a reason, which I will then append to the end of some notes field (which is a string).
Your explained process is limited. If you only want records with NULL boolean fields, then simply use a Where X IS NULL in your query. My tests show your If statement will not work.
If Null needs to be handled, then Null might be unwanted. Replace all Null values one way or the other, then make the column not nullable and the database will refuse to save incomplete records where this boolean is missing.
SQL is availlable as in:
SELECT "Table".* ,
CASEWHEN "B"=True THEN 'this' WHEN "B"=False then 'that' ELSE 'NULL' END AS "My Text"
FROM "Table"
returns everything from “Table” plus an additional text field with ‘this’ or ‘that’ or ‘NULL’ depending on the boolean “B” being True, False or Null.
The same language can set all Nulls to True or False…
The same langauge can modify column types, indices, defaults and restrictions.
Since 50 years SQL is the language. LibreOffice Base translates all your actions to SQL commands when you design, query or edit your database.
You can not operate a database by means of Basic macros.
Definitely not. The reality is that a decision on the boolean often cannot be made at the time a record is created, but during a later process (that cannot take place without the record already existing), that decision needs to be made.
If I avoid dumping the boolean into a variable and try doing a comparison in an if statement directly on oQueryResult(1) as I did in my original question, I get error messages of “Incorrect property value” or “Object variable not set”. I do not think Basic has a way of doing this. I think as a workaround, I’ll have use the difference in a count SQL statement with a where boolean is null clause.
Also, I just realized I was trying to use oQueryResult incorrectly. It’s not correct to just use oQueryResult(1). I need to use oQueryResult.get***, like oQueryResult.getInt(1). Unfortunately, if I get an int from a null boolean, what I get is 0, just like if the boolean is false. So, I still need to use my SQL count workaround.
oResult.getstring(your column index) will return 1 of 3 values.
if boolean is true “True”, if false “False”, if null “” (empty string)
Edit:
if boolean is true “true”, if false “false”, if null “” (empty string)
Thanks! That really simplifies things for me. I was hung up on .getInt().
I don’t see a difference from your original statement.
Now I see it! The capitalization is different in the non-empty strings.