This is an automated email from the ASF dual-hosted git repository.
hansva 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 a90fd026dd Add "SQL from file" functionality to "Table Input"
transform, fixes #5182 (#6601)
a90fd026dd is described below
commit a90fd026dd17259e17348147b60e154e53597d1e
Author: Hans Van Akelyen <[email protected]>
AuthorDate: Wed Feb 18 08:22:47 2026 +0100
Add "SQL from file" functionality to "Table Input" transform, fixes #5182
(#6601)
---
.../database/0008-table-input-with-sql-file.hpl | 213 +++++++++++++++++++++
.../database/main-0008-table-input.hwf | 142 ++++++++------
integration-tests/database/scripts/testquery.sql | 18 ++
.../pipeline/transforms/tableinput/TableInput.java | 16 +-
.../transforms/tableinput/TableInputDialog.java | 90 ++++++++-
.../transforms/tableinput/TableInputMeta.java | 88 +++++++--
.../tableinput/messages/messages_en_US.properties | 6 +
7 files changed, 492 insertions(+), 81 deletions(-)
diff --git a/integration-tests/database/0008-table-input-with-sql-file.hpl
b/integration-tests/database/0008-table-input-with-sql-file.hpl
new file mode 100644
index 0000000000..31d96739a2
--- /dev/null
+++ b/integration-tests/database/0008-table-input-with-sql-file.hpl
@@ -0,0 +1,213 @@
+<?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.
+
+-->
+<pipeline>
+ <info>
+ <name>0008-table-input-with-sql-file</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <pipeline_status>0</pipeline_status>
+ <parameters>
+ </parameters>
+ <capture_transform_performance>N</capture_transform_performance>
+
<transform_performance_capturing_delay>1000</transform_performance_capturing_delay>
+
<transform_performance_capturing_size_limit>100</transform_performance_capturing_size_limit>
+ <created_user>-</created_user>
+ <created_date>2021/04/29 15:06:59.515</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2021/04/29 15:06:59.515</modified_date>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>Table input</from>
+ <to>count rows</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>count rows</from>
+ <to>check count</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>check count</from>
+ <to>success</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>check count</from>
+ <to>Abort</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>Abort</name>
+ <type>Abort</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <always_log_rows>Y</always_log_rows>
+ <row_threshold>0</row_threshold>
+ <attributes/>
+ <GUI>
+ <xloc>432</xloc>
+ <yloc>176</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Table input</name>
+ <type>TableInput</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <connection>unit-test-db</connection>
+ <execute_each_row>N</execute_each_row>
+ <limit>0</limit>
+ <sql>/*
+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.
+*/
+
+select * from public.testtable</sql>
+ <sql_from_file>${PROJECT_HOME}/scripts/testquery.sql</sql_from_file>
+ <variables_active>N</variables_active>
+ <attributes/>
+ <GUI>
+ <xloc>144</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>check count</name>
+ <type>FilterRows</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <compare>
+ <condition>
+ <conditions>
+</conditions>
+ <function>=</function>
+ <leftvalue>value</leftvalue>
+ <negated>N</negated>
+ <operator>-</operator>
+ <value>
+ <isnull>N</isnull>
+ <length>-1</length>
+ <mask>####0;-####0</mask>
+ <name>constant</name>
+ <precision>0</precision>
+ <text>5</text>
+ <type>Integer</type>
+ </value>
+ </condition>
+ </compare>
+ <send_false_to>Abort</send_false_to>
+ <send_true_to>success</send_true_to>
+ <attributes/>
+ <GUI>
+ <xloc>432</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>count rows</name>
+ <type>GroupBy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <add_linenr>N</add_linenr>
+ <all_rows>N</all_rows>
+ <directory>${java.io.tmpdir}</directory>
+ <fields>
+ <field>
+ <aggregate>value</aggregate>
+ <subject>value</subject>
+ <type>COUNT_ANY</type>
+ </field>
+ </fields>
+ <give_back_row>N</give_back_row>
+ <group>
+</group>
+ <ignore_aggregate>N</ignore_aggregate>
+ <prefix>grp</prefix>
+ <attributes/>
+ <GUI>
+ <xloc>304</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>success</name>
+ <type>Dummy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ <GUI>
+ <xloc>576</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git a/integration-tests/database/main-0008-table-input.hwf
b/integration-tests/database/main-0008-table-input.hwf
index 740d350024..1e4580a1fb 100644
--- a/integration-tests/database/main-0008-table-input.hwf
+++ b/integration-tests/database/main-0008-table-input.hwf
@@ -35,14 +35,15 @@ limitations under the License.
<description/>
<type>SPECIAL</type>
<attributes/>
- <repeat>N</repeat>
- <schedulerType>0</schedulerType>
- <intervalSeconds>0</intervalSeconds>
- <intervalMinutes>60</intervalMinutes>
+ <DayOfMonth>1</DayOfMonth>
+ <doNotWaitOnFirstExecution>N</doNotWaitOnFirstExecution>
<hour>12</hour>
+ <intervalMinutes>60</intervalMinutes>
+ <intervalSeconds>0</intervalSeconds>
<minutes>0</minutes>
+ <repeat>N</repeat>
+ <schedulerType>0</schedulerType>
<weekDay>1</weekDay>
- <DayOfMonth>1</DayOfMonth>
<parallel>N</parallel>
<xloc>64</xloc>
<yloc>48</yloc>
@@ -53,25 +54,22 @@ limitations under the License.
<description/>
<type>PIPELINE</type>
<attributes/>
- <filename>${PROJECT_HOME}/0008-table-input.hpl</filename>
- <params_from_previous>N</params_from_previous>
- <exec_per_row>N</exec_per_row>
- <clear_rows>N</clear_rows>
- <clear_files>N</clear_files>
- <set_logfile>N</set_logfile>
- <logfile/>
- <logext/>
<add_date>N</add_date>
<add_time>N</add_time>
- <loglevel>Basic</loglevel>
- <set_append_logfile>N</set_append_logfile>
- <wait_until_finished>Y</wait_until_finished>
- <follow_abort_remote>N</follow_abort_remote>
+ <clear_files>N</clear_files>
+ <clear_rows>N</clear_rows>
<create_parent_folder>N</create_parent_folder>
- <run_configuration>local</run_configuration>
+ <exec_per_row>N</exec_per_row>
+ <filename>${PROJECT_HOME}/0008-table-input.hpl</filename>
+ <loglevel>Basic</loglevel>
<parameters>
<pass_all_parameters>Y</pass_all_parameters>
</parameters>
+ <params_from_previous>N</params_from_previous>
+ <run_configuration>local</run_configuration>
+ <set_append_logfile>N</set_append_logfile>
+ <set_logfile>N</set_logfile>
+ <wait_until_finished>Y</wait_until_finished>
<parallel>N</parallel>
<xloc>400</xloc>
<yloc>48</yloc>
@@ -82,27 +80,24 @@ limitations under the License.
<description/>
<type>PIPELINE</type>
<attributes/>
- <filename>${PROJECT_HOME}/0008-table-input-with-parameter.hpl</filename>
- <params_from_previous>N</params_from_previous>
- <exec_per_row>N</exec_per_row>
- <clear_rows>N</clear_rows>
- <clear_files>N</clear_files>
- <set_logfile>N</set_logfile>
- <logfile/>
- <logext/>
<add_date>N</add_date>
<add_time>N</add_time>
- <loglevel>Basic</loglevel>
- <set_append_logfile>N</set_append_logfile>
- <wait_until_finished>Y</wait_until_finished>
- <follow_abort_remote>N</follow_abort_remote>
+ <clear_files>N</clear_files>
+ <clear_rows>N</clear_rows>
<create_parent_folder>N</create_parent_folder>
- <run_configuration>local</run_configuration>
+ <exec_per_row>N</exec_per_row>
+ <filename>${PROJECT_HOME}/0008-table-input-with-parameter.hpl</filename>
+ <loglevel>Basic</loglevel>
<parameters>
<pass_all_parameters>Y</pass_all_parameters>
</parameters>
+ <params_from_previous>N</params_from_previous>
+ <run_configuration>local</run_configuration>
+ <set_append_logfile>N</set_append_logfile>
+ <set_logfile>N</set_logfile>
+ <wait_until_finished>Y</wait_until_finished>
<parallel>N</parallel>
- <xloc>608</xloc>
+ <xloc>880</xloc>
<yloc>48</yloc>
<attributes_hac/>
</action>
@@ -111,27 +106,24 @@ limitations under the License.
<description/>
<type>PIPELINE</type>
<attributes/>
- <filename>${PROJECT_HOME}/0008-table-input-with-variable.hpl</filename>
- <params_from_previous>N</params_from_previous>
- <exec_per_row>N</exec_per_row>
- <clear_rows>N</clear_rows>
- <clear_files>N</clear_files>
- <set_logfile>N</set_logfile>
- <logfile/>
- <logext/>
<add_date>N</add_date>
<add_time>N</add_time>
- <loglevel>Basic</loglevel>
- <set_append_logfile>N</set_append_logfile>
- <wait_until_finished>Y</wait_until_finished>
- <follow_abort_remote>N</follow_abort_remote>
+ <clear_files>N</clear_files>
+ <clear_rows>N</clear_rows>
<create_parent_folder>N</create_parent_folder>
- <run_configuration>local</run_configuration>
+ <exec_per_row>N</exec_per_row>
+ <filename>${PROJECT_HOME}/0008-table-input-with-variable.hpl</filename>
+ <loglevel>Basic</loglevel>
<parameters>
<pass_all_parameters>Y</pass_all_parameters>
</parameters>
+ <params_from_previous>N</params_from_previous>
+ <run_configuration>local</run_configuration>
+ <set_append_logfile>N</set_append_logfile>
+ <set_logfile>N</set_logfile>
+ <wait_until_finished>Y</wait_until_finished>
<parallel>N</parallel>
- <xloc>864</xloc>
+ <xloc>1136</xloc>
<yloc>48</yloc>
<attributes_hac/>
</action>
@@ -140,26 +132,46 @@ limitations under the License.
<description/>
<type>SQL</type>
<attributes/>
- <sql/>
- <useVariableSubstitution>F</useVariableSubstitution>
- <sqlfromfile>T</sqlfromfile>
- <sqlfilename>${PROJECT_HOME}/scripts/script2.sql</sqlfilename>
- <sendOneStatement>F</sendOneStatement>
<connection>unit-test-db</connection>
+ <sendOneStatement>N</sendOneStatement>
+ <sqlfilename>${PROJECT_HOME}/scripts/script2.sql</sqlfilename>
+ <sqlfromfile>Y</sqlfromfile>
+ <useVariableSubstitution>N</useVariableSubstitution>
<parallel>N</parallel>
<xloc>208</xloc>
<yloc>48</yloc>
<attributes_hac/>
</action>
+ <action>
+ <name>0008-table-input-with-sql-file.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <clear_files>N</clear_files>
+ <clear_rows>N</clear_rows>
+ <create_parent_folder>N</create_parent_folder>
+ <exec_per_row>N</exec_per_row>
+ <filename>${PROJECT_HOME}/0008-table-input-with-sql-file.hpl</filename>
+ <logext/>
+ <logfile/>
+ <loglevel>Basic</loglevel>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <params_from_previous>N</params_from_previous>
+ <run_configuration>local</run_configuration>
+ <set_append_logfile>N</set_append_logfile>
+ <set_logfile>N</set_logfile>
+ <wait_until_finished>Y</wait_until_finished>
+ <parallel>N</parallel>
+ <xloc>608</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
</actions>
<hops>
- <hop>
- <from>0008-table-input.hpl</from>
- <to>0008-table-input-with-parameter.hpl</to>
- <enabled>Y</enabled>
- <evaluation>Y</evaluation>
- <unconditional>N</unconditional>
- </hop>
<hop>
<from>0008-table-input-with-parameter.hpl</from>
<to>0008-table-input-with-variable.hpl</to>
@@ -181,6 +193,20 @@ limitations under the License.
<evaluation>Y</evaluation>
<unconditional>N</unconditional>
</hop>
+ <hop>
+ <from>0008-table-input.hpl</from>
+ <to>0008-table-input-with-sql-file.hpl</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>0008-table-input-with-sql-file.hpl</from>
+ <to>0008-table-input-with-parameter.hpl</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
</hops>
<notepads>
</notepads>
diff --git a/integration-tests/database/scripts/testquery.sql
b/integration-tests/database/scripts/testquery.sql
new file mode 100644
index 0000000000..c5290dd285
--- /dev/null
+++ b/integration-tests/database/scripts/testquery.sql
@@ -0,0 +1,18 @@
+/*
+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.
+*/
+
+select * from public.testtable
\ No newline at end of file
diff --git
a/plugins/transforms/tableinput/src/main/java/org/apache/hop/pipeline/transforms/tableinput/TableInput.java
b/plugins/transforms/tableinput/src/main/java/org/apache/hop/pipeline/transforms/tableinput/TableInput.java
index d986deda35..9a18cc44ce 100644
---
a/plugins/transforms/tableinput/src/main/java/org/apache/hop/pipeline/transforms/tableinput/TableInput.java
+++
b/plugins/transforms/tableinput/src/main/java/org/apache/hop/pipeline/transforms/tableinput/TableInput.java
@@ -217,11 +217,17 @@ public class TableInput extends
BaseTransform<TableInputMeta, TableInputData> {
boolean success = true;
// Open the query with the optional parameters received from the source
transforms.
- String sql = null;
+ String sql;
+ try {
+ sql = meta.getEffectiveSql(variables);
+ } catch (HopException e) {
+ logError("Could not get SQL: " + e.getMessage());
+ setErrors(1);
+ stopAll();
+ return false;
+ }
if (meta.isVariableReplacementActive()) {
- sql = resolve(meta.getSql());
- } else {
- sql = meta.getSql();
+ sql = resolve(sql);
}
if (isDetailed()) {
@@ -304,7 +310,7 @@ public class TableInput extends
BaseTransform<TableInputMeta, TableInputData> {
// Verify some basic things first...
//
boolean passed = true;
- if (Utils.isEmpty(meta.getSql())) {
+ if (Utils.isEmpty(meta.getSql()) &&
Utils.isEmpty(meta.getSqlFromFile())) {
logError(BaseMessages.getString(PKG,
"TableInput.Exception.SQLIsNeeded"));
passed = false;
}
diff --git
a/plugins/transforms/tableinput/src/main/java/org/apache/hop/pipeline/transforms/tableinput/TableInputDialog.java
b/plugins/transforms/tableinput/src/main/java/org/apache/hop/pipeline/transforms/tableinput/TableInputDialog.java
index 0d255931a6..961cc813ce 100644
---
a/plugins/transforms/tableinput/src/main/java/org/apache/hop/pipeline/transforms/tableinput/TableInputDialog.java
+++
b/plugins/transforms/tableinput/src/main/java/org/apache/hop/pipeline/transforms/tableinput/TableInputDialog.java
@@ -25,10 +25,12 @@ import org.apache.hop.core.Props;
import org.apache.hop.core.database.Database;
import org.apache.hop.core.database.DatabaseMeta;
import org.apache.hop.core.exception.HopException;
+import org.apache.hop.core.exception.HopFileException;
import org.apache.hop.core.row.IRowMeta;
import org.apache.hop.core.row.IValueMeta;
import org.apache.hop.core.util.Utils;
import org.apache.hop.core.variables.IVariables;
+import org.apache.hop.core.vfs.HopVfs;
import org.apache.hop.i18n.BaseMessages;
import org.apache.hop.pipeline.Pipeline;
import org.apache.hop.pipeline.PipelineMeta;
@@ -85,6 +87,10 @@ public class TableInputDialog extends BaseTransformDialog {
private Label wlPosition;
+ private Label wlSqlFromFile;
+ private TextVar wSqlFromFile;
+ private Button wbSqlFromFile;
+
public TableInputDialog(
Shell parent, IVariables variables, TableInputMeta transformMeta,
PipelineMeta pipelineMeta) {
super(parent, variables, transformMeta, pipelineMeta);
@@ -101,6 +107,56 @@ public class TableInputDialog extends BaseTransformDialog {
wConnection = addConnectionLine(shell, wSpacer, input.getConnection(),
lsMod);
wConnection.addListener(SWT.Selection, e -> getSqlReservedWords());
+ // Load SQL from file
+ wlSqlFromFile = new Label(shell, SWT.RIGHT);
+ wlSqlFromFile.setText(BaseMessages.getString(PKG,
"TableInputDialog.LoadSqlFromFile"));
+ PropsUi.setLook(wlSqlFromFile);
+ FormData fdlSqlFromFile = new FormData();
+ fdlSqlFromFile.left = new FormAttachment(0, 0);
+ fdlSqlFromFile.right = new FormAttachment(middle, -margin);
+ fdlSqlFromFile.top = new FormAttachment(wConnection, margin);
+ wlSqlFromFile.setLayoutData(fdlSqlFromFile);
+ wbSqlFromFile = new Button(shell, SWT.PUSH);
+ PropsUi.setLook(wbSqlFromFile);
+ wbSqlFromFile.setText(BaseMessages.getString(PKG,
"TableInputDialog.Browse"));
+ FormData fdbSqlFromFile = new FormData();
+ fdbSqlFromFile.right = new FormAttachment(100, 0);
+ fdbSqlFromFile.top = new FormAttachment(wlSqlFromFile, 0, SWT.CENTER);
+ wbSqlFromFile.setLayoutData(fdbSqlFromFile);
+
+ wSqlFromFile = new TextVar(variables, shell, SWT.SINGLE | SWT.LEFT |
SWT.BORDER);
+ PropsUi.setLook(wSqlFromFile);
+ wSqlFromFile.addModifyListener(lsMod);
+ FormData fdSqlFromFile = new FormData();
+ fdSqlFromFile.left = new FormAttachment(middle, 0);
+ fdSqlFromFile.right = new FormAttachment(wbSqlFromFile, -margin);
+ fdSqlFromFile.top = new FormAttachment(wlSqlFromFile, 0, SWT.CENTER);
+ wSqlFromFile.setLayoutData(fdSqlFromFile);
+ wbSqlFromFile.addListener(
+ SWT.Selection,
+ e -> {
+ String path =
+ BaseDialog.presentFileDialog(
+ shell,
+ wSqlFromFile,
+ variables,
+ new String[] {"*.sql", "*"},
+ new String[] {
+ BaseMessages.getString(PKG, "TableInputDialog.SqlFiles"),
+ BaseMessages.getString(PKG, "System.FileType.AllFiles")
+ },
+ false);
+ if (path != null) {
+ loadSqlFromFileAndSetReadOnly();
+ }
+ });
+ wSqlFromFile.addModifyListener(
+ e -> {
+ if (Utils.isEmpty(wSqlFromFile.getText())) {
+ wSql.setEditable(true);
+ }
+ });
+
// Limit input ...
Label wlLimit = new Label(shell, SWT.RIGHT);
wlLimit.setText(BaseMessages.getString(PKG, "TableInputDialog.LimitSize"));
@@ -209,7 +265,7 @@ public class TableInputDialog extends BaseTransformDialog {
PropsUi.setLook(wlSql);
FormData fdlSql = new FormData();
fdlSql.left = new FormAttachment(0, 0);
- fdlSql.top = new FormAttachment(wConnection, margin);
+ fdlSql.top = new FormAttachment(wbSqlFromFile, margin);
wlSql.setLayoutData(fdlSql);
Button wbTable = new Button(shell, SWT.PUSH | SWT.CENTER);
@@ -217,7 +273,7 @@ public class TableInputDialog extends BaseTransformDialog {
wbTable.setText(BaseMessages.getString(PKG,
"TableInputDialog.GetSQLAndSelectStatement"));
FormData fdbTable = new FormData();
fdbTable.right = new FormAttachment(100, 0);
- fdbTable.top = new FormAttachment(wConnection, margin);
+ fdbTable.top = new FormAttachment(wbSqlFromFile, margin);
wbTable.setLayoutData(fdbTable);
if (EnvironmentUtils.getInstance().isWeb()) {
@@ -330,6 +386,28 @@ public class TableInputDialog extends BaseTransformDialog {
PKG, "TableInputDialog.Position.Label", "" + lineNumber, "" +
columnNumber));
}
+ private void loadSqlFromFileAndSetReadOnly() {
+ String path = variables.resolve(wSqlFromFile.getText());
+ if (Utils.isEmpty(path)) {
+ wSql.setEditable(true);
+ return;
+ }
+ try {
+ String content = HopVfs.getTextFileContent(path, Const.XML_ENCODING);
+ wSql.setText(content);
+ wSql.setEditable(false);
+ } catch (HopFileException e) {
+ MessageBox mb = new MessageBox(shell, SWT.OK | SWT.ICON_WARNING);
+ mb.setText(BaseMessages.getString(PKG,
"TableInputDialog.DialogCaptionError"));
+ mb.setMessage(
+ BaseMessages.getString(PKG,
"TableInputDialog.CouldNotLoadSqlFromFile", path)
+ + Const.CR
+ + e.getMessage());
+ mb.open();
+ wSql.setEditable(true);
+ }
+ }
+
protected void setSqlToolTip() {
if (wVariables.getSelection()) {
wSql.setToolTipText(variables.resolve(wSql.getText()));
@@ -346,6 +424,13 @@ public class TableInputDialog extends BaseTransformDialog {
wConnection.setText(input.getConnection());
}
+ wSqlFromFile.setText(Const.NVL(input.getSqlFromFile(), ""));
+ if (!Utils.isEmpty(wSqlFromFile.getText())) {
+ loadSqlFromFileAndSetReadOnly();
+ } else {
+ wSql.setEditable(true);
+ }
+
wLimit.setText(Const.NVL(input.getRowLimit(), ""));
wDataFrom.setText(Const.NVL(input.getLookup(), ""));
wEachRow.setSelection(input.isExecuteEachInputRow());
@@ -369,6 +454,7 @@ public class TableInputDialog extends BaseTransformDialog {
preview && !Utils.isEmpty(wSql.getSelectionText())
? wSql.getSelectionText()
: wSql.getText());
+ meta.setSqlFromFile(wSqlFromFile.getText());
meta.setRowLimit(wLimit.getText());
meta.setExecuteEachInputRow(wEachRow.getSelection());
diff --git
a/plugins/transforms/tableinput/src/main/java/org/apache/hop/pipeline/transforms/tableinput/TableInputMeta.java
b/plugins/transforms/tableinput/src/main/java/org/apache/hop/pipeline/transforms/tableinput/TableInputMeta.java
index a245ecfd72..7eeb2aa14b 100644
---
a/plugins/transforms/tableinput/src/main/java/org/apache/hop/pipeline/transforms/tableinput/TableInputMeta.java
+++
b/plugins/transforms/tableinput/src/main/java/org/apache/hop/pipeline/transforms/tableinput/TableInputMeta.java
@@ -27,6 +27,7 @@ import org.apache.hop.core.database.Database;
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.exception.HopFileException;
import org.apache.hop.core.exception.HopTransformException;
import org.apache.hop.core.exception.HopXmlException;
import org.apache.hop.core.row.IRowMeta;
@@ -35,6 +36,7 @@ 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.core.vfs.HopVfs;
import org.apache.hop.i18n.BaseMessages;
import org.apache.hop.metadata.api.HopMetadataProperty;
import org.apache.hop.metadata.api.HopMetadataPropertyType;
@@ -88,6 +90,12 @@ public class TableInputMeta extends
BaseTransformMeta<TableInput, TableInputData
@HopMetadataProperty private String lookup;
+ /**
+ * When set, SQL is loaded from this file (VFS path, supports variables).
SQL editor is read-only.
+ */
+ @HopMetadataProperty(key = "sql_from_file", injectionKey = "SQL_FROM_FILE")
+ private String sqlFromFile;
+
public TableInputMeta() {
super();
}
@@ -150,6 +158,32 @@ public class TableInputMeta extends
BaseTransformMeta<TableInput, TableInputData
this.lookup = lookup;
}
+ public String getSqlFromFile() {
+ return sqlFromFile;
+ }
+
+ public void setSqlFromFile(String sqlFromFile) {
+ this.sqlFromFile = sqlFromFile;
+ }
+
+ /**
+ * Returns the SQL to execute: either from the inline editor or loaded from
the file specified by
+ * sqlFromFile (using VFS). Variables are resolved in the file path.
+ */
+ public String getEffectiveSql(IVariables variables) throws HopException {
+ if (!Utils.isEmpty(sqlFromFile)) {
+ String path = variables.resolve(sqlFromFile);
+ try {
+ return HopVfs.getTextFileContent(path, Const.XML_ENCODING);
+ } catch (HopFileException e) {
+ throw new HopException(
+ BaseMessages.getString(PKG,
"TableInputMeta.Exception.CouldNotLoadSqlFromFile", path),
+ e);
+ }
+ }
+ return sql;
+ }
+
@Override
public Object clone() {
TableInputMeta retval = (TableInputMeta) super.clone();
@@ -189,9 +223,15 @@ public class TableInputMeta extends
BaseTransformMeta<TableInput, TableInputData
super.databases = new Database[] {db}; // keep track of it for canceling
purposes...
// First try without connecting to the database... (can be S L O W)
- String sNewSql = sql;
+ String effectiveSql;
+ try {
+ effectiveSql = getEffectiveSql(variables);
+ } catch (HopException e) {
+ throw new HopTransformException(e.getMessage(), e);
+ }
+ String sNewSql = effectiveSql;
if (isVariableReplacementActive()) {
- sNewSql = db.resolve(sql);
+ sNewSql = db.resolve(effectiveSql);
if (variables != null) {
sNewSql = variables.resolve(sNewSql);
}
@@ -277,6 +317,18 @@ public class TableInputMeta extends
BaseTransformMeta<TableInput, TableInputData
IHopMetadataProvider metadataProvider) {
CheckResult cr;
+ String effectiveSql = null;
+ try {
+ effectiveSql = getEffectiveSql(variables);
+ } catch (HopException e) {
+ cr =
+ new CheckResult(
+ ICheckResult.TYPE_RESULT_ERROR,
+ "Could not get SQL: " + e.getMessage(),
+ transformMeta);
+ remarks.add(cr);
+ }
+
DatabaseMeta databaseMeta = null;
try {
@@ -308,16 +360,18 @@ public class TableInputMeta extends
BaseTransformMeta<TableInput, TableInputData
ICheckResult.TYPE_RESULT_OK, "Connection to database OK",
transformMeta);
remarks.add(cr);
- if (!Utils.isEmpty(sql)) {
- cr =
- new CheckResult(
- ICheckResult.TYPE_RESULT_OK, "SQL statement is entered",
transformMeta);
- remarks.add(cr);
- } else {
- cr =
- new CheckResult(
- ICheckResult.TYPE_RESULT_ERROR, "SQL statement is missing.",
transformMeta);
- remarks.add(cr);
+ if (effectiveSql != null) {
+ if (!Utils.isEmpty(effectiveSql)) {
+ cr =
+ new CheckResult(
+ ICheckResult.TYPE_RESULT_OK, "SQL statement is entered",
transformMeta);
+ remarks.add(cr);
+ } else {
+ cr =
+ new CheckResult(
+ ICheckResult.TYPE_RESULT_ERROR, "SQL statement is
missing.", transformMeta);
+ remarks.add(cr);
+ }
}
} catch (HopException e) {
cr =
@@ -369,12 +423,13 @@ public class TableInputMeta extends
BaseTransformMeta<TableInput, TableInputData
// Count the number of ? in the SQL string:
int count = 0;
- for (int i = 0; i < sql.length(); i++) {
- char c = sql.charAt(i);
+ String sqlForParams = (effectiveSql != null) ? effectiveSql : "";
+ for (int i = 0; i < sqlForParams.length(); i++) {
+ char c = sqlForParams.charAt(i);
if (c == '\'') { // skip to next quote!
do {
i++;
- c = sql.charAt(i);
+ c = sqlForParams.charAt(i);
} while (c != '\'');
}
if (c == '?') {
@@ -458,6 +513,7 @@ public class TableInputMeta extends
BaseTransformMeta<TableInput, TableInputData
try {
DatabaseMeta databaseMeta =
metadataProvider.getSerializer(DatabaseMeta.class).load(variables.resolve(connection));
+ String effectiveSql = getEffectiveSql(variables);
// Find the lookupfields...
IRowMeta out = new RowMeta();
@@ -478,7 +534,7 @@ public class TableInputMeta extends
BaseTransformMeta<TableInput, TableInputData
outvalue.getName(),
outvalue.getName(),
transformMeta.getName(),
- sql,
+ effectiveSql,
"read from one or more database tables via SQL statement");
impact.add(ii);
}
diff --git
a/plugins/transforms/tableinput/src/main/resources/org/apache/hop/pipeline/transforms/tableinput/messages/messages_en_US.properties
b/plugins/transforms/tableinput/src/main/resources/org/apache/hop/pipeline/transforms/tableinput/messages/messages_en_US.properties
index 291103399c..f199d7e4d1 100644
---
a/plugins/transforms/tableinput/src/main/resources/org/apache/hop/pipeline/transforms/tableinput/messages/messages_en_US.properties
+++
b/plugins/transforms/tableinput/src/main/resources/org/apache/hop/pipeline/transforms/tableinput/messages/messages_en_US.properties
@@ -37,9 +37,13 @@ TableInputDialog.LimitSize=Limit size
TableInputDialog.NumberOfRowsToPreview=Enter the number of rows you would like
to preview\:
TableInputDialog.PerhapsNoPermissions=Perhaps you don''t have the right
permissions?
TableInputDialog.Position.Label=Line {0} Column {1}
+TableInputDialog.Browse=Browse...
+TableInputDialog.CouldNotLoadSqlFromFile=Could not load SQL from file: {0}
+TableInputDialog.LoadSqlFromFile=Load SQL from file
TableInputDialog.ReplaceVariables=Replace variables in script
TableInputDialog.SelectValidConnection=Please select a valid connection\!
TableInputDialog.SQL=SQL
+TableInputDialog.SqlFiles=SQL files
TableInputDialog.TableInput=Table input
TableInputDialog.TransformName=Transform name
TableInputMeta.CheckResult.DatabaseMetaError=Unable to get a reference to
databaseMeta for connection: ''{0}''
@@ -50,4 +54,6 @@ TableInputMeta.Injection.LAZY_CONVERSION=Enable this option
to optimize data typ
TableInputMeta.Injection.LIMIT=The maximum number of lines to read.
TableInputMeta.Injection.REPLACE_VARIABLES=Enable this option to replace
variables in the script.
TableInputMeta.Injection.SQL=The SQL statement used to read information from
the database connection.
+TableInputMeta.Exception.CouldNotLoadSqlFromFile=Could not load SQL from file:
{0}
TableInputMeta.keyword=table,input
+System.FileType.AllFiles=All files
