@Test public void testInvalidException() throws Exception { String ddl = "create virtual procedure vproc (x integer) returns integer as begin declare object e = sqlexception 'hello'; raise e; raise sqlexception 'hello world' sqlstate 'abc', 1 chain e; end;"; try { TestProcedureResolving.createMetadata(ddl); fail(); } catch (RuntimeException e) { assertEquals("TEIID31080 test.vproc validation error: TEIID31120 An exception may only be chained to another exception. e is not valid.", e.getMessage()); } }
@Test public void testExceptionGroup() throws Exception { String ddl = "create virtual procedure vproc () returns string as begin select 1/0; exception e \"return\" = e.state || ' ' || e.errorcode || ' ' || e.teiidcode || ' ' || cast(e.exception as string) || ' ' || cast(e.chain as string); end;"; TransformationMetadata tm = TestProcedureResolving.createMetadata(ddl); String sql = "call vproc()"; //$NON-NLS-1$ ProcessorPlan plan = getProcedurePlan(sql, tm); HardcodedDataManager dataManager = new HardcodedDataManager(tm); helpTestProcess(plan, new List[] {Arrays.asList("50000 30328 TEIID30328 org.teiid.jdbc.TeiidSQLException: TEIID30328 Unable to evaluate (1 / 0): TEIID30384 Error while evaluating function / org.teiid.api.exception.query.ExpressionEvaluationException: TEIID30328 Unable to evaluate (1 / 0): TEIID30384 Error while evaluating function /")}, dataManager, tm); }
@Test public void testNestedBlock() throws Exception { String ddl = "create virtual procedure proc (z STRING) returns table (x string, y string) as begin declare string x = z; select x without return; begin select x, x; end end;"; TransformationMetadata tm = TestProcedureResolving.createMetadata(ddl); String sql = "call proc('a')"; //$NON-NLS-1$ ProcessorPlan plan = getProcedurePlan(sql, tm); HardcodedDataManager dataManager = new HardcodedDataManager(tm); List[] expected = new List[] { Arrays.asList("a", "a") }; //$NON-NLS-1$ helpTestProcess(plan, expected, dataManager, tm); }
/** * ensures that a processing error is trappable */ @Test public void testExceptionHandlingWithResultSet() throws Exception { String ddl = "create virtual procedure proc2 (x integer) returns table(y integer) as begin atomic select 1; begin select 1/x; end exception e end;"; TransformationMetadata tm = TestProcedureResolving.createMetadata(ddl); String sql = "call proc2(0)"; //$NON-NLS-1$ ProcessorPlan plan = getProcedurePlan(sql, tm); HardcodedDataManager dataManager = new HardcodedDataManager(tm); helpTestProcess(plan, new List[] {Arrays.asList(1)}, dataManager, tm); }
@Test public void testResultSetAtomic() throws Exception { String ddl = "create virtual procedure proc2 (x integer) returns table(y integer) as begin select 1; begin atomic select 2; end end;"; TransformationMetadata tm = TestProcedureResolving.createMetadata(ddl); String sql = "call proc2(0)"; //$NON-NLS-1$ ProcessorPlan plan = getProcedurePlan(sql, tm); HardcodedDataManager dataManager = new HardcodedDataManager(tm); helpTestProcess(plan, new List[] {Arrays.asList(2)}, dataManager, tm); }
@Test public void testVarArgsVirtNull() throws Exception { String ddl = "create virtual procedure vproc (x integer, VARIADIC z integer not null) returns (y integer) as begin select array_length(z); end;"; TransformationMetadata tm = TestProcedureResolving.createMetadata(ddl); String sql = "call vproc(1, (select cast(null as integer[])))"; //$NON-NLS-1$ ProcessorPlan plan = getProcedurePlan(sql, tm); HardcodedDataManager dataManager = new HardcodedDataManager(); helpTestProcess(plan, new List[] {Collections.singletonList(null)}, dataManager, tm); }
@Test public void testVarArgsFunctionInVirt() throws Exception { String ddl = "create virtual procedure proc (VARIADIC z STRING) returns string as \"return\" = coalesce(null, null, z);"; TransformationMetadata tm = TestProcedureResolving.createMetadata(ddl); String sql = "call proc(1, 2, 3)"; //$NON-NLS-1$ ProcessorPlan plan = getProcedurePlan(sql, tm); HardcodedDataManager dataManager = new HardcodedDataManager(tm); // note that we're properly cast to string, even though we called with int List[] expected = new List[] { Arrays.asList("1") }; //$NON-NLS-1$ helpTestProcess(plan, expected, dataManager, tm); }
@Test public void testReturnStatementWithDynamicCommad() throws Exception { String ddl = "create virtual procedure proc (z STRING) returns integer as begin execute immediate 'select '' || z || '''; return 1; end;"; TransformationMetadata tm = TestProcedureResolving.createMetadata(ddl); String sql = "{? = call proc('a')}"; //$NON-NLS-1$ ProcessorPlan plan = getProcedurePlan(sql, tm); HardcodedDataManager dataManager = new HardcodedDataManager(tm); List[] expected = new List[] { Arrays.asList(1) }; //$NON-NLS-1$ helpTestProcess(plan, expected, dataManager, tm); }
@Test public void testExceptionHandlingWithDynamic() throws Exception { String ddl = "create virtual procedure vproc (x integer) returns integer as begin " + "raise sqlexception 'hello world' sqlstate 'abc', 5;" + "exception e " + "execute immediate 'select \"ERRORCODE\"' as x integer into #temp; " + "\"return\" = (select x from #temp);"+ "end;"; TransformationMetadata tm = TestProcedureResolving.createMetadata(ddl); String sql = "call vproc(1)"; //$NON-NLS-1$ ProcessorPlan plan = getProcedurePlan(sql, tm); HardcodedDataManager dataManager = new HardcodedDataManager(tm); helpTestProcess(plan, new List[] {Arrays.asList(5)}, dataManager, tm); }
@Test public void testExceptionHandlingDynamicError() throws Exception { String ddl = "create virtual procedure vproc (x integer) returns integer as begin " + "execute immediate 'select x/0';" + "exception e " + "execute immediate 'select x' as x integer into #temp; " + "\"return\" = (select x from #temp);"+ "end;"; TransformationMetadata tm = TestProcedureResolving.createMetadata(ddl); String sql = "call vproc(1)"; //$NON-NLS-1$ ProcessorPlan plan = getProcedurePlan(sql, tm); HardcodedDataManager dataManager = new HardcodedDataManager(tm); helpTestProcess(plan, new List[] {Arrays.asList(1)}, dataManager, tm); }
@Test public void testExceptionHandlingWithLoops() throws Exception { String ddl = "create virtual procedure proc2 (out x integer result) as " + "begin create local temporary table t (i integer); insert into t (i) values (0); " + "begin loop on (select * from t) as x select 1/0; exception e end " + "insert into t (i) values (1); " + "declare integer result = 0; " + "loop on (select * from t) as x result = result + 1; " + "x = result;" + "select result; end;"; TransformationMetadata tm = TestProcedureResolving.createMetadata(ddl); String sql = "call proc2()"; //$NON-NLS-1$ ProcessorPlan plan = getProcedurePlan(sql, tm); HardcodedDataManager dataManager = new HardcodedDataManager(tm); helpTestProcess(plan, new List[] {Arrays.asList(2)}, dataManager, tm); }
@Test public void testVarArgs() throws Exception { String ddl = "create foreign procedure proc (x integer, VARIADIC z integer); create virtual procedure vproc (x integer, VARIADIC z integer) returns integer as begin \"return\" = z[2] + array_length(z); call proc(x, z); end;"; TransformationMetadata tm = TestProcedureResolving.createMetadata(ddl); String sql = "call vproc(1, 2, 3)"; //$NON-NLS-1$ ProcessorPlan plan = getProcedurePlan(sql, tm); HardcodedDataManager dataManager = new HardcodedDataManager(tm); dataManager.addData("EXEC proc(1, 2, 3)", new List<?>[0]); // Create expected results List[] expected = new List[] { Arrays.asList(5) }; //$NON-NLS-1$ helpTestProcess(plan, expected, dataManager, tm); }
@Test public void testReturnStatement() throws Exception { String ddl = "create virtual procedure proc (OUT a string RESULT, z STRING) returns table (x string, y string) as begin declare string x = z; select x without return; if (z = 'a') return 2; else if (z = 'b') return; begin select x, x; end end;"; TransformationMetadata tm = TestProcedureResolving.createMetadata(ddl); String sql = "{? = call proc('a')}"; //$NON-NLS-1$ ProcessorPlan plan = getProcedurePlan(sql, tm); HardcodedDataManager dataManager = new HardcodedDataManager(tm); List[] expected = new List[] { Arrays.asList(null, null, "2") }; //$NON-NLS-1$ helpTestProcess(plan, expected, dataManager, tm); sql = "{? = call proc('b')}"; //$NON-NLS-1$ plan = getProcedurePlan(sql, tm); expected = new List[] { Arrays.asList(null, null, null) }; //$NON-NLS-1$ helpTestProcess(plan, expected, dataManager, tm); }
/** * ensures that the whole result is formed so that the error does not escape the handler */ @Test public void testExceptionHandlingWithResultSet1() throws Exception { String ddl = "create virtual procedure proc2 (x integer) as begin create local temporary table t (i integer); insert into t (i) values (1); declare integer y = 0; while (y < 16) begin insert into t (i) select 1 from t; y = y+1; end insert into t (i) values (0); select cast(1/i as string) from t; exception e end;"; TransformationMetadata tm = TestProcedureResolving.createMetadata(ddl); String sql = "call proc2(0)"; //$NON-NLS-1$ ProcessorPlan plan = getProcedurePlan(sql, tm); HardcodedDataManager dataManager = new HardcodedDataManager(tm); helpTestProcess(plan, new List[0], dataManager, tm); }
@Test public void testExceptionHandlingLoopException() throws Exception { String ddl = "create virtual procedure pr() as\n" + "begin\n" + " error 'a';\n" + "end ;;"; TransformationMetadata tm = TestProcedureResolving.createMetadata(ddl); String sql = "BEGIN\n" + " LOOP ON (SELECT 1 union all select 2) AS market\n" + " BEGIN\n" + " CALL pr();\n" + " EXCEPTION e\n" + " END\n" + "END ;"; //$NON-NLS-1$ ProcessorPlan plan = getProcedurePlan(sql, tm); HardcodedDataManager dataManager = new HardcodedDataManager(tm); helpTestProcess(plan, new List[] {}, dataManager, tm); }
@Test public void testVarArgsNull() throws Exception { String ddl = "create foreign procedure proc (x integer, VARIADIC z integer not null); create virtual procedure vproc (x integer, VARIADIC z integer) returns integer as begin \"return\" = z[2] + array_length(z); call proc(x, z); end;"; TransformationMetadata tm = TestProcedureResolving.createMetadata(ddl); String sql = "call vproc(1, cast(null as integer[]))"; //$NON-NLS-1$ ProcessorPlan plan = getProcedurePlan(sql, tm); HardcodedDataManager dataManager = new HardcodedDataManager(tm); dataManager.addData("EXEC proc(1)", new List<?>[0]); // Create expected results List[] expected = new List[] { Collections.singletonList(null) }; //$NON-NLS-1$ helpTestProcess(plan, expected, dataManager, tm); sql = "call vproc(x=>1, z=>null)"; //$NON-NLS-1$ plan = getProcedurePlan(sql, tm); helpTestProcess(plan, expected, dataManager, tm); }
@Test public void testOptionalParams1() throws Exception { String ddl = "create foreign procedure proc (x integer, y string NOT NULL, z integer);\n"; TransformationMetadata tm = createMetadata(ddl); String sql = "call proc (1, 'a')"; //$NON-NLS-1$ StoredProcedure sp = (StoredProcedure) TestResolver.helpResolve(sql, tm); assertEquals(new Constant("a", DataTypeManager.DefaultDataClasses.STRING), sp.getParameter(2).getExpression()); }
@Test public void testVarArgs2() throws Exception { String ddl = "create foreign procedure proc (VARIADIC z object) returns (x string);\n"; TransformationMetadata tm = createMetadata(ddl); String sql = "call proc ()"; //$NON-NLS-1$ StoredProcedure sp = (StoredProcedure) TestResolver.helpResolve(sql, tm); assertEquals("EXEC proc()", sp.toString()); assertEquals(new Array(DataTypeManager.DefaultDataClasses.OBJECT, new ArrayList<Expression>(0)), sp.getParameter(1).getExpression()); sql = "call proc (1, (2, 3))"; //$NON-NLS-1$ sp = (StoredProcedure) TestResolver.helpResolve(sql, tm); assertEquals("EXEC proc(1, (2, 3))", sp.toString()); ArrayList<Expression> expressions = new ArrayList<Expression>(); expressions.add(new Constant(1)); expressions.add(new Array(DataTypeManager.DefaultDataClasses.INTEGER, Arrays.asList((Expression)new Constant(2), new Constant(3)))); assertEquals(new Array(DataTypeManager.DefaultDataClasses.OBJECT, expressions), sp.getParameter(1).getExpression()); }
@Test public void testVarArgs1() throws Exception { String ddl = "create foreign procedure proc (VARIADIC z integer) returns (x string);\n"; TransformationMetadata tm = createMetadata(ddl); String sql = "call proc ()"; //$NON-NLS-1$ StoredProcedure sp = (StoredProcedure) TestResolver.helpResolve(sql, tm); assertEquals("EXEC proc()", sp.toString()); assertEquals(new Array(DataTypeManager.DefaultDataClasses.INTEGER, new ArrayList<Expression>(0)), sp.getParameter(1).getExpression()); sp = (StoredProcedure) QueryRewriter.evaluateAndRewrite(sp, new Evaluator(null, null, null), null, tm); LanguageBridgeFactory lbf = new LanguageBridgeFactory(tm); Call call = (Call)lbf.translate(sp); assertEquals("EXEC proc()", call.toString()); //we pass to the translator level flattened, so no argument assertEquals(0, call.getArguments().size()); }
@Test public void testOptionalParams() throws Exception { String ddl = "create foreign procedure proc (x integer, y string);\n"; TransformationMetadata tm = createMetadata(ddl); String sql = "call proc (1)"; //$NON-NLS-1$ StoredProcedure sp = (StoredProcedure) TestResolver.helpResolve(sql, tm); assertEquals(new Constant(null, DataTypeManager.DefaultDataClasses.STRING), sp.getParameter(2).getExpression()); sql = "call proc (1, 'a')"; //$NON-NLS-1$ sp = (StoredProcedure) TestResolver.helpResolve(sql, tm); assertEquals(new Constant("a", DataTypeManager.DefaultDataClasses.STRING), sp.getParameter(2).getExpression()); }