@Test public void testExecNoParams() { StoredProcedure proc = new StoredProcedure(); proc.setProcedureName("myproc"); //$NON-NLS-1$ helpTest(proc, "EXEC myproc()"); //$NON-NLS-1$ }
@Test public void testStoredQueryWithNoParameter(){ StoredProcedure storedQuery = new StoredProcedure(); storedQuery.setProcedureName("proc1"); //$NON-NLS-1$ helpTest("exec proc1()", "EXEC proc1()", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$ helpTest("execute proc1()", "EXEC proc1()", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testExecInputOutputParam() { StoredProcedure proc = new StoredProcedure(); proc.setProcedureName("myproc"); //$NON-NLS-1$ SPParameter param1 = new SPParameter(1, new Constant(new Integer(5))); param1.setParameterType(ParameterInfo.IN); proc.setParameter(param1); SPParameter param2 = new SPParameter(2, ParameterInfo.OUT, "x"); //$NON-NLS-1$ proc.setParameter(param2); helpTest(proc, "EXEC myproc(5)"); //$NON-NLS-1$ }
@Test public void testExecOutputInputParam() { StoredProcedure proc = new StoredProcedure(); proc.setProcedureName("myproc"); //$NON-NLS-1$ SPParameter param2 = new SPParameter(2, ParameterInfo.OUT, "x"); //$NON-NLS-1$ proc.setParameter(param2); SPParameter param1 = new SPParameter(1, new Constant(new Integer(5))); param1.setParameterType(ParameterInfo.IN); proc.setParameter(param1); helpTest(proc, "EXEC myproc(5)"); //$NON-NLS-1$ }
@Test public void testCache1() { String sql = "/*+ cache */ execute foo()"; //$NON-NLS-1$ StoredProcedure sp = new StoredProcedure(); sp.setCacheHint(new CacheHint()); sp.setProcedureName("foo"); //$NON-NLS-1$ TestParser.helpTest(sql, "/*+ cache */ EXEC foo()", sp); //$NON-NLS-1$ }
@Test public void testExecReturnParam() { StoredProcedure proc = new StoredProcedure(); proc.setProcedureName("myproc"); //$NON-NLS-1$ SPParameter param = new SPParameter(1, ParameterInfo.RETURN_VALUE, "ret"); //$NON-NLS-1$ proc.setParameter(param); helpTest(proc, "EXEC myproc()"); //$NON-NLS-1$ }
@Test public void testStoredQueryWithOption(){ StoredProcedure storedQuery = new StoredProcedure(); storedQuery.setProcedureName("proc1"); //$NON-NLS-1$ Option option = new Option(); option.setNoCache(true); storedQuery.setOption(option); TestParser.helpTest("exec proc1() option nocache", "EXEC proc1() OPTION NOCACHE", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testNestedTable() throws Exception { String sql = "SELECT * from TABLE(exec foo()) as x"; //$NON-NLS-1$ Query query = new Query(); query.setSelect(new Select(Arrays.asList(new MultipleElementSymbol()))); StoredProcedure sp = new StoredProcedure(); sp.setProcedureName("foo"); SubqueryFromClause sfc = new SubqueryFromClause("x", sp); sfc.setLateral(true); query.setFrom(new From(Arrays.asList(sfc))); helpTest(sql, "SELECT * FROM LATERAL(EXEC foo()) AS x", query); }
@Test public void testExecInputParam() { StoredProcedure proc = new StoredProcedure(); proc.setProcedureName("myproc"); //$NON-NLS-1$ SPParameter param = new SPParameter(1, new Reference(0)); proc.setParameter(param); helpTest(proc, "EXEC myproc(?)"); //$NON-NLS-1$ }
@Test public void testStoredQuery(){ StoredProcedure storedQuery = new StoredProcedure(); storedQuery.setProcedureName("proc1"); //$NON-NLS-1$ SPParameter parameter = new SPParameter(1, new Constant("param1")); //$NON-NLS-1$ parameter.setParameterType(ParameterInfo.IN); storedQuery.setParameter(parameter); helpTest("Exec proc1('param1')", "EXEC proc1('param1')", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$ helpTest("execute proc1('param1')", "EXEC proc1('param1')", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testVariablesInExec(){ StoredProcedure storedQuery = new StoredProcedure(); storedQuery.setProcedureName("proc1"); //$NON-NLS-1$ SPParameter parameter = new SPParameter(1, new ElementSymbol("param1")); //$NON-NLS-1$ parameter.setParameterType(ParameterInfo.IN); storedQuery.setParameter(parameter); helpTest("Exec proc1(param1)", "EXEC proc1(param1)", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$ helpTest("execute proc1(param1)", "EXEC proc1(param1)", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testExecNamedParam() { StoredProcedure proc = new StoredProcedure(); proc.setDisplayNamedParameters(true); proc.setProcedureName("myproc"); //$NON-NLS-1$ SPParameter param = new SPParameter(1, new Reference(0)); param.setName("p1");//$NON-NLS-1$ proc.setParameter(param); helpTest(proc, "EXEC myproc(p1 => ?)"); //$NON-NLS-1$ }
@Test public void testCacheHintCallableStatement() { String sql = "/*+ cache */ { ? = call proc() }"; //$NON-NLS-1$ StoredProcedure sp = new StoredProcedure(); SPParameter param = new SPParameter(1, null); param.setParameterType(SPParameter.RETURN_VALUE); sp.setParameter(param); sp.setProcedureName("proc"); sp.setCallableStatement(true); CacheHint hint = new CacheHint(); sp.setCacheHint(hint); TestParser.helpTest(sql, "/*+ cache */ ? = EXEC proc()", sp); //$NON-NLS-1$ }
@Test public void testCase3281NamedVariable() { StoredProcedure storedQuery = new StoredProcedure(); storedQuery.setDisplayNamedParameters(true); storedQuery.setProcedureName("proc1"); //$NON-NLS-1$ SPParameter parameter = new SPParameter(1, new Constant("paramValue1")); //$NON-NLS-1$ parameter.setName("param1"); //$NON-NLS-1$ parameter.setParameterType(ParameterInfo.IN); storedQuery.setParameter(parameter); helpTest("Exec proc1(param1 = 'paramValue1')", "EXEC proc1(param1 => 'paramValue1')", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$ helpTest("execute proc1(param1 = 'paramValue1')", "EXEC proc1(param1 => 'paramValue1')", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$ }
public void testExecParamElement() { StoredProcedure exec = new StoredProcedure(); exec.setProcedureName("pm1.proc1"); //$NON-NLS-1$ exec.setProcedureID("proc"); //$NON-NLS-1$ SPParameter param1 = new SPParameter(1, exampleElement(true, 1)); exec.setParameter(param1); // Run symbol mapper StaticSymbolMappingVisitor visitor = new StaticSymbolMappingVisitor(getSymbolMap()); DeepPreOrderNavigator.doVisit(exec, visitor); // Check that element got switched assertEquals("Stored proc param did not get mapped correctly: ", exampleElement(false, 1), param1.getExpression()); //$NON-NLS-1$ }
public void testExecName() { StoredProcedure exec = new StoredProcedure(); exec.setProcedureName(exampleGroup(true, 1).getName()); exec.setProcedureID("proc"); //$NON-NLS-1$ // Run symbol mapper StaticSymbolMappingVisitor visitor = new StaticSymbolMappingVisitor(getSymbolMap()); DeepPreOrderNavigator.doVisit(exec, visitor); // Check that group got mapped assertEquals("Procedure name did not get mapped correctly: ", exampleGroup(false, 1).getName(), exec.getProcedureName()); //$NON-NLS-1$ }
@Test public void testStoredQueryWithNoParameter2(){ StoredProcedure storedQuery = new StoredProcedure(); storedQuery.setProcedureName("proc1"); //$NON-NLS-1$ From from = new From(); SubqueryFromClause sfc = new SubqueryFromClause("x", storedQuery); //$NON-NLS-1$ from.addClause(sfc); Select select = new Select(); select.addSymbol(new ElementSymbol("X.A")); //$NON-NLS-1$ Query query = new Query(); query.setSelect(select); query.setFrom(from); helpTest("SELECT X.A FROM (exec proc1()) AS X", "SELECT X.A FROM (EXEC proc1()) AS X", query); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testStoredQuery2SanityCheck(){ StoredProcedure storedQuery = new StoredProcedure(); storedQuery.setProcedureName("proc1"); //$NON-NLS-1$ SPParameter parameter = new SPParameter(1, new Constant("param1")); //$NON-NLS-1$ storedQuery.setParameter(parameter); From from = new From(); SubqueryFromClause sfc = new SubqueryFromClause("x", storedQuery); //$NON-NLS-1$ from.addClause(sfc); Select select = new Select(); select.addSymbol(new ElementSymbol("x.a")); //$NON-NLS-1$ helpTest("exec proc1('param1')", "EXEC proc1('param1')", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testExecNamedParams() { StoredProcedure proc = new StoredProcedure(); proc.setDisplayNamedParameters(true); proc.setProcedureName("myproc"); //$NON-NLS-1$ SPParameter param = new SPParameter(1, new Reference(0)); param.setName("p1");//$NON-NLS-1$ proc.setParameter(param); SPParameter param2 = new SPParameter(2, new Reference(0)); param2.setName("p2");//$NON-NLS-1$ proc.setParameter(param2); helpTest(proc, "EXEC myproc(p1 => ?, p2 => ?)"); //$NON-NLS-1$ }
@Test public void testCase3281QuotedNamedVariableFails2() { StoredProcedure storedQuery = new StoredProcedure(); storedQuery.setProcedureName("proc1"); //$NON-NLS-1$ SPParameter param1 = new SPParameter(1, new CompareCriteria(new Constant("a"), CompareCriteria.EQ, new Constant("b"))); //$NON-NLS-1$ param1.setParameterType(ParameterInfo.IN); storedQuery.setParameter(param1); helpTest("Exec proc1('a' = 'b')", "EXEC proc1(('a' = 'b'))", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$ }