[Solved] Can a default be assigned to a Boolean via SQL

[anotherAlan]

Edit: Note: this is marked as solved, but there is a bug (http://www.openoffice.org/issues/show_bug.cgi?id=114304) that currently prevents SQL from creating Booleans with proper default values. Until that is fixed, you can still use SQL to create the filed directly by you must edit them in the table GUI to set defaults. An important note to this is that any value that is originally set to "No" in the GUI needs to be flipped to "Yes" and back to "No" if that's what you want. Otherwise an error occurs with NULL values. More details in this comment: http://user.services.openoffice.org/en/ ... 16#p155152

Using OpenOffice 3.2.1 (Build: 9502) on Mac OSX 10.6.4, I'm trying to create a Boolean field in a table that defaults to "TRUE" via SQL, but it's not working.

Based off of this reference:
http://hsqldb.org/doc/guide/ch09.html#c ... le-section

something like the following should work:

Code: Select all

CREATE TABLE "testTable" ("testBool" BOOLEAN DEFAULT TRUE NOT NULL);

That code creates the table and boolean field just fine, but when I open up the table in edit mode the Default is set to "No" instead of the expected "Yes". I've tried several permutations including the following:

Code: Select all

CREATE TABLE "testTable" (

"pKey" INTEGER NOT NULL IDENTITY PRIMARY KEY,

"test01" BOOLEAN DEFAULT TRUE NOT NULL,
"test02" BOOLEAN DEFAULT True NOT NULL,
"test03" BOOLEAN DEFAULT true NOT NULL,
"test04" BOOLEAN DEFAULT 1 NOT NULL,

"test05" BOOLEAN DEFAULT FALSE NOT NULL,
"test06" BOOLEAN DEFAULT False NOT NULL,
"test07" BOOLEAN DEFAULT false NOT NULL,
"test08" BOOLEAN DEFAULT 0 NOT NULL,

"test09" BOOLEAN DEFAULT TRUE,
"test10" BOOLEAN DEFAULT True,
"test11" BOOLEAN DEFAULT true,
"test12" BOOLEAN DEFAULT 1,

"test13" BOOLEAN DEFAULT FALSE,
"test14" BOOLEAN DEFAULT False,
"test15" BOOLEAN DEFAULT false,
"test16" BOOLEAN DEFAULT 0

);

In all the above cases when "NOT NULL" is specified, upon opening the table in Edit mode, the default value for the respective fields is "No". In any case where I did not specify "NOT NULL" the default value is "none".

(NOTE: In order to see the newly create tables you have to you to select View->Refresh Table from the menu bar. If you don't, you won't see the newly created tables after the SQL is run.)

Am I missing something, or is this a bug?

[RPG]

Helo

There is a check box on the special settings tabpage

menu --> Edit --> Advanced settings.

check /uncheck : Form data input checks for required fields

I'm not sure if this is working for you.

Romke

[anotherAlan]

I tried flipping to both checked and unchecked and it didn't have any effect. (One quick note, on my machine, in the menu it's under Edit->Database->Advanced Settings.) It seems like since I'm entering this SQL directly from the Tools->SQL menu it should go without something else getting in the way.

So, unfortunately, still not working.

[RPG]

Hello

Maybe you have also to reload the tables.

select first tables
then
menu ---> view --> refresh tables

I post this and will look if I can find back a post where I have read it.

Romke

[anotherAlan]

Afraid that refreshing the tables doesn't get it either. In fact, you actually have to do that after you run the SQL in order to see the tables at all. (I've edited the original post to put a note in there about that since it can be a little confusing if you aren't aware of that fact.)

[RPG]

myself wrote:db-menu:Edit>Database>AdvancedSettings... "Form input checks for required fields". Otherwise the form will reject to store the record with the missing date-value. With this option unset, the underlying HSQLDB takes care of the integrity, adding the field's default value.

http://user.services.openoffice.org/en/ ... 39&t=32890

I was thinking that it also can work for a logical field and not only for a date

Form data input checks for required fields
When you enter a new record or update an existing record in a form, and you leave a field empty which is bound to a database column which requires input, then you will see a message complaining about the empty field.
If this control box is not enabled, then the forms in the current database will not be checked for required fields.
The control box is available for all data source types which support write access to their data. The control box does not exist for spreadsheets, text, csv, and the various read-only address books.

I have no more ideas

Romke

[anotherAlan]

I appreciate the effort. I'm now pretty sure this is a bug. If anyone wants to follow along, I've filed it at:
http://www.openoffice.org/issues/show_bug.cgi?id=114304

[Villeroy]

Allan,
Your table

Code: Select all

CREATE TABLE "testTable" ("testBool" BOOLEAN DEFAULT TRUE NOT NULL);

does not allow testing the field. You need a second field and a primary key to test the boolean defaults.

Code: Select all

CREATE TABLE "testTable" (ID INT PRIMARY KEY, "testBool" BOOLEAN DEFAULT TRUE NOT NULL);

This table lets me enter ID 1, go to the next new record and I see that the entered record gets a True in the boolean field.
Then I enter ID 2, go to the next record and get the nasty error about Null in not nullable column.
So this seems to work only for the first record in a grid.

Edit: and the default value is False in the graphical Base interface

Then I let the form wizzard build a form based on this simple table and the defaults for the boolean field are:
Data>Input required: Yes
Triple State: No
Default value: False (unchecked, no)
Entering more integers, I get False as default value.

With Default value: True (checked, yes) I get True as default value which may be exactly what you want.
With Triple State: Yes plus Default value "undefined" (null) I get a form error (with blue exclamation mark) about the missing value. The form checks if the input is complete. The underlying database field requires "Not Null". This configuration can be useful to let the user make an active decistion rather than leaving the default (click actively either one of True or False or the record won't be accepted).

Now I close the form, set the database option to not check required fields and the reloaded form won't let me go to the next record. I think this is a bug indeed. At least it is not conformant with the behaviour of date fields where I used this set up quite often.

Then I set Data>Input required to "No" and the form lets me jump to the next record. It does not care if the input is complete or not. The database puts it's default value into every new record (True/False, I tested both).

So far I see a rather complex set of options on document level ("form checks required fields"), on table level ([Not] Null, Default Value) and form level (triple state, default value, input required). But I'm not sure if there is something buggy. The options seem to do what they are supposed to do.

The obvious bug I started with (table grid works only once) should not be considered as too serious since the grid is not meant to be used by the database user. Of course it deserves to be fixed anyway. Thank you for filing bug 114304.

[anotherAlan]

Villeroy,
checkout my second example as well. I put a primary key in there just to see if that would make any difference and also tried several different ways of setting the TRUE/FALSE default. None of which worked.

You hit something else which I hadn't noticed about the nulls, but further points to a bug. If you create a field with NOT NULL, in the GUI for the table it'll appear as "No", but it doesn't appear that it's really acting that way. If you create the table from the second example and then add a new row by directly editing the table, the first four values look like they are "FALSE" since they aren't checked (which is inaccurate since they should be TRUE/checked). Fields 5-8 all show FALSE/unchecked which looks like what we want. However, in all cases, the table actually seems to think that the data behind each is NULL. Hence the error. If you toggle all 8 fields at least one time (and then leave them in whatever state you want) the error goes away and the record is added to the database.

Also, after the table is originally created, if you go into the GUI editor for the table and explicitly set the first four values to "Yes"/TRUE, they work fine. For fields 5-8 when you first look at the GUI, the default appears to be set to "No", but it doesn't work out that way. I had to actually switch it to "Yes" and then back to "No" in order for the default value to be set properly. Once those edits were made in the GUI, the adjusted fields showed up with the proper defaults as expected for each new record and there was never an issue with NULL values regardless of if the values weren't changes before actually adding the record.

So, for anyone tracking this, there is a bug that's open, but you can get a temporary fix by editing the table itself in the GUI table editor and setting default values there. Just make sure that if you have a value as "No" that you switch it to "Yes" and then back to "No" in the GUI to make sure that the default is actually set properly so an invisible NULL doesn't cause you problems.

(NOTE: I'm going to make this thread as solved even though an issue exists. The post editing in the GUI will get you there until the actual bug (http://www.openoffice.org/issues/show_bug.cgi?id=114304) is fixed.)

[anotherAlan]

Villeroy,
I think the NULL errors that you were running into were a related, but separate bug. I did a little more testing and opened another bug:
http://www.openoffice.org/issues/show_bug.cgi?id=114309

They may both have the same underling cause, but want to make sure they both make it into the mix.