Re: [dba-users] Re: [users] BOOLEAN field in Database

[Andrew Jensen]

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. The behavior is identical to what is described in the closed Issue #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.