| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.lang.UDTTest |
| |
| Licensed to the Apache Software Foundation (ASF) under one or more |
| contributor license agreements. See the NOTICE file distributed with |
| this work for additional information regarding copyright ownership. |
| The ASF licenses this file to you under the Apache License, Version 2.0 |
| (the "License"); you may not use this file except in compliance with |
| the License. You may obtain a copy of the License at |
| |
| http://www.apache.org/licenses/LICENSE-2.0 |
| |
| Unless required by applicable law or agreed to in writing, software |
| distributed under the License is distributed on an "AS IS" BASIS, |
| WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| See the License for the specific language governing permissions and |
| limitations under the License. |
| |
| */ |
| |
| package org.apache.derbyTesting.functionTests.tests.lang; |
| |
| import java.sql.CallableStatement; |
| import java.sql.Connection; |
| import java.sql.DriverManager; |
| import java.sql.ParameterMetaData; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.ResultSetMetaData; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.util.HashMap; |
| |
| import junit.framework.Test; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| import org.apache.derbyTesting.junit.JDBC; |
| |
| /** |
| * <p> |
| * Test user defined types. See DERBY-651. |
| * </p> |
| */ |
| public class UDTTest extends GeneratedColumnsHelper |
| { |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // CONSTANTS |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| public static final String OBJECT_EXISTS = "X0Y68"; |
| public static final String VIEW_DEPENDS_ON_TYPE = "X0Y23"; |
| public static final String TRIGGER_DEPENDS_ON_TYPE = "X0Y24"; |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // STATE |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // CONSTRUCTOR |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| |
| /** |
| * Create a new instance. |
| */ |
| |
| public UDTTest(String name) |
| { |
| super(name); |
| } |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // JUnit BEHAVIOR |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| |
| /** |
| * Construct top level suite in this JUnit test |
| */ |
| public static Test suite() |
| { |
| return TestConfiguration.defaultSuite(UDTTest.class); |
| } |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // TESTS |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| /** |
| * <p> |
| * Basic syntax. |
| * </p> |
| */ |
| public void test_01_basicSyntax() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| goodStatement( conn, "create schema adt_schema\n" ); |
| |
| // create some types |
| makeGoodTypes( conn ); |
| |
| // duplicate type names should raise errors |
| expectExecutionError( conn, OBJECT_EXISTS, "create type fooType external name 'mypackage.foo' language java\n" ); |
| expectExecutionError( conn, OBJECT_EXISTS, "create type adt_schema.fooType external name 'mypackage.foo' language java\n" ); |
| expectExecutionError( conn, OBJECT_EXISTS, "create type \"smallint\" external name 'mypackage.foo' language java\n" ); |
| expectExecutionError( conn, OBJECT_EXISTS, "create type \"SMALLINT\" external name 'mypackage.foo' language java\n" ); |
| |
| // only RESTRICTed drops allowed now |
| expectCompilationError( SYNTAX_ERROR, "drop type fooType\n" ); |
| |
| // drop some types |
| goodStatement( conn, "drop type fooType restrict\n" ); |
| goodStatement( conn, "drop type adt_schema.fooType restrict\n" ); |
| goodStatement( conn, "drop type \"smallint\" restrict\n" ); |
| goodStatement( conn, "drop type \"SMALLINT\" restrict\n" ); |
| |
| // can't drop a non-existent type |
| expectCompilationError( NONEXISTENT_OBJECT, "drop type fooType restrict\n" ); |
| expectCompilationError( NONEXISTENT_OBJECT, "drop type adt_schema.fooType restrict\n" ); |
| expectCompilationError( NONEXISTENT_OBJECT, "drop type \"smallint\" restrict\n" ); |
| expectCompilationError( NONEXISTENT_OBJECT, "drop type \"SMALLINT\" restrict\n" ); |
| |
| // re-create the types |
| makeGoodTypes( conn ); |
| } |
| private void makeGoodTypes( Connection conn ) throws Exception |
| { |
| goodStatement( conn, "create type fooType external name 'mypackage.foo' language java\n" ); |
| goodStatement( conn, "create type adt_schema.fooType external name 'mypackage.foo' language java\n" ); |
| goodStatement( conn, "create type \"smallint\" external name 'mypackage.foo' language java\n" ); |
| goodStatement( conn, "create type \"SMALLINT\" external name 'mypackage.foo' language java\n" ); |
| } |
| |
| /** |
| * <p> |
| * Basic column, return value, and parameter support. |
| * </p> |
| */ |
| public void test_02_basicColumnRetvalParam() throws Exception |
| { |
| // |
| // DECIMAL datatype used here and the JSR169 support for it is less complete. |
| // |
| if ( JDBC.vmSupportsJSR169() ) { return; } |
| Connection conn = getConnection(); |
| |
| goodStatement( conn, "create type Price external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n" ); |
| goodStatement( conn, "create table orders( orderID int generated always as identity, customerID int, totalPrice price )\n" ); |
| goodStatement |
| ( conn, |
| "create function makePrice( currencyCode char( 3 ), amount decimal( 31, 5 ), timeInstant Timestamp )\n" + |
| "returns Price language java parameter style java no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.makePrice'\n"); |
| goodStatement |
| ( conn, |
| "create function getCurrencyCode( price Price ) returns char( 3 ) language java parameter style java no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.getCurrencyCode'\n" ); |
| goodStatement |
| ( conn, |
| "create function getAmount( price Price ) returns decimal( 31, 5 ) language java parameter style java no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.getAmount'\n" ); |
| goodStatement |
| ( conn, |
| "create function getTimeInstant( price Price ) returns timestamp language java parameter style java no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.getTimeInstant'\n" ); |
| goodStatement |
| ( conn, |
| "create procedure savePrice( in a Price ) language java parameter style java no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.savePrice'\n" ); |
| goodStatement |
| ( conn, |
| "create function getSavedPrice() returns Price language java parameter style java no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.getSavedPrice'\n" ); |
| goodStatement |
| ( conn, |
| "insert into orders( customerID, totalPrice ) values\n" + |
| "( 12345, makePrice( 'USD', cast( 9.99 as decimal( 31, 5 ) ), timestamp('2009-10-16 14:24:43') ) )\n" ); |
| |
| assertResults |
| ( |
| conn, |
| "select getCurrencyCode( totalPrice ), getTimeInstant( totalPrice ) from orders", |
| new String[][] |
| { |
| { "USD" , "2009-10-16 14:24:43.0" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select totalPrice from orders", |
| new String[][] |
| { |
| { "Price( USD, 9.99000, 2009-10-16 14:24:43.0 )" }, |
| }, |
| false |
| ); |
| |
| goodStatement |
| ( conn, |
| "call savePrice\n" + |
| "( makePrice( 'EUR', cast( 1.23 as decimal( 31, 5 ) ), timestamp('2008-10-16 14:24:43') ) )\n" ); |
| assertResults |
| ( |
| conn, |
| "values( getSavedPrice() )", |
| new String[][] |
| { |
| { "Price( EUR, 1.23000, 2008-10-16 14:24:43.0 )" }, |
| }, |
| false |
| ); |
| } |
| |
| /** |
| * <p> |
| * Adding and dropping udt columns. |
| * </p> |
| */ |
| public void test_03_addDropColumn() throws Exception |
| { |
| Connection conn = getConnection(); |
| String tableName1 = "UDTCOLUMNS"; |
| String tableName2 = "UDTCOLUMNS2"; |
| |
| goodStatement |
| ( conn, |
| "create type price_03 external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n" ); |
| |
| // even though there are 2 price_03 columns, we only create 1 dependency |
| goodStatement |
| ( conn, |
| "create table " + tableName1 + "\n" + |
| "(\n" + |
| " a int, b int,\n" + |
| " price1 price_03,\n" + |
| " price2 price_03\n" + |
| ")\n" |
| ); |
| assertEquals( 1, countTableDependencies( conn, tableName1 ) ); |
| |
| // verify that we can't drop the type while the table depends on it |
| expectExecutionError( conn, TABLE_DEPENDS_ON_TYPE, "drop type Price_03 restrict\n" ); |
| |
| // add another price_03 column. should not add another dependency |
| goodStatement |
| ( conn, |
| "alter table udtColumns add column price3 price_03\n" ); |
| assertEquals( 1, countTableDependencies( conn, tableName1 ) ); |
| expectExecutionError( conn, TABLE_DEPENDS_ON_TYPE, "drop type Price_03 restrict\n" ); |
| |
| // drop one of the price_03 column. there should still be a dependency |
| goodStatement |
| ( conn, |
| "alter table udtColumns drop column price3\n" ); |
| assertEquals( 1, countTableDependencies( conn, tableName1 ) ); |
| expectExecutionError( conn, TABLE_DEPENDS_ON_TYPE, "drop type Price_03 restrict\n" ); |
| |
| // drop another column. same story. |
| goodStatement |
| ( conn, |
| "alter table udtColumns drop column price2\n" ); |
| assertEquals( 1, countTableDependencies( conn, tableName1 ) ); |
| expectExecutionError( conn, TABLE_DEPENDS_ON_TYPE, "drop type Price_03 restrict\n" ); |
| |
| // drop the last udt column. dependency should disappear |
| goodStatement |
| ( conn, |
| "alter table udtColumns drop column price1\n" ); |
| assertEquals( 0, countTableDependencies( conn, tableName1 ) ); |
| goodStatement |
| ( conn, |
| "drop type Price_03 restrict\n" ); |
| |
| // similar experiments with more types |
| goodStatement |
| ( conn, |
| "create type price_03_a external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n" ); |
| goodStatement |
| ( conn, |
| "create type price_03_b external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n" ); |
| goodStatement |
| ( conn, |
| "create type price_03_c external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n" ); |
| |
| goodStatement |
| ( conn, |
| "create table udtColumns2\n" + |
| "(\n" + |
| " a int, b int,\n" + |
| " price1 price_03_a,\n" + |
| " price2 price_03_b\n" + |
| ")\n" |
| ); |
| assertEquals( 2, countTableDependencies( conn, tableName2 ) ); |
| expectExecutionError( conn, TABLE_DEPENDS_ON_TYPE, "drop type Price_03_a restrict\n" ); |
| expectExecutionError( conn, TABLE_DEPENDS_ON_TYPE, "drop type Price_03_b restrict\n" ); |
| |
| goodStatement |
| ( conn, |
| "alter table udtColumns2 add column price3 price_03_c\n" ); |
| assertEquals( 3, countTableDependencies( conn, tableName2 ) ); |
| expectExecutionError( conn, TABLE_DEPENDS_ON_TYPE, "drop type Price_03_a restrict\n" ); |
| expectExecutionError( conn, TABLE_DEPENDS_ON_TYPE, "drop type Price_03_b restrict\n" ); |
| expectExecutionError( conn, TABLE_DEPENDS_ON_TYPE, "drop type Price_03_c restrict\n" ); |
| |
| goodStatement |
| ( conn, |
| "alter table udtColumns2 drop column b\n" ); |
| assertEquals( 3, countTableDependencies( conn, tableName2 ) ); |
| expectExecutionError( conn, TABLE_DEPENDS_ON_TYPE, "drop type Price_03_a restrict\n" ); |
| expectExecutionError( conn, TABLE_DEPENDS_ON_TYPE, "drop type Price_03_b restrict\n" ); |
| expectExecutionError( conn, TABLE_DEPENDS_ON_TYPE, "drop type Price_03_c restrict\n" ); |
| |
| goodStatement |
| ( conn, |
| "alter table udtColumns2 drop column price3\n" ); |
| assertEquals( 2, countTableDependencies( conn, tableName2 ) ); |
| goodStatement |
| ( conn, |
| "drop type Price_03_c restrict\n" ); |
| |
| goodStatement |
| ( conn, |
| "alter table udtColumns2 drop column price2\n" ); |
| assertEquals( 1, countTableDependencies( conn, tableName2 ) ); |
| goodStatement |
| ( conn, |
| "drop type Price_03_b restrict\n" ); |
| |
| goodStatement |
| ( conn, |
| "alter table udtColumns2 drop column price1\n" ); |
| assertEquals( 0, countTableDependencies( conn, tableName2 ) ); |
| goodStatement |
| ( conn, |
| "drop type Price_03_a restrict\n" ); |
| } |
| |
| /** |
| * <p> |
| * Dropping a whole table which has udt columns. |
| * </p> |
| */ |
| public void test_04_dropTable() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| goodStatement |
| ( conn, |
| "create type price_orphan external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n" ); |
| goodStatement |
| ( conn, |
| "create type price_orphan2 external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n" ); |
| goodStatement |
| ( conn, |
| "create type price_orphan3 external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n" ); |
| goodStatement |
| ( conn, |
| "create type price_orphan4 external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n" ); |
| goodStatement |
| ( conn, |
| "create table t_orphan( a price_orphan )\n" ); |
| goodStatement |
| ( conn, |
| "create table t_orphan2( a price_orphan2, b int, c price_orphan2 )\n" ); |
| goodStatement |
| ( conn, |
| "create table t_orphan3( a price_orphan3, b int, c price_orphan4 )\n" ); |
| |
| expectExecutionError( conn, TABLE_DEPENDS_ON_TYPE, "drop type price_orphan restrict\n" ); |
| goodStatement |
| ( conn, |
| "drop table t_orphan\n" ); |
| goodStatement |
| ( conn, |
| "drop type price_orphan restrict\n" ); |
| |
| expectExecutionError( conn, TABLE_DEPENDS_ON_TYPE, "drop type price_orphan2 restrict\n" ); |
| goodStatement |
| ( conn, |
| "drop table t_orphan2\n" ); |
| goodStatement |
| ( conn, |
| "drop type price_orphan2 restrict\n" ); |
| |
| expectExecutionError( conn, TABLE_DEPENDS_ON_TYPE, "drop type price_orphan3 restrict\n" ); |
| expectExecutionError( conn, TABLE_DEPENDS_ON_TYPE, "drop type price_orphan4 restrict\n" ); |
| goodStatement |
| ( conn, |
| "drop table t_orphan3\n" ); |
| goodStatement |
| ( conn, |
| "drop type price_orphan3 restrict\n" ); |
| goodStatement |
| ( conn, |
| "drop type price_orphan4 restrict\n" ); |
| } |
| |
| /** |
| * <p> |
| * Dependencies of views on UDTs. |
| * </p> |
| */ |
| public void test_05_viewDependencies() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| String createTypeStatement; |
| String dropTypeStatement; |
| String createObjectStatement; |
| String dropObjectStatement; |
| String badDropSQLState; |
| |
| // view with UDT in select list |
| createTypeStatement = "create type price_05_a external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n"; |
| dropTypeStatement = "drop type price_05_a restrict\n"; |
| createObjectStatement = |
| "create view udtView( a, b, c ) as\n" + |
| "select tabletype, cast (null as price_05_a), cast( null as price_05_a)\n" + |
| "from sys.systables\n"; |
| dropObjectStatement = "drop view udtView\n"; |
| badDropSQLState = VIEW_DEPENDS_ON_TYPE; |
| verifyDropRestrictions |
| ( |
| conn, |
| createTypeStatement, |
| dropTypeStatement, |
| createObjectStatement, |
| dropObjectStatement, |
| badDropSQLState |
| ); |
| |
| // view with UDT in where clause |
| createTypeStatement = "create type price_05_b external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n"; |
| dropTypeStatement = "drop type price_05_b restrict\n"; |
| createObjectStatement = |
| "create view udtView_b( a ) as\n" + |
| "select tabletype from sys.systables where ( cast (null as price_05_b) ) is not null\n"; |
| dropObjectStatement = "drop view udtView_b\n"; |
| badDropSQLState = VIEW_DEPENDS_ON_TYPE; |
| verifyDropRestrictions |
| ( |
| conn, |
| createTypeStatement, |
| dropTypeStatement, |
| createObjectStatement, |
| dropObjectStatement, |
| badDropSQLState |
| ); |
| |
| } |
| |
| /** |
| * <p> |
| * Casting to UDTs. |
| * </p> |
| */ |
| public void test_06_casts() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| // cast a NULL as a UDT |
| goodStatement |
| ( conn, |
| "create type price_06_b external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n" ); |
| assertResults |
| ( |
| conn, |
| "values ( cast ( null as price_06_b ) )\n", |
| new String[][] |
| { |
| { null }, |
| }, |
| false |
| ); |
| |
| // casting an untyped parameter to a UDT |
| PreparedStatement ps = chattyPrepare |
| ( conn, "values ( cast ( ? as price_06_b ) )" ); |
| ps.setObject( 1, Price.makePrice() ); |
| ResultSet rs = ps.executeQuery(); |
| rs.next(); |
| Price result = (Price) rs.getObject( 1 ); |
| rs.close(); |
| ps.close(); |
| assertTrue( Price.makePrice().equals( result ) ); |
| } |
| |
| /** |
| * <p> |
| * Dependencies of routines on UDTs. |
| * </p> |
| */ |
| public void test_07_routineDependencies() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| String createTypeStatement; |
| String dropTypeStatement; |
| String createObjectStatement; |
| String dropObjectStatement; |
| String badDropSQLState; |
| |
| // function that returns a udt |
| createTypeStatement = "create type price_07_a external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n"; |
| dropTypeStatement = "drop type price_07_a restrict\n"; |
| createObjectStatement = |
| "create function makePrice_07_a( )\n" + |
| "returns price_07_a\n" + |
| "language java\n" + |
| "parameter style java\n" + |
| "no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.makePrice'\n"; |
| dropObjectStatement = "drop function makePrice_07_a\n"; |
| badDropSQLState = ROUTINE_DEPENDS_ON_TYPE; |
| verifyDropRestrictions |
| ( |
| conn, |
| createTypeStatement, |
| dropTypeStatement, |
| createObjectStatement, |
| dropObjectStatement, |
| badDropSQLState |
| ); |
| |
| // function with a udt arg |
| createTypeStatement = "create type price_07_b external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n"; |
| dropTypeStatement = "drop type price_07_b restrict\n"; |
| createObjectStatement = |
| "create function getCurrencyCode_07_b( priceArg1 price_07_b )\n" + |
| "returns char( 3 )\n" + |
| "language java\n" + |
| "parameter style java\n" + |
| "no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.Price.getCurrencyCode'\n"; |
| dropObjectStatement = "drop function getCurrencyCode_07_b\n"; |
| badDropSQLState = ROUTINE_DEPENDS_ON_TYPE; |
| verifyDropRestrictions |
| ( |
| conn, |
| createTypeStatement, |
| dropTypeStatement, |
| createObjectStatement, |
| dropObjectStatement, |
| badDropSQLState |
| ); |
| |
| // procedure with a udt arg |
| createTypeStatement = "create type price_07_c external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n"; |
| dropTypeStatement = "drop type price_07_c restrict\n"; |
| createObjectStatement = |
| "create procedure oneArgPriceProc_07( price1 price_07_c )\n" + |
| "language java\n" + |
| "parameter style java\n" + |
| "no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.oneArgPriceProc_07'\n"; |
| dropObjectStatement = "drop procedure oneArgPriceProc_07\n"; |
| badDropSQLState = ROUTINE_DEPENDS_ON_TYPE; |
| verifyDropRestrictions |
| ( |
| conn, |
| createTypeStatement, |
| dropTypeStatement, |
| createObjectStatement, |
| dropObjectStatement, |
| badDropSQLState |
| ); |
| |
| // procedure with two udt args |
| createTypeStatement = "create type price_07_d external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n"; |
| dropTypeStatement = "drop type price_07_d restrict\n"; |
| createObjectStatement = |
| "create procedure twoArgPriceProc_07( price1 price_07_d, price2 price_07_d )\n" + |
| "language java\n" + |
| "parameter style java\n" + |
| "no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.twoArgPriceProc_07'\n"; |
| dropObjectStatement = "drop procedure twoArgPriceProc_07\n"; |
| badDropSQLState = ROUTINE_DEPENDS_ON_TYPE; |
| verifyDropRestrictions |
| ( |
| conn, |
| createTypeStatement, |
| dropTypeStatement, |
| createObjectStatement, |
| dropObjectStatement, |
| badDropSQLState |
| ); |
| |
| // table function with a udt column |
| createTypeStatement = "create type hashmap_07 external name 'java.util.HashMap' language java\n"; |
| dropTypeStatement = "drop type hashmap_07 restrict\n"; |
| createObjectStatement = |
| "create function hashmapReader_07() returns table\n" + |
| "(\n" + |
| " a int,\n" + |
| " b hashmap_07\n" + |
| ")\n" + |
| "language java parameter style derby_jdbc_result_set\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.hashmapReader'\n"; |
| dropObjectStatement = "drop function hashmapReader_07\n"; |
| badDropSQLState = ROUTINE_DEPENDS_ON_TYPE; |
| verifyDropRestrictions |
| ( |
| conn, |
| createTypeStatement, |
| dropTypeStatement, |
| createObjectStatement, |
| dropObjectStatement, |
| badDropSQLState |
| ); |
| |
| // table function with a udt column and udt arg |
| createTypeStatement = "create type hashmap_07_a external name 'java.util.HashMap' language java\n"; |
| dropTypeStatement = "drop type hashmap_07_a restrict\n"; |
| createObjectStatement = |
| "create function hashmapReader_07_a( a hashmap_07_a ) returns table\n" + |
| "(\n" + |
| " a int,\n" + |
| " b hashmap_07_a\n" + |
| ")\n" + |
| "language java parameter style derby_jdbc_result_set\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.hashmapReader'\n"; |
| dropObjectStatement = "drop function hashmapReader_07_a\n"; |
| badDropSQLState = ROUTINE_DEPENDS_ON_TYPE; |
| verifyDropRestrictions |
| ( |
| conn, |
| createTypeStatement, |
| dropTypeStatement, |
| createObjectStatement, |
| dropObjectStatement, |
| badDropSQLState |
| ); |
| |
| } |
| |
| /** |
| * <p> |
| * Dependencies of triggers on UDTs. |
| * </p> |
| */ |
| public void test_08_triggerDependencies() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| goodStatement( conn, "create table t_08_a( a int )" ); |
| goodStatement( conn, "create table t_08_b( a int )" ); |
| |
| String createTypeStatement; |
| String dropTypeStatement; |
| String createObjectStatement; |
| String dropObjectStatement; |
| String badDropSQLState; |
| |
| // trigger that mentions a udt |
| createTypeStatement = "create type price_08_a external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n"; |
| dropTypeStatement = "drop type price_08_a restrict\n"; |
| createObjectStatement = |
| "create trigger trig_08_a after insert on t_08_a\n" + |
| " insert into t_08_b( a ) select ( a ) from t_08_a where ( cast( null as price_08_a ) ) is not null\n"; |
| dropObjectStatement = "drop trigger trig_08_a"; |
| badDropSQLState = TRIGGER_DEPENDS_ON_TYPE; |
| verifyDropRestrictions |
| ( |
| conn, |
| createTypeStatement, |
| dropTypeStatement, |
| createObjectStatement, |
| dropObjectStatement, |
| badDropSQLState |
| ); |
| |
| } |
| |
| /** |
| * <p> |
| * Check result set metadata for UDT columns. |
| * </p> |
| */ |
| public void test_09_resultSetMetaData() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| goodStatement( conn, "create type price_09_a external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n" ); |
| goodStatement( conn, "create table t_09_a( a price_09_a )\n" ); |
| |
| // ANSI UDT |
| checkRSMD |
| ( |
| conn, |
| "select a from t_09_a\n", |
| "org.apache.derbyTesting.functionTests.tests.lang.Price", |
| 15, |
| java.sql.Types.JAVA_OBJECT, |
| "\"APP\".\"PRICE_09_A\"", |
| 0, |
| 0 |
| ); |
| |
| // old-style objects in Derby system tables do not have |
| // schema-qualified type names |
| checkRSMD |
| ( |
| conn, |
| "select aliasinfo from sys.sysaliases\n", |
| "org.apache.derby.catalog.AliasInfo", |
| 15, |
| java.sql.Types.JAVA_OBJECT, |
| "org.apache.derby.catalog.AliasInfo", |
| 0, |
| 0 |
| ); |
| } |
| |
| /** |
| * <p> |
| * Check parameter metadata for UDT parameters. |
| * </p> |
| */ |
| public void test_10_parameterMetaData() throws Exception |
| { |
| // |
| // Parameter meta data is not available on JSR-169 platforms, |
| // so skip this test in those environments. |
| // |
| if ( JDBC.vmSupportsJSR169() ) { return; } |
| |
| Connection conn = getConnection(); |
| |
| goodStatement( conn, "create type price_10_a external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java\n" ); |
| goodStatement( conn, "create table t_10_a( a price_10_a )\n" ); |
| |
| // ANSI UDT |
| checkPMD |
| ( |
| conn, |
| "insert into t_10_a( a ) values ( ? )\n", |
| "org.apache.derbyTesting.functionTests.tests.lang.Price", |
| java.sql.Types.JAVA_OBJECT, |
| "\"APP\".\"PRICE_10_A\"", |
| 0, |
| 0 |
| ); |
| |
| // |
| // I don't know of any way to create a statement with a parameter |
| // whose type is an old-style object from Derby's system tables. |
| // If you figure out how to trick Derby into letting you do that, |
| // this would be a good place to assert the shape of the parameter |
| // meta data for that statement. |
| // |
| } |
| |
| /** |
| * <p> |
| * Verify that table functions can have UDT columns. |
| * </p> |
| */ |
| public void test_11_tableFunctionColumns() throws Exception |
| { |
| // |
| // This test uses DriverManager. |
| // |
| if ( JDBC.vmSupportsJSR169() ) { return; } |
| |
| Connection conn = getConnection(); |
| |
| goodStatement( conn, "create type hashmap_11 external name 'java.util.HashMap' language java\n" ); |
| goodStatement |
| ( |
| conn, |
| "create function makeHashMap_11() returns hashmap_11\n" + |
| "language java parameter style java no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.makeHashMap'\n" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create function putValue_11( map hashmap_11, k varchar( 100 ), v varchar( 100 ) ) returns hashmap_11\n" + |
| "language java parameter style java no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.putValue'\n" |
| ); |
| goodStatement( conn, "create table t_11( intCol int, varcharCol varchar( 10 ), hashmapCol hashmap_11 )\n" ); |
| goodStatement |
| ( |
| conn, |
| "create function hashmapReader() returns table\n" + |
| "(\n" + |
| " a int,\n" + |
| " b hashmap_11\n" + |
| ")\n" + |
| "language java parameter style derby_jdbc_result_set\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.hashmapReader'\n" |
| ); |
| goodStatement( conn, "insert into t_11( intCol, varcharCol, hashmapCol ) values ( 2, 'def', putValue_11( makeHashMap_11(), 'kangaroo', 'foo' ) )\n" ); |
| |
| assertResults |
| ( |
| conn, |
| "select * from table( hashmapReader() ) s", |
| new String[][] |
| { |
| { "2" , "{kangaroo=foo}" }, |
| }, |
| true |
| ); |
| } |
| |
| /** |
| * <p> |
| * Verify that you can store large objects in UDT columns. |
| * </p> |
| */ |
| public void test_12_largeUDTs() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| // |
| // Store and retrieve a UDT which is more than 90K bytes long |
| // |
| goodStatement( conn, "create type IntArray external name 'org.apache.derbyTesting.functionTests.tests.lang.IntArray' language java\n" ); |
| goodStatement |
| ( conn, |
| "create function makeIntArray( arrayLength int ) returns IntArray\n" + |
| "language java parameter style java no sql external name 'org.apache.derbyTesting.functionTests.tests.lang.IntArray.makeIntArray'\n" ); |
| goodStatement |
| ( conn, |
| "create function setCell( array IntArray, cellNumber int, cellValue int ) returns IntArray\n" + |
| "language java parameter style java no sql external name 'org.apache.derbyTesting.functionTests.tests.lang.IntArray.setCell'\n" ); |
| goodStatement |
| ( conn, |
| "create function getCell( array IntArray, cellNumber int ) returns int\n" + |
| "language java parameter style java no sql external name 'org.apache.derbyTesting.functionTests.tests.lang.IntArray.getCell'\n" ); |
| goodStatement( conn, "create table t_12( id int generated always as identity, data IntArray )\n" ); |
| goodStatement( conn, "insert into t_12( data ) values ( setCell( makeIntArray( 3 ), 1, 5 ) )\n" ); |
| goodStatement( conn, "insert into t_12( data ) values ( setCell( makeIntArray( 100000 ), 90000, 3 ) )\n" ); |
| |
| assertResults |
| ( |
| conn, |
| "select getCell( data, 1 ), getCell( data, 2 ) from t_12 where id = 1", |
| new String[][] |
| { |
| { "5" , "0" }, |
| }, |
| true |
| ); |
| |
| assertResults |
| ( |
| conn, |
| "select getCell( data, 1 ), getCell( data, 90000 ) from t_12 where id = 2", |
| new String[][] |
| { |
| { "0" , "3" }, |
| }, |
| true |
| ); |
| |
| // |
| // Store and retrieve a UDT which is more than 1000K bytes long |
| // |
| goodStatement( conn, "create type FakeByteArray external name 'org.apache.derbyTesting.functionTests.tests.lang.FakeByteArray' language java\n" ); |
| goodStatement |
| ( conn, |
| "create function makeFakeByteArray( l int, f int ) returns FakeByteArray\n" + |
| "language java parameter style java no sql external name 'org.apache.derbyTesting.functionTests.tests.lang.FakeByteArray.makeFakeByteArray'\n" ); |
| goodStatement |
| ( conn, |
| "create function toString( arg FakeByteArray ) returns varchar( 30 )\n" + |
| "language java parameter style java no sql external name 'org.apache.derbyTesting.functionTests.tests.lang.FakeByteArray.toString'\n" ); |
| goodStatement( conn, "create table t_12_a( id int generated always as identity, data FakeByteArray )\n" ); |
| goodStatement( conn, "insert into t_12_a( data ) values ( makeFakeByteArray( 3, 33 ) )\n" ); |
| goodStatement( conn, "insert into t_12_a( data ) values ( makeFakeByteArray( 1000000, 44 ) )\n" ); |
| |
| assertResults |
| ( |
| conn, |
| "select id, toString( data ) from t_12_a order by id", |
| new String[][] |
| { |
| { "1" , "[ 3, 33 ]" }, |
| { "2" , "[ 1000000, 44 ]" }, |
| }, |
| true |
| ); |
| } |
| |
| /** |
| * <p> |
| * Verify that implementing the SQLData interface does not make an object storeable. |
| * </p> |
| */ |
| public void test_13_sqlData() throws Exception |
| { |
| // |
| // SQLData not defined in JSR 169. |
| // |
| if ( JDBC.vmSupportsJSR169() ) { return; } |
| |
| Connection conn = getConnection(); |
| |
| goodStatement( conn, "create type SampleSQLData external name 'org.apache.derbyTesting.functionTests.tests.lang.SampleSQLData' language java\n" ); |
| goodStatement |
| ( conn, |
| "create function makeSampleSQLData( l int ) returns SampleSQLData\n" + |
| "language java parameter style java no sql external name 'org.apache.derbyTesting.functionTests.tests.lang.SampleSQLData.makeSampleSQLData'\n" ); |
| goodStatement( conn, "create table t_13_a( id int generated always as identity, data SampleSQLData )\n" ); |
| |
| expectExecutionError( conn, JAVA_EXCEPTION, "insert into t_13_a( data ) values ( makeSampleSQLData( 3 ) )\n" ); |
| } |
| |
| /** |
| * <p> |
| * Verify that you can't bind UDTs to the classes which back the system types. |
| * </p> |
| */ |
| public void test_14_systemClasses() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| // |
| // Before checking types, make sure that all types we understand are accounted for. |
| // If a new system type is added, then we need to add it to the following block |
| // of compilation errors. |
| // |
| assertEquals( 21, vetDatatypeCount( conn ) ); |
| |
| expectCompilationError( ILLEGAL_UDT_CLASS, "create type java_string external name 'byte[]' language java\n" ); |
| expectCompilationError( ILLEGAL_UDT_CLASS, "create type java_string external name 'java.lang.Boolean' language java\n" ); |
| expectCompilationError( ILLEGAL_UDT_CLASS, "create type java_string external name 'java.lang.Integer' language java\n" ); |
| expectCompilationError( ILLEGAL_UDT_CLASS, "create type java_string external name 'java.lang.Long' language java\n" ); |
| expectCompilationError( ILLEGAL_UDT_CLASS, "create type java_string external name 'java.lang.Float' language java\n" ); |
| expectCompilationError( ILLEGAL_UDT_CLASS, "create type java_string external name 'java.lang.Double' language java\n" ); |
| expectCompilationError( ILLEGAL_UDT_CLASS, "create type java_string external name 'java.lang.String' language java\n" ); |
| expectCompilationError( ILLEGAL_UDT_CLASS, "create type java_string external name 'java.math.BigDecimal' language java\n" ); |
| expectCompilationError( ILLEGAL_UDT_CLASS, "create type java_string external name 'java.sql.Blob' language java\n" ); |
| expectCompilationError( ILLEGAL_UDT_CLASS, "create type java_string external name 'java.sql.Clob' language java\n" ); |
| expectCompilationError( ILLEGAL_UDT_CLASS, "create type java_string external name 'java.sql.Date' language java\n" ); |
| expectCompilationError( ILLEGAL_UDT_CLASS, "create type java_string external name 'java.sql.Ref' language java\n" ); |
| expectCompilationError( ILLEGAL_UDT_CLASS, "create type java_string external name 'java.sql.Time' language java\n" ); |
| expectCompilationError( ILLEGAL_UDT_CLASS, "create type java_string external name 'java.sql.Timestamp' language java\n" ); |
| expectCompilationError( ILLEGAL_UDT_CLASS, "create type java_string external name 'org.apache.derby.iapi.types.XML' language java\n" ); |
| expectCompilationError( ILLEGAL_UDT_CLASS, "create type java_string external name 'org.apache.derby.Foo' language java\n" ); |
| } |
| private int vetDatatypeCount( Connection conn ) throws Exception |
| { |
| ResultSet rs = conn.getMetaData().getTypeInfo(); |
| int expectedTypeCount = 0; |
| while ( rs.next() ) { expectedTypeCount++; } |
| rs.close(); |
| |
| expectedTypeCount--; // eliminate JAVA_OBJECT |
| |
| int actualTypeCount = org.apache.derby.iapi.types.TypeId.getAllBuiltinTypeIds().length; |
| actualTypeCount--; // eliminate TINYINT |
| actualTypeCount--; // eliminate REF |
| actualTypeCount++; // add FLOAT (synonym of REAL) |
| |
| // |
| // Make sure that all types have been added to TypeId.getAllBuiltinTypeIds(). |
| // |
| assertEquals( expectedTypeCount, actualTypeCount ); |
| |
| return actualTypeCount; |
| } |
| |
| |
| /** |
| * <p> |
| * Verify that UDTs have no ordering. |
| * </p> |
| */ |
| public void test_15_ordering() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| // Create a Comparable type. We can't take advantage of that interface yet. |
| goodStatement( conn, "create type IntArray_15 external name 'org.apache.derbyTesting.functionTests.tests.lang.IntArray' language java\n" ); |
| goodStatement |
| ( conn, |
| "create function makeIntArray_15( arrayLength int ) returns IntArray_15\n" + |
| "language java parameter style java no sql external name 'org.apache.derbyTesting.functionTests.tests.lang.IntArray.makeIntArray'\n" ); |
| goodStatement( conn, "create table t_15( a IntArray_15 )\n" ); |
| goodStatement( conn, "insert into t_15( a ) values ( makeIntArray_15( 3 ) )\n" ); |
| goodStatement( conn, "insert into t_15( a ) values ( makeIntArray_15( 4 ) )\n" ); |
| |
| expectCompilationError( FORBIDDEN_ORDERING_OPERATION, "create index t_15_idx on t_15( a )\n" ); |
| expectCompilationError( FORBIDDEN_ORDERING_OPERATION, "select * from t_15 order by a\n" ); |
| expectCompilationError( FORBIDDEN_ORDERING_OPERATION, "select * from t_15 group by a\n" ); |
| expectCompilationError( FORBIDDEN_ORDERING_OPERATION, "select distinct a from t_15\n" ); |
| expectCompilationError( ILLEGAL_AGG, "select max( a ) from t_15\n" ); |
| expectCompilationError( ILLEGAL_AGG, "select min( a ) from t_15\n" ); |
| expectCompilationError( ILLEGAL_AGG, "select avg( a ) from t_15\n" ); |
| expectCompilationError( FORBIDDEN_ORDERING_OPERATION, "select * from t_15 union select * from t_15\n" ); |
| expectCompilationError( ILLEGAL_COMPARISON, "select * from t_15 where a = makeIntArray_15( 3 )\n" ); |
| expectCompilationError( ILLEGAL_COMPARISON, "select * from t_15 where a between makeIntArray_15( 2 ) and makeIntArray_15( 4 )\n" ); |
| expectCompilationError( ILLEGAL_COMPARISON, "select * from t_15 l, t_15 r where l.a = r.a\n" ); |
| expectCompilationError( ILLEGAL_COMPARISON, "select * from t_15 l, t_15 r where l.a < r.a\n" ); |
| expectCompilationError( ILLEGAL_COMPARISON, "select * from t_15 l, t_15 r where l.a > r.a\n" ); |
| expectCompilationError( ILLEGAL_COMPARISON, "select * from t_15 l, t_15 r where l.a <= r.a\n" ); |
| expectCompilationError( ILLEGAL_COMPARISON, "select * from t_15 l, t_15 r where l.a >= r.a\n" ); |
| expectCompilationError( FORBIDDEN_ORDERING_OPERATION, "select count( distinct a ) from t_15\n" ); |
| |
| // but these don't involve any comparisons |
| goodStatement( conn, "select count(*) from t_15\n" ); |
| goodStatement( conn, "select all * from t_15\n" ); |
| } |
| |
| /** |
| * <p> |
| * Verify implicit and explicit casts. |
| * </p> |
| */ |
| public void test_16_casts() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| goodStatement( conn, "create type javaSerializable external name 'java.io.Serializable' language java\n" ); |
| goodStatement( conn, "create type javaNumber external name 'java.lang.Number' language java\n" ); |
| goodStatement( conn, "create type javaDate external name 'java.util.Date' language java\n" ); |
| goodStatement |
| ( conn, |
| "create function makeNumber( arg int ) returns javaNumber\n" + |
| "language java parameter style java no sql external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.makeNumber'\n" ); |
| goodStatement( conn, "create table t_16( a int generated always as identity, b javaNumber )\n" ); |
| goodStatement( conn, "create table t_16_1( a int generated always as identity, b javaDate )\n" ); |
| goodStatement( conn, "create table t_16_2( a int generated always as identity, b javaSerializable )\n" ); |
| goodStatement( conn, "insert into t_16( b ) values ( makeNumber( 1 ) )\n" ); |
| goodStatement( conn, "insert into t_16( b ) select b from t_16\n" ); |
| |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16( b ) values ( 1 )\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16( b ) values ( 1.0 )\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16( b ) values ( '1' )\n" ); |
| |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_1( b ) values ( date('1994-02-23') )\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_1( b ) values ( time('15:09:02') )\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_1( b ) values ( timestamp('1960-01-01 23:03:20') )\n" ); |
| |
| // subtypes not recognized yet |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_2( b ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_2( b ) values( cast (null as javaNumber) )\n" ); |
| |
| // casts to other udts not allowed |
| expectCompilationError( BAD_CAST, "select cast (b as javaDate) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as javaSerializable) from t_16\n" ); |
| |
| // |
| // If this fails, it means that we need to add another system type to the |
| // cast checks below. |
| // |
| assertEquals( 21, vetDatatypeCount( conn ) ); |
| |
| // casts to system types not allowed |
| expectCompilationError( BAD_CAST, "select cast (b as boolean) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as bigint) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as blob) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as char( 1 ) ) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as char( 1 ) for bit data) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as clob) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as date) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as decimal) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as double) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as float) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as int) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as long varchar) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as long varchar for bit data) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as numeric) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as real) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as smallint) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as time) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as timestamp) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as varchar(10)) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as varchar(10) for bit data) from t_16\n" ); |
| expectCompilationError( BAD_CAST, "select cast (b as xml) from t_16\n" ); |
| |
| |
| // |
| // If this fails, it means that we need to add another system type to the |
| // t_16_all_types table and add a corresponding cast check below. |
| // |
| assertEquals( 21, vetDatatypeCount( conn ) ); |
| |
| goodStatement |
| ( |
| conn, |
| "create table t_16_all_types\n" + |
| "(\n" + |
| " a01 bigint,\n" + |
| " a02 blob,\n" + |
| " a03 char( 1 ),\n" + |
| " a04 char( 1 ) for bit data ,\n" + |
| " a05 clob,\n" + |
| " a06 date,\n" + |
| " a07 decimal,\n" + |
| " a08 double,\n" + |
| " a09 float,\n" + |
| " a10 int,\n" + |
| " a11 long varchar,\n" + |
| " a12 long varchar for bit data,\n" + |
| " a13 numeric,\n" + |
| " a14 real,\n" + |
| " a15 smallint,\n" + |
| " a16 time,\n" + |
| " a17 timestamp,\n" + |
| " a18 varchar(10),\n" + |
| " a19 varchar(10) for bit data,\n" + |
| " a20 xml,\n" + |
| " a21 boolean\n" + |
| ")" |
| ); |
| |
| expectCompilationError( BAD_CAST, "select cast( a01 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a02 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a03 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a04 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a05 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a06 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a07 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a08 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a09 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a10 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a11 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a12 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a13 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a14 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a15 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a16 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a17 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a18 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a19 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a20 as javaSerializable ) from t_16_all_types\n" ); |
| expectCompilationError( BAD_CAST, "select cast( a21 as javaSerializable ) from t_16_all_types\n" ); |
| |
| |
| // |
| // If this fails, it means that we need to add another system type to the |
| // implicit casts which follow. |
| // |
| assertEquals( 21, vetDatatypeCount( conn ) ); |
| |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a01 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a02 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a03 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a04 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a05 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a06 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a07 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a08 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a09 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a10 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a11 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a12 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a13 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a14 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a15 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a16 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a17 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a18 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a19 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a20 ) select b from t_16\n" ); |
| expectCompilationError( ILLEGAL_STORAGE, "insert into t_16_all_types( a21 ) select b from t_16\n" ); |
| |
| // test cast from the half-supported boolean type |
| expectCompilationError( BAD_CAST, "select cast (isindex as javaNumber) from sys.sysconglomerates\n" ); |
| |
| // good cast to self |
| assertResults |
| ( |
| conn, |
| "select cast (b as javaNumber) from t_16", |
| new String[][] |
| { |
| { "1" }, |
| { "1" }, |
| }, |
| false |
| ); |
| } |
| |
| /** |
| * <p> |
| * Verify that you can use UDTs as output parameters in database procedures. |
| * </p> |
| */ |
| public void test_17_outputParameters() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| goodStatement( conn, "create type intArray_17 external name 'org.apache.derbyTesting.functionTests.tests.lang.IntArray' language java\n" ); |
| goodStatement |
| ( conn, |
| "create procedure changeIntArray_17\n" + |
| "( in newSize int, inout oldIntArray intArray_17 )\n" + |
| "language java parameter style java no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.UDTTest.changeIntArray'\n" ); |
| |
| CallableStatement cs = chattyPrepareCall( conn, "call changeIntArray_17( ?, ? )" ); |
| cs.registerOutParameter( 2, java.sql.Types.JAVA_OBJECT ); |
| cs.setInt( 1, 2 ); |
| cs.setObject( 2, new IntArray( new int[ 5 ] ) ); |
| cs.execute(); |
| Object obj = cs.getObject( 2 ); |
| cs.close(); |
| |
| assertEquals( "[ 0, 0 ]", obj.toString() ); |
| } |
| |
| /** |
| * Verify that you can cast a value to an UDT in a generation clause or |
| * a CHECK constraint. Regression test case for DERBY-6421. |
| */ |
| public void test_18_derby6421() throws SQLException { |
| setAutoCommit(false); |
| |
| Statement s = createStatement(); |
| s.execute("create type d6421_type external name 'java.util.ArrayList' " |
| + "language java"); |
| s.execute("create table d6421_table " |
| + "(x generated always as (cast(null as d6421_type)), " |
| + "check (cast(null as d6421_type) is null))"); |
| |
| // This insert used to cause assert failure (in sane builds) or |
| // NullPointerException (in insane builds). |
| s.execute("insert into d6421_table values default"); |
| } |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // PROCEDURES AND FUNCTIONS |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| public static void oneArgPriceProc( Price price1 ) {} |
| public static void twoArgPriceProc( Price price1, Price price2 ) {} |
| |
| public static void changeIntArray( int newSize, IntArray[] array ) |
| { |
| IntArray newArray = new IntArray( new int[ newSize ] ); |
| |
| array[ 0 ] = newArray; |
| } |
| |
| public static HashMap makeHashMap() { return new HashMap(); } |
| |
| public static HashMap makeHashMap( String key, Integer value ) |
| { |
| HashMap<String,Integer> map = new HashMap<String,Integer>(); |
| map.put( key, value ); |
| return map; |
| } |
| |
| public static HashMap putValue( |
| HashMap<String, String> map, String key, String value) |
| { |
| map.put( key, value ); |
| |
| return map; |
| } |
| |
| public static ResultSet hashmapReader() throws Exception |
| { |
| Connection conn = DriverManager.getConnection( "jdbc:default:connection" ); |
| |
| PreparedStatement ps = conn.prepareStatement( "select intCol, hashmapCol from t_11" ); |
| |
| return ps.executeQuery(); |
| } |
| |
| public static Number makeNumber( int arg ) { return arg; } |
| |
| public static Integer getIntValue( HashMap<String,Integer> map, String key ) |
| { |
| return map.get( key ); |
| } |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // MINIONS |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| /** |
| * Verify that a type can't be dropped if it is used by a schema object. |
| */ |
| private void verifyDropRestrictions |
| ( |
| Connection conn, |
| String createTypeStatement, |
| String dropTypeStatement, |
| String createObjectStatement, |
| String dropObjectStatement, |
| String badDropSQLState |
| ) |
| throws Exception |
| { |
| goodStatement( conn, createTypeStatement ); |
| goodStatement( conn, createObjectStatement ); |
| expectExecutionError( conn, badDropSQLState, dropTypeStatement ); |
| goodStatement( conn, dropObjectStatement ); |
| goodStatement( conn, dropTypeStatement ); |
| } |
| |
| /** Get the number of dependencies that a table has */ |
| private int countTableDependencies( Connection conn, String tableName ) throws Exception |
| { |
| PreparedStatement ps = chattyPrepare |
| ( conn, "select count(*) from sys.sysdepends d, sys.systables t where d.dependentid = t.tableid and t.tablename = ?" ); |
| ps.setString( 1, tableName ); |
| |
| return getScalarInteger( ps ); |
| } |
| |
| /** Get a scalar integer result from a query */ |
| private int getScalarInteger( PreparedStatement ps ) throws Exception |
| { |
| ResultSet rs = ps.executeQuery(); |
| rs.next(); |
| int retval = rs.getInt( 1 ); |
| |
| rs.close(); |
| ps.close(); |
| |
| return retval; |
| } |
| |
| /** |
| * Check the ResultSetMetaData for a query whose first column is a UDT. |
| */ |
| private void checkRSMD |
| ( |
| Connection conn, |
| String query, |
| String expectedClassName, |
| int expectedDisplaySize, |
| int expectedJDBCType, |
| String expectedSQLTypeName, |
| int expectedPrecision, |
| int expectedScale |
| ) throws Exception |
| { |
| PreparedStatement ps = conn.prepareStatement( query ); |
| ResultSet rs = ps.executeQuery(); |
| ResultSetMetaData rsmd = rs.getMetaData(); |
| |
| assertEquals( rsmd.getColumnClassName( 1 ), expectedClassName ); |
| assertEquals( rsmd.getColumnDisplaySize( 1 ), expectedDisplaySize ); |
| assertEquals( rsmd.getColumnType( 1 ), expectedJDBCType ); |
| assertEquals( rsmd.getColumnTypeName( 1 ), expectedSQLTypeName ); |
| assertEquals( rsmd.getPrecision( 1 ), expectedPrecision ); |
| assertEquals( rsmd.getScale( 1 ), expectedScale ); |
| |
| rs.close(); |
| ps.close(); |
| } |
| |
| /** |
| * Check the ParameterMetaData for a statement whose first parameter is a UDT. |
| */ |
| private void checkPMD |
| ( |
| Connection conn, |
| String query, |
| String expectedClassName, |
| int expectedJDBCType, |
| String expectedSQLTypeName, |
| int expectedPrecision, |
| int expectedScale |
| ) throws Exception |
| { |
| PreparedStatement ps = conn.prepareStatement( query ); |
| ParameterMetaData pmd = ps.getParameterMetaData(); |
| |
| assertEquals( pmd.getParameterClassName( 1 ), expectedClassName ); |
| assertEquals( pmd.getParameterType( 1 ), expectedJDBCType ); |
| assertEquals( pmd.getParameterTypeName( 1 ), expectedSQLTypeName ); |
| assertEquals( pmd.getPrecision( 1 ), expectedPrecision ); |
| assertEquals( pmd.getScale( 1 ), expectedScale ); |
| |
| ps.close(); |
| } |
| |
| } |
