Re: TRUE/FALSE SQL functionality

[mark boylan]

I don't know if this helps, but I created a boolean_type table...

create table boolean_type (
 boolean_type_id char(1) not null primary key,
 boolean_type_name varchar(5) not null
);

insert into boolean_type values ('Y', 'true');
insert into boolean_type values ('N', 'false');

and then I use it for the constraint...

create table my_table (
 my_id int not null primary key,
 my_string varchar(100),
 create_user int,
 deleted char(1) not null references boolean_type(boolean_type_id)
);


Hope that helps,

- mark

Hi Joel,

There is a lot of interest in enabling the BOOLEAN datatype. Unfortunately, our first attempt at this foundered on network protocol and release-compatibility issues. At this time, we don't understand how to enable this datatype without issuing a major release of Derby (release 11).

Regards,
-Rick

Joel Garringer wrote:

We would like to migrate to Derby, but we have encountered some difficulty in regard to support for Boolean operations. It isn’t critical to us that Derby store Booleans, but there are some operations in a large number of our SQL statements that appear to be unsupported on Derby.

For instance:

Imagine a table with the following columns:

myID as Integer,

myString as String,

createUser as Integer,

deleted as Boolean

We would like to be able to:

SELECT * FROM myTable WHERE NOT deleted

UPDATE myTable SET deleted=True WHERE createUser=1

DELETE FROM myTable WHERE deleted

Is there any data-type in Derby that supports this? I know that Derby doesn’t currently support a Boolean data type for columns, but some databases that do not have a Boolean column type do support the operations I have listed here so I thought I should check. We really want to migrate to Derby, and it isn’t an issue to us how the underlying values are stored as long as we can perform these types of operations. I am pretty sure I know the answer, but I wanted to make sure before we gave up.

We saw that there was some discussion of support for Booleans being backed out of version 10.2. Are there any plans to reintroduce it in the future?

Thanks.

Joel

www.enduropls.com <http://www.enduropls.com/>