This is an automated email from the ASF dual-hosted git repository.
mattcasters 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 f6e8a6d424 Enhance DatabaseJoin with SQL parameter support (#8339)
f6e8a6d424 is described below
commit f6e8a6d4247a15514d840a215ad5b0c0a6562504
Author: Happy <[email protected]>
AuthorDate: Thu Sep 17 15:42:49 2026 +0800
Enhance DatabaseJoin with SQL parameter support (#8339)
* Enhance DatabaseJoin to support named and positional SQL parameters
* Enhance DatabaseJoin to support runtime output metadata initialization
and improve parameter handling
* Add tests for mixed named and positional SQL parameters in
DatabaseJoinMeta
* Added a new core SQL parser: parseSqlParameterSpec(String) that returns
* Refactor DatabaseJoin to simplify output field name handling and ensure
parameter specification is initialized
* Refactor DatabaseJoinMeta to streamline SQL parameter handling and
enhance metadata discovery
* Add tests for SQL parameter parsing and metadata handling in
DatabaseJoinMeta
* Fix SQL parameter parsing for JSONB operators and ARRAY expressions
- Skip only explicit JSONB operator tokens: `?|`, `?&`, `??`, `??|`,
and `??&`.
- Consume complete operator tokens so `col ?? ?` still counts the trailing
`?` as a bind parameter.
- Stop treating all `[...]` expressions as bracket-quoted identifiers.
- Add dialect-aware bracket handling for SQL Server.
- Preserve PostgreSQL parameter markers in `ARRAY[?]` and `ARRAY[?, ?]`.
- Apply the same parsing behavior to the Database Join path.
- Expand regression coverage for JSONB operators, `LIKE`, `NOT LIKE`,
`BETWEEN`, `SELECT ? FROM dual`, and ARRAY expressions.
* Enhance parseSqlParameterSpec() call in DatabaseJoin to support
bracket-quoted identifiers
* Refactor parseSqlParameterSpec() in DatabaseJoinMeta to support
bracket-quoted identifiers
* Add tests for mixed named and positional placeholders in SQL parameter
parsing
* Refactor SQL parameter tests to improve clarity and coverage for JSONB
operators and bracket-quoted identifiers
* Add resolved parameters panel to DatabaseJoinDialog for improved SQL
parameter handling
---------
Co-authored-by: happy.he <[email protected]>
---
.../org/apache/hop/core/database/Database.java | 262 ++++++++++++--
.../org/apache/hop/core/database/DatabaseTest.java | 66 ++++
.../transforms/databasejoin/DatabaseJoin.java | 71 +++-
.../transforms/databasejoin/DatabaseJoinData.java | 6 +
.../databasejoin/DatabaseJoinDialog.java | 205 ++++++++++-
.../transforms/databasejoin/DatabaseJoinMeta.java | 392 +++++++++++++++++----
.../messages/messages_en_US.properties | 6 +
.../databasejoin/DatabaseJoinMetaTest.java | 163 +++++++--
8 files changed, 1046 insertions(+), 125 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 bc44cf0e33..c8cd1fe566 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
@@ -3582,32 +3582,254 @@ public class Database implements IVariables,
ILoggingObject, AutoCloseable {
return (sqlType == Types.NUMERIC || sqlType == Types.DECIMAL) && precision
<= 0 && scale <= 0;
}
- public int countParameters(String sql) {
- int q = 0;
- boolean quoteOpened = false;
- boolean dquoteOpened = false;
+ /** Parsed SQL parameter information: prepared SQL plus ordered parameter
references. */
+ public static final class SqlParameterSpec {
+ private final String preparedSql;
+ private final List<String> parameterReferences;
- for (int x = 0; x < sql.length(); x++) {
- char c = sql.charAt(x);
+ public SqlParameterSpec(String preparedSql, List<String>
parameterReferences) {
+ this.preparedSql = preparedSql;
+ this.parameterReferences = Collections.unmodifiableList(new
ArrayList<>(parameterReferences));
+ }
- switch (c) {
- case '\'':
- quoteOpened = !quoteOpened;
- break;
- case '"':
- dquoteOpened = !dquoteOpened;
- break;
- case '?':
- if (!quoteOpened && !dquoteOpened) {
- q++;
+ public String getPreparedSql() {
+ return preparedSql;
+ }
+
+ /** Ordered references: named parameter field name or null for positional
'?'. */
+ public List<String> getParameterReferences() {
+ return parameterReferences;
+ }
+
+ public int getParameterCount() {
+ return parameterReferences.size();
+ }
+ }
+
+ /**
+ * Parse SQL for JDBC parameter markers and ?{name} placeholders.
+ *
+ * <p>The parser skips SQL text within literals, quoted identifiers and
comments. PostgreSQL JSONB
+ * operators (?|, ?&, ??, ??|, ??&) are not treated as parameters.
+ */
+ public static SqlParameterSpec parseSqlParameterSpec(String sourceSql) {
+ return parseSqlParameterSpec(sourceSql, false);
+ }
+
+ /**
+ * Parse SQL for JDBC parameter markers and ?{name} placeholders.
+ *
+ * <p>The parser skips SQL text within literals, quoted identifiers and
comments. PostgreSQL JSONB
+ * operators (?|, ?&, ??, ??|, ??&) are not treated as parameters.
+ *
+ * @param sourceSql the SQL text to parse
+ * @param consumeBracketIdentifiers true to treat [identifier] sections as
quoted identifiers
+ */
+ public static SqlParameterSpec parseSqlParameterSpec(
+ String sourceSql, boolean consumeBracketIdentifiers) {
+ String sql = Const.NVL(sourceSql, "");
+ StringBuilder preparedSql = new StringBuilder(sql.length());
+ List<String> parameterReferences = new ArrayList<>();
+
+ int index = 0;
+ while (index < sql.length()) {
+ char c = sql.charAt(index);
+
+ if (c == '-' && index + 1 < sql.length() && sql.charAt(index + 1) ==
'-') {
+ int end = index + 2;
+ while (end < sql.length()) {
+ char current = sql.charAt(end);
+ if (current == '\n' || current == '\r') {
+ break;
}
- break;
- default:
- break;
+ end++;
+ }
+ preparedSql.append(sql, index, end);
+ index = end;
+ continue;
+ }
+
+ if (c == '/' && index + 1 < sql.length() && sql.charAt(index + 1) ==
'*') {
+ int end = index + 2;
+ boolean closed = false;
+ while (end + 1 < sql.length()) {
+ if (sql.charAt(end) == '*' && sql.charAt(end + 1) == '/') {
+ end += 2;
+ closed = true;
+ break;
+ }
+ end++;
+ }
+ if (!closed) {
+ end = sql.length();
+ }
+ preparedSql.append(sql, index, end);
+ index = end;
+ continue;
+ }
+
+ if (c == '\'') {
+ int end = consumeQuotedSection(sql, index, '\'', true);
+ preparedSql.append(sql, index, end);
+ index = end;
+ continue;
+ }
+
+ if (c == '"') {
+ int end = consumeQuotedSection(sql, index, '"', true);
+ preparedSql.append(sql, index, end);
+ index = end;
+ continue;
+ }
+
+ if (c == '`') {
+ int end = consumeQuotedSection(sql, index, '`', true);
+ preparedSql.append(sql, index, end);
+ index = end;
+ continue;
+ }
+
+ if (consumeBracketIdentifiers && c == '[') {
+ int end = consumeBracketIdentifier(sql, index);
+ preparedSql.append(sql, index, end);
+ index = end;
+ continue;
+ }
+
+ if (c == '$') {
+ String tag = getDollarQuoteTag(sql, index);
+ if (tag != null) {
+ int end = sql.indexOf(tag, index + tag.length());
+ if (end < 0) {
+ preparedSql.append(sql, index, sql.length());
+ break;
+ }
+ int closeEnd = end + tag.length();
+ preparedSql.append(sql, index, closeEnd);
+ index = closeEnd;
+ continue;
+ }
}
+
+ if (c == '?') {
+ if (index + 1 < sql.length() && sql.charAt(index + 1) == '{') {
+ int end = sql.indexOf('}', index + 2);
+ if (end > index + 2) {
+ String fieldName = sql.substring(index + 2, end).trim();
+ if (!fieldName.isEmpty()) {
+ parameterReferences.add(fieldName);
+ preparedSql.append('?');
+ index = end + 1;
+ continue;
+ }
+ }
+ }
+
+ int jsonbOperatorLength = getPostgresJsonbOperatorLength(sql, index);
+ if (jsonbOperatorLength > 0) {
+ preparedSql.append(sql, index, index + jsonbOperatorLength);
+ index += jsonbOperatorLength;
+ continue;
+ }
+
+ parameterReferences.add(null);
+ preparedSql.append(c);
+ index++;
+ continue;
+ }
+
+ preparedSql.append(c);
+ index++;
+ }
+
+ return new SqlParameterSpec(preparedSql.toString(), parameterReferences);
+ }
+
+ private static int consumeQuotedSection(
+ String sql, int startIndex, char quoteCharacter, boolean
allowDoubledQuoteEscape) {
+ int index = startIndex + 1;
+ while (index < sql.length()) {
+ if (sql.charAt(index) == quoteCharacter) {
+ if (allowDoubledQuoteEscape
+ && index + 1 < sql.length()
+ && sql.charAt(index + 1) == quoteCharacter) {
+ index += 2;
+ continue;
+ }
+ return index + 1;
+ }
+ index++;
}
+ return sql.length();
+ }
- return q;
+ private static int consumeBracketIdentifier(String sql, int startIndex) {
+ int index = startIndex + 1;
+ while (index < sql.length()) {
+ if (sql.charAt(index) == ']') {
+ if (index + 1 < sql.length() && sql.charAt(index + 1) == ']') {
+ index += 2;
+ continue;
+ }
+ return index + 1;
+ }
+ index++;
+ }
+ return sql.length();
+ }
+
+ private static String getDollarQuoteTag(String sql, int startIndex) {
+ if (sql.charAt(startIndex) != '$') {
+ return null;
+ }
+ int cursor = startIndex + 1;
+ while (cursor < sql.length()) {
+ char current = sql.charAt(cursor);
+ if (current == '$') {
+ return sql.substring(startIndex, cursor + 1);
+ }
+ if (!(Character.isLetterOrDigit(current) || current == '_')) {
+ return null;
+ }
+ cursor++;
+ }
+ return null;
+ }
+
+ /**
+ * Keep PostgreSQL JSONB operators (?|, ?&, ??, ??|, ??&) from being parsed
as JDBC parameters.
+ */
+ private static int getPostgresJsonbOperatorLength(String sql, int
questionMarkIndex) {
+ int length = sql.length();
+ if (questionMarkIndex + 2 < length
+ && sql.charAt(questionMarkIndex) == '?'
+ && sql.charAt(questionMarkIndex + 1) == '?'
+ && (sql.charAt(questionMarkIndex + 2) == '|' ||
sql.charAt(questionMarkIndex + 2) == '&')) {
+ return 3;
+ }
+
+ if (questionMarkIndex + 1 < length && sql.charAt(questionMarkIndex) ==
'?') {
+ char next = sql.charAt(questionMarkIndex + 1);
+ if (next == '?') {
+ return 2;
+ }
+ if (next == '|' || next == '&') {
+ return 2;
+ }
+ }
+ return 0;
+ }
+
+ public int countParameters(String sql) {
+ return parseSqlParameterSpec(sql,
supportsBracketQuotedIdentifiers()).getParameterCount();
+ }
+
+ private boolean supportsBracketQuotedIdentifiers() {
+ if (databaseMeta == null || databaseMeta.getIDatabase() == null) {
+ return false;
+ }
+ return "[".equals(databaseMeta.getIDatabase().getStartQuote())
+ && "]".equals(databaseMeta.getIDatabase().getEndQuote());
}
// Get the fields back from an SQL query
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 3ec4b3d3ce..9d7fab271f 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
@@ -181,6 +181,72 @@ class DatabaseTest {
assertTrue(rowMeta.getValueMeta(0).isInteger());
}
+ @Test
+ void parseSqlParameterSpecSkipsOnlyExplicitJsonbOperatorQuestionMarks() {
+ String sql =
+ "SELECT \"?identifier\", $$ ? $$ FROM t /* ? block */ "
+ + "WHERE payload ? 'key' AND options ?| array['a'] AND flags ?&
array['b'] "
+ + "AND route = ?{route} -- ? line\nAND status = ?";
+
+ Database.SqlParameterSpec spec = Database.parseSqlParameterSpec(sql);
+
+ assertEquals(
+ "SELECT \"?identifier\", $$ ? $$ FROM t /* ? block */ "
+ + "WHERE payload ? 'key' AND options ?| array['a'] AND flags ?&
array['b'] "
+ + "AND route = ? -- ? line\nAND status = ?",
+ spec.getPreparedSql());
+ assertEquals(3, spec.getParameterCount());
+ assertNull(spec.getParameterReferences().get(0));
+ assertEquals("route", spec.getParameterReferences().get(1));
+ assertNull(spec.getParameterReferences().get(2));
+ }
+
+ @Test
+ void countParametersTreatsArrayConstructorsAsPositionalParameters() {
+ Database db = new Database(log, variables, meta);
+ String sql = "SELECT 1 WHERE x = ANY(ARRAY[?]) AND y = ARRAY[?, ?]";
+
+ assertEquals(3, db.countParameters(sql));
+ }
+
+ @Test
+ void countParametersUsesBracketIdentifierRulesForBracketQuotingDialects() {
+ when(meta.getIDatabase()).thenReturn(iDatabase);
+ when(iDatabase.getStartQuote()).thenReturn("[");
+ when(iDatabase.getEndQuote()).thenReturn("]");
+
+ Database db = new Database(log, variables, meta);
+ String sql =
+ "SELECT [a?b], `c?d`, \"e?f\" FROM t WHERE payload ? ? AND note = '?z'
"
+ + "AND id = ? -- ? comment\nAND type = ?";
+
+ assertEquals(4, db.countParameters(sql));
+ }
+
+ @Test
+ void countParametersTreatsCommonJdbcQuestionMarksAsParameters() {
+ Database db = new Database(log, variables, meta);
+
+ assertEquals(2, db.countParameters("WHERE name LIKE ? AND status = ?"));
+ assertEquals(2, db.countParameters("WHERE val BETWEEN ? AND ?"));
+ assertEquals(1, db.countParameters("SELECT ? FROM dual"));
+ assertEquals(1, db.countParameters("WHERE col NOT LIKE ? OR active =
true"));
+ }
+
+ @Test
+ void parseSqlParameterSpecHandlesEscapedQuestionJsonbOperators() {
+ String sql =
+ "SELECT * FROM t WHERE col ?? ? "
+ + "AND options ?| array['a'] "
+ + "AND flags ??& array['b']";
+
+ Database.SqlParameterSpec spec = Database.parseSqlParameterSpec(sql);
+
+ assertEquals(sql, spec.getPreparedSql());
+ assertEquals(1, spec.getParameterCount());
+ assertNull(spec.getParameterReferences().get(0));
+ }
+
/**
* When using getLookup calls there is no need to make attempt to retrieve
row set metadata for
* every call. That may bring performance penalty depends on jdbc driver
implementation. For some
diff --git
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoin.java
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoin.java
index aa0c89f2c7..7db91d2ac7 100644
---
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoin.java
+++
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoin.java
@@ -27,6 +27,7 @@ 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;
@@ -47,6 +48,35 @@ public class DatabaseJoin extends
BaseTransform<DatabaseJoinMeta, DatabaseJoinDa
private final ReentrantLock dbLock = new ReentrantLock();
+ /**
+ * Appends runtime output metadata to the given DatabaseJoinData object.
+ *
+ * @param data The DatabaseJoinData object to update.
+ * @param addMeta The additional metadata to append.
+ */
+ private void appendRuntimeOutputMetadata(DatabaseJoinData data, IRowMeta
addMeta) {
+ if (data.outputMetadataInitialized || addMeta == null) {
+ return;
+ }
+
+ for (int i = 0; i < addMeta.size(); i++) {
+ IValueMeta valueMeta = addMeta.getValueMeta(i).clone();
+ String valueName = valueMeta.getName();
+ if (valueName == null || valueName.trim().isEmpty()) {
+ valueName = "field" + (i + 1);
+ }
+ valueMeta.setName(valueName);
+ valueMeta.setOrigin(getTransformName());
+ data.outputRowMeta.addValueMeta(valueMeta);
+ }
+ data.outputMetadataInitialized = true;
+
+ if (isDetailed()) {
+ logDetailed(
+ "Initialized Database Join runtime output metadata: " +
data.outputRowMeta.toString());
+ }
+ }
+
public DatabaseJoin(
TransformMeta transformMeta,
DatabaseJoinMeta meta,
@@ -74,6 +104,9 @@ public class DatabaseJoin extends
BaseTransform<DatabaseJoinMeta, DatabaseJoinDa
null,
this,
metadataProvider);
+ // mark whether getFields populated runtime output meta (for stored
procs it may defer to
+ // runtime)
+ data.outputMetadataInitialized = data.outputRowMeta.size() >
rowMeta.size();
data.lookupRowMeta = new RowMeta();
@@ -83,14 +116,26 @@ public class DatabaseJoin extends
BaseTransform<DatabaseJoinMeta, DatabaseJoinDa
+ rowMeta.getString(rowData));
}
- data.keynrs = new int[meta.getParameters().size()];
+ DatabaseJoinMeta.SqlParameterSpec parameterSpec = data.parameterSpec;
+ if (parameterSpec == null) {
+ throw new HopTransformException(
+ "Database Join SQL parameter specification is not initialized.");
+ }
+ data.keynrs = new int[parameterSpec.getParameterCount()];
+
+ int positionalIndex = 0;
+ for (int i = 0; i < parameterSpec.getParameterCount(); i++) {
+ String parameterReference =
parameterSpec.getParameterReferences().get(i);
+ String sourceFieldName = parameterReference;
+ if (sourceFieldName == null) {
+ sourceFieldName =
meta.getPositionalParameterFieldName(positionalIndex);
+ positionalIndex++;
+ }
- for (int i = 0; i < data.keynrs.length; i++) {
- ParameterField field = meta.getParameters().get(i);
- data.keynrs[i] = rowMeta.indexOfValue(field.getName());
+ data.keynrs[i] = rowMeta.indexOfValue(sourceFieldName);
if (data.keynrs[i] < 0) {
throw new HopTransformException(
- BaseMessages.getString(PKG,
"DatabaseJoin.Exception.FieldNotFound", field.getName()));
+ BaseMessages.getString(PKG,
"DatabaseJoin.Exception.FieldNotFound", sourceFieldName));
}
data.lookupRowMeta.addValueMeta(rowMeta.getValueMeta(data.keynrs[i]).clone());
@@ -107,6 +152,10 @@ public class DatabaseJoin extends
BaseTransform<DatabaseJoinMeta, DatabaseJoinDa
IRowMeta addMeta = data.db.getReturnRowMeta();
+ // If we couldn't determine output fields at design time, derive them
from the runtime
+ // resultset
+ appendRuntimeOutputMetadata(data, addMeta);
+
int counter = 0;
for (Object[] add : adds) {
if (add != null && (meta.getRowLimit() == 0 || counter <
meta.getRowLimit())) {
@@ -277,10 +326,18 @@ public class DatabaseJoin extends
BaseTransform<DatabaseJoinMeta, DatabaseJoinDa
if (meta.isReplaceVariables()) {
sql = resolve(sql);
}
+
+ // Parse SQL parameter spec (supports ?{name}) and prepare statement
with prepared SQL
+ DatabaseJoinMeta.SqlParameterSpec parameterSpec =
+ DatabaseJoinMeta.parseSqlParameterSpec(
+ sql,
DatabaseJoinMeta.supportsBracketQuotedIdentifiers(databaseMeta));
+ data.parameterSpec = parameterSpec;
+ String preparedSql = parameterSpec.getPreparedSql();
+
// Prepare the SQL statement
- data.pstmt = data.db.prepareSql(sql);
+ data.pstmt = data.db.prepareSql(preparedSql);
if (isDebug()) {
- logDebug(BaseMessages.getString(PKG,
"DatabaseJoin.Log.SQLStatement", sql));
+ logDebug(BaseMessages.getString(PKG,
"DatabaseJoin.Log.SQLStatement", preparedSql));
}
data.db.setQueryLimit(meta.getRowLimit());
diff --git
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinData.java
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinData.java
index 52fcfec614..7e2c5a481b 100644
---
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinData.java
+++
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinData.java
@@ -33,7 +33,13 @@ public class DatabaseJoinData extends BaseTransformData
implements ITransformDat
IRowMeta outputRowMeta;
IRowMeta lookupRowMeta;
+ // If SQL contains named and/or positional parameters, this spec describes
them
+ public DatabaseJoinMeta.SqlParameterSpec parameterSpec;
+
public int[] keynrs; // parameter value index in an input row...
public Object[] notfound; // Values in case nothing is found...
public boolean isCanceled;
+
+ // The output metadata has been initialized.
+ public boolean outputMetadataInitialized;
}
diff --git
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinDialog.java
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinDialog.java
index 32d783a079..c655466284 100644
---
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinDialog.java
+++
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinDialog.java
@@ -20,7 +20,9 @@ package org.apache.hop.pipeline.transforms.databasejoin;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Arrays;
+import java.util.LinkedHashMap;
import java.util.List;
+import java.util.Map;
import org.apache.hop.core.Const;
import org.apache.hop.core.Props;
import org.apache.hop.core.database.DatabaseMeta;
@@ -81,16 +83,20 @@ public class DatabaseJoinDialog extends BaseTransformDialog
{
private Button wOuter;
private TableView wParam;
+ private TableView wResolvedParam;
private Button wUseVars;
private final DatabaseJoinMeta input;
private Label wlPosition;
+ private Label wlParam;
private ColumnInfo[] ciKey;
+ private ColumnInfo[] ciResolvedParam;
private final List<String> inputFields = new ArrayList<>();
+ private IRowMeta sourceFieldsMeta;
private Button wCache;
@@ -117,7 +123,12 @@ public class DatabaseJoinDialog extends
BaseTransformDialog {
// Connection line
wConnection = addConnectionLine(shell, wSpacer, input.getConnection(),
lsMod);
- wConnection.addListener(SWT.Selection, e -> getSqlReservedWords());
+ wConnection.addListener(
+ SWT.Selection,
+ e -> {
+ getSqlReservedWords();
+ refreshResolvedParametersPanel();
+ });
// ICache?
Label wlCache = new Label(shell, SWT.RIGHT);
@@ -228,10 +239,12 @@ public class DatabaseJoinDialog extends
BaseTransformDialog {
wSql.addLineStyleListener(getSqlReservedWords());
PropsUi.setLook(wSql, Props.WIDGET_STYLE_FIXED);
wSql.addModifyListener(lsMod);
+ wSql.addModifyListener(e -> refreshResolvedParametersPanel());
wSqlFromFile.addModifyListener(
e -> {
if (Utils.isEmpty(wSqlFromFile.getText())) {
wSql.setEditable(true);
+ refreshResolvedParametersPanel();
}
});
FormData fdSql = new FormData();
@@ -293,6 +306,53 @@ public class DatabaseJoinDialog extends
BaseTransformDialog {
fdlPosition.right = new FormAttachment(100, 0);
wlPosition.setLayoutData(fdlPosition);
+ Label wlResolvedParam = new Label(shell, SWT.NONE);
+ wlResolvedParam.setText(
+ BaseMessages.getString(PKG,
"DatabaseJoinDialog.ResolvedParameters.Label"));
+ PropsUi.setLook(wlResolvedParam);
+ FormData fdlResolvedParam = new FormData();
+ fdlResolvedParam.left = new FormAttachment(0, 0);
+ fdlResolvedParam.top = new FormAttachment(wlPosition, margin);
+ wlResolvedParam.setLayoutData(fdlResolvedParam);
+
+ ciResolvedParam = new ColumnInfo[3];
+ ciResolvedParam[0] =
+ new ColumnInfo(
+ BaseMessages.getString(PKG,
"DatabaseJoinDialog.ColumnInfo.ResolvedPlaceholder"),
+ ColumnInfo.COLUMN_TYPE_TEXT,
+ false);
+ ciResolvedParam[1] =
+ new ColumnInfo(
+ BaseMessages.getString(PKG,
"DatabaseJoinDialog.ColumnInfo.ResolvedInputField"),
+ ColumnInfo.COLUMN_TYPE_TEXT,
+ false);
+ ciResolvedParam[2] =
+ new ColumnInfo(
+ BaseMessages.getString(PKG,
"DatabaseJoinDialog.ColumnInfo.ResolvedType"),
+ ColumnInfo.COLUMN_TYPE_TEXT,
+ false);
+
+ wResolvedParam =
+ new TableView(
+ variables,
+ shell,
+ SWT.BORDER | SWT.FULL_SELECTION | SWT.MULTI | SWT.V_SCROLL |
SWT.H_SCROLL,
+ ciResolvedParam,
+ 1,
+ true,
+ e -> {},
+ props,
+ true,
+ null,
+ false,
+ false);
+ FormData fdResolvedParam = new FormData();
+ fdResolvedParam.left = new FormAttachment(0, 0);
+ fdResolvedParam.top = new FormAttachment(wlResolvedParam, margin);
+ fdResolvedParam.right = new FormAttachment(100, 0);
+ fdResolvedParam.height = (int) (90 * props.getZoomFactor());
+ wResolvedParam.setLayoutData(fdResolvedParam);
+
// Limit the number of lines returns
Label wlLimit = new Label(shell, SWT.RIGHT);
wlLimit.setText(BaseMessages.getString(PKG,
"DatabaseJoinDialog.Limit.Label"));
@@ -300,7 +360,7 @@ public class DatabaseJoinDialog extends BaseTransformDialog
{
FormData fdlLimit = new FormData();
fdlLimit.left = new FormAttachment(0, 0);
fdlLimit.right = new FormAttachment(middle, -margin);
- fdlLimit.top = new FormAttachment(wlPosition, margin);
+ fdlLimit.top = new FormAttachment(wResolvedParam, margin);
wlLimit.setLayoutData(fdlLimit);
wLimit = new Text(shell, SWT.SINGLE | SWT.LEFT | SWT.BORDER);
PropsUi.setLook(wLimit);
@@ -308,7 +368,7 @@ public class DatabaseJoinDialog extends BaseTransformDialog
{
FormData fdLimit = new FormData();
fdLimit.left = new FormAttachment(middle, 0);
fdLimit.right = new FormAttachment(100, 0);
- fdLimit.top = new FormAttachment(wlPosition, margin);
+ fdLimit.top = new FormAttachment(wResolvedParam, margin);
wLimit.setLayoutData(fdLimit);
// Outer join?
@@ -363,7 +423,7 @@ public class DatabaseJoinDialog extends BaseTransformDialog
{
// THE BUTTONS
// The parameters
- Label wlParam = new Label(shell, SWT.NONE);
+ wlParam = new Label(shell, SWT.NONE);
wlParam.setText(BaseMessages.getString(PKG,
"DatabaseJoinDialog.Param.Label"));
PropsUi.setLook(wlParam);
FormData fdlParam = new FormData();
@@ -387,6 +447,12 @@ public class DatabaseJoinDialog extends
BaseTransformDialog {
ColumnInfo.COLUMN_TYPE_CCOMBO,
ValueMetaFactory.getValueMetaNames());
+ ModifyListener lsParamMod =
+ e -> {
+ input.setChanged();
+ refreshResolvedParametersPanel();
+ };
+
wParam =
new TableView(
variables,
@@ -394,7 +460,7 @@ public class DatabaseJoinDialog extends BaseTransformDialog
{
SWT.BORDER | SWT.FULL_SELECTION | SWT.MULTI | SWT.V_SCROLL |
SWT.H_SCROLL,
ciKey,
nrKeyRows,
- lsMod,
+ lsParamMod,
props);
FormData fdParam = new FormData();
@@ -415,10 +481,14 @@ public class DatabaseJoinDialog extends
BaseTransformDialog {
IRowMeta row = pipelineMeta.getPrevTransformFields(variables,
transformMeta);
// Remember these fields...
+ sourceFieldsMeta = row;
for (int i = 0; i < row.size(); i++) {
inputFields.add(row.getValueMeta(i).getName());
}
setComboBoxes();
+ if (!shell.isDisposed()) {
+ shell.getDisplay().asyncExec(() ->
refreshResolvedParametersPanel());
+ }
} catch (HopException e) {
logError(BaseMessages.getString(PKG,
"System.Dialog.GetFieldsFailed.Message"));
}
@@ -471,6 +541,114 @@ public class DatabaseJoinDialog extends
BaseTransformDialog {
PKG, "DatabaseJoinDialog.Position.Label", "" + lineNumber, "" +
columnNumber));
}
+ private DatabaseJoinMeta.SqlParameterSpec parseCurrentSqlParameterSpec() {
+ String sourceSql = wSql == null ? null : wSql.getText();
+ return DatabaseJoinMeta.parseSqlParameterSpec(
+ sourceSql,
+ DatabaseJoinMeta.supportsBracketQuotedIdentifiers(
+ pipelineMeta.findDatabase(wConnection.getText(), variables)));
+ }
+
+ private List<ParameterField> getDeclaredParametersFromGrid() {
+ List<ParameterField> parameters = new ArrayList<>();
+ if (wParam == null) {
+ return parameters;
+ }
+
+ int nrparam = wParam.nrNonEmpty();
+ for (int i = 0; i < nrparam; i++) {
+ TableItem item = wParam.getNonEmpty(i);
+ ParameterField field = new ParameterField();
+ field.setName(item.getText(1));
+ field.setType(ValueMetaFactory.getIdForValueMeta(item.getText(2)));
+ parameters.add(field);
+ }
+ return parameters;
+ }
+
+ private void refreshResolvedParametersPanel() {
+ if (wResolvedParam == null || wResolvedParam.isDisposed()) {
+ return;
+ }
+
+ DatabaseJoinMeta.SqlParameterSpec parameterSpec =
parseCurrentSqlParameterSpec();
+ List<ParameterField> declaredParameters = getDeclaredParametersFromGrid();
+ Map<String, String> declaredTypesByName = new LinkedHashMap<>();
+ for (ParameterField parameter : declaredParameters) {
+ if (!Utils.isEmpty(parameter.getName())) {
+ declaredTypesByName.put(parameter.getName(), parameter.getType());
+ }
+ }
+
+ wResolvedParam.table.removeAll();
+ int positionalIndex = 0;
+ for (String parameterReference : parameterSpec.getParameterReferences()) {
+ boolean positional = parameterReference == null;
+ String placeholder = positional ? "?" : "?{" + parameterReference + "}";
+
+ String inputFieldName = parameterReference;
+ String declaredType = positional ? null :
declaredTypesByName.get(parameterReference);
+ if (positional) {
+ if (positionalIndex < declaredParameters.size()) {
+ ParameterField declared = declaredParameters.get(positionalIndex);
+ inputFieldName = declared.getName();
+ declaredType = declared.getType();
+ } else {
+ inputFieldName = null;
+ }
+ positionalIndex++;
+ }
+
+ boolean foundInInput =
+ !Utils.isEmpty(inputFieldName)
+ && ((sourceFieldsMeta != null &&
sourceFieldsMeta.indexOfValue(inputFieldName) >= 0)
+ || inputFields.contains(inputFieldName));
+
+ String inputFieldDisplay;
+ if (Utils.isEmpty(inputFieldName)) {
+ inputFieldDisplay =
+ BaseMessages.getString(PKG,
"DatabaseJoinDialog.ResolvedParameters.Unmapped");
+ } else if (foundInInput) {
+ inputFieldDisplay = inputFieldName;
+ } else {
+ inputFieldDisplay =
+ inputFieldName
+ + " ("
+ + BaseMessages.getString(PKG,
"DatabaseJoinDialog.ResolvedParameters.NotFound")
+ + ")";
+ }
+
+ String typeDisplay = declaredType;
+ if (foundInInput && sourceFieldsMeta != null) {
+ int sourceIndex = sourceFieldsMeta.indexOfValue(inputFieldName);
+ if (sourceIndex >= 0) {
+ typeDisplay =
+ ValueMetaFactory.getValueMetaName(
+ sourceFieldsMeta.getValueMeta(sourceIndex).getType());
+ }
+ }
+ if (typeDisplay == null) {
+ typeDisplay = "";
+ }
+
+ wResolvedParam.add(placeholder, inputFieldDisplay, typeDisplay);
+ }
+
+ if (wResolvedParam.table.getItemCount() == 0) {
+ new TableItem(wResolvedParam.table, SWT.NONE);
+ }
+ wResolvedParam.setRowNums();
+ wResolvedParam.optWidth(true);
+
+ boolean hasPositionalParameters =
parameterSpec.getPositionalParameterCount() > 0;
+ if (wParam != null && !wParam.isDisposed()) {
+ wParam.setEnabled(hasPositionalParameters);
+ }
+ if (wlParam != null && !wlParam.isDisposed()) {
+ wlParam.setEnabled(hasPositionalParameters);
+ }
+ }
+
private void loadSqlFromFileAndSetReadOnly() {
String path = variables.resolve(wSqlFromFile.getText());
if (Utils.isEmpty(path)) {
@@ -481,6 +659,7 @@ public class DatabaseJoinDialog extends BaseTransformDialog
{
String content = HopVfs.getTextFileContent(path, StandardCharsets.UTF_8);
wSql.setText(content);
wSql.setEditable(false);
+ refreshResolvedParametersPanel();
} catch (HopFileException e) {
MessageBox mb = new MessageBox(shell, SWT.OK | SWT.ICON_WARNING);
mb.setText(BaseMessages.getString(PKG,
"DatabaseJoinDialog.InvalidConnection.DialogTitle"));
@@ -490,6 +669,7 @@ public class DatabaseJoinDialog extends BaseTransformDialog
{
+ e.getMessage());
mb.open();
wSql.setEditable(true);
+ refreshResolvedParametersPanel();
}
}
@@ -525,6 +705,7 @@ public class DatabaseJoinDialog extends BaseTransformDialog
{
wParam.setRowNums();
wParam.optWidth(true);
+ refreshResolvedParametersPanel();
enableFields();
}
@@ -540,8 +721,6 @@ public class DatabaseJoinDialog extends BaseTransformDialog
{
return;
}
- int nrparam = wParam.nrNonEmpty();
-
input.setConnection(wConnection.getText());
input.setCached(wCache.getSelection());
input.setCacheSize(Const.toInt(wCacheSize.getText(), 0));
@@ -550,19 +729,11 @@ public class DatabaseJoinDialog extends
BaseTransformDialog {
input.setSqlFromFile(wSqlFromFile.getText());
input.setOuterJoin(wOuter.getSelection());
input.setReplaceVariables(wUseVars.getSelection());
+ List<ParameterField> parameters = getDeclaredParametersFromGrid();
logDebug(
BaseMessages.getString(PKG, "DatabaseJoinDialog.Log.ParametersFound")
- + nrparam
+ + parameters.size()
+ " parameters");
-
- List<ParameterField> parameters = new ArrayList<>();
- for (int i = 0; i < nrparam; i++) {
- TableItem item = wParam.getNonEmpty(i);
- ParameterField field = new ParameterField();
- field.setName(item.getText(1));
- field.setType(ValueMetaFactory.getIdForValueMeta(item.getText(2)));
- parameters.add(field);
- }
input.setParameters(parameters);
transformName = wTransformName.getText(); // return value
diff --git
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinMeta.java
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinMeta.java
index 32cebec8a4..7499620a57 100644
---
a/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinMeta.java
+++
b/plugins/transforms/databasejoin/src/main/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinMeta.java
@@ -161,21 +161,235 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
parameters = new ArrayList<>();
}
- public IRowMeta getParameterRow(IRowMeta fields) {
- IRowMeta param = new RowMeta();
+ /**
+ * SQL parameter specification parsed from source SQL. Supports named
?{name} and positional ?
+ * placeholders.
+ */
+ static final class SqlParameterSpec {
+ private final String preparedSql;
+ private final List<String> parameterReferences;
+ private final int positionalParameterCount;
+
+ SqlParameterSpec(
+ String preparedSql, List<String> parameterReferences, int
positionalParameterCount) {
+ this.preparedSql = preparedSql;
+ this.parameterReferences = parameterReferences;
+ this.positionalParameterCount = positionalParameterCount;
+ }
+
+ String getPreparedSql() {
+ return preparedSql;
+ }
+
+ List<String> getParameterReferences() {
+ return parameterReferences;
+ }
+
+ int getParameterCount() {
+ return parameterReferences.size();
+ }
- if (fields != null) {
- for (ParameterField field : this.parameters) {
- IValueMeta valueMeta = fields.searchValueMeta(field.getName());
- if (valueMeta != null) {
- param.addValueMeta(valueMeta);
+ int getPositionalParameterCount() {
+ return positionalParameterCount;
+ }
+
+ boolean hasNamedParameters() {
+ for (String parameterReference : parameterReferences) {
+ if (parameterReference != null) {
+ return true;
}
}
+ return false;
}
+ }
+
+ public IRowMeta createQueryParameterRowMeta(IRowMeta sourceMeta,
SqlParameterSpec parameterSpec)
+ throws HopTransformException {
+ IRowMeta queryParametersMeta = new RowMeta();
+
+ int positionalIndex = 0;
+ for (String parameterReference : parameterSpec.getParameterReferences()) {
+ String sourceFieldName = parameterReference;
+ if (sourceFieldName == null) {
+ sourceFieldName = getPositionalParameterFieldName(positionalIndex);
+ positionalIndex++;
+ }
+ if (Utils.isEmpty(sourceFieldName)) {
+ throw new HopTransformException(
+ "Unable to find input field for positional SQL parameter #"
+ + positionalIndex
+ + ". Configure parameter fields or use ?{fieldName}.");
+ }
+
+ int sourceIndex = sourceMeta == null ? -1 :
sourceMeta.indexOfValue(sourceFieldName);
+ if (sourceIndex < 0 || sourceIndex >= sourceMeta.size()) {
+ throw new HopTransformException(
+ "Unable to find input field [" + sourceFieldName + "] referenced
in SQL parameter.");
+ }
+
+
queryParametersMeta.addValueMeta(sourceMeta.getValueMeta(sourceIndex).clone());
+ }
+
+ return queryParametersMeta;
+ }
+
+ /**
+ * Parse SQL with support for ?{name} named placeholders. Returns prepared
SQL (with '?' markers)
+ * and a list of parameter references (null for positional parameters).
+ */
+ static SqlParameterSpec parseSqlParameterSpec(String sourceSql) {
+ return parseSqlParameterSpec(sourceSql, false);
+ }
+
+ static SqlParameterSpec parseSqlParameterSpec(
+ String sourceSql, boolean consumeBracketIdentifiers) {
+ Database.SqlParameterSpec parsed =
+ Database.parseSqlParameterSpec(sourceSql, consumeBracketIdentifiers);
+ int positionalParameterCount = 0;
+ for (String parameterReference : parsed.getParameterReferences()) {
+ if (parameterReference == null) {
+ positionalParameterCount++;
+ }
+ }
+ return new SqlParameterSpec(
+ parsed.getPreparedSql(), parsed.getParameterReferences(),
positionalParameterCount);
+ }
+
+ static boolean supportsBracketQuotedIdentifiers(DatabaseMeta databaseMeta) {
+ if (databaseMeta == null || databaseMeta.getIDatabase() == null) {
+ return false;
+ }
+ return "[".equals(databaseMeta.getIDatabase().getStartQuote())
+ && "]".equals(databaseMeta.getIDatabase().getEndQuote());
+ }
+
+ static java.util.Set<String> getMissingNamedParameters(
+ IRowMeta sourceMeta, SqlParameterSpec parameterSpec) {
+ java.util.Set<String> missingFields = new java.util.LinkedHashSet<>();
+
+ for (String parameterReference : parameterSpec.getParameterReferences()) {
+ if (parameterReference != null
+ && (sourceMeta == null ||
sourceMeta.indexOfValue(parameterReference) < 0)) {
+ missingFields.add(parameterReference);
+ }
+ }
+
+ return missingFields;
+ }
+
+ /** Get the missing positional parameter fields for the given parameter
specification. */
+ java.util.Set<String> getMissingPositionalParameterFields(
+ IRowMeta sourceMeta, SqlParameterSpec parameterSpec) {
+ java.util.Set<String> missingFields = new java.util.LinkedHashSet<>();
+ for (int i = 0; i < parameterSpec.getPositionalParameterCount(); i++) {
+ String positionalFieldName = getPositionalParameterFieldName(i);
+ if (Utils.isEmpty(positionalFieldName)) {
+ missingFields.add("#" + (i + 1));
+ } else if (sourceMeta == null ||
sourceMeta.indexOfValue(positionalFieldName) < 0) {
+ missingFields.add(positionalFieldName);
+ }
+ }
+ return missingFields;
+ }
+
+ /**
+ * Heuristic to determine if the SQL is likely a stored procedure call. Used
to suppress metadata
+ * discovery errors at design time.
+ */
+ private boolean isLikelyStoredProcedureSql(String sqlText) {
+ String normalized = Const.NVL(sqlText, "").trim().toLowerCase();
+ return normalized.startsWith("exec ")
+ || normalized.startsWith("execute ")
+ || normalized.startsWith("{call ");
+ }
+
+ /**
+ * Get the field name for the positional parameter at the given index.
Returns null if no field is
+ * defined for that index.
+ */
+ String getPositionalParameterFieldName(int positionalIndex) {
+ if (parameters == null || positionalIndex < 0 || positionalIndex >=
parameters.size()) {
+ return null;
+ }
+ return parameters.get(positionalIndex).getName();
+ }
+
+ /** Build a row meta describing declared parameters (from the step
configuration). */
+ public IRowMeta createDeclaredParameterRowMeta() {
+ IRowMeta param = new RowMeta();
+ if (parameters == null) {
+ return param;
+ }
+
+ for (ParameterField field : this.parameters) {
+ IValueMeta v;
+ try {
+ int id = ValueMetaFactory.getIdForValueMeta(field.getType());
+ v = ValueMetaFactory.createValueMeta(field.getName(), id);
+ } catch (HopPluginException e) {
+ v = new ValueMetaNone(field.getName());
+ }
+ param.addValueMeta(v);
+ }
return param;
}
+ /**
+ * Create a parameter row meta suitable for metadata discovery: maps named
placeholders to
+ * declared parameters when possible and falls back to ValueMetaNone
placeholders when not
+ * available.
+ */
+ public IRowMeta createMetadataLookupParameterRowMeta(SqlParameterSpec
parameterSpec) {
+ return createMetadataLookupParameterRowMeta(parameterSpec, null);
+ }
+
+ /**
+ * Create a parameter row meta suitable for metadata discovery: maps
placeholders to incoming
+ * stream fields when available and falls back to declared parameter types.
+ */
+ public IRowMeta createMetadataLookupParameterRowMeta(
+ SqlParameterSpec parameterSpec, IRowMeta sourceMeta) {
+ IRowMeta declared = createDeclaredParameterRowMeta();
+ IRowMeta queryParameters = new RowMeta();
+
+ int positionalIndex = 0;
+ for (String parameterReference : parameterSpec.getParameterReferences()) {
+ String lookupFieldName = parameterReference;
+ String defaultName = lookupFieldName;
+ if (lookupFieldName == null) {
+ lookupFieldName = getPositionalParameterFieldName(positionalIndex);
+ defaultName = "param" + (positionalIndex + 1);
+ }
+
+ IValueMeta valueMeta = null;
+ if (!Utils.isEmpty(lookupFieldName) && sourceMeta != null) {
+ valueMeta = sourceMeta.searchValueMeta(lookupFieldName);
+ }
+ if (valueMeta == null && !Utils.isEmpty(lookupFieldName)) {
+ valueMeta = declared.searchValueMeta(lookupFieldName);
+ }
+ if (valueMeta == null) {
+ valueMeta = new ValueMetaNone(defaultName);
+ }
+
+ queryParameters.addValueMeta(valueMeta.clone());
+ if (parameterReference == null) {
+ positionalIndex++;
+ }
+ }
+
+ return queryParameters;
+ }
+
+ /** Create a parameter row suitable for metadata discovery with null values.
*/
+ public Object[] createMetadataLookupParameterRowData(IRowMeta
queryParametersMeta) {
+ if (queryParametersMeta == null || queryParametersMeta.isEmpty()) {
+ return new Object[0];
+ }
+ return new Object[queryParametersMeta.size()];
+ }
+
@Override
public void getFields(
IRowMeta row,
@@ -207,8 +421,6 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
// Which fields are parameters?
// info[0] comes from the database connection.
//
- IRowMeta param = getParameterRow(row);
-
String sqlToUse;
try {
sqlToUse = resolveSql(variables);
@@ -216,17 +428,30 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
throw new HopTransformException(e.getMessage(), e);
}
+ SqlParameterSpec parameterSpec =
+ parseSqlParameterSpec(sqlToUse,
supportsBracketQuotedIdentifiers(databaseMeta));
+
+ // Build metadata lookup parameters, preferring incoming field types
when available.
+ IRowMeta param = createMetadataLookupParameterRowMeta(parameterSpec,
row);
+ Object[] paramRow = createMetadataLookupParameterRowData(param);
+
// First try without connecting to the database... (can be S L O W)
// See if it's in the cache...
//
IRowMeta add = null;
try {
- add = db.getQueryFields(sqlToUse, true, param, new
Object[param.size()]);
+ add = db.getQueryFields(parameterSpec.getPreparedSql(), true, param,
paramRow);
} catch (HopDatabaseException dbe) {
+ if (isLikelyStoredProcedureSql(parameterSpec.getPreparedSql())) {
+ logDetailed(
+ "Unable to determine stored procedure output fields at design
time; deferring metadata to runtime.");
+ logDebug("Stored procedure metadata discovery exception", dbe);
+ return;
+ }
throw new HopTransformException(
BaseMessages.getString(PKG,
"DatabaseJoinMeta.Exception.UnableToDetermineQueryFields")
+ Const.CR
- + sqlToUse,
+ + parameterSpec.getPreparedSql(),
dbe);
}
@@ -241,13 +466,25 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
//
try {
db.connect();
- add = db.getQueryFields(sqlToUse, true, param, new
Object[param.size()]);
+ } catch (HopDatabaseException dbe) {
+ throw new HopTransformException(
+ BaseMessages.getString(PKG,
"DatabaseJoinMeta.Exception.ErrorObtainingFields"), dbe);
+ }
+
+ try {
+ add = db.getQueryFields(parameterSpec.getPreparedSql(), true, param,
paramRow);
for (int i = 0; i < add.size(); i++) {
IValueMeta v = add.getValueMeta(i);
v.setOrigin(name);
}
row.addRowMeta(add);
} catch (HopDatabaseException dbe) {
+ if (isLikelyStoredProcedureSql(parameterSpec.getPreparedSql())) {
+ logDetailed(
+ "Unable to determine stored procedure output fields after
connecting; deferring metadata to runtime.");
+ logDebug("Stored procedure metadata discovery exception after
connecting", dbe);
+ return;
+ }
throw new HopTransformException(
BaseMessages.getString(PKG,
"DatabaseJoinMeta.Exception.ErrorObtainingFields"), dbe);
}
@@ -291,14 +528,30 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
databases = new Database[] {db}; // Keep track of this one for
cancelQuery
try {
- db.connect();
String sqlToUse = resolveSql(variables);
- if (!Utils.isEmpty(sqlToUse)) {
- IRowMeta param = getParameterRow(prev);
+ if (Utils.isEmpty(sqlToUse)) {
+ errorMessage = BaseMessages.getString(PKG,
"DatabaseJoinMeta.CheckResult.InvalidDBQuery");
+ cr = new CheckResult(ICheckResult.TYPE_RESULT_ERROR, errorMessage,
transformMeta);
+ remarks.add(cr);
+ } else {
+ db.connect();
+ SqlParameterSpec parameterSpec =
+ parseSqlParameterSpec(sqlToUse,
supportsBracketQuotedIdentifiers(databaseMeta));
errorMessage = "";
- IRowMeta r = db.getQueryFields(sqlToUse, true, param, new
Object[param.size()]);
+ IRowMeta param;
+ try {
+ param = createQueryParameterRowMeta(prev, parameterSpec);
+ } catch (HopTransformException e) {
+ cr = new CheckResult(ICheckResult.TYPE_RESULT_ERROR,
e.getMessage(), transformMeta);
+ remarks.add(cr);
+ param = new RowMeta();
+ }
+
+ IRowMeta r =
+ db.getQueryFields(
+ parameterSpec.getPreparedSql(), true, param, new
Object[param.size()]);
if (r != null) {
cr =
new CheckResult(
@@ -313,8 +566,8 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
remarks.add(cr);
}
- int q = db.countParameters(sqlToUse);
- if (q != parameters.size()) {
+ int q = db.countParameters(parameterSpec.getPreparedSql());
+ if (q != parameterSpec.getParameterCount()) {
errorMessage =
BaseMessages.getString(
PKG,
"DatabaseJoinMeta.CheckResult.DismatchBetweenParametersAndQuestion")
@@ -327,7 +580,7 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
errorMessage +=
BaseMessages.getString(
PKG,
"DatabaseJoinMeta.CheckResult.DismatchBetweenParametersAndQuestion3")
- + parameters.size();
+ + parameterSpec.getParameterCount();
cr = new CheckResult(ICheckResult.TYPE_RESULT_ERROR, errorMessage,
transformMeta);
remarks.add(cr);
@@ -341,17 +594,19 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
transformMeta);
remarks.add(cr);
}
- }
- // Look up fields in the input stream <prev>
- if (prev != null && !prev.isEmpty()) {
- boolean first = true;
- errorMessage = "";
- boolean errorFound = false;
+ // Look up fields in the input stream <prev>
+ if (prev != null && !prev.isEmpty()) {
+ java.util.Set<String> missingNamedParameters =
+ getMissingNamedParameters(prev, parameterSpec);
+ java.util.Set<String> missingPositionalParameterFields =
+ getMissingPositionalParameterFields(prev, parameterSpec);
- for (ParameterField field : this.parameters) {
- IValueMeta v = prev.searchValueMeta(field.getName());
- if (v == null) {
+ boolean first = true;
+ errorMessage = "";
+ boolean errorFound = false;
+
+ for (String parameterName : missingNamedParameters) {
if (first) {
first = false;
errorMessage +=
@@ -359,25 +614,37 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
+ Const.CR;
}
errorFound = true;
- errorMessage += "\t\t" + field.getName() + Const.CR;
+ errorMessage += "\t\t" + parameterName + Const.CR;
}
- }
- if (errorFound) {
- cr = new CheckResult(ICheckResult.TYPE_RESULT_ERROR, errorMessage,
transformMeta);
+
+ for (String positionalReference :
missingPositionalParameterFields) {
+ if (first) {
+ first = false;
+ errorMessage +=
+ BaseMessages.getString(PKG,
"DatabaseJoinMeta.CheckResult.MissingFields")
+ + Const.CR;
+ }
+ errorFound = true;
+ errorMessage += "\t\t" + positionalReference + Const.CR;
+ }
+
+ if (errorFound) {
+ cr = new CheckResult(ICheckResult.TYPE_RESULT_ERROR,
errorMessage, transformMeta);
+ } else {
+ cr =
+ new CheckResult(
+ ICheckResult.TYPE_RESULT_OK,
+ BaseMessages.getString(PKG,
"DatabaseJoinMeta.CheckResult.AllFieldsFound"),
+ transformMeta);
+ }
+ remarks.add(cr);
} else {
- cr =
- new CheckResult(
- ICheckResult.TYPE_RESULT_OK,
- BaseMessages.getString(PKG,
"DatabaseJoinMeta.CheckResult.AllFieldsFound"),
- transformMeta);
+ errorMessage =
+ BaseMessages.getString(PKG,
"DatabaseJoinMeta.CheckResult.CounldNotReadFields")
+ + Const.CR;
+ cr = new CheckResult(ICheckResult.TYPE_RESULT_ERROR, errorMessage,
transformMeta);
+ remarks.add(cr);
}
- remarks.add(cr);
- } else {
- errorMessage =
- BaseMessages.getString(PKG,
"DatabaseJoinMeta.CheckResult.CounldNotReadFields")
- + Const.CR;
- cr = new CheckResult(ICheckResult.TYPE_RESULT_ERROR, errorMessage,
transformMeta);
- remarks.add(cr);
}
} catch (HopException e) {
errorMessage =
@@ -414,32 +681,37 @@ public class DatabaseJoinMeta extends
BaseTransformMeta<DatabaseJoin, DatabaseJo
@Override
public IRowMeta getTableFields(IVariables variables) {
- // Build a dummy parameter row...
- //
-
DatabaseMeta databaseMeta =
getParentTransformMeta().getParentPipelineMeta().findDatabase(connection,
variables);
- IRowMeta param = new RowMeta();
- for (ParameterField field : this.parameters) {
- IValueMeta v;
- try {
- int id = ValueMetaFactory.getIdForValueMeta(field.getType());
- v = ValueMetaFactory.createValueMeta(field.getName(), id);
- } catch (HopPluginException e) {
- v = new ValueMetaNone(field.getName());
- }
- param.addValueMeta(v);
- }
-
IRowMeta fields = null;
if (databaseMeta != null) {
Database db = new Database(loggingObject, variables, databaseMeta);
databases = new Database[] {db}; // Keep track of this one for
cancelQuery
+ SqlParameterSpec parameterSpec = null;
try {
+ parameterSpec =
+ parseSqlParameterSpec(
+ resolveSql(variables),
supportsBracketQuotedIdentifiers(databaseMeta));
db.connect();
- fields = db.getQueryFields(resolveSql(variables), true, param, new
Object[param.size()]);
+ IRowMeta param = createMetadataLookupParameterRowMeta(parameterSpec);
+ try {
+ fields =
+ db.getQueryFields(
+ parameterSpec.getPreparedSql(),
+ true,
+ param,
+ createMetadataLookupParameterRowData(param));
+ } catch (HopException dbe) {
+ if (isLikelyStoredProcedureSql(parameterSpec.getPreparedSql())) {
+ logDetailed(
+ "Unable to determine stored procedure output fields in
getTableFields(); deferring metadata to runtime.");
+ logDebug("Stored procedure getTableFields metadata discovery
exception", dbe);
+ return null;
+ }
+ throw dbe;
+ }
} catch (HopException dbe) {
logError(
BaseMessages.getString(PKG,
"DatabaseJoinMeta.Log.DatabaseErrorOccurred")
diff --git
a/plugins/transforms/databasejoin/src/main/resources/org/apache/hop/pipeline/transforms/databasejoin/messages/messages_en_US.properties
b/plugins/transforms/databasejoin/src/main/resources/org/apache/hop/pipeline/transforms/databasejoin/messages/messages_en_US.properties
index 90ff031e2c..f0c5b13a7c 100644
---
a/plugins/transforms/databasejoin/src/main/resources/org/apache/hop/pipeline/transforms/databasejoin/messages/messages_en_US.properties
+++
b/plugins/transforms/databasejoin/src/main/resources/org/apache/hop/pipeline/transforms/databasejoin/messages/messages_en_US.properties
@@ -30,6 +30,9 @@ DatabaseJoinDialog.Cache.Label=Enable cache
DatabaseJoinDialog.CacheSize.Label=Cache size in rows (0\: cache everything)
DatabaseJoinDialog.ColumnInfo.ParameterFieldname=Parameter fieldname
DatabaseJoinDialog.ColumnInfo.ParameterType=Parameter Type
+DatabaseJoinDialog.ColumnInfo.ResolvedInputField=Input field
+DatabaseJoinDialog.ColumnInfo.ResolvedPlaceholder=Placeholder
+DatabaseJoinDialog.ColumnInfo.ResolvedType=Type
DatabaseJoinDialog.GetFields.Button=\ &Get Fields
DatabaseJoinDialog.GetFieldsFailed.DialogMessage=Unable to get fields from
previous transforms because of an error
DatabaseJoinDialog.GetFieldsFailed.DialogTitle=Get fields failed
@@ -42,6 +45,9 @@ DatabaseJoinDialog.Outerjoin.Label=Outer join
DatabaseJoinDialog.Outerjoin.Tooltip=Return al least the input row and add
NULLs for the lookup values
DatabaseJoinDialog.Param.Label=The parameters to use\:
DatabaseJoinDialog.Position.Label=Line {0} Column {1}
+DatabaseJoinDialog.ResolvedParameters.Label=Resolved SQL parameters\:
+DatabaseJoinDialog.ResolvedParameters.NotFound=not found
+DatabaseJoinDialog.ResolvedParameters.Unmapped=(not mapped)
DatabaseJoinDialog.Shell.Title=Database join
DatabaseJoinDialog.SQL.Label=SQL
DatabaseJoinDialog.LoadSqlFromFile=Load SQL from file
diff --git
a/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinMetaTest.java
b/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinMetaTest.java
index ca599feefe..6d9b8e45b6 100644
---
a/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinMetaTest.java
+++
b/plugins/transforms/databasejoin/src/test/java/org/apache/hop/pipeline/transforms/databasejoin/DatabaseJoinMetaTest.java
@@ -18,6 +18,7 @@ package org.apache.hop.pipeline.transforms.databasejoin;
import java.nio.file.Files;
import java.nio.file.Path;
+import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
@@ -31,6 +32,7 @@ import org.apache.hop.core.row.IRowMeta;
import org.apache.hop.core.row.IValueMeta;
import org.apache.hop.core.row.RowMeta;
import org.apache.hop.core.row.value.ValueMetaInteger;
+import org.apache.hop.core.row.value.ValueMetaNumber;
import org.apache.hop.core.row.value.ValueMetaString;
import org.apache.hop.core.variables.Variables;
import org.apache.hop.junit.rules.RestoreHopEngineEnvironmentExtension;
@@ -192,36 +194,155 @@ class DatabaseJoinMetaTest implements
IInitializer<DatabaseJoinMeta> {
}
@Test
- void getParameterRowSelectsMatchingIncomingFieldsInOrder() {
+ void parseSqlParameterSpecSupportsMixedNamedAndPositionalPlaceholders() {
+ String sql =
+ "SELECT order_id, total FROM orders WHERE customer_id = ?{customer_id}
AND status = ?";
+
+ DatabaseJoinMeta.SqlParameterSpec spec =
DatabaseJoinMeta.parseSqlParameterSpec(sql);
+
+ Assertions.assertEquals(
+ "SELECT order_id, total FROM orders WHERE customer_id = ? AND status =
?",
+ spec.getPreparedSql());
+ Assertions.assertEquals(2, spec.getParameterCount());
+ Assertions.assertEquals(1, spec.getPositionalParameterCount());
+ Assertions.assertEquals("customer_id",
spec.getParameterReferences().get(0));
+ Assertions.assertNull(spec.getParameterReferences().get(1));
+ }
+
+ @Test
+ void parseSqlParameterSpecForSqlServerExecUsesJdbcPositionalMarkers() {
+ String sql = "exec usp_WOAvgCycleTimeByOp @OrderNumber = ?{order},
@RouteName = ?{route};";
+
+ DatabaseJoinMeta.SqlParameterSpec spec =
DatabaseJoinMeta.parseSqlParameterSpec(sql);
+
+ Assertions.assertEquals(
+ "exec usp_WOAvgCycleTimeByOp @OrderNumber = ?, @RouteName = ?;",
spec.getPreparedSql());
+ Assertions.assertEquals(List.of("order", "route"),
spec.getParameterReferences());
+ Assertions.assertEquals(0, spec.getPositionalParameterCount());
+ }
+
+ @Test
+ void
parseSqlParameterSpecIgnoresQuotedCommentedAndJsonbOperatorQuestionMarks() {
+ String sql =
+ "SELECT \"?identifier\" FROM t /* ? block */ WHERE payload ? 'x' -- ?
line\nAND route = ?{route} AND status = ?";
+
+ DatabaseJoinMeta.SqlParameterSpec spec =
DatabaseJoinMeta.parseSqlParameterSpec(sql);
+
+ Assertions.assertEquals(
+ "SELECT \"?identifier\" FROM t /* ? block */ WHERE payload ? 'x' -- ?
line\nAND route = ? AND status = ?",
+ spec.getPreparedSql());
+ Assertions.assertEquals(3, spec.getParameterCount());
+ Assertions.assertEquals(Arrays.asList(null, "route", null),
spec.getParameterReferences());
+ }
+
+ @Test
+ void parseSqlParameterSpecIgnoresPostgresDollarQuotedQuestionMarks() {
+ String sql = "SELECT $$ ? $$, $tag$ ?{ignored} $tag$ FROM dual WHERE id =
?";
+
+ DatabaseJoinMeta.SqlParameterSpec spec =
DatabaseJoinMeta.parseSqlParameterSpec(sql);
+
+ Assertions.assertEquals(sql, spec.getPreparedSql());
+ Assertions.assertEquals(1, spec.getParameterCount());
+ Assertions.assertEquals(Arrays.asList((String) null),
spec.getParameterReferences());
+ }
+
+ @Test
+ void parseSqlParameterSpecCountsPostgresArrayConstructorParameters() {
+ String sql = "SELECT 1 WHERE x = ANY(ARRAY[?]) AND y = ARRAY[?, ?]";
+
+ DatabaseJoinMeta.SqlParameterSpec spec =
DatabaseJoinMeta.parseSqlParameterSpec(sql);
+
+ Assertions.assertEquals(sql, spec.getPreparedSql());
+ Assertions.assertEquals(3, spec.getParameterCount());
+ Assertions.assertEquals(Arrays.asList(null, null, null),
spec.getParameterReferences());
+ }
+
+ @Test
+ void createQueryParameterRowMetaResolvesNamedAndPositionalOrder() throws
Exception {
DatabaseJoinMeta meta = new DatabaseJoinMeta();
- ParameterField id = new ParameterField();
- id.setName("id");
- ParameterField missing = new ParameterField();
- missing.setName("missing");
- ParameterField name = new ParameterField();
- name.setName("name");
- meta.setParameters(List.of(id, missing, name));
+ ParameterField positional = new ParameterField();
+ positional.setName("status");
+ positional.setType(IValueMeta.TYPE_STRING);
+ meta.setParameters(List.of(positional));
- IRowMeta incoming = new RowMeta();
- incoming.addValueMeta(new ValueMetaString("name"));
- incoming.addValueMeta(new ValueMetaInteger("id"));
- incoming.addValueMeta(new ValueMetaString("extra"));
+ IRowMeta source = new RowMeta();
+ source.addValueMeta(new ValueMetaInteger("customer_id"));
+ source.addValueMeta(new ValueMetaString("status"));
- IRowMeta param = meta.getParameterRow(incoming);
- Assertions.assertEquals(2, param.size());
- Assertions.assertEquals("id", param.getValueMeta(0).getName());
- Assertions.assertEquals("name", param.getValueMeta(1).getName());
+ DatabaseJoinMeta.SqlParameterSpec spec =
+ DatabaseJoinMeta.parseSqlParameterSpec(
+ "SELECT 1 FROM dual WHERE customer_id = ?{customer_id} AND status
= ?");
+
+ IRowMeta queryParamMeta = meta.createQueryParameterRowMeta(source, spec);
+ Assertions.assertEquals(2, queryParamMeta.size());
+ Assertions.assertEquals("customer_id",
queryParamMeta.getValueMeta(0).getName());
+ Assertions.assertEquals("status",
queryParamMeta.getValueMeta(1).getName());
}
@Test
- void getParameterRowHandlesNullIncoming() {
+ void
createMetadataLookupParameterRowMetaPrefersIncomingTypeOverDeclaredType() {
DatabaseJoinMeta meta = new DatabaseJoinMeta();
ParameterField field = new ParameterField();
field.setName("id");
- meta.getParameters().add(field);
- IRowMeta param = meta.getParameterRow(null);
- Assertions.assertNotNull(param);
- Assertions.assertTrue(param.isEmpty());
+ field.setType(IValueMeta.TYPE_STRING);
+ meta.setParameters(List.of(field));
+
+ IRowMeta incoming = new RowMeta();
+ incoming.addValueMeta(new ValueMetaNumber("id"));
+
+ DatabaseJoinMeta.SqlParameterSpec spec =
+ DatabaseJoinMeta.parseSqlParameterSpec("SELECT 1 FROM dual WHERE id =
?");
+ IRowMeta queryParamMeta = meta.createMetadataLookupParameterRowMeta(spec,
incoming);
+
+ Assertions.assertEquals(1, queryParamMeta.size());
+ Assertions.assertEquals(IValueMeta.TYPE_NUMBER,
queryParamMeta.getValueMeta(0).getType());
+ }
+
+ @Test
+ void createMetadataLookupParameterRowDataUsesNullValues() {
+ DatabaseJoinMeta meta = new DatabaseJoinMeta();
+ ParameterField intField = new ParameterField();
+ intField.setName("order");
+ intField.setType(IValueMeta.TYPE_INTEGER);
+ ParameterField boolField = new ParameterField();
+ boolField.setName("active");
+ boolField.setType(IValueMeta.TYPE_BOOLEAN);
+ ParameterField textField = new ParameterField();
+ textField.setName("route");
+ textField.setType(IValueMeta.TYPE_STRING);
+ meta.setParameters(List.of(intField, boolField, textField));
+
+ DatabaseJoinMeta.SqlParameterSpec spec =
+ DatabaseJoinMeta.parseSqlParameterSpec(
+ "exec usp_WOAvgCycleTimeByOp @OrderNumber = ?{order}, @Active =
?{active}, @RouteName = ?{route}");
+ IRowMeta queryParamMeta = meta.createMetadataLookupParameterRowMeta(spec);
+ Object[] rowData =
meta.createMetadataLookupParameterRowData(queryParamMeta);
+
+ Assertions.assertEquals(3, rowData.length);
+ Assertions.assertNull(rowData[0]);
+ Assertions.assertNull(rowData[1]);
+ Assertions.assertNull(rowData[2]);
+ }
+
+ @Test
+ void
getMissingPositionalParameterFieldsChecksDeclaredFieldAndIncomingFieldPresence()
{
+ DatabaseJoinMeta meta = new DatabaseJoinMeta();
+ ParameterField p1 = new ParameterField();
+ p1.setName("id");
+ ParameterField p2 = new ParameterField();
+ p2.setName("missing");
+ meta.setParameters(List.of(p1, p2));
+
+ IRowMeta incoming = new RowMeta();
+ incoming.addValueMeta(new ValueMetaInteger("id"));
+
+ DatabaseJoinMeta.SqlParameterSpec spec =
+ DatabaseJoinMeta.parseSqlParameterSpec(
+ "SELECT 1 FROM dual WHERE id = ? AND status = ? AND route = ?");
+
+ Assertions.assertEquals(
+ Arrays.asList("missing", "#3"),
+ new ArrayList<>(meta.getMissingPositionalParameterFields(incoming,
spec)));
}
public class ParameterFieldLoadSaveValidator implements
IFieldLoadSaveValidator<ParameterField> {
