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 Army <qo...@sbcglobal.net> on 2005/03/25 23:57:08 UTC
Questions about "getApproximateLengthInBytes()"
Hi,
I was looking yet again at some of Derby's metadata code, and I noticed that for the getProcedureColumns() method, the
"LENGTH" value of the result set is supposed to be "length in bytes" (per JDBC spec). However, the value as assigned in
the org.apache.derby.catalog.GetProcedureColumns class is really just the value of getMaximumWidth(), which doesn't seem
correct.
For example, a column defined as "CHAR(10)" has a max width of 10, but the length in bytes should be "20" since Java
uses two bytes per character. Thus the current Derby code will return the wrong value for LENGTH with this metadata call.
As I was looking into this problem, I found a method in TypeId.java called "getApproximateLengthInBytes()" that is
apprently supposed to return the length of a column in bytes--i.e. it's just what we need for the metadata. While
looking at the method, though, I had some questions, and I'm wondering if anyone out there knows any of the answers...
1) Why the "+ 2" in the following case?
case StoredFormatIds.CHAR_TYPE_ID:
case StoredFormatIds.NATIONAL_CHAR_TYPE_ID:
return (2 * dts.getMaximumWidth()) + 2;
2) Why the value "200" in this case? It's used throughout the method whenever Integer.MAX_VALUE is encountered--is
there a reason we use the specific value of 200? What does it mean, and how/where is it interpreted by Derby? Also,
why the "8 +" in the else clause?
case StoredFormatIds.DECIMAL_TYPE_ID:
// Return 200 if precision is max int
if (dts.getPrecision() == Integer.MAX_VALUE)
{
return 200;
}
else
{
return 8 + (int) (Math.ceil(((double)dts.getPrecision())/2d));
}
3) Why the "10240" in the follwowing case? What does it mean, and how/where is it interpreted by Derby?
case StoredFormatIds.LONGVARBIT_TYPE_ID:
case StoredFormatIds.BLOB_TYPE_ID:
case StoredFormatIds.CLOB_TYPE_ID:
case StoredFormatIds.NCLOB_TYPE_ID:
return 10240;
4) For datetime values, there is the following code:
/*
** For Date/time we know the exact size
** thanks to some investigative work by
** someone or other (sad isn't it).
*/
case StoredFormatIds.DATE_TYPE_ID:
return 18;
case StoredFormatIds.TIME_TYPE_ID:
return 16;
case StoredFormatIds.TIMESTAMP_TYPE_ID:
return 29;
Does anyone know how these values were calculated? I.e. what "investigative work" led to these results? Are they still
correct?
5) Generally speaking, would it be acceptable to use this method for metadata LENGTH queries, instead of the current
(and incorrect) "getMaximumLength()" call?
Also, as a general question, are these values the same for Network Server? I.e. for a given type, does Network Server
transfer the same (approximate) number of bytes as reported by this method, or does Network Server do it's own thing? I
looked at the FdocaConstants class in Network Server and I noticed that the "outlen" field of the
"mapJdbcTypeToDrdaType" method matches the above numbers in most cases EXCEPT for the datetime values--so is there a
difference between the number of bytes used for datetime values in Embedded mode vs. that used/transferred in Network
Server mode?
Anyone know the answers to any of these questions?
Thanks much,
Army
Re: Questions about "getApproximateLengthInBytes()"
Posted by Jack Klebanoff <kl...@sbcglobal.net>.
Army wrote:
> RPost wrote:
>
>> I also noticed that the 'estimated MemoryUsage() method in
>> DataTypeDescriptor.java has similar, but in some cases different,
>> numbers.
>
>
> Thanks for the reply--and so the plot thickens. I did notice that
> values for date/time/timestamp are all 12 in the estimateMemoryUsage
> method, and that the phrase "I think" shows up once while "Who knows?"
> shows up three times, which makes me wonder about the accuracy there.
> On the other hand, the method "getApproximateLengthInBytes()" has the
> word "approximate" in it, which isn't exactly confidence-boosting,
> either. And since "getMaximumWidth()" (which is what is currently
> used for metadata LENGTH) isn't correct either, one is forced to
> wonder if metadata shouldn't perhaps be doing it's own thing entirely...?
>
> Hmmm,
> Army
>
>
The DataTypeDescriptor.estimated MemoryUsage() method estimates the
amount of RAM used by the data value. It is supposed to include of Java
object overhead and the space taken by pointers (references). It is
likely to be different than the amount of disk space used by the data value.
I am not sure whether DatabaseMetaData.getProcedureColumns() is supposed
to return the size on disk or the RAM size of the column. I would guess
that it is supposed to return the size on disk.
Jack
Re: Questions about "getApproximateLengthInBytes()"
Posted by Army <qo...@sbcglobal.net>.
RPost wrote:
> I also noticed that the 'estimated MemoryUsage() method in
> DataTypeDescriptor.java has similar, but in some cases different, numbers.
Thanks for the reply--and so the plot thickens. I did notice that values for date/time/timestamp are all 12 in the
estimateMemoryUsage method, and that the phrase "I think" shows up once while "Who knows?" shows up three times, which
makes me wonder about the accuracy there. On the other hand, the method "getApproximateLengthInBytes()" has the word
"approximate" in it, which isn't exactly confidence-boosting, either. And since "getMaximumWidth()" (which is what is
currently used for metadata LENGTH) isn't correct either, one is forced to wonder if metadata shouldn't perhaps be doing
it's own thing entirely...?
Hmmm,
Army
Re: Questions about "getApproximateLengthInBytes()"
Posted by RPost <rp...@pacbell.net>.
>"Army" wrote:
> Questions about "getApproximateLengthInBytes()"
I also noticed that the 'estimated MemoryUsage() method in
DataTypeDescriptor.java has similar, but in some cases different, numbers.
For example this method doesn't include the +2:
case StoredFormatIds.CHAR_TYPE_ID:
case StoredFormatIds.VARCHAR_TYPE_ID:
case StoredFormatIds.NATIONAL_CHAR_TYPE_ID:
case StoredFormatIds.NATIONAL_VARCHAR_TYPE_ID:
return (double) (2.0 * getMaximumWidth());
Both DataTypeDescriptor and TypeId list the author as Jeff Lichtman. Did any
of you work with him?
Re: Questions about "getApproximateLengthInBytes()"
Posted by "Lance J. Andersen" <La...@Sun.COM>.
I raised this as a clarification issue with the EG so that we can have
a discussion on this.
My hope is to close on this within the next week.
I am trying to clean-up issues like this for JDBC 4. However, some
issues might not get addressed until after the first EDR. I hope to
address as many gray areas as i can, time permitting before JDBC 4 goes
final.
Regards
Lance
Army wrote:
> Daniel John Debrunner wrote:
>
>> Now as Lance has pointed out then the JDBC metadata is meant to match
>> the ODBC metadata, so hopefully the complete defintion of what 'length'
>> means in each case can be found.
>
>
> Does this mean that, until JDBC defines something further, it's okay
> to just return the values as defined by the ODBC specification? If
> so, that'd certainly make my life easier (:), since ODBC nicely
> dictates what the values are all supposed to be in the Appendix
> mentioned by Lance:
>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbctransfer_octet_length.asp
>
>
> Can I just use those values, or is that taking the expression "meant
> to match the ODBC metadata" a bit too far?
>
>> Yes, and most likely getApproximateLengthInBytes() is not correct for
>> your use.
>
>
> Agreed.
>
>> Some cleanup is probably needed in Derby's internal datatypes api to
>> clearly define what each get length or get size method is defined to
>> return.
>
>
> Is this something that should be included in the BUFFER_LENGTH patch
> that I'm working on, or is this more of a "someone should do this
> someday" suggestion?
>
> Thanks for the feedback,
> Army
>
Re: Questions about "getApproximateLengthInBytes()"
Posted by Army <qo...@sbcglobal.net>.
Daniel John Debrunner wrote:
> Now as Lance has pointed out then the JDBC metadata is meant to match
> the ODBC metadata, so hopefully the complete defintion of what 'length'
> means in each case can be found.
Does this mean that, until JDBC defines something further, it's okay to just return the values as defined by the ODBC
specification? If so, that'd certainly make my life easier (:), since ODBC nicely dictates what the values are all
supposed to be in the Appendix mentioned by Lance:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbctransfer_octet_length.asp
Can I just use those values, or is that taking the expression "meant to match the ODBC metadata" a bit too far?
> Yes, and most likely getApproximateLengthInBytes() is not correct for
> your use.
Agreed.
> Some cleanup is probably needed in Derby's internal datatypes api to
> clearly define what each get length or get size method is defined to
> return.
Is this something that should be included in the BUFFER_LENGTH patch that I'm working on, or is this more of a "someone
should do this someday" suggestion?
Thanks for the feedback,
Army
Re: Questions about "getApproximateLengthInBytes()"
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Army wrote:
> My question is whether or not a declaration of maximum BYTE length for
> the Derby builtin types exists somewhere in the codeline, and if so, is
> it okay to use that for the metadata calls? And on that premise, I was
> looking at "getApproximateLengthInBytes" to see if that method has the
> info required (hence my initial email on this subject).
As I said in Derby-194, the definition of length needs to be precise,
length of what exactly. Since Derby is written in Java and its interface
is JDBC then 'length' as in the C sizeof operator is not applicable.
Take DATE as an example, with JDBC/Derby there is no way to convert a
DATE value to/from a byte array, so its length in bytes has no meaning.
Now as Lance has pointed out then the JDBC metadata is meant to match
the ODBC metadata, so hopefully the complete defintion of what 'length'
means in each case can be found.
> If no methods/declarations exist in Derby that can provide this info,
> then new methods/declarations are going to have to be added in order for
> metadata to return the correct value.
Yes, and most likely getApproximateLengthInBytes() is not correct for
your use. I also noticed recently that the lengths returned by various
methods in the type id classes were not updated when the on-disk format
of the type changed. E.g. DATE used (several years ago) to be stored as
a serialzed java.sql.Date object, but is now a Derby specific format.
Some cleanup is probably needed in Derby's internal datatypes api to
clearly define what each get length or get size method is defined to
return. With JDBC and Derby the stored (on-disk) byte size of a type or
value is not meant to be exposed to the application. E.g. VARCHAR(10)
says that up to 10 Unicode characters can be stored in that column, not
10 bytes, or 20 bytes, but just 10 characters. Derby can then change its
storage format without affecting the application in any way.
Dan.
Re: Questions about "getApproximateLengthInBytes()"
Posted by Army <qo...@sbcglobal.net>.
Jack Klebanoff wrote:
> I am not sure whether DatabaseMetaData.getProcedureColumns() is supposed
> to return the size on disk or the RAM size of the column.
Or another option: is it supposed to return the size of bytes "transferred", as in the case of a server sending data to
a client? I only reason ask is because that's the way the field is described in the ODBC specification--I don't know if
it's supposed to be the same for JDBC or not? Nor do I know what it would mean to "transfer" bytes in embedded mode...?
If this value _is_ supposed to be the number of bytes "transferred", does that number always match the size on disk? My
guess is probably not, since it appears, for example, that the "writeExternal" method in SQLDate stores the date as an
integer--but I doubt that's what would be transferred from server to client...
Suresh Thalamati wrote:
> I don't think char(10) always uses 20 bytes. It could just use 10 bytes
> or more depending on the type of character being written...
Right, sorry, I was just giving an example. My assumption here is that the value to be returned by the metadata call is
the _maximum_ number of bytes possible--so in the case of a char(10) column, since it could, as you said, "use 10 bytes
or more", we'd have to return some number that is greater than 10--i.e. to return "10" like we currently do is wrong.
My question is whether or not a declaration of maximum BYTE length for the Derby builtin types exists somewhere in the
codeline, and if so, is it okay to use that for the metadata calls? And on that premise, I was looking at
"getApproximateLengthInBytes" to see if that method has the info required (hence my initial email on this subject).
If no methods/declarations exist in Derby that can provide this info, then new methods/declarations are going to have to
be added in order for metadata to return the correct value.
Army
Re: Questions about "getApproximateLengthInBytes()"
Posted by Suresh Thalamati <su...@gmail.com>.
Hi Army ,
I don't think char(10) always uses 20 bytes. It could just use 10
bytes or more depending on the type of character being written..
You can find more deataill on how characters written in
org.apache.derby.iapi.types.SQLChar: writeExternal(). , similarly for other
types in the same package.
Thanks
-suresht
Army wrote:
> Hi,
>
> I was looking yet again at some of Derby's metadata code, and I
> noticed that for the getProcedureColumns() method, the "LENGTH" value
> of the result set is supposed to be "length in bytes" (per JDBC
> spec). However, the value as assigned in the
> org.apache.derby.catalog.GetProcedureColumns class is really just the
> value of getMaximumWidth(), which doesn't seem correct.
>
> For example, a column defined as "CHAR(10)" has a max width of 10, but
> the length in bytes should be "20" since Java uses two bytes per
> character. Thus the current Derby code will return the wrong value
> for LENGTH with this metadata call.
>
> As I was looking into this problem, I found a method in TypeId.java
> called "getApproximateLengthInBytes()" that is apprently supposed to
> return the length of a column in bytes--i.e. it's just what we need
> for the metadata. While looking at the method, though, I had some
> questions, and I'm wondering if anyone out there knows any of the
> answers...
>
> 1) Why the "+ 2" in the following case?
>
> case StoredFormatIds.CHAR_TYPE_ID:
> case StoredFormatIds.NATIONAL_CHAR_TYPE_ID:
> return (2 * dts.getMaximumWidth()) + 2;
>
> 2) Why the value "200" in this case? It's used throughout the method
> whenever Integer.MAX_VALUE is encountered--is there a reason we use
> the specific value of 200? What does it mean, and how/where is it
> interpreted by Derby? Also, why the "8 +" in the else clause?
>
> case StoredFormatIds.DECIMAL_TYPE_ID:
> // Return 200 if precision is max int
> if (dts.getPrecision() == Integer.MAX_VALUE)
> {
> return 200;
> }
> else
> {
> return 8 + (int)
> (Math.ceil(((double)dts.getPrecision())/2d));
> }
>
> 3) Why the "10240" in the follwowing case? What does it mean, and
> how/where is it interpreted by Derby?
>
> case StoredFormatIds.LONGVARBIT_TYPE_ID:
> case StoredFormatIds.BLOB_TYPE_ID:
> case StoredFormatIds.CLOB_TYPE_ID:
> case StoredFormatIds.NCLOB_TYPE_ID:
> return 10240;
>
> 4) For datetime values, there is the following code:
>
> /*
> ** For Date/time we know the exact size
> ** thanks to some investigative work by
> ** someone or other (sad isn't it).
> */
> case StoredFormatIds.DATE_TYPE_ID:
> return 18;
> case StoredFormatIds.TIME_TYPE_ID:
> return 16;
> case StoredFormatIds.TIMESTAMP_TYPE_ID:
> return 29;
>
> Does anyone know how these values were calculated? I.e. what
> "investigative work" led to these results? Are they still correct?
>
> 5) Generally speaking, would it be acceptable to use this method for
> metadata LENGTH queries, instead of the current (and incorrect)
> "getMaximumLength()" call?
>
> Also, as a general question, are these values the same for Network
> Server? I.e. for a given type, does Network Server transfer the same
> (approximate) number of bytes as reported by this method, or does
> Network Server do it's own thing? I looked at the FdocaConstants
> class in Network Server and I noticed that the "outlen" field of the
> "mapJdbcTypeToDrdaType" method matches the above numbers in most cases
> EXCEPT for the datetime values--so is there a difference between the
> number of bytes used for datetime values in Embedded mode vs. that
> used/transferred in Network Server mode?
>
> Anyone know the answers to any of these questions?
>
> Thanks much,
> Army
>
>