Return-Path: 
 <derby-commits-return-10227-apmail-db-derby-commits-archive=db.apache.org@db.apache.org>
Delivered-To: apmail-db-derby-commits-archive@www.apache.org
Received: (qmail 60763 invoked from network); 7 May 2008 13:50:52 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 7 May 2008 13:50:52 -0000
Received: (qmail 5515 invoked by uid 500); 7 May 2008 13:50:54 -0000
Delivered-To: apmail-db-derby-commits-archive@db.apache.org
Received: (qmail 5485 invoked by uid 500); 7 May 2008 13:50:54 -0000
Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
list-help: <mailto:derby-commits-help@db.apache.org>
list-unsubscribe: <mailto:derby-commits-unsubscribe@db.apache.org>
List-Post: <mailto:derby-commits@db.apache.org>
Reply-To: "Derby Development" <derby-dev@db.apache.org>
List-Id: <derby-commits.db.apache.org>
Delivered-To: mailing list derby-commits@db.apache.org
Received: (qmail 5474 invoked by uid 99); 7 May 2008 13:50:54 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 07 May 2008 06:50:54 -0700
X-ASF-Spam-Status: No, hits=-2000.0 required=10.0
	tests=ALL_TRUSTED
X-Spam-Check-By: apache.org
Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4)
    by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 07 May 2008 13:50:05 +0000
Received: by eris.apache.org (Postfix, from userid 65534)
	id 5B0CA2388A09; Wed,  7 May 2008 06:50:20 -0700 (PDT)
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r654105 - in /db/derby/site/trunk: build/site/faq.html
 src/documentation/content/xdocs/faq.xml
Date: Wed, 07 May 2008 13:50:19 -0000
To: derby-commits@db.apache.org
From: johnemb@apache.org
X-Mailer: svnmailer-1.0.8
Message-Id: <20080507135020.5B0CA2388A09@eris.apache.org>
X-Virus-Checked: Checked by ClamAV on apache.org

Author: johnemb
Date: Wed May  7 06:50:18 2008
New Revision: 654105

URL: http://svn.apache.org/viewvc?rev=654105&view=rev
Log:
DERBY-232: FAQ: Updated answer for LIMIT question. Added examples 
for Statement.setMaxRows() as well as ROW_NUMBER (new in 10.4), 
and a couple of links.

Modified:
    db/derby/site/trunk/build/site/faq.html
    db/derby/site/trunk/src/documentation/content/xdocs/faq.xml

Modified: db/derby/site/trunk/build/site/faq.html
URL: http://svn.apache.org/viewvc/db/derby/site/trunk/build/site/faq.html?rev=654105&r1=654104&r2=654105&view=diff
==============================================================================
--- db/derby/site/trunk/build/site/faq.html (original)
+++ db/derby/site/trunk/build/site/faq.html Wed May  7 06:50:18 2008
@@ -775,10 +775,83 @@
 <div style="margin-left: 15px">
 <p>
       Derby supports limiting the number of rows returned by a query through
-      <a class="external" href="http://mail-archives.apache.org/mod_mbox/db-derby-dev/200411.mbox/%3c41A95632.3010301@debrunners.com%3e">JDBC</a>.
+      <a class="external" href="http://mail-archives.apache.org/mod_mbox/db-derby-dev/200411.mbox/%3c41A95632.3010301@debrunners.com%3e">JDBC</a>. For example, to fetch the first 5 rows of a large table:
+      </p>
+<p>
+      
+<span class="codefrag">Statement stmt = con.createStatement();</span>
+<br>
+      
+<span class="codefrag">stmt.setMaxRows(5);</span>
+<br>
+      
+<span class="codefrag">ResultSet rs = stmt.executeQuery("SELECT * FROM myLargeTable");</span>
+<br>
+      
+</p>
+<p>
+      Some related tuning tips are available in 
+      <a class="external" href="http://www.onjava.com/pub/a/onjava/2007/01/31/tuning-derby.html?page=1">this 
+      external article</a>.
+      </p>
+<p>
+      Starting with the 10.4.1.3 release Derby also supports limiting the number of
+      rows using the <span class="codefrag">ROW_NUMBER</span> function. 
+      </p>
+<p>
+      For example, to fetch the first 5
+      rows of a large table:
+      </p>
+<p>
+      
+<span class="codefrag">SELECT * FROM (</span>
+<br>
+      &nbsp;&nbsp;&nbsp;&nbsp;<span class="codefrag">SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*</span>
+<br>
+      &nbsp;&nbsp;&nbsp;&nbsp;<span class="codefrag">FROM myLargeTable</span>
+<br>
+      
+<span class="codefrag">) AS tmp</span>
+<br>
+      
+<span class="codefrag">WHERE rownum &lt;= 5;</span>
+<br>
+      
+</p>
+<p>
+      The <span class="codefrag">ROW_NUMBER</span> function can also be used to select a 
+      limited number of rows starting with an offset, for example:
+      </p>
+<p>
+      
+<span class="codefrag">SELECT * FROM (</span>
+<br>
+      &nbsp;&nbsp;&nbsp;&nbsp;<span class="codefrag">SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*</span>
+<br>
+      &nbsp;&nbsp;&nbsp;&nbsp;<span class="codefrag">FROM myLargeTable</span>
+<br>
+      
+<span class="codefrag">) AS tmp</span>
+<br>
+      
+<span class="codefrag">WHERE rownum &gt; 200000 AND rownum &lt;= 200005;</span>
+<br>
+      
+</p>
+<p>
+      For more information, refer to the ROW_NUMBER built-in function in 
+      the Derby Reference Manual (available from the 
+      <a href="manuals/index.html">Documentation page</a>). Development 
+      notes are available on the 
+      <a class="external" href="http://wiki.apache.org/db-derby/OLAPRowNumber">OLAPRowNumber 
+      wiki page</a>.
+      </p>
+<p>
+      The <span class="codefrag">LIMIT</span> keyword is not defined in the SQL standard, 
+      and is currently not supported.
       </p>
 </div>
-<a name="N1018E"></a><a name="schema_exist"></a>
+<a name="N101F4"></a><a name="schema_exist"></a>
 <h4 class="faq">5.3. 
       Why do I get the error 'schema does not exist'?
       </h4>
@@ -810,7 +883,7 @@
       to create in a particular schema and no explicit CREATE SCHEMA was issued.
       </p>
 </div>
-<a name="N1019F"></a><a name="debug_lock_timeout"></a>
+<a name="N10205"></a><a name="debug_lock_timeout"></a>
 <h4 class="faq">5.4. 
       I get a lock timeout error. How do I debug this?
       </h4>
@@ -839,7 +912,7 @@
       to narrow down which statements may be holding the locks.
       </p>
 </div>
-<a name="N101B9"></a><a name="identity"></a>
+<a name="N1021F"></a><a name="identity"></a>
 <h4 class="faq">5.5. 
       Can Derby generate unique identifiers like sequences?
       </h4>
@@ -856,9 +929,9 @@
        function returns the most recently assigned number.
       </p>
 </div>
-<a name="N101C9"></a><a name="users_client"></a>
+<a name="N1022F"></a><a name="users_client"></a>
 <h3 class="boxed">6. Using Derby: Client Programming</h3>
-<a name="N101CD"></a><a name="clientserv"></a>
+<a name="N10233"></a><a name="clientserv"></a>
 <h4 class="faq">6.1. 
       Can you use Derby for client-server applications?
       </h4>
@@ -879,7 +952,7 @@
       <a class="external" href="http://sequoia.continuent.org/HomePage">Sequoia</a>.
       </p>
 </div>
-<a name="N101E9"></a><a name="netclient"></a>
+<a name="N1024F"></a><a name="netclient"></a>
 <h4 class="faq">6.2. 
       Does Derby include a client-server ("network") jdbc driver?
       </h4>
@@ -895,7 +968,7 @@
       and it became available starting with Derby 10.1.
       </p>
 </div>
-<a name="N101F5"></a><a name="db2_jcc_errors"></a>
+<a name="N1025B"></a><a name="db2_jcc_errors"></a>
 <h4 class="faq">6.3. 
       How can I get the message text for an error using the DB2 JDBC
       Universal Driver?
@@ -913,7 +986,7 @@
       mail list topic.
       </p>
 </div>
-<a name="N10204"></a><a name="derby_xa"></a>
+<a name="N1026A"></a><a name="derby_xa"></a>
 <h4 class="faq">6.4. 
       Can you execute a query that spans two Derby databases 
       across different JVMs?
@@ -929,7 +1002,7 @@
       <a href="http://db.apache.org/derby/docs/10.2/adminguide/cadminapps811478.html">Admin and Server Guide</a>.
       </p>
 </div>
-<a name="N10210"></a><a name="derby_faster"></a>
+<a name="N10276"></a><a name="derby_faster"></a>
 <h4 class="faq">6.5. 
       Are there any tips to make Derby go faster?
       </h4>
@@ -964,7 +1037,7 @@
       
 </ol>
 </div>
-<a name="N10229"></a><a name="jdbc_lob"></a>
+<a name="N1028F"></a><a name="jdbc_lob"></a>
 <h4 class="faq">6.6. 
       Where is an example that shows how to insert a CLOB?
       </h4>
@@ -982,7 +1055,7 @@
       provides another example.
       </p>
 </div>
-<a name="N1023C"></a><a name="jdbc4"></a>
+<a name="N102A2"></a><a name="jdbc4"></a>
 <h4 class="faq">6.7. 
       Does Derby support JDBC 4.0?
       </h4>

Modified: db/derby/site/trunk/src/documentation/content/xdocs/faq.xml
URL: http://svn.apache.org/viewvc/db/derby/site/trunk/src/documentation/content/xdocs/faq.xml?rev=654105&r1=654104&r2=654105&view=diff
==============================================================================
--- db/derby/site/trunk/src/documentation/content/xdocs/faq.xml (original)
+++ db/derby/site/trunk/src/documentation/content/xdocs/faq.xml Wed May  7 06:50:18 2008
@@ -412,7 +412,60 @@
       <answer>
       <p>
       Derby supports limiting the number of rows returned by a query through
-      <a href="http://mail-archives.apache.org/mod_mbox/db-derby-dev/200411.mbox/%3c41A95632.3010301@debrunners.com%3e">JDBC</a>.
+      <a href="http://mail-archives.apache.org/mod_mbox/db-derby-dev/200411.mbox/%3c41A95632.3010301@debrunners.com%3e">JDBC</a>. For example, to fetch the first 5 rows of a large table:
+      </p>
+      <!-- Using <code> and <br/> tags etc. as a workaround for including 
+           multi-line formatted code examples, since Forrest's XML 
+           validation fails when using other HTML tags (such as <pre>) in 
+           this DTD element.
+      -->
+      <p>
+      <code>Statement stmt = con.createStatement();</code><br/>
+      <code>stmt.setMaxRows(5);</code><br/>
+      <code>ResultSet rs = stmt.executeQuery("SELECT * FROM myLargeTable");</code><br/>
+      </p>
+      <p>
+      Some related tuning tips are available in 
+      <a href="http://www.onjava.com/pub/a/onjava/2007/01/31/tuning-derby.html?page=1">this 
+      external article</a>.
+      </p>
+      <p>
+      Starting with the 10.4.1.3 release Derby also supports limiting the number of
+      rows using the <code>ROW_NUMBER</code> function. 
+      </p>
+      <p>
+      For example, to fetch the first 5
+      rows of a large table:
+      </p>
+      <p>
+      <code>SELECT * FROM (</code><br/>
+      &nbsp;&nbsp;&nbsp;&nbsp;<code>SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*</code><br/>
+      &nbsp;&nbsp;&nbsp;&nbsp;<code>FROM myLargeTable</code><br/>
+      <code>) AS tmp</code><br/>
+      <code>WHERE rownum &lt;= 5;</code><br/>
+      </p>
+      <p>
+      The <code>ROW_NUMBER</code> function can also be used to select a 
+      limited number of rows starting with an offset, for example:
+      </p>
+      <p>
+      <code>SELECT * FROM (</code><br/>
+      &nbsp;&nbsp;&nbsp;&nbsp;<code>SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.*</code><br/>
+      &nbsp;&nbsp;&nbsp;&nbsp;<code>FROM myLargeTable</code><br/>
+      <code>) AS tmp</code><br/>
+      <code>WHERE rownum &gt; 200000 AND rownum &lt;= 200005;</code><br/>
+      </p>
+      <p>
+      For more information, refer to the ROW_NUMBER built-in function in 
+      the Derby Reference Manual (available from the 
+      <a href="manuals/index.html">Documentation page</a>). Development 
+      notes are available on the 
+      <a href="http://wiki.apache.org/db-derby/OLAPRowNumber">OLAPRowNumber 
+      wiki page</a>.
+      </p>
+      <p>
+      The <code>LIMIT</code> keyword is not defined in the SQL standard, 
+      and is currently not supported.
       </p>
       </answer>
     </faq>