TRUE/FALSE SQL functionality

[Joel Garringer <Jo...@enduropls.com>]

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

Re: TRUE/FALSE SQL functionality

[mark boylan <ma...@hotmail.com>]

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/>
>
>

Re: TRUE/FALSE SQL functionality

[Rick Hillegas <Ri...@Sun.COM>]

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/>