You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Rick Hillegas (JIRA)" <de...@db.apache.org> on 2005/11/21 17:39:42 UTC
[jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
[ http://issues.apache.org/jira/browse/DERBY-499?page=all ]
Rick Hillegas updated DERBY-499:
--------------------------------
Attachment: BooleanFS.html
I have attached a thin functional specification describing expected SQL and JDBC behavior for the re-enabled BOOLEAN datatype.
> Expose BOOLEAN datatype to end users
> ------------------------------------
>
> Key: DERBY-499
> URL: http://issues.apache.org/jira/browse/DERBY-499
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.1.1.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Attachments: BooleanFS.html
>
> Veaceslav Chicu started an email thread on 8 August 2005 titled "boolean type". He was disappointed that Derby doesn't support the ansi BOOLEAN datatype. On closer inspection, Derby does internally support this type but does not expose this support to end users.
> Derby should let users declare table columns of type BOOLEAN. This should be an indexable datatype.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Francois Orsini <fr...@gmail.com>.
Yep - not a problem tho...
Thanks Rick
On 11/21/05, Rick Hillegas <Ri...@sun.com> wrote:
>
> Hi Francois,
>
> I think what is puzzling you is the use of a single INSERT statement to
> poke two rows into the table. Each of the parenthisized lists after the
> VALUES keyword is actually a separate tuple. Putting each tuple on its
> own line might clarify this.
>
> Thanks,
> -Rick
>
> Francois Orsini wrote:
>
> > Ok thanks for the nullable clarification .
> >
> > I would not necessarily associate ANSI BOOLEAN datatype to
> > java.lang.Boolean one (different domains), but for the sake of this
> > clarification, it does work.
> >
> > The statement I mentioned is:
> >
> >INSERT INTO SAMPLE( boolCol ) VALUES ( true ), ( false );
> >
> >There is only 1 column in the table - the VALUES clause shows 2
> values...It may be that am not up-to-date on the latest SQL syntax ;)
> >
> >Thanks,
> >
> >
> >--francois
> >
> >
> >
> >
>
>
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Francois,
I think what is puzzling you is the use of a single INSERT statement to
poke two rows into the table. Each of the parenthisized lists after the
VALUES keyword is actually a separate tuple. Putting each tuple on its
own line might clarify this.
Thanks,
-Rick
Francois Orsini wrote:
> Ok thanks for the nullable clarification .
>
> I would not necessarily associate ANSI BOOLEAN datatype to
> java.lang.Boolean one (different domains), but for the sake of this
> clarification, it does work.
>
> The statement I mentioned is:
>
>INSERT INTO SAMPLE( boolCol ) VALUES ( true ), ( false );
>
>There is only 1 column in the table - the VALUES clause shows 2 values...It may be that am not up-to-date on the latest SQL syntax ;)
>
>Thanks,
>
>
>--francois
>
>
>
>
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Francois Orsini <fr...@gmail.com>.
Ok thanks for the nullable clarification .
I would not necessarily associate ANSI BOOLEAN datatype to
java.lang.Booleanone (different domains), but for the sake of this
clarification, it does
work.
The statement I mentioned is:
INSERT INTO SAMPLE( boolCol ) VALUES ( true ), ( false );
There is only 1 column in the table - the VALUES clause shows 2
values...It may be that am not up-to-date on the latest SQL syntax ;)
Thanks,
--francois
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Francois,
In ANSI, BOOLEAN columns can be nullable, just like other columns. So
there are three values which a BOOLEAN column may take: true, false, and
null. Think of ANSI BOOLEAN as java.lang.Boolean.
I'm afraid I'm confused about your confusion about the INSERT statement.
How can I clarify this piece of code?
Thanks,
-Rick
Francois Orsini wrote:
> Hi Rick,
>
> For a column of type BOOLEAN and Nullable (if such thing allowed in
> the first place), is the default value 'false' if value not specified
> upon an INSERT DML? - Am assuming that the BOOLEAN datatype as defined
> in ANSI cannot be null - just wondering? Maybe something to add to the
> specs.
>
> Also, the INSERT statement in the specs looks a bit confusing (VALUES
> clause having 'false' & 'true' specified (I understood what you were
> trying to mean tho)...
>
> Thanks,
>
> --francois
>
> On 11/21/05, *Rick Hillegas* <Richard.Hillegas@sun.com
> <ma...@sun.com>> wrote:
>
> Hi Bryan,
>
> Nope. This does not affect getBoolean() and setBoolean() on other
> datatypes.
>
> Cheers,
> -Rick
>
> Bryan Pendleton wrote:
>
> >> I have attached a thin functional specification describing
> expected
> >> SQL and JDBC behavior for the re-enabled BOOLEAN datatype.
> >
> >
> > Are there any changes to the way that ResultSet.getBoolean()
> > and PreparedStatement.setBoolean() work for data types *other*
> > than the boolean data type?
> >
> > thanks,
> >
> > bryan
> >
> >
>
>
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Francois Orsini <fr...@gmail.com>.
Hi Rick,
For a column of type BOOLEAN and Nullable (if such thing allowed in the
first place), is the default value 'false' if value not specified upon an
INSERT DML? - Am assuming that the BOOLEAN datatype as defined in ANSI
cannot be null - just wondering? Maybe something to add to the specs.
Also, the INSERT statement in the specs looks a bit confusing (VALUES clause
having 'false' & 'true' specified (I understood what you were trying to mean
tho)...
Thanks,
--francois
On 11/21/05, Rick Hillegas <Ri...@sun.com> wrote:
>
> Hi Bryan,
>
> Nope. This does not affect getBoolean() and setBoolean() on other
> datatypes.
>
> Cheers,
> -Rick
>
> Bryan Pendleton wrote:
>
> >> I have attached a thin functional specification describing expected
> >> SQL and JDBC behavior for the re-enabled BOOLEAN datatype.
> >
> >
> > Are there any changes to the way that ResultSet.getBoolean()
> > and PreparedStatement.setBoolean() work for data types *other*
> > than the boolean data type?
> >
> > thanks,
> >
> > bryan
> >
> >
>
>
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Bryan,
Nope. This does not affect getBoolean() and setBoolean() on other datatypes.
Cheers,
-Rick
Bryan Pendleton wrote:
>> I have attached a thin functional specification describing expected
>> SQL and JDBC behavior for the re-enabled BOOLEAN datatype.
>
>
> Are there any changes to the way that ResultSet.getBoolean()
> and PreparedStatement.setBoolean() work for data types *other*
> than the boolean data type?
>
> thanks,
>
> bryan
>
>
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Bryan Pendleton <bp...@amberpoint.com>.
> I have attached a thin functional specification describing expected SQL and JDBC behavior for the re-enabled BOOLEAN datatype.
Are there any changes to the way that ResultSet.getBoolean()
and PreparedStatement.setBoolean() work for data types *other*
than the boolean data type?
thanks,
bryan
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Rick Hillegas <Ri...@Sun.COM>.
...
>>> Are you planning to support getObject/setObject?
>>>
>>>
>> These don't seem to be required by the JDBC spec. I don't plan to
>> put in any extra effort to make these work, but if they work for
>> free, then I see no reason to disable them. In any event, when I'm
>> done, I'll update the spec to describe how these methods behave.
>
>
> They are required, it was a bug in pre-jdbc 4. See the EDR2 for jdbc 4
Thanks, Lance. I will update the spec to say that we will support them.
Regards,
-Rick
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by "Lance J. Andersen" <La...@Sun.COM>.
Rick Hillegas wrote:
> Hi Dan,
>
> Thanks for your comments. Some responses follow. Cheers-Rick
>
> Daniel John Debrunner wrote:
>
> ...
>
>>
>> Derby has never supported BOOLEAN, so you are not re-enabling it. This
>> might confuse readers of this spec.
>>
>>
> I will wordsmith this.
>
>> In order to support BOOLEAN data type according to SQL Standard 2003
>> (feature T031) boolean literals need to be supported, and <boolean
>> literal> includes UNKNOWN
>>
>>
> UNKNOWN will also be a supported literal. I will note this in the spec.
>
>> In the JDBC api section, I assume it means that this "In all other
>> cases, DatabaseMetaData and ResultSetMetaData will report BOOLEAN
>> columns as JDBC type java.sql.Types.SMALLINT." means the JDBC typid
>> column will be Types.SMALLINT, but the SQL type name will continue to be
>> BOOLEAN. E.g. ResultSetMetaData.getColumnNameType() will return
>> "BOOLEAN".
>
> I don't understand what it means if type names don't agree with type
> codes. I was planning to report these columns as type code
> java.sql.Types.SMALLINT and type name "SMALLINT".
>
>> Why Types.SMALLINT, and not Types.OTHER or Types.BIT?
>>
>>
> I chose SMALLINT for a tactical reason: It is the type currently used
> if the Derby client selects from one of our BOOLEAN-typed system
> columns. I'm hoping that this mapping will have fewer compatibility
> issues for existing applications.
>
>> Can I also assume you meant "ResultSet.getXXX and
>> CallableStatement.getXXX" methods?
>>
>>
> Yes to the question about CallableStatement.getXXX(). I'm afraid I
> don't understand the question about ResultSet.getXXX().
>
>> When you say "... getXXX ... setXXX methods will succeed", what exactly
>> does that mean? How do the BOOLEAN values true and false map the
>> numeric/string getXXX values, and how do numeric/string values map to
>> true/false on the setXXX methods?
>>
>>
> I will clarify this in the spec.
>
>> "methods will succeed on BOOLEAN columns peeked and poked by Derby 10.2
>> clients running on jdk1.4 or higher"
>> Can I retrieve/set BOOLEAN values on other clients?
>>
>>
> Yes. I will clarify this.
>
>> Are you planning to support getObject/setObject?
>>
>>
> These don't seem to be required by the JDBC spec. I don't plan to put
> in any extra effort to make these work, but if they work for free,
> then I see no reason to disable them. In any event, when I'm done,
> I'll update the spec to describe how these methods behave.
They are required, it was a bug in pre-jdbc 4. See the EDR2 for jdbc 4
>
>> Dan.
>>
>>
>>
>
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Army <qo...@sbcglobal.net>.
Daniel John Debrunner wrote:
>>Okay, sounds good. Then what should we return for the metadata "class
>>name"? Esp. ResultSetMetaData.getColumnClassName()?
>
> How about "java.sql.SQLXML" ?
I guess that would work since the user's attempt to call "getObject()" would
fail before the class was instantiated. But something about returning a
non-existent (for JDBC 3.0) class name seemed funny to me. *shrug* Maybe
that's the way to go, though...
Another option is to simply return "java.lang.Object" since, per the JDBC API,
"ResultSet.getObject _may_ return a _subclass_ of the class returned by this
method" (emphasis is my own). Then we'd at least be returning a valid JDBC 3.0
class name. When JDBC 4.0 support is available, changing to "java.sql.SQLXML"
seems okay to me; i.e. there wouldn't be any compatibility problems there, would
there?
Army
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Army wrote:
> Rick Hillegas wrote:
>
>> Dan Debrunner wrote:
>>
>>> I think I see a similar problem with the upcoming XML changes. If we
>>> return XML as CLOB in jdk 1.4 then applications will break when then
>>> switch to the jdk that supports JDBC 4.0. Again for the same reason,
>>> with jdk 1.4 getObject would return java.sql.Clob and in the JDBC 4.0
>>> jdk it would return java.sql.SQLXML. I think for this case
>>> get/setObject() should not succeed for XML columns until JDBC 4.0 is
>>> supported.
>>>
>>>
>> Agreed.
>
>
> Okay, sounds good. Then what should we return for the metadata "class
> name"? Esp. ResultSetMetaData.getColumnClassName()?
>
> The API for that method says:
>
> "Returns the fully-qualified name of the Java class whose instances are
> manufactured if the method ResultSet.getObject is called to retrieve a
> value from the column."
>
> So should a call to that method fail, too?
How about "java.sql.SQLXML" ?
Dan.
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Rick Hillegas wrote:
> Hi Lance,
>
> If ResultSet.getObject() throws an unimplemented feature exception,
> should the corresponding ResultSetMetaData.getColumnClassName() throw an
> exception? Return null?
As a minor point, if the existing data type code is used then it will
not be an unimplemented feature exception, but instead a cannot convert
XML to something exception.
Dan.
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by "Lance J. Andersen" <La...@Sun.COM>.
Hi Rick,
Rick Hillegas wrote:
> Hi Lance,
>
> If ResultSet.getObject() throws an unimplemented feature exception,
> should the corresponding ResultSetMetaData.getColumnClassName() throw
> an exception? Return null?
A SQLException is the correct Exception to be thrown per the spec when
something is not implemented. I am not sure null is correct, i guess an
Exception given null is not specified in the javadocs as an appropriate
return value.
>
> Thanks,
> -Rick
>
> Army wrote:
>
>> Rick Hillegas wrote:
>>
>>> Dan Debrunner wrote:
>>>
>>>> I think I see a similar problem with the upcoming XML changes. If we
>>>> return XML as CLOB in jdk 1.4 then applications will break when then
>>>> switch to the jdk that supports JDBC 4.0. Again for the same reason,
>>>> with jdk 1.4 getObject would return java.sql.Clob and in the JDBC 4.0
>>>> jdk it would return java.sql.SQLXML. I think for this case
>>>> get/setObject() should not succeed for XML columns until JDBC 4.0 is
>>>> supported.
>>>>
>>>>
>>> Agreed.
>>
>>
>>
>> Okay, sounds good. Then what should we return for the metadata
>> "class name"? Esp. ResultSetMetaData.getColumnClassName()?
>>
>> The API for that method says:
>>
>> "Returns the fully-qualified name of the Java class whose instances
>> are manufactured if the method ResultSet.getObject is called to
>> retrieve a value from the column."
>>
>> So should a call to that method fail, too?
>>
>> Army
>>
>
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Lance,
If ResultSet.getObject() throws an unimplemented feature exception,
should the corresponding ResultSetMetaData.getColumnClassName() throw an
exception? Return null?
Thanks,
-Rick
Army wrote:
> Rick Hillegas wrote:
>
>> Dan Debrunner wrote:
>>
>>> I think I see a similar problem with the upcoming XML changes. If we
>>> return XML as CLOB in jdk 1.4 then applications will break when then
>>> switch to the jdk that supports JDBC 4.0. Again for the same reason,
>>> with jdk 1.4 getObject would return java.sql.Clob and in the JDBC 4.0
>>> jdk it would return java.sql.SQLXML. I think for this case
>>> get/setObject() should not succeed for XML columns until JDBC 4.0 is
>>> supported.
>>>
>>>
>> Agreed.
>
>
> Okay, sounds good. Then what should we return for the metadata "class
> name"? Esp. ResultSetMetaData.getColumnClassName()?
>
> The API for that method says:
>
> "Returns the fully-qualified name of the Java class whose instances
> are manufactured if the method ResultSet.getObject is called to
> retrieve a value from the column."
>
> So should a call to that method fail, too?
>
> Army
>
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Army <qo...@sbcglobal.net>.
Rick Hillegas wrote:
> Dan Debrunner wrote:
>> I think I see a similar problem with the upcoming XML changes. If we
>> return XML as CLOB in jdk 1.4 then applications will break when then
>> switch to the jdk that supports JDBC 4.0. Again for the same reason,
>> with jdk 1.4 getObject would return java.sql.Clob and in the JDBC 4.0
>> jdk it would return java.sql.SQLXML. I think for this case
>> get/setObject() should not succeed for XML columns until JDBC 4.0 is
>> supported.
>>
>>
> Agreed.
Okay, sounds good. Then what should we return for the metadata "class name"?
Esp. ResultSetMetaData.getColumnClassName()?
The API for that method says:
"Returns the fully-qualified name of the Java class whose instances are
manufactured if the method ResultSet.getObject is called to retrieve a value
from the column."
So should a call to that method fail, too?
Army
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Rick Hillegas <Ri...@Sun.COM>.
>I think I see a similar problem with the upcoming XML changes. If we
>return XML as CLOB in jdk 1.4 then applications will break when then
>switch to the jdk that supports JDBC 4.0. Again for the same reason,
>with jdk 1.4 getObject would return java.sql.Clob and in the JDBC 4.0
>jdk it would return java.sql.SQLXML. I think for this case
>get/setObject() should not succeed for XML columns until JDBC 4.0 is
>supported.
>
>
Agreed.
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Rick Hillegas wrote:
>
>>
>>
>>> I am trying to avoid an explosion of cases because I think that will
>>> cause a lot of support headaches. The simplification I was hoping for
>>> was one set of behaviors governing clients/servers operating at the
>>> current Derby and JDBC rev level and another set of behaviors for all
>>> other combinations.
>>>
>>
>>
>> I would have no issue if existing JCC/10.1 network clients continued to
>> report BOOLEAN as SMALLINT, as they seem to. I would have an issue if
>> you changed the current correct behaviour of embedded under jdk 1.3 to
>> match that.
>>
>>
> I will abandon my attempt to simplify the support problem. Instead, I
> will amend the spec as follows: I will add a matrix describing how the
> BOOLEAN datatype will appear to clients based on client and server rev
> and vm.
I think your original direction would cause problems with upwards
compatibility for applications. An embedded application running under
jdk 1.3 would see changes in behaviour when upgrading the VM to Jdk 1.4
or later. This is because api to the BOOLEAN column would change, namely
getObject would change from returning java.lang.Integer (for SMALLINT)
to returning java.lang.Boolean (for BOOLEAN).
I think I see a similar problem with the upcoming XML changes. If we
return XML as CLOB in jdk 1.4 then applications will break when then
switch to the jdk that supports JDBC 4.0. Again for the same reason,
with jdk 1.4 getObject would return java.sql.Clob and in the JDBC 4.0
jdk it would return java.sql.SQLXML. I think for this case
get/setObject() should not succeed for XML columns until JDBC 4.0 is
supported.
Dan.
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Rick Hillegas <Ri...@Sun.COM>.
>
>
>>I am trying to avoid an explosion of cases because I think that will
>>cause a lot of support headaches. The simplification I was hoping for
>>was one set of behaviors governing clients/servers operating at the
>>current Derby and JDBC rev level and another set of behaviors for all
>>other combinations.
>>
>>
>
>I would have no issue if existing JCC/10.1 network clients continued to
>report BOOLEAN as SMALLINT, as they seem to. I would have an issue if
>you changed the current correct behaviour of embedded under jdk 1.3 to
>match that.
>
>
I will abandon my attempt to simplify the support problem. Instead, I
will amend the spec as follows: I will add a matrix describing how the
BOOLEAN datatype will appear to clients based on client and server rev
and vm.
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Rick Hillegas wrote:
> It depends on what rev level you're running at. Here are the cases:
>
> Embedded on jdk1.3:
>
> DatabaseMetaData: java.sql.Types.BIT (called "BOOLEAN")
> ResultSetMetaData: java.sql.Types.BIT (called "BOOLEAN")
>
> Embedded on jdk1.4:
>
> DatabaseMetaData: java.sql.Types.BOOLEAN (called "BOOLEAN")
> ResultSetMetaData: java.sql.Types.BOOLEAN (called "BOOLEAN")
>
> Client on jdk1.3/1.4 against server on jdk1.3:
>
> DatabaseMetaData: java.sql.Types.BIT (called "BOOLEAN")
> ResultSetMetaData: java.sql.Types.SMALLINT (called "SMALLINT")
>
> Client on jdk1.3/jdk1.4 against server on jdk1.4:
>
> DatabaseMetaData: java.sql.Types.BOOLEAN (called "BOOLEAN")
> ResultSetMetaData: java.sql.Types.SMALLINT (called "SMALLINT")
> I am trying to avoid an explosion of cases because I think that will
> cause a lot of support headaches. The simplification I was hoping for
> was one set of behaviors governing clients/servers operating at the
> current Derby and JDBC rev level and another set of behaviors for all
> other combinations.
I would have no issue if existing JCC/10.1 network clients continued to
report BOOLEAN as SMALLINT, as they seem to. I would have an issue if
you changed the current correct behaviour of embedded under jdk 1.3 to
match that.
Dan.
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Rick Hillegas <Ri...@Sun.COM>.
...
>
>>>In the JDBC api section, I assume it means that this "In all other
>>>cases, DatabaseMetaData and ResultSetMetaData will report BOOLEAN
>>>columns as JDBC type java.sql.Types.SMALLINT." means the JDBC typid
>>>column will be Types.SMALLINT, but the SQL type name will continue to be
>>>BOOLEAN. E.g. ResultSetMetaData.getColumnNameType() will return
>>>"BOOLEAN".
>>>
>>>
>>I don't understand what it means if type names don't agree with type
>>codes. I was planning to report these columns as type code
>>java.sql.Types.SMALLINT and type name "SMALLINT".
>>
>>
>
>I think that's wrong, the SQL name/type of the column is BOOLEAN, not
>SMALLINT. As for "type names don't agree with type codes", I'm not sure
>what you mean by agree, these are two different type domains, JDBC for
>the Types.XXX values and SQL for the SQL names. The JDBC driver is
>providing a mapping between domains, there is not any requirement for
>agreement in names to ids.
>
>
You are right. That's what the javadoc says. I will update the spec to
say that getColumnTypeName() will return the string "BOOLEAN" for
BOOLEAN-typed columns.
>
>
>>>Why Types.SMALLINT, and not Types.OTHER or Types.BIT?
>>>
>>>
>>>
>>>
>>I chose SMALLINT for a tactical reason: It is the type currently used
>>if the Derby client selects from one of our BOOLEAN-typed system
>>columns.
>>
>>
>
>Does the embedded driver report these columns as SMALLINT? Types.BIT was
>the recommended value for BOOLEAN SQL types in JDBC 2, look at the Java
>type to JDBC type mapping for Types.BIT.
>
>
It depends on what rev level you're running at. Here are the cases:
Embedded on jdk1.3:
DatabaseMetaData: java.sql.Types.BIT (called "BOOLEAN")
ResultSetMetaData: java.sql.Types.BIT (called "BOOLEAN")
Embedded on jdk1.4:
DatabaseMetaData: java.sql.Types.BOOLEAN (called "BOOLEAN")
ResultSetMetaData: java.sql.Types.BOOLEAN (called "BOOLEAN")
Client on jdk1.3/1.4 against server on jdk1.3:
DatabaseMetaData: java.sql.Types.BIT (called "BOOLEAN")
ResultSetMetaData: java.sql.Types.SMALLINT (called "SMALLINT")
Client on jdk1.3/jdk1.4 against server on jdk1.4:
DatabaseMetaData: java.sql.Types.BOOLEAN (called "BOOLEAN")
ResultSetMetaData: java.sql.Types.SMALLINT (called "SMALLINT")
>
>
>>I'm hoping that this mapping will have fewer compatibility
>>issues for existing applications.
>>
>>
>
>So I assume you mean existing applications from other databases that
>currently support BOOLEAN SQL type. Do you know what JDBC type id those
>databases return?
>
I was concerned about compatibiltiy with existing Derby-enabled
applications running on previous Derby revs or old VMs.
Unfortunately, there are two other type systems hidden in our network
layer: DRDA data value formats and DB2 type ids. I am skeptical that we
could get old Derby clients to handle BIT datatypes and values correctly.
I am trying to avoid an explosion of cases because I think that will
cause a lot of support headaches. The simplification I was hoping for
was one set of behaviors governing clients/servers operating at the
current Derby and JDBC rev level and another set of behaviors for all
other combinations.
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Rick Hillegas wrote:
> Hi Dan,
>
> Thanks for your comments. Some responses follow. Cheers-Rick
>
> Daniel John Debrunner wrote:
>> In the JDBC api section, I assume it means that this "In all other
>> cases, DatabaseMetaData and ResultSetMetaData will report BOOLEAN
>> columns as JDBC type java.sql.Types.SMALLINT." means the JDBC typid
>> column will be Types.SMALLINT, but the SQL type name will continue to be
>> BOOLEAN. E.g. ResultSetMetaData.getColumnNameType() will return
>> "BOOLEAN".
>
> I don't understand what it means if type names don't agree with type
> codes. I was planning to report these columns as type code
> java.sql.Types.SMALLINT and type name "SMALLINT".
I think that's wrong, the SQL name/type of the column is BOOLEAN, not
SMALLINT. As for "type names don't agree with type codes", I'm not sure
what you mean by agree, these are two different type domains, JDBC for
the Types.XXX values and SQL for the SQL names. The JDBC driver is
providing a mapping between domains, there is not any requirement for
agreement in names to ids.
>> Why Types.SMALLINT, and not Types.OTHER or Types.BIT?
>>
>>
> I chose SMALLINT for a tactical reason: It is the type currently used
> if the Derby client selects from one of our BOOLEAN-typed system
> columns.
Does the embedded driver report these columns as SMALLINT? Types.BIT was
the recommended value for BOOLEAN SQL types in JDBC 2, look at the Java
type to JDBC type mapping for Types.BIT.
> I'm hoping that this mapping will have fewer compatibility
> issues for existing applications.
So I assume you mean existing applications from other databases that
currently support BOOLEAN SQL type. Do you know what JDBC type id those
databases return?
Dan.
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Dan,
Thanks for your comments. Some responses follow. Cheers-Rick
Daniel John Debrunner wrote:
...
>
>Derby has never supported BOOLEAN, so you are not re-enabling it. This
>might confuse readers of this spec.
>
>
I will wordsmith this.
>In order to support BOOLEAN data type according to SQL Standard 2003
>(feature T031) boolean literals need to be supported, and <boolean
>literal> includes UNKNOWN
>
>
UNKNOWN will also be a supported literal. I will note this in the spec.
>In the JDBC api section, I assume it means that this "In all other
>cases, DatabaseMetaData and ResultSetMetaData will report BOOLEAN
>columns as JDBC type java.sql.Types.SMALLINT." means the JDBC typid
>column will be Types.SMALLINT, but the SQL type name will continue to be
>BOOLEAN. E.g. ResultSetMetaData.getColumnNameType() will return
>"BOOLEAN".
>
I don't understand what it means if type names don't agree with type
codes. I was planning to report these columns as type code
java.sql.Types.SMALLINT and type name "SMALLINT".
>Why Types.SMALLINT, and not Types.OTHER or Types.BIT?
>
>
I chose SMALLINT for a tactical reason: It is the type currently used
if the Derby client selects from one of our BOOLEAN-typed system
columns. I'm hoping that this mapping will have fewer compatibility
issues for existing applications.
>Can I also assume you meant "ResultSet.getXXX and
>CallableStatement.getXXX" methods?
>
>
Yes to the question about CallableStatement.getXXX(). I'm afraid I don't
understand the question about ResultSet.getXXX().
>When you say "... getXXX ... setXXX methods will succeed", what exactly
>does that mean? How do the BOOLEAN values true and false map the
>numeric/string getXXX values, and how do numeric/string values map to
>true/false on the setXXX methods?
>
>
I will clarify this in the spec.
>"methods will succeed on BOOLEAN columns peeked and poked by Derby 10.2
>clients running on jdk1.4 or higher"
>Can I retrieve/set BOOLEAN values on other clients?
>
>
Yes. I will clarify this.
>Are you planning to support getObject/setObject?
>
>
These don't seem to be required by the JDBC spec. I don't plan to put
in any extra effort to make these work, but if they work for free, then
I see no reason to disable them. In any event, when I'm done, I'll
update the spec to describe how these methods behave.
>Dan.
>
>
>
Re: [jira] Updated: (DERBY-499) Expose BOOLEAN datatype to end users
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Rick Hillegas (JIRA) wrote:
> [ http://issues.apache.org/jira/browse/DERBY-499?page=all ]
>
> Rick Hillegas updated DERBY-499:
> --------------------------------
>
> Attachment: BooleanFS.html
>
> I have attached a thin functional specification describing expected SQL and JDBC behavior for the re-enabled BOOLEAN datatype.
Nice enhancment for Derby, I have some comments, most are related to
clarifying what you are intending to do.
Derby has never supported BOOLEAN, so you are not re-enabling it. This
might confuse readers of this spec.
In order to support BOOLEAN data type according to SQL Standard 2003
(feature T031) boolean literals need to be supported, and <boolean
literal> includes UNKNOWN
In the JDBC api section, I assume it means that this "In all other
cases, DatabaseMetaData and ResultSetMetaData will report BOOLEAN
columns as JDBC type java.sql.Types.SMALLINT." means the JDBC typid
column will be Types.SMALLINT, but the SQL type name will continue to be
BOOLEAN. E.g. ResultSetMetaData.getColumnNameType() will return
"BOOLEAN". Why Types.SMALLINT, and not Types.OTHER or Types.BIT?
Can I also assume you meant "ResultSet.getXXX and
CallableStatement.getXXX" methods?
When you say "... getXXX ... setXXX methods will succeed", what exactly
does that mean? How do the BOOLEAN values true and false map the
numeric/string getXXX values, and how do numeric/string values map to
true/false on the setXXX methods?
"methods will succeed on BOOLEAN columns peeked and poked by Derby 10.2
clients running on jdk1.4 or higher"
Can I retrieve/set BOOLEAN values on other clients?
Are you planning to support getObject/setObject?
Dan.