Differences between revisions 106 and 107
Revision 106 as of 2006-10-05 20:04:11
Size: 19684
Editor: LauraStewart
Comment:
Revision 107 as of 2009-09-20 22:11:41
Size: 19790
Editor: localhost
Comment: converted to 1.6 markup
Deletions are marked like this. Additions are marked like this.
Line 1: Line 1:
This page is for reviewing the 10.2 [http://db.apache.org/derby/manuals/index.html#latest ''Derby Reference Manual'']. This page is for reviewing the 10.2 [[http://db.apache.org/derby/manuals/index.html#latest|''Derby Reference Manual'']].
Line 7: Line 7:
||[#sqlreference SQL Language Reference] ||Yip, Manjula || Completed(Yip) ||
||[#sqlreference SQL Language Reference] ||Myrna || Completed ||
||[#sqlreservedwords SQL Reserved Words] ||Yip || Completed ||
||[#sql92support Derby support for SQL-92 features] ||Yip || Completed ||
||[#systemtables Derby System Tables] ||Yip || Completed ||
||[#exceptionmessages Derby exception messages and SQL States] ([https://issues.apache.org/jira/browse/DERBY-1566 DERBY-1566])|| jta, Yip || Completed ||
||[#jdbcref JDBC Reference] || Mamta, Kristian || ||
||[#settingattr Setting attributes for the database connection URL] || Mamta || ||
||[#j2eecompliance J2EE Compliance: Java Transaction API and javax.sql Extensions] || Susan || Completed ||
||[#derbyapi Derby API] || Mamta || ||
||[#derbylimits Derby limitations] || || ||
||[[#sqlreference|SQL Language Reference]] ||Yip, Manjula || Completed(Yip) ||
||[[#sqlreference|SQL Language Reference]] ||Myrna || Completed ||
||[[#sqlreservedwords|SQL Reserved Words]] ||Yip || Completed ||
||[[#sql92support|Derby support for SQL-92 features]] ||Yip || Completed ||
||[[#systemtables|Derby System Tables]] ||Yip || Completed ||
||[[#exceptionmessages|Derby exception messages and SQL States]] ([[https://issues.apache.org/jira/browse/DERBY-1566|DERBY-1566]])|| jta, Yip || Completed ||
||[[#jdbcref|JDBC Reference]] || Mamta, Kristian || ||
||[[#settingattr|Setting attributes for the database connection URL]] || Mamta || ||
||[[#j2eecompliance|J2EE Compliance: Java Transaction API and javax.sql Extensions]] || Susan || Completed ||
||[[#derbyapi|Derby API]] || Mamta || ||
||[[#derbylimits|Derby limitations]] || || ||
Line 21: Line 21:
As you spot issues please add specific comments to the tables below, paying attention to the [http://wiki.apache.org/db-derby/TenTwoDocReview guidelines]. For very extensive comments, please post to derby-dev, or to the JIRA issue 1839, then include the URL in the Comment field. '''Derby-1839''' has been setup to address the issues that are found in the Reference Manual. As you spot issues please add specific comments to the tables below, paying attention to the [[http://wiki.apache.org/db-derby/TenTwoDocReview|guidelines]]. For very extensive comments, please post to derby-dev, or to the JIRA issue 1839, then include the URL in the Comment field. '''Derby-1839''' has been setup to address the issues that are found in the Reference Manual.
Line 23: Line 23:
[[Anchor(sqlreference)]]''' SQL Language Reference:''' <<Anchor(sqlreference)>>''' SQL Language Reference:'''
Line 27: Line 27:
||DECLARE GLOBAL TEMPORARY TABLE statement || [http://db.apache.org/derby/docs/dev/ref/rrefdeclaretemptable.html rrefdeclaretemptable.html] || Yip || Under Restrictions on Declared Global Temporary Tables, GRANT and REVOKE should be added to that restriction list. || Fixed (Laura) ||
||CREATE SCHEMA statement || [http://db.apache.org/derby/docs/dev/ref/rrefsqlj31580.html rrefsqlj31580.html] || Yip || CREATE SCHEMA syntax needs to be updated. The following are also allowed: CREATE SCHEMA schema-name AUTHORIZATION user-name and CREATE SCHEMA AUTHORIZATION user-name. Addtional examples to use these new syntax would be nice. || Fixed (Laura) ||
||RENAME TABLE statement || [http://db.apache.org/derby/docs/dev/ref/rrefsqljrenametablestatement.html rrefsqljrenametablestatement.html] || Yip || RENAME TABLE needs to address that one can only perform renaming of a table if the user is the database owner or the user have the authorization to the schema of the table to be renamed. || Fixed (Laura) ||
||LOCK TABLE statement || [http://db.apache.org/derby/docs/dev/ref/rrefsqlj40506.html rrefsqlj40506.html] || Yip || LOCK TABLE needs to address that one can only perform locking of a table if the user is the database owner or the user have the proper privilege to execute such an action. This is not covered in the design spec. || Fixed (Laura) ||
||CREATE TRIGGER statement || [http://db.apache.org/derby/docs/dev/ref/rrefsqlj43125.html rrefsqlj43125.html] || Yip || The statement "You can create a trigger in any schema except one that starts with SYS." is no longer true when sql authorization is enabled. Also, under the syntax section, { INSERT | DELETE | UPDATE } [OF column-name [, column-name]* ] should be { INSERT | DELETE | UPDATE [ OF column-name [, column-name]* ] } || Fixed (Laura) ||
||Built-in Functions || [http://db.apache.org/derby/docs/dev/ref/toc.html ref/toc.html] || Susan || The table of contents was edited to include the new Math Functions (see DERBY-1549) ACOS, ASIN, ATAN, etc. The word "function" was added to each of these functions in the toc, whereas the existing functions do not contain this word. For consistency it would be better if this word was removed. || This has not been fixed as of the 10.2.1.6 RC. Derby-1920 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1920 ||
||ATAN Function || [http://db.apache.org/derby/docs/dev/ref/rreffuncatan.html rreffuncatan.html] || Susan || According to DERBY-1548, Derby does not support negative zero, so this statement 'If the specified number is zero (0), the result of this function is zero with the same sign as the specified number. ' should be changed to this 'If the specified number is zero (0), the result of this function is zero.' || Fixed (Laura) ||
||CEIL Function || [http://db.apache.org/derby/docs/dev/ref/rreffuncceil.html rreffuncceil.html] || Susan || According to DERBY-1548, Derby does not support negative zero, so this statement 'If the specified number is less than zero but greater than -1.0, then the result of these functions is negative zero.' should be changed to this 'If the specified number is less than zero but greater than -1.0, then the result of these functions is zero.' || Fixed (Laura) ||
||JDBC Escape Syntax - PI Function || [http://db.apache.org/derby/docs/dev/ref/rrefjdbc88908.html rrefjdbc88908.html] || Susan || I think this is a typo - 'Returns an value that is closer than any other value to pi.' I believe this is correct, 'Returns a value that is closer than any other value to pi.' || Fixed (Laura) ||
||List of datatypes || [http://db.apache.org/derby/docs/dev/ref/ derv/ref]|| Dan D || In the list of data types the XML type is listed as 'XML data type', remove the words 'data type', all of the other types are listed just as the type name. Same is true of the page that describes the XML type, it says 'XML data type', other types just have the name of the type, e.g. BLOB || Derby-1920 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1920 ||
|| XQUERY operator || [http://db.apache.org/derby/docs/dev/ref/rreffuncxmlquery.html rreffuncxmlquery.html] || Dan D || This description: ''The result of the XMLQUERY operator is another Derby XML value. Unlike other XML values, the result might represent an empty sequence of XML nodes or values. Atomic values, such as strings, can be part of the result sequence. The result of an XMLQUERY operator is not guaranteed to represent a valid XML document and it might not be possible to insert the result of an XMLQUERY operator into a Derby XML column'' should be replaced with: The result of the XMLQUERY operator is '''a value of type XML'''. '''The result represents a''' sequence of XML nodes or values. Atomic values, such as strings, can be part of the result sequence. The result of an XMLQUERY operator is not guaranteed to represent a '''well formed''' XML document and it might not be possible to insert the result of an XMLQUERY operator into an XML column - Notes on the changes (bold), a) no need to use Derby b) The old wording had the use of 'might' and 'empty sequence', I think it wanted to describe a sequence and the result is always a sequence, not that it might be. c) Use of 'well-formed' is better than 'valid'. || Fixed (Laura) ||
|| Date function || [http://db.apache.org/derby/docs/dev/ref/rrefdatefunc.html ref/rrefdatefunc.html] || jta || The number listed in the description "a positive number less than or equal to 3,652,059" gets an error, the max number that works is 2,932,897 ([http://mail-archives.apache.org/mod_mbox/db-derby-dev/200608.mbox/%3c44F722C0.2060203@bristowhill.com%3e posted to derby-dev], added to [https://issues.apache.org/jira/browse/DERBY-1803 DERBY-1803]) || Fixed (Laura)||
|| XML data type || [http://db.apache.org/derby/docs/dev/ref/rrefsqljtypexml.html rrefsqljtypexml.html] [[BR]] || Army || See following thread: [http://thread.gmane.org/gmane.comp.apache.db.derby.devel/28136/focus=28136  xmlTypeThread] [[BR]] ^(KW)^Missing dot between class and methodname. Change !PreparedStatementsetCharacterStream to !PreparedStatement.setCharacterStream.|| Fixed (Laura) ||
|| XML operators || [http://db.apache.org/derby/docs/dev/ref/rrefsqlj29026.html rrefsqlj29026.html] || Army || See following thread: [http://thread.gmane.org/gmane.comp.apache.db.derby.devel/28570/focus=28570  xmlOpsThread] || Fixed (Laura) ||
||DECLARE GLOBAL TEMPORARY TABLE statement || [[http://db.apache.org/derby/docs/dev/ref/rrefdeclaretemptable.html|rrefdeclaretemptable.html]] || Yip || Under Restrictions on Declared Global Temporary Tables, GRANT and REVOKE should be added to that restriction list. || Fixed (Laura) ||
||CREATE SCHEMA statement || [[http://db.apache.org/derby/docs/dev/ref/rrefsqlj31580.html|rrefsqlj31580.html]] || Yip || CREATE SCHEMA syntax needs to be updated. The following are also allowed: CREATE SCHEMA schema-name AUTHORIZATION user-name and CREATE SCHEMA AUTHORIZATION user-name. Addtional examples to use these new syntax would be nice. || Fixed (Laura) ||
||RENAME TABLE statement || [[http://db.apache.org/derby/docs/dev/ref/rrefsqljrenametablestatement.html|rrefsqljrenametablestatement.html]] || Yip || RENAME TABLE needs to address that one can only perform renaming of a table if the user is the database owner or the user have the authorization to the schema of the table to be renamed. || Fixed (Laura) ||
||LOCK TABLE statement || [[http://db.apache.org/derby/docs/dev/ref/rrefsqlj40506.html|rrefsqlj40506.html]] || Yip || LOCK TABLE needs to address that one can only perform locking of a table if the user is the database owner or the user have the proper privilege to execute such an action. This is not covered in the design spec. || Fixed (Laura) ||
||CREATE TRIGGER statement || [[http://db.apache.org/derby/docs/dev/ref/rrefsqlj43125.html|rrefsqlj43125.html]] || Yip || The statement "You can create a trigger in any schema except one that starts with SYS." is no longer true when sql authorization is enabled. Also, under the syntax section, { INSERT | DELETE | UPDATE } [OF column-name [, column-name]* ] should be { INSERT | DELETE | UPDATE [ OF column-name [, column-name]* ] } || Fixed (Laura) ||
||Built-in Functions || [[http://db.apache.org/derby/docs/dev/ref/toc.html|ref/toc.html]] || Susan || The table of contents was edited to include the new Math Functions (see DERBY-1549) ACOS, ASIN, ATAN, etc. The word "function" was added to each of these functions in the toc, whereas the existing functions do not contain this word. For consistency it would be better if this word was removed. || This has not been fixed as of the 10.2.1.6 RC. Derby-1920 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1920 ||
||ATAN Function || [[http://db.apache.org/derby/docs/dev/ref/rreffuncatan.html|rreffuncatan.html]] || Susan || According to DERBY-1548, Derby does not support negative zero, so this statement 'If the specified number is zero (0), the result of this function is zero with the same sign as the specified number. ' should be changed to this 'If the specified number is zero (0), the result of this function is zero.' || Fixed (Laura) ||
||CEIL Function || [[http://db.apache.org/derby/docs/dev/ref/rreffuncceil.html|rreffuncceil.html]] || Susan || According to DERBY-1548, Derby does not support negative zero, so this statement 'If the specified number is less than zero but greater than -1.0, then the result of these functions is negative zero.' should be changed to this 'If the specified number is less than zero but greater than -1.0, then the result of these functions is zero.' || Fixed (Laura) ||
||JDBC Escape Syntax - PI Function || [[http://db.apache.org/derby/docs/dev/ref/rrefjdbc88908.html|rrefjdbc88908.html]] || Susan || I think this is a typo - 'Returns an value that is closer than any other value to pi.' I believe this is correct, 'Returns a value that is closer than any other value to pi.' || Fixed (Laura) ||
||List of datatypes || [[http://db.apache.org/derby/docs/dev/ref/|derv/ref]]|| Dan D || In the list of data types the XML type is listed as 'XML data type', remove the words 'data type', all of the other types are listed just as the type name. Same is true of the page that describes the XML type, it says 'XML data type', other types just have the name of the type, e.g. BLOB || Derby-1920 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1920 ||
|| XQUERY operator || [[http://db.apache.org/derby/docs/dev/ref/rreffuncxmlquery.html|rreffuncxmlquery.html]] || Dan D || This description: ''The result of the XMLQUERY operator is another Derby XML value. Unlike other XML values, the result might represent an empty sequence of XML nodes or values. Atomic values, such as strings, can be part of the result sequence. The result of an XMLQUERY operator is not guaranteed to represent a valid XML document and it might not be possible to insert the result of an XMLQUERY operator into a Derby XML column'' should be replaced with: The result of the XMLQUERY operator is '''a value of type XML'''. '''The result represents a''' sequence of XML nodes or values. Atomic values, such as strings, can be part of the result sequence. The result of an XMLQUERY operator is not guaranteed to represent a '''well formed''' XML document and it might not be possible to insert the result of an XMLQUERY operator into an XML column - Notes on the changes (bold), a) no need to use Derby b) The old wording had the use of 'might' and 'empty sequence', I think it wanted to describe a sequence and the result is always a sequence, not that it might be. c) Use of 'well-formed' is better than 'valid'. || Fixed (Laura) ||
|| Date function || [[http://db.apache.org/derby/docs/dev/ref/rrefdatefunc.html|ref/rrefdatefunc.html]] || jta || The number listed in the description "a positive number less than or equal to 3,652,059" gets an error, the max number that works is 2,932,897 ([[http://mail-archives.apache.org/mod_mbox/db-derby-dev/200608.mbox/%3c44F722C0.2060203@bristowhill.com%3e|posted to derby-dev]], added to [[https://issues.apache.org/jira/browse/DERBY-1803|DERBY-1803]]) || Fixed (Laura)||
|| XML data type || [[http://db.apache.org/derby/docs/dev/ref/rrefsqljtypexml.html|rrefsqljtypexml.html]] <<BR>> || Army || See following thread: [[http://thread.gmane.org/gmane.comp.apache.db.derby.devel/28136/focus=28136|xmlTypeThread]] <<BR>> ^(KW)^Missing dot between class and methodname. Change !PreparedStatementsetCharacterStream to !PreparedStatement.setCharacterStream.|| Fixed (Laura) ||
|| XML operators || [[http://db.apache.org/derby/docs/dev/ref/rrefsqlj29026.html|rrefsqlj29026.html]] || Army || See following thread: [[http://thread.gmane.org/gmane.comp.apache.db.derby.devel/28570/focus=28570|xmlOpsThread]] || Fixed (Laura) ||
Line 42: Line 42:
|| XMLSERIAIZE operator || [http://db.apache.org/derby/docs/dev/ref/rreffuncxmlserialize.html rreffuncxmlserialize.html ] || Susan || To retain consistencey in all of the XML examples the example table name should remain x_table. When used in the XMLSERIALIZE example it was changed to xtable. || Fixed (Laura) ||
|| CAST function || [http://db.apache.org/derby/docs/10.2/ref/rrefsqlj33562.html rrefsqlj33562.html] || Susan || Several of the values listed in the supported CAST conversion table are incorrect when casting to VARCHAR and TIMESTAMP. See [http://issues.apache.org/jira/browse/DERBY-1804 DERBY-1804] || Fixed (Laura) ||
|| SECOND, SMALLINT, SUBSTR, TIMESTAMP, UCASE functions || See the following thread on [http://mail-archives.apache.org/mod_mbox/db-derby-dev/200609.mbox/%3c20060901224247.49614.qmail@web81305.mail.mud.yahoo.com%3e derby-dev] || Susan || There are some incorrect statements or errors for all of these functions. || Patch submitted (Laura) for SMALLINT, SUBSTR, TIMESTAMP, AND UCASE functions. Some updates to SECOND. Derby-1927 and Derby-908 have been opened to address the remaining issues for SECOND and the general comment about built-in functions. https://issues.apache.org/jira/browse/DERBY-1924 https://issues.apache.org/jira/browse/DERBY-908 ||
|| DECLARE GLOBAL TEMPORARY TABLE || [http://db.apache.org/derby/docs/10.2/ref/rrefdeclaretemptable.html rrefdeclaretemptable.html] || Myrna || 1. DOUBLE is missing from list of supported Data-types (although it's a synonym of DOUBLE PRECISION which is there, so minor, minor). 2. Following are missing from 'the following data types cannot be used with Declared...': CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONG VARCHAR FOR BIT DATA. 3. The section 'Restrictions specific to Derby' needs to be integrated into the section 'Restrictions on Declared Global Temporary Tables'. Some items are in both places, for instance, 'Temporary tables cannot be specified in the following statements' lists 'LOCK' and the other section 'LOCK TABLE'. On the other hand, CREATE INDEX is not in the list of unsupported statements, but the 'Restrictions specific to Derby' shows that 'Indexes' are not supported. || Fixed (Laura) ||
|| !TableExpression || [http://db.apache.org/derby/docs/dev/ref/rreftableexpression.html rreftableexpression.html ] || Myrna || Link to and list with 1 item !TableOrViewExpression is placed oddly. I think !TableOrViewExpression should be the main item, and !TableExpression should be a subitem. Maybe we can just get rid of the page for !TableExpression altogether and only have !TableOrViewExpression instead with all the info currently on the !TableExpression page - except for combining the syntax. || Derby-1924 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1924 ||
|| XMLSERIAIZE operator || [[http://db.apache.org/derby/docs/dev/ref/rreffuncxmlserialize.html|rreffuncxmlserialize.html ]] || Susan || To retain consistencey in all of the XML examples the example table name should remain x_table. When used in the XMLSERIALIZE example it was changed to xtable. || Fixed (Laura) ||
|| CAST function || [[http://db.apache.org/derby/docs/10.2/ref/rrefsqlj33562.html|rrefsqlj33562.html]] || Susan || Several of the values listed in the supported CAST conversion table are incorrect when casting to VARCHAR and TIMESTAMP. See [[http://issues.apache.org/jira/browse/DERBY-1804|DERBY-1804]] || Fixed (Laura) ||
|| SECOND, SMALLINT, SUBSTR, TIMESTAMP, UCASE functions || See the following thread on [[http://mail-archives.apache.org/mod_mbox/db-derby-dev/200609.mbox/%3c20060901224247.49614.qmail@web81305.mail.mud.yahoo.com%3e|derby-dev]] || Susan || There are some incorrect statements or errors for all of these functions. || Patch submitted (Laura) for SMALLINT, SUBSTR, TIMESTAMP, AND UCASE functions. Some updates to SECOND. Derby-1927 and Derby-908 have been opened to address the remaining issues for SECOND and the general comment about built-in functions. https://issues.apache.org/jira/browse/DERBY-1924 https://issues.apache.org/jira/browse/DERBY-908 ||
|| DECLARE GLOBAL TEMPORARY TABLE || [[http://db.apache.org/derby/docs/10.2/ref/rrefdeclaretemptable.html|rrefdeclaretemptable.html]] || Myrna || 1. DOUBLE is missing from list of supported Data-types (although it's a synonym of DOUBLE PRECISION which is there, so minor, minor). 2. Following are missing from 'the following data types cannot be used with Declared...': CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONG VARCHAR FOR BIT DATA. 3. The section 'Restrictions specific to Derby' needs to be integrated into the section 'Restrictions on Declared Global Temporary Tables'. Some items are in both places, for instance, 'Temporary tables cannot be specified in the following statements' lists 'LOCK' and the other section 'LOCK TABLE'. On the other hand, CREATE INDEX is not in the list of unsupported statements, but the 'Restrictions specific to Derby' shows that 'Indexes' are not supported. || Fixed (Laura) ||
|| !TableExpression || [[http://db.apache.org/derby/docs/dev/ref/rreftableexpression.html|rreftableexpression.html ]] || Myrna || Link to and list with 1 item !TableOrViewExpression is placed oddly. I think !TableOrViewExpression should be the main item, and !TableExpression should be a subitem. Maybe we can just get rid of the page for !TableExpression altogether and only have !TableOrViewExpression instead with all the info currently on the !TableExpression page - except for combining the syntax. || Derby-1924 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1924 ||
Line 49: Line 49:
|| Grant Statement || [http://db.apache.org/derby/docs/dev/ref/rrefsqlgrant.html rrefsqlgrant.html ] || Manjula || For the update previlege we should mention that to update a row using a statement which has a where clause one should have select permission on that particular column. || Fixed (Laura) ||
|| Grant Statement || [http://db.apache.org/derby/docs/dev/ref/rrefsqlgrant.html rrefsqlgrant.html ] || Manjula || Please add an example about the RESTRICT clause on REVOKE statements for routines . || Fixed (Laura) ||
|| Grant statement || [http://mail-archives.apache.org/mod_mbox/db-derby-dev/200609.mbox/%3c44F87B93.1090607@apache.org%3e derby-dev] || Dan D || Incorrect information for GRANT || Fixed (Laura) ||
|| Grant Statement || [[http://db.apache.org/derby/docs/dev/ref/rrefsqlgrant.html|rrefsqlgrant.html ]] || Manjula || For the update previlege we should mention that to update a row using a statement which has a where clause one should have select permission on that particular column. || Fixed (Laura) ||
|| Grant Statement || [[http://db.apache.org/derby/docs/dev/ref/rrefsqlgrant.html|rrefsqlgrant.html ]] || Manjula || Please add an example about the RESTRICT clause on REVOKE statements for routines . || Fixed (Laura) ||
|| Grant statement || [[http://mail-archives.apache.org/mod_mbox/db-derby-dev/200609.mbox/%3c44F87B93.1090607@apache.org%3e|derby-dev]] || Dan D || Incorrect information for GRANT || Fixed (Laura) ||
Line 54: Line 54:
[[Anchor(sqlreservedwords)]]'''SQL Reserved Words: ''' <<Anchor(sqlreservedwords)>>'''SQL Reserved Words: '''
Line 59: Line 59:
[[Anchor(sql92support)]]'''Derby support for SQL-92 features: ''' <<Anchor(sql92support)>>'''Derby support for SQL-92 features: '''
Line 65: Line 65:
[[Anchor(systemtables)]]'''Derby System Tables: ''' <<Anchor(systemtables)>>'''Derby System Tables: '''
Line 71: Line 71:
[[Anchor(exceptionmessages)]]'''Derby exception messages and SQL States: ''' <<Anchor(exceptionmessages)>>'''Derby exception messages and SQL States: '''
Line 74: Line 74:
|| Autogen of sqlstates || [https://issues.apache.org/jira/browse/DERBY-1566 DERBY-1566] || jta || See the Jira issue|| Fixed (Jean) || || Autogen of sqlstates || [[https://issues.apache.org/jira/browse/DERBY-1566|DERBY-1566]] || jta || See the Jira issue|| Fixed (Jean) ||
Line 76: Line 76:
[[Anchor(jdbcref)]]'''JDBC Reference: ''' <<Anchor(jdbcref)>>'''JDBC Reference: '''
Line 80: Line 80:
||java.sql.Blob and java.sql.Clob || [http://db.apache.org/derby/docs/10.2/ref/rrefjdbc96386.html] || Mamta || This section has a table titled Table 1. JDBC 2.0 java.sql.Blob Methods Supported. The first row, 3rd column of this table shows up empty in the html page but has an ' in the pdf format. The pdf format should also show the column empty. I think this happens for all the tables with no data in the pdf format || Derby-1932 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1932 ||
||DatabaseMetaData functionality not supported || [http://db.apache.org/derby/docs/10.2/ref/ref-single.html#N2E3A9] || Mamta || This section says that getColumnPrivileges and getTablePrivileges are not supported. But checkin r381109 has changes to metadata.properties for these 2 functions. So, looks like these methods are probably now supported with grant revoke functionality. || Derby-1932 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1932 ||
|| Data streaming || ^(1)^[http://db.apache.org/derby/docs/dev/ref/rrefjavsqlprst.html][[BR]] ^(2)^[http://db.apache.org/derby/docs/dev/ref/rrefjavsqlrssc.html][[BR]] ^(3)^[http://db.apache.org/derby/docs/dev/ref/crefjavbclobnot.html] [[BR]] ^(4)^[http://db.apache.org/derby/docs/dev/ref/rrefjdbc4_0summary.html]|| Kristian ||(1): Rewrite section to reflect a newer JDBC version than 1.2? Comment that getUnicodeStream is deprecated and no longer implemented in Derby. Add description of getCharacterStream. Could also add info about setNCharacterStream, which is JDBC 4 only and not supported. The note under the table is no longer accurate; it only applies when using the client driver (and also only for the client?).[[BR]](2): OK [[BR]] (3): Update list of methods (mark as deprecated, add getCharacterStream). [[BR]](4): OK.|| Derby-1932 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1932 ||
||java.sql.Blob and java.sql.Clob || [[http://db.apache.org/derby/docs/10.2/ref/rrefjdbc96386.html]] || Mamta || This section has a table titled Table 1. JDBC 2.0 java.sql.Blob Methods Supported. The first row, 3rd column of this table shows up empty in the html page but has an ' in the pdf format. The pdf format should also show the column empty. I think this happens for all the tables with no data in the pdf format || Derby-1932 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1932 ||
||DatabaseMetaData functionality not supported || [[http://db.apache.org/derby/docs/10.2/ref/ref-single.html#N2E3A9]] || Mamta || This section says that getColumnPrivileges and getTablePrivileges are not supported. But checkin r381109 has changes to metadata.properties for these 2 functions. So, looks like these methods are probably now supported with grant revoke functionality. || Derby-1932 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1932 ||
|| Data streaming || ^(1)^[[http://db.apache.org/derby/docs/dev/ref/rrefjavsqlprst.html]]<<BR>> ^(2)^[[http://db.apache.org/derby/docs/dev/ref/rrefjavsqlrssc.html]]<<BR>> ^(3)^[[http://db.apache.org/derby/docs/dev/ref/crefjavbclobnot.html]] <<BR>> ^(4)^[[http://db.apache.org/derby/docs/dev/ref/rrefjdbc4_0summary.html]]|| Kristian ||(1): Rewrite section to reflect a newer JDBC version than 1.2? Comment that getUnicodeStream is deprecated and no longer implemented in Derby. Add description of getCharacterStream. Could also add info about setNCharacterStream, which is JDBC 4 only and not supported. The note under the table is no longer accurate; it only applies when using the client driver (and also only for the client?).<<BR>>(2): OK <<BR>> (3): Update list of methods (mark as deprecated, add getCharacterStream). <<BR>>(4): OK.|| Derby-1932 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1932 ||
Line 85: Line 85:
[[Anchor(settingattr)]]'''Setting attributes for the database connection URL: ''' <<Anchor(settingattr)>>'''Setting attributes for the database connection URL: '''
Line 89: Line 89:
||Attributes of the Derby database connection URL || [http://db.apache.org/derby/docs/10.2/ref/ref-single.html#N2D6D9] || Mamta || This section has 3 examples of setting attributes on database connection URL using Java. One minor suggestion is we should use Java commenting scheme ie /*..*/ instead of -- in this sample code. || Derby-1933 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1933 ||
|| encryptionKey section under Setting attributes for the database connection URL || [http://db.apache.org/derby/docs/10.2/ref/rrefattribencryptkey.html] || Mamta || The pdf page for encryptionKey has incomplete example. The pdf page only shows jdbc:derby:newDB;create=true;dataEncryption=true;encryptionAlgorithm=DES/CBC/NoPadding; The corresponding html page shows the correct example which looks as follows jdbc:derby:newDB;create=true;dataEncryption=true;encryptionAlgorithm=DES/CBC/NoPadding;encryptionKey=6162636465666768 || Derby-1933 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1933 ||
||Attributes of the Derby database connection URL || [[http://db.apache.org/derby/docs/10.2/ref/ref-single.html#N2D6D9]] || Mamta || This section has 3 examples of setting attributes on database connection URL using Java. One minor suggestion is we should use Java commenting scheme ie /*..*/ instead of -- in this sample code. || Derby-1933 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1933 ||
|| encryptionKey section under Setting attributes for the database connection URL || [[http://db.apache.org/derby/docs/10.2/ref/rrefattribencryptkey.html]] || Mamta || The pdf page for encryptionKey has incomplete example. The pdf page only shows jdbc:derby:newDB;create=true;dataEncryption=true;encryptionAlgorithm=DES/CBC/NoPadding; The corresponding html page shows the correct example which looks as follows jdbc:derby:newDB;create=true;dataEncryption=true;encryptionAlgorithm=DES/CBC/NoPadding;encryptionKey=6162636465666768 || Derby-1933 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1933 ||
Line 93: Line 93:
[[Anchor(j2eecompliance)]]'''J2EE Compliance: Java Transaction API and javax.sql Extensions: ''' <<Anchor(j2eecompliance)>>'''J2EE Compliance: Java Transaction API and javax.sql Extensions: '''
Line 96: Line 96:
|| javax.sql:JDBC Extensions || [http://db.apache.org/derby/docs/dev/ref/rrefjta18596.html HTML Url] || Susan || This URL no longer exists: (For more details about these extensions, see http://java.sun.com/products/jdbc/jdbc20.stdext.javadoc/javax/sql/package-summary.html). The page that has this information, although you have to browse to the section called JDBC 2.0 Optional Package API is http://java.sun.com/products/jdbc/download.html || Derby-1934 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1934 || || javax.sql:JDBC Extensions || [[http://db.apache.org/derby/docs/dev/ref/rrefjta18596.html|HTML Url]] || Susan || This URL no longer exists: (For more details about these extensions, see http://java.sun.com/products/jdbc/jdbc20.stdext.javadoc/javax/sql/package-summary.html). The page that has this information, although you have to browse to the section called JDBC 2.0 Optional Package API is http://java.sun.com/products/jdbc/download.html || Derby-1934 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1934 ||
Line 99: Line 99:
[[Anchor(derbyapi)]]'''Derby API: ''' <<Anchor(derbyapi)>>'''Derby API: '''
Line 105: Line 105:
[[Anchor(derbylimits)]]'''Derby limitations: ''' <<Anchor(derbylimits)>>'''Derby limitations: '''
Line 108: Line 108:
|| DATE, TIME and TIMESTAMP || [http://db.apache.org/derby/docs/dev/ref/rrefdttlimits.html rrefdttlimits.html] || Susan || The largest TIMESTAMP value is incorrect, [http://issues.apache.org/jira/browse/DERBY-1803 DERBY-1803] || Fixed (Laura)||
|| Numeric limitations|| [http://db.apache.org/derby/docs/dev/ref/rrefnumericlimits.html rrefnumericlimits.html] || Susan || The largest decimal precision value is incorrect, it should be 31 instead of 31,255 || Derby-1935 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1935 ||
|| DATE, TIME and TIMESTAMP || [[http://db.apache.org/derby/docs/dev/ref/rrefdttlimits.html|rrefdttlimits.html]] || Susan || The largest TIMESTAMP value is incorrect, [[http://issues.apache.org/jira/browse/DERBY-1803|DERBY-1803]] || Fixed (Laura)||
|| Numeric limitations|| [[http://db.apache.org/derby/docs/dev/ref/rrefnumericlimits.html|rrefnumericlimits.html]] || Susan || The largest decimal precision value is incorrect, it should be 31 instead of 31,255 || Derby-1935 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1935 ||

This page is for reviewing the 10.2 ''Derby Reference Manual''.

Section Summary

Please list the sections you intend to review in the summary table below:

Section Name

Reviewers

Status

SQL Language Reference

Yip, Manjula

Completed(Yip)

SQL Language Reference

Myrna

Completed

SQL Reserved Words

Yip

Completed

Derby support for SQL-92 features

Yip

Completed

Derby System Tables

Yip

Completed

Derby exception messages and SQL States (DERBY-1566)

jta, Yip

Completed

JDBC Reference

Mamta, Kristian

Setting attributes for the database connection URL

Mamta

J2EE Compliance: Java Transaction API and javax.sql Extensions

Susan

Completed

Derby API

Mamta

Derby limitations

Sub Section Details

As you spot issues please add specific comments to the tables below, paying attention to the guidelines. For very extensive comments, please post to derby-dev, or to the JIRA issue 1839, then include the URL in the Comment field. Derby-1839 has been setup to address the issues that are found in the Reference Manual.

SQL Language Reference:

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

DECLARE GLOBAL TEMPORARY TABLE statement

rrefdeclaretemptable.html

Yip

Under Restrictions on Declared Global Temporary Tables, GRANT and REVOKE should be added to that restriction list.

Fixed (Laura)

CREATE SCHEMA statement

rrefsqlj31580.html

Yip

CREATE SCHEMA syntax needs to be updated. The following are also allowed: CREATE SCHEMA schema-name AUTHORIZATION user-name and CREATE SCHEMA AUTHORIZATION user-name. Addtional examples to use these new syntax would be nice.

Fixed (Laura)

RENAME TABLE statement

rrefsqljrenametablestatement.html

Yip

RENAME TABLE needs to address that one can only perform renaming of a table if the user is the database owner or the user have the authorization to the schema of the table to be renamed.

Fixed (Laura)

LOCK TABLE statement

rrefsqlj40506.html

Yip

LOCK TABLE needs to address that one can only perform locking of a table if the user is the database owner or the user have the proper privilege to execute such an action. This is not covered in the design spec.

Fixed (Laura)

CREATE TRIGGER statement

rrefsqlj43125.html

Yip

The statement "You can create a trigger in any schema except one that starts with SYS." is no longer true when sql authorization is enabled. Also, under the syntax section, { INSERT | DELETE | UPDATE } [OF column-name [, column-name]* ] should be { INSERT | DELETE | UPDATE [ OF column-name [, column-name]* ] }

Fixed (Laura)

Built-in Functions

ref/toc.html

Susan

The table of contents was edited to include the new Math Functions (see DERBY-1549) ACOS, ASIN, ATAN, etc. The word "function" was added to each of these functions in the toc, whereas the existing functions do not contain this word. For consistency it would be better if this word was removed.

This has not been fixed as of the 10.2.1.6 RC. Derby-1920 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1920

ATAN Function

rreffuncatan.html

Susan

According to DERBY-1548, Derby does not support negative zero, so this statement 'If the specified number is zero (0), the result of this function is zero with the same sign as the specified number. ' should be changed to this 'If the specified number is zero (0), the result of this function is zero.'

Fixed (Laura)

CEIL Function

rreffuncceil.html

Susan

According to DERBY-1548, Derby does not support negative zero, so this statement 'If the specified number is less than zero but greater than -1.0, then the result of these functions is negative zero.' should be changed to this 'If the specified number is less than zero but greater than -1.0, then the result of these functions is zero.'

Fixed (Laura)

JDBC Escape Syntax - PI Function

rrefjdbc88908.html

Susan

I think this is a typo - 'Returns an value that is closer than any other value to pi.' I believe this is correct, 'Returns a value that is closer than any other value to pi.'

Fixed (Laura)

List of datatypes

derv/ref

Dan D

In the list of data types the XML type is listed as 'XML data type', remove the words 'data type', all of the other types are listed just as the type name. Same is true of the page that describes the XML type, it says 'XML data type', other types just have the name of the type, e.g. BLOB

Derby-1920 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1920

XQUERY operator

rreffuncxmlquery.html

Dan D

This description: The result of the XMLQUERY operator is another Derby XML value. Unlike other XML values, the result might represent an empty sequence of XML nodes or values. Atomic values, such as strings, can be part of the result sequence. The result of an XMLQUERY operator is not guaranteed to represent a valid XML document and it might not be possible to insert the result of an XMLQUERY operator into a Derby XML column should be replaced with: The result of the XMLQUERY operator is a value of type XML. The result represents a sequence of XML nodes or values. Atomic values, such as strings, can be part of the result sequence. The result of an XMLQUERY operator is not guaranteed to represent a well formed XML document and it might not be possible to insert the result of an XMLQUERY operator into an XML column - Notes on the changes (bold), a) no need to use Derby b) The old wording had the use of 'might' and 'empty sequence', I think it wanted to describe a sequence and the result is always a sequence, not that it might be. c) Use of 'well-formed' is better than 'valid'.

Fixed (Laura)

Date function

ref/rrefdatefunc.html

jta

The number listed in the description "a positive number less than or equal to 3,652,059" gets an error, the max number that works is 2,932,897 (posted to derby-dev, added to DERBY-1803)

Fixed (Laura)

XML data type

rrefsqljtypexml.html

Army

See following thread: xmlTypeThread
(KW)Missing dot between class and methodname. Change PreparedStatementsetCharacterStream to PreparedStatement.setCharacterStream.

Fixed (Laura)

XML operators

rrefsqlj29026.html

Army

See following thread: xmlOpsThread

Fixed (Laura)

XML Operators

various

Dan D

The XML operators and data type include text about requiring Xalan etc. When trying out XML I felt lost at this point because there was no information on what I was meant to do. It was the feeling I was being told "You need to do something with Xalan to get this to work, but you are going to have to figure that out yourself". It stopped me from evaluating Mustang with XML. Can there be information in the developers' guide about this, and a link to that information from these Usage notes?

Derby-1923 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1923

XMLSERIAIZE operator

rreffuncxmlserialize.html

Susan

To retain consistencey in all of the XML examples the example table name should remain x_table. When used in the XMLSERIALIZE example it was changed to xtable.

Fixed (Laura)

CAST function

rrefsqlj33562.html

Susan

Several of the values listed in the supported CAST conversion table are incorrect when casting to VARCHAR and TIMESTAMP. See DERBY-1804

Fixed (Laura)

SECOND, SMALLINT, SUBSTR, TIMESTAMP, UCASE functions

See the following thread on derby-dev

Susan

There are some incorrect statements or errors for all of these functions.

Patch submitted (Laura) for SMALLINT, SUBSTR, TIMESTAMP, AND UCASE functions. Some updates to SECOND. Derby-1927 and Derby-908 have been opened to address the remaining issues for SECOND and the general comment about built-in functions. https://issues.apache.org/jira/browse/DERBY-1924 https://issues.apache.org/jira/browse/DERBY-908

DECLARE GLOBAL TEMPORARY TABLE

rrefdeclaretemptable.html

Myrna

1. DOUBLE is missing from list of supported Data-types (although it's a synonym of DOUBLE PRECISION which is there, so minor, minor). 2. Following are missing from 'the following data types cannot be used with Declared...': CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONG VARCHAR FOR BIT DATA. 3. The section 'Restrictions specific to Derby' needs to be integrated into the section 'Restrictions on Declared Global Temporary Tables'. Some items are in both places, for instance, 'Temporary tables cannot be specified in the following statements' lists 'LOCK' and the other section 'LOCK TABLE'. On the other hand, CREATE INDEX is not in the list of unsupported statements, but the 'Restrictions specific to Derby' shows that 'Indexes' are not supported.

Fixed (Laura)

TableExpression

rreftableexpression.html

Myrna

Link to and list with 1 item TableOrViewExpression is placed oddly. I think TableOrViewExpression should be the main item, and TableExpression should be a subitem. Maybe we can just get rid of the page for TableExpression altogether and only have TableOrViewExpression instead with all the info currently on the TableExpression page - except for combining the syntax.

Derby-1924 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1924

Grant Statement

rrefsqlgrant.html

Manjula

For the update previlege we should mention that to update a row using a statement which has a where clause one should have select permission on that particular column.

Fixed (Laura)

Grant Statement

rrefsqlgrant.html

Manjula

Please add an example about the RESTRICT clause on REVOKE statements for routines .

Fixed (Laura)

Grant statement

derby-dev

Dan D

Incorrect information for GRANT

Fixed (Laura)

SQL Reserved Words:

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

SQL reserved words

http://db.apache.org/derby/docs/10.2/ref/rrefkeywords29722.html

Yip

Add XMLQUERY as reserved word.

Patch submitted (Laura)

Derby support for SQL-92 features:

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

Table 12. Support for SQL-92 Features: Basic schema manipulation

http://db.apache.org/derby/docs/10.2/ref/rrefsql9241891.html

Yip

Column DERBY for GRANT/REVOKE row should mark as partial instead of no.

Derby-1928 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1928

Derby System Tables:

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

SYSTABLEPERMS

http://db.apache.org/derby/docs/10.2/ref/rrefsistabssystableperms.html

Yip

In the column information for the SYSTABLEPERMS table's SELECTPRIV,DELETEPRIV,INSERTPRIV,UPDATEPRIV, REFERENCEPRIV and TRIGGERPRIV column. Their contents column should be: The valid values are 'y'(non-grantable privilege),'Y'(grantable privilege) and 'N'(no privilege).

Derby-1929 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1929

SYSCOLPERMS

http://db.apache.org/derby/docs/10.2/ref/rrefsistabssyscolperms.html

Yip

In the column information for the SYSCOLPERMS table's TYPE column. Its content column should be: If the privilege is non-grantable, the valid values are 's' for SELECT, 'u' for UPDATE and 'r' for REFERENCES. If the privilege is grantable, the valid values are 'S' for SELECT, 'U' for UPDATE and 'R' for REFERENCES.

Derby-1929 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1929

Derby exception messages and SQL States:

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

Autogen of sqlstates

DERBY-1566

jta

See the Jira issue

Fixed (Jean)

JDBC Reference:

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

java.sql.Blob and java.sql.Clob

http://db.apache.org/derby/docs/10.2/ref/rrefjdbc96386.html

Mamta

This section has a table titled Table 1. JDBC 2.0 java.sql.Blob Methods Supported. The first row, 3rd column of this table shows up empty in the html page but has an ' in the pdf format. The pdf format should also show the column empty. I think this happens for all the tables with no data in the pdf format

Derby-1932 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1932

DatabaseMetaData functionality not supported

http://db.apache.org/derby/docs/10.2/ref/ref-single.html#N2E3A9

Mamta

This section says that getColumnPrivileges and getTablePrivileges are not supported. But checkin r381109 has changes to metadata.properties for these 2 functions. So, looks like these methods are probably now supported with grant revoke functionality.

Derby-1932 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1932

Data streaming

(1)http://db.apache.org/derby/docs/dev/ref/rrefjavsqlprst.html
(2)http://db.apache.org/derby/docs/dev/ref/rrefjavsqlrssc.html
(3)http://db.apache.org/derby/docs/dev/ref/crefjavbclobnot.html
(4)http://db.apache.org/derby/docs/dev/ref/rrefjdbc4_0summary.html

Kristian

(1): Rewrite section to reflect a newer JDBC version than 1.2? Comment that getUnicodeStream is deprecated and no longer implemented in Derby. Add description of getCharacterStream. Could also add info about setNCharacterStream, which is JDBC 4 only and not supported. The note under the table is no longer accurate; it only applies when using the client driver (and also only for the client?).
(2): OK
(3): Update list of methods (mark as deprecated, add getCharacterStream).
(4): OK.

Derby-1932 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1932

Setting attributes for the database connection URL:

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

Attributes of the Derby database connection URL

http://db.apache.org/derby/docs/10.2/ref/ref-single.html#N2D6D9

Mamta

This section has 3 examples of setting attributes on database connection URL using Java. One minor suggestion is we should use Java commenting scheme ie /*..*/ instead of -- in this sample code.

Derby-1933 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1933

encryptionKey section under Setting attributes for the database connection URL

http://db.apache.org/derby/docs/10.2/ref/rrefattribencryptkey.html

Mamta

The pdf page for encryptionKey has incomplete example. The pdf page only shows jdbc:derby:newDB;create=true;dataEncryption=true;encryptionAlgorithm=DES/CBC/NoPadding; The corresponding html page shows the correct example which looks as follows jdbc:derby:newDB;create=true;dataEncryption=true;encryptionAlgorithm=DES/CBC/NoPadding;encryptionKey=6162636465666768

Derby-1933 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1933

J2EE Compliance: Java Transaction API and javax.sql Extensions:

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

javax.sql:JDBC Extensions

HTML Url

Susan

This URL no longer exists: (For more details about these extensions, see http://java.sun.com/products/jdbc/jdbc20.stdext.javadoc/javax/sql/package-summary.html). The page that has this information, although you have to browse to the section called JDBC 2.0 Optional Package API is http://java.sun.com/products/jdbc/download.html

Derby-1934 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1934

Derby API:

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

Derby limitations:

Sub Section

HTML Url or PDF Page

Reviewer

Comment

Resolution

DATE, TIME and TIMESTAMP

rrefdttlimits.html

Susan

The largest TIMESTAMP value is incorrect, DERBY-1803

Fixed (Laura)

Numeric limitations

rrefnumericlimits.html

Susan

The largest decimal precision value is incorrect, it should be 31 instead of 31,255

Derby-1935 has been opened for this issue. https://issues.apache.org/jira/browse/DERBY-1935

ReferenceManualTenTwo (last edited 2009-09-20 22:11:41 by localhost)