This is an automated email from the ASF dual-hosted git repository.
bamaer pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/hop.git
The following commit(s) were added to refs/heads/main by this push:
new a7a2964881 Issue #4858 : Output stored procedure result rows from Call
DB Procedure (#8693)
a7a2964881 is described below
commit a7a296488131ec7efad8ab7258fdfd9945411541
Author: Matt Casters <[email protected]>
AuthorDate: Fri Oct 2 06:46:56 2026 +0200
Issue #4858 : Output stored procedure result rows from Call DB Procedure
(#8693)
* Issue #4858 : Output stored procedure result rows from Call DB Procedure
* Issue #4858 : Reject output arguments in procedure row mode and confirm
Get fields
---
.../org/apache/hop/core/database/Database.java | 377 ++++++++---
.../org/apache/hop/core/database/DatabaseTest.java | 213 ++++++
.../ROOT/pages/pipeline/transforms/calldbproc.adoc | 70 +-
.../ROOT/pages/pipeline/transforms/tableinput.adoc | 27 +
.../hop/pipeline/transforms/dbproc/DBProc.java | 286 ++++++--
.../pipeline/transforms/dbproc/DBProcDialog.java | 720 ++++++++++++++-------
.../pipeline/transforms/dbproc/DBProcField.java | 68 ++
.../hop/pipeline/transforms/dbproc/DBProcMeta.java | 409 ++++++------
.../transforms/dbproc/DBProcResultFieldLookup.java | 65 ++
.../dbproc/messages/messages_en_US.properties | 32 +-
.../pipeline/transforms/dbproc/DBProcMetaTest.java | 365 ++++++++++-
.../dbproc/src/test/resources/db-proc-rows.xml | 67 ++
12 files changed, 2115 insertions(+), 584 deletions(-)
diff --git a/core/src/main/java/org/apache/hop/core/database/Database.java
b/core/src/main/java/org/apache/hop/core/database/Database.java
index b03e6982d3..1e65a17d61 100644
--- a/core/src/main/java/org/apache/hop/core/database/Database.java
+++ b/core/src/main/java/org/apache/hop/core/database/Database.java
@@ -148,6 +148,13 @@ public class Database implements IVariables,
ILoggingObject, AutoCloseable {
private PreparedStatement pstmtSeq;
private CallableStatement cstmt;
+ /**
+ * First result set of the current procedure call, when the caller asked to
keep it. {@code
+ * getMoreResults()} closes the current result set, so this is left open
until the caller reads
+ * it.
+ */
+ private ResultSet retainedProcedureResultSet;
+
private DatabaseMetaData dbmd;
private IRowMeta rowMeta;
@@ -4645,124 +4652,332 @@ public class Database implements IVariables,
ILoggingObject, AutoCloseable {
public RowMetaAndData callProcedure(
String[] arg, String[] argdir, int[] argtype, String resultname, int
resulttype)
throws HopDatabaseException {
- RowMetaAndData ret;
+ return callProcedure(arg, argdir, argtype, resultname, resulttype, false);
+ }
+
+ /**
+ * Execute the callable statement prepared by {@link #setProcLookup}.
+ *
+ * @param retainResultSet when {@code true}, the first result set is left
open and returned by
+ * {@link #takeProcedureResultSet()}. The caller closes it and then
calls {@link
+ * #discardProcedureResults()} so later results are still drained. When
{@code false}, every
+ * result set is closed here, which is the historical behavior.
+ */
+ public RowMetaAndData callProcedure(
+ String[] arg,
+ String[] argdir,
+ int[] argtype,
+ String resultname,
+ int resulttype,
+ boolean retainResultSet)
+ throws HopDatabaseException {
try {
+ closeRetainedProcedureResultSet();
boolean moreResults = cstmt.execute();
- ret = new RowMetaAndData();
- int pos = 1;
- if (!Utils.isEmpty(resultname)) {
- IValueMeta vMeta = ValueMetaFactory.createValueMeta(resultname,
resulttype);
+ RowMetaAndData ret = readProcedureOutputs(arg, argdir, argtype,
resultname, resulttype);
+ if (retainResultSet) {
+ retainFirstResultSet(moreResults);
+ } else {
+ discardResultSets(moreResults);
+ }
+ return ret;
+ } catch (Exception ex) {
+ throw new HopDatabaseException("Unable to call procedure", ex);
+ }
+ }
+
+ /**
+ * Result-set columns of {@code procedure} when the driver can describe them
before execution.
+ * Returns {@code null} when it cannot. Does not execute the procedure and
does not bind
+ * arguments. Callers that still need the columns must ask the user and then
call {@link
+ * #getProcedureResultFields}.
+ */
+ public IRowMeta describeProcedureResultFields(
+ String procedure, String[] arg, String[] argdir, int[] argtype) throws
HopDatabaseException {
+ String[] names = arg == null ? new String[0] : arg;
+ String[] directions = argdir == null ? new String[0] : argdir;
+ int[] types = argtype == null ? new int[0] : argtype;
+ try {
+ setProcLookup(procedure, names, directions, types, null,
IValueMeta.TYPE_NONE);
+ return procedureMetadataBeforeExecute();
+ } finally {
+ closeProcedureStatement();
+ }
+ }
+
+ /**
+ * Result-set layout of {@code procedure} without a scalar function return.
Uses statement
+ * metadata when the driver provides it, otherwise executes the call with
null input arguments and
+ * reads the first result set. The caller decides whether that execution is
rolled back. Call
+ * {@link #describeProcedureResultFields} first when the caller must ask
before this runs the
+ * procedure.
+ */
+ public IRowMeta getProcedureResultFields(
+ String procedure, String[] arg, String[] argdir, int[] argtype) throws
HopDatabaseException {
+ String[] names = arg == null ? new String[0] : arg;
+ String[] directions = argdir == null ? new String[0] : argdir;
+ int[] types = argtype == null ? new int[0] : argtype;
+ try {
+ setProcLookup(procedure, names, directions, types, null,
IValueMeta.TYPE_NONE);
+ IRowMeta beforeExecute = procedureMetadataBeforeExecute();
+ if (beforeExecute != null) {
+ return beforeExecute;
+ }
+ bindNullProcedureArguments(names, directions, types);
+ limitProcedureRows();
+ callProcedure(names, directions, types, null, IValueMeta.TYPE_NONE,
true);
+ ResultSet resultSet = takeProcedureResultSet();
+ try {
+ if (resultSet == null) {
+ return new RowMeta();
+ }
+ try {
+ return getRowInfo(resultSet.getMetaData(),
databaseMeta.isMySqlVariant(), false);
+ } catch (SQLException e) {
+ throw new HopDatabaseException("Unable to get procedure result
fields", e);
+ }
+ } finally {
+ if (resultSet != null) {
+ try {
+ resultSet.close();
+ } catch (SQLException e) {
+ throw new HopDatabaseException("Unable to close procedure result
set", e);
+ }
+ }
+ discardProcedureResults();
+ }
+ } finally {
+ closeProcedureStatement();
+ }
+ }
+
+ private IRowMeta procedureMetadataBeforeExecute() throws
HopDatabaseException {
+ if (cstmt == null) {
+ return null;
+ }
+ ResultSetMetaData metadata = null;
+ try {
+ metadata = cstmt.getMetaData();
+ } catch (SQLException e) {
+ return null;
+ }
+ if (metadata == null) {
+ return null;
+ }
+ try {
+ if (metadata.getColumnCount() <= 0) {
+ return null;
+ }
+ } catch (SQLException e) {
+ return null;
+ }
+ return getRowInfo(metadata, databaseMeta.isMySqlVariant(), false);
+ }
+
+ private void limitProcedureRows() {
+ if (cstmt == null) {
+ return;
+ }
+ try {
+ cstmt.setMaxRows(1);
+ } catch (SQLException e) {
+ // Not every driver accepts a row limit on a callable statement.
+ }
+ }
+
+ private void bindNullProcedureArguments(String[] arg, String[] argdir, int[]
argtype)
+ throws HopDatabaseException {
+ if (arg.length == 0) {
+ return;
+ }
+ boolean hasInput = false;
+ for (String direction : argdir) {
+ if (direction != null
+ && (direction.equalsIgnoreCase("IN") ||
direction.equalsIgnoreCase(CONST_INOUT))) {
+ hasInput = true;
+ break;
+ }
+ }
+ if (!hasInput) {
+ return;
+ }
+ IRowMeta paramMeta = new RowMeta();
+ Object[] data = new Object[arg.length];
+ int[] argnrs = new int[arg.length];
+ try {
+ for (int i = 0; i < arg.length; i++) {
+ argnrs[i] = i;
+ int type = argtype.length > i ? argtype[i] : IValueMeta.TYPE_NONE;
+ String name = arg[i] == null ? "arg" + i : arg[i];
+ paramMeta.addValueMeta(ValueMetaFactory.createValueMeta(name, type));
+ }
+ } catch (HopException e) {
+ throw new HopDatabaseException("Unable to prepare database procedure
call", e);
+ }
+ setProcValues(paramMeta, data, argnrs, argdir, false);
+ }
+
+ private RowMetaAndData readProcedureOutputs(
+ String[] arg, String[] argdir, int[] argtype, String resultname, int
resulttype)
+ throws HopException, SQLException {
+ RowMetaAndData ret = new RowMetaAndData();
+ int pos = 1;
+ if (!Utils.isEmpty(resultname)) {
+ IValueMeta vMeta = ValueMetaFactory.createValueMeta(resultname,
resulttype);
+ Object v = null;
+ switch (resulttype) {
+ case IValueMeta.TYPE_BOOLEAN:
+ v = cstmt.getBoolean(pos);
+ break;
+ case IValueMeta.TYPE_NUMBER:
+ v = cstmt.getDouble(pos);
+ break;
+ case IValueMeta.TYPE_BIGNUMBER:
+ v = cstmt.getBigDecimal(pos);
+ break;
+ case IValueMeta.TYPE_INTEGER:
+ v = cstmt.getLong(pos);
+ break;
+ case IValueMeta.TYPE_STRING:
+ v = cstmt.getString(pos);
+ break;
+ case IValueMeta.TYPE_BINARY:
+ v = cstmt.getBytes(pos);
+ break;
+ case IValueMeta.TYPE_DATE:
+ if (databaseMeta.supportsTimeStampToDateConversion()) {
+ v = cstmt.getTimestamp(pos);
+ } else {
+ v = cstmt.getDate(pos);
+ }
+ break;
+ default:
+ break;
+ }
+ ret.addValue(vMeta, v);
+ pos++;
+ }
+ for (int i = 0; i < arg.length; i++) {
+ if (argdir[i].equalsIgnoreCase("OUT") ||
argdir[i].equalsIgnoreCase(CONST_INOUT)) {
+ IValueMeta vMeta = ValueMetaFactory.createValueMeta(arg[i],
argtype[i]);
Object v = null;
- switch (resulttype) {
+ switch (argtype[i]) {
case IValueMeta.TYPE_BOOLEAN:
- v = cstmt.getBoolean(pos);
+ v = cstmt.getBoolean(pos + i);
break;
case IValueMeta.TYPE_NUMBER:
- v = cstmt.getDouble(pos);
+ v = cstmt.getDouble(pos + i);
break;
case IValueMeta.TYPE_BIGNUMBER:
- v = cstmt.getBigDecimal(pos);
+ v = cstmt.getBigDecimal(pos + i);
break;
case IValueMeta.TYPE_INTEGER:
- v = cstmt.getLong(pos);
+ v = cstmt.getLong(pos + i);
break;
case IValueMeta.TYPE_STRING:
- v = cstmt.getString(pos);
+ v = cstmt.getString(pos + i);
break;
case IValueMeta.TYPE_BINARY:
- v = cstmt.getBytes(pos);
+ v = cstmt.getBytes(pos + i);
break;
case IValueMeta.TYPE_DATE:
if (databaseMeta.supportsTimeStampToDateConversion()) {
- v = cstmt.getTimestamp(pos);
+ v = cstmt.getTimestamp(pos + i);
} else {
- v = cstmt.getDate(pos);
+ v = cstmt.getDate(pos + i);
}
break;
default:
break;
}
ret.addValue(vMeta, v);
- pos++;
}
- for (int i = 0; i < arg.length; i++) {
- if (argdir[i].equalsIgnoreCase("OUT") ||
argdir[i].equalsIgnoreCase(CONST_INOUT)) {
- IValueMeta vMeta = ValueMetaFactory.createValueMeta(arg[i],
argtype[i]);
- Object v = null;
- switch (argtype[i]) {
- case IValueMeta.TYPE_BOOLEAN:
- v = cstmt.getBoolean(pos + i);
- break;
- case IValueMeta.TYPE_NUMBER:
- v = cstmt.getDouble(pos + i);
- break;
- case IValueMeta.TYPE_BIGNUMBER:
- v = cstmt.getBigDecimal(pos + i);
- break;
- case IValueMeta.TYPE_INTEGER:
- v = cstmt.getLong(pos + i);
- break;
- case IValueMeta.TYPE_STRING:
- v = cstmt.getString(pos + i);
- break;
- case IValueMeta.TYPE_BINARY:
- v = cstmt.getBytes(pos + i);
- break;
- case IValueMeta.TYPE_DATE:
- if (databaseMeta.supportsTimeStampToDateConversion()) {
- v = cstmt.getTimestamp(pos + i);
- } else {
- v = cstmt.getDate(pos + i);
- }
- break;
- default:
- break;
- }
- ret.addValue(vMeta, v);
+ }
+ return ret;
+ }
+
+ /**
+ * Leave the first result set open. {@code getMoreResults()} would close it,
so this returns
+ * before moving on when a result set was found. Update counts before that
result set are
+ * consumed.
+ */
+ private void retainFirstResultSet(boolean moreResults) throws SQLException {
+ retainedProcedureResultSet = null;
+ int updateCount = -1;
+ do {
+ if (moreResults) {
+ ResultSet resultSet = cstmt.getResultSet();
+ if (resultSet != null) {
+ retainedProcedureResultSet = resultSet;
+ return;
}
+ } else {
+ updateCount = cstmt.getUpdateCount();
}
- ResultSet rs = null;
- int updateCount = -1;
-
- // CHE: Iterate through the result sets and update counts
- // to receive all error messages from within the stored procedure.
- // This is only the first transform to ensure that the stored procedure
- // is properly executed. A future extension would be to return all
- // result sets and update counts properly.
-
- do {
- rs = null;
- try {
- // Save the result set
- if (moreResults) {
- rs = cstmt.getResultSet();
-
- } else {
- // Save the update count if it is available (> -1)
- updateCount = cstmt.getUpdateCount();
- }
-
- moreResults = cstmt.getMoreResults();
+ moreResults = cstmt.getMoreResults();
+ } while (moreResults || (updateCount > -1));
+ }
- } finally {
- if (rs != null) {
- rs.close();
- rs = null;
- }
+ private void discardResultSets(boolean moreResults) throws SQLException {
+ int updateCount = -1;
+ // Iterate through the result sets and update counts to receive all error
messages from within
+ // the stored procedure.
+ do {
+ ResultSet resultSet = null;
+ try {
+ if (moreResults) {
+ resultSet = cstmt.getResultSet();
+ } else {
+ updateCount = cstmt.getUpdateCount();
}
+ moreResults = cstmt.getMoreResults();
+ } finally {
+ if (resultSet != null) {
+ resultSet.close();
+ }
+ }
+ } while (moreResults || (updateCount > -1));
+ }
- } while (moreResults || (updateCount > -1));
+ /** The result set retained by {@link #callProcedure}, or {@code null}. The
caller closes it. */
+ public ResultSet takeProcedureResultSet() {
+ ResultSet resultSet = retainedProcedureResultSet;
+ retainedProcedureResultSet = null;
+ return resultSet;
+ }
- return ret;
- } catch (Exception ex) {
+ /**
+ * Close a retained result set that was not taken, then drain every later
result of the current
+ * procedure call.
+ */
+ public void discardProcedureResults() throws HopDatabaseException {
+ if (cstmt == null) {
+ try {
+ closeRetainedProcedureResultSet();
+ } catch (SQLException ex) {
+ throw new HopDatabaseException("Unable to call procedure", ex);
+ }
+ return;
+ }
+ try {
+ closeRetainedProcedureResultSet();
+ boolean moreResults = cstmt.getMoreResults();
+ discardResultSets(moreResults);
+ } catch (SQLException ex) {
throw new HopDatabaseException("Unable to call procedure", ex);
}
}
+ private void closeRetainedProcedureResultSet() throws SQLException {
+ if (retainedProcedureResultSet != null) {
+ retainedProcedureResultSet.close();
+ retainedProcedureResultSet = null;
+ }
+ }
+
public void closeProcedureStatement() throws HopDatabaseException {
- // CHE: close the callable statement involved in the stored
- // procedure call!
try {
+ closeRetainedProcedureResultSet();
if (cstmt != null) {
cstmt.close();
cstmt = null;
diff --git a/core/src/test/java/org/apache/hop/core/database/DatabaseTest.java
b/core/src/test/java/org/apache/hop/core/database/DatabaseTest.java
index 774dd8a778..c9591239d2 100644
--- a/core/src/test/java/org/apache/hop/core/database/DatabaseTest.java
+++ b/core/src/test/java/org/apache/hop/core/database/DatabaseTest.java
@@ -22,6 +22,7 @@ import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertInstanceOf;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertNull;
+import static org.junit.jupiter.api.Assertions.assertSame;
import static org.junit.jupiter.api.Assertions.assertThrows;
import static org.junit.jupiter.api.Assertions.assertTrue;
import static org.junit.jupiter.api.Assertions.fail;
@@ -41,6 +42,7 @@ import static org.mockito.Mockito.when;
import java.lang.reflect.Field;
import java.sql.BatchUpdateException;
+import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ParameterMetaData;
@@ -898,4 +900,215 @@ class DatabaseTest {
assertEquals(0, result.getNrLinesUpdated());
assertEquals(0, result.getNrLinesOutput());
}
+
+ private Database procedureDatabase(CallableStatement statement) throws
SQLException {
+ when(meta.getIDatabase()).thenReturn(new NoneDatabaseMeta());
+ when(meta.isMySqlVariant()).thenReturn(false);
+ when(conn.prepareCall(anyString())).thenReturn(statement);
+ when(statement.getUpdateCount()).thenReturn(-1);
+ when(statement.getMoreResults()).thenReturn(false);
+ Database db = new Database(log, variables, meta);
+ db.setConnection(conn);
+ return db;
+ }
+
+ private ResultSetMetaData columnMetadata(String name, int sqlType) throws
SQLException {
+ ResultSetMetaData metadata = mock(ResultSetMetaData.class);
+ when(metadata.getColumnCount()).thenReturn(1);
+ when(metadata.getColumnName(1)).thenReturn(name);
+ when(metadata.getColumnLabel(1)).thenReturn(name);
+ when(metadata.getColumnType(1)).thenReturn(sqlType);
+ when(metadata.getColumnTypeName(1)).thenReturn("type");
+ return metadata;
+ }
+
+ @Test
+ void setProcLookupKeepsTheHistoricalCallSyntax() throws Exception {
+ CallableStatement statement = mock(CallableStatement.class);
+ Database db = procedureDatabase(statement);
+
+ db.setProcLookup(
+ "list_customers", new String[0], new String[0], new int[0], null,
IValueMeta.TYPE_NONE);
+ verify(conn).prepareCall("{ call list_customers }");
+
+ db.setProcLookup(
+ "add_one",
+ new String[] {"n"},
+ new String[] {"IN"},
+ new int[] {IValueMeta.TYPE_INTEGER},
+ "result",
+ IValueMeta.TYPE_INTEGER);
+ verify(conn).prepareCall("{ ? = call add_one ( ?)}");
+ verify(statement).registerOutParameter(1, Types.BIGINT);
+ }
+
+ @Test
+ void callProcedureClosesResultSetsWhenNotRetained() throws Exception {
+ CallableStatement statement = mock(CallableStatement.class);
+ ResultSet resultSet = mock(ResultSet.class);
+ when(statement.execute()).thenReturn(true);
+ when(statement.getResultSet()).thenReturn(resultSet);
+ Database db = procedureDatabase(statement);
+
+ db.setProcLookup(
+ "list_customers", new String[0], new String[0], new int[0], null,
IValueMeta.TYPE_NONE);
+ db.callProcedure(new String[0], new String[0], new int[0], null,
IValueMeta.TYPE_NONE);
+
+ verify(resultSet).close();
+ verify(statement).getMoreResults();
+ }
+
+ @Test
+ void callProcedureKeepsTheFirstResultSetUntilDiscarded() throws Exception {
+ CallableStatement statement = mock(CallableStatement.class);
+ ResultSet resultSet = mock(ResultSet.class);
+ when(statement.execute()).thenReturn(true);
+ when(statement.getResultSet()).thenReturn(resultSet);
+ Database db = procedureDatabase(statement);
+
+ db.setProcLookup(
+ "list_customers", new String[0], new String[0], new int[0], null,
IValueMeta.TYPE_NONE);
+ db.callProcedure(new String[0], new String[0], new int[0], null,
IValueMeta.TYPE_NONE, true);
+
+ assertSame(resultSet, db.takeProcedureResultSet());
+ verify(resultSet, never()).close();
+ verify(statement, never()).getMoreResults();
+
+ db.discardProcedureResults();
+ verify(statement, times(2)).getMoreResults();
+ verify(resultSet, never()).close();
+ }
+
+ @Test
+ void callProcedureRetainsResultSetAfterAnUpdateCount() throws Exception {
+ CallableStatement statement = mock(CallableStatement.class);
+ ResultSet resultSet = mock(ResultSet.class);
+ when(statement.execute()).thenReturn(false);
+ when(statement.getResultSet()).thenReturn(resultSet);
+ Database db = procedureDatabase(statement);
+ when(statement.getUpdateCount()).thenReturn(1, -1);
+ when(statement.getMoreResults()).thenReturn(true, false);
+
+ db.setProcLookup(
+ "list_customers", new String[0], new String[0], new int[0], null,
IValueMeta.TYPE_NONE);
+ db.callProcedure(new String[0], new String[0], new int[0], null,
IValueMeta.TYPE_NONE, true);
+
+ assertSame(resultSet, db.takeProcedureResultSet());
+ verify(resultSet, never()).close();
+ verify(statement, times(1)).getMoreResults();
+ verify(statement, times(1)).getUpdateCount();
+ }
+
+ @Test
+ void describeProcedureResultFieldsUsesStatementMetadataWithoutExecuting()
throws Exception {
+ CallableStatement statement = mock(CallableStatement.class);
+ ResultSetMetaData metadata = columnMetadata("customer", Types.VARCHAR);
+ when(statement.getMetaData()).thenReturn(metadata);
+ Database db = procedureDatabase(statement);
+
+ IRowMeta fields =
+ db.describeProcedureResultFields(
+ "list_customers", new String[0], new String[0], new int[0]);
+
+ assertEquals(1, fields.size());
+ assertEquals("customer", fields.getValueMeta(0).getName());
+ assertTrue(fields.getValueMeta(0).isString());
+ verify(statement, never()).execute();
+ verify(statement).close();
+ }
+
+ @Test
+ void describeProcedureResultFieldsDoesNotExecuteWhenMetadataIsMissing()
throws Exception {
+ CallableStatement statement = mock(CallableStatement.class);
+ when(statement.getMetaData()).thenReturn(null);
+ Database db = procedureDatabase(statement);
+
+ assertNull(
+ db.describeProcedureResultFields(
+ "list_customers",
+ new String[] {"id"},
+ new String[] {"IN"},
+ new int[] {IValueMeta.TYPE_INTEGER}));
+
+ verify(statement, never()).execute();
+ verify(statement, never()).setMaxRows(1);
+ verify(statement).close();
+ }
+
+ @Test
+ void describeProcedureResultFieldsDoesNotExecuteWhenTheDriverHasNoColumns()
throws Exception {
+ CallableStatement statement = mock(CallableStatement.class);
+ ResultSetMetaData metadata = mock(ResultSetMetaData.class);
+ when(statement.getMetaData()).thenReturn(metadata);
+ when(metadata.getColumnCount()).thenReturn(0);
+ Database db = procedureDatabase(statement);
+
+ assertNull(
+ db.describeProcedureResultFields(
+ "list_customers", new String[0], new String[0], new int[0]));
+
+ verify(statement, never()).execute();
+ verify(statement).close();
+ }
+
+ @Test
+ void getProcedureResultFieldsUsesStatementMetadataWithoutExecuting() throws
Exception {
+ CallableStatement statement = mock(CallableStatement.class);
+ ResultSetMetaData metadata = columnMetadata("customer", Types.VARCHAR);
+ when(statement.getMetaData()).thenReturn(metadata);
+ Database db = procedureDatabase(statement);
+
+ IRowMeta fields =
+ db.getProcedureResultFields(
+ "list_customers",
+ new String[] {"id"},
+ new String[] {"OUT"},
+ new int[] {IValueMeta.TYPE_INTEGER});
+
+ assertEquals(1, fields.size());
+ assertEquals("customer", fields.getValueMeta(0).getName());
+ assertTrue(fields.getValueMeta(0).isString());
+ verify(conn).prepareCall("{ call list_customers ( ?)}");
+ verify(statement, never()).execute();
+ verify(statement).close();
+ }
+
+ @Test
+ void getProcedureResultFieldsReadsTheFirstResultSetWhenMetadataIsMissing()
throws Exception {
+ CallableStatement statement = mock(CallableStatement.class);
+ ResultSet resultSet = mock(ResultSet.class);
+ when(statement.getMetaData()).thenReturn(null);
+ when(statement.execute()).thenReturn(true);
+ when(statement.getResultSet()).thenReturn(resultSet);
+ ResultSetMetaData metadata = columnMetadata("id", Types.INTEGER);
+ when(resultSet.getMetaData()).thenReturn(metadata);
+ Database db = procedureDatabase(statement);
+
+ IRowMeta fields =
+ db.getProcedureResultFields("list_customers", new String[0], new
String[0], new int[0]);
+
+ assertEquals(1, fields.size());
+ assertEquals("id", fields.getValueMeta(0).getName());
+ assertTrue(fields.getValueMeta(0).isInteger());
+ verify(statement).execute();
+ verify(statement).setMaxRows(1);
+ verify(resultSet).close();
+ verify(statement).close();
+ }
+
+ @Test
+ void getProcedureResultFieldsReturnsNoColumnsWhenTheCallHasNoResultSet()
throws Exception {
+ CallableStatement statement = mock(CallableStatement.class);
+ when(statement.getMetaData()).thenReturn(null);
+ when(statement.execute()).thenReturn(false);
+ Database db = procedureDatabase(statement);
+
+ IRowMeta fields =
+ db.getProcedureResultFields("list_customers", new String[0], new
String[0], new int[0]);
+
+ assertNotNull(fields);
+ assertEquals(0, fields.size());
+ verify(statement).execute();
+ verify(statement).close();
+ }
}
diff --git
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/calldbproc.adoc
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/calldbproc.adoc
index 70f57edd38..73da6e2ae3 100644
---
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/calldbproc.adoc
+++
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/calldbproc.adoc
@@ -16,36 +16,66 @@ under the License.
////
:documentationPath: /pipeline/transforms/
:language: en_US
-:description: The Call DB Procedure transform allows you to execute a database
procedure (or function) and get the result(s) back.
+:description: The Call DB Procedure transform calls a database procedure or
function and returns a scalar value, output parameters, or result rows.
:page-engines: Hop Engine=yes, Single Threaded=yes, Native Spark=yes, Beam
Spark=yes, Beam Flink=yes, Beam Dataflow=yes
= image:transforms/icons/dbproc.svg[Call DB procedure transform Icon,
role="image-doc-icon"] Call DB procedure
== Description
-The Call DB Procedure transform allows you to execute a database procedure (or
function) and get the result(s) back.
-With MySQL and JDBC, it is not possible to retrieve the result set of a stored
procedure.
-Stored procedures and functions can only return values through their function
arguments and those arguments must be defined in the Parameters section of the
DB Procedure Call configuration.
+Call DB procedure runs a stored procedure or function once for every incoming
row. With no incoming hop it runs once.
+
+The call can return a scalar function value and `OUT` or `INOUT` parameters,
or the rows of the procedure's first result set. Row mode does not accept `OUT`
or `INOUT` arguments. Some databases only return those values after the result
set has been consumed, and reading them first discards the rows. Hop reports
that combination from the transform check and stops at runtime instead of
returning no rows. Later result sets are read and discarded so procedure errors
still surface. An empty f [...]
+
+A procedure that only returns a result set can also be called from
xref:pipeline/transforms/tableinput.adoc[Table Input]. MySQL and MariaDB accept
a `CALL` statement. Many other JDBC drivers accept the call escape:
+
+[source,sql]
+----
+{call list_customers()}
+----
+
+PostgreSQL functions are usually read with `SELECT * FROM
procedure_name(...)`. Use Table Input when the call is ordinary SQL and you
only need the result set. Use Call DB procedure when the procedure needs `IN`,
`OUT`, or `INOUT` arguments from the stream, or when you want one call per
incoming row. Output parameters and result rows are separate choices; do not
combine them.
== Options
+The dialog is split into *General*, *Parameters*, and *Fields* tabs.
+
+=== General
+
+[options="header"]
+|===
+|Option|Description
+|Transform name|Name of the transform. This name has to be unique in a single
pipeline.
+|Connection|Database connection on which the procedure resides.
+|Procedure name|Name of the procedure or function to call. Variables are
supported.
+|Find procedure|Lists the procedures JDBC reports for the selected connection.
Some drivers return none; type the name when the list is empty.
+|Enable auto commit|When auto commit is off, Hop commits once after the last
row is processed.
+|Result name|Name of the function return value. Leave this blank when the
procedure has no scalar return. Ignored when the result type is Row; the stored
name is kept if you switch back.
+|Result type|Hop type of the function return value, or *Row* to output the
rows of the first result set. *Row* cannot be combined with `OUT` or `INOUT`
arguments.
+|===
+
+=== Parameters
+
+[options="header"]
+|===
+|Option|Description
+|Name|Argument name. For `IN` and `INOUT` this is also the incoming stream
field.
+|Direction|`IN` (input only), `OUT` (output only), or `INOUT` (the incoming
value is sent and replaced with the returned value). `OUT` and `INOUT` are not
allowed when the result type is *Row*.
+|Type|Hop type of the argument. Output parameters need this so Hop knows what
the driver returns.
+|Get Fields|Button on the dialog. Fills the grid from the incoming stream and
sets each direction to `IN`. Remove the rows you do not need.
+|===
+
+=== Fields
+
+This tab is available when the result type is *Row*. The listed columns are
appended to the incoming row. Columns that are missing from the result set are
null. Only the columns you list are added; an empty list still emits one output
row per result row, with the incoming fields.
+
[options="header"]
|===
|Option|Description
-|Transform name|Name of the transform; this name has to be unique in a single
pipeline
-|Connection|Name of the database connection on which the procedure resides
-|Proc-name|Name of the procedure or function to call
-|Find it|Click to search on the specified database connection for available
procedures and functions (Oracle and SQL Server only)
-|Enable auto|In some instances you want to perform updates
-|commit|in the database using the specified procedure.
-In that case you can either have the changes performed using auto-commit or
not.
-If auto-commit is disabled, a single commit is being performed after the last
row is received by this transform.
-|Result name|Name of the result of the function call; leave blank if this is a
procedure
-|Result type|Type of the result of the function call; not used in case of a
procedure.
-|Parameters a|List of parameters that the procedure or function needs
-
-* Field name: Name of the field.
-* Direction: Can be either IN (input only), OUT (output only), INOUT (value is
changed on the database).
-* Type: Used for output parameters so that Hop knows what returns
-|Get Fields|Fills in all the fields in the input streams to make your life
easier; delete the lines you don't need and re-order the remaining lines
+|Get fields|Reads the column names, types, format, length, and precision of
the first result set, in the same way
xref:pipeline/transforms/tableinput.adoc[Table Input] reads query fields. Hop
asks the driver for callable-statement metadata first and does not run the
procedure when that metadata is available. When the driver has none, Hop asks
for confirmation and then executes the procedure with null values for `IN` and
`INOUT` arguments, limits the read to one row when the driver allows [...]
+|Name|Output field name. Matched to the result-set column by label, then by
name, ignoring case. Variables are resolved.
+|Type|Hop type of the output field.
+|Format|Optional conversion mask.
+|Length|Field length. Empty means unspecified.
+|Precision|Field precision. Empty means unspecified.
|===
\ No newline at end of file
diff --git
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/tableinput.adoc
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/tableinput.adoc
index 526b444b67..43115c673c 100644
---
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/tableinput.adoc
+++
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/tableinput.adoc
@@ -39,6 +39,33 @@ The dialog is split into *SQL*, *Options*, and *Fields* tabs.
These features make Table Input ideal for dynamic and reusable data retrieval
logic.
+=== Calling a stored procedure
+
+A procedure that returns rows can be read with ordinary SQL in this transform.
+
+MySQL and MariaDB accept `CALL`:
+
+[source,sql]
+----
+CALL list_customers();
+----
+
+PostgreSQL functions are usually selected like a table:
+
+[source,sql]
+----
+SELECT * FROM list_customers();
+----
+
+Many other JDBC drivers accept the call escape:
+
+[source,sql]
+----
+{call list_customers()}
+----
+
+xref:pipeline/transforms/calldbproc.adoc[Call DB procedure] runs the procedure
once per incoming row and can also return a scalar value and `OUT` or `INOUT`
parameters. Use it when those arguments come from the stream. Use Table Input
when the call is SQL and you only need the result set.
+
== Getting Started: Generate a Basic SQL Query
You can auto-generate a query using the `Get SQL select statement` button.
diff --git
a/plugins/transforms/dbproc/src/main/java/org/apache/hop/pipeline/transforms/dbproc/DBProc.java
b/plugins/transforms/dbproc/src/main/java/org/apache/hop/pipeline/transforms/dbproc/DBProc.java
index 066758a39e..99a821b83b 100644
---
a/plugins/transforms/dbproc/src/main/java/org/apache/hop/pipeline/transforms/dbproc/DBProc.java
+++
b/plugins/transforms/dbproc/src/main/java/org/apache/hop/pipeline/transforms/dbproc/DBProc.java
@@ -17,8 +17,13 @@
package org.apache.hop.pipeline.transforms.dbproc;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
import java.sql.SQLException;
+import java.util.HashMap;
import java.util.List;
+import java.util.Locale;
+import java.util.Map;
import org.apache.commons.lang3.StringUtils;
import org.apache.hop.core.Const;
import org.apache.hop.core.RowMetaAndData;
@@ -28,9 +33,11 @@ import org.apache.hop.core.exception.HopDatabaseException;
import org.apache.hop.core.exception.HopException;
import org.apache.hop.core.exception.HopTransformException;
import org.apache.hop.core.row.IRowMeta;
+import org.apache.hop.core.row.IValueMeta;
import org.apache.hop.core.row.RowDataUtil;
import org.apache.hop.core.row.RowMeta;
import org.apache.hop.core.util.Utils;
+import org.apache.hop.core.variables.IVariables;
import org.apache.hop.i18n.BaseMessages;
import org.apache.hop.pipeline.Pipeline;
import org.apache.hop.pipeline.PipelineMeta;
@@ -51,83 +58,240 @@ public class DBProc extends BaseTransform<DBProcMeta,
DBProcData> {
super(transformMeta, meta, data, copyNr, pipelineMeta, pipeline);
}
- private Object[] runProc(IRowMeta rowMeta, Object[] rowData) throws
HopException {
- if (first) {
- first = false;
-
- // get the RowMeta for the output
- //
- data.outputMeta = data.inputRowMeta.clone();
- meta.getFields(data.outputMeta, getTransformName(), null, null, this,
metadataProvider);
-
- data.argnrs = new int[meta.getArguments().size()];
- for (int i = 0; i < meta.getArguments().size(); i++) {
- DBProcMeta.ProcArgument argument = meta.getArguments().get(i);
- if (!argument.getDirection().equalsIgnoreCase("OUT")) { // IN or INOUT
- data.argnrs[i] = rowMeta.indexOfValue(argument.getName());
- if (data.argnrs[i] < 0) {
- logError(
- BaseMessages.getString(PKG, "DBProc.Log.ErrorFindingField")
- + argument.getName()
- + "]");
- throw new HopTransformException(
- BaseMessages.getString(
- PKG, "DBProc.Exception.CouldnotFindField",
argument.getName()));
- }
- } else {
- data.argnrs[i] = -1;
+ private boolean scalarResult() {
+ return !meta.isResultRows() &&
StringUtils.isNotEmpty(meta.getResultName());
+ }
+
+ private void prepareProcedure(IRowMeta rowMeta) throws HopException {
+ if (!first) {
+ return;
+ }
+ first = false;
+
+ data.outputMeta = data.inputRowMeta.clone();
+ meta.getFields(data.outputMeta, getTransformName(), null, null, this,
metadataProvider);
+
+ List<DBProcMeta.ProcArgument> arguments =
+ meta.getArguments() == null ? List.of() : meta.getArguments();
+ data.argnrs = new int[arguments.size()];
+ for (int i = 0; i < arguments.size(); i++) {
+ DBProcMeta.ProcArgument argument = arguments.get(i);
+ if (!argument.getDirection().equalsIgnoreCase("OUT")) { // IN or INOUT
+ data.argnrs[i] = rowMeta.indexOfValue(argument.getName());
+ if (data.argnrs[i] < 0) {
+ logError(
+ BaseMessages.getString(PKG, "DBProc.Log.ErrorFindingField")
+ + argument.getName()
+ + "]");
+ throw new HopTransformException(
+ BaseMessages.getString(
+ PKG, "DBProc.Exception.CouldnotFindField",
argument.getName()));
}
+ } else {
+ data.argnrs[i] = -1;
}
+ }
+
+ // A Row result is a result set, not a JDBC function return value.
+ String resultName = scalarResult() ? meta.getResult().getName() : null;
+ int resultType = scalarResult() ? meta.getResult().getHopType() :
IValueMeta.TYPE_NONE;
+ data.db.setProcLookup(
+ resolve(meta.getProcedure()),
+ meta.argumentNames(),
+ meta.argumentDirections(),
+ meta.argumentTypes(),
+ resultName,
+ resultType);
+ }
- data.db.setProcLookup(
- resolve(meta.getProcedure()),
- meta.argumentNames(),
- meta.argumentDirections(),
- meta.argumentTypes(),
- meta.getResult().getName(),
- meta.getResult().getHopType());
+ /**
+ * Stops a row-mode call that also has OUT or INOUT arguments. Those values
are read before the
+ * result set, which makes some databases discard the rows.
+ */
+ static void rejectRowResultOutputArguments(DBProcMeta meta) throws
HopTransformException {
+ if (meta != null && meta.hasRowResultOutputArgument()) {
+ throw new HopTransformException(
+ BaseMessages.getString(PKG,
"DBProc.Exception.RowResultOutputArguments"));
}
+ }
- Object[] outputRowData = RowDataUtil.resizeArray(rowData,
data.outputMeta.size());
- int outputIndex = rowMeta.size();
+ private void runProc(IRowMeta rowMeta, Object[] rowData) throws HopException
{
+ rejectRowResultOutputArguments(meta);
+ prepareProcedure(rowMeta);
+ boolean scalar = scalarResult();
+ data.db.setProcValues(rowMeta, rowData, data.argnrs,
meta.argumentDirections(), scalar);
- data.db.setProcValues(
- rowMeta,
- rowData,
- data.argnrs,
- meta.argumentDirections(),
- StringUtils.isNotEmpty(meta.getResult().getName()));
+ if (meta.isResultRows()) {
+ boolean executed = false;
+ ResultSet resultSet = null;
+ try {
+ RowMetaAndData add =
+ data.db.callProcedure(
+ meta.argumentNames(),
+ meta.argumentDirections(),
+ meta.argumentTypes(),
+ null,
+ IValueMeta.TYPE_NONE,
+ true);
+ executed = true;
+ resultSet = data.db.takeProcedureResultSet();
+ writeResultRows(rowMeta, rowData, add, resultSet);
+ } finally {
+ try {
+ if (resultSet != null) {
+ resultSet.close();
+ }
+ } catch (SQLException e) {
+ throw new HopDatabaseException(
+ BaseMessages.getString(PKG,
"DBProc.Exception.UnableToReadResultSet"), e);
+ } finally {
+ if (executed) {
+ data.db.discardProcedureResults();
+ }
+ }
+ }
+ return;
+ }
+ String resultName = scalar ? meta.getResult().getName() : null;
+ int resultType = scalar ? meta.getResult().getHopType() :
IValueMeta.TYPE_NONE;
RowMetaAndData add =
data.db.callProcedure(
meta.argumentNames(),
meta.argumentDirections(),
meta.argumentTypes(),
- meta.getResult().getName(),
- meta.getResult().getHopType());
+ resultName,
+ resultType);
+ Object[] outputRowData =
+ buildOutputRow(
+ rowData,
+ rowMeta.size(),
+ data.outputMeta.size(),
+ add.getData(),
+ data.argnrs,
+ meta.getArguments(),
+ scalar,
+ 0,
+ false);
+ putRow(data.outputMeta, outputRowData);
+ }
+
+ private void writeResultRows(
+ IRowMeta rowMeta, Object[] rowData, RowMetaAndData procedureData,
ResultSet resultSet)
+ throws HopException {
+ if (resultSet == null) {
+ return;
+ }
+ List<DBProcField> fields = meta.activeResultFields();
+ int inputSize = rowMeta == null ? 0 : rowMeta.size();
+ Object[] template =
+ buildOutputRow(
+ rowData,
+ inputSize,
+ data.outputMeta.size(),
+ procedureData == null ? null : procedureData.getData(),
+ data.argnrs,
+ meta.getArguments(),
+ false,
+ fields.size(),
+ true);
+ try {
+ int[] indexes =
resultColumnIndexes(resultColumnNames(resultSet.getMetaData()), fields, this);
+ while (resultSet.next()) {
+ Object[] output = RowDataUtil.createResizedCopy(template,
data.outputMeta.size());
+ for (int i = 0; i < indexes.length; i++) {
+ if (indexes[i] < 0) {
+ continue;
+ }
+ IValueMeta valueMeta = data.outputMeta.getValueMeta(inputSize + i);
+ output[inputSize + i] =
+ data.db.getDatabaseMeta().getValueFromResultSet(resultSet,
valueMeta, indexes[i]);
+ }
+ putRow(data.outputMeta, output);
+ }
+ } catch (SQLException e) {
+ throw new HopDatabaseException(
+ BaseMessages.getString(PKG,
"DBProc.Exception.UnableToReadResultSet"), e);
+ }
+ }
+
+ /**
+ * @param copy when {@code true}, always allocate a new row. {@link
RowDataUtil#resizeArray}
+ * returns the same array when it is already large enough, which aliases
every result row.
+ */
+ static Object[] buildOutputRow(
+ Object[] rowData,
+ int inputSize,
+ int outputSize,
+ Object[] procedureData,
+ int[] argnrs,
+ List<DBProcMeta.ProcArgument> arguments,
+ boolean scalarResult,
+ int resultFieldCount,
+ boolean copy) {
+ Object[] source = rowData == null ? new Object[0] : rowData;
+ Object[] output =
+ copy
+ ? RowDataUtil.createResizedCopy(source, outputSize)
+ : RowDataUtil.resizeArray(source, outputSize);
+ int outputIndex = inputSize + resultFieldCount;
int addIndex = 0;
+ if (scalarResult) {
+ output[outputIndex++] = procedureData[addIndex++];
+ }
+ if (arguments == null) {
+ return output;
+ }
+ for (int i = 0; i < arguments.size(); i++) {
+ DBProcMeta.ProcArgument argument = arguments.get(i);
+ if (argument.getDirection().equalsIgnoreCase("OUT")) {
+ output[outputIndex++] = procedureData[addIndex++];
+ } else if (argument.getDirection().equalsIgnoreCase("INOUT")) {
+ output[argnrs[i]] = procedureData[addIndex++];
+ }
+ }
+ return output;
+ }
- // Function return?
- if (StringUtils.isNotEmpty(meta.getResult().getName())) {
- outputRowData[outputIndex++] = add.getData()[addIndex++]; // first is
the function return
+ static String[] resultColumnNames(ResultSetMetaData metadata) throws
SQLException {
+ if (metadata == null) {
+ return new String[0];
}
+ int count = metadata.getColumnCount();
+ String[] names = new String[count];
+ for (int i = 0; i < count; i++) {
+ String label = metadata.getColumnLabel(i + 1);
+ if (Utils.isEmpty(label)) {
+ label = metadata.getColumnName(i + 1);
+ }
+ names[i] = label;
+ }
+ return names;
+ }
- // We are only expecting the OUT and INOUT arguments here.
- // The INOUT values need to replace the value with the same name in the
row.
- //
- for (int i = 0; i < data.argnrs.length; i++) {
- DBProcMeta.ProcArgument argument = meta.getArguments().get(i);
- if ("OUT".equalsIgnoreCase(argument.getDirection())) {
- // add
- outputRowData[outputIndex++] = add.getData()[addIndex++];
- } else if ("INOUT".equalsIgnoreCase(argument.getDirection())) {
- // replace
- outputRowData[data.argnrs[i]] = add.getData()[addIndex];
- addIndex++;
+ static int[] resultColumnIndexes(
+ String[] columnNames, List<DBProcField> fields, IVariables variables) {
+ if (fields == null || fields.isEmpty()) {
+ return new int[0];
+ }
+ Map<String, Integer> byName = new HashMap<>();
+ if (columnNames != null) {
+ for (int i = 0; i < columnNames.length; i++) {
+ if (columnNames[i] != null) {
+ byName.putIfAbsent(columnNames[i].toLowerCase(Locale.ROOT), i);
+ }
+ }
+ }
+ int[] indexes = new int[fields.size()];
+ for (int i = 0; i < fields.size(); i++) {
+ String name = fields.get(i).getName();
+ if (variables != null && name != null) {
+ name = variables.resolve(name);
}
- // IN not taken
+ Integer index = name == null ? null :
byName.get(name.toLowerCase(Locale.ROOT));
+ indexes[i] = index == null ? -1 : index;
}
- return outputRowData;
+ return indexes;
}
@Override
@@ -157,9 +321,7 @@ public class DBProc extends BaseTransform<DBProcMeta,
DBProcData> {
}
try {
- Object[] outputRowData =
- runProc(data.inputRowMeta, r); // add new values to the row in
rowset[0].
- putRow(data.outputMeta, outputRowData); // copy row to output rowset(s)
+ runProc(data.inputRowMeta, r); // add new values to the row in rowset[0].
if (checkFeedback(getLinesRead()) && isBasic()) {
logBasic(BaseMessages.getString(PKG, "DBProc.LineNumber") +
getLinesRead());
diff --git
a/plugins/transforms/dbproc/src/main/java/org/apache/hop/pipeline/transforms/dbproc/DBProcDialog.java
b/plugins/transforms/dbproc/src/main/java/org/apache/hop/pipeline/transforms/dbproc/DBProcDialog.java
index 2d0729e147..7eb94361d1 100644
---
a/plugins/transforms/dbproc/src/main/java/org/apache/hop/pipeline/transforms/dbproc/DBProcDialog.java
+++
b/plugins/transforms/dbproc/src/main/java/org/apache/hop/pipeline/transforms/dbproc/DBProcDialog.java
@@ -25,6 +25,7 @@ import org.apache.hop.core.database.DatabaseMeta;
import org.apache.hop.core.exception.HopDatabaseException;
import org.apache.hop.core.exception.HopException;
import org.apache.hop.core.row.IRowMeta;
+import org.apache.hop.core.row.IValueMeta;
import org.apache.hop.core.row.value.ValueMetaFactory;
import org.apache.hop.core.util.Utils;
import org.apache.hop.core.variables.IVariables;
@@ -37,6 +38,8 @@ import org.apache.hop.ui.core.dialog.BaseDialog;
import org.apache.hop.ui.core.dialog.EnterSelectionDialog;
import org.apache.hop.ui.core.dialog.ErrorDialog;
import org.apache.hop.ui.core.dialog.MessageBox;
+import org.apache.hop.ui.core.gui.GuiCompositeWidgets;
+import org.apache.hop.ui.core.gui.GuiCompositeWidgetsAdapter;
import org.apache.hop.ui.core.widget.ColumnInfo;
import org.apache.hop.ui.core.widget.MetaSelectionLine;
import org.apache.hop.ui.core.widget.TableView;
@@ -45,13 +48,14 @@ import org.apache.hop.ui.hopgui.BackgroundThreadFacade;
import org.apache.hop.ui.pipeline.transform.BaseTransformDialog;
import org.apache.hop.ui.pipeline.transform.ITableItemInsertListener;
import org.eclipse.swt.SWT;
-import org.eclipse.swt.custom.CCombo;
-import org.eclipse.swt.graphics.Point;
+import org.eclipse.swt.custom.CTabFolder;
+import org.eclipse.swt.custom.CTabItem;
import org.eclipse.swt.layout.FormAttachment;
import org.eclipse.swt.layout.FormData;
import org.eclipse.swt.widgets.Button;
-import org.eclipse.swt.widgets.Event;
-import org.eclipse.swt.widgets.Label;
+import org.eclipse.swt.widgets.Combo;
+import org.eclipse.swt.widgets.Composite;
+import org.eclipse.swt.widgets.Control;
import org.eclipse.swt.widgets.Shell;
import org.eclipse.swt.widgets.TableItem;
import org.eclipse.swt.widgets.Text;
@@ -59,24 +63,21 @@ import org.eclipse.swt.widgets.Text;
public class DBProcDialog extends BaseTransformDialog {
private static final Class<?> PKG = DBProcMeta.class;
- private MetaSelectionLine<DatabaseMeta> wConnection;
-
- private TextVar wProcName;
-
- private Button wAutoCommit;
-
- private Text wResult;
-
- private CCombo wResultType;
-
- private TableView wFields;
-
private final DBProcMeta input;
+ private GuiCompositeWidgets widgets;
- private ColumnInfo[] fieldColumns;
+ private TableView wArguments;
+ private ColumnInfo[] argumentColumns;
+ private TableView wResultFields;
+ private Button wGetResultFields;
private final List<String> inputFields = new ArrayList<>();
+ private CTabFolder tabFolder;
+ private CTabItem fieldsTab;
+ private CTabItem generalTab;
+ private boolean adjustingTab;
+
public DBProcDialog(
Shell parent, IVariables variables, DBProcMeta transformMeta,
PipelineMeta pipelineMeta) {
super(parent, variables, transformMeta, pipelineMeta);
@@ -87,107 +88,76 @@ public class DBProcDialog extends BaseTransformDialog {
public String open() {
createShell(BaseMessages.getString(PKG, "DBProcDialog.Shell.Title"));
+ changed = input.hasChanged();
+
buildButtonBar().ok(e -> ok()).get(e -> get()).cancel(e ->
cancel()).build();
- // Connection line
- wConnection = addConnectionLine(shell, wSpacer, input.getConnection(),
null);
-
- // ProcName line...
- // add button to get list of procedures on selected connection...
- Button wbProcName = new Button(shell, SWT.PUSH);
- wbProcName.setText(BaseMessages.getString(PKG,
"DBProcDialog.Finding.Button"));
- FormData fdbProcName = new FormData();
- fdbProcName.right = new FormAttachment(100, 0);
- fdbProcName.top = new FormAttachment(wConnection, margin);
- wbProcName.setLayoutData(fdbProcName);
- wbProcName.addListener(SWT.Selection, this::selectProcedure);
-
- Label wlProcName = new Label(shell, SWT.RIGHT);
- wlProcName.setText(BaseMessages.getString(PKG,
"DBProcDialog.ProcedureName.Label"));
- PropsUi.setLook(wlProcName);
- FormData fdlProcName = new FormData();
- fdlProcName.left = new FormAttachment(0, 0);
- fdlProcName.right = new FormAttachment(middle, -margin);
- fdlProcName.top = new FormAttachment(wConnection, margin);
- wlProcName.setLayoutData(fdlProcName);
-
- wProcName = new TextVar(variables, shell, SWT.SINGLE | SWT.LEFT |
SWT.BORDER);
- PropsUi.setLook(wProcName);
- FormData fdProcName = new FormData();
- fdProcName.left = new FormAttachment(middle, 0);
- fdProcName.top = new FormAttachment(wConnection, margin);
- fdProcName.right = new FormAttachment(wbProcName, -margin);
- wProcName.setLayoutData(fdProcName);
-
- // AutoCommit line
- Label wlAutoCommit = new Label(shell, SWT.RIGHT);
- wlAutoCommit.setText(BaseMessages.getString(PKG,
"DBProcDialog.AutoCommit.Label"));
- wlAutoCommit.setToolTipText(BaseMessages.getString(PKG,
"DBProcDialog.AutoCommit.Tooltip"));
- PropsUi.setLook(wlAutoCommit);
- FormData fdlAutoCommit = new FormData();
- fdlAutoCommit.left = new FormAttachment(0, 0);
- fdlAutoCommit.top = new FormAttachment(wProcName, margin);
- fdlAutoCommit.right = new FormAttachment(middle, -margin);
- wlAutoCommit.setLayoutData(fdlAutoCommit);
- wAutoCommit = new Button(shell, SWT.CHECK);
- wAutoCommit.setToolTipText(BaseMessages.getString(PKG,
"DBProcDialog.AutoCommit.Tooltip"));
- PropsUi.setLook(wAutoCommit);
- FormData fdAutoCommit = new FormData();
- fdAutoCommit.left = new FormAttachment(middle, 0);
- fdAutoCommit.top = new FormAttachment(wlAutoCommit, 0, SWT.CENTER);
- fdAutoCommit.right = new FormAttachment(100, 0);
- wAutoCommit.setLayoutData(fdAutoCommit);
-
- // Result line...
- Label wlResult = new Label(shell, SWT.RIGHT);
- wlResult.setText(BaseMessages.getString(PKG, "DBProcDialog.Result.Label"));
- PropsUi.setLook(wlResult);
- FormData fdlResult = new FormData();
- fdlResult.left = new FormAttachment(0, 0);
- fdlResult.right = new FormAttachment(middle, -margin);
- fdlResult.top = new FormAttachment(wAutoCommit, margin);
- wlResult.setLayoutData(fdlResult);
- wResult = new Text(shell, SWT.SINGLE | SWT.LEFT | SWT.BORDER);
- PropsUi.setLook(wResult);
- FormData fdResult = new FormData();
- fdResult.left = new FormAttachment(middle, 0);
- fdResult.top = new FormAttachment(wAutoCommit, margin);
- fdResult.right = new FormAttachment(100, 0);
- wResult.setLayoutData(fdResult);
-
- // ResultType line
- Label wlResultType = new Label(shell, SWT.RIGHT);
- wlResultType.setText(BaseMessages.getString(PKG,
"DBProcDialog.ResultType.Label"));
- PropsUi.setLook(wlResultType);
- FormData fdlResultType = new FormData();
- fdlResultType.left = new FormAttachment(0, 0);
- fdlResultType.right = new FormAttachment(middle, -margin);
- fdlResultType.top = new FormAttachment(wResult, margin);
- wlResultType.setLayoutData(fdlResultType);
- wResultType = new CCombo(shell, SWT.BORDER | SWT.READ_ONLY);
- PropsUi.setLook(wResultType);
- String[] types = ValueMetaFactory.getValueMetaNames();
- for (String type : types) {
- wResultType.add(type);
- }
- wResultType.select(0);
- FormData fdResultType = new FormData();
- fdResultType.left = new FormAttachment(middle, 0);
- fdResultType.top = new FormAttachment(wResult, margin);
- fdResultType.right = new FormAttachment(100, 0);
- wResultType.setLayoutData(fdResultType);
-
- Label wlFields = new Label(shell, SWT.NONE);
- wlFields.setText(BaseMessages.getString(PKG,
"DBProcDialog.Parameters.Label"));
- PropsUi.setLook(wlFields);
- FormData fdlFields = new FormData();
- fdlFields.left = new FormAttachment(0, 0);
- fdlFields.top = new FormAttachment(wResultType, margin);
- wlFields.setLayoutData(fdlFields);
-
- final int nrRows = input.getArguments().size();
-
- fieldColumns =
+ widgets =
+ GuiCompositeWidgets.addScrolledComposite(
+ shell,
+ variables,
+ wTransformName,
+ wOk,
+ DBProcMeta.GUI_PLUGIN_ELEMENT_PARENT_ID,
+ input,
+ this::beforeCreate);
+ widgets.setWidgetsListener(
+ new GuiCompositeWidgetsAdapter() {
+ @Override
+ public void widgetModified(
+ GuiCompositeWidgets compositeWidgets, Control changedWidget,
String widgetId) {
+ if (DBProcMeta.WIDGET_RESULT_TYPE.equals(widgetId)) {
+ updateFieldsTab();
+ }
+ }
+ });
+
+ populateArguments();
+ populateResultFields();
+ findTabs();
+ updateFieldsTab();
+ loadInputFieldNames();
+
+ input.setChanged(changed);
+ focusTransformName();
+ BaseDialog.defaultShellHandling(shell, c -> ok(), c -> cancel());
+ return transformName;
+ }
+
+ private void beforeCreate(GuiCompositeWidgets compositeWidgets) {
+ widgets = compositeWidgets;
+ compositeWidgets.registerExtraGroup(
+ BaseMessages.getString(PKG, "DBProcDialog.Group.General"), "10", null,
this::addFindButton);
+ compositeWidgets.registerExtraGroup(
+ BaseMessages.getString(PKG, "DBProcDialog.Group.Parameters"),
+ "20",
+ null,
+ this::addArgumentsTable);
+ compositeWidgets.registerExtraGroup(
+ BaseMessages.getString(PKG, "DBProcDialog.Group.Fields"),
+ "30",
+ null,
+ this::addResultFieldsTable);
+ }
+
+ private void addFindButton(Composite parent) {
+ Control[] children = parent.getChildren();
+ Control last = children.length == 0 ? null : children[children.length - 1];
+
+ Button find = new Button(parent, SWT.PUSH);
+ find.setText(BaseMessages.getString(PKG, "DBProcDialog.Finding.Button"));
+ find.setToolTipText(BaseMessages.getString(PKG,
"DBProcDialog.Finding.Tooltip"));
+ PropsUi.setLook(find);
+ find.addListener(SWT.Selection, e -> selectProcedure());
+ FormData fdFind = new FormData();
+ fdFind.right = new FormAttachment(100, 0);
+ fdFind.top = last == null ? new FormAttachment(0, 0) : new
FormAttachment(last, margin);
+ find.setLayoutData(fdFind);
+ }
+
+ private void addArgumentsTable(Composite parent) {
+ int nrRows = input.getArguments() == null ? 0 :
input.getArguments().size();
+ argumentColumns =
new ColumnInfo[] {
new ColumnInfo(
BaseMessages.getString(PKG, "DBProcDialog.ColumnInfo.Name"),
@@ -205,131 +175,450 @@ public class DBProcDialog extends BaseTransformDialog {
ColumnInfo.COLUMN_TYPE_CCOMBO,
ValueMetaFactory.getValueMetaNames()),
};
- wFields =
+ wArguments =
new TableView(
variables,
- shell,
+ parent,
SWT.BORDER | SWT.FULL_SELECTION | SWT.MULTI,
- fieldColumns,
+ argumentColumns,
nrRows,
null,
props);
+ FormData fdArguments = new FormData();
+ fdArguments.left = new FormAttachment(0, 0);
+ fdArguments.top = new FormAttachment(0, 0);
+ fdArguments.right = new FormAttachment(100, 0);
+ fdArguments.bottom = new FormAttachment(100, 0);
+ wArguments.setLayoutData(fdArguments);
+ }
+ private void addResultFieldsTable(Composite parent) {
+ wGetResultFields = new Button(parent, SWT.PUSH);
+ wGetResultFields.setText(BaseMessages.getString(PKG,
"DBProcDialog.GetResultFields.Button"));
+ wGetResultFields.setToolTipText(
+ BaseMessages.getString(PKG, "DBProcDialog.GetResultFields.Tooltip"));
+ PropsUi.setLook(wGetResultFields);
+ wGetResultFields.addListener(SWT.Selection, e -> getResultFields());
+ FormData fdGet = new FormData();
+ fdGet.top = new FormAttachment(0, 0);
+ fdGet.right = new FormAttachment(100, 0);
+ wGetResultFields.setLayoutData(fdGet);
+
+ int nrRows = input.getResultFields() == null ? 0 :
input.getResultFields().size();
+ ColumnInfo[] columns =
+ new ColumnInfo[] {
+ new ColumnInfo(
+ BaseMessages.getString(PKG, "DBProcDialog.ColumnInfo.Name"),
+ ColumnInfo.COLUMN_TYPE_TEXT,
+ false),
+ new ColumnInfo(
+ BaseMessages.getString(PKG, "DBProcDialog.ColumnInfo.Type"),
+ ColumnInfo.COLUMN_TYPE_CCOMBO,
+ ValueMetaFactory.getValueMetaNames(),
+ true),
+ new ColumnInfo(
+ BaseMessages.getString(PKG, "DBProcDialog.ColumnInfo.Format"),
+ ColumnInfo.COLUMN_TYPE_FORMAT,
+ 2),
+ new ColumnInfo(
+ BaseMessages.getString(PKG, "DBProcDialog.ColumnInfo.Length"),
+ ColumnInfo.COLUMN_TYPE_TEXT,
+ false),
+ new ColumnInfo(
+ BaseMessages.getString(PKG, "DBProcDialog.ColumnInfo.Precision"),
+ ColumnInfo.COLUMN_TYPE_TEXT,
+ false)
+ };
+ wResultFields =
+ new TableView(
+ variables,
+ parent,
+ SWT.BORDER | SWT.FULL_SELECTION | SWT.MULTI | SWT.V_SCROLL |
SWT.H_SCROLL,
+ columns,
+ nrRows,
+ null,
+ props);
FormData fdFields = new FormData();
fdFields.left = new FormAttachment(0, 0);
- fdFields.top = new FormAttachment(wlFields, margin);
+ fdFields.top = new FormAttachment(wGetResultFields, margin);
fdFields.right = new FormAttachment(100, 0);
- fdFields.bottom = new FormAttachment(wOk, -margin);
- wFields.setLayoutData(fdFields);
+ fdFields.bottom = new FormAttachment(100, 0);
+ wResultFields.setLayoutData(fdFields);
+ }
- //
- // Search the fields in the background
+ private void findTabs() {
+ tabFolder = findTabFolder(shell);
+ if (tabFolder == null) {
+ return;
+ }
+ String fieldsLabel = BaseMessages.getString(PKG,
"DBProcDialog.Group.Fields");
+ String generalLabel = BaseMessages.getString(PKG,
"DBProcDialog.Group.General");
+ for (CTabItem item : tabFolder.getItems()) {
+ if (fieldsLabel.equals(item.getText())) {
+ fieldsTab = item;
+ fieldsTab.setToolTipText(BaseMessages.getString(PKG,
"DBProcDialog.FieldsTab.Tooltip"));
+ } else if (generalLabel.equals(item.getText())) {
+ generalTab = item;
+ }
+ }
+ tabFolder.addListener(
+ SWT.Selection,
+ e -> {
+ if (adjustingTab || isRowResultType() || fieldsTab == null) {
+ return;
+ }
+ if (tabFolder.getSelection() == fieldsTab) {
+ adjustingTab = true;
+ try {
+ tabFolder.setSelection(generalTab != null ? generalTab :
tabFolder.getItem(0));
+ } finally {
+ adjustingTab = false;
+ }
+ }
+ });
+ }
- final Runnable runnable =
+ private CTabFolder findTabFolder(Control control) {
+ if (control instanceof CTabFolder folder) {
+ return folder;
+ }
+ if (control instanceof Composite composite) {
+ for (Control child : composite.getChildren()) {
+ CTabFolder found = findTabFolder(child);
+ if (found != null) {
+ return found;
+ }
+ }
+ }
+ return null;
+ }
+
+ private void updateFieldsTab() {
+ boolean rows = isRowResultType();
+ setWidgetEnabled(DBProcMeta.WIDGET_RESULT_NAME, !rows);
+ if (wResultFields != null && !wResultFields.isDisposed()) {
+ wResultFields.setEnabled(rows);
+ }
+ if (wGetResultFields != null && !wGetResultFields.isDisposed()) {
+ wGetResultFields.setEnabled(rows);
+ }
+ if (fieldsTab != null && !fieldsTab.isDisposed()) {
+ fieldsTab.setForeground(rows ? null :
shell.getDisplay().getSystemColor(SWT.COLOR_DARK_GRAY));
+ if (fieldsTab.getControl() != null &&
!fieldsTab.getControl().isDisposed()) {
+ fieldsTab.getControl().setEnabled(rows);
+ }
+ if (!rows && tabFolder != null && tabFolder.getSelection() == fieldsTab)
{
+ adjustingTab = true;
+ try {
+ tabFolder.setSelection(generalTab != null ? generalTab :
tabFolder.getItem(0));
+ } finally {
+ adjustingTab = false;
+ }
+ }
+ }
+ }
+
+ private boolean isRowResultType() {
+ return
DBProcMeta.RESULT_TYPE_ROW.equalsIgnoreCase(widgetText(DBProcMeta.WIDGET_RESULT_TYPE));
+ }
+
+ private void loadInputFieldNames() {
+ BackgroundThreadFacade.start(
() -> {
TransformMeta transformMeta =
pipelineMeta.findTransform(transformName);
- if (transformMeta != null) {
- try {
- IRowMeta row = pipelineMeta.getPrevTransformFields(variables,
transformMeta);
-
- // Remember these fields...
+ if (transformMeta == null) {
+ return;
+ }
+ final List<String> names = new ArrayList<>();
+ try {
+ IRowMeta row = pipelineMeta.getPrevTransformFields(variables,
transformMeta);
+ if (row != null) {
for (int i = 0; i < row.size(); i++) {
- inputFields.add(row.getValueMeta(i).getName());
+ names.add(row.getValueMeta(i).getName());
}
- setComboBoxes();
- } catch (HopException e) {
- logError(BaseMessages.getString(PKG,
"System.Dialog.GetFieldsFailed.Message"));
}
+ } catch (HopException e) {
+ logError(BaseMessages.getString(PKG,
"System.Dialog.GetFieldsFailed.Message"));
+ return;
}
- };
- BackgroundThreadFacade.start(runnable);
-
- lsResize =
- event -> {
- Point size = shell.getSize();
- wFields.setSize(size.x - 10, size.y - 50);
- wFields.table.setSize(size.x - 10, size.y - 50);
- wFields.redraw();
- };
- shell.addListener(SWT.Resize, lsResize);
-
- getData();
- focusTransformName();
- BaseDialog.defaultShellHandling(shell, c -> ok(), c -> cancel());
+ if (shell.isDisposed()) {
+ return;
+ }
+ shell
+ .getDisplay()
+ .asyncExec(
+ () -> {
+ if (shell.isDisposed() || argumentColumns == null) {
+ return;
+ }
+ inputFields.clear();
+ inputFields.addAll(names);
+ setComboBoxes();
+ });
+ });
+ }
- return transformName;
+ private void selectProcedure() {
+ String connectionName = widgetText(DBProcMeta.WIDGET_CONNECTION);
+ if (Utils.isEmpty(connectionName)) {
+ showMessage(
+ "DBProcDialog.InvalidConnection.DialogTitle",
+ "DBProcDialog.InvalidConnection.DialogMessage",
+ SWT.OK | SWT.ICON_ERROR);
+ return;
+ }
+ DatabaseMeta databaseMeta = pipelineMeta.findDatabase(connectionName,
variables);
+ if (databaseMeta == null) {
+ showMessage(
+ "DBProcDialog.InvalidConnection.DialogTitle",
+ "DBProcDialog.InvalidConnection.DialogMessage",
+ SWT.OK | SWT.ICON_ERROR);
+ return;
+ }
+ try (Database db = new Database(loggingObject, variables, databaseMeta)) {
+ db.connect();
+ String[] procs = db.getProcedures();
+ if (procs != null && procs.length > 0) {
+ EnterSelectionDialog esd =
+ new EnterSelectionDialog(
+ shell,
+ procs,
+ BaseMessages.getString(PKG,
"DBProcDialog.EnterSelection.DialogTitle"),
+ BaseMessages.getString(PKG,
"DBProcDialog.EnterSelection.DialogMessage"));
+ String procedure = esd.open();
+ if (procedure != null) {
+ setProcedureText(procedure);
+ }
+ } else {
+ showMessage(
+ "DBProcDialog.NoProceduresFound.DialogTitle",
+ "DBProcDialog.NoProceduresFound.DialogMessage",
+ SWT.OK | SWT.ICON_INFORMATION);
+ }
+ } catch (HopDatabaseException dbe) {
+ new ErrorDialog(
+ shell,
+ BaseMessages.getString(PKG,
"DBProcDialog.ErrorGettingProceduresList.DialogTitle"),
+ BaseMessages.getString(PKG,
"DBProcDialog.ErrorGettingProceduresList.DialogMessage"),
+ dbe);
+ }
}
- private void selectProcedure(Event event) {
- DatabaseMeta databaseMeta =
pipelineMeta.findDatabase(wConnection.getText(), variables);
- if (databaseMeta != null) {
- try (Database db = new Database(loggingObject, variables, databaseMeta))
{
- db.connect();
- String[] procs = db.getProcedures();
- if (procs != null && procs.length > 0) {
- EnterSelectionDialog esd =
- new EnterSelectionDialog(
- shell,
- procs,
- BaseMessages.getString(PKG,
"DBProcDialog.EnterSelection.DialogTitle"),
- BaseMessages.getString(PKG,
"DBProcDialog.EnterSelection.DialogMessage"));
- String proc = esd.open();
- if (proc != null) {
- wProcName.setText(proc);
- }
- } else {
- MessageBox mb = new MessageBox(shell, SWT.OK | SWT.ICON_INFORMATION);
- mb.setMessage(
- BaseMessages.getString(PKG,
"DBProcDialog.NoProceduresFound.DialogMessage"));
- mb.setText(BaseMessages.getString(PKG,
"DBProcDialog.NoProceduresFound.DialogTitle"));
- mb.open();
+ private void getResultFields() {
+ String connectionName = widgetText(DBProcMeta.WIDGET_CONNECTION);
+ if (Utils.isEmpty(connectionName)) {
+ showMessage(
+ "DBProcDialog.InvalidConnection.DialogTitle",
+ "DBProcDialog.InvalidConnection.DialogMessage",
+ SWT.OK | SWT.ICON_ERROR);
+ return;
+ }
+ DatabaseMeta databaseMeta = pipelineMeta.findDatabase(connectionName,
variables);
+ if (databaseMeta == null) {
+ showMessage(
+ "DBProcDialog.InvalidConnection.DialogTitle",
+ "DBProcDialog.InvalidConnection.DialogMessage",
+ SWT.OK | SWT.ICON_ERROR);
+ return;
+ }
+
+ List<DBProcMeta.ProcArgument> arguments = new ArrayList<>();
+ for (DBProcMeta.ProcArgument argument : readArguments()) {
+ if (!Utils.isEmpty(argument.getName()) &&
!Utils.isEmpty(argument.getDirection())) {
+ arguments.add(argument);
+ }
+ }
+ String[] names = new String[arguments.size()];
+ String[] directions = new String[arguments.size()];
+ int[] types = new int[arguments.size()];
+ for (int i = 0; i < arguments.size(); i++) {
+ names[i] = arguments.get(i).getName();
+ directions[i] = arguments.get(i).getDirection();
+ types[i] =
ValueMetaFactory.getIdForValueMeta(arguments.get(i).getType());
+ }
+ String procedure =
variables.resolve(widgetText(DBProcMeta.WIDGET_PROCEDURE));
+
+ try (Database db = new Database(loggingObject, variables, databaseMeta)) {
+ db.connect();
+ try {
+ db.setAutoCommit(false);
+ } catch (HopDatabaseException ignored) {
+ // The driver does not allow auto-commit to be turned off. A procedure
that commits
+ // itself can still change data.
+ }
+ try {
+ IRowMeta described = db.describeProcedureResultFields(procedure,
names, directions, types);
+ IRowMeta fields =
+ DBProcResultFieldLookup.fields(
+ described,
+ this::confirmProcedureExecution,
+ () -> db.getProcedureResultFields(procedure, names,
directions, types));
+ // Null means the user declined. An empty row is a call that returned
no result set.
+ if (fields == null) {
+ return;
+ }
+ if (fields.isEmpty()) {
+ showMessage(
+ "DBProcDialog.NoResultSet.DialogTitle",
+ "DBProcDialog.NoResultSet.DialogMessage",
+ SWT.OK | SWT.ICON_INFORMATION);
+ return;
+ }
+ wResultFields.clearAll(false);
+ for (IValueMeta valueMeta : fields.getValueMetaList()) {
+ TableItem item = new TableItem(wResultFields.table, SWT.NONE);
+ item.setText(1, Const.NVL(valueMeta.getName(), ""));
+ item.setText(2, valueMeta.getTypeDesc());
+ item.setText(3, Const.NVL(valueMeta.getConversionMask(), ""));
+ item.setText(4, valueMeta.getLength() < 0 ? "" :
Integer.toString(valueMeta.getLength()));
+ item.setText(
+ 5, valueMeta.getPrecision() < 0 ? "" :
Integer.toString(valueMeta.getPrecision()));
+ }
+ wResultFields.removeEmptyRows();
+ wResultFields.setRowNums();
+ wResultFields.optWidth(true);
+ } finally {
+ try {
+ db.rollback();
+ } catch (HopDatabaseException ignored) {
+ // A procedure that commits itself cannot be rolled back by this
probe.
}
- } catch (HopDatabaseException dbe) {
- new ErrorDialog(
- shell,
- BaseMessages.getString(PKG,
"DBProcDialog.ErrorGettingProceduresList.DialogTitle"),
- BaseMessages.getString(PKG,
"DBProcDialog.ErrorGettingProceduresList.DialogMessage"),
- dbe);
}
+ } catch (HopException e) {
+ new ErrorDialog(
+ shell,
+ BaseMessages.getString(PKG,
"DBProcDialog.FailedToGetResultFields.DialogTitle"),
+ BaseMessages.getString(PKG,
"DBProcDialog.FailedToGetResultFields.DialogMessage"),
+ e);
}
}
protected void setComboBoxes() {
- // Something was changed in the row.
- //
String[] fieldNames = ConstUi.sortFieldNames(inputFields);
- fieldColumns[0].setComboValues(fieldNames);
+ argumentColumns[0].setComboValues(fieldNames);
}
- /** Copy information from the meta-data input to the dialog fields. */
- public void getData() {
- int i;
- logDebug(BaseMessages.getString(PKG, "DBProcDialog.Log.GettingKeyInfo"));
-
- for (i = 0; i < input.getArguments().size(); i++) {
+ private void populateArguments() {
+ if (wArguments == null || input.getArguments() == null) {
+ return;
+ }
+ for (int i = 0; i < input.getArguments().size(); i++) {
DBProcMeta.ProcArgument argument = input.getArguments().get(i);
- TableItem item = wFields.table.getItem(i);
-
+ TableItem item = wArguments.table.getItem(i);
item.setText(1, Const.NVL(argument.getName(), ""));
item.setText(2, Const.NVL(argument.getDirection(), ""));
item.setText(3, Const.NVL(argument.getType(), ""));
}
+ wArguments.optimizeTableView();
+ }
- if (input.getConnection() != null) {
- wConnection.setText(input.getConnection());
+ private void populateResultFields() {
+ if (wResultFields == null || input.getResultFields() == null) {
+ return;
+ }
+ for (int i = 0; i < input.getResultFields().size(); i++) {
+ DBProcField field = input.getResultFields().get(i);
+ TableItem item = wResultFields.table.getItem(i);
+ item.setText(1, Const.NVL(field.getName(), ""));
+ item.setText(2, Const.NVL(field.getType(), ""));
+ item.setText(3, Const.NVL(field.getFormat(), ""));
+ item.setText(4, field.getLength() < 0 ? "" :
Integer.toString(field.getLength()));
+ item.setText(5, field.getPrecision() < 0 ? "" :
Integer.toString(field.getPrecision()));
}
- wProcName.setText(Const.NVL(input.getProcedure(), ""));
+ wResultFields.optimizeTableView();
+ }
- wResult.setText(Const.NVL(input.getResult().getName(), ""));
- wResultType.setText(Const.NVL(input.getResult().getType(), ""));
+ private List<DBProcMeta.ProcArgument> readArguments() {
+ List<DBProcMeta.ProcArgument> arguments = new ArrayList<>();
+ if (wArguments == null || wArguments.isDisposed()) {
+ return arguments;
+ }
+ for (TableItem item : wArguments.getNonEmptyItems()) {
+ DBProcMeta.ProcArgument argument = new DBProcMeta.ProcArgument();
+ argument.setName(item.getText(1));
+ argument.setDirection(item.getText(2));
+ argument.setType(item.getText(3));
+ arguments.add(argument);
+ }
+ return arguments;
+ }
- wAutoCommit.setSelection(input.isAutoCommit());
+ private List<DBProcField> readResultFields() {
+ List<DBProcField> fields = new ArrayList<>();
+ if (wResultFields == null || wResultFields.isDisposed()) {
+ return fields;
+ }
+ for (TableItem item : wResultFields.getNonEmptyItems()) {
+ if (Utils.isEmpty(item.getText(1))) {
+ continue;
+ }
+ DBProcField field = new DBProcField();
+ field.setName(item.getText(1));
+ field.setType(item.getText(2));
+ field.setFormat(item.getText(3));
+ field.setLength(Const.toInt(item.getText(4), -1));
+ field.setPrecision(Const.toInt(item.getText(5), -1));
+ fields.add(field);
+ }
+ return fields;
+ }
- wFields.optimizeTableView();
+ private String widgetText(String widgetId) {
+ Control control = widgets.getWidgetsMap().get(widgetId);
+ if (control == null || control.isDisposed()) {
+ return "";
+ }
+ if (control instanceof MetaSelectionLine<?> line) {
+ return line.getText();
+ }
+ if (control instanceof TextVar textVar) {
+ return textVar.getText();
+ }
+ if (control instanceof Combo combo) {
+ return combo.getText();
+ }
+ if (control instanceof Text text) {
+ return text.getText();
+ }
+ return "";
+ }
+
+ private void setProcedureText(String procedure) {
+ Control control = widgets.getWidgetsMap().get(DBProcMeta.WIDGET_PROCEDURE);
+ if (control instanceof TextVar textVar && !textVar.isDisposed()) {
+ textVar.setText(Const.NVL(procedure, ""));
+ }
+ }
+
+ private void setWidgetEnabled(String widgetId, boolean enabled) {
+ Control control = widgets.getWidgetsMap().get(widgetId);
+ if (control != null && !control.isDisposed()) {
+ control.setEnabled(enabled);
+ }
+ Control label = widgets.getLabelsMap().get(widgetId);
+ if (label != null && !label.isDisposed()) {
+ label.setEnabled(enabled);
+ }
+ }
+
+ private boolean confirmProcedureExecution() {
+ MessageBox box = new MessageBox(shell, SWT.YES | SWT.NO |
SWT.ICON_QUESTION);
+ box.setText(BaseMessages.getString(PKG,
"DBProcDialog.ConfirmProcedureExecution.DialogTitle"));
+ box.setMessage(
+ BaseMessages.getString(PKG,
"DBProcDialog.ConfirmProcedureExecution.DialogMessage"));
+ return box.open() == SWT.YES;
+ }
+
+ private void showMessage(String titleKey, String messageKey, int style) {
+ MessageBox box = new MessageBox(shell, style);
+ box.setText(BaseMessages.getString(PKG, titleKey));
+ box.setMessage(BaseMessages.getString(PKG, messageKey));
+ box.open();
}
private void cancel() {
transformName = null;
+ input.setChanged(changed);
dispose();
}
@@ -337,31 +626,18 @@ public class DBProcDialog extends BaseTransformDialog {
if (Utils.isEmpty(wTransformName.getText())) {
return;
}
-
- input.getArguments().clear();
- for (TableItem item : wFields.getNonEmptyItems()) {
- DBProcMeta.ProcArgument argument = new DBProcMeta.ProcArgument();
- argument.setName(item.getText(1));
- argument.setDirection(item.getText(2));
- argument.setType(item.getText(3));
- input.getArguments().add(argument);
- }
- input.setConnection(wConnection.getText());
- input.setProcedure(wProcName.getText());
- input.getResult().setName(wResult.getText());
- input.getResult().setType(wResultType.getText());
- input.setAutoCommit(wAutoCommit.getSelection());
-
- transformName = wTransformName.getText(); // return value
-
- if (input.getConnection() == null) {
- MessageBox mb = new MessageBox(shell, SWT.OK | SWT.ICON_ERROR);
- mb.setMessage(BaseMessages.getString(PKG,
"DBProcDialog.InvalidConnection.DialogMessage"));
- mb.setText(BaseMessages.getString(PKG,
"DBProcDialog.InvalidConnection.DialogTitle"));
- mb.open();
- }
-
+ String connectionName = widgetText(DBProcMeta.WIDGET_CONNECTION);
+ widgets.getWidgetsContents(input, DBProcMeta.GUI_PLUGIN_ELEMENT_PARENT_ID);
+ input.setArguments(readArguments());
+ input.setResultFields(readResultFields());
+ transformName = wTransformName.getText();
input.setChanged();
+ if (Utils.isEmpty(connectionName)) {
+ showMessage(
+ "DBProcDialog.InvalidConnection.DialogTitle",
+ "DBProcDialog.InvalidConnection.DialogMessage",
+ SWT.OK | SWT.ICON_ERROR);
+ }
dispose();
}
@@ -375,7 +651,7 @@ public class DBProcDialog extends BaseTransformDialog {
return true;
};
BaseTransformDialog.getFieldsFromPrevious(
- r, wFields, 1, new int[] {1}, new int[] {3}, -1, -1, listener);
+ r, wArguments, 1, new int[] {1}, new int[] {3}, -1, -1, listener);
}
} catch (HopException ke) {
new ErrorDialog(
diff --git
a/plugins/transforms/dbproc/src/main/java/org/apache/hop/pipeline/transforms/dbproc/DBProcField.java
b/plugins/transforms/dbproc/src/main/java/org/apache/hop/pipeline/transforms/dbproc/DBProcField.java
new file mode 100644
index 0000000000..23b6bda17a
--- /dev/null
+++
b/plugins/transforms/dbproc/src/main/java/org/apache/hop/pipeline/transforms/dbproc/DBProcField.java
@@ -0,0 +1,68 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.dbproc;
+
+import lombok.Getter;
+import lombok.Setter;
+import org.apache.hop.core.exception.HopPluginException;
+import org.apache.hop.core.row.IValueMeta;
+import org.apache.hop.core.row.value.ValueMetaFactory;
+import org.apache.hop.core.util.Utils;
+import org.apache.hop.core.variables.IVariables;
+import org.apache.hop.metadata.api.HopMetadataProperty;
+
+/** One column of a procedure result set, used when the result type is {@code
Row}. */
+@Getter
+@Setter
+public class DBProcField {
+ @HopMetadataProperty(key = "name")
+ private String name;
+
+ @HopMetadataProperty(key = "type")
+ private String type;
+
+ @HopMetadataProperty(key = "format")
+ private String format;
+
+ @HopMetadataProperty(key = "length")
+ private int length = -1;
+
+ @HopMetadataProperty(key = "precision")
+ private int precision = -1;
+
+ public DBProcField() {}
+
+ public IValueMeta toValueMeta(String origin, IVariables variables) throws
HopPluginException {
+ int hopType = ValueMetaFactory.getIdForValueMeta(type);
+ if (hopType == IValueMeta.TYPE_NONE) {
+ hopType = IValueMeta.TYPE_STRING;
+ }
+ String fieldName = name;
+ if (variables != null && fieldName != null) {
+ fieldName = variables.resolve(fieldName);
+ }
+ IValueMeta valueMeta = ValueMetaFactory.createValueMeta(fieldName,
hopType);
+ valueMeta.setLength(length);
+ valueMeta.setPrecision(precision);
+ valueMeta.setOrigin(origin);
+ if (!Utils.isEmpty(format)) {
+ valueMeta.setConversionMask(variables == null ? format :
variables.resolve(format));
+ }
+ return valueMeta;
+ }
+}
diff --git
a/plugins/transforms/dbproc/src/main/java/org/apache/hop/pipeline/transforms/dbproc/DBProcMeta.java
b/plugins/transforms/dbproc/src/main/java/org/apache/hop/pipeline/transforms/dbproc/DBProcMeta.java
index e0b6bff334..1523bd74b8 100644
---
a/plugins/transforms/dbproc/src/main/java/org/apache/hop/pipeline/transforms/dbproc/DBProcMeta.java
+++
b/plugins/transforms/dbproc/src/main/java/org/apache/hop/pipeline/transforms/dbproc/DBProcMeta.java
@@ -19,6 +19,9 @@ package org.apache.hop.pipeline.transforms.dbproc;
import java.util.ArrayList;
import java.util.List;
+import lombok.AccessLevel;
+import lombok.Getter;
+import lombok.Setter;
import org.apache.hop.core.CheckResult;
import org.apache.hop.core.Const;
import org.apache.hop.core.ICheckResult;
@@ -29,6 +32,11 @@ import org.apache.hop.core.database.DatabaseMeta;
import org.apache.hop.core.exception.HopException;
import org.apache.hop.core.exception.HopPluginException;
import org.apache.hop.core.exception.HopTransformException;
+import org.apache.hop.core.gui.plugin.GuiElementType;
+import org.apache.hop.core.gui.plugin.GuiPlugin;
+import org.apache.hop.core.gui.plugin.GuiWidgetElement;
+import org.apache.hop.core.gui.plugin.GuiWidgetGroupType;
+import org.apache.hop.core.logging.ILogChannel;
import org.apache.hop.core.row.IRowMeta;
import org.apache.hop.core.row.IValueMeta;
import org.apache.hop.core.row.value.ValueMetaBase;
@@ -52,42 +60,206 @@ import org.apache.hop.pipeline.transform.TransformMeta;
keywords = "i18n::DBProcMeta.keyword",
documentationUrl = "/pipeline/transforms/calldbproc.html",
actionTransformTypes = ActionTransformType.RDBMS)
+@GuiPlugin
+@Getter
+@Setter
public class DBProcMeta extends BaseTransformMeta<DBProc, DBProcData> {
private static final Class<?> PKG = DBProcMeta.class;
+ public static final String GUI_PLUGIN_ELEMENT_PARENT_ID = "DBProcDialog";
+ public static final String WIDGET_CONNECTION = "connection";
+ public static final String WIDGET_PROCEDURE = "procedure";
+ public static final String WIDGET_AUTO_COMMIT = "autoCommit";
+ public static final String WIDGET_RESULT_NAME = "resultName";
+ public static final String WIDGET_RESULT_TYPE = "resultType";
+
+ /** Result type that outputs the rows of the procedure's first result set. */
+ public static final String RESULT_TYPE_ROW = "Row";
+
+ public static final String GROUP_GENERAL =
"i18n::DBProcDialog.Group.General";
+
/** database connection */
+ @GuiWidgetElement(
+ id = WIDGET_CONNECTION,
+ order = "0100",
+ type = GuiElementType.METADATA,
+ metadata = DatabaseMeta.class,
+ label = "i18n::DBProcDialog.Connection.Label",
+ toolTip = "i18n::DBProcDialog.Connection.Tooltip",
+ parentId = GUI_PLUGIN_ELEMENT_PARENT_ID,
+ group = GROUP_GENERAL,
+ groupOrder = "10",
+ groupType = GuiWidgetGroupType.TABS)
@HopMetadataProperty(
key = "connection",
hopMetadataPropertyType = HopMetadataPropertyType.RDBMS_CONNECTION)
private String connection;
/** procedure name to be called */
- @HopMetadataProperty private String procedure;
+ @GuiWidgetElement(
+ id = WIDGET_PROCEDURE,
+ order = "0200",
+ type = GuiElementType.TEXT,
+ label = "i18n::DBProcDialog.ProcedureName.Label",
+ toolTip = "i18n::DBProcDialog.ProcedureName.Tooltip",
+ parentId = GUI_PLUGIN_ELEMENT_PARENT_ID,
+ group = GROUP_GENERAL,
+ groupOrder = "10",
+ groupType = GuiWidgetGroupType.TABS)
+ @HopMetadataProperty
+ private String procedure;
+
+ /** The flag to set auto commit on or off on the connection */
+ @GuiWidgetElement(
+ id = WIDGET_AUTO_COMMIT,
+ order = "0300",
+ type = GuiElementType.CHECKBOX,
+ label = "i18n::DBProcDialog.AutoCommit.Label",
+ toolTip = "i18n::DBProcDialog.AutoCommit.Tooltip",
+ parentId = GUI_PLUGIN_ELEMENT_PARENT_ID,
+ group = GROUP_GENERAL,
+ groupOrder = "10",
+ groupType = GuiWidgetGroupType.TABS,
+ getterMethod = "isAutoCommit")
+ @HopMetadataProperty(key = "auto_commit")
+ private boolean autoCommit;
+
+ /** Not stored. The dialog edits {@link ProcResult#name} through this
widget. */
+ @Getter(AccessLevel.NONE)
+ @Setter(AccessLevel.NONE)
+ @GuiWidgetElement(
+ id = WIDGET_RESULT_NAME,
+ order = "0400",
+ type = GuiElementType.TEXT,
+ variables = false,
+ label = "i18n::DBProcDialog.Result.Label",
+ toolTip = "i18n::DBProcDialog.Result.Tooltip",
+ parentId = GUI_PLUGIN_ELEMENT_PARENT_ID,
+ group = GROUP_GENERAL,
+ groupOrder = "10",
+ groupType = GuiWidgetGroupType.TABS,
+ getterMethod = "getResultName",
+ setterMethod = "setResultName")
+ private String resultNameForUi;
+
+ /** Not stored. The dialog edits {@link ProcResult#type} through this
widget. */
+ @Getter(AccessLevel.NONE)
+ @Setter(AccessLevel.NONE)
+ @GuiWidgetElement(
+ id = WIDGET_RESULT_TYPE,
+ order = "0500",
+ type = GuiElementType.COMBO,
+ variables = false,
+ comboValuesMethod = "getResultTypeNames",
+ label = "i18n::DBProcDialog.ResultType.Label",
+ toolTip = "i18n::DBProcDialog.ResultType.Tooltip",
+ parentId = GUI_PLUGIN_ELEMENT_PARENT_ID,
+ group = GROUP_GENERAL,
+ groupOrder = "10",
+ groupType = GuiWidgetGroupType.TABS,
+ getterMethod = "getResultType",
+ setterMethod = "setResultType")
+ private String resultTypeForUi;
/** function arguments */
@HopMetadataProperty(groupKey = "lookup", key = "arg")
- List<ProcArgument> arguments;
+ private List<ProcArgument> arguments;
@HopMetadataProperty private ProcResult result;
- /** The flag to set auto commit on or off on the connection */
- @HopMetadataProperty(key = "auto_commit")
- private boolean autoCommit;
+ /** Columns of the first result set when {@link #result} type is {@link
#RESULT_TYPE_ROW}. */
+ @HopMetadataProperty(groupKey = "result_fields", key = "field")
+ private List<DBProcField> resultFields;
public DBProcMeta() {
super();
this.arguments = new ArrayList<>();
+ this.resultFields = new ArrayList<>();
this.result = new ProcResult();
}
@Override
public void setDefault() {
connection = null;
- result.name = "result";
- result.type = "Number";
+ result.setName("result");
+ result.setType("Number");
autoCommit = true;
}
+ public boolean isResultRows() {
+ return RESULT_TYPE_ROW.equalsIgnoreCase(getResultType());
+ }
+
+ public String getResultName() {
+ return result == null ? null : result.getName();
+ }
+
+ public void setResultName(String name) {
+ ensureResult();
+ result.setName(name);
+ }
+
+ public String getResultType() {
+ return result == null ? null : result.getType();
+ }
+
+ public void setResultType(String type) {
+ ensureResult();
+ result.setType(type);
+ }
+
+ private void ensureResult() {
+ if (result == null) {
+ result = new ProcResult();
+ }
+ }
+
+ public List<String> getResultTypeNames(ILogChannel log, IHopMetadataProvider
metadataProvider) {
+ List<String> names = new ArrayList<>();
+ for (String name : ValueMetaFactory.getValueMetaNames()) {
+ if (!RESULT_TYPE_ROW.equalsIgnoreCase(name)) {
+ names.add(name);
+ }
+ }
+ names.add(RESULT_TYPE_ROW);
+ return names;
+ }
+
+ /**
+ * Row mode cannot read OUT or INOUT values. {@link
+ * org.apache.hop.core.database.Database#callProcedure} reads them before
the result set, and SQL
+ * Server and Sybase then discard the rows.
+ */
+ public boolean hasRowResultOutputArgument() {
+ if (!isResultRows() || arguments == null) {
+ return false;
+ }
+ for (ProcArgument argument : arguments) {
+ if (argument == null || argument.getDirection() == null) {
+ continue;
+ }
+ String direction = argument.getDirection();
+ if (direction.equalsIgnoreCase("OUT") ||
direction.equalsIgnoreCase("INOUT")) {
+ return true;
+ }
+ }
+ return false;
+ }
+
+ /** Result columns with a name. Empty names are not part of the output row.
*/
+ public List<DBProcField> activeResultFields() {
+ List<DBProcField> active = new ArrayList<>();
+ if (resultFields == null) {
+ return active;
+ }
+ for (DBProcField field : resultFields) {
+ if (field != null && !Utils.isEmpty(field.getName())) {
+ active.add(field);
+ }
+ }
+ return active;
+ }
+
@Override
public void getFields(
IRowMeta r,
@@ -98,10 +270,17 @@ public class DBProcMeta extends BaseTransformMeta<DBProc,
DBProcData> {
IHopMetadataProvider metadataProvider)
throws HopTransformException {
- if (!Utils.isEmpty(result.getName())) {
- IValueMeta v;
+ if (isResultRows()) {
+ for (DBProcField field : activeResultFields()) {
+ try {
+ r.addValueMeta(field.toValueMeta(name, variables));
+ } catch (HopPluginException e) {
+ throw new HopTransformException(e);
+ }
+ }
+ } else if (result != null && !Utils.isEmpty(result.getName())) {
try {
- v = ValueMetaFactory.createValueMeta(result.getName(),
result.getHopType());
+ IValueMeta v = ValueMetaFactory.createValueMeta(result.getName(),
result.getHopType());
v.setOrigin(name);
r.addValueMeta(v);
} catch (HopPluginException e) {
@@ -109,11 +288,14 @@ public class DBProcMeta extends BaseTransformMeta<DBProc,
DBProcData> {
}
}
+ if (arguments == null) {
+ return;
+ }
for (ProcArgument argument : arguments) {
- if (argument.getDirection().equalsIgnoreCase("OUT")) {
- IValueMeta v;
+ if (argument.getDirection() != null &&
argument.getDirection().equalsIgnoreCase("OUT")) {
try {
- v = ValueMetaFactory.createValueMeta(argument.getName(),
argument.getHopType());
+ IValueMeta v =
+ ValueMetaFactory.createValueMeta(argument.getName(),
argument.getHopType());
v.setOrigin(name);
r.addValueMeta(v);
} catch (HopPluginException e) {
@@ -138,6 +320,14 @@ public class DBProcMeta extends BaseTransformMeta<DBProc,
DBProcData> {
CheckResult cr;
String errorMessage = "";
+ if (hasRowResultOutputArgument()) {
+ remarks.add(
+ new CheckResult(
+ ICheckResult.TYPE_RESULT_ERROR,
+ BaseMessages.getString(PKG,
"DBProcMeta.CheckResult.RowResultOutputArguments"),
+ transformMeta));
+ }
+
DatabaseMeta databaseMeta = null;
try {
@@ -239,6 +429,9 @@ public class DBProcMeta extends BaseTransformMeta<DBProc,
DBProcData> {
}
public String[] argumentNames() {
+ if (arguments == null) {
+ return new String[0];
+ }
String[] names = new String[arguments.size()];
for (int i = 0; i < names.length; i++) {
names[i] = arguments.get(i).getName();
@@ -247,6 +440,9 @@ public class DBProcMeta extends BaseTransformMeta<DBProc,
DBProcData> {
}
public String[] argumentDirections() {
+ if (arguments == null) {
+ return new String[0];
+ }
String[] directions = new String[arguments.size()];
for (int i = 0; i < directions.length; i++) {
directions[i] = arguments.get(i).getDirection();
@@ -255,6 +451,9 @@ public class DBProcMeta extends BaseTransformMeta<DBProc,
DBProcData> {
}
public int[] argumentTypes() {
+ if (arguments == null) {
+ return new int[0];
+ }
int[] types = new int[arguments.size()];
for (int i = 0; i < types.length; i++) {
types[i] = arguments.get(i).getHopType();
@@ -262,6 +461,8 @@ public class DBProcMeta extends BaseTransformMeta<DBProc,
DBProcData> {
return types;
}
+ @Getter
+ @Setter
public static class ProcArgument {
@HopMetadataProperty private String name;
@HopMetadataProperty private String direction;
@@ -275,65 +476,13 @@ public class DBProcMeta extends BaseTransformMeta<DBProc,
DBProcData> {
this.type = a.type;
}
- /**
- * Gets name
- *
- * @return value of name
- */
- public String getName() {
- return name;
- }
-
- /**
- * Sets name
- *
- * @param name value of name
- */
- public void setName(String name) {
- this.name = name;
- }
-
- /**
- * Gets direction
- *
- * @return value of direction
- */
- public String getDirection() {
- return direction;
- }
-
- /**
- * Sets direction
- *
- * @param direction value of direction
- */
- public void setDirection(String direction) {
- this.direction = direction;
- }
-
- /**
- * Gets type
- *
- * @return value of type
- */
- public String getType() {
- return type;
- }
-
- /**
- * Sets type
- *
- * @param type value of type
- */
- public void setType(String type) {
- this.type = type;
- }
-
public int getHopType() {
return ValueMetaFactory.getIdForValueMeta(type);
}
}
+ @Getter
+ @Setter
public static class ProcResult {
/** function result: new value name */
@HopMetadataProperty private String name;
@@ -348,134 +497,8 @@ public class DBProcMeta extends BaseTransformMeta<DBProc,
DBProcData> {
this.type = r.type;
}
- /**
- * Gets name
- *
- * @return value of name
- */
- public String getName() {
- return name;
- }
-
- /**
- * Sets name
- *
- * @param name value of name
- */
- public void setName(String name) {
- this.name = name;
- }
-
- /**
- * Gets type
- *
- * @return value of type
- */
- public String getType() {
- return type;
- }
-
- /**
- * Sets type
- *
- * @param type value of type
- */
- public void setType(String type) {
- this.type = type;
- }
-
public int getHopType() {
return ValueMetaFactory.getIdForValueMeta(type);
}
}
-
- /**
- * Gets database connection
- *
- * @return value of connection
- */
- public String getConnection() {
- return connection;
- }
-
- /**
- * Sets database connection
- *
- * @param connection value of database connection
- */
- public void setConnection(String connection) {
- this.connection = connection;
- }
-
- /**
- * Gets procedure
- *
- * @return value of procedure
- */
- public String getProcedure() {
- return procedure;
- }
-
- /**
- * Sets procedure
- *
- * @param procedure value of procedure
- */
- public void setProcedure(String procedure) {
- this.procedure = procedure;
- }
-
- /**
- * Gets arguments
- *
- * @return value of arguments
- */
- public List<ProcArgument> getArguments() {
- return arguments;
- }
-
- /**
- * Sets arguments
- *
- * @param arguments value of arguments
- */
- public void setArguments(List<ProcArgument> arguments) {
- this.arguments = arguments;
- }
-
- /**
- * Gets result
- *
- * @return value of result
- */
- public ProcResult getResult() {
- return result;
- }
-
- /**
- * Sets result
- *
- * @param result value of result
- */
- public void setResult(ProcResult result) {
- this.result = result;
- }
-
- /**
- * Gets autoCommit
- *
- * @return value of autoCommit
- */
- public boolean isAutoCommit() {
- return autoCommit;
- }
-
- /**
- * Sets autoCommit
- *
- * @param autoCommit value of autoCommit
- */
- public void setAutoCommit(boolean autoCommit) {
- this.autoCommit = autoCommit;
- }
}
diff --git
a/plugins/transforms/dbproc/src/main/java/org/apache/hop/pipeline/transforms/dbproc/DBProcResultFieldLookup.java
b/plugins/transforms/dbproc/src/main/java/org/apache/hop/pipeline/transforms/dbproc/DBProcResultFieldLookup.java
new file mode 100644
index 0000000000..06e484e1fe
--- /dev/null
+++
b/plugins/transforms/dbproc/src/main/java/org/apache/hop/pipeline/transforms/dbproc/DBProcResultFieldLookup.java
@@ -0,0 +1,65 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.dbproc;
+
+import java.util.function.BooleanSupplier;
+import org.apache.hop.core.exception.HopDatabaseException;
+import org.apache.hop.core.row.IRowMeta;
+
+/**
+ * Decides whether Get fields can use driver metadata or has to run the
procedure. The confirmation
+ * supplier is called only when the driver did not describe the result set.
+ */
+final class DBProcResultFieldLookup {
+ private DBProcResultFieldLookup() {}
+
+ enum Choice {
+ DESCRIBE_WITHOUT_RUNNING,
+ EXECUTE,
+ CANCELLED
+ }
+
+ @FunctionalInterface
+ interface ResultFieldSource {
+ IRowMeta get() throws HopDatabaseException;
+ }
+
+ static Choice choose(boolean describedWithoutRunning, BooleanSupplier
confirmExecution) {
+ if (describedWithoutRunning) {
+ return Choice.DESCRIBE_WITHOUT_RUNNING;
+ }
+ if (confirmExecution != null && confirmExecution.getAsBoolean()) {
+ return Choice.EXECUTE;
+ }
+ return Choice.CANCELLED;
+ }
+
+ /**
+ * @return the columns the driver already described, the columns from {@code
execute}, or {@code
+ * null} when the procedure would have to run and the user declined
+ */
+ static IRowMeta fields(
+ IRowMeta describedWithoutRunning, BooleanSupplier confirmExecution,
ResultFieldSource execute)
+ throws HopDatabaseException {
+ return switch (choose(describedWithoutRunning != null, confirmExecution)) {
+ case DESCRIBE_WITHOUT_RUNNING -> describedWithoutRunning;
+ case CANCELLED -> null;
+ case EXECUTE -> execute.get();
+ };
+ }
+}
diff --git
a/plugins/transforms/dbproc/src/main/resources/org/apache/hop/pipeline/transforms/dbproc/messages/messages_en_US.properties
b/plugins/transforms/dbproc/src/main/resources/org/apache/hop/pipeline/transforms/dbproc/messages/messages_en_US.properties
index 9127ae740f..8af0bb188e 100644
---
a/plugins/transforms/dbproc/src/main/resources/org/apache/hop/pipeline/transforms/dbproc/messages/messages_en_US.properties
+++
b/plugins/transforms/dbproc/src/main/resources/org/apache/hop/pipeline/transforms/dbproc/messages/messages_en_US.properties
@@ -15,10 +15,12 @@
# limitations under the License.
#
-CallDBProcedure.Description=Get back information by calling a database
procedure.
+CallDBProcedure.Description=Call a database procedure and return a value,
output parameters, or result rows.
CallDBProcedure.Name=Call DB procedure
DBProc.ErrorInTransformRunning=Because of an error, this transform can''t
continue:
DBProc.Exception.CouldnotFindField=Couldn''t find field ''{0}'' in row!
+DBProc.Exception.RowResultOutputArguments=Result type Row cannot be combined
with OUT or INOUT arguments. Reading those values before the result set makes
some databases discard the rows.
+DBProc.Exception.UnableToReadResultSet=Unable to read the procedure result set
DBProc.LineNumber=linenr
DBProc.Log.AutoCommit=Auto commit turned off, procedures need to handle commit
and rollback.
DBProc.Log.CloseProcedureError=There was an error closing the stored procedure
:
@@ -29,16 +31,34 @@ DBProc.Log.ErrorFindingField=Error finding field:
DBProcDialog.AutoCommit.Label=Enable auto commit
DBProcDialog.AutoCommit.Tooltip=Enable auto commit on this database
connection.\nWhen the auto commit is off, a commit is done after the last row
is processed.
DBProcDialog.ColumnInfo.Direction=Direction
+DBProcDialog.ColumnInfo.Format=Format
+DBProcDialog.ColumnInfo.Length=Length
DBProcDialog.ColumnInfo.Name=Name
+DBProcDialog.ColumnInfo.Precision=Precision
DBProcDialog.ColumnInfo.Type=Type
+DBProcDialog.ConfirmProcedureExecution.DialogMessage=This runs the procedure
with NULL arguments to find its result columns. Continue?
+DBProcDialog.ConfirmProcedureExecution.DialogTitle=Run procedure?
+DBProcDialog.Connection.Label=Connection
+DBProcDialog.Connection.Tooltip=Database connection that holds the procedure.
DBProcDialog.EnterSelection.DialogMessage=Select a procedure
DBProcDialog.EnterSelection.DialogTitle=Enter choice
DBProcDialog.ErrorGettingProceduresList.DialogMessage=Error getting list of
available procedures
DBProcDialog.ErrorGettingProceduresList.DialogTitle=Error
DBProcDialog.FailedToGetFields.DialogMessage=Unable to get fields from
previous transforms because of an error
DBProcDialog.FailedToGetFields.DialogTitle=Get fields failed
-DBProcDialog.Finding.Button=\ &Find it...
+DBProcDialog.FailedToGetResultFields.DialogMessage=Unable to get the result
fields of the procedure because of an error
+DBProcDialog.FailedToGetResultFields.DialogTitle=Get fields failed
+DBProcDialog.FieldsTab.Tooltip=Columns of the procedure''s first result set.
Available when the result type is Row.
+DBProcDialog.Finding.Button=Find &procedure...
+DBProcDialog.Finding.Tooltip=List the procedures reported by JDBC for this
connection. Some drivers return none; type the procedure name when the list is
empty.
DBProcDialog.GetFields.Button=\ &Get Fields
+DBProcDialog.GetResultFields.Button=Get &fields
+DBProcDialog.GetResultFields.Tooltip=Read the column names and types of the
procedure''s first result set. When the driver cannot describe the result set,
Hop asks before it runs the procedure with null arguments. Hop rolls the
connection back when the driver allows it. A procedure that commits itself can
still change data.
+DBProcDialog.Group.Fields=Fields
+DBProcDialog.Group.General=General
+DBProcDialog.Group.Parameters=Parameters
+DBProcDialog.NoResultSet.DialogMessage=The procedure did not return a result
set.
+DBProcDialog.NoResultSet.DialogTitle=No result set
DBProcDialog.InvalidConnection.DialogMessage=Please select a valid connection\!
DBProcDialog.InvalidConnection.DialogTitle=ERROR
DBProcDialog.Log.FoundArguments=Found {0} arguments
@@ -46,9 +66,12 @@ DBProcDialog.Log.GettingKeyInfo=getting key info...
DBProcDialog.NoProceduresFound.DialogMessage=I couldn''t find any procedures
to select from.\nIf you are certain there is one, try to specify the details
manually.
DBProcDialog.NoProceduresFound.DialogTitle=Sorry
DBProcDialog.Parameters.Label=Parameters:
-DBProcDialog.ProcedureName.Label=Proc-name
+DBProcDialog.ProcedureName.Label=Procedure name
+DBProcDialog.ProcedureName.Tooltip=Name of the procedure or function to call.
A variable is allowed, for example '${PROCEDURE_NAME}'.
DBProcDialog.Result.Label=Result name
+DBProcDialog.Result.Tooltip=Name of the function return value. Leave this
blank when the procedure has no scalar return. Not used when the result type is
Row; the name is kept for when you switch back.
DBProcDialog.ResultType.Label=Result type
+DBProcDialog.ResultType.Tooltip=Type of the function return value. Choose Row
to output the rows of the procedure''s first result set. List those columns on
the Fields tab. Row cannot be combined with OUT or INOUT arguments.
DBProcDialog.Shell.Title=Call DB procedure
DBProcDialog.TransformName.Label=Transform name
DBProcMeta.CheckResult.AllArgumentsOK=All arguments found in the input stream.
@@ -58,6 +81,7 @@ DBProcMeta.CheckResult.InvalidConnection=Please select or
create a connection\!
DBProcMeta.CheckResult.MissingArguments=Missing arguments, not found in input
from previous transforms:
DBProcMeta.CheckResult.NoInpuReceived=No input received from other transforms\!
DBProcMeta.CheckResult.ReceivingInfoFromOtherTransforms=Transform is receiving
info from other transforms.
+DBProcMeta.CheckResult.RowResultOutputArguments=Result type Row cannot be
combined with OUT or INOUT arguments. Reading those values before the result
set makes some databases discard the rows.
DBProcMeta.CheckResult.WrongTypeArguments=\ (found but wrong type: {0} vs. {1})
DBProcMeta.Exception.UnableToReadTransformMeta=Unable to read transform
information from XML
-DBProcMeta.keyword=procedure,storedprocedure,call,function,sql
+DBProcMeta.keyword=procedure,storedprocedure,call,function,sql,resultset,rows
diff --git
a/plugins/transforms/dbproc/src/test/java/org/apache/hop/pipeline/transforms/dbproc/DBProcMetaTest.java
b/plugins/transforms/dbproc/src/test/java/org/apache/hop/pipeline/transforms/dbproc/DBProcMetaTest.java
index 4b24f3a234..b9799cab5c 100644
---
a/plugins/transforms/dbproc/src/test/java/org/apache/hop/pipeline/transforms/dbproc/DBProcMetaTest.java
+++
b/plugins/transforms/dbproc/src/test/java/org/apache/hop/pipeline/transforms/dbproc/DBProcMetaTest.java
@@ -16,16 +16,377 @@
*/
package org.apache.hop.pipeline.transforms.dbproc;
-import org.apache.hop.core.util.Assert;
+import static org.junit.jupiter.api.Assertions.assertDoesNotThrow;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertFalse;
+import static org.junit.jupiter.api.Assertions.assertNotSame;
+import static org.junit.jupiter.api.Assertions.assertNull;
+import static org.junit.jupiter.api.Assertions.assertSame;
+import static org.junit.jupiter.api.Assertions.assertThrows;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+import java.sql.ResultSetMetaData;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.concurrent.atomic.AtomicBoolean;
+import org.apache.hop.core.HopEnvironment;
+import org.apache.hop.core.ICheckResult;
+import org.apache.hop.core.exception.HopTransformException;
+import org.apache.hop.core.row.IRowMeta;
+import org.apache.hop.core.row.IValueMeta;
+import org.apache.hop.core.row.RowDataUtil;
+import org.apache.hop.core.row.RowMeta;
+import org.apache.hop.core.row.value.ValueMetaString;
+import org.apache.hop.core.variables.Variables;
+import org.apache.hop.i18n.BaseMessages;
+import org.apache.hop.junit.rules.RestoreHopEnvironmentExtension;
+import org.apache.hop.metadata.serializer.memory.MemoryMetadataProvider;
+import org.apache.hop.pipeline.transform.TransformMeta;
import org.apache.hop.pipeline.transform.TransformSerializationTestUtil;
+import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.extension.ExtendWith;
+@ExtendWith(RestoreHopEnvironmentExtension.class)
class DBProcMetaTest {
+ @BeforeAll
+ static void setUp() throws Exception {
+ HopEnvironment.init();
+ }
+
@Test
void testSerialization() throws Exception {
DBProcMeta meta =
TransformSerializationTestUtil.testSerialization(
"/db-proc-transform.xml", DBProcMeta.class);
- Assert.assertNotNull(meta.getProcedure());
+
+ assertEquals("addCount", meta.getProcedure());
+ assertEquals("unit-test-db", meta.getConnection());
+ assertEquals("count", meta.getResultName());
+ assertEquals("Integer", meta.getResultType());
+ assertFalse(meta.isResultRows());
+ assertTrue(meta.isAutoCommit());
+ assertEquals(1, meta.getArguments().size());
+ assertEquals("value", meta.getArguments().get(0).getName());
+ assertEquals("IN", meta.getArguments().get(0).getDirection());
+ assertEquals("String", meta.getArguments().get(0).getType());
+ assertTrue(meta.getResultFields() == null ||
meta.getResultFields().isEmpty());
+ }
+
+ @Test
+ void rowResultSerializationKeepsTheScalarNameAndTheFieldList() throws
Exception {
+ DBProcMeta meta =
+ TransformSerializationTestUtil.testSerialization("/db-proc-rows.xml",
DBProcMeta.class);
+
+ assertEquals("list_customers", meta.getProcedure());
+ assertEquals("count", meta.getResultName());
+ assertEquals(DBProcMeta.RESULT_TYPE_ROW, meta.getResultType());
+ assertTrue(meta.isResultRows());
+ assertFalse(meta.isAutoCommit());
+ assertEquals(2, meta.getArguments().size());
+ assertEquals("OUT", meta.getArguments().get(1).getDirection());
+ assertEquals(2, meta.getResultFields().size());
+ assertEquals("id", meta.getResultFields().get(0).getName());
+ assertEquals("Integer", meta.getResultFields().get(0).getType());
+ assertEquals("#", meta.getResultFields().get(0).getFormat());
+ assertEquals(9, meta.getResultFields().get(0).getLength());
+ assertEquals(0, meta.getResultFields().get(0).getPrecision());
+ assertEquals("customer_name", meta.getResultFields().get(1).getName());
+ assertEquals(-1, meta.getResultFields().get(1).getLength());
+ assertEquals(-1, meta.getResultFields().get(1).getPrecision());
+
+ meta.getResult().setType("row");
+ assertTrue(meta.isResultRows());
+ }
+
+ @Test
+ void getFieldsAddsScalarThenOutAndRowColumnsInsteadOfTheScalar() throws
Exception {
+ DBProcMeta meta = new DBProcMeta();
+ meta.setDefault();
+ meta.getResult().setName("count");
+ meta.getResult().setType("Integer");
+ DBProcMeta.ProcArgument out = new DBProcMeta.ProcArgument();
+ out.setName("total");
+ out.setDirection("OUT");
+ out.setType("Number");
+ meta.getArguments().add(out);
+
+ RowMeta scalar = new RowMeta();
+ scalar.addValueMeta(new ValueMetaString("value"));
+ meta.getFields(scalar, "proc", null, null, new Variables(), null);
+
+ assertEquals(3, scalar.size());
+ assertEquals("value", scalar.getValueMeta(0).getName());
+ assertEquals("count", scalar.getValueMeta(1).getName());
+ assertEquals(IValueMeta.TYPE_INTEGER, scalar.getValueMeta(1).getType());
+ assertEquals("total", scalar.getValueMeta(2).getName());
+ assertEquals("proc", scalar.getValueMeta(1).getOrigin());
+
+ meta.setResultType(DBProcMeta.RESULT_TYPE_ROW);
+ DBProcField id = new DBProcField();
+ id.setName("${COL}");
+ id.setType("Integer");
+ id.setLength(9);
+ id.setPrecision(0);
+ id.setFormat("${MASK}");
+ DBProcField blank = new DBProcField();
+ blank.setName("");
+ meta.getResultFields().add(id);
+ meta.getResultFields().add(blank);
+ Variables variables = new Variables();
+ variables.setVariable("COL", "id");
+ variables.setVariable("MASK", "#");
+
+ RowMeta rows = new RowMeta();
+ rows.addValueMeta(new ValueMetaString("value"));
+ meta.getFields(rows, "proc", null, null, variables, null);
+
+ assertEquals(3, rows.size());
+ assertEquals("id", rows.getValueMeta(1).getName());
+ assertEquals(IValueMeta.TYPE_INTEGER, rows.getValueMeta(1).getType());
+ assertEquals(9, rows.getValueMeta(1).getLength());
+ assertEquals(0, rows.getValueMeta(1).getPrecision());
+ assertEquals("#", rows.getValueMeta(1).getConversionMask());
+ assertEquals("total", rows.getValueMeta(2).getName());
+ }
+
+ @Test
+ void unknownResultFieldTypeBecomesString() throws Exception {
+ DBProcField field = new DBProcField();
+ field.setName("value");
+ field.setType("Row");
+ assertEquals(IValueMeta.TYPE_STRING, field.toValueMeta("origin",
null).getType());
+ assertEquals(-1, new DBProcField().getLength());
+ assertEquals(-1, new DBProcField().getPrecision());
+ }
+
+ @Test
+ void resultTypeNamesEndWithASingleRowEntry() {
+ List<String> names = new DBProcMeta().getResultTypeNames(null, null);
+ assertEquals(DBProcMeta.RESULT_TYPE_ROW, names.get(names.size() - 1));
+ assertEquals(1,
names.stream().filter(DBProcMeta.RESULT_TYPE_ROW::equalsIgnoreCase).count());
+ assertFalse(names.get(0).equalsIgnoreCase(DBProcMeta.RESULT_TYPE_ROW));
+ }
+
+ @Test
+ void buildOutputRowKeepsTheScalarPathAndCopiesResultRows() {
+ Object[] row = new Object[] {"in", null, null};
+ DBProcMeta.ProcArgument in = new DBProcMeta.ProcArgument();
+ in.setDirection("IN");
+ DBProcMeta.ProcArgument out = new DBProcMeta.ProcArgument();
+ out.setDirection("out");
+ List<DBProcMeta.ProcArgument> arguments = List.of(in, out);
+ int[] argnrs = new int[] {0, -1};
+
+ Object[] scalar =
+ DBProc.buildOutputRow(row, 1, 3, new Object[] {7L, 42L}, argnrs,
arguments, true, 0, false);
+ assertSame(row, scalar);
+ assertEquals("in", scalar[0]);
+ assertEquals(7L, scalar[1]);
+ assertEquals(42L, scalar[2]);
+
+ Object[] wide = new Object[] {"old"};
+ DBProcMeta.ProcArgument inout = new DBProcMeta.ProcArgument();
+ inout.setDirection("INOUT");
+ Object[] replaced =
+ DBProc.buildOutputRow(
+ wide, 1, 1, new Object[] {"new"}, new int[] {0}, List.of(inout),
false, 0, true);
+ assertNotSame(wide, replaced);
+ assertEquals("new", replaced[0]);
+ assertEquals("old", wide[0]);
+
+ Object[] template =
+ DBProc.buildOutputRow(
+ new Object[] {"in"}, 1, 3, new Object[] {42L}, argnrs, arguments,
false, 1, true);
+ assertNull(template[1]);
+ assertEquals(42L, template[2]);
+ Object[] copy = RowDataUtil.createResizedCopy(template, 3);
+ copy[1] = "row";
+ assertNull(template[1]);
+ assertEquals(42L, copy[2]);
+ }
+
+ @Test
+ void resultColumnsMatchLabelThenNameIgnoringCase() throws Exception {
+ ResultSetMetaData metadata =
org.mockito.Mockito.mock(ResultSetMetaData.class);
+ org.mockito.Mockito.when(metadata.getColumnCount()).thenReturn(3);
+ org.mockito.Mockito.when(metadata.getColumnLabel(1)).thenReturn("ID");
+ org.mockito.Mockito.when(metadata.getColumnName(1)).thenReturn("id_col");
+ org.mockito.Mockito.when(metadata.getColumnLabel(2)).thenReturn("");
+ org.mockito.Mockito.when(metadata.getColumnName(2)).thenReturn("Name");
+ org.mockito.Mockito.when(metadata.getColumnLabel(3)).thenReturn("ID");
+ org.mockito.Mockito.when(metadata.getColumnName(3)).thenReturn("other");
+
+ String[] columns = DBProc.resultColumnNames(metadata);
+ assertEquals("ID", columns[0]);
+ assertEquals("Name", columns[1]);
+
+ DBProcField id = new DBProcField();
+ id.setName("Id");
+ DBProcField missing = new DBProcField();
+ missing.setName("city");
+ DBProcField fromVariable = new DBProcField();
+ fromVariable.setName("${COL}");
+ Variables variables = new Variables();
+ variables.setVariable("COL", "name");
+
+ int[] indexes =
+ DBProc.resultColumnIndexes(columns, List.of(id, missing,
fromVariable), variables);
+ assertEquals(0, indexes[0]);
+ assertEquals(-1, indexes[1]);
+ assertEquals(1, indexes[2]);
+ }
+
+ @Test
+ void checkRejectsOutAndInOutArgumentsWhenTheResultIsRows() {
+ String message =
+ BaseMessages.getString(DBProcMeta.class,
"DBProcMeta.CheckResult.RowResultOutputArguments");
+ assertEquals(
+ "Result type Row cannot be combined with OUT or INOUT arguments.
Reading those values"
+ + " before the result set makes some databases discard the rows.",
+ message);
+
+ DBProcMeta rows = rowMetaWithArgument("total", "OUT");
+ assertTrue(rows.hasRowResultOutputArgument());
+ assertTrue(hasRemark(check(rows), message));
+
+ rows.getArguments().get(0).setDirection("InOut");
+ assertTrue(rows.hasRowResultOutputArgument());
+ assertTrue(hasRemark(check(rows), message));
+
+ rows.getArguments().get(0).setDirection("IN");
+ assertFalse(rows.hasRowResultOutputArgument());
+ assertFalse(hasRemark(check(rows), message));
+
+ DBProcMeta scalar = rowMetaWithArgument("total", "OUT");
+ scalar.setResultType("Integer");
+ assertFalse(scalar.hasRowResultOutputArgument());
+ assertFalse(hasRemark(check(scalar), message));
+
+ DBProcMeta blank = rowMetaWithArgument("total", null);
+ assertFalse(blank.hasRowResultOutputArgument());
+ assertFalse(hasRemark(check(blank), message));
+ }
+
+ @Test
+ void rowModeRejectsOutputArgumentsBeforeTheProcedureRuns() {
+ String message =
+ BaseMessages.getString(DBProcMeta.class,
"DBProc.Exception.RowResultOutputArguments");
+ assertEquals(
+ "Result type Row cannot be combined with OUT or INOUT arguments.
Reading those values"
+ + " before the result set makes some databases discard the rows.",
+ message);
+
+ DBProcMeta rows = rowMetaWithArgument("total", "Out");
+ HopTransformException rejected =
+ assertThrows(
+ HopTransformException.class, () ->
DBProc.rejectRowResultOutputArguments(rows));
+ assertEquals(message, rejected.getSuperMessage());
+
+ rows.getArguments().get(0).setDirection("INOUT");
+ assertThrows(HopTransformException.class, () ->
DBProc.rejectRowResultOutputArguments(rows));
+
+ rows.getArguments().get(0).setDirection("IN");
+ assertDoesNotThrow(() -> DBProc.rejectRowResultOutputArguments(rows));
+
+ rows.getArguments().get(0).setDirection("OUT");
+ rows.setResultType("Number");
+ assertDoesNotThrow(() -> DBProc.rejectRowResultOutputArguments(rows));
+ assertDoesNotThrow(() -> DBProc.rejectRowResultOutputArguments(null));
+ }
+
+ @Test
+ void resultFieldLookupSkipsConfirmationWhenTheDriverDescribedTheResultSet()
throws Exception {
+ IRowMeta described = new RowMeta();
+ described.addValueMeta(new ValueMetaString("id"));
+ AtomicBoolean asked = new AtomicBoolean();
+ AtomicBoolean executed = new AtomicBoolean();
+
+ assertSame(
+ described,
+ DBProcResultFieldLookup.fields(
+ described,
+ () -> {
+ asked.set(true);
+ return false;
+ },
+ () -> {
+ executed.set(true);
+ return new RowMeta();
+ }));
+ assertFalse(asked.get());
+ assertFalse(executed.get());
+ assertEquals(
+ DBProcResultFieldLookup.Choice.DESCRIBE_WITHOUT_RUNNING,
+ DBProcResultFieldLookup.choose(true, () -> true));
+ }
+
+ @Test
+ void resultFieldLookupRunsOnlyAfterTheUserConfirms() throws Exception {
+ AtomicBoolean executed = new AtomicBoolean();
+ IRowMeta fromProcedure = new RowMeta();
+ fromProcedure.addValueMeta(new ValueMetaString("id"));
+
+ assertNull(
+ DBProcResultFieldLookup.fields(
+ null,
+ () -> false,
+ () -> {
+ executed.set(true);
+ return fromProcedure;
+ }));
+ assertFalse(executed.get());
+ assertNull(DBProcResultFieldLookup.fields(null, null, () ->
fromProcedure));
+
+ assertSame(
+ fromProcedure,
+ DBProcResultFieldLookup.fields(
+ null,
+ () -> true,
+ () -> {
+ executed.set(true);
+ return fromProcedure;
+ }));
+ assertTrue(executed.get());
+ assertEquals(
+ DBProcResultFieldLookup.Choice.EXECUTE,
DBProcResultFieldLookup.choose(false, () -> true));
+ assertEquals(
+ DBProcResultFieldLookup.Choice.CANCELLED,
+ DBProcResultFieldLookup.choose(false, () -> false));
+ }
+
+ private static DBProcMeta rowMetaWithArgument(String name, String direction)
{
+ DBProcMeta meta = new DBProcMeta();
+ meta.setResultType(DBProcMeta.RESULT_TYPE_ROW);
+ DBProcMeta.ProcArgument argument = new DBProcMeta.ProcArgument();
+ argument.setName(name);
+ argument.setDirection(direction);
+ argument.setType("Integer");
+ meta.getArguments().add(argument);
+ return meta;
+ }
+
+ private static List<ICheckResult> check(DBProcMeta meta) {
+ List<ICheckResult> remarks = new ArrayList<>();
+ meta.check(
+ remarks,
+ null,
+ new TransformMeta(),
+ new RowMeta(),
+ new String[] {"in"},
+ new String[0],
+ null,
+ new Variables(),
+ new MemoryMetadataProvider());
+ return remarks;
+ }
+
+ private static boolean hasRemark(List<ICheckResult> remarks, String text) {
+ for (ICheckResult remark : remarks) {
+ if (remark.getType() == ICheckResult.TYPE_RESULT_ERROR &&
text.equals(remark.getText())) {
+ return true;
+ }
+ }
+ return false;
}
}
diff --git a/plugins/transforms/dbproc/src/test/resources/db-proc-rows.xml
b/plugins/transforms/dbproc/src/test/resources/db-proc-rows.xml
new file mode 100644
index 0000000000..17b9bab956
--- /dev/null
+++ b/plugins/transforms/dbproc/src/test/resources/db-proc-rows.xml
@@ -0,0 +1,67 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ ~
+ -->
+<transform>
+ <name>Call DB procedure</name>
+ <type>DBProc</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <connection>unit-test-db</connection>
+ <procedure>list_customers</procedure>
+ <lookup>
+ <arg>
+ <name>customer_id</name>
+ <direction>IN</direction>
+ <type>Integer</type>
+ </arg>
+ <arg>
+ <name>status</name>
+ <direction>OUT</direction>
+ <type>String</type>
+ </arg>
+ </lookup>
+ <result>
+ <name>count</name>
+ <type>Row</type>
+ </result>
+ <result_fields>
+ <field>
+ <name>id</name>
+ <type>Integer</type>
+ <format>#</format>
+ <length>9</length>
+ <precision>0</precision>
+ </field>
+ <field>
+ <name>customer_name</name>
+ <type>String</type>
+ </field>
+ </result_fields>
+ <auto_commit>N</auto_commit>
+ <attributes></attributes>
+ <GUI>
+ <xloc>352</xloc>
+ <yloc>96</yloc>
+ </GUI>
+</transform>
