Re: Re: [users] BOOLEAN field in Database

James, Alex, et al

I took the liberty of downloading the database and ran a little test.

What I did was first do a SCRIPT command on the database to see what 
HSQLDB thought it had for table definitions. Then I created the 
following text file and applied it to the databae via the SQL window in 
Base.

CREATE CACHED TABLE "Customers2"(
"Key" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL 
PRIMARY KEY,
"Number" VARCHAR(8) NOT NULL,
"Unit" VARCHAR_IGNORECASE(5),
"Street" VARCHAR_IGNORECASE(15),
"Prefix" VARCHAR_IGNORECASE(1),
"Suffix" VARCHAR_IGNORECASE(4),
"DeliverySegment" VARCHAR(20),
"SwitchSide" BOOLEAN DEFAULT FALSE NOT NULL ,
"DeliverySchedule" VARCHAR(3),
"Active" BOOLEAN DEFAULT TRUE NOT NULL,
"Ordinal" TINYINT,
"Last" BOOLEAN DEFAULT FALSE,
"Corner" BOOLEAN DEFAULT FALSE,
"Bag" BOOLEAN DEFAULT FALSE NOT NULL,
"Porch" BOOLEAN DEFAULT FALSE NOT NULL,
"Handicapped" BOOLEAN DEFAULT FALSE NOT NULL,
"SatSunonMon" BOOLEAN DEFAULT FALSE NOT NULL,
"Remark" VARCHAR(30),
"Sequence" VARCHAR(5),
"Order" SMALLINT NOT NULL)

Then creaed a second script file form the database to see if the two 
tables Customers and Customers2 would indeed be different. Here are the 
results

CREATE CACHED TABLE "Customers"("Key" INTEGER GENERATED BY DEFAULT AS 
IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Number" VARCHAR(8) NOT 
NULL,"Unit" VARCHAR_IGNORECASE(5),"Street" 
VARCHAR_IGNORECASE(15),"Prefix" VARCHAR_IGNORECASE(1),"Suffix" 
VARCHAR_IGNORECASE(4),"DeliverySegment" VARCHAR(20),"SwitchSide" BOOLEAN 
NOT NULL,"DeliverySchedule" VARCHAR(3),"Active" BOOLEAN NOT 
NULL,"Ordinal" TINYINT,"Last" BOOLEAN,"Corner" BOOLEAN,"Bag" BOOLEAN NOT 
NULL,"Porch" BOOLEAN NOT NULL,"Handicapped" BOOLEAN NOT 
NULL,"SatSunonMon" BOOLEAN NOT NULL,"Remark" VARCHAR(30),"Sequence" 
VARCHAR(5),"Order" SMALLINT NOT NULL)


CREATE CACHED TABLE "Customers2"("Key" INTEGER GENERATED BY DEFAULT AS 
IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"Number" VARCHAR(8) NOT 
NULL,"Unit" VARCHAR_IGNORECASE(5),"Street" 
VARCHAR_IGNORECASE(15),"Prefix" VARCHAR_IGNORECASE(1),"Suffix" 
VARCHAR_IGNORECASE(4),"DeliverySegment" VARCHAR(20),"SwitchSide" BOOLEAN 
DEFAULT false NOT NULL,"DeliverySchedule" VARCHAR(3),"Active" BOOLEAN 
DEFAULT true NOT NULL,"Ordinal" TINYINT,"Last" BOOLEAN DEFAULT 
false,"Corner" BOOLEAN DEFAULT false,"Bag" BOOLEAN DEFAULT false NOT 
NULL,"Porch" BOOLEAN DEFAULT false NOT NULL,"Handicapped" BOOLEAN 
DEFAULT false NOT NULL,"SatSunonMon" BOOLEAN DEFAULT false NOT 
NULL,"Remark" VARCHAR(30),"Sequence" VARCHAR(5),"Order" SMALLINT NOT NULL

Then I created a NEW database and attempted to set the default values 
for the boolean fields via the GUI. I am having the same problem with 
the defaults not holding. In fact when I saved a table definition, then 
opened it for editing again I could not save back, instead I received 
error messages as described in Issue #52919 
<http://qa.openoffice.org/issues/show_bug.cgi?id=52919>. The behavior is 
identical to what is described in the closed Issue #48851. 
<http://qa.openoffice.org/issues/show_bug.cgi?id=48851>

All my tests where done on WinXP SP2, Java JRE 1.5.0_04 and OO.o Build 
1.9.129

Looks to me the problem is in the GUI of OO.o.

James, looks like the best way to get your tables right for now is via 
good old ASCII issued to the database engine, shouldn't look to foriegn 
to an old mainframer... ;-)

Drew Jensen
James E. Lang wrote:

>Thank you, Alex, for your response. First off, I'm not sure where to go to 
>subscribe to the dba-users list. Point me in the right direction and I will 
>subscribe.
>
>FWIW, this is my first db application. I deliver newspapers and am trying to 
>create a database for my delivery route. The agent for whom I work uses a 
>proprietary database application and won't alter the delivery sequence to suit 
>my driving preferences.
>
>I'm a retired mainframe systems programmer so I'm not afraid to tackle 
>something new like this. I'm willing to work around problems as the OOo 
>database functionality and my own database both experience birthing pains.
>
>If you think it will help track down a problem, you may download this copy of 
>my database definition as it exists right now.
>
>    http://www.ktb.net/~jelly/ooo_db/delroute.odb
>
>Alex Thurgood wrote at 21:34 on 13 Sep 2005:
>
>  
>
>>Le mardi 13 septembre 2005 à 13:14 -0400, G. Roderick Singleton a
>>écrit :
>>
>>Hi,
>>
>>This sounds exactly like Issue 52919
>>
>>http://qa.openoffice.org/issues/show_bug.cgi?id=52919
>>    
>>
>
>It may well be the same.
>
>  
>
>>The previous poster didn't provide any sample dbs for us to test the
>>problem and confirm it, so it would be a good idea if you could add your
>>comments and an example. 
>>    
>>
>
>Feel free to download the file I mentioned above.
>
>The primary problem I have identified is that BOOLEAN default values don't 
>appear to be honored. In particular, if I set the default to "Yes" it reverts 
>to "No" once I leave the space for setting it. I have not attempted to assign 
>default values to any other fields.
>
>Other problems I have noted are the inability to insert a field where I want it 
>within an existing table and the loss of field descriptions under conditions 
>that I have not yet identified. It may be related to having used cut and paste 
>to rearrange fields. At present all fields except those generated by default as 
>identity have descriptive text.
>
>I hope this helps the development team.
>
>  
>
>>Additionally, while I might be criticised for what I'm about to write
>>here, I personally wouldn't start developing a full blown db app using
>>the embedded HSQLDB in OOo just yet, it still has too many bugs and
>>limitations for my liking. This is not just the HSQLDB integration, but
>>the whole db module, is new, young, and well, needs to mature somewhat
>>before I throw my weight behind it. The maturity may come about with the
>>2.01 release, who knows, but until then I shall keep testing and trying.
>>    
>>
>
>Thank you for your candor. It is alright with me that this is still a work in 
>progress. This is a learning experience for me with the side effect of creating 
>a tool that I can use. So far all I have created are the table definitions and 
>they are still subject to relatively minor change. I have not yet coded any 
>Queries, Forms, or Reports though I have mentally worked out how I want them to 
>function. I'm sure I'll run into more problems but that is part of the learning 
>process. I *am* bouncing the table design off another head to try to ensure 
>that there will be no major rewrite of this part of things once I get deep into 
>the application development.
>
>  
>