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/09 22:58:03 UTC
[jira] Created: (DERBY-695) Re-enable the TINYINT datatype
Re-enable the TINYINT datatype
------------------------------
Key: DERBY-695
URL: http://issues.apache.org/jira/browse/DERBY-695
Project: Derby
Type: New Feature
Reporter: Rick Hillegas
I would like to collect here the arguments for and against re-enabling the TINYINT datatype. Once this discussion calms down, we can schedule a vote on the issue.
Background: Cloudscape used to support the TINYINT datatype, which was an 8 bit int. This datatype was hidden from customers as part of an effort to remove all datatypes not supported by DB2. Re-enabling the datatype would not require a lot of effort. Some arguments for and against re-enabling this datatype can be found on the November 2005 email thread titled "New features for next release .... (Was: Grant and Revoke ... DERBY-464...)".
Here are the arguments in favor so far:
+ This datatype is defined by one of our key standards, JDBC. It is in JDBC 2, 3, and 4, all of the JDBC revs supported by Derby 10.2.
+ This datatype is supported by some important databases, including MySQL, Microsoft SQL Server, and Sybase.
Here are the arguments against so far:
- This datatype is not defined by our other key standard, ANSI SQL. Here our two main standards diverge.
- This datatype is not supported by some important databases, including Oracle, DB2, and (some) Informix databases.
Against this proposal, it was also argued that there was some sort of friction with ODBC. I do not understand this argument: SQL_TINYINT is an ODBC datatype. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsql_data_types.asp.
A friction with .NET was also suggested but I don't understand this either. "byte" and "Sbyte" are the .NET 8-bit integer types. See http://www.codersource.net/csharp_tutorial_data_types.html.
A friction with Perl was also suggested but I don't understand this either.
--
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] Created: (DERBY-695) Re-enable the TINYINT datatype
Posted by "Lance J. Andersen" <La...@Sun.COM>.
David W. Van Couvering wrote:
> One could argue that adding TINYINT support makes it harder to migrate
> applications from Derby *to* Oracle and DB2. But it seems to me this
> is easily mitigated by clearly documenting that this type is not
> supported by databases X,Y,Z and thus should be avoided if your plan
> is to migrate to that database in the future.
You could argue that but...
The concern IMHO is migrating apps to Derby not away from Derby. We
need to make it easier for people to adopt. You could always add a
level of verbosity via a flag which generates warnings when
non-compliant syntax is used in addition to adding a means to flag SQL
compliance to the docs for various syntax and datatypes. SQL Anywhere
does a pretty good job of that. Oracle I believe has a chart in their
docs which compares their datatypes and SQL standard datatypes along
with a guide as to what they should map to
I would rather document SQL compliance (for more than just datatypes)
than not support the datatype.
Besides the more vendors that support it, the sooner will be considered
for adoption by the standards committee
>
> David
>
> Lance J. Andersen wrote:
>
>> I am for adding this datatype back. While it may not be part of the
>> SQL Standard, it is a common datatype supported by multiple vendors.
>> Having this datatype supported helps with the migration of
>> applications and at the end of the day making it easier for
>> applications to migrate is more important than basing a decision
>> soley on whether the datatype is supported by the SQL standards.
>>
>> +1 (unofficial vote) for this addition
>>
>> Rick Hillegas (JIRA) wrote:
>>
>>> Re-enable the TINYINT datatype
>>> ------------------------------
>>>
>>> Key: DERBY-695
>>> URL: http://issues.apache.org/jira/browse/DERBY-695
>>> Project: Derby
>>> Type: New Feature
>>> Reporter: Rick Hillegas
>>>
>>>
>>> I would like to collect here the arguments for and against
>>> re-enabling the TINYINT datatype. Once this discussion calms down,
>>> we can schedule a vote on the issue.
>>>
>>> Background: Cloudscape used to support the TINYINT datatype, which
>>> was an 8 bit int. This datatype was hidden from customers as part of
>>> an effort to remove all datatypes not supported by DB2. Re-enabling
>>> the datatype would not require a lot of effort. Some arguments for
>>> and against re-enabling this datatype can be found on the November
>>> 2005 email thread titled "New features for next release .... (Was:
>>> Grant and Revoke ... DERBY-464...)".
>>>
>>> Here are the arguments in favor so far:
>>>
>>> + This datatype is defined by one of our key standards, JDBC. It is
>>> in JDBC 2, 3, and 4, all of the JDBC revs supported by Derby 10.2.
>>> + This datatype is supported by some important databases, including
>>> MySQL, Microsoft SQL Server, and Sybase.
>>>
>>> Here are the arguments against so far:
>>>
>>> - This datatype is not defined by our other key standard, ANSI SQL.
>>> Here our two main standards diverge.
>>> - This datatype is not supported by some important databases,
>>> including Oracle, DB2, and (some) Informix databases.
>>>
>>> Against this proposal, it was also argued that there was some sort
>>> of friction with ODBC. I do not understand this argument:
>>> SQL_TINYINT is an ODBC datatype. See
>>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsql_data_types.asp.
>>>
>>>
>>> A friction with .NET was also suggested but I don't understand this
>>> either. "byte" and "Sbyte" are the .NET 8-bit integer types. See
>>> http://www.codersource.net/csharp_tutorial_data_types.html.
>>>
>>> A friction with Perl was also suggested but I don't understand this
>>> either.
>>>
>>>
>>>
>>>
>>>
Re: [jira] Created: (DERBY-695) Re-enable the TINYINT datatype
Posted by "David W. Van Couvering" <Da...@Sun.COM>.
One could argue that adding TINYINT support makes it harder to migrate
applications from Derby *to* Oracle and DB2. But it seems to me this is
easily mitigated by clearly documenting that this type is not supported
by databases X,Y,Z and thus should be avoided if your plan is to migrate
to that database in the future.
David
Lance J. Andersen wrote:
> I am for adding this datatype back. While it may not be part of the SQL
> Standard, it is a common datatype supported by multiple vendors. Having
> this datatype supported helps with the migration of applications and at
> the end of the day making it easier for applications to migrate is more
> important than basing a decision soley on whether the datatype is
> supported by the SQL standards.
>
> +1 (unofficial vote) for this addition
>
> Rick Hillegas (JIRA) wrote:
>
>> Re-enable the TINYINT datatype
>> ------------------------------
>>
>> Key: DERBY-695
>> URL: http://issues.apache.org/jira/browse/DERBY-695
>> Project: Derby
>> Type: New Feature
>> Reporter: Rick Hillegas
>>
>>
>> I would like to collect here the arguments for and against re-enabling
>> the TINYINT datatype. Once this discussion calms down, we can schedule
>> a vote on the issue.
>>
>> Background: Cloudscape used to support the TINYINT datatype, which was
>> an 8 bit int. This datatype was hidden from customers as part of an
>> effort to remove all datatypes not supported by DB2. Re-enabling the
>> datatype would not require a lot of effort. Some arguments for and
>> against re-enabling this datatype can be found on the November 2005
>> email thread titled "New features for next release .... (Was: Grant
>> and Revoke ... DERBY-464...)".
>>
>> Here are the arguments in favor so far:
>>
>> + This datatype is defined by one of our key standards, JDBC. It is in
>> JDBC 2, 3, and 4, all of the JDBC revs supported by Derby 10.2.
>> + This datatype is supported by some important databases, including
>> MySQL, Microsoft SQL Server, and Sybase.
>>
>> Here are the arguments against so far:
>>
>> - This datatype is not defined by our other key standard, ANSI SQL.
>> Here our two main standards diverge.
>> - This datatype is not supported by some important databases,
>> including Oracle, DB2, and (some) Informix databases.
>>
>> Against this proposal, it was also argued that there was some sort of
>> friction with ODBC. I do not understand this argument: SQL_TINYINT is
>> an ODBC datatype. See
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsql_data_types.asp.
>>
>>
>> A friction with .NET was also suggested but I don't understand this
>> either. "byte" and "Sbyte" are the .NET 8-bit integer types. See
>> http://www.codersource.net/csharp_tutorial_data_types.html.
>>
>> A friction with Perl was also suggested but I don't understand this
>> either.
>>
>>
>>
>>
>>
Re: [jira] Created: (DERBY-695) Re-enable the TINYINT datatype
Posted by "Lance J. Andersen" <La...@Sun.COM>.
I am for adding this datatype back. While it may not be part of the SQL
Standard, it is a common datatype supported by multiple vendors. Having
this datatype supported helps with the migration of applications and at
the end of the day making it easier for applications to migrate is more
important than basing a decision soley on whether the datatype is
supported by the SQL standards.
+1 (unofficial vote) for this addition
Rick Hillegas (JIRA) wrote:
>Re-enable the TINYINT datatype
>------------------------------
>
> Key: DERBY-695
> URL: http://issues.apache.org/jira/browse/DERBY-695
> Project: Derby
> Type: New Feature
> Reporter: Rick Hillegas
>
>
>I would like to collect here the arguments for and against re-enabling the TINYINT datatype. Once this discussion calms down, we can schedule a vote on the issue.
>
>Background: Cloudscape used to support the TINYINT datatype, which was an 8 bit int. This datatype was hidden from customers as part of an effort to remove all datatypes not supported by DB2. Re-enabling the datatype would not require a lot of effort. Some arguments for and against re-enabling this datatype can be found on the November 2005 email thread titled "New features for next release .... (Was: Grant and Revoke ... DERBY-464...)".
>
>Here are the arguments in favor so far:
>
>+ This datatype is defined by one of our key standards, JDBC. It is in JDBC 2, 3, and 4, all of the JDBC revs supported by Derby 10.2.
>+ This datatype is supported by some important databases, including MySQL, Microsoft SQL Server, and Sybase.
>
>Here are the arguments against so far:
>
>- This datatype is not defined by our other key standard, ANSI SQL. Here our two main standards diverge.
>- This datatype is not supported by some important databases, including Oracle, DB2, and (some) Informix databases.
>
>Against this proposal, it was also argued that there was some sort of friction with ODBC. I do not understand this argument: SQL_TINYINT is an ODBC datatype. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsql_data_types.asp.
>
>A friction with .NET was also suggested but I don't understand this either. "byte" and "Sbyte" are the .NET 8-bit integer types. See http://www.codersource.net/csharp_tutorial_data_types.html.
>
>A friction with Perl was also suggested but I don't understand this either.
>
>
>
>
>
[jira] Updated: (DERBY-695) Re-enable the TINYINT datatype
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-695?page=all ]
Mike Matrigali updated DERBY-695:
---------------------------------
Component: SQL
> Re-enable the TINYINT datatype
> ------------------------------
>
> Key: DERBY-695
> URL: http://issues.apache.org/jira/browse/DERBY-695
> Project: Derby
> Type: New Feature
> Components: SQL
> Reporter: Rick Hillegas
>
> I would like to collect here the arguments for and against re-enabling the TINYINT datatype. Once this discussion calms down, we can schedule a vote on the issue.
> Background: Cloudscape used to support the TINYINT datatype, which was an 8 bit int. This datatype was hidden from customers as part of an effort to remove all datatypes not supported by DB2. Re-enabling the datatype would not require a lot of effort. Some arguments for and against re-enabling this datatype can be found on the November 2005 email thread titled "New features for next release .... (Was: Grant and Revoke ... DERBY-464...)".
> Here are the arguments in favor so far:
> + This datatype is defined by one of our key standards, JDBC. It is in JDBC 2, 3, and 4, all of the JDBC revs supported by Derby 10.2.
> + This datatype is supported by some important databases, including MySQL, Microsoft SQL Server, and Sybase.
> Here are the arguments against so far:
> - This datatype is not defined by our other key standard, ANSI SQL. Here our two main standards diverge.
> - This datatype is not supported by some important databases, including Oracle, DB2, and (some) Informix databases.
> Against this proposal, it was also argued that there was some sort of friction with ODBC. I do not understand this argument: SQL_TINYINT is an ODBC datatype. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsql_data_types.asp.
> A friction with .NET was also suggested but I don't understand this either. "byte" and "Sbyte" are the .NET 8-bit integer types. See http://www.codersource.net/csharp_tutorial_data_types.html.
> A friction with Perl was also suggested but I don't understand this either.
--
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] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Rick Hillegas wrote:
> My apologies for the colorful language. I do recall a general discussion
> about how there was no standard for optimizer hints. But I don't recall
> ANSI's silence on the matter being put forward as an objection.
Well it's kind of "scratch your own objection" :-)
One difference I can see is that optimizer directives provide value to
Derby, it enables something that cannot be done currently. TINYINT on
the other hand doesn't really add much value, the same functionality can
be achieved by the application using SMALLINT.
Dan.
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Rick Hillegas <Ri...@Sun.COM>.
My apologies for the colorful language. I do recall a general discussion
about how there was no standard for optimizer hints. But I don't recall
ANSI's silence on the matter being put forward as an objection.
Cheers,
-Rick
Daniel John Debrunner wrote:
>Rick Hillegas (JIRA) wrote:
>
>
>
>
>>I would like to explore why TINYINT should have to sustain an attack not mounted against, say, optimizer hints.
>>
>>
>
>I don't think anyone is "attacking" anything, it's just a discussion of
>the various issues and views.
>
>I think optimizer directives went through a similar discussion, mainly
>centering on not generating a system where SQL statements would no
>longer run on other database systems.
>
>Dan.
>
>
>
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Rick Hillegas (JIRA) wrote:
> I would like to explore why TINYINT should have to sustain an attack not mounted against, say, optimizer hints.
I don't think anyone is "attacking" anything, it's just a discussion of
the various issues and views.
I think optimizer directives went through a similar discussion, mainly
centering on not generating a system where SQL statements would no
longer run on other database systems.
Dan.
[jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-695?page=comments#action_12357165 ]
Rick Hillegas commented on DERBY-695:
-------------------------------------
I'm sorry for garbling your argument. I share your feeling that where ANSI does propose solutions, we should consider them carefully. Merely relying on ANSI though, we would not have indexes or system procedures. We have not recently felt constrained by the fact that ANSI omits features. If you are willing, I would like to explore why TINYINT should have to sustain an attack not mounted against, say, optimizer hints.
The 8-bit integer is a useful feature, as evidenced by its presence in Java, C#, JDBC, ODBC, DRDA, and several major databases.
> Re-enable the TINYINT datatype
> ------------------------------
>
> Key: DERBY-695
> URL: http://issues.apache.org/jira/browse/DERBY-695
> Project: Derby
> Type: New Feature
> Reporter: Rick Hillegas
>
> I would like to collect here the arguments for and against re-enabling the TINYINT datatype. Once this discussion calms down, we can schedule a vote on the issue.
> Background: Cloudscape used to support the TINYINT datatype, which was an 8 bit int. This datatype was hidden from customers as part of an effort to remove all datatypes not supported by DB2. Re-enabling the datatype would not require a lot of effort. Some arguments for and against re-enabling this datatype can be found on the November 2005 email thread titled "New features for next release .... (Was: Grant and Revoke ... DERBY-464...)".
> Here are the arguments in favor so far:
> + This datatype is defined by one of our key standards, JDBC. It is in JDBC 2, 3, and 4, all of the JDBC revs supported by Derby 10.2.
> + This datatype is supported by some important databases, including MySQL, Microsoft SQL Server, and Sybase.
> Here are the arguments against so far:
> - This datatype is not defined by our other key standard, ANSI SQL. Here our two main standards diverge.
> - This datatype is not supported by some important databases, including Oracle, DB2, and (some) Informix databases.
> Against this proposal, it was also argued that there was some sort of friction with ODBC. I do not understand this argument: SQL_TINYINT is an ODBC datatype. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsql_data_types.asp.
> A friction with .NET was also suggested but I don't understand this either. "byte" and "Sbyte" are the .NET 8-bit integer types. See http://www.codersource.net/csharp_tutorial_data_types.html.
> A friction with Perl was also suggested but I don't understand this either.
--
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
[jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-695?page=comments#action_12357155 ]
Satheesh Bandaram commented on DERBY-695:
-----------------------------------------
I wasn't saying Derby shouldn't have TINYINT because it causes problems for ODBC or .NET or perl...Not sure where that was implied.
I had a comment about, in general, what Derby should do in case of a conflict between SQL standard specification Vs what JDBC spec. I was only saying, in my opinion, Derby might put more weigh on SQL specification. This part of the comment had NOTHING to do with TINYINT issue...
> Re-enable the TINYINT datatype
> ------------------------------
>
> Key: DERBY-695
> URL: http://issues.apache.org/jira/browse/DERBY-695
> Project: Derby
> Type: New Feature
> Reporter: Rick Hillegas
>
> I would like to collect here the arguments for and against re-enabling the TINYINT datatype. Once this discussion calms down, we can schedule a vote on the issue.
> Background: Cloudscape used to support the TINYINT datatype, which was an 8 bit int. This datatype was hidden from customers as part of an effort to remove all datatypes not supported by DB2. Re-enabling the datatype would not require a lot of effort. Some arguments for and against re-enabling this datatype can be found on the November 2005 email thread titled "New features for next release .... (Was: Grant and Revoke ... DERBY-464...)".
> Here are the arguments in favor so far:
> + This datatype is defined by one of our key standards, JDBC. It is in JDBC 2, 3, and 4, all of the JDBC revs supported by Derby 10.2.
> + This datatype is supported by some important databases, including MySQL, Microsoft SQL Server, and Sybase.
> Here are the arguments against so far:
> - This datatype is not defined by our other key standard, ANSI SQL. Here our two main standards diverge.
> - This datatype is not supported by some important databases, including Oracle, DB2, and (some) Informix databases.
> Against this proposal, it was also argued that there was some sort of friction with ODBC. I do not understand this argument: SQL_TINYINT is an ODBC datatype. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsql_data_types.asp.
> A friction with .NET was also suggested but I don't understand this either. "byte" and "Sbyte" are the .NET 8-bit integer types. See http://www.codersource.net/csharp_tutorial_data_types.html.
> A friction with Perl was also suggested but I don't understand this either.
--
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] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Rick Hillegas wrote:
> You can also implement NOT NULL as a check contraint. Most databases
> consider that to be an inefficiency which it's worth optimizing with
> custom logic.
"Custom" logic that's defined by the SQL Standard. :-)
Dan.
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Francois Orsini <fr...@gmail.com>.
This is highly arguable - you say SQL is ugly as it is (which is arguable by
itself ;)) but then you think it's ok to add a non-standard UNSIGNED keyword
if we want the unsigned version which has been there for more than 15 years
at least in very well known RDBMS out there ;)
Either way is fine really (as long both semantics end-up being supported at
the end)...It's a question of views and priorities from different people
having different niches ;)
--francois
If we end-up supporting both semantics,
On 11/14/05, Dag H. Wanvik <Da...@sun.com> wrote:
>
>
> Hi,
> >>>>> "Francois" == Francois Orsini <fr...@gmail.com> wrote:
>
> Francois> Dan's argument which is mine too I believe is in respect with
> users
> Francois> migrating from Sybase/MS SQL Server apps using TINYINT to Derby
> - if we
> Francois> provide an unsigned type by default then they don't have
> anything to change
> Francois> in respect with that type (same semantics) - MySQL has support
> for both
> Francois> SIGNED and UNSIGNED so what not have (unsigned) TINYINT
> supported in Derby
> Francois> by default and encompass a wider range of databases supporting
> (unsigned)
> Francois> TINYINT which in the end will ease migration and help Derby's
> adoption...
>
> So it would seem this boils down to which consideration is more
> important; semantic consistency (=>TINYINT is by default signed, since
> other integer types are signed in Derby), or ease of portability to
> Derby (=> TINYINT is unsigned by default, since 2 major players use
> that).
>
> I would argue that a single byte data type is useful in its own right,
> and will favor signed semantics as the default - to keep things clean,
> SQL is ugly enough as it is.. If we want the unsigned version
> also, I would vote for the UNSIGNED keyword, the type is non-standard
> syntax in SQL anyway.
>
> Disclaimer: I guess I should add I am a little biased against unsigned
> integer types in general, I have seen far too many bugs due to
> indiscriminate mixing of signed and unsigned types over the years ;-)
>
> Thanks,
> Dag
>
>
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Hi,
>>>>> "Francois" == Francois Orsini <fr...@gmail.com> wrote:
Francois> Dan's argument which is mine too I believe is in respect with users
Francois> migrating from Sybase/MS SQL Server apps using TINYINT to Derby - if we
Francois> provide an unsigned type by default then they don't have anything to change
Francois> in respect with that type (same semantics) - MySQL has support for both
Francois> SIGNED and UNSIGNED so what not have (unsigned) TINYINT supported in Derby
Francois> by default and encompass a wider range of databases supporting (unsigned)
Francois> TINYINT which in the end will ease migration and help Derby's adoption...
So it would seem this boils down to which consideration is more
important; semantic consistency (=>TINYINT is by default signed, since
other integer types are signed in Derby), or ease of portability to
Derby (=> TINYINT is unsigned by default, since 2 major players use
that).
I would argue that a single byte data type is useful in its own right,
and will favor signed semantics as the default - to keep things clean,
SQL is ugly enough as it is.. If we want the unsigned version
also, I would vote for the UNSIGNED keyword, the type is non-standard
syntax in SQL anyway.
Disclaimer: I guess I should add I am a little biased against unsigned
integer types in general, I have seen far too many bugs due to
indiscriminate mixing of signed and unsigned types over the years ;-)
Thanks,
Dag
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Francois Orsini <fr...@gmail.com>.
On 11/14/05, Rick Hillegas <Ri...@sun.com> wrote:
>
> Daniel John Debrunner wrote:
>
> >David W. Van Couvering wrote:
> >
> >
> >
> >>I thought Rick's suggestion of adding the "UNSIGNED" keyword was a good
> >>solution -- we can get the best of both worlds...
> >>
> >>
> >
> >So more non-standard syntax? Why is it better for a SQL Server/Sybase
> >application to change their types to TINYINT UNSIGNED, instead of
> >SMALLINT?
> >
> Some customers may not care if they have to double the column size.
> Others may care.
Dan's argument which is mine too I believe is in respect with users
migrating from Sybase/MS SQL Server apps using TINYINT to Derby - if we
provide an unsigned type by default then they don't have anything to change
in respect with that type (same semantics) - MySQL has support for both
SIGNED and UNSIGNED so what not have (unsigned) TINYINT supported in Derby
by default and encompass a wider range of databases supporting (unsigned)
TINYINT which in the end will ease migration and help Derby's adoption...
>Or even SMALLINT with a check constraint to limit the range.
> >
> >Dan.
> >
> >
> >
> You can also implement NOT NULL as a check contraint. Most databases
> consider that to be an inefficiency which it's worth optimizing with
> custom logic.
>
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Rick Hillegas <Ri...@Sun.COM>.
Daniel John Debrunner wrote:
>David W. Van Couvering wrote:
>
>
>
>>I thought Rick's suggestion of adding the "UNSIGNED" keyword was a good
>>solution -- we can get the best of both worlds...
>>
>>
>
>So more non-standard syntax? Why is it better for a SQL Server/Sybase
>application to change their types to TINYINT UNSIGNED, instead of
>SMALLINT?
>
Some customers may not care if they have to double the column size.
Others may care.
>Or even SMALLINT with a check constraint to limit the range.
>
>Dan.
>
>
>
You can also implement NOT NULL as a check contraint. Most databases
consider that to be an inefficiency which it's worth optimizing with
custom logic.
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Daniel John Debrunner <dj...@debrunners.com>.
David W. Van Couvering wrote:
> I thought Rick's suggestion of adding the "UNSIGNED" keyword was a good
> solution -- we can get the best of both worlds...
So more non-standard syntax? Why is it better for a SQL Server/Sybase
application to change their types to TINYINT UNSIGNED, instead of
SMALLINT? Or even SMALLINT with a check constraint to limit the range.
Dan.
>
> David
>
> Francois Orsini wrote:
>
>> Since Sybase, MySQL and MS SQL Server have had support for UNSIGNED
>> TINYINT for many years (at least for 2 of them), offering support for
>> an UNSIGNED TINYINT rather than SIGNED at this point makes more sense
>> and can only be good for Derby's adoption (and that a sufficient
>> reason for adding it IMHO) (SIGNED TINYINT could always be enabled
>> later _if_ required but JDBC does not require the type to be signed in
>> the first place) - it brings value for getting Derby more adopted from
>> users looking to migrate from other known and popular RDBMS (not just
>> from the ones which got most market shares)...and as far as the
>> footprint as previously mentioned, it is good to offer support for a
>> 1-Byte datatype which does matter indeed when running in a
>> small-device environment.
>>
>> --francois
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by "David W. Van Couvering" <Da...@Sun.COM>.
I thought Rick's suggestion of adding the "UNSIGNED" keyword was a good
solution -- we can get the best of both worlds...
David
Francois Orsini wrote:
> Since Sybase, MySQL and MS SQL Server have had support for UNSIGNED
> TINYINT for many years (at least for 2 of them), offering support for an
> UNSIGNED TINYINT rather than SIGNED at this point makes more sense and
> can only be good for Derby's adoption (and that a sufficient reason for
> adding it IMHO) (SIGNED TINYINT could always be enabled later _if_
> required but JDBC does not require the type to be signed in the first
> place) - it brings value for getting Derby more adopted from users
> looking to migrate from other known and popular RDBMS (not just from the
> ones which got most market shares)...and as far as the footprint as
> previously mentioned, it is good to offer support for a 1-Byte datatype
> which does matter indeed when running in a small-device environment.
>
> --francois
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
+1. Original argument of ease of migration from Sybase, Microsoft
servers goes for a toss with a SIGNED implementation. There are too many
issues with the current proposal. I am for making this follow closer to
current implementations, if all.
Satheesh
Francois Orsini wrote:
> Since Sybase, MySQL and MS SQL Server have had support for UNSIGNED
> TINYINT for many years (at least for 2 of them), offering support for
> an UNSIGNED TINYINT rather than SIGNED at this point makes more sense
> and can only be good for Derby's adoption (and that a sufficient
> reason for adding it IMHO) (SIGNED TINYINT could always be enabled
> later _if_ required but JDBC does not require the type to be signed in
> the first place) - it brings value for getting Derby more adopted from
> users looking to migrate from other known and popular RDBMS (not just
> from the ones which got most market shares)...and as far as the
> footprint as previously mentioned, it is good to offer support for a
> 1-Byte datatype which does matter indeed when running in a
> small-device environment.
>
> --francois
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Francois Orsini <fr...@gmail.com>.
Since Sybase, MySQL and MS SQL Server have had support for UNSIGNED TINYINT
for many years (at least for 2 of them), offering support for an UNSIGNED
TINYINT rather than SIGNED at this point makes more sense and can only be
good for Derby's adoption (and that a sufficient reason for adding it IMHO)
(SIGNED TINYINT could always be enabled later _if_ required but JDBC does
not require the type to be signed in the first place) - it brings value for
getting Derby more adopted from users looking to migrate from other known
and popular RDBMS (not just from the ones which got most market
shares)...and as far as the footprint as previously mentioned, it is good to
offer support for a 1-Byte datatype which does matter indeed when running in
a small-device environment.
--francois
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by "Lance J. Andersen" <La...@Sun.COM>.
Daniel John Debrunner wrote:
>Rick Hillegas wrote:
>
>
>
>>I logged this enhancment request because it seemed that re-enabling
>>TINYINT would be low hanging fruit.
>>
>>
>
>I believe how hard or easy an item is has no relevance to if it should
>be included in Derby. The criteria should be, is it good for Derby given
>Derby's charter.
>
>
>
>>Furthermore, to me TINYINT should be
>>a signed integer just like its bigger sisters SMALLINT, INTEGER, and
>>LONG. It makes sense to me that the premier Java database should have
>>datatypes which correspond as exactly as possible with the Java
>>datatypes. This would be the most natural fit for persisting the data
>>from Java applications, whether flattened by hand or by off-the-shelf
>>O-R mapping technologies.
>>
>>
>
>OK, I was misled by the comment in DERBY-695 that TINYINT was a good
>thing because other databases supported it. If that's not the driving
>factor, then it's different proposal.
>
>
>
>>Currently, we are missing two of these Java datatypes: boolean
>>(java.sql.BOOLEAN) and byte (java.sql.TINYINT).
>>
>>Exposing a signed TINYINT does not violate any standard we care about:
>>ANSI is silent on the matter, and JDBC is compatible with this
>>interpretation. A signed TINYINT would be an easy, natural, and useful
>>addition for the Java applications which are our bread-and-butter
>>customers.
>>
>>
>
>How much demand for this type, as you've described is there? Are many
>Java applications using byte for fields? My guess would have been that
>any requests for TINYINT would have been due to existing database
>applications.
>
>
Well, certainly MySQL added long after Sybase, so i am sure there are
some apps using it.
>One potential issue is that we are creating a non-standard datatype that
>is in conflict with the existing type of the same name by SQL Server and
>Sybase. Will this cause more problems for users of Derby as they try to
>migrate off those databases? Of course in some ways it's not much better
>if we go 0-255, as then it's in conflict with MySQL. Though I would be
>interested to know if the new strict flag in MySQL has any effect on
>TINYINT.
>
>
There are range issues across many databases with datatypes. Again, you
have to know the range of the datatype on the backend to make sure it
works for you.
>So, does adding this datatype solve more problems than it creates? How
>does it compare to having the JDBC drivers map JDBC type TINYINT to SQL
>Type SMALLINT?
>
>
So map it to an existing datatype then such as SMALLINT, just allow for
the DDL to be processed when defined as a TINYINT.
>Again, don't take this as an attack on TINYINT, I'm just trying to
>ensure we see all the issues.
>
>
Understand, but i am not sure what else can be said about this datatype.
So how do we close on this issue as we seem to be not moving forward?
>Dan.
>
>
>
>
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Rick Hillegas <Ri...@Sun.COM>.
Daniel John Debrunner wrote:
...
>How much demand for this type, as you've described is there? Are many
>Java applications using byte for fields? My guess would have been that
>any requests for TINYINT would have been due to existing database
>applications.
>
>
I think Lance is right: we can see demand for this type in the fact that
a relatively new database like MySQL bothered to add it.
>One potential issue is that we are creating a non-standard datatype that
>is in conflict with the existing type of the same name by SQL Server and
>Sybase. Will this cause more problems for users of Derby as they try to
>migrate off those databases? Of course in some ways it's not much better
>if we go 0-255, as then it's in conflict with MySQL. Though I would be
>interested to know if the new strict flag in MySQL has any effect on
>TINYINT.
>
>
Right, we need both signed and unsigned bytes to ease all of these
migration paths. That's the appeal of starting out with a signed TINYINT
and adding the UNSIGNED keyword later on.
Cheers,
-Rick
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Rick Hillegas wrote:
> I logged this enhancment request because it seemed that re-enabling
> TINYINT would be low hanging fruit.
I believe how hard or easy an item is has no relevance to if it should
be included in Derby. The criteria should be, is it good for Derby given
Derby's charter.
> Furthermore, to me TINYINT should be
> a signed integer just like its bigger sisters SMALLINT, INTEGER, and
> LONG. It makes sense to me that the premier Java database should have
> datatypes which correspond as exactly as possible with the Java
> datatypes. This would be the most natural fit for persisting the data
> from Java applications, whether flattened by hand or by off-the-shelf
> O-R mapping technologies.
OK, I was misled by the comment in DERBY-695 that TINYINT was a good
thing because other databases supported it. If that's not the driving
factor, then it's different proposal.
> Currently, we are missing two of these Java datatypes: boolean
> (java.sql.BOOLEAN) and byte (java.sql.TINYINT).
>
> Exposing a signed TINYINT does not violate any standard we care about:
> ANSI is silent on the matter, and JDBC is compatible with this
> interpretation. A signed TINYINT would be an easy, natural, and useful
> addition for the Java applications which are our bread-and-butter
> customers.
How much demand for this type, as you've described is there? Are many
Java applications using byte for fields? My guess would have been that
any requests for TINYINT would have been due to existing database
applications.
One potential issue is that we are creating a non-standard datatype that
is in conflict with the existing type of the same name by SQL Server and
Sybase. Will this cause more problems for users of Derby as they try to
migrate off those databases? Of course in some ways it's not much better
if we go 0-255, as then it's in conflict with MySQL. Though I would be
interested to know if the new strict flag in MySQL has any effect on
TINYINT.
So, does adding this datatype solve more problems than it creates? How
does it compare to having the JDBC drivers map JDBC type TINYINT to SQL
Type SMALLINT?
Again, don't take this as an attack on TINYINT, I'm just trying to
ensure we see all the issues.
Dan.
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Rick Hillegas <Ri...@Sun.COM>.
At this point, I think we have agreed on the facts about how JDBC and
various SQL databases treat the TINYINT type. I think I would not be
going out on a limb if I said that there is no industry-wide consensus
about whether TINYINT is a signed or unsigned quantity.
I logged this enhancment request because it seemed that re-enabling
TINYINT would be low hanging fruit. Furthermore, to me TINYINT should be
a signed integer just like its bigger sisters SMALLINT, INTEGER, and
LONG. It makes sense to me that the premier Java database should have
datatypes which correspond as exactly as possible with the Java
datatypes. This would be the most natural fit for persisting the data
from Java applications, whether flattened by hand or by off-the-shelf
O-R mapping technologies.
Currently, we are missing two of these Java datatypes: boolean
(java.sql.BOOLEAN) and byte (java.sql.TINYINT).
Exposing a signed TINYINT does not violate any standard we care about:
ANSI is silent on the matter, and JDBC is compatible with this
interpretation. A signed TINYINT would be an easy, natural, and useful
addition for the Java applications which are our bread-and-butter customers.
I agree that in the future, to support migration from MySQL, we should
consider implementing the UNSIGNED keyword, allowing all of our integer
datatypes to support their unsigned ranges. That, however, is some other
itch to scratch.
Cheers,
-Rick
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Dan,
If I said somewhere that Postgres supports TINYINT, then that was a big,
old lie. Perhaps my brain short-circuited and I meant the other open
source database I care about: MySQL. As you note, MySQL supports a
TINYINT datatype, intended to fit in a byte. I'm looking at a copy of
the MySQL Reference Manual version 5.0,3-alpha. Section 11.1.1 of that
manual gives both signed and unsigned ranges of the integer types.
Section 11.2 says that when declaring these types, you can specify an
extra UNSIGNED keyword to pick the unsigned range.
Sybase and Microsoft SQL Server, as you note, support the unsigned range
for TINYINT. It's interesting that all of the other integer types are
signed quantities. See
http://gsbwww.uchicago.edu/computing/research/SASManual/accdb/z0439559.htm
and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_3ss4.asp.
So as you say, among database vendors TINYINT's range is not
standardized. This underscores Lance's point about the JDBC verbiage.
Cheers,
-Rick
Daniel John Debrunner wrote:
>Rick Hillegas wrote:
>
>
>
>>>I see this in section 8.3.4
>>>
>>>"The JDBC type TINYINT represents an 8-bit integer value between 0 and
>>>255 that may be signed or unsigned."
>>>
>>>
>>>
>>>
>>Hi Dan,
>>
>>I don't mean to pile up on you after your jet lag. Welcome back!
>>
>>
>
>I wasn't back, it's that three day in jet-lag, but thanks anyway!
>I'm back now though. :-)
>
>
>
>>Yeah, that's the same sentence I saw. It's a bit oddly worded. The
>>"8-bit integer value" makes it sound like a byte. But "value between 0
>>and 255 that may be signed or unsigned" could mean a "value between
>>-256 and 255", that is, a 9-bit quantity. I'm putting my faith in the
>>8-bit part.
>>
>>
>
>So it seems there would be two choices:
>
>-128 to 127 :
>
>+ matches JDBC 3.0 mapping to a byte (tables B.1/B.2)
>- does not match SQL Server, Sybase
>
>0 to 255 :
>
>+ matches SQL Server, Sybase
>- does not match JDBC 3.0 mapping to a byte
>
>Hmmmm, that's kind of an awkward place to be in. It seems you want to
>add this non-standard SQL type to match other databases, but by matching
>the other databases you break the JDBC spec. :-)
>
>MySQL seems to claim both ranges, but I didn't look to see how you
>specifiy signed vs. unsigned. Given MySQL's type handling in other areas
>I'm not sure they a a good "reference" implementation.
>
>Rick, you also, I think, said in one e-mail that Postgres supports
>TINYINT, I've only been able to find information that Postgres does not
>support TINYINT, do you have a link?
>
>Dan.
>
>
>
>
>
>
>
>
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by "Lance J. Andersen" <La...@Sun.COM>.
Daniel John Debrunner wrote:
>Lance J. Andersen wrote:
>
>
>
>>>Hmmmm, that's kind of an awkward place to be in. It seems you want to
>>>add this non-standard SQL type to match other databases, but by matching
>>>the other databases you break the JDBC spec. :-)
>>>
>>>
>>>
>>>
>>I do not see how this is going to break anything. The JDBC spec
>>indicates should, not "must".
>>
>>
>>
>
>
>
>>So i guess i am not sure of your concern dan. This datatype has worked
>>fine for drivers which have supported Sybase and MS SQL server for years.
>>
>>
>
>Maybe break was too strong, how about 'has the potential to confuse
>application developers'. :-)
>
>Just looking at the various tables in appendix B, the natural choice for
>an application developer to use is byte, setByte, getByte. Given the
>mappings in B1 & B2, and the big X in the getByte-TINYINT cell of B-6.
>
>
Understand but this is also somewhat of a guide as not every database
supports the datatypes listed in the list so it is not just a straight
answer.
JDBC is really just plumbing so unfortunately the user has to know the
requirements of the backend.
>Thus they may find their application works fine, because they only test
>it with values in the range 0-128, but later hit bugs in the field when
>larger values are used.
>
>
True, but this is not the only scenario where this could happen due to
database compatiblity issues
>If the JDBC TINYINT type was really put there to match the TINYINT SQL
>datatype of SQL Server and Sybase, then maybe JDBC 4.0 should clarify
>this so that the type mapping in B1 and B2 is TINYINT <-> short and B6
>has the big X in getShort-TINYINT cell?
>
>
I will put this on my todo list to have the fun EG discussion, but i am
afraid this will open up pandoras box about all data types that are not
explictly supported.
>Dan.
>
>
>
>
>
>
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Lance J. Andersen wrote:
>>Hmmmm, that's kind of an awkward place to be in. It seems you want to
>>add this non-standard SQL type to match other databases, but by matching
>>the other databases you break the JDBC spec. :-)
>>
>>
> I do not see how this is going to break anything. The JDBC spec
> indicates should, not "must".
>
> So i guess i am not sure of your concern dan. This datatype has worked
> fine for drivers which have supported Sybase and MS SQL server for years.
Maybe break was too strong, how about 'has the potential to confuse
application developers'. :-)
Just looking at the various tables in appendix B, the natural choice for
an application developer to use is byte, setByte, getByte. Given the
mappings in B1 & B2, and the big X in the getByte-TINYINT cell of B-6.
Thus they may find their application works fine, because they only test
it with values in the range 0-128, but later hit bugs in the field when
larger values are used.
If the JDBC TINYINT type was really put there to match the TINYINT SQL
datatype of SQL Server and Sybase, then maybe JDBC 4.0 should clarify
this so that the type mapping in B1 and B2 is TINYINT <-> short and B6
has the big X in getShort-TINYINT cell?
Dan.
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by "Lance J. Andersen" <La...@Sun.COM>.
Daniel John Debrunner wrote:
>David W. Van Couvering wrote:
>
>
>
>>As I understand it the value of TINYINT is:
>>
>>- Enables of migration of applications to Derby
>>- Allows for better use of storage (which goes in line with our "small
>>footprint" goal)
>>
>>The reason against it is it is a non-standard SQL type. But don't we
>>already have things in Derby that are not part of the SQL standard?
>>
>>
>
>I think people are just pointing out all aspects and implications of
>adding such a type. Adding something that is not in the standard is
>something that should be considered carefully, and considered on a
>per-case basis.
>
>SYNONYM is an example of something that was added to Derby that is not
>in the SQL standard, but in that case there is a clearer de-facto
>standard, it's supported by most databases and it provides some new
>useful functionality.
>
>TINYINT (in my mind) is more borderline, it's not supported by a lot of
>databases, and not by the databases that hold #1 and #2 in marketshare.
>Thus I see all the points of view being very useful to leading to a
>decision.
>
>
Yes, but they are not the only players in the database market and it
also depends on which marketshare you are looking at. Microsoft has a
very large install base and ASA owns over 60% of the embedded
marketplace (they also support TINYINT). Even though MySQL has a
different range, it also supports the data type.
I also could not find any documentation which indicates that Postgresql
supports this datatype.
If we soley pick and choose just based on Oracle and DB2 i think that is
a mistake IMHO
>Dan.
>
>
>
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Daniel John Debrunner <dj...@debrunners.com>.
David W. Van Couvering wrote:
> As I understand it the value of TINYINT is:
>
> - Enables of migration of applications to Derby
> - Allows for better use of storage (which goes in line with our "small
> footprint" goal)
>
> The reason against it is it is a non-standard SQL type. But don't we
> already have things in Derby that are not part of the SQL standard?
I think people are just pointing out all aspects and implications of
adding such a type. Adding something that is not in the standard is
something that should be considered carefully, and considered on a
per-case basis.
SYNONYM is an example of something that was added to Derby that is not
in the SQL standard, but in that case there is a clearer de-facto
standard, it's supported by most databases and it provides some new
useful functionality.
TINYINT (in my mind) is more borderline, it's not supported by a lot of
databases, and not by the databases that hold #1 and #2 in marketshare.
Thus I see all the points of view being very useful to leading to a
decision.
As an example, I think Rick was originally proposing TINYINT as
Cloudscape used to support it. That might have seemed reasonable but the
discussion has thrown up that such an implementation would not make
sense. Derby would have a TINYINT that was incompatble with the two of
the three databases that support it, while saying TINYINT is being added
to be compatible with those databases.
The the mismatch with JDBC is interesting (short vs. byte) and useful
information that would need to be documented.
Dan.
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by "David W. Van Couvering" <Da...@Sun.COM>.
As I understand it the value of TINYINT is:
- Enables of migration of applications to Derby
- Allows for better use of storage (which goes in line with our "small
footprint" goal)
The reason against it is it is a non-standard SQL type. But don't we
already have things in Derby that are not part of the SQL standard?
Thanks,
David
Lance J. Andersen wrote:
>
>
>>
>>
>>>Yeah, that's the same sentence I saw. It's a bit oddly worded. The
>>>"8-bit integer value" makes it sound like a byte. But "value between 0
>>>and 255 that may be signed or unsigned" could mean a "value between
>>>-256 and 255", that is, a 9-bit quantity. I'm putting my faith in the
>>>8-bit part.
>>>
>>>
>>
>>So it seems there would be two choices:
>>
>>-128 to 127 :
>>
>>+ matches JDBC 3.0 mapping to a byte (tables B.1/B.2)
>>- does not match SQL Server, Sybase
>>
>>0 to 255 :
>>
>>+ matches SQL Server, Sybase
>>- does not match JDBC 3.0 mapping to a byte
>>
>>Hmmmm, that's kind of an awkward place to be in. It seems you want to
>>add this non-standard SQL type to match other databases, but by matching
>>the other databases you break the JDBC spec. :-)
>>
>>
> I do not see how this is going to break anything. The JDBC spec
> indicates should, not "must".
>
> As the other doc points out you could use either a short or a byte based
> on your needs.
>
>
> setbyte() will work if the value is in range for the database which
> means it has to be non-negative for TINYINT in the case of Sybase.
>
>
>
> setShort() will also work for a TINYINT as long as the value is in range.
>
>
> So i guess i am not sure of your concern dan. This datatype has worked
> fine for drivers which have supported Sybase and MS SQL server for years.
>
> Assuming there is agreement to add TINYINT, i would add it to be
> compatible with Sybase and MS SQL Server.
>
>
>>MySQL seems to claim both ranges, but I didn't look to see how you
>>specifiy signed vs. unsigned. Given MySQL's type handling in other areas
>>I'm not sure they a a good "reference" implementation.
>>
>>Rick, you also, I think, said in one e-mail that Postgres supports
>>TINYINT, I've only been able to find information that Postgres does not
>>support TINYINT, do you have a link?
>>
>>Dan.
>>
>>
>>
>>
>>
>>
>>
>>
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by "Lance J. Andersen" <La...@Sun.COM>.
>
>
>>Yeah, that's the same sentence I saw. It's a bit oddly worded. The
>>"8-bit integer value" makes it sound like a byte. But "value between 0
>>and 255 that may be signed or unsigned" could mean a "value between
>>-256 and 255", that is, a 9-bit quantity. I'm putting my faith in the
>>8-bit part.
>>
>>
>
>So it seems there would be two choices:
>
>-128 to 127 :
>
>+ matches JDBC 3.0 mapping to a byte (tables B.1/B.2)
>- does not match SQL Server, Sybase
>
>0 to 255 :
>
>+ matches SQL Server, Sybase
>- does not match JDBC 3.0 mapping to a byte
>
>Hmmmm, that's kind of an awkward place to be in. It seems you want to
>add this non-standard SQL type to match other databases, but by matching
>the other databases you break the JDBC spec. :-)
>
>
I do not see how this is going to break anything. The JDBC spec
indicates should, not "must".
As the other doc points out you could use either a short or a byte based
on your needs.
setbyte() will work if the value is in range for the database which
means it has to be non-negative for TINYINT in the case of Sybase.
setShort() will also work for a TINYINT as long as the value is in range.
So i guess i am not sure of your concern dan. This datatype has worked
fine for drivers which have supported Sybase and MS SQL server for years.
Assuming there is agreement to add TINYINT, i would add it to be
compatible with Sybase and MS SQL Server.
>MySQL seems to claim both ranges, but I didn't look to see how you
>specifiy signed vs. unsigned. Given MySQL's type handling in other areas
>I'm not sure they a a good "reference" implementation.
>
>Rick, you also, I think, said in one e-mail that Postgres supports
>TINYINT, I've only been able to find information that Postgres does not
>support TINYINT, do you have a link?
>
>Dan.
>
>
>
>
>
>
>
>
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Rick Hillegas wrote:
>
>> I see this in section 8.3.4
>>
>> "The JDBC type TINYINT represents an 8-bit integer value between 0 and
>> 255 that may be signed or unsigned."
>>
>>
> Hi Dan,
>
> I don't mean to pile up on you after your jet lag. Welcome back!
I wasn't back, it's that three day in jet-lag, but thanks anyway!
I'm back now though. :-)
> Yeah, that's the same sentence I saw. It's a bit oddly worded. The
> "8-bit integer value" makes it sound like a byte. But "value between 0
> and 255 that may be signed or unsigned" could mean a "value between
> -256 and 255", that is, a 9-bit quantity. I'm putting my faith in the
> 8-bit part.
So it seems there would be two choices:
-128 to 127 :
+ matches JDBC 3.0 mapping to a byte (tables B.1/B.2)
- does not match SQL Server, Sybase
0 to 255 :
+ matches SQL Server, Sybase
- does not match JDBC 3.0 mapping to a byte
Hmmmm, that's kind of an awkward place to be in. It seems you want to
add this non-standard SQL type to match other databases, but by matching
the other databases you break the JDBC spec. :-)
MySQL seems to claim both ranges, but I didn't look to see how you
specifiy signed vs. unsigned. Given MySQL's type handling in other areas
I'm not sure they a a good "reference" implementation.
Rick, you also, I think, said in one e-mail that Postgres supports
TINYINT, I've only been able to find information that Postgres does not
support TINYINT, do you have a link?
Dan.
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Rick Hillegas <Ri...@Sun.COM>.
>I see this in section 8.3.4
>
>"The JDBC type TINYINT represents an 8-bit integer value between 0 and
>255 that may be signed or unsigned."
>
>
Hi Dan,
I don't mean to pile up on you after your jet lag. Welcome back!
Yeah, that's the same sentence I saw. It's a bit oddly worded. The
"8-bit integer value" makes it sound like a byte. But "value between 0
and 255 that may be signed or unsigned" could mean a "value between
-256 and 255", that is, a 9-bit quantity. I'm putting my faith in the
8-bit part.
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by "Lance J. Andersen" <La...@Sun.COM>.
Satheesh Bandaram wrote:
> What does this mean? Section 8.3.4 at
> http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html.
The JDBC TINYINT is intended to represent the datatype on the backend
which in the databases i am familiar with is a range 0-255 such as
Sybase and MS SQL Server where I believe this type originated. This
section of the tutorial is a guideline for an attempted best match for
the types it would map to on the backend
Regards,
Lance
> "The recommended Java mapping for the *JDBC |TINYINT|* type is as
> either a *Java |byte|* or a *Java |short|*. The 8-bit Java |byte| type
> represents a signed value from |-128| to |127|, so *it may not always
> be appropriate* for larger |TINYINT| values, whereas the *16-bit Java
> |short| will always be able* to hold all |TINYINT| values."
>
> This seems to infer JDBC TINYINT range of 0-255.
>
> Satheesh
>
> Lance J. Andersen wrote:
>
>> Nope the only section which discusses this is Appendix B which
>> indicates the equivalent Java type is a byte.
>>
>> -lance
>>
>>>But I'm tired and jet-lagged ...
>>>
>>>Dan.
>>>
>>>
>>>
>>>
>>>
>>>
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by "Lance J. Andersen" <La...@Sun.COM>.
>>8-bit integer.
>>
>>
>
>I see this in section 8.3.4
>
>"The JDBC type TINYINT represents an 8-bit integer value between 0 and
>255 that may be signed or unsigned."
>
>I don't see anything in that section or elsewhere about "fits in a
>byte", in fact I see:
>
>"The 8-bit Java byte type represents a signed value from -128 to 127, so
>it may not always be appropriate for larger TINYINT values"
>
>Maybe you could read the first quote as unsigned 0-255, and signed
>-128-127 but it seems a strange way of saying that.
>
>Maybe there's a better definition somewhere, that's just the first I
>found, since it doesn't seem to be mentioned in JDBC 3.0 in any
>meaningful way.
>
>
Nope the only section which discusses this is Appendix B which indicates
the equivalent Java type is a byte.
-lance
>
>But I'm tired and jet-lagged ...
>
>Dan.
>
>
>
>
>
>
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Rick Hillegas wrote:
>
>> http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html
>>
>> This mapping is reflected in the data returned by
>> DatabaseMetaData.getTypeInfo
>>
>> On an implementation note, the link above says TINYINT is valued 0-255
>> signed or unsigned, I do not believe that is what was implemented by
>> Cloudscape, or any existing code in Derby. I seem to remember
>> Cloudscape used a Java byte which holds values -128 to 127, thus its a
>> good thing the TINYINT type was removed/disabled because it was
>> non-standard on several fronts.
>>
>>
> I'm afraid I don't understand. The above link says that a JDBC TINYINT
> is an 8-bit integer which fits in a byte and may be regarded as signed
> or unsigned. And you say that Cloudscape implemented this as a byte. I'm
> missing the discrepancy here. For the record, Postgres, SQL Server, and
> Sybase all treat TINYINT as an 8-bit integer.
I see this in section 8.3.4
"The JDBC type TINYINT represents an 8-bit integer value between 0 and
255 that may be signed or unsigned."
I don't see anything in that section or elsewhere about "fits in a
byte", in fact I see:
"The 8-bit Java byte type represents a signed value from -128 to 127, so
it may not always be appropriate for larger TINYINT values"
Maybe you could read the first quote as unsigned 0-255, and signed
-128-127 but it seems a strange way of saying that.
Maybe there's a better definition somewhere, that's just the first I
found, since it doesn't seem to be mentioned in JDBC 3.0 in any
meaningful way.
But I'm tired and jet-lagged ...
Dan.
Re: [jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by Rick Hillegas <Ri...@Sun.COM>.
>http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html
>
>This mapping is reflected in the data returned by DatabaseMetaData.getTypeInfo
>
>On an implementation note, the link above says TINYINT is valued 0-255 signed or unsigned, I do not believe that is what was implemented by Cloudscape, or any existing code in Derby. I seem to remember Cloudscape used a Java byte which holds values -128 to 127, thus its a good thing the TINYINT type was removed/disabled because it was non-standard on several fronts.
>
>
I'm afraid I don't understand. The above link says that a JDBC TINYINT
is an 8-bit integer which fits in a byte and may be regarded as signed
or unsigned. And you say that Cloudscape implemented this as a byte. I'm
missing the discrepancy here. For the record, Postgres, SQL Server, and
Sybase all treat TINYINT as an 8-bit integer.
[jira] Commented: (DERBY-695) Re-enable the TINYINT datatype
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-695?page=comments#action_12357163 ]
Daniel John Debrunner commented on DERBY-695:
---------------------------------------------
We should be clear that the JDBC spec defines *JDBC Types* and the SQL standard defines *SQL types*.
Thus they are not really in conflict, here, just defining types in different domains.
A JDBC driver *implementation* defines how it maps SQL types to JDBC types, I believe there is nothing violated by either standard by having a JDBC Type TINYINT being mapped to the SQL type SMALLINT for example.
Informix 9.2 according to this JDBC guide maps SMALLINT to TINYINT.
http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html
This mapping is reflected in the data returned by DatabaseMetaData.getTypeInfo
On an implementation note, the link above says TINYINT is valued 0-255 signed or unsigned, I do not believe that is what was implemented by Cloudscape, or any existing code in Derby. I seem to remember Cloudscape used a Java byte which holds values -128 to 127, thus its a good thing the TINYINT type was removed/disabled because it was non-standard on several fronts.
> Re-enable the TINYINT datatype
> ------------------------------
>
> Key: DERBY-695
> URL: http://issues.apache.org/jira/browse/DERBY-695
> Project: Derby
> Type: New Feature
> Reporter: Rick Hillegas
>
> I would like to collect here the arguments for and against re-enabling the TINYINT datatype. Once this discussion calms down, we can schedule a vote on the issue.
> Background: Cloudscape used to support the TINYINT datatype, which was an 8 bit int. This datatype was hidden from customers as part of an effort to remove all datatypes not supported by DB2. Re-enabling the datatype would not require a lot of effort. Some arguments for and against re-enabling this datatype can be found on the November 2005 email thread titled "New features for next release .... (Was: Grant and Revoke ... DERBY-464...)".
> Here are the arguments in favor so far:
> + This datatype is defined by one of our key standards, JDBC. It is in JDBC 2, 3, and 4, all of the JDBC revs supported by Derby 10.2.
> + This datatype is supported by some important databases, including MySQL, Microsoft SQL Server, and Sybase.
> Here are the arguments against so far:
> - This datatype is not defined by our other key standard, ANSI SQL. Here our two main standards diverge.
> - This datatype is not supported by some important databases, including Oracle, DB2, and (some) Informix databases.
> Against this proposal, it was also argued that there was some sort of friction with ODBC. I do not understand this argument: SQL_TINYINT is an ODBC datatype. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsql_data_types.asp.
> A friction with .NET was also suggested but I don't understand this either. "byte" and "Sbyte" are the .NET 8-bit integer types. See http://www.codersource.net/csharp_tutorial_data_types.html.
> A friction with Perl was also suggested but I don't understand this either.
--
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