Hi
This is a patch which implements typed arrays in function results.
It removes the "feature" where elements of function array results are converted
to columns.
Regards, Noel.
Lukas Eder wrote:
>> As for me issue 3 is definitely a bug. But this happens because CALL
>> statement translates elements of array result to columns. For example:
>> CALL (1,2,3) will return row with 3 columns. Indeed this behavior was
>> introduced before me (see rev. 2427 Call.query) and I'm not a fan of
>> this feature at all too but I've kept backward compatibility at time
>> of my changes.
> Ah, I understand. So with the current versions of H2, it is difficult
> to retrieve an array from a stored function correctly, if using a
> CallableStatement... While I register only one OUT parameter for the
> return value with java.sql.Types.ARRAY as a type, I have to retrieve
> three (or five or seven) values from the CallableStatement.
>
> I understand backwards compatibility thoughts. But this design will
> cause major trouble, should you ever decide to allow for regular OUT
> parameters as other databases do. Right now, this is not an issue, but
> HSQLDB also went from Java-based stored functions to a PL/SQL-like
> syntax with regular OUT parameters. Besides, the current behaviour is
> inconsistent with calling the function in a normal PreparedStatement
> as in "select f_arrays1(?) from dual", where an actual Object[] array
> is returned.
>
> In my case, I can hide these facts from client code and always use the
> PreparedStatement syntax. But for H2, I suggest you remove that
> "feature" in the next major release. I doubt that a lot of people will
> rely on that feature, since it isn't really documented. But that's
> your decision.
>
> Cheers
> Lukas
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
Index: src/main/org/h2/command/dml/Call.java
===================================================================
--- src/main/org/h2/command/dml/Call.java (revision 3533)
+++ src/main/org/h2/command/dml/Call.java (working copy)
@@ -69,16 +69,6 @@
case Value.RESULT_SET:
ResultSet rs = ((ValueResultSet) v).getResultSet();
return LocalResult.read(session, rs, maxrows);
- case Value.ARRAY:
- Value[] list = ((ValueArray) v).getList();
- Expression[] expr = expression.getExpressionColumns(session);
- if (expr.length != list.length) {
- throw DbException.throwInternalError();
- }
- LocalResult result = new LocalResult(session, expr, expr.length);
- result.addRow(list);
- result.done();
- return result;
}
LocalResult result = new LocalResult(session, expressions, 1);
Value[] row = { v };
Index: src/main/org/h2/engine/FunctionAlias.java
===================================================================
--- src/main/org/h2/engine/FunctionAlias.java (revision 3533)
+++ src/main/org/h2/engine/FunctionAlias.java (working copy)
@@ -11,6 +11,7 @@
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.sql.Connection;
+import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import org.h2.Driver;
@@ -30,6 +31,7 @@
import org.h2.util.Utils;
import org.h2.value.DataType;
import org.h2.value.Value;
+import org.h2.value.ValueArray;
import org.h2.value.ValueNull;
/**
@@ -352,6 +354,14 @@
Object o;
if (Value.class.isAssignableFrom(paramClass)) {
o = v;
+ } else if (v.getType() == Value.ARRAY && paramClass.isArray()
&& paramClass.getComponentType() != Object.class) {
+ ValueArray argVA = (ValueArray) v;
+ Object[] objArray = (Object[])
Array.newInstance(paramClass.getComponentType(), argVA.getList().length);
+ int componentType =
DataType.getTypeFromClass(paramClass.getComponentType());
+ for (int i=0; i<objArray.length; i++) {
+ objArray[i] =
argVA.getList()[i].convertTo(componentType).getObject();
+ }
+ o = objArray;
} else {
v = v.convertTo(type);
o = v.getObject();
@@ -359,10 +369,10 @@
if (o == null) {
if (paramClass.isPrimitive()) {
if (columnList) {
- // if the column list is requested, the parameters
may
- // be null
- // need to set to default value,
- // otherwise the function can't be called at all
+ // If the column list is requested, the parameters
may
+ // be null.
+ // Need to set to default value,
+ // otherwise the function can't be called at all.
o =
DataType.getDefaultForPrimitiveType(paramClass);
} else {
// NULL for a java primitive: return NULL
@@ -409,6 +419,15 @@
if (Value.class.isAssignableFrom(method.getReturnType())) {
return (Value) returnValue;
}
+ if (dataType == Value.ARRAY) {
+ Object[] returnValueArray = (Object[]) returnValue;
+ Value[] vals = new Value[returnValueArray.length];
+ for (int i=0; i<returnValueArray.length; i++) {
+ vals[i] = DataType.convertToValue(session,
returnValueArray[i], Value.ARRAY);
+ }
+ ValueArray va =
ValueArray.get(returnValue.getClass().getComponentType(), vals);
+ return va;
+ }
Value ret = DataType.convertToValue(session, returnValue,
dataType);
return ret.convertTo(dataType);
} finally {
Index: src/main/org/h2/value/ValueArray.java
===================================================================
--- src/main/org/h2/value/ValueArray.java (revision 3533)
+++ src/main/org/h2/value/ValueArray.java (working copy)
@@ -6,6 +6,7 @@
*/
package org.h2.value;
+import java.lang.reflect.Array;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import org.h2.engine.Constants;
@@ -18,12 +19,18 @@
*/
public class ValueArray extends Value {
+ private final Class<?> componentType;
private final Value[] values;
private int hash;
- private ValueArray(Value[] list) {
+ private ValueArray(Class<?> componentType, Value[] list) {
+ this.componentType = componentType;
this.values = list;
}
+
+ private ValueArray(Value[] list) {
+ this(Object.class, list);
+ }
/**
* Get or create a array value for the given value array.
@@ -36,6 +43,17 @@
return new ValueArray(list);
}
+ /**
+ * Get or create a array value for the given value array.
+ * Do not clone the data.
+ *
+ * @param list the value array
+ * @return the value
+ */
+ public static ValueArray get(Class<?> componentType, Value[] list) {
+ return new ValueArray(componentType, list);
+ }
+
public int hashCode() {
if (hash != 0) {
return hash;
@@ -55,6 +73,10 @@
public int getType() {
return Value.ARRAY;
}
+
+ public Class<?> getComponentType() {
+ return componentType;
+ }
public long getPrecision() {
long p = 0;
@@ -94,7 +116,7 @@
public Object getObject() {
int len = values.length;
- Object[] list = new Object[len];
+ Object[] list = (Object[]) Array.newInstance(componentType, len);
for (int i = 0; i < len; i++) {
list[i] = values[i].getObject();
}
Index: src/test/org/h2/test/TestBase.java
===================================================================
--- src/test/org/h2/test/TestBase.java (revision 3533)
+++ src/test/org/h2/test/TestBase.java (working copy)
@@ -579,6 +579,30 @@
}
/**
+ * Check if two values are equal, and if not throw an exception.
+ *
+ * @param expected the expected value
+ * @param actual the actual value
+ * @throws AssertionError if the values are not equal
+ */
+ public void assertEquals(Object[] expected, Object[] actual) {
+ if (expected == null || actual == null) {
+ assertTrue(expected == actual);
+ return;
+ }
+ assertEquals(expected.length, actual.length);
+ for (int i = 0; i < expected.length; i++) {
+ if (expected[i] == null || actual[i] == null) {
+ if (expected[i] != actual[i]) {
+ fail("[" + i + "]: expected: " + expected[i] + " actual: "
+ actual[i]);
+ }
+ } else if (!expected[i].equals(actual[i])) {
+ fail("[" + i + "]: expected: " + expected[i] + " actual: " +
actual[i]);
+ }
+ }
+ }
+
+ /**
* Check if two readers are equal, and if not throw an exception.
*
* @param expected the expected value
@@ -677,6 +701,23 @@
}
/**
+ * Check if two values are equal, and if not throw an exception.
+ *
+ * @param expected the expected value
+ * @param actual the actual value
+ * @throws AssertionError if the values are not equal
+ */
+ protected void assertEquals(Object expected, Object actual) {
+ if (expected == null || actual == null) {
+ if (expected != actual) {
+ fail("expected: " + expected + " actual: " + actual);
+ }
+ } else if (!expected.equals(actual)) {
+ fail("expected: " + expected + " actual: " + actual);
+ }
+ }
+
+ /**
* Check if two result sets are equal, and if not throw an exception.
*
* @param message the message to use if the check fails
@@ -775,6 +816,21 @@
* @param actual the actual value
* @throws AssertionError if the values are not equal
*/
+ protected void assertEquals(Double expected, Double actual) {
+ if (expected == null || actual == null) {
+ assertTrue(expected == null && actual == null);
+ } else {
+ assertEquals(expected.doubleValue(), actual.doubleValue());
+ }
+ }
+
+ /**
+ * Check if two values are equal, and if not throw an exception.
+ *
+ * @param expected the expected value
+ * @param actual the actual value
+ * @throws AssertionError if the values are not equal
+ */
protected void assertEquals(float expected, float actual) {
if (expected != actual) {
if (Float.isNaN(expected) && Float.isNaN(actual)) {
Index: src/test/org/h2/test/db/TestFunctions.java
===================================================================
--- src/test/org/h2/test/db/TestFunctions.java (revision 3533)
+++ src/test/org/h2/test/db/TestFunctions.java (working copy)
@@ -12,6 +12,7 @@
import java.io.OutputStream;
import java.math.BigDecimal;
import java.sql.Blob;
+import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
@@ -23,7 +24,6 @@
import java.util.ArrayList;
import java.util.Properties;
import java.util.UUID;
-
import org.h2.api.AggregateFunction;
import org.h2.test.TestBase;
import org.h2.tools.SimpleResultSet;
@@ -66,6 +66,7 @@
testFunctions();
testFileRead();
testValue();
+ testArrayParameters();
deleteDb("functions");
IOUtils.deleteRecursive(TEMP_DIR, true);
}
@@ -551,10 +552,10 @@
stat.execute("CREATE ALIAS ARRAY FOR \"" + getClass().getName() +
".getArray\"");
rs = stat.executeQuery("CALL ARRAY()");
- assertEquals(2, rs.getMetaData().getColumnCount());
+ assertEquals(1, rs.getMetaData().getColumnCount());
rs.next();
- assertEquals(0, rs.getInt(1));
- assertEquals("Hello", rs.getString(2));
+ assertEquals(0, ((Integer) ((Object[])
rs.getObject(1))[0]).intValue());
+ assertEquals("Hello", (String) ((Object[]) rs.getObject(1))[1]);
assertFalse(rs.next());
stat.execute("CREATE ALIAS ROOT FOR \"" + getClass().getName() +
".root\"");
@@ -675,6 +676,31 @@
conn.close();
}
+ private void testArrayParameters() throws SQLException {
+ deleteDb("functions");
+ Connection conn = getConnection("functions");
+ Statement stat = conn.createStatement();
+ ResultSet rs;
+ stat.execute("CREATE ALIAS f_arrays1 AS "
+ + "$$ Integer[] f_arrays1(Integer[] in_array) "
+ + "{ return in_array; } $$;");
+
+ PreparedStatement stmt = conn.prepareStatement("select f_arrays1(?)
from dual");
+ stmt.setObject(1, new Integer[] { 1, 2 });
+ rs = stmt.executeQuery();
+ rs.next();
+ assertEquals(Integer[].class, rs.getObject(1).getClass());
+
+ CallableStatement call = conn.prepareCall("{ ? = call f_arrays1(?) }");
+ call.setObject(2, new Integer[] { 2, 1 });
+ call.registerOutParameter(1, Types.ARRAY);
+ call.execute();
+ assertEquals(Integer[].class, call.getArray(1).getArray().getClass());
+ assertEquals(new Integer[] { 2, 1 }, (Integer[])call.getObject(1));
+
+ conn.close();
+ }
+
private void assertCallResult(String expected, Statement stat, String sql)
throws SQLException {
ResultSet rs = stat.executeQuery("CALL " + sql);
rs.next();
Index: src/test/org/h2/test/jaqu/UpdateTest.java
===================================================================
--- src/test/org/h2/test/jaqu/UpdateTest.java (revision 3533)
+++ src/test/org/h2/test/jaqu/UpdateTest.java (working copy)
@@ -56,7 +56,7 @@
Product p2 = new Product();
Product pChang2 =
db.from(p2).where(p2.productName).is("Chang").selectFirst();
- assertEquals(19.5, pChang2.unitPrice);
+ assertEquals(new Double(19.5), pChang2.unitPrice);
assertEquals(16, pChang2.unitsInStock.intValue());
// undo update
@@ -90,7 +90,7 @@
Product p2 = new Product();
Product pChang2 =
db.from(p2).where(p2.productName).is("Chang").selectFirst();
- assertEquals(19.5, pChang2.unitPrice);
+ assertEquals(new Double(19.5), pChang2.unitPrice);
assertEquals(16, pChang2.unitsInStock.intValue());
// undo update
@@ -129,7 +129,7 @@
// confirm the data was properly updated
Product revised = db.from(p).where(p.productId).is(1).selectFirst();
assertEquals("updated", revised.productName);
- assertEquals(original.unitPrice + 3.14, revised.unitPrice);
+ assertEquals(new Double(original.unitPrice + 3.14), revised.unitPrice);
assertEquals(original.unitsInStock + 2,
revised.unitsInStock.intValue());
// restore the data
