Author: shawkins Date: 2012-10-01 22:08:01 -0400 (Mon, 01 Oct 2012) New Revision: 4498 Modified: trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCDirectQueryExecution.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCPlugin.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/TestJDBCDirectQueryExecution.java trunk/engine/src/main/java/org/teiid/query/metadata/DirectQueryMetadataRepository.java Log: TEIID-2176 refinements to jdbc native query support Modified: trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java =================================================================== --- trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java 2012-10-02 00:34:06 UTC (rev 4497) +++ trunk/api/src/main/java/org/teiid/translator/ExecutionFactory.java 2012-10-02 02:08:01 UTC (rev 4498) @@ -272,6 +272,11 @@ public Execution createExecution(Command command, ExecutionContext executionContext, RuntimeMetadata metadata, C connection) throws TranslatorException { if (command instanceof Call) { Call obj = (Call)command; + //TODO: our extension property support in designer makes ad-hoc properties impossible, so + //we just match based upon name. it would be better to have the metadatarepository/tooling add a teiid property + //to explicitly set this proc as direct. + //the other approach would be to addd a native system stored procedure, but that would require + //special security semantics, whereas this proc can be secured on a schema basis if (supportsNativeQueries() && obj.getMetadataObject().getName().equals(getNativeQueryProcedureName())) { List<Argument> arguments = obj.getArguments(); return createDirectExecution(arguments, command, executionContext, metadata, connection); @@ -1054,7 +1059,7 @@ * True, if this translator supports execution of source specific commands unaltered through 'native' procedure. * @return */ - @TranslatorProperty(display="Supports Native Queries", description="True, if this translator supports execution of source specific commands unaltered through 'native' procedure", advanced=true) + @TranslatorProperty(display="Supports Native Queries", description="True, if this translator supports execution of source specific commands unaltered through a 'native' procedure", advanced=true) public boolean supportsNativeQueries() { return this.supportsNativeQueries; } @@ -1068,7 +1073,7 @@ * of the procedure is defined automatically. * @return */ - @TranslatorProperty(display="Name of the native query", description="The name of the procedure that is considered as the name of the direct query procedure", advanced=true) + @TranslatorProperty(display="Name of the native query", description="The name of the direct query procedure", advanced=true) public String getNativeQueryProcedureName() { return this.nativeProcedureName; } Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCDirectQueryExecution.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCDirectQueryExecution.java 2012-10-02 00:34:06 UTC (rev 4497) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCDirectQueryExecution.java 2012-10-02 02:08:01 UTC (rev 4498) @@ -25,7 +25,6 @@ package org.teiid.translator.jdbc; -import java.sql.CallableStatement; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; @@ -61,64 +60,41 @@ List<Argument> parameters = this.arguments.subList(1, this.arguments.size()); LogManager.logTrace(LogConstants.CTX_CONNECTOR, "Source sql", sourceSQL); //$NON-NLS-1$ - boolean prepared = (sourceSQL.indexOf('?') != -1); - boolean callable = (sourceSQL.indexOf("call ") != -1); //$NON-NLS-1$ - int paramCount = getParamCount(sourceSQL); + int paramCount = parameters.size(); try { - if (callable) { - CallableStatement cstmt = getCallableStatement(sourceSQL); - this.results = this.executionFactory.executeStoredProcedure(cstmt, parameters, TypeFacility.RUNTIME_TYPES.OBJECT); - this.columnCount = this.results.getMetaData().getColumnCount(); - } + Statement stmt; + boolean hasResults = false; + + if(paramCount > 0) { + PreparedStatement pstatement = getPreparedStatement(sourceSQL); + for (int i = 0; i < paramCount; i++) { + Argument arg = parameters.get(i); + //TODO: if ParameterMetadata is supported we could use that type + this.executionFactory.bindValue(pstatement, arg.getArgumentValue(), arg.getArgumentValue().getType(), i); + } + stmt = pstatement; + hasResults = pstatement.execute(); + } else { - Statement stmt; - boolean hasResults = false; - - if(prepared) { - PreparedStatement pstatement = getPreparedStatement(sourceSQL); - for (int i = 0; i < paramCount; i++) { - if (this.arguments.size()-1 < paramCount) { - throw new TranslatorException(JDBCPlugin.Util.gs(JDBCPlugin.Event.TEIID11019, sourceSQL)); - } - Argument arg = this.arguments.get(i+1); - pstatement.setObject(i+1, arg.getArgumentValue().getValue()); - } - stmt = pstatement; - hasResults = pstatement.execute(); - } - else { - stmt = getStatement(); - hasResults = stmt.execute(sourceSQL); - } - - if (hasResults) { - this.results = stmt.getResultSet(); - this.columnCount = this.results.getMetaData().getColumnCount(); - } - else { - this.updateCount = stmt.getUpdateCount(); - } + //TODO: when array support becomes more robust calling like "exec native('sql', ARRAY[]) could still be prepared + stmt = getStatement(); + hasResults = stmt.execute(sourceSQL); } + + if (hasResults) { + this.results = stmt.getResultSet(); + this.columnCount = this.results.getMetaData().getColumnCount(); + } + else { + this.updateCount = stmt.getUpdateCount(); + } addStatementWarnings(); } catch (SQLException e) { throw new JDBCExecutionException(JDBCPlugin.Event.TEIID11008, e, sourceSQL); } } - private int getParamCount(String sourceSQL) { - int paramCount = 0; - int idx = -1; - while (true) { - idx = sourceSQL.indexOf('?', idx+1); - if (idx == -1) { - break; - } - paramCount++; - } - return paramCount; - } - @Override public List<?> next() throws TranslatorException, DataNotAvailableException { try { @@ -153,6 +129,6 @@ @Override public List<?> getOutputParameterValues() throws TranslatorException { - return null; + return null; //could support as an array of output values via given that the native procedure returns an array value } } Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCPlugin.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCPlugin.java 2012-10-02 00:34:06 UTC (rev 4497) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCPlugin.java 2012-10-02 02:08:01 UTC (rev 4498) @@ -54,6 +54,5 @@ TEIID11016, TEIID11017, TEIID11018, - TEIID11019 } } Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/TestJDBCDirectQueryExecution.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/TestJDBCDirectQueryExecution.java 2012-10-02 00:34:06 UTC (rev 4497) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/TestJDBCDirectQueryExecution.java 2012-10-02 02:08:01 UTC (rev 4498) @@ -24,7 +24,6 @@ import static org.junit.Assert.*; -import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.PreparedStatement; @@ -32,7 +31,6 @@ import java.sql.ResultSetMetaData; import java.sql.Statement; - import org.junit.Test; import org.mockito.Mockito; import org.teiid.language.Command; @@ -43,29 +41,6 @@ @SuppressWarnings("nls") public class TestJDBCDirectQueryExecution { - @Test public void testProcedureExecution() throws Exception { - Command command = TranslationHelper.helpTranslate(TranslationHelper.BQT_VDB, "exec native('{?=call spTest8a()}')"); //$NON-NLS-1$ - Connection connection = Mockito.mock(Connection.class); - CallableStatement cs = Mockito.mock(CallableStatement.class); - ResultSet rs = Mockito.mock(ResultSet.class); - ResultSetMetaData rsm = Mockito.mock(ResultSetMetaData.class); - - Mockito.stub(cs.getUpdateCount()).toReturn(-1); - Mockito.stub(cs.getResultSet()).toReturn(rs); - Mockito.stub(rs.getMetaData()).toReturn(rsm); - Mockito.stub(rsm.getColumnCount()).toReturn(1); - Mockito.stub(connection.prepareCall("{?=call spTest8a()}")).toReturn(cs); //$NON-NLS-1$ - Mockito.stub(rs.next()).toReturn(true); - Mockito.stub(rs.getObject(1)).toReturn(5); - Mockito.stub(connection.getMetaData()).toReturn(Mockito.mock(DatabaseMetaData.class)); - - JDBCExecutionFactory ef = new JDBCExecutionFactory(); - ef.setSupportsNativeQueries(true); - ResultSetExecution execution = (ResultSetExecution)ef.createExecution(command, Mockito.mock(ExecutionContext.class), Mockito.mock(RuntimeMetadata.class), connection); - execution.execute(); - assertArrayEquals(new Object[] {5}, (Object[])execution.next().get(0)); - } - @Test public void testSelectExecution() throws Exception { Command command = TranslationHelper.helpTranslate(TranslationHelper.BQT_VDB, "call native('select * from Source')"); //$NON-NLS-1$ Connection connection = Mockito.mock(Connection.class); Modified: trunk/engine/src/main/java/org/teiid/query/metadata/DirectQueryMetadataRepository.java =================================================================== --- trunk/engine/src/main/java/org/teiid/query/metadata/DirectQueryMetadataRepository.java 2012-10-02 00:34:06 UTC (rev 4497) +++ trunk/engine/src/main/java/org/teiid/query/metadata/DirectQueryMetadataRepository.java 2012-10-02 02:08:01 UTC (rev 4498) @@ -40,14 +40,14 @@ if (executionFactory != null && executionFactory.supportsNativeQueries()) { Procedure p = factory.addProcedure(executionFactory.getNativeQueryProcedureName()); - p.setAnnotation("Invokes translator with provided native query that returns result in array of values"); //$NON-NLS-1$ + p.setAnnotation("Invokes translator with a native query that returns results in array of values"); //$NON-NLS-1$ ProcedureParameter param = factory.addProcedureParameter("request", TypeFacility.RUNTIME_NAMES.STRING, Type.In, p); //$NON-NLS-1$ param.setAnnotation("The native query to execute"); //$NON-NLS-1$ param.setNullType(NullType.No_Nulls); param = factory.addProcedureParameter("variable", TypeFacility.RUNTIME_NAMES.OBJECT, Type.In, p); //$NON-NLS-1$ - param.setAnnotation("Any number of varaibles, depends upon how a translator uses them individually"); //$NON-NLS-1$ + param.setAnnotation("Any number of varaibles; usage will vary by translator"); //$NON-NLS-1$ param.setNullType(NullType.Nullable); param.setVarArg(true);