Author: shawkins Date: 2011-10-27 13:05:18 -0400 (Thu, 27 Oct 2011) New Revision: 3591 Modified: trunk/api/src/main/java/org/teiid/language/Call.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCProcedureExecution.java trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java trunk/documentation/reference/src/main/docbook/en-US/content/translators.xml Log: TEIID-832 added support for calling oracle stored procedures returning cursor/resultsets Modified: trunk/api/src/main/java/org/teiid/language/Call.java =================================================================== --- trunk/api/src/main/java/org/teiid/language/Call.java 2011-10-27 14:44:07 UTC (rev 3590) +++ trunk/api/src/main/java/org/teiid/language/Call.java 2011-10-27 17:05:18 UTC (rev 3591) @@ -93,6 +93,9 @@ return null; } + /** + * @return the result set types or a zero length array if no result set is returned + */ public Class<?>[] getResultSetColumnTypes() { ColumnSet<Procedure> resultSet = this.metadataObject.getResultSet(); if (resultSet == null) { Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCProcedureExecution.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCProcedureExecution.java 2011-10-27 14:44:07 UTC (rev 3590) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/JDBCProcedureExecution.java 2011-10-27 17:05:18 UTC (rev 3591) @@ -31,10 +31,10 @@ import org.teiid.language.Argument; import org.teiid.language.Call; import org.teiid.language.Command; -import org.teiid.translator.TranslatorException; import org.teiid.translator.DataNotAvailableException; import org.teiid.translator.ExecutionContext; import org.teiid.translator.ProcedureExecution; +import org.teiid.translator.TranslatorException; /** */ @@ -87,7 +87,10 @@ List<Object> result = new ArrayList<Object>(); int paramIndex = 1; if (proc.getReturnType() != null) { - addParameterValue(result, paramIndex++, proc.getReturnType()); + if (proc.getReturnParameter() != null) { + addParameterValue(result, paramIndex, proc.getReturnType()); + } + paramIndex++; } for (Argument parameter : proc.getArguments()) { switch (parameter.getDirection()) { Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java 2011-10-27 14:44:07 UTC (rev 3590) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java 2011-10-27 17:05:18 UTC (rev 3591) @@ -26,7 +26,9 @@ import static org.teiid.translator.TypeFacility.RUNTIME_NAMES.*; +import java.sql.CallableStatement; import java.sql.PreparedStatement; +import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.sql.Types; @@ -35,6 +37,7 @@ import java.util.Collection; import java.util.List; +import org.teiid.language.Call; import org.teiid.language.ColumnReference; import org.teiid.language.Command; import org.teiid.language.DerivedColumn; @@ -42,6 +45,7 @@ import org.teiid.language.ExpressionValueSource; import org.teiid.language.Function; import org.teiid.language.Insert; +import org.teiid.language.LanguageObject; import org.teiid.language.Limit; import org.teiid.language.Literal; import org.teiid.language.NamedTable; @@ -55,6 +59,7 @@ import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.Translator; import org.teiid.translator.TranslatorException; +import org.teiid.translator.TranslatorProperty; import org.teiid.translator.TypeFacility; import org.teiid.translator.jdbc.AliasModifier; import org.teiid.translator.jdbc.ConvertModifier; @@ -62,6 +67,7 @@ import org.teiid.translator.jdbc.FunctionModifier; import org.teiid.translator.jdbc.JDBCExecutionFactory; import org.teiid.translator.jdbc.LocateFunctionModifier; +import org.teiid.translator.jdbc.TranslatedCommand; @Translator(name="oracle", description="A translator for Oracle 9i Database or later") @@ -85,6 +91,14 @@ public static final String WITHIN_DISTANCE = "sdo_within_distance"; //$NON-NLS-1$ public static final String NEAREST_NEIGHBOR_DISTANCE = "sdo_nn_distance"; //$NON-NLS-1$ public static final String ORACLE_SDO = "Oracle-SDO"; //$NON-NLS-1$ + + /* + * Handling for cursor return values + */ + static final class RefCursorType {} + static int CURSOR_TYPE = -10; + + private boolean oracleSuppliedDriver = true; public void start() throws TranslatorException { super.start(); @@ -548,4 +562,46 @@ return "REGEXP_LIKE"; //$NON-NLS-1$ } + public void setOracleSuppliedDriver(boolean oracleNative) { + this.oracleSuppliedDriver = oracleNative; + } + + @TranslatorProperty(display="Oracle Native Driver", description="True if the driver is an Oracle supplied driver",advanced=true) + public boolean isOracleSuppliedDriver() { + return oracleSuppliedDriver; + } + + @Override + public List<?> translate(LanguageObject obj, ExecutionContext context) { + if (oracleSuppliedDriver && obj instanceof Call) { + Call call = (Call)obj; + //oracle returns the resultset as a parameter + if (call.getReturnType() == null) { + call.setReturnType(RefCursorType.class); + } + } + return super.translate(obj, context); + } + + @Override + protected void registerSpecificTypeOfOutParameter( + CallableStatement statement, Class<?> runtimeType, int index) + throws SQLException { + if (oracleSuppliedDriver && index == 1 && runtimeType == RefCursorType.class) { + statement.registerOutParameter(1, CURSOR_TYPE); + } else { + super.registerSpecificTypeOfOutParameter(statement, runtimeType, index); + } + } + + @Override + public ResultSet executeStoredProcedure(CallableStatement statement, + TranslatedCommand command, Class<?> returnType) throws SQLException { + ResultSet rs = super.executeStoredProcedure(statement, command, returnType); + if (!oracleSuppliedDriver || returnType != RefCursorType.class) { + return rs; + } + return (ResultSet)statement.getObject(1); + } + } Modified: trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java =================================================================== --- trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2011-10-27 14:44:07 UTC (rev 3590) +++ trunk/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2011-10-27 17:05:18 UTC (rev 3591) @@ -24,10 +24,15 @@ import static org.junit.Assert.*; +import java.sql.CallableStatement; +import java.sql.Connection; +import java.sql.ResultSet; +import java.util.Arrays; import java.util.List; import org.junit.Before; import org.junit.Test; +import org.mockito.Mockito; import org.teiid.cdk.CommandBuilder; import org.teiid.cdk.api.TranslationUtility; import org.teiid.core.types.DataTypeManager; @@ -45,6 +50,7 @@ import org.teiid.query.unittest.RealMetadataFactory; import org.teiid.translator.ExecutionContext; import org.teiid.translator.TranslatorException; +import org.teiid.translator.jdbc.JDBCProcedureExecution; import org.teiid.translator.jdbc.TranslatedCommand; import org.teiid.translator.jdbc.TranslationHelper; @@ -789,5 +795,32 @@ input, output, TRANSLATOR); } + + @Test public void testCallWithResultSet() throws Exception { + String input = "call spTest5(1)"; //$NON-NLS-1$ + String output = "{ ?= call spTest5(?)}"; //$NON-NLS-1$ + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, + input, output, + TRANSLATOR); + } + + @Test public void testProcedureExecution() throws Exception { + Command command = TranslationHelper.helpTranslate(TranslationHelper.BQT_VDB, "call spTest8(1)"); //$NON-NLS-1$ + Connection connection = Mockito.mock(Connection.class); + CallableStatement cs = Mockito.mock(CallableStatement.class); + Mockito.stub(cs.getUpdateCount()).toReturn(-1); + ResultSet rs = Mockito.mock(ResultSet.class); + Mockito.stub(cs.getObject(1)).toReturn(rs); + Mockito.stub(cs.getInt(3)).toReturn(4); + Mockito.stub(connection.prepareCall("{ ?= call spTest8(?,?)}")).toReturn(cs); //$NON-NLS-1$ + OracleExecutionFactory ef = new OracleExecutionFactory(); + + JDBCProcedureExecution procedureExecution = new JDBCProcedureExecution(command, connection, Mockito.mock(ExecutionContext.class), ef); + procedureExecution.execute(); + assertEquals(Arrays.asList(4), procedureExecution.getOutputParameterValues()); + Mockito.verify(cs, Mockito.times(1)).registerOutParameter(1, OracleExecutionFactory.CURSOR_TYPE); + Mockito.verify(cs, Mockito.times(1)).getObject(1); + } + } Modified: trunk/documentation/reference/src/main/docbook/en-US/content/translators.xml =================================================================== --- trunk/documentation/reference/src/main/docbook/en-US/content/translators.xml 2011-10-27 14:44:07 UTC (rev 3590) +++ trunk/documentation/reference/src/main/docbook/en-US/content/translators.xml 2011-10-27 17:05:18 UTC (rev 3591) @@ -322,6 +322,12 @@ A rownum colum should have a name in source of <code>rownum</code>. These rownum columns do not have the same semantics as the Oracle rownum construct so care must be taken in their usage. </para> + <para>Oracle specific execution properties:</para> + <itemizedlist> + <listitem> + <para><emphasis>OracleSuppliedDriver</emphasis> - indicates that the Oracle supplied driver (typically prefixed by ojdbc) is being used. Defaults to true. Set to false when using DataDirect or other Oracle JDBC drivers.</para> + </listitem> + </itemizedlist> </listitem> <listitem> <para>