You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by da...@apache.org on 2009/08/24 20:55:00 UTC
svn commit: r807337 - in /db/derby/code/trunk/java:
engine/org/apache/derby/iapi/sql/execute/
engine/org/apache/derby/impl/sql/compile/
engine/org/apache/derby/impl/sql/execute/ engine/org/apache/derby/loc/
shared/org/apache/derby/shared/common/referen...
Author: dag
Date: Mon Aug 24 18:55:00 2009
New Revision: 807337
URL: http://svn.apache.org/viewvc?rev=807337&view=rev
Log:
DERBY-4208 Parameters ? with OFFSET and/or FETCH
This patch implements the use of dynamic parameters with OFFSET/FETCH and adds new tests.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowCountNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java
db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java Mon Aug 24 18:55:00 2009
@@ -1620,8 +1620,9 @@
* which provides the context for the row
* allocation operation
* @param resultSetNumber The resultSetNumber for the ResultSet
- * @param offset The offset value (0 by default)
- * @param fetchFirst The fetch first value (-1 if not in use)
+ * @param offsetMethod The OFFSET parameter was specified
+ * @param fetchFirstMethod The FETCH FIRST/NEXT parameter was
+ * specified
* @param optimizerEstimatedRowCount
* Estimated total # of rows by optimizer
* @param optimizerEstimatedCost
@@ -1633,8 +1634,8 @@
NoPutResultSet source,
Activation activation,
int resultSetNumber,
- long offset,
- long fetchFirst,
+ GeneratedMethod offsetMethod,
+ GeneratedMethod fetchFirstMethod,
double optimizerEstimatedRowCount,
double optimizerEstimatedCost) throws StandardException;
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CursorNode.java Mon Aug 24 18:55:00 2009
@@ -23,6 +23,7 @@
import java.util.ArrayList;
import java.util.Vector;
+import java.sql.Types;
import org.apache.derby.iapi.error.StandardException;
import org.apache.derby.iapi.reference.SQLState;
@@ -35,6 +36,8 @@
import org.apache.derby.impl.sql.CursorInfo;
import org.apache.derby.impl.sql.CursorTableReference;
import org.apache.derby.iapi.types.DataValueDescriptor;
+import org.apache.derby.iapi.types.DataTypeDescriptor;
+import org.apache.derby.iapi.types.TypeId;
/**
* A CursorNode represents a result set that can be returned to a client.
@@ -53,8 +56,8 @@
private String name;
private OrderByList orderByList;
- private NumericConstantNode offset; // <result offset clause> value
- private NumericConstantNode fetchFirst; // <fetch first clause> value
+ private ValueNode offset; // <result offset clause> value
+ private ValueNode fetchFirst; // <fetch first clause> value
private String statementType;
private int updateMode;
private boolean needTarget;
@@ -106,8 +109,8 @@
this.name = (String) name;
this.statementType = (String) statementType;
this.orderByList = (OrderByList) orderByList;
- this.offset = (NumericConstantNode)offset;
- this.fetchFirst = (NumericConstantNode)fetchFirst;
+ this.offset = (ValueNode)offset;
+ this.fetchFirst = (ValueNode)fetchFirst;
this.updateMode = ((Integer) updateMode).intValue();
this.updatableColumns = (Vector) updatableColumns;
@@ -362,7 +365,7 @@
private void bindOffsetFetch() throws StandardException {
- if (offset != null) {
+ if (offset instanceof ConstantNode) {
DataValueDescriptor dvd = ((ConstantNode)offset).getValue();
long val = dvd.getLong();
@@ -371,9 +374,16 @@
SQLState.LANG_INVALID_ROW_COUNT_OFFSET,
Long.toString(val) );
}
+ } else if (offset instanceof ParameterNode) {
+ offset.
+ setType(new DataTypeDescriptor(
+ TypeId.getBuiltInTypeId(Types.BIGINT),
+ false /* ignored tho; ends up nullable,
+ so we test for NULL at execute time */));
}
- if (fetchFirst != null) {
+
+ if (fetchFirst instanceof ConstantNode) {
DataValueDescriptor dvd = ((ConstantNode)fetchFirst).getValue();
long val = dvd.getLong();
@@ -382,6 +392,12 @@
SQLState.LANG_INVALID_ROW_COUNT_FIRST,
Long.toString(val) );
}
+ } else if (fetchFirst instanceof ParameterNode) {
+ fetchFirst.
+ setType(new DataTypeDescriptor(
+ TypeId.getBuiltInTypeId(Types.BIGINT),
+ false /* ignored tho; ends up nullable,
+ so we test for NULL at execute time*/));
}
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowCountNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowCountNode.java?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowCountNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowCountNode.java Mon Aug 24 18:55:00 2009
@@ -27,6 +27,8 @@
import org.apache.derby.iapi.services.sanity.SanityManager;
import org.apache.derby.iapi.reference.ClassName;
import org.apache.derby.iapi.services.classfile.VMOpcode;
+import org.apache.derby.iapi.types.SQLLongint;
+import org.apache.derby.iapi.reference.ClassName;
/**
* The result set generated by this node (RowCountResultSet) implements the
@@ -93,19 +95,23 @@
acb.pushThisAsActivation(mb); // arg2
mb.push(resultSetNumber); // arg3
+ boolean dynamicOffset = false;
+ boolean dynamicFetchFirst = false;
+
+ // arg4
+ if (offset != null) {
+ generateExprFun(acb, mb, offset);
+ } else {
+ mb.pushNull(ClassName.GeneratedMethod);
+ }
- // If OFFSET is not given, we pass in the default, i.e 0.
- long offsetVal =
- (offset != null) ?
- ((ConstantNode)offset).getValue().getLong() : 0;
-
- // If FETCH FIRST is not given, we pass in -1 to RowCountResultSet.
- long fetchFirstVal =
- (fetchFirst != null) ?
- ((ConstantNode)fetchFirst).getValue().getLong() : -1;
+ // arg5
+ if (fetchFirst != null) {
+ generateExprFun(acb, mb, fetchFirst);
+ } else {
+ mb.pushNull(ClassName.GeneratedMethod);
+ }
- mb.push(offsetVal); // arg4
- mb.push(fetchFirstVal); // arg5
mb.push(costEstimate.rowCount()); // arg6
mb.push(costEstimate.getEstimatedCost()); // arg7
@@ -117,6 +123,31 @@
}
+ private void generateExprFun(
+ ExpressionClassBuilder ecb,
+ MethodBuilder mb,
+ ValueNode vn) throws StandardException {
+
+ // Generates:
+ // Object exprFun { }
+ MethodBuilder exprFun = ecb.newExprFun();
+
+ /* generates:
+ * return <dynamic parameter.generate(ecb)>;
+ * and adds it to exprFun
+ */
+ vn.generateExpression(ecb, exprFun);
+ exprFun.methodReturn();
+
+ // we are done modifying exprFun, complete it.
+ exprFun.complete();
+
+ // Pass in the method that will be used to evaluates the dynamic
+ // parameter in RowCountResultSet.
+ ecb.pushMethodReference(mb, exprFun);
+ }
+
+
/**
* Convert this object to a String. See comments in QueryTreeNode.java
* for how this should be done for tree printing.
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Mon Aug 24 18:55:00 2009
@@ -497,7 +497,6 @@
DataTypeDescriptor[] descriptors = cc.getParameterTypes();
ParameterNode newNode;
- ParameterNode oldNode;
int paramCount;
/*
@@ -3115,8 +3114,8 @@
int isolationLevel = ExecutionContext.UNSPECIFIED_ISOLATION_LEVEL;
CursorNode retval;
OrderByList orderCols = null;
- NumericConstantNode offset = null;
- NumericConstantNode fetchFirst = null;
+ ValueNode offset = null;
+ ValueNode fetchFirst = null;
}
{
queryExpression = queryExpression(null, NO_SET_OP)
@@ -8069,18 +8068,22 @@
/*
* <A NAME="offsetClause">offsetClause</A>
*/
-NumericConstantNode
+ValueNode
offsetClause() throws StandardException :
{
- NumericConstantNode result;
+ ValueNode result = null;
}
{
// Since OFFSET is not yet a reserved keyword, cf. disambiguation
// look-ahead for it w.r.t. offsetClause in method nonReservedKeyword.
// This solves the shift/reduce conflict, and allows us to use OFFSET as an
// identifier in all contexts.
- <OFFSET> result = intLiteral() ( <ROW> | <ROWS> )
- {
+ <OFFSET>
+ ( result = intLiteral()
+ | result = dynamicParameterSpecification()
+ )
+ ( <ROW> | <ROWS> )
+ {
return result;
}
}
@@ -8089,16 +8092,18 @@
/*
* <A NAME="fetchFirstClause">fetchFirstClause</A>
*/
-NumericConstantNode
+ValueNode
fetchFirstClause() throws StandardException :
{
// The default number of rows to fetch if the literal is omitted is 1:
- NumericConstantNode result = getNumericNode("1", true);
+ ValueNode result = getNumericNode("1", true);
}
{
<FETCH> ( <FIRST> | <NEXT> )
- [ result = intLiteral() ] ( <ROW> | <ROWS> ) <ONLY>
- {
+ [ result = intLiteral()
+ | result = dynamicParameterSpecification()
+ ] ( <ROW> | <ROWS> ) <ONLY>
+ {
return result;
}
}
@@ -13893,7 +13898,8 @@
getToken(1).kind == OFFSET &&
!(getToken(2).kind == PLUS_SIGN ||
getToken(2).kind == MINUS_SIGN ||
- getToken(2).kind == EXACT_NUMERIC)
+ getToken(2).kind == EXACT_NUMERIC ||
+ getToken(2).kind == QUESTION_MARK)
})
tok = <OFFSET>
| tok = <OLD>
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java Mon Aug 24 18:55:00 2009
@@ -1273,8 +1273,8 @@
NoPutResultSet source,
Activation activation,
int resultSetNumber,
- long offset,
- long fetchFirst,
+ GeneratedMethod offsetMethod,
+ GeneratedMethod fetchFirstMethod,
double optimizerEstimatedRowCount,
double optimizerEstimatedCost)
throws StandardException
@@ -1282,8 +1282,8 @@
return new RowCountResultSet(source,
activation,
resultSetNumber,
- offset,
- fetchFirst,
+ offsetMethod,
+ fetchFirstMethod,
optimizerEstimatedRowCount,
optimizerEstimatedCost);
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java Mon Aug 24 18:55:00 2009
@@ -21,6 +21,7 @@
package org.apache.derby.impl.sql.execute;
+import org.apache.derby.iapi.reference.SQLState;
import org.apache.derby.iapi.sql.conn.StatementContext;
import org.apache.derby.iapi.sql.execute.CursorResultSet;
import org.apache.derby.iapi.sql.execute.ExecRow;
@@ -28,7 +29,9 @@
import org.apache.derby.iapi.sql.Activation;
import org.apache.derby.iapi.error.StandardException;
import org.apache.derby.iapi.services.sanity.SanityManager;
+import org.apache.derby.iapi.services.loader.GeneratedMethod;
import org.apache.derby.iapi.types.RowLocation;
+import org.apache.derby.iapi.types.DataValueDescriptor;
@@ -49,8 +52,10 @@
// life of object.
final NoPutResultSet source;
final private boolean runTimeStatsOn;
- final private long offset;
- final private long fetchFirst;
+ private long offset;
+ private long fetchFirst;
+ final private GeneratedMethod offsetMethod;
+ final private GeneratedMethod fetchFirstMethod;
/**
* True if we haven't yet fetched any rows from this result set.
@@ -72,8 +77,8 @@
* which provides the context for the row
* allocation operation
* @param resultSetNumber The resultSetNumber for the ResultSet
- * @param offset The offset value (0 by default)
- * @param fetchFirst The fetch first value (-1 if not in use)
+ * @param offsetMethod Generated method
+ * @param fetchFirstMethod Generated method
* @param optimizerEstimatedRowCount
* Estimated total # of rows by optimizer
* @param optimizerEstimatedCost
@@ -84,21 +89,22 @@
(NoPutResultSet s,
Activation a,
int resultSetNumber,
- long offset,
- long fetchFirst,
+ GeneratedMethod offsetMethod,
+ GeneratedMethod fetchFirstMethod,
double optimizerEstimatedRowCount,
double optimizerEstimatedCost)
- throws StandardException
- {
+ throws StandardException {
+
super(a,
resultSetNumber,
optimizerEstimatedRowCount,
optimizerEstimatedCost);
+ this.offsetMethod = offsetMethod;
+ this.fetchFirstMethod = fetchFirstMethod;
+
source = s;
- this.offset = offset;
- this.fetchFirst = fetchFirst;
virginal = true;
rowsFetched = 0;
@@ -173,26 +179,77 @@
beginTime = getCurrentTimeMillis();
- if (virginal && offset > 0) {
- // Only skip rows the first time around
- virginal = false;
+ if (virginal) {
+ if (offsetMethod != null) {
+ DataValueDescriptor offVal
+ = (DataValueDescriptor)offsetMethod.invoke(activation);
+
+ if (offVal.isNotNull().getBoolean()) {
+ offset = offVal.getLong();
+
+ if (offset < 0) {
+ throw StandardException.newException(
+ SQLState.LANG_INVALID_ROW_COUNT_OFFSET,
+ Long.toString(offset));
+ } else {
+ offset = offVal.getLong();
+ }
+ } else {
+ throw StandardException.newException(
+ SQLState.LANG_ROW_COUNT_OFFSET_FIRST_IS_NULL,
+ "OFFSET");
+ }
+ } else {
+ // not given
+ offset = 0;
+ }
- long offsetCtr = offset;
- do {
- result = source.getNextRowCore();
- offsetCtr--;
-
- if (result != null && offsetCtr >= 0) {
- rowsFiltered++;
+ if (fetchFirstMethod != null) {
+ DataValueDescriptor fetchFirstVal
+ = (DataValueDescriptor)fetchFirstMethod.invoke(activation);
+
+ if (fetchFirstVal.isNotNull().getBoolean()) {
+
+ fetchFirst = fetchFirstVal.getLong();
+
+ if (fetchFirst < 1) {
+ throw StandardException.newException(
+ SQLState.LANG_INVALID_ROW_COUNT_FIRST,
+ Long.toString(fetchFirst));
+ }
} else {
- break;
+ throw StandardException.newException(
+ SQLState.LANG_ROW_COUNT_OFFSET_FIRST_IS_NULL,
+ "FETCH FIRST/NEXT");
}
+ }
- } while (true);
+ if (offset > 0) {
+ // Only skip rows the first time around
+ virginal = false;
+
+ long offsetCtr = offset;
+
+ do {
+ result = source.getNextRowCore();
+ offsetCtr--;
+
+ if (result != null && offsetCtr >= 0) {
+ rowsFiltered++;
+ } else {
+ break;
+ }
+ } while (true);
+ } else {
+ if (fetchFirstMethod != null && rowsFetched >= fetchFirst) {
+ result = null;
+ } else {
+ result = source.getNextRowCore();
+ }
+ }
} else {
-
- if (fetchFirst != -1 && rowsFetched >= fetchFirst) {
+ if (fetchFirstMethod != null && rowsFetched >= fetchFirst) {
result = null;
} else {
result = source.getNextRowCore();
Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Mon Aug 24 18:55:00 2009
@@ -764,6 +764,12 @@
<text>Invalid row count for FIRST/NEXT, must be >= 1.</text>
</msg>
+ <msg>
+ <name>2201Z</name>
+ <text>NULL value not allowed for {0} argument.</text>
+ <arg>string</arg>
+ </msg>
+
</family>
Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java (original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java Mon Aug 24 18:55:00 2009
@@ -714,6 +714,7 @@
String LANG_ESCAPE_IS_NULL = "22501";
String LANG_INVALID_ROW_COUNT_OFFSET = "2201X";
String LANG_INVALID_ROW_COUNT_FIRST = "2201W";
+ String LANG_ROW_COUNT_OFFSET_FIRST_IS_NULL = "2201Z";
/*
** Integrity violations.
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java Mon Aug 24 18:55:00 2009
@@ -27,6 +27,7 @@
import java.sql.Statement;
import java.sql.Types;
import java.sql.PreparedStatement;
+import java.sql.ParameterMetaData ;
import junit.framework.Test;
import junit.framework.TestSuite;
@@ -41,6 +42,14 @@
*/
public class OffsetFetchNextTest extends BaseJDBCTestCase {
+ private final static String LANG_FORMAT_EXCEPTION = "22018";
+ private final static String LANG_INTEGER_LITERAL_EXPECTED = "42X20";
+ private final static String LANG_INVALID_ROW_COUNT_FIRST = "2201W";
+ private final static String LANG_INVALID_ROW_COUNT_OFFSET = "2201X";
+ private final static String LANG_MISSING_PARMS = "07000";
+ private final static String LANG_SYNTAX_ERROR = "42X01";
+ private final static String LANG_ROW_COUNT_OFFSET_FIRST_IS_NULL = "2201Z";
+
public OffsetFetchNextTest(String name) {
super(name);
}
@@ -103,21 +112,21 @@
// Wrong range in row count argument
- assertStatementError("2201X", st,
+ assertStatementError(LANG_INVALID_ROW_COUNT_OFFSET, st,
"select * from t1 offset -1 rows");
- assertStatementError("2201W", st,
+ assertStatementError(LANG_INVALID_ROW_COUNT_FIRST, st,
"select * from t1 fetch first 0 rows only");
- assertStatementError("2201W", st,
+ assertStatementError(LANG_INVALID_ROW_COUNT_FIRST, st,
"select * from t1 fetch first -1 rows only");
// Wrong type in row count argument
- assertStatementError("42X20", st,
+ assertStatementError(LANG_INTEGER_LITERAL_EXPECTED, st,
"select * from t1 fetch first 3.14 rows only");
// Wrong order of clauses
- assertStatementError("42X01", st,
+ assertStatementError(LANG_SYNTAX_ERROR, st,
"select * from t1 " +
"fetch first 0 rows only offset 0 rows");
}
@@ -660,6 +669,136 @@
}
}
+ /**
+ * Test dynamic arguments
+ */
+ public void testDynamicArgs() throws SQLException {
+ // Check look-ahead also for ? in grammar since offset is not reserved
+ PreparedStatement ps = prepareStatement(
+ "select * from t1 offset ? rows");
+
+ // Check range errors
+ ps = prepareStatement(
+ "select * from t1 order by b " +
+ "offset ? rows fetch next ? rows only");
+
+ ps.setInt(1, 0);
+ assertPreparedStatementError(LANG_MISSING_PARMS, ps);
+
+ ps.setInt(1, -1);
+ ps.setInt(2, 2);
+ assertPreparedStatementError(LANG_INVALID_ROW_COUNT_OFFSET, ps);
+
+ ps.setInt(1, 0);
+ ps.setInt(2, 0);
+ assertPreparedStatementError(LANG_INVALID_ROW_COUNT_FIRST, ps);
+
+ // Check non-integer values
+ try {
+ ps.setString(1, "aaa");
+ } catch (SQLException e) {
+ assertSQLState(LANG_FORMAT_EXCEPTION, e);
+ }
+
+ try {
+ ps.setString(2, "aaa");
+ } catch (SQLException e) {
+ assertSQLState(LANG_FORMAT_EXCEPTION, e);
+ }
+
+
+ // A normal case
+ String[][] expected = {{"1", "3"}, {"1", "4"}};
+ for (int i = 0; i < 2; i++) {
+ ps.setInt(1,2);
+ ps.setInt(2,2);
+ JDBC.assertFullResultSet(ps.executeQuery(), expected);
+ }
+
+ // Now, note that since we now have different values for offset and
+ // fetch first, we also exercise reusing the result set for this
+ // prepared statement (i.e. the values are computed at execution time,
+ // not at result set generation time). Try long value for change.
+ ps.setLong(1, 1L);
+ ps.setInt(2, 3);
+ expected = new String[][]{{"1", "2"}, {"1", "3"}, {"1", "4"}};
+ JDBC.assertFullResultSet(ps.executeQuery(), expected);
+
+
+ // Try a large number
+ ps.setLong(1, Integer.MAX_VALUE * 2L);
+ ps.setInt(2, 5);
+ JDBC.assertEmpty(ps.executeQuery());
+
+ // Mix of prepared and not
+ ps = prepareStatement(
+ "select * from t1 order by b " +
+ "offset ? rows fetch next 3 rows only");
+ ps.setLong(1, 1L);
+ JDBC.assertFullResultSet(ps.executeQuery(), expected);
+
+ ps = prepareStatement(
+ "select * from t1 order by b " +
+ "offset 4 rows fetch next ? rows only");
+ ps.setLong(1, 1L);
+ JDBC.assertFullResultSet(ps.executeQuery(),
+ new String[][]{{"1", "5"}});
+
+ // Mix of other dyn args and ours:
+ ps = prepareStatement(
+ "select * from t1 where a = ? order by b " +
+ "offset ? rows fetch next 3 rows only");
+ ps.setInt(1, 1);
+ ps.setLong(2, 1L);
+ JDBC.assertFullResultSet(ps.executeQuery(), expected);
+
+ ps = prepareStatement(
+ "select * from t1 where a = ? order by b " +
+ "offset 1 rows fetch next ? rows only");
+ ps.setInt(1, 1);
+ ps.setLong(2, 2L);
+ expected = new String[][]{{"1", "2"}, {"1", "3"}};
+ JDBC.assertFullResultSet(ps.executeQuery(), expected);
+
+
+ // NULLs not allowed (Note: parameter metadata says "isNullable" for
+ // all ? args in Derby...)
+ ps = prepareStatement(
+ "select * from t1 order by b " +
+ "offset ? rows fetch next ? rows only");
+ ps.setNull(1, Types.BIGINT);
+ ps.setInt(2, 2);
+ assertPreparedStatementError(LANG_ROW_COUNT_OFFSET_FIRST_IS_NULL, ps);
+
+ ps.setInt(1,1);
+ ps.setNull(2, Types.BIGINT);
+ assertPreparedStatementError(LANG_ROW_COUNT_OFFSET_FIRST_IS_NULL, ps);
+
+ ps.close();
+ }
+
+ /**
+ * Test dynamic arguments
+ */
+ public void testDynamicArgsMetaData() throws SQLException {
+ PreparedStatement ps = prepareStatement(
+ "select * from t1 where a = ? order by b " +
+ "offset ? rows fetch next ? rows only");
+
+ ParameterMetaData pmd = ps.getParameterMetaData();
+ int[] expectedTypes = { Types.INTEGER, Types.BIGINT, Types.BIGINT };
+
+ for (int i = 0; i < 3; i++) {
+ assertEquals("Unexpected parameter type",
+ expectedTypes[i], pmd.getParameterType(i+1));
+ assertEquals("Derby ? args are nullable",
+ // Why is that? Cf. logic in ParameterNode.setType
+ ParameterMetaData.parameterNullable,
+ pmd.isNullable(i+1));
+ }
+ ps.close();
+ }
+
private void queryAndCheck(
Statement stm,
String queryText,
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java?rev=807337&r1=807336&r2=807337&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java Mon Aug 24 18:55:00 2009
@@ -1014,7 +1014,29 @@
}
}
-
+
+ /**
+ * Assert that the query fails (either in execution, or retrieval of
+ * results--doesn't matter) and throws a SQLException with the expected
+ * state and error code
+ *
+ * Parameters must have been already bound, if any.
+ *
+ * @param sqlState expected sql state.
+ * @param ps PreparedStatement query object to execute.
+ */
+ public static void assertPreparedStatementError(String sqlState,
+ PreparedStatement ps) {
+ try {
+ boolean haveRS = ps.execute();
+ fetchAndDiscardAllResults(ps, haveRS);
+ fail("Expected error '" + sqlState +
+ "' but no error was thrown.");
+ } catch (SQLException se) {
+ assertSQLState(sqlState, se);
+ }
+ }
+
/**
* Assert that execution of the received PreparedStatement
* object fails (either in execution or when retrieving