BOOLEAN Filter problem with split HSQLDB 2.3.2

Creating tables and queries

BOOLEAN Filter problem with split HSQLDB 2.3.2

Postby papijo » Mon Jan 19, 2015 1:28 pm

Hi all "split HSQLDB" users!

I am having a problem with filtering BOOLEAN fields in my form. I guess the problem comes from the change of "nature" of the BOOLEAN type between HSQLDB version 1.8 (embedded in OpenOffice) and version 2.3.2 which I am using in my split database setting.

1.- The HSQLDB documentation here says:
The BOOLEAN type conforms to the SQL Standard and represents the values TRUE, FALSE and UNKNOWN. This type of column can be initialised with Java boolean values, or with NULL for the UNKNOWN value.
The three-value logic is sometimes misunderstood. For example, x IN (1, 2, NULL) does not return true if x is NULL.
In previous versions of HyperSQL, BIT was simply an alias for BOOLEAN. In version 2, BIT is a single-bit bit map.


2.- I have a TABLE1 table with ID [INTEGER], NAME [VARCHAR] and REGISTERED [BOOLEAN] fields. In my form, if I use the Standard Filter on that BOOLEAN field, my records are correctly filtered if I enter TRUE or FALSE in the CONDITION = box.

3.- But, if I enter 0 or 1, my form simply freezes and becomes un-usable. If I have opened my form in Edit mode, I can then switch back to Design mode and go to Form properties > Data > Filter which contains e.g. the following formula: ( "TABLE1"."REGISTERED" = '1' ). Then if I switch the Design mode OFF, I get this error message:
"The data content could not be loaded. data exception: invalid character value for cast".

4.- Now, if before switching the Design mode OFF I correct the filter formula by removing the single quotes surrounding the 1 (or 0) value, like this: ( "TABLE1"."REGISTERED" = 1 ) and then switch the Design mode OFF, the filter is correctly applied to my form.

5.- Diagnostic. When I enter a 0 or 1 value in the Standard filter CONDITION = box, OpenOffice automatically adds single quotes, and this results in freezing my form.

6.- Of course I might avoid this annoying freezing problem by avoiding using 0 and 1 but using FALSE or TRUE instead. But since I intend to make my application available to the general public, I would rather that OpenOffice + HSQLDB v2 did not behave in such a buggy way. :roll:

7.- Re-reading the HSQLDB documentation here I thought I might use the BIT type instead of the BOOLEAN type in my tables. This Bit (fix) [BIT] type is indeed available in the list of field types when I create a table in my HSQLDB 2.3.2 based installation. It is available, but ... it does not work in OpenOffice! Whenever I try to tick the check-box of a [BIT] field and try to save the record, I get this error message:
"Error inserting the new record / value cannot be converted to target type" :x

8.- I appreciate that the "responsibility" for the current problem is shared between OpenOffice and HSQLDB. Can anyone suggest a solution? Should this "bug" be reported to the OpenOffice/LibreOffice developers?

EDIT.-
I should add that if I try to filter my form using the Form Navigation bar's "Form-based filters" button, the 0 or 1 values are automatically added, and not surrounded by single quotes, so that the filter works fine. :roll:
LO: LibreOffice 6.4.0.3 (x64) on Windows 10 64bits. Split database HSQL 2.3.4.
papijo
 
Posts: 88
Joined: Sat Nov 08, 2014 5:46 pm
Location: Brittany, West of France

Re: BOOLEAN Filter problem with split HSQLDB 2.3.2

Postby MTP » Mon Jan 19, 2015 11:27 pm

Have you tried adding a BIT column through Tools->SQL window instead of the GUI? (I don't know anything about 2.3.2, but agree with your logic that trying a BIT column is worthwhile.)
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: BOOLEAN Filter problem with split HSQLDB 2.3.2

Postby papijo » Mon Jan 19, 2015 11:59 pm

@MTP you misunderstood me. I can add a BIT column using the OpenOffice interface. But I can't save a record with a BIT type field in it!
LO: LibreOffice 6.4.0.3 (x64) on Windows 10 64bits. Split database HSQL 2.3.4.
papijo
 
Posts: 88
Joined: Sat Nov 08, 2014 5:46 pm
Location: Brittany, West of France

Re: BOOLEAN Filter problem with split HSQLDB 2.3.2

Postby Villeroy » Tue Jan 20, 2015 12:12 am

When I turn on the form based filter, the boolean fields are shown as triple state check boxes (null, true, false).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30159
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: BOOLEAN Filter problem with split HSQLDB 2.3.2

Postby papijo » Tue Jan 20, 2015 12:58 am

@Villeroy, I do not see this. Could you please attach a screenshot?

Anyway, I find it hard to understand that tri-state logic for BOOLEAN fields. I have set all the BOOLEAN type fields in my tables as Entry required Yes and Default value No and on my form I have set Tri-state No.
I don't think my problem is directly related to this tri-state situation.
LO: LibreOffice 6.4.0.3 (x64) on Windows 10 64bits. Split database HSQL 2.3.4.
papijo
 
Posts: 88
Joined: Sat Nov 08, 2014 5:46 pm
Location: Brittany, West of France

Re: BOOLEAN Filter problem with split HSQLDB 2.3.2

Postby Villeroy » Tue Jan 20, 2015 2:26 am

form_based_filter.png
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 30159
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: BOOLEAN Filter problem with split HSQLDB 2.3.2

Postby papijo » Tue Jan 20, 2015 10:50 am

@Villeroy, I do not understand your screenshot. When using the Form-based filters on a BOOLEAN tri-state column, I maintain that it is only possible to filter by TRUE (1) or FALSE (0). It is not possible to filter the NULL/UNKNOWN state. See attached screenshot.
Attachments
2015-01-20_09-34-19.jpg
LO: LibreOffice 6.4.0.3 (x64) on Windows 10 64bits. Split database HSQL 2.3.4.
papijo
 
Posts: 88
Joined: Sat Nov 08, 2014 5:46 pm
Location: Brittany, West of France

Re: BOOLEAN Filter problem with split HSQLDB 2.3.2

Postby papijo » Tue Jan 20, 2015 11:58 am

EDIT.- Actually, it is possible to edit the "Filter for" filter for a BOOLEAN tri-state column. Using the usual method of clicking on the check-box in the form you can only get 0 or 1 states (as illustrated in my previous message). In order to set the filter value to something different, you must right-click the filter, then you can enter "NULL". As seen in (1) on the screenshot. But this still does not work as expected, it does not filter the "NULL/UNKNOWN/NOT SET" fields when you apply the filter. If you go back to the Form-based filters button, you notice that OpenOffice has automatically changed the "NULL" value you entered in the filter into an "EMPTY" value (2). This is getting more and more weird. :crazy:
Attachments
2015-01-20_10-47-58.jpg
LO: LibreOffice 6.4.0.3 (x64) on Windows 10 64bits. Split database HSQL 2.3.4.
papijo
 
Posts: 88
Joined: Sat Nov 08, 2014 5:46 pm
Location: Brittany, West of France


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests