Author: jolee Date: 2012-03-13 12:05:28 -0400 (Tue, 13 Mar 2012) New Revision: 3935 Modified: branches/7.4.x/documentation/reference/src/main/docbook/en-US/content/procedures.xml branches/7.4.x/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java branches/7.4.x/engine/src/test/java/org/teiid/query/parser/TestParser.java branches/7.4.x/engine/src/test/java/org/teiid/query/processor/proc/TestProcedureProcessor.java branches/7.4.x/engine/src/test/java/org/teiid/query/sql/visitor/TestSQLStringVisitor.java Log: TEIID-1920 fixing issues with dynamic sql Modified: branches/7.4.x/documentation/reference/src/main/docbook/en-US/content/procedures.xml =================================================================== --- branches/7.4.x/documentation/reference/src/main/docbook/en-US/content/procedures.xml 2012-03-12 18:52:17 UTC (rev 3934) +++ branches/7.4.x/documentation/reference/src/main/docbook/en-US/content/procedures.xml 2012-03-13 16:05:28 UTC (rev 3935) @@ -37,7 +37,7 @@ </para> <para> Usage: - <synopsis>EXECUTE STRING &lt;expression&gt; <optional>AS &lt;variable&gt; &lt;type&gt; [, &lt;variable&gt; &lt;type&gt;]* [INTO &lt;variable&gt;]</optional> + <synopsis>EXECUTE IMMEDIATE &lt;expression&gt; <optional>AS &lt;variable&gt; &lt;type&gt; [, &lt;variable&gt; &lt;type&gt;]* [INTO &lt;variable&gt;]</optional> [USING &lt;variable&gt;=&lt;expression&gt; [,&lt;variable&gt;=&lt;expression&gt;]*] [UPDATE &lt;literal&gt;] </synopsis> </para> @@ -52,7 +52,7 @@ </para> </listitem> <listitem> - <para>The "USING" clause allows the dynamic SQL string to contain variable references that are bound at runtime to specified values. This allows for some independence of the SQL string from the surrounding procedure variable names and input names. In the dynamic command "USING" clause, each variable is specified by short name only. However in the dynamic SQL the "USING" variable must be fully qualified to "UVAR.". The "USING" clause is only for values that will be used in the dynamic SQL as legal expressions. It is not possible to use the "USING" clause to replace table names, keywords, etc. This makes using symbols equivalent in power to normal bind (?) expressions in prepared statements. The "USING" clause helps reduce the amount of string manipulation needed. If a reference is made to a USING symbol in the SQL string that is not bound to a value in the "USING" clause, an exception will occur. + <para>The "USING" clause allows the dynamic SQL string to contain variable references that are bound at runtime to specified values. This allows for some independence of the SQL string from the surrounding procedure variable names and input names. In the dynamic command "USING" clause, each variable is specified by short name only. However in the dynamic SQL the "USING" variable must be fully qualified to "DVAR.". The "USING" clause is only for values that will be used in the dynamic SQL as legal expressions. It is not possible to use the "USING" clause to replace table names, keywords, etc. This makes using symbols equivalent in power to normal bind (?) expressions in prepared statements. The "USING" clause helps reduce the amount of string manipulation needed. If a reference is made to a USING symbol in the SQL string that is not bound to a value in the "USING" clause, an exception will occur. </para> </listitem> <listitem> @@ -71,7 +71,7 @@ DECLARE string sql_string = 'SELECT ID, First || ‘‘ ‘‘ || Last AS Name, Birthdate FROM Customer.Accounts WHERE ' || criteria; /* The execution of the SQL string will create the #temp table with the columns (ID, Name, Birthdate). Note that we also have the USING clause to bind a value to LastName, which is referenced in the criteria. */ -EXECUTE STRING sql_string AS ID integer, Name string, Birthdate date INTO #temp USING LastName='some name'; +EXECUTE IMMEDIATE sql_string AS ID integer, Name string, Birthdate date INTO #temp USING LastName='some name'; /* The temp table can now be used with the values from the Dynamic SQL */ loop on (SELCT ID from #temp) as myCursor ...</programlisting> @@ -98,7 +98,7 @@ END ELSE ERROR "ID or LastName must be specified."; -EXECUTE STRING ‘SELECT ID, First || ‘‘ ‘‘ || Last AS Name, Birthdate FROM Customer.Accounts WHERE ’ || crit USING ID=AccountAccess.GetAccounts.ID, LastName=AccountAccess.GetAccounts.LastName, BirthDay=AccountAccess.GetAccounts.Bday; +EXECUTE IMMEDIATE ‘SELECT ID, First || ‘‘ ‘‘ || Last AS Name, Birthdate FROM Customer.Accounts WHERE ’ || crit USING ID=AccountAccess.GetAccounts.ID, LastName=AccountAccess.GetAccounts.LastName, BirthDay=AccountAccess.GetAccounts.Bday; ...</programlisting> </example> <itemizedlist> @@ -110,7 +110,7 @@ <example> <title>Example Assignment </title> - <programlisting>EXECUTE STRING &lt;expression&gt; AS x string INTO #temp; + <programlisting>EXECUTE IMMEDIATE &lt;expression&gt; AS x string INTO #temp; DECLARE string VARIABLES.RESULT = (SELECT x FROM #temp);</programlisting> </example> </listitem> @@ -138,7 +138,7 @@ <example> <title>Example with AS and INTO clauses</title> <programlisting>/* This name does not need to match the expected update command symbol "count". */ -EXECUTE STRING &lt;expression&gt; AS x integer INTO #temp;</programlisting> +EXECUTE IMMEDIATE &lt;expression&gt; AS x integer INTO #temp;</programlisting> </example> </listitem> <listitem> Modified: branches/7.4.x/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java 2012-03-12 18:52:17 UTC (rev 3934) +++ branches/7.4.x/engine/src/main/java/org/teiid/query/sql/visitor/SQLStringVisitor.java 2012-03-13 16:05:28 UTC (rev 3935) @@ -679,6 +679,8 @@ public void visit( DynamicCommand obj ) { append(EXECUTE); append(SPACE); + append(IMMEDIATE); + append(SPACE); visitNode(obj.getSql()); if (obj.isAsClauseSet()) { Modified: branches/7.4.x/engine/src/test/java/org/teiid/query/parser/TestParser.java =================================================================== --- branches/7.4.x/engine/src/test/java/org/teiid/query/parser/TestParser.java 2012-03-12 18:52:17 UTC (rev 3934) +++ branches/7.4.x/engine/src/test/java/org/teiid/query/parser/TestParser.java 2012-03-13 16:05:28 UTC (rev 3935) @@ -41,90 +41,11 @@ import org.teiid.language.SQLConstants.NonReserved; import org.teiid.language.SQLConstants.Reserved; import org.teiid.language.SortSpecification.NullOrdering; -import org.teiid.query.sql.lang.ArrayTable; -import org.teiid.query.sql.lang.BetweenCriteria; -import org.teiid.query.sql.lang.Command; -import org.teiid.query.sql.lang.CompareCriteria; -import org.teiid.query.sql.lang.CompoundCriteria; -import org.teiid.query.sql.lang.Create; -import org.teiid.query.sql.lang.Criteria; -import org.teiid.query.sql.lang.Delete; -import org.teiid.query.sql.lang.Drop; -import org.teiid.query.sql.lang.DynamicCommand; -import org.teiid.query.sql.lang.ExistsCriteria; -import org.teiid.query.sql.lang.ExpressionCriteria; -import org.teiid.query.sql.lang.From; -import org.teiid.query.sql.lang.FromClause; -import org.teiid.query.sql.lang.GroupBy; -import org.teiid.query.sql.lang.Insert; -import org.teiid.query.sql.lang.Into; -import org.teiid.query.sql.lang.IsNullCriteria; -import org.teiid.query.sql.lang.JoinPredicate; -import org.teiid.query.sql.lang.JoinType; -import org.teiid.query.sql.lang.Limit; -import org.teiid.query.sql.lang.MatchCriteria; -import org.teiid.query.sql.lang.NotCriteria; -import org.teiid.query.sql.lang.OrderBy; -import org.teiid.query.sql.lang.OrderByItem; -import org.teiid.query.sql.lang.PredicateCriteria; -import org.teiid.query.sql.lang.Query; -import org.teiid.query.sql.lang.SPParameter; -import org.teiid.query.sql.lang.Select; -import org.teiid.query.sql.lang.SetClauseList; -import org.teiid.query.sql.lang.SetCriteria; -import org.teiid.query.sql.lang.SetQuery; -import org.teiid.query.sql.lang.StoredProcedure; -import org.teiid.query.sql.lang.SubqueryCompareCriteria; -import org.teiid.query.sql.lang.SubqueryFromClause; -import org.teiid.query.sql.lang.SubquerySetCriteria; -import org.teiid.query.sql.lang.TableFunctionReference; -import org.teiid.query.sql.lang.TextTable; -import org.teiid.query.sql.lang.UnaryFromClause; -import org.teiid.query.sql.lang.Update; -import org.teiid.query.sql.lang.WithQueryCommand; -import org.teiid.query.sql.lang.XMLTable; +import org.teiid.query.sql.lang.*; import org.teiid.query.sql.lang.SetQuery.Operation; import org.teiid.query.sql.lang.TextTable.TextColumn; -import org.teiid.query.sql.proc.AssignmentStatement; -import org.teiid.query.sql.proc.Block; -import org.teiid.query.sql.proc.BreakStatement; -import org.teiid.query.sql.proc.CommandStatement; -import org.teiid.query.sql.proc.ContinueStatement; -import org.teiid.query.sql.proc.CreateUpdateProcedureCommand; -import org.teiid.query.sql.proc.CriteriaSelector; -import org.teiid.query.sql.proc.DeclareStatement; -import org.teiid.query.sql.proc.HasCriteria; -import org.teiid.query.sql.proc.IfStatement; -import org.teiid.query.sql.proc.LoopStatement; -import org.teiid.query.sql.proc.RaiseErrorStatement; -import org.teiid.query.sql.proc.Statement; -import org.teiid.query.sql.proc.TranslateCriteria; -import org.teiid.query.sql.proc.WhileStatement; -import org.teiid.query.sql.symbol.AggregateSymbol; -import org.teiid.query.sql.symbol.AliasSymbol; -import org.teiid.query.sql.symbol.AllInGroupSymbol; -import org.teiid.query.sql.symbol.AllSymbol; -import org.teiid.query.sql.symbol.CaseExpression; -import org.teiid.query.sql.symbol.Constant; -import org.teiid.query.sql.symbol.DerivedColumn; -import org.teiid.query.sql.symbol.ElementSymbol; -import org.teiid.query.sql.symbol.Expression; -import org.teiid.query.sql.symbol.ExpressionSymbol; -import org.teiid.query.sql.symbol.Function; -import org.teiid.query.sql.symbol.GroupSymbol; -import org.teiid.query.sql.symbol.Reference; -import org.teiid.query.sql.symbol.ScalarSubquery; -import org.teiid.query.sql.symbol.SearchedCaseExpression; -import org.teiid.query.sql.symbol.TestCaseExpression; -import org.teiid.query.sql.symbol.TestSearchedCaseExpression; -import org.teiid.query.sql.symbol.TextLine; -import org.teiid.query.sql.symbol.XMLAttributes; -import org.teiid.query.sql.symbol.XMLElement; -import org.teiid.query.sql.symbol.XMLForest; -import org.teiid.query.sql.symbol.XMLNamespaces; -import org.teiid.query.sql.symbol.XMLParse; -import org.teiid.query.sql.symbol.XMLQuery; -import org.teiid.query.sql.symbol.XMLSerialize; +import org.teiid.query.sql.proc.*; +import org.teiid.query.sql.symbol.*; @SuppressWarnings("nls") public class TestParser { @@ -3655,7 +3576,7 @@ CommandStatement cmdStmt = new CommandStatement(sqlCmd); - helpStmtTest("exec string 'SELECT a1 FROM g WHERE a2 = 5' as a1 string into #g;", "EXECUTE 'SELECT a1 FROM g WHERE a2 = 5' AS a1 string INTO #g;", //$NON-NLS-1$ //$NON-NLS-2$ + helpStmtTest("exec string 'SELECT a1 FROM g WHERE a2 = 5' as a1 string into #g;", "EXECUTE IMMEDIATE 'SELECT a1 FROM g WHERE a2 = 5' AS a1 string INTO #g;", //$NON-NLS-1$ //$NON-NLS-2$ cmdStmt); } @@ -3684,7 +3605,7 @@ CommandStatement cmdStmt = new CommandStatement(sqlCmd); - helpStmtTest("execute string z as a1 string, a2 integer into #g update 1;", "EXECUTE z AS a1 string, a2 integer INTO #g UPDATE 1;", //$NON-NLS-1$ //$NON-NLS-2$ + helpStmtTest("execute IMMEDIATE z as a1 string, a2 integer into #g update 1;", "EXECUTE IMMEDIATE z AS a1 string, a2 integer INTO #g UPDATE 1;", //$NON-NLS-1$ //$NON-NLS-2$ cmdStmt); } @@ -3702,7 +3623,7 @@ CommandStatement cmdStmt = new CommandStatement(sqlCmd); - helpStmtTest("execute immediate z using a=b;", "EXECUTE z USING a = b;", //$NON-NLS-1$ //$NON-NLS-2$ + helpStmtTest("execute immediate z using a=b;", "EXECUTE IMMEDIATE z USING a = b;", //$NON-NLS-1$ //$NON-NLS-2$ cmdStmt); } Modified: branches/7.4.x/engine/src/test/java/org/teiid/query/processor/proc/TestProcedureProcessor.java =================================================================== --- branches/7.4.x/engine/src/test/java/org/teiid/query/processor/proc/TestProcedureProcessor.java 2012-03-12 18:52:17 UTC (rev 3934) +++ branches/7.4.x/engine/src/test/java/org/teiid/query/processor/proc/TestProcedureProcessor.java 2012-03-13 16:05:28 UTC (rev 3935) @@ -1378,7 +1378,7 @@ helpTestProcessFailure(plan, dataMgr, - "Couldn't execute the dynamic SQL command \"EXECUTE 'EXEC pm1.sq2(''First'')' AS e1 string, e2 integer\" with the SQL statement \"'EXEC pm1.sq2(''First'')'\" due to: There is a recursive invocation of group 'PM1.SQ2'. Please correct the SQL.", metadata); //$NON-NLS-1$ + "Couldn't execute the dynamic SQL command \"EXECUTE IMMEDIATE 'EXEC pm1.sq2(''First'')' AS e1 string, e2 integer\" with the SQL statement \"'EXEC pm1.sq2(''First'')'\" due to: There is a recursive invocation of group 'PM1.SQ2'. Please correct the SQL.", metadata); //$NON-NLS-1$ } @Test public void testDynamicCommandIncorrectProjectSymbolCount() throws Exception { @@ -1399,7 +1399,7 @@ ProcessorPlan plan = getProcedurePlan(userUpdateStr, metadata); - helpTestProcessFailure(plan, dataMgr, "Couldn't execute the dynamic SQL command \"EXECUTE 'EXEC pm1.sq1(''First'')' AS e1 string, e2 integer\" with the SQL statement \"'EXEC pm1.sq1(''First'')'\" due to: The dynamic sql string contains an incorrect number of elements.", metadata); //$NON-NLS-1$ + helpTestProcessFailure(plan, dataMgr, "Couldn't execute the dynamic SQL command \"EXECUTE IMMEDIATE 'EXEC pm1.sq1(''First'')' AS e1 string, e2 integer\" with the SQL statement \"'EXEC pm1.sq1(''First'')'\" due to: The dynamic sql string contains an incorrect number of elements.", metadata); //$NON-NLS-1$ } @Test public void testDynamicCommandPositional() throws Exception { @@ -1432,7 +1432,7 @@ ProcessorPlan plan = getProcedurePlan(userUpdateStr, metadata); - helpTestProcessFailure(plan, dataMgr, "Couldn't execute the dynamic SQL command \"EXECUTE 'select e1 from pm1.g1'\" with the SQL statement \"'select e1 from pm1.g1'\" due to: The datatype 'string' for element 'e1' in the dynamic SQL cannot be implicitly converted to 'integer'.", metadata); //$NON-NLS-1$ + helpTestProcessFailure(plan, dataMgr, "Couldn't execute the dynamic SQL command \"EXECUTE IMMEDIATE 'select e1 from pm1.g1'\" with the SQL statement \"'select e1 from pm1.g1'\" due to: The datatype 'string' for element 'e1' in the dynamic SQL cannot be implicitly converted to 'integer'.", metadata); //$NON-NLS-1$ } @Test public void testDynamicCommandWithTwoDynamicStatements() throws Exception { Modified: branches/7.4.x/engine/src/test/java/org/teiid/query/sql/visitor/TestSQLStringVisitor.java =================================================================== --- branches/7.4.x/engine/src/test/java/org/teiid/query/sql/visitor/TestSQLStringVisitor.java 2012-03-12 18:52:17 UTC (rev 3934) +++ branches/7.4.x/engine/src/test/java/org/teiid/query/sql/visitor/TestSQLStringVisitor.java 2012-03-13 16:05:28 UTC (rev 3935) @@ -35,36 +35,7 @@ import org.teiid.query.parser.QueryParser; import org.teiid.query.resolver.QueryResolver; import org.teiid.query.sql.LanguageObject; -import org.teiid.query.sql.lang.BetweenCriteria; -import org.teiid.query.sql.lang.Command; -import org.teiid.query.sql.lang.CompareCriteria; -import org.teiid.query.sql.lang.CompoundCriteria; -import org.teiid.query.sql.lang.Criteria; -import org.teiid.query.sql.lang.Delete; -import org.teiid.query.sql.lang.DynamicCommand; -import org.teiid.query.sql.lang.ExistsCriteria; -import org.teiid.query.sql.lang.From; -import org.teiid.query.sql.lang.GroupBy; -import org.teiid.query.sql.lang.Insert; -import org.teiid.query.sql.lang.IsNullCriteria; -import org.teiid.query.sql.lang.JoinPredicate; -import org.teiid.query.sql.lang.JoinType; -import org.teiid.query.sql.lang.Limit; -import org.teiid.query.sql.lang.MatchCriteria; -import org.teiid.query.sql.lang.NotCriteria; -import org.teiid.query.sql.lang.Option; -import org.teiid.query.sql.lang.OrderBy; -import org.teiid.query.sql.lang.Query; -import org.teiid.query.sql.lang.SPParameter; -import org.teiid.query.sql.lang.Select; -import org.teiid.query.sql.lang.SetCriteria; -import org.teiid.query.sql.lang.SetQuery; -import org.teiid.query.sql.lang.StoredProcedure; -import org.teiid.query.sql.lang.SubqueryCompareCriteria; -import org.teiid.query.sql.lang.SubqueryFromClause; -import org.teiid.query.sql.lang.SubquerySetCriteria; -import org.teiid.query.sql.lang.UnaryFromClause; -import org.teiid.query.sql.lang.Update; +import org.teiid.query.sql.lang.*; import org.teiid.query.sql.lang.SetQuery.Operation; import org.teiid.query.sql.proc.AssignmentStatement; import org.teiid.query.sql.proc.Block; @@ -75,22 +46,7 @@ import org.teiid.query.sql.proc.HasCriteria; import org.teiid.query.sql.proc.IfStatement; import org.teiid.query.sql.proc.RaiseErrorStatement; -import org.teiid.query.sql.symbol.AggregateSymbol; -import org.teiid.query.sql.symbol.AliasSymbol; -import org.teiid.query.sql.symbol.AllInGroupSymbol; -import org.teiid.query.sql.symbol.AllSymbol; -import org.teiid.query.sql.symbol.CaseExpression; -import org.teiid.query.sql.symbol.Constant; -import org.teiid.query.sql.symbol.ElementSymbol; -import org.teiid.query.sql.symbol.Expression; -import org.teiid.query.sql.symbol.ExpressionSymbol; -import org.teiid.query.sql.symbol.Function; -import org.teiid.query.sql.symbol.GroupSymbol; -import org.teiid.query.sql.symbol.Reference; -import org.teiid.query.sql.symbol.ScalarSubquery; -import org.teiid.query.sql.symbol.SearchedCaseExpression; -import org.teiid.query.sql.symbol.TestCaseExpression; -import org.teiid.query.sql.symbol.TestSearchedCaseExpression; +import org.teiid.query.sql.symbol.*; import org.teiid.query.unittest.RealMetadataFactory; @@ -1721,7 +1677,7 @@ obj.setAsClauseSet(true); obj.setIntoGroup(new GroupSymbol("#g")); //$NON-NLS-1$ - helpTest(obj, "EXECUTE 'SELECT a1 FROM g WHERE a2 = 5' AS a1 string INTO #g"); //$NON-NLS-1$ + helpTest(obj, "EXECUTE IMMEDIATE 'SELECT a1 FROM g WHERE a2 = 5' AS a1 string INTO #g"); //$NON-NLS-1$ } public void testScalarSubquery() {