You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hop.apache.org by ba...@apache.org on 2026/10/09 14:43:36 UTC
(hop) branch main updated: issue #4189 : SQL File Output, option to skip INSERT statements and select fields (#8782)
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 3b4cd83447 issue #4189 : SQL File Output, option to skip INSERT statements and select fields (#8782)
3b4cd83447 is described below
commit 3b4cd83447dc300441fe4ebc1e2c7d1f50186ee2
Author: Jean-Baptiste Sossou <jb...@atolcd.com>
AuthorDate: Fri Oct 9 16:43:29 2026 +0200
issue #4189 : SQL File Output, option to skip INSERT statements and select fields (#8782)
Adds two options to the SQL File Output transform:
- "Do not add INSERT statements": only available when "Add create table statement" is checked, so you can generate a ddl-only script (CREATE TABLE) without dumping every row.
- "Specify table fields" in the Content tab, with a field grid (name / rename) and a Get fields button. When enabled, only the selected fields end up in the CREATE TABLE and INSERT statements, renamed if needed.
Format and encoding are now grouped in the Content tab.
Existing pipelines keep the current behaviour (both options off by default).
---
.../pages/pipeline/transforms/sqlfileoutput.adoc | 10 +-
...l-file-output-selected-fields-validate-file.hpl | 289 +++++++++++++++++++++
.../0029-sql-file-output-selected-fields.hpl | 136 ++++++++++
...l-file-output-with-no-inserts-validate-file.hpl | 286 ++++++++++++++++++++
.../0029-sql-file-output-with-no-inserts.hpl | 139 ++++++++++
.../database/main-0029-sql-file-output.hwf | 245 +++++++++++++----
.../transforms/sqlfileoutput/SQLFileOutput.java | 43 ++-
.../sqlfileoutput/SQLFileOutputData.java | 2 +
.../sqlfileoutput/SQLFileOutputDialog.java | 230 +++++++++++++++-
.../sqlfileoutput/SQLFileOutputField.java | 91 +++++++
.../sqlfileoutput/SQLFileOutputMeta.java | 114 ++++++--
.../messages/messages_en_US.properties | 20 +-
.../messages/messages_fr_FR.properties | 24 +-
.../sqlfileoutput/SQLFileOutputMetaTest.java | 103 ++++++++
.../resources/sql-file-output-transform-fields.xml | 68 +++++
15 files changed, 1705 insertions(+), 95 deletions(-)
diff --git a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/sqlfileoutput.adoc b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/sqlfileoutput.adoc
index 863f742c0a..83ab27444e 100644
--- a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/sqlfileoutput.adoc
+++ b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/sqlfileoutput.adoc
@@ -46,6 +46,8 @@ The information in the connection is used to select the appropriate dialect.
* Add create table statement : check this if you want the "CREATE TABLE" statement to be included in the generated SQL
* Add truncate table statement: check this if the table is expected to exist and you want the "TRUNCATE TABLE" statement to be included in the generated SQL.
Note that this could also be a "DELETE FROM" statement, depending on the database dialect.
+* Do not add INSERT statements : only available when "Add create table statement" is checked.
+Enable this to generate only the CREATE TABLE statement, without an INSERT statement for every input row.
* Start new line for each statement : Enable this to increase the readability of the file
* Filename : the filename (without file extension)
* Create parent folder : if you want the parent folder of the specified file to be created if it doesn't exist already.
@@ -68,4 +70,10 @@ A unique list is being kept in memory that can be used in the next workflow acti
|Option|Description
|Date format|Allows you to specify the date format.
|Encoding|Allows you to specify the text file encoding
-|===
+|Specify table fields|Enable this to only use the fields listed below in the generated CREATE TABLE and INSERT statements. When disabled, all incoming fields are used.
+|Fields a|
+* Name : the name of the incoming field to include
+* Rename : optional new column name to use in the generated SQL
+
+Use the "Get fields" button to fill the grid with the fields of the previous transform.
+|===
\ No newline at end of file
diff --git a/integration-tests/database/0029-sql-file-output-selected-fields-validate-file.hpl b/integration-tests/database/0029-sql-file-output-selected-fields-validate-file.hpl
new file mode 100644
index 0000000000..610d538007
--- /dev/null
+++ b/integration-tests/database/0029-sql-file-output-selected-fields-validate-file.hpl
@@ -0,0 +1,289 @@
+<?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>
+ <pipeline_version/>
+ <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>
+ <pipeline_status>0</pipeline_status>
+ <parameters/>
+ <name>0029-sql-file-output-selected-fields-validate-file</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <created_user>-</created_user>
+ <modified_user>-</modified_user>
+ <created_date>2026/10/06 14:12:52.063</created_date>
+ <modified_date>2026/10/06 14:12:52.063</modified_date>
+ <created_hop_version/>
+ <modified_hop_version>2.20.0-SNAPSHOT</modified_hop_version>
+ </info>
+ <transform>
+ <type>Abort</type>
+ <name>Abort</name>
+ <row_threshold>0</row_threshold>
+ <always_log_rows>Y</always_log_rows>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>704</xloc>
+ <yloc>208</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>DataGrid</type>
+ <name>Data grid</name>
+ <fields>
+ <field>
+ <currency/>
+ <decimal/>
+ <group/>
+ <name>sql_file</name>
+ <type>String</type>
+ <format/>
+ <length>-1</length>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ </field>
+ </fields>
+ <data>
+ <line>
+ <item>(</item>
+ </line>
+ <line>
+ <item> col1 TEXT</item>
+ </line>
+ <line>
+ <item>)</item>
+ </line>
+ <line>
+ <item>;</item>
+ </line>
+ <line>
+ <item/>
+ </line>
+ <line>
+ <item>INSERT INTO "public".testtable(col1) VALUES (E'this');</item>
+ </line>
+ <line>
+ <item>INSERT INTO "public".testtable(col1) VALUES (E'some');</item>
+ </line>
+ </data>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>192</xloc>
+ <yloc>112</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>FilterRows</type>
+ <name>Filter rows</name>
+ <compare>
+ <condition>
+ <negated>N</negated>
+ <operator>-</operator>
+ <leftvalue>sql_file</leftvalue>
+ <function><></function>
+ <rightvalue>content</rightvalue>
+ <conditions/>
+ </condition>
+ </compare>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>512</xloc>
+ <yloc>208</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>MergeJoin</type>
+ <name>Merge join</name>
+ <join_type>FULL OUTER</join_type>
+ <transform1>Data grid</transform1>
+ <transform2>Text file input</transform2>
+ <keys_1>
+ <key>sql_file</key>
+ </keys_1>
+ <keys_2>
+ <key>content</key>
+ </keys_2>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>352</xloc>
+ <yloc>208</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>TextFileInput2</type>
+ <name>Text file input</name>
+ <file_type>CSV</file_type>
+ <compression>None</compression>
+ <separator>[]]';''./</separator>
+ <enclosure/>
+ <enclosure_breaks>N</enclosure_breaks>
+ <escapechar/>
+ <null_if_not_enclosed>N</null_if_not_enclosed>
+ <header>Y</header>
+ <prependFileName>N</prependFileName>
+ <nr_headerlines>1</nr_headerlines>
+ <footer>N</footer>
+ <nr_footerlines>1</nr_footerlines>
+ <line_wrapped>N</line_wrapped>
+ <nr_wraps>1</nr_wraps>
+ <layout_paged>N</layout_paged>
+ <nr_lines_per_page>80</nr_lines_per_page>
+ <nr_lines_doc_header>0</nr_lines_doc_header>
+ <noempty>Y</noempty>
+ <include>N</include>
+ <include_field/>
+ <rownum>N</rownum>
+ <rownum_field/>
+ <rownumByFile>N</rownumByFile>
+ <format>mixed</format>
+ <encoding/>
+ <limit>0</limit>
+ <date_format_lenient>Y</date_format_lenient>
+ <date_format_locale>en_US</date_format_locale>
+ <length>Characters</length>
+ <filters/>
+ <error_count_field/>
+ <error_fields_field/>
+ <error_text_field/>
+ <error_line_skipped>N</error_line_skipped>
+ <schema_definition/>
+ <ignore_fields>N</ignore_fields>
+ <namingScheme/>
+ <shortFileFieldName/>
+ <extensionFieldName/>
+ <pathFieldName/>
+ <sizeFieldName/>
+ <hiddenFieldName/>
+ <lastModificationTimeFieldName/>
+ <uriNameFieldName/>
+ <rootUriNameFieldName/>
+ <files>
+ <file>
+ <name>${PROJECT_HOME}/output/filename_selected_fields.sql</name>
+ <filemask/>
+ <exclude_filemask/>
+ <file_required>N</file_required>
+ <include_subfolders>N</include_subfolders>
+ </file>
+ </files>
+ <accept_filenames>N</accept_filenames>
+ <accept_transform_name/>
+ <passing_through_fields>N</passing_through_fields>
+ <accept_field/>
+ <add_to_result_filenames>Y</add_to_result_filenames>
+ <error_ignored>N</error_ignored>
+ <file_error_field/>
+ <file_error_message_field/>
+ <skip_bad_files>N</skip_bad_files>
+ <bad_line_files_destination_directory/>
+ <bad_line_files_extension>warning</bad_line_files_extension>
+ <error_line_files_destination_directory/>
+ <error_line_files_extension>error</error_line_files_extension>
+ <line_number_files_destination_directory/>
+ <line_number_files_extension>line</line_number_files_extension>
+ <fields>
+ <field>
+ <name>content</name>
+ <position>-1</position>
+ <length>-1</length>
+ <type>String</type>
+ <ignore>N</ignore>
+ <format/>
+ <trim_type>none</trim_type>
+ <precision>-1</precision>
+ <currency/>
+ <decimal/>
+ <group/>
+ <repeat>N</repeat>
+ <null_string/>
+ <if_null_value/>
+ </field>
+ </fields>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>176</xloc>
+ <yloc>288</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <order>
+ <hop>
+ <from>Data grid</from>
+ <to>Merge join</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Text file input</from>
+ <to>Merge join</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Merge join</from>
+ <to>Filter rows</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Filter rows</from>
+ <to>Abort</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <notepads/>
+ <attributes/>
+ <transform_error_handling/>
+</pipeline>
diff --git a/integration-tests/database/0029-sql-file-output-selected-fields.hpl b/integration-tests/database/0029-sql-file-output-selected-fields.hpl
new file mode 100644
index 0000000000..93100a0589
--- /dev/null
+++ b/integration-tests/database/0029-sql-file-output-selected-fields.hpl
@@ -0,0 +1,136 @@
+<?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>
+ <pipeline_version/>
+ <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>
+ <pipeline_status>0</pipeline_status>
+ <parameters/>
+ <name>0029-sql-file-output-selected-fields</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <created_user>-</created_user>
+ <modified_user>-</modified_user>
+ <created_date>2026/10/06 14:37:48.534</created_date>
+ <modified_date>2026/10/06 14:37:48.534</modified_date>
+ <created_hop_version/>
+ <modified_hop_version>2.20.0-SNAPSHOT</modified_hop_version>
+ </info>
+ <transform>
+ <type>DataGrid</type>
+ <name>Data grid</name>
+ <fields>
+ <field>
+ <name>column1</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ </field>
+ <field>
+ <name>column2</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ </field>
+ </fields>
+ <data>
+ <line>
+ <item>this</item>
+ <item>is</item>
+ </line>
+ <line>
+ <item>some</item>
+ <item>data</item>
+ </line>
+ </data>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>192</xloc>
+ <yloc>112</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>SQLFileOutput</type>
+ <name>SQL file output</name>
+ <connection>${DATABASE_NAME}</connection>
+ <schema>public</schema>
+ <table>testtable</table>
+ <truncate>N</truncate>
+ <DoNotAddInsertStatements>N</DoNotAddInsertStatements>
+ <AddToResult>N</AddToResult>
+ <create>Y</create>
+ <file>
+ <name>${PROJECT_HOME}/output/filename_selected_fields</name>
+ <create_parent_folder>Y</create_parent_folder>
+ <DoNotOpenNewFileInit>Y</DoNotOpenNewFileInit>
+ <extension>sql</extension>
+ <splitevery>0</splitevery>
+ <append>N</append>
+ <split>N</split>
+ <haspartno>N</haspartno>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ </file>
+ <encoding>UTF-8</encoding>
+ <dateformat/>
+ <StartNewLine>Y</StartNewLine>
+ <fields>
+ <field>
+ <name>column1</name>
+ <rename>col1</rename>
+ </field>
+ </fields>
+ <specifyFields>Y</specifyFields>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>448</xloc>
+ <yloc>112</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <order>
+ <hop>
+ <from>Data grid</from>
+ <to>SQL file output</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <notepads/>
+ <attributes/>
+ <transform_error_handling/>
+</pipeline>
diff --git a/integration-tests/database/0029-sql-file-output-with-no-inserts-validate-file.hpl b/integration-tests/database/0029-sql-file-output-with-no-inserts-validate-file.hpl
new file mode 100644
index 0000000000..87c9919c9d
--- /dev/null
+++ b/integration-tests/database/0029-sql-file-output-with-no-inserts-validate-file.hpl
@@ -0,0 +1,286 @@
+<?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>
+ <pipeline_version/>
+ <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>
+ <pipeline_status>0</pipeline_status>
+ <parameters/>
+ <name>0029-sql-file-output-with-no-inserts-validate-file</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <created_user>-</created_user>
+ <modified_user>-</modified_user>
+ <created_date>2026/10/06 14:12:52.063</created_date>
+ <modified_date>2026/10/06 20:04:45.363</modified_date>
+ <created_hop_version/>
+ <modified_hop_version>2.20.0-SNAPSHOT</modified_hop_version>
+ </info>
+ <transform>
+ <type>Abort</type>
+ <name>Abort</name>
+ <row_threshold>0</row_threshold>
+ <always_log_rows>Y</always_log_rows>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>704</xloc>
+ <yloc>208</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>DataGrid</type>
+ <name>Data grid</name>
+ <fields>
+ <field>
+ <currency/>
+ <decimal/>
+ <group/>
+ <name>sql_file</name>
+ <type>String</type>
+ <format/>
+ <length>-1</length>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ </field>
+ </fields>
+ <data>
+ <line>
+ <item>(</item>
+ </line>
+ <line>
+ <item> column1 TEXT</item>
+ </line>
+ <line>
+ <item>, column2 TEXT</item>
+ </line>
+ <line>
+ <item>)</item>
+ </line>
+ <line>
+ <item>;</item>
+ </line>
+ <line>
+ <item/>
+ </line>
+ </data>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>192</xloc>
+ <yloc>112</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>FilterRows</type>
+ <name>Filter rows</name>
+ <compare>
+ <condition>
+ <negated>N</negated>
+ <operator>-</operator>
+ <leftvalue>sql_file</leftvalue>
+ <function><></function>
+ <rightvalue>content</rightvalue>
+ <conditions/>
+ </condition>
+ </compare>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>512</xloc>
+ <yloc>208</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>MergeJoin</type>
+ <name>Merge join</name>
+ <join_type>FULL OUTER</join_type>
+ <transform1>Data grid</transform1>
+ <transform2>Text file input</transform2>
+ <keys_1>
+ <key>sql_file</key>
+ </keys_1>
+ <keys_2>
+ <key>content</key>
+ </keys_2>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>352</xloc>
+ <yloc>208</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>TextFileInput2</type>
+ <name>Text file input</name>
+ <file_type>CSV</file_type>
+ <compression>None</compression>
+ <separator>[]]';''./</separator>
+ <enclosure/>
+ <enclosure_breaks>N</enclosure_breaks>
+ <escapechar/>
+ <null_if_not_enclosed>N</null_if_not_enclosed>
+ <header>Y</header>
+ <prependFileName>N</prependFileName>
+ <nr_headerlines>1</nr_headerlines>
+ <footer>N</footer>
+ <nr_footerlines>1</nr_footerlines>
+ <line_wrapped>N</line_wrapped>
+ <nr_wraps>1</nr_wraps>
+ <layout_paged>N</layout_paged>
+ <nr_lines_per_page>80</nr_lines_per_page>
+ <nr_lines_doc_header>0</nr_lines_doc_header>
+ <noempty>Y</noempty>
+ <include>N</include>
+ <include_field/>
+ <rownum>N</rownum>
+ <rownum_field/>
+ <rownumByFile>N</rownumByFile>
+ <format>mixed</format>
+ <encoding/>
+ <limit>0</limit>
+ <date_format_lenient>Y</date_format_lenient>
+ <date_format_locale>en_US</date_format_locale>
+ <length>Characters</length>
+ <filters/>
+ <error_count_field/>
+ <error_fields_field/>
+ <error_text_field/>
+ <error_line_skipped>N</error_line_skipped>
+ <schema_definition/>
+ <ignore_fields>N</ignore_fields>
+ <namingScheme/>
+ <shortFileFieldName/>
+ <extensionFieldName/>
+ <pathFieldName/>
+ <sizeFieldName/>
+ <hiddenFieldName/>
+ <lastModificationTimeFieldName/>
+ <uriNameFieldName/>
+ <rootUriNameFieldName/>
+ <files>
+ <file>
+ <name>${PROJECT_HOME}/output/filename_with_no_inserts.sql</name>
+ <filemask/>
+ <exclude_filemask/>
+ <file_required>N</file_required>
+ <include_subfolders>N</include_subfolders>
+ </file>
+ </files>
+ <accept_filenames>N</accept_filenames>
+ <accept_transform_name/>
+ <passing_through_fields>N</passing_through_fields>
+ <accept_field/>
+ <add_to_result_filenames>Y</add_to_result_filenames>
+ <error_ignored>N</error_ignored>
+ <file_error_field/>
+ <file_error_message_field/>
+ <skip_bad_files>N</skip_bad_files>
+ <bad_line_files_destination_directory/>
+ <bad_line_files_extension>warning</bad_line_files_extension>
+ <error_line_files_destination_directory/>
+ <error_line_files_extension>error</error_line_files_extension>
+ <line_number_files_destination_directory/>
+ <line_number_files_extension>line</line_number_files_extension>
+ <fields>
+ <field>
+ <name>content</name>
+ <position>-1</position>
+ <length>-1</length>
+ <type>String</type>
+ <ignore>N</ignore>
+ <format/>
+ <trim_type>none</trim_type>
+ <precision>-1</precision>
+ <currency/>
+ <decimal/>
+ <group/>
+ <repeat>N</repeat>
+ <null_string/>
+ <if_null_value/>
+ </field>
+ </fields>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>176</xloc>
+ <yloc>288</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <order>
+ <hop>
+ <from>Data grid</from>
+ <to>Merge join</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Text file input</from>
+ <to>Merge join</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Merge join</from>
+ <to>Filter rows</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Filter rows</from>
+ <to>Abort</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <notepads/>
+ <attributes/>
+ <transform_error_handling/>
+</pipeline>
diff --git a/integration-tests/database/0029-sql-file-output-with-no-inserts.hpl b/integration-tests/database/0029-sql-file-output-with-no-inserts.hpl
new file mode 100644
index 0000000000..d5dd7e5de2
--- /dev/null
+++ b/integration-tests/database/0029-sql-file-output-with-no-inserts.hpl
@@ -0,0 +1,139 @@
+<?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>
+ <pipeline_version/>
+ <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>
+ <pipeline_status>0</pipeline_status>
+ <parameters/>
+ <name>0029-sql-file-output-with-no-inserts</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <created_user>-</created_user>
+ <modified_user>-</modified_user>
+ <created_date>2026/10/06 14:37:48.534</created_date>
+ <modified_date>2026/10/06 19:52:32.745</modified_date>
+ <created_hop_version/>
+ <modified_hop_version>2.20.0-SNAPSHOT</modified_hop_version>
+ </info>
+ <transform>
+ <type>DataGrid</type>
+ <name>Data grid</name>
+ <fields>
+ <field>
+ <currency/>
+ <decimal/>
+ <group/>
+ <name>column1</name>
+ <type>String</type>
+ <format/>
+ <length>-1</length>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ </field>
+ <field>
+ <currency/>
+ <decimal/>
+ <group/>
+ <name>column2</name>
+ <type>String</type>
+ <format/>
+ <length>-1</length>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ </field>
+ </fields>
+ <data>
+ <line>
+ <item>this</item>
+ <item>is</item>
+ </line>
+ <line>
+ <item>some</item>
+ <item>data</item>
+ </line>
+ </data>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>192</xloc>
+ <yloc>112</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>SQLFileOutput</type>
+ <name>SQL file output</name>
+ <connection>${DATABASE_NAME}</connection>
+ <schema>public</schema>
+ <table>testtable</table>
+ <truncate>N</truncate>
+ <DoNotAddInsertStatements>Y</DoNotAddInsertStatements>
+ <AddToResult>N</AddToResult>
+ <create>Y</create>
+ <file>
+ <name>${PROJECT_HOME}/output/filename_with_no_inserts</name>
+ <create_parent_folder>Y</create_parent_folder>
+ <DoNotOpenNewFileInit>Y</DoNotOpenNewFileInit>
+ <extension>sql</extension>
+ <splitevery>0</splitevery>
+ <append>N</append>
+ <split>N</split>
+ <haspartno>N</haspartno>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ </file>
+ <encoding>UTF-8</encoding>
+ <dateformat/>
+ <StartNewLine>Y</StartNewLine>
+ <fields/>
+ <specifyFields>N</specifyFields>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>448</xloc>
+ <yloc>112</yloc>
+ </GUI>
+ <description/>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <order>
+ <hop>
+ <from>Data grid</from>
+ <to>SQL file output</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <notepads/>
+ <attributes/>
+ <transform_error_handling/>
+</pipeline>
diff --git a/integration-tests/database/main-0029-sql-file-output.hwf b/integration-tests/database/main-0029-sql-file-output.hwf
index ddf0fd6f9e..885cbbef17 100644
--- a/integration-tests/database/main-0029-sql-file-output.hwf
+++ b/integration-tests/database/main-0029-sql-file-output.hwf
@@ -22,100 +22,218 @@ limitations under the License.
<name_sync_with_filename>Y</name_sync_with_filename>
<description/>
<extended_description/>
- <workflow_version/>
<created_user>-</created_user>
- <created_date>2022/12/02 13:51:33.435</created_date>
<modified_user>-</modified_user>
- <modified_date>2022/12/02 13:51:33.435</modified_date>
- <parameters>
- </parameters>
+ <created_date>2022/12/02 13:51:33.435</created_date>
+ <modified_date>2026/10/06 19:47:22.336</modified_date>
+ <created_hop_version/>
+ <modified_hop_version>2.20.0-SNAPSHOT</modified_hop_version>
+ <workflow_version/>
+ <parameters/>
<actions>
<action>
+ <repeat>N</repeat>
+ <schedulerType>0</schedulerType>
+ <intervalSeconds>0</intervalSeconds>
+ <intervalMinutes>60</intervalMinutes>
+ <DayOfMonth>1</DayOfMonth>
+ <weekDay>1</weekDay>
+ <minutes>0</minutes>
+ <hour>12</hour>
+ <doNotWaitOnFirstExecution>N</doNotWaitOnFirstExecution>
<name>Start</name>
<description/>
<type>SPECIAL</type>
<attributes/>
- <DayOfMonth>1</DayOfMonth>
- <hour>12</hour>
- <intervalMinutes>60</intervalMinutes>
- <intervalSeconds>0</intervalSeconds>
- <minutes>0</minutes>
- <repeat>N</repeat>
- <schedulerType>0</schedulerType>
- <weekDay>1</weekDay>
- <parallel>N</parallel>
<xloc>80</xloc>
<yloc>80</yloc>
+ <parallel>N</parallel>
<attributes_hac/>
</action>
<action>
+ <filename>${PROJECT_HOME}/0029-sql-file-output.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>
+ <create_parent_folder>N</create_parent_folder>
+ <set_logfile>N</set_logfile>
+ <set_append_logfile>N</set_append_logfile>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <loglevel>Basic</loglevel>
+ <wait_until_finished>Y</wait_until_finished>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <run_configuration>local</run_configuration>
<name>0029-sql-file-output.hpl</name>
<description/>
<type>PIPELINE</type>
<attributes/>
- <add_date>N</add_date>
- <add_time>N</add_time>
- <clear_files>N</clear_files>
+ <xloc>224</xloc>
+ <yloc>80</yloc>
+ <parallel>N</parallel>
+ <attributes_hac/>
+ </action>
+ <action>
+ <filename>${PROJECT_HOME}/0029-sql-file-output-validate-file.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>
<create_parent_folder>N</create_parent_folder>
- <exec_per_row>N</exec_per_row>
- <filename>${PROJECT_HOME}/0029-sql-file-output.hpl</filename>
+ <set_logfile>N</set_logfile>
+ <set_append_logfile>N</set_append_logfile>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
<loglevel>Basic</loglevel>
+ <wait_until_finished>Y</wait_until_finished>
<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>272</xloc>
+ <name>0029-sql-file-output-validate-file.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <xloc>464</xloc>
<yloc>80</yloc>
+ <parallel>N</parallel>
<attributes_hac/>
</action>
<action>
- <name>0029-sql-file-output-validate-file.hpl</name>
+ <arg_from_previous>N</arg_from_previous>
+ <fields>
+ <field>
+ <name>${PROJECT_HOME}/output</name>
+ </field>
+ </fields>
+ <limit_folders>10</limit_folders>
+ <success_condition>success_if_no_errors</success_condition>
+ <name>Delete folders</name>
<description/>
- <type>PIPELINE</type>
+ <type>DELETE_FOLDERS</type>
<attributes/>
+ <xloc>368</xloc>
+ <yloc>240</yloc>
+ <parallel>N</parallel>
+ <attributes_hac/>
+ </action>
+ <action>
+ <filename>${PROJECT_HOME}/0029-sql-file-output-selected-fields.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>
+ <create_parent_folder>N</create_parent_folder>
+ <set_logfile>N</set_logfile>
+ <set_append_logfile>N</set_append_logfile>
+ <logfile/>
+ <logext/>
<add_date>N</add_date>
<add_time>N</add_time>
- <clear_files>N</clear_files>
+ <loglevel>Basic</loglevel>
+ <wait_until_finished>Y</wait_until_finished>
+ <wait_timeout/>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <run_configuration>local</run_configuration>
+ <name>0029-sql-file-output-selected-fields.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <xloc>768</xloc>
+ <yloc>80</yloc>
+ <parallel>N</parallel>
+ <attributes_hac/>
+ </action>
+ <action>
+ <filename>${PROJECT_HOME}/0029-sql-file-output-selected-fields-validate-file.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>
<create_parent_folder>N</create_parent_folder>
- <exec_per_row>N</exec_per_row>
- <filename>${PROJECT_HOME}/0029-sql-file-output-validate-file.hpl</filename>
+ <set_logfile>N</set_logfile>
+ <set_append_logfile>N</set_append_logfile>
+ <logfile/>
+ <logext/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
<loglevel>Basic</loglevel>
+ <wait_until_finished>Y</wait_until_finished>
+ <wait_timeout/>
<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>
+ <name>0029-sql-file-output-selected-fields-validate-file.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <xloc>1136</xloc>
+ <yloc>80</yloc>
+ <parallel>N</parallel>
+ <attributes_hac/>
+ </action>
+ <action>
+ <filename>${PROJECT_HOME}/0029-sql-file-output-with-no-inserts.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>
+ <create_parent_folder>N</create_parent_folder>
<set_logfile>N</set_logfile>
+ <set_append_logfile>N</set_append_logfile>
+ <logfile/>
+ <logext/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <loglevel>Basic</loglevel>
<wait_until_finished>Y</wait_until_finished>
+ <wait_timeout/>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <run_configuration>local</run_configuration>
+ <name>0029-sql-file-output-with-no-inserts.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <xloc>1136</xloc>
+ <yloc>240</yloc>
<parallel>N</parallel>
- <xloc>528</xloc>
- <yloc>80</yloc>
<attributes_hac/>
</action>
<action>
- <name>Delete folders</name>
+ <filename>${PROJECT_HOME}/0029-sql-file-output-with-no-inserts-validate-file.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>
+ <create_parent_folder>N</create_parent_folder>
+ <set_logfile>N</set_logfile>
+ <set_append_logfile>N</set_append_logfile>
+ <logfile/>
+ <logext/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <loglevel>Basic</loglevel>
+ <wait_until_finished>Y</wait_until_finished>
+ <wait_timeout/>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <run_configuration>local</run_configuration>
+ <name>0029-sql-file-output-with-no-inserts-validate-file.hpl</name>
<description/>
- <type>DELETE_FOLDERS</type>
+ <type>PIPELINE</type>
<attributes/>
- <arg_from_previous>N</arg_from_previous>
- <success_condition>success_if_no_errors</success_condition>
- <limit_folders>10</limit_folders>
- <fields>
- <field>
- <name>${PROJECT_HOME}/output</name>
- </field>
- </fields>
+ <xloc>736</xloc>
+ <yloc>240</yloc>
<parallel>N</parallel>
- <xloc>800</xloc>
- <yloc>80</yloc>
<attributes_hac/>
</action>
</actions>
@@ -123,26 +241,53 @@ limitations under the License.
<hop>
<from>Start</from>
<to>0029-sql-file-output.hpl</to>
- <enabled>Y</enabled>
<evaluation>Y</evaluation>
<unconditional>Y</unconditional>
+ <enabled>Y</enabled>
</hop>
<hop>
<from>0029-sql-file-output.hpl</from>
<to>0029-sql-file-output-validate-file.hpl</to>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
<enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>0029-sql-file-output-selected-fields.hpl</from>
+ <to>0029-sql-file-output-selected-fields-validate-file.hpl</to>
<evaluation>Y</evaluation>
<unconditional>N</unconditional>
+ <enabled>Y</enabled>
</hop>
<hop>
<from>0029-sql-file-output-validate-file.hpl</from>
- <to>Delete folders</to>
+ <to>0029-sql-file-output-selected-fields.hpl</to>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>0029-sql-file-output-with-no-inserts.hpl</from>
+ <to>0029-sql-file-output-with-no-inserts-validate-file.hpl</to>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>0029-sql-file-output-selected-fields-validate-file.hpl</from>
+ <to>0029-sql-file-output-with-no-inserts.hpl</to>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
<enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>0029-sql-file-output-with-no-inserts-validate-file.hpl</from>
+ <to>Delete folders</to>
<evaluation>N</evaluation>
<unconditional>Y</unconditional>
+ <enabled>Y</enabled>
</hop>
</hops>
- <notepads>
- </notepads>
+ <notepads/>
<attributes/>
</workflow>
diff --git a/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutput.java b/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutput.java
index 01d6578714..3c6b36cab3 100644
--- a/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutput.java
+++ b/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutput.java
@@ -68,8 +68,19 @@ public class SQLFileOutput extends BaseTransform<SQLFileOutputMeta, SQLFileOutpu
first = false;
data.outputRowMeta = getInputRowMeta().clone();
meta.getFields(data.outputRowMeta, getTransformName(), null, null, this, metadataProvider);
- data.insertRowMeta = getInputRowMeta().clone();
-
+ // Columns written in the CREATE TABLE and INSERT statements. This fails with a clear
+ // error if "Specify table fields" is enabled with an empty grid or an unknown field.
+ data.insertRowMeta = meta.getSqlRowMeta(getInputRowMeta());
+ if (meta.isSpecifyFields()) {
+ // positions of the selected fields in the incoming row (validated just above)
+ data.fieldIndexes = new int[meta.getSqlFileOutputFields().size()];
+ for (int i = 0; i < data.fieldIndexes.length; i++) {
+ data.fieldIndexes[i] =
+ getInputRowMeta().indexOfValue(meta.getSqlFileOutputFields().get(i).getName());
+ }
+ } else {
+ data.fieldIndexes = null;
+ }
if (meta.getFile().isDoNotOpenNewFileInit() && !openNewFile()) {
logError("Couldn't open file [" + buildFilename() + "]");
setErrors(1);
@@ -124,15 +135,29 @@ public class SQLFileOutput extends BaseTransform<SQLFileOutputMeta, SQLFileOutpu
}
try {
- String sql =
- data.db.getSqlOutput(schemaName, tableName, data.insertRowMeta, r, meta.getDateFormat())
- + ";";
+ String sql = "";
+ // Inserts are only skipped together with "Add create table statement", as in the dialog.
+ boolean skipInserts = meta.isDoNotAddInsertStatements() && meta.isCreateTable();
+ if (!skipInserts) {
+ // Build the reduced data row matching data.insertRowMeta
+ Object[] insertRowData = r;
+ if (data.fieldIndexes != null) {
+ insertRowData = new Object[data.fieldIndexes.length];
+ for (int i = 0; i < data.fieldIndexes.length; i++) {
+ insertRowData[i] = r[data.fieldIndexes[i]];
+ }
+ }
- // Do we start a new line for this statement ?
- if (meta.isStartNewLine()) {
- sql = sql + Const.CR;
- }
+ sql =
+ data.db.getSqlOutput(
+ schemaName, tableName, data.insertRowMeta, insertRowData, meta.getDateFormat())
+ + ";";
+ // Do we start a new line for this statement ?
+ if (meta.isStartNewLine()) {
+ sql = sql + Const.CR;
+ }
+ }
if (isRowLevel()) {
logRowlevel(BaseMessages.getString(PKG, "SQLFileOutputLog.OutputSQL", sql));
}
diff --git a/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputData.java b/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputData.java
index 6bc24b951f..d2bce790de 100644
--- a/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputData.java
+++ b/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputData.java
@@ -49,6 +49,8 @@ public class SQLFileOutputData extends BaseTransformData implements ITransformDa
public IRowMeta insertRowMeta;
+ public int[] fieldIndexes;
+
public SQLFileOutputData() {
super();
diff --git a/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputDialog.java b/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputDialog.java
index 1ee1036b7d..1486fa79de 100644
--- a/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputDialog.java
+++ b/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputDialog.java
@@ -17,6 +17,8 @@
package org.apache.hop.pipeline.transforms.sqlfileoutput;
+import java.util.ArrayList;
+import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.apache.hop.core.Const;
import org.apache.hop.core.Props;
@@ -37,8 +39,10 @@ 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.GuiResource;
+import org.apache.hop.ui.core.widget.ColumnInfo;
import org.apache.hop.ui.core.widget.MetaSelectionLine;
import org.apache.hop.ui.core.widget.NamingSchemeTypes;
+import org.apache.hop.ui.core.widget.TableView;
import org.apache.hop.ui.core.widget.TextVar;
import org.apache.hop.ui.hopgui.perspective.database.DatabaseWorkbenchDialog;
import org.apache.hop.ui.pipeline.transform.BaseTransformDialog;
@@ -80,6 +84,9 @@ public class SQLFileOutputDialog extends BaseTransformDialog {
private Button wAddCreate;
+ private Label wlDoNotAddInsertStmt;
+ private Button wDoNotAddInsertStmt;
+
private TextVar wFilename;
private TextVar wExtension;
@@ -106,6 +113,12 @@ public class SQLFileOutputDialog extends BaseTransformDialog {
private final SQLFileOutputMeta input;
+ private TableView wFields;
+
+ private Button wSpecifyFields;
+
+ private Button wGet;
+
public SQLFileOutputDialog(
Shell parent,
IVariables variables,
@@ -267,6 +280,7 @@ public class SQLFileOutputDialog extends BaseTransformDialog {
@Override
public void widgetSelected(SelectionEvent arg0) {
activateTruncate();
+ activateDoNotAddInsertStmt();
input.setChanged();
}
};
@@ -299,13 +313,40 @@ public class SQLFileOutputDialog extends BaseTransformDialog {
};
wTruncate.addSelectionListener(lsSelTMod);
+ // Do not create insert statements
+ wlDoNotAddInsertStmt = new Label(wFileName, SWT.RIGHT);
+ wlDoNotAddInsertStmt.setText(
+ BaseMessages.getString(PKG, "SQLFileOutputDialog.DoNotAddInsertStatements.Label"));
+ PropsUi.setLook(wlDoNotAddInsertStmt);
+ FormData fdlDoNotAddInsertStmt = new FormData();
+ fdlDoNotAddInsertStmt.left = new FormAttachment(0, 0);
+ fdlDoNotAddInsertStmt.top = new FormAttachment(wTruncate, margin);
+ fdlDoNotAddInsertStmt.right = new FormAttachment(middle, -margin);
+ wlDoNotAddInsertStmt.setLayoutData(fdlDoNotAddInsertStmt);
+ wDoNotAddInsertStmt = new Button(wFileName, SWT.CHECK);
+ wDoNotAddInsertStmt.setToolTipText(
+ BaseMessages.getString(PKG, "SQLFileOutputDialog.DoNotAddInsertStatements.Tooltip"));
+ PropsUi.setLook(wDoNotAddInsertStmt);
+ FormData fdDoNotAddInsertStmt = new FormData();
+ fdDoNotAddInsertStmt.left = new FormAttachment(middle, 0);
+ fdDoNotAddInsertStmt.top = new FormAttachment(wlDoNotAddInsertStmt, 0, SWT.CENTER);
+ fdDoNotAddInsertStmt.right = new FormAttachment(100, 0);
+ wDoNotAddInsertStmt.setLayoutData(fdDoNotAddInsertStmt);
+ wDoNotAddInsertStmt.addSelectionListener(
+ new SelectionAdapter() {
+ @Override
+ public void widgetSelected(SelectionEvent e) {
+ input.setChanged();
+ }
+ });
+
// Start New Line For each statement
Label wlStartNewLine = new Label(wFileName, SWT.RIGHT);
wlStartNewLine.setText(BaseMessages.getString(PKG, "SQLFileOutputDialog.StartNewLine.Label"));
PropsUi.setLook(wlStartNewLine);
FormData fdlStartNewLine = new FormData();
fdlStartNewLine.left = new FormAttachment(0, 0);
- fdlStartNewLine.top = new FormAttachment(wTruncate, margin);
+ fdlStartNewLine.top = new FormAttachment(wDoNotAddInsertStmt, margin);
fdlStartNewLine.right = new FormAttachment(middle, -margin);
wlStartNewLine.setLayoutData(fdlStartNewLine);
wStartNewLine = new Button(wFileName, SWT.CHECK);
@@ -645,11 +686,27 @@ public class SQLFileOutputDialog extends BaseTransformDialog {
PropsUi.setLook(wContentComp);
wContentComp.setLayout(contentLayout);
+ Group wGFormatEncodage = new Group(wContentComp, SWT.SHADOW_NONE);
+ PropsUi.setLook(wGFormatEncodage);
+ wGFormatEncodage.setText(
+ BaseMessages.getString(PKG, "SQLFileOutputDialog.Group.FormatEncodageInfos.Label"));
+
+ FormLayout groupFormatEncodageLayout = new FormLayout();
+ groupFormatEncodageLayout.marginWidth = 10;
+ groupFormatEncodageLayout.marginHeight = 10;
+ wGFormatEncodage.setLayout(groupFormatEncodageLayout);
+
+ FormData fdGFormatEncodage = new FormData();
+ fdGFormatEncodage.left = new FormAttachment(0, margin);
+ fdGFormatEncodage.top = new FormAttachment(0, margin);
+ fdGFormatEncodage.right = new FormAttachment(100, -margin);
+ wGFormatEncodage.setLayoutData(fdGFormatEncodage);
+
// Prepare a list of possible formats...
String[] dats = Const.getDateFormats();
// format
- Label wlFormat = new Label(wContentComp, SWT.RIGHT);
+ Label wlFormat = new Label(wGFormatEncodage, SWT.RIGHT);
wlFormat.setText(BaseMessages.getString(PKG, "SQLFileOutputDialog.DateFormat.Label"));
PropsUi.setLook(wlFormat);
FormData fdlFormat = new FormData();
@@ -657,7 +714,8 @@ public class SQLFileOutputDialog extends BaseTransformDialog {
fdlFormat.top = new FormAttachment(0, margin);
fdlFormat.right = new FormAttachment(middle, -margin);
wlFormat.setLayoutData(fdlFormat);
- wFormat = new CCombo(wContentComp, SWT.BORDER | SWT.READ_ONLY);
+
+ wFormat = new CCombo(wGFormatEncodage, SWT.BORDER | SWT.READ_ONLY);
wFormat.setEditable(true);
PropsUi.setLook(wFormat);
wFormat.addModifyListener(lsMod);
@@ -672,7 +730,7 @@ public class SQLFileOutputDialog extends BaseTransformDialog {
}
// Encoding
- Label wlEncoding = new Label(wContentComp, SWT.RIGHT);
+ Label wlEncoding = new Label(wGFormatEncodage, SWT.RIGHT);
wlEncoding.setText(BaseMessages.getString(PKG, "SQLFileOutputDialog.Encoding.Label"));
PropsUi.setLook(wlEncoding);
FormData fdlEncoding = new FormData();
@@ -680,7 +738,7 @@ public class SQLFileOutputDialog extends BaseTransformDialog {
fdlEncoding.top = new FormAttachment(wFormat, margin);
fdlEncoding.right = new FormAttachment(middle, -margin);
wlEncoding.setLayoutData(fdlEncoding);
- wEncoding = new CCombo(wContentComp, SWT.BORDER | SWT.READ_ONLY);
+ wEncoding = new CCombo(wGFormatEncodage, SWT.BORDER | SWT.READ_ONLY);
wEncoding.setEditable(true);
PropsUi.setLook(wEncoding);
wEncoding.addModifyListener(lsMod);
@@ -706,6 +764,83 @@ public class SQLFileOutputDialog extends BaseTransformDialog {
}
});
+ // Specify table fields
+ wSpecifyFields = new Button(wContentComp, SWT.CHECK);
+ wSpecifyFields.setText(
+ BaseMessages.getString(PKG, "SQLFileOutputMeta.Content.SpecifyTableFields"));
+ PropsUi.setLook(wSpecifyFields);
+ FormData fdSpecifyFields = new FormData();
+ fdSpecifyFields.left = new FormAttachment(0, margin);
+ fdSpecifyFields.top = new FormAttachment(wGFormatEncodage, margin);
+ wSpecifyFields.setLayoutData(fdSpecifyFields);
+ wSpecifyFields.addSelectionListener(
+ new SelectionAdapter() {
+ @Override
+ public void widgetSelected(SelectionEvent e) {
+ activateFieldsTable();
+ input.setChanged();
+ }
+ });
+
+ Group wGFields = new Group(wContentComp, SWT.SHADOW_NONE);
+ PropsUi.setLook(wGFields);
+ wGFields.setText(BaseMessages.getString(PKG, "SQLFileOutputMeta.Group.SelectFields"));
+
+ FormLayout groupFieldsLayout = new FormLayout();
+ groupFieldsLayout.marginWidth = 10;
+ groupFieldsLayout.marginHeight = 10;
+ wGFields.setLayout(groupFieldsLayout);
+
+ FormData fdGFields = new FormData();
+ fdGFields.left = new FormAttachment(0, margin);
+ fdGFields.top = new FormAttachment(wSpecifyFields, margin);
+ fdGFields.right = new FormAttachment(100, -margin);
+ fdGFields.bottom = new FormAttachment(100, -margin);
+ wGFields.setLayoutData(fdGFields);
+
+ wGet = new Button(wGFields, SWT.PUSH);
+ wGet.setText(BaseMessages.getString(PKG, "System.Button.GetFields"));
+ PropsUi.setLook(wGet);
+ FormData fdGet = new FormData();
+ fdGet.left = new FormAttachment(0, 0);
+ fdGet.bottom = new FormAttachment(100, 0);
+ wGet.setLayoutData(fdGet);
+ wGet.addListener(SWT.Selection, e -> get());
+
+ // Select fields table
+ ColumnInfo[] colinf =
+ new ColumnInfo[] {
+ new ColumnInfo(
+ BaseMessages.getString(PKG, "SQLFileOutputMeta.Content.Fieldname"),
+ ColumnInfo.COLUMN_TYPE_CCOMBO,
+ new String[] {BaseMessages.getString(PKG, "SQLFileOutputMeta.Content.Loading")},
+ false),
+ new ColumnInfo(
+ BaseMessages.getString(PKG, "SQLFileOutputMeta.Content.RenameTo"),
+ ColumnInfo.COLUMN_TYPE_TEXT)
+ };
+
+ colinf[0].setWidth(150); // width for "Name"
+ colinf[1].setWidth(100); // width for "Rename"
+
+ int nrFields = 0;
+ wFields =
+ new TableView(
+ variables,
+ wGFields,
+ SWT.BORDER | SWT.FULL_SELECTION | SWT.MULTI,
+ colinf,
+ nrFields,
+ null,
+ props);
+
+ FormData fdFields = new FormData();
+ fdFields.left = new FormAttachment(0, 0);
+ fdFields.top = new FormAttachment(0, 0);
+ fdFields.right = new FormAttachment(100, 0);
+ fdFields.bottom = new FormAttachment(wGet, -margin);
+ wFields.setLayoutData(fdFields);
+
wContentComp.setLayoutData(wContentComp);
wContentComp.layout();
wContentTab.setControl(wContentComp);
@@ -745,6 +880,21 @@ public class SQLFileOutputDialog extends BaseTransformDialog {
getData();
activateTruncate();
+ activateDoNotAddInsertStmt();
+
+ try {
+ IRowMeta prevFields = pipelineMeta.getPrevTransformFields(variables, transformName);
+ if (prevFields != null) {
+ colinf[0].setComboValues(prevFields.getFieldNames());
+ }
+ } catch (HopException ke) {
+ new ErrorDialog(
+ shell,
+ BaseMessages.getString(PKG, "System.Dialog.GetFieldsFailed.Title"),
+ BaseMessages.getString(PKG, "System.Dialog.GetFieldsFailed.Message"),
+ ke);
+ }
+
input.setChanged(changed);
focusTransformName();
BaseDialog.defaultShellHandling(shell, c -> ok(), c -> cancel());
@@ -752,6 +902,13 @@ public class SQLFileOutputDialog extends BaseTransformDialog {
return transformName;
}
+ private void activateFieldsTable() {
+ boolean specify = wSpecifyFields.getSelection();
+ wFields.setEnabled(specify);
+ wFields.table.setEnabled(specify);
+ wGet.setEnabled(specify);
+ }
+
private void activateTruncate() {
wlTruncate.setEnabled(!wAddCreate.getSelection());
@@ -761,6 +918,15 @@ public class SQLFileOutputDialog extends BaseTransformDialog {
}
}
+ private void activateDoNotAddInsertStmt() {
+ boolean createTable = wAddCreate.getSelection();
+ wlDoNotAddInsertStmt.setEnabled(createTable);
+ wDoNotAddInsertStmt.setEnabled(createTable);
+ if (!createTable) {
+ wDoNotAddInsertStmt.setSelection(false);
+ }
+ }
+
private void setEncodings() {
// Encoding of the text file:
if (!gotEncodings) {
@@ -803,6 +969,7 @@ public class SQLFileOutputDialog extends BaseTransformDialog {
wAddTransformNr.setSelection(input.getFile().isTransformNrInFilename());
wTruncate.setSelection(input.isTruncateTable());
+ wDoNotAddInsertStmt.setSelection(input.isDoNotAddInsertStatements());
wAddCreate.setSelection(input.isCreateTable());
if (input.getEncoding() != null) {
@@ -811,6 +978,18 @@ public class SQLFileOutputDialog extends BaseTransformDialog {
wAddToResult.setSelection(input.isAddToResult());
wStartNewLine.setSelection(input.isStartNewLine());
wDoNotOpenNewFileInit.setSelection(input.getFile().isDoNotOpenNewFileInit());
+
+ if (input.getSqlFileOutputFields() != null && !input.getSqlFileOutputFields().isEmpty()) {
+ wFields.table.removeAll();
+ for (SQLFileOutputField field : input.getSqlFileOutputFields()) {
+ wFields.add(Const.NVL(field.getName(), ""), Const.NVL(field.getRename(), ""));
+ }
+ }
+ wFields.setRowNums();
+ wFields.optWidth(true);
+
+ wSpecifyFields.setSelection(input.isSpecifyFields());
+ activateFieldsTable();
}
private void cancel() {
@@ -824,6 +1003,7 @@ public class SQLFileOutputDialog extends BaseTransformDialog {
info.setTableName(wTable.getText());
info.setConnection(wConnection.getText());
info.setTruncateTable(wTruncate.getSelection());
+ info.setDoNotAddInsertStatements(wDoNotAddInsertStmt.getSelection());
info.getFile().setCreateParentFolder(wCreateParentFolder.getSelection());
info.setCreateTable(wAddCreate.getSelection());
@@ -841,6 +1021,20 @@ public class SQLFileOutputDialog extends BaseTransformDialog {
info.setAddToResult(wAddToResult.getSelection());
info.setStartNewLine(wStartNewLine.getSelection());
info.getFile().setDoNotOpenNewFileInit(wDoNotOpenNewFileInit.getSelection());
+ info.setSpecifyFields(wSpecifyFields.getSelection());
+
+ int nrFields = wFields.nrNonEmpty();
+ List<SQLFileOutputField> targetFields = new ArrayList<>();
+ for (int i = 0; i < nrFields; i++) {
+ org.eclipse.swt.widgets.TableItem item = wFields.getNonEmpty(i);
+
+ SQLFileOutputField field = new SQLFileOutputField();
+ field.setName(item.getText(1)); // get column 1 (Fieldname)
+ field.setRename(item.getText(2)); // get column 2 (Rename to)
+
+ targetFields.add(field);
+ }
+ info.setSqlFileOutputFields(targetFields);
}
private void ok() {
@@ -924,6 +1118,32 @@ public class SQLFileOutputDialog extends BaseTransformDialog {
}
}
+ private void get() {
+ try {
+ IRowMeta r = pipelineMeta.getPrevTransformFields(variables, transformName);
+ if (r != null && !r.isEmpty()) {
+ BaseTransformDialog.getFieldsFromPrevious(
+ r,
+ wFields,
+ 1,
+ new int[] {1}, // Only fill the Name column, Rename stays empty unless set by the user
+ new int[] {},
+ -1,
+ -1,
+ null);
+
+ wSpecifyFields.setSelection(true);
+ activateFieldsTable();
+ }
+ } catch (HopException ke) {
+ new ErrorDialog(
+ shell,
+ BaseMessages.getString(PKG, "System.Dialog.GetFieldsFailed.Title"),
+ BaseMessages.getString(PKG, "System.Dialog.GetFieldsFailed.Message"),
+ ke);
+ }
+ }
+
// Generate code for create table...
// Conversions done by Database
//
diff --git a/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputField.java b/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputField.java
new file mode 100644
index 0000000000..8bc3137015
--- /dev/null
+++ b/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputField.java
@@ -0,0 +1,91 @@
+/*
+ * 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.sqlfileoutput;
+
+import java.util.Objects;
+import lombok.Getter;
+import lombok.Setter;
+import org.apache.hop.metadata.api.HopMetadataProperty;
+
+@Getter
+@Setter
+public class SQLFileOutputField {
+
+ public SQLFileOutputField() {}
+
+ public SQLFileOutputField(SQLFileOutputField f) {
+ this.name = f.name;
+ this.rename = f.rename;
+ }
+
+ /** Select: Name of the selected field */
+ @HopMetadataProperty(
+ key = "name",
+ injectionKey = "FIELD_NAME",
+ injectionKeyDescription = "SQLFileOutput.Injection.FIELD_NAME")
+ private String name;
+
+ /** Select: Rename to ... */
+ @HopMetadataProperty(
+ key = "rename",
+ injectionKey = "FIELD_RENAME",
+ injectionKeyDescription = "SQLFileOutput.Injection.FIELD_RENAME")
+ private String rename;
+
+ @Override
+ public boolean equals(Object o) {
+ if (this == o) {
+ return true;
+ }
+ if (o == null || getClass() != o.getClass()) {
+ return false;
+ }
+ SQLFileOutputField that = (SQLFileOutputField) o;
+ return Objects.equals(name, that.name);
+ }
+
+ @Override
+ public int hashCode() {
+ return Objects.hash(name);
+ }
+
+ /**
+ * Gets name
+ *
+ * @return value of name
+ */
+ public String getName() {
+ return name;
+ }
+
+ /**
+ * @param name The name to set
+ */
+ public void setName(String name) {
+ this.name = name;
+ }
+
+ /**
+ * Gets type
+ *
+ * @return value of type
+ */
+ public String getRename() {
+ return rename;
+ }
+}
diff --git a/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputMeta.java b/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputMeta.java
index 0365295844..efbafc947b 100644
--- a/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputMeta.java
+++ b/plugins/transforms/sqlfileoutput/src/main/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputMeta.java
@@ -18,6 +18,7 @@
package org.apache.hop.pipeline.transforms.sqlfileoutput;
import java.text.SimpleDateFormat;
+import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
@@ -36,6 +37,7 @@ 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.RowMeta;
import org.apache.hop.core.util.Utils;
import org.apache.hop.core.variables.IVariables;
import org.apache.hop.core.vfs.HopVfs;
@@ -83,6 +85,9 @@ public class SQLFileOutputMeta extends BaseTransformMeta<SQLFileOutput, SQLFileO
@HopMetadataProperty(key = "truncate")
private boolean truncateTable;
+ @HopMetadataProperty(key = "DoNotAddInsertStatements")
+ private boolean doNotAddInsertStatements;
+
@HopMetadataProperty(key = "AddToResult")
private boolean addToResult;
@@ -104,6 +109,12 @@ public class SQLFileOutputMeta extends BaseTransformMeta<SQLFileOutput, SQLFileO
@HopMetadataProperty(key = "StartNewLine")
private boolean startNewLine;
+ @HopMetadataProperty(groupKey = "fields", key = "field")
+ private List<SQLFileOutputField> sqlFileOutputFields = new ArrayList<>();
+
+ @HopMetadataProperty(key = "specifyFields")
+ private boolean specifyFields;
+
/** Added for backwards compatibility with older XML using "extention". */
@Override
public void convertLegacyXml(Node node) throws HopException {
@@ -118,6 +129,41 @@ public class SQLFileOutputMeta extends BaseTransformMeta<SQLFileOutput, SQLFileO
}
}
+ /**
+ * Returns the layout of the rows written to the SQL file. When "Specify table fields" is
+ * disabled, this is the incoming row. When it is enabled, only the selected fields are kept, in
+ * the order of the grid, with their new name if a rename is given. Runtime, the SQL button,
+ * check() and analyseImpact() all rely on this so they describe the same columns.
+ *
+ * @param inputRowMeta the layout of the incoming rows
+ * @return the layout of the rows written in the CREATE TABLE and INSERT statements
+ * @throws HopTransformException when the option is enabled with an empty grid, or when a selected
+ * field is not found in the incoming rows
+ */
+ public IRowMeta getSqlRowMeta(IRowMeta inputRowMeta) throws HopTransformException {
+ if (!specifyFields) {
+ return inputRowMeta.clone();
+ }
+ if (sqlFileOutputFields == null || sqlFileOutputFields.isEmpty()) {
+ throw new HopTransformException(
+ BaseMessages.getString(PKG, "SQLFileOutputMeta.Exception.NoFieldsSpecified"));
+ }
+ IRowMeta sqlRowMeta = new RowMeta();
+ for (SQLFileOutputField field : sqlFileOutputFields) {
+ int index = inputRowMeta.indexOfValue(field.getName());
+ if (index < 0) {
+ throw new HopTransformException(
+ BaseMessages.getString(PKG, "SQLFileOutput.Exception.FieldNotFound", field.getName()));
+ }
+ IValueMeta valueMeta = inputRowMeta.getValueMeta(index).clone();
+ if (!Utils.isEmpty(field.getRename())) {
+ valueMeta.setName(field.getRename());
+ }
+ sqlRowMeta.addValueMeta(valueMeta);
+ }
+ return sqlRowMeta;
+ }
+
public String[] getFiles(IVariables variables, String fileName) {
int copies = 1;
int splits = 1;
@@ -275,9 +321,13 @@ public class SQLFileOutputMeta extends BaseTransformMeta<SQLFileOutput, SQLFileO
transformMeta);
remarks.add(cr);
- // Starting from prev...
- for (int i = 0; i < prev.size(); i++) {
- IValueMeta pv = prev.getValueMeta(i);
+ // Compare the table with the columns that will actually be written
+ // (only the selected fields, renamed if needed, when specifyFields is set)
+ IRowMeta sqlRowMeta = getSqlRowMeta(prev);
+
+ // starting from the written columns...
+ for (int i = 0; i < sqlRowMeta.size(); i++) {
+ IValueMeta pv = sqlRowMeta.getValueMeta(i);
int idx = r.indexOfValue(pv.getName());
if (idx < 0) {
errorMessage +=
@@ -309,7 +359,7 @@ public class SQLFileOutputMeta extends BaseTransformMeta<SQLFileOutput, SQLFileO
// Starting from table fields in r...
for (int i = 0; i < r.size(); i++) {
IValueMeta rv = r.getValueMeta(i);
- int idx = prev.indexOfValue(rv.getName());
+ int idx = sqlRowMeta.indexOfValue(rv.getName());
if (idx < 0) {
errorMessage +=
"\t\t" + rv.getName() + " (" + rv.getTypeDesc() + ")" + Const.CR;
@@ -450,21 +500,22 @@ public class SQLFileOutputMeta extends BaseTransformMeta<SQLFileOutput, SQLFileO
}
// The values that are entering this transform are in "prev":
if (prev != null) {
- for (int i = 0; i < prev.size(); i++) {
- IValueMeta v = prev.getValueMeta(i);
- DatabaseImpact ii =
- new DatabaseImpact(
- DatabaseImpact.TYPE_IMPACT_WRITE,
- pipelineMeta.getName(),
- transformMeta.getName(),
- databaseMeta.getDatabaseName(),
- tableName,
- v.getName(),
- v.getName(),
- v != null ? v.getOrigin() : "?",
- "",
- "Type = " + v.toStringMeta());
- impact.add(ii);
+ if (specifyFields) {
+ // Only the selected fields are written, under their new name if one is given
+ for (SQLFileOutputField field : sqlFileOutputFields) {
+ IValueMeta v = prev.searchValueMeta(field.getName());
+ if (v == null) {
+ continue; // reported by check() and at runtime
+ }
+ String column = Utils.isEmpty(field.getRename()) ? field.getName() : field.getRename();
+ impact.add(createWriteImpact(pipelineMeta, transformMeta, databaseMeta, column, v));
+ }
+ } else {
+ for (int i = 0; i < prev.size(); i++) {
+ IValueMeta v = prev.getValueMeta(i);
+ impact.add(
+ createWriteImpact(pipelineMeta, transformMeta, databaseMeta, v.getName(), v));
+ }
}
}
} catch (HopException e) {
@@ -473,6 +524,25 @@ public class SQLFileOutputMeta extends BaseTransformMeta<SQLFileOutput, SQLFileO
}
}
+ private DatabaseImpact createWriteImpact(
+ PipelineMeta pipelineMeta,
+ TransformMeta transformMeta,
+ DatabaseMeta databaseMeta,
+ String column,
+ IValueMeta v) {
+ return new DatabaseImpact(
+ DatabaseImpact.TYPE_IMPACT_WRITE,
+ pipelineMeta.getName(),
+ transformMeta.getName(),
+ databaseMeta.getDatabaseName(),
+ tableName,
+ column,
+ v.getName(),
+ v.getOrigin(),
+ "",
+ "Type = " + v.toStringMeta());
+ }
+
@Override
public SqlStatement getSqlStatements(
IVariables variables,
@@ -497,7 +567,8 @@ public class SQLFileOutputMeta extends BaseTransformMeta<SQLFileOutput, SQLFileO
String schemaTable =
databaseMeta.getQuotedSchemaTableCombination(variables, schemaName, tableName);
- String crTable = db.getDDL(schemaTable, prev);
+ // Build the DDL from the columns that will actually be written
+ String crTable = db.getDDL(schemaTable, getSqlRowMeta(prev));
// Empty string means: nothing to do: set it to null...
if (Utils.isEmpty(crTable)) {
@@ -509,6 +580,9 @@ public class SQLFileOutputMeta extends BaseTransformMeta<SQLFileOutput, SQLFileO
retVal.setError(
BaseMessages.getString(
PKG, "SQLFileOutputMeta.Error.ErrorConnecting", dbe.getMessage()));
+ } catch (HopTransformException e) {
+ // Invalid field selection: empty grid or unknown field
+ retVal.setError(e.getMessage());
} finally {
db.close();
}
diff --git a/plugins/transforms/sqlfileoutput/src/main/resources/org/apache/hop/pipeline/transforms/sqlfileoutput/messages/messages_en_US.properties b/plugins/transforms/sqlfileoutput/src/main/resources/org/apache/hop/pipeline/transforms/sqlfileoutput/messages/messages_en_US.properties
index 9d7d45b4bc..ade9b5dedf 100644
--- a/plugins/transforms/sqlfileoutput/src/main/resources/org/apache/hop/pipeline/transforms/sqlfileoutput/messages/messages_en_US.properties
+++ b/plugins/transforms/sqlfileoutput/src/main/resources/org/apache/hop/pipeline/transforms/sqlfileoutput/messages/messages_en_US.properties
@@ -16,6 +16,9 @@
#
SQLFileOutput.Description=Output SQL INSERT statements to file
+SQLFileOutput.Exception.FieldNotFound=Field [{0}] was not found in the input stream
+SQLFileOutput.Injection.FIELD_NAME=Name of the incoming field to write
+SQLFileOutput.Injection.FIELD_RENAME=Column name to use in the generated SQL (optional)
SQLFileOutput.Init.ConnectionMissing=Database connection is missing for transform [{0}]\!
SQLFileOutput.Name=SQL file output
SQLFileOutputDialog.AddDate.Label=Include date in filename
@@ -36,6 +39,8 @@ SQLFileOutputDialog.CreateTable.Label=Add 'create table' statement
SQLFileOutputDialog.CreateTable.Tooltip=Add 'create table' statement CREATE TABLE TableName (...);'
SQLFileOutputDialog.DateFormat.Label=Date Format
SQLFileOutputDialog.DialogTitle=SQL file output
+SQLFileOutputDialog.DoNotAddInsertStatements.Label=Do not add 'insert into' statements
+SQLFileOutputDialog.DoNotAddInsertStatements.Tooltip=Only generate the create table statement, without an insert into statement for each row
SQLFileOutputDialog.DoNotOpenNewFileInit.Label=Do not create at start
SQLFileOutputDialog.DoNotOpenNewFileInit.Tooltip=Do not open create at start
SQLFileOutputDialog.Encoding.Label=Encoding
@@ -43,6 +48,7 @@ SQLFileOutputDialog.Filename.Label=Filename
SQLFileOutputDialog.GeneralTab.TabTitle=General
SQLFileOutputDialog.Group.ConnectionInfos.Label=Connection
SQLFileOutputDialog.Group.File.Label=Output File
+SQLFileOutputDialog.Group.FormatEncodageInfos.Label=Format && Encoding
SQLFileOutputDialog.Log.LookingAtConnection=Looking at connection\: {0}
SQLFileOutputDialog.NoFilesFound.DialogMessage=No files found\! Please check the filename/directory and options.
SQLFileOutputDialog.NoSQL.DialogMessage=No SQL needs to be executed to make this transform function properly.
@@ -56,8 +62,8 @@ SQLFileOutputDialog.TargetSchema.Label=Target schema
SQLFileOutputDialog.TargetSchema.Tooltip=Specify here the schema name (SchemaName) .\n The statement will be like ''INSERT INTO SchemaName.TableName ...''
SQLFileOutputDialog.TargetTable.Label=Target table
SQLFileOutputDialog.TargetTable.Tooltip=Specify here the target table name (TableName) .\n The statement will be like ''INSERT INTO TableName ...''
-SQLFileOutputDialog.TruncateTable.Label=Add 'truncate table' statement
-SQLFileOutputDialog.TruncateTable.Tooltip=Add 'truncate table' statement
+SQLFileOutputDialog.TruncateTable.Label=Add 'truncate table' statement
+SQLFileOutputDialog.TruncateTable.Tooltip=Add 'truncate table' statement
SQLFileOutputLog.OutputSQL=Output SQL statement \: {0}
SQLFileOutputMeta.CheckResult.AllFieldsFound=All fields in the table are found in the input stream, coming from previous transforms
SQLFileOutputMeta.CheckResult.AllFieldsFoundInOutput=All fields, coming from previous transforms, are found in the output table
@@ -69,7 +75,7 @@ SQLFileOutputMeta.CheckResult.FieldsNotFound=Fields in table, not found in input
SQLFileOutputMeta.CheckResult.FieldsNotFoundInOutput=Fields in input stream, not found in output table\:\n\n{0}
SQLFileOutputMeta.CheckResult.FieldsReceived=Transform is connected to previous one, receiving {0} fields
SQLFileOutputMeta.CheckResult.NoConnection=Please select or create a connection to use
-SQLFileOutputMeta.CheckResult.DatabaseErrorOccurred=A database error occurred\:
+SQLFileOutputMeta.CheckResult.DatabaseErrorOccurred=A database error occurred\:
SQLFileOutputMeta.CheckResult.NoFields=Couldn''t find fields from previous transforms, check the hops...\!
SQLFileOutputMeta.CheckResult.NoTableName=No table name was entered in this transform.
SQLFileOutputMeta.CheckResult.TableAccessible=Table [{0}] exists and is accessible
@@ -77,12 +83,18 @@ SQLFileOutputMeta.CheckResult.TableError=Table [{0}] doesn''t exist or can''t be
SQLFileOutputMeta.CheckResult.TableNotAccessible=Couldn''t read the table info, please check the table-name & permissions.
SQLFileOutputMeta.CheckResult.TableOk=Table [{0}] is readeable and we got the fields from it.
SQLFileOutputMeta.CheckResult.UndefinedError=An error occurred\: {0}
+SQLFileOutputMeta.Content.Fieldname=Name
+SQLFileOutputMeta.Content.Loading=Loading
+SQLFileOutputMeta.Content.RenameTo=Rename
+SQLFileOutputMeta.Content.SpecifyTableFields=Specify table fields
SQLFileOutputMeta.Error.ErrorConnecting=I was unable to connect to the database to verify the status of the table\: {0}
SQLFileOutputMeta.Error.NoConnection=There is no connection defined in this transform.
SQLFileOutputMeta.Error.NoInput=Not receiving any fields from previous transforms. Check the previous transforms for errors & the connecting hops.
SQLFileOutputMeta.Exception.ConnectionNotDefined=Unable to determine the required fields because the database connection wasn''t defined.
SQLFileOutputMeta.Exception.ErrorGettingFields=Unable to determine the required fields.
+SQLFileOutputMeta.Exception.NoFieldsSpecified=Specify table fields is enabled but no field is selected
SQLFileOutputMeta.Exception.TableNotFound=Unable to determine the required fields because the specified database table couldn''t be found.
SQLFileOutputMeta.Exception.TableNotSpecified=Unable to determine the required fields because the database table name wasn''t specified.
+SQLFileOutputMeta.Group.SelectFields=Select fields
SQLFileOutputMeta.keyword=sql,insert,script,dump,ddl
-SQLFileOutputMeta.Log.ErrorInTransform=Error in transform, asking everyone to stop because of\:
+SQLFileOutputMeta.Log.ErrorInTransform=Error in transform, asking everyone to stop because of\:
\ No newline at end of file
diff --git a/plugins/transforms/sqlfileoutput/src/main/resources/org/apache/hop/pipeline/transforms/sqlfileoutput/messages/messages_fr_FR.properties b/plugins/transforms/sqlfileoutput/src/main/resources/org/apache/hop/pipeline/transforms/sqlfileoutput/messages/messages_fr_FR.properties
index ff803604a4..f2dcb53113 100644
--- a/plugins/transforms/sqlfileoutput/src/main/resources/org/apache/hop/pipeline/transforms/sqlfileoutput/messages/messages_fr_FR.properties
+++ b/plugins/transforms/sqlfileoutput/src/main/resources/org/apache/hop/pipeline/transforms/sqlfileoutput/messages/messages_fr_FR.properties
@@ -18,6 +18,9 @@
#
SQLFileOutput.Description=Export instructions INSERT SQL vers un fichier
+SQLFileOutput.Exception.FieldNotFound=Le champ [{0}] est introuvable dans le flux d''entr\u00E9e
+SQLFileOutput.Injection.FIELD_NAME=Nom du champ entrant \u00E0 \u00E9crire
+SQLFileOutput.Injection.FIELD_RENAME=Nom de colonne \u00E0 utiliser dans le SQL g\u00E9n\u00E9r\u00E9 (optionnel)
SQLFileOutput.Init.ConnectionMissing=La connexion \u00E0 la base de donn\u00E9es n''a pas \u00E9t\u00E9 d\u00E9finie ou est introuvable pour la transformation [{0}]\!
SQLFileOutput.Name=Export instructions SQL vers fichier
SQLFileOutputDialog.AddDate.Label=Inclure date dans nom fichier
@@ -38,13 +41,16 @@ SQLFileOutputDialog.CreateTable.Label=Ajouter instruction cr\u00E9ation table
SQLFileOutputDialog.CreateTable.Tooltip=Ajouter instruction 'create table' CREATE TABLE NomTable (...);'
SQLFileOutputDialog.DateFormat.Label=Format Date
SQLFileOutputDialog.DialogTitle=Export SQL vers fichier
+SQLFileOutputDialog.DoNotAddInsertStatements.Label=Ne pas ajouter les instructions INSERT INTO
+SQLFileOutputDialog.DoNotAddInsertStatements.Tooltip=G\u00E9n\u00E9rer uniquement le CREATE TABLE, sans instruction INSERT INTO pour chaque ligne
SQLFileOutputDialog.DoNotOpenNewFileInit.Label=Ne pas cr\u00E9er le fichier au d\u00E9marrage
SQLFileOutputDialog.DoNotOpenNewFileInit.Tooltip=S\u00E9lectionnez cette option si vous ne souhaitez pas cr\u00E9er de fichier \r\nau d\u00E9marrage du pipeline.\r\nLe fichier sera cr\u00E9\u00E9 \u00E0 la premi\u00E8re ligne re\u00E7ue.
SQLFileOutputDialog.Encoding.Label=Encodage
-SQLFileOutputDialog.Filename.Label=Fichier
+SQLFileOutputDialog.Filename.Label=Fichier
SQLFileOutputDialog.GeneralTab.TabTitle=G\u00E9n\u00E9ral
SQLFileOutputDialog.Group.ConnectionInfos.Label=Connexion
SQLFileOutputDialog.Group.File.Label=Fichier r\u00E9sultat
+SQLFileOutputDialog.Group.FormatEncodageInfos.Label=Format && Encodage
SQLFileOutputDialog.Log.LookingAtConnection=Recherche connexion\: {0}
SQLFileOutputDialog.NoFilesFound.DialogMessage=Aucun fichier trouv\u00E9\! Veuillez svp v\u00E9rifier les param\u00E8tres.
SQLFileOutputDialog.NoSQL.DialogMessage=No SQL needs to be executed to make this transform function properly.
@@ -54,18 +60,18 @@ SQLFileOutputDialog.SelectOutputFiles.DialogTitle=Fichier en sortie
SQLFileOutputDialog.ShowFiles.Button=&Afficher nom fichier...
SQLFileOutputDialog.SplitEvery.Label=Cr\u00E9er un fichier tous les...lignes
SQLFileOutputDialog.StartNewLine.Label=Retour \u00E0 la ligne \u00E0 chaque instruction
-SQLFileOutputDialog.TargetSchema.Label=Sch\u00E9ma cible
+SQLFileOutputDialog.TargetSchema.Label=Sch\u00E9ma cible
SQLFileOutputDialog.TargetSchema.Tooltip=Indiquez \u00E0 ce niveau le nom du sch\u00E9ma (NomSch\u00E9ma) .\n L''instruction SQL sera sous la forme ''INSERT INTO NomSch\u00E9ma.NomTable ...''
-SQLFileOutputDialog.TargetTable.Label=Table cible
+SQLFileOutputDialog.TargetTable.Label=Table cible
SQLFileOutputDialog.TargetTable.Tooltip=Indiquez \u00E0 ce niveau le nom de la table cible (NomTable) .\n L''instruction SQL sera sous la forme ''INSERT INTO NomTable ...''.\n Bien entendu, l''existance de la table cible n''est pas n\u00E9c\u00E9ssaire car cette transformation g\u00E9n\u00E8re\n uniquement des instructions SQL.
SQLFileOutputDialog.TruncateTable.Label=Ajouter instruction tronquage
-SQLFileOutputDialog.TruncateTable.Tooltip=Ajouter instruction ''TRUNCATE table'' au d\u00E9but du fichier
+SQLFileOutputDialog.TruncateTable.Tooltip=Ajouter instruction ''TRUNCATE table'' au d\u00E9but du fichier
SQLFileOutputLog.OutputSQL=Instructions SQL \: {0}
SQLFileOutputMeta.CheckResult.AllFieldsFound=Tous les champs de la table ont \u00E9t\u00E9 trouv\u00E9s dans le flux d''entre\u00E9.
SQLFileOutputMeta.CheckResult.AllFieldsFoundInOutput=Tous les champs du flux d''entr\u00E9e ont \u00E9t\u00E9 trouv\u00E9 dans la table cible
SQLFileOutputMeta.CheckResult.ConnectionExists=La connexion existe
SQLFileOutputMeta.CheckResult.ConnectionOk=La connexion \u00E0 la base de donn\u00E9es est correcte
-SQLFileOutputMeta.CheckResult.DatabaseErrorOccurred=Apache Hop a rencontr\u00E9 une erreur de base de donn\u00E9es:
+SQLFileOutputMeta.CheckResult.DatabaseErrorOccurred=Apache Hop a rencontr\u00E9 une erreur de base de donn\u00E9es:
SQLFileOutputMeta.CheckResult.ExpectedInputError=Aucun flux en provenance des autres transformations\!
SQLFileOutputMeta.CheckResult.ExpectedInputOk=La transformation re\u00E7oit des informations depuis les transformations pr\u00E9c\u00E9dentes.
SQLFileOutputMeta.CheckResult.FieldsNotFound=Champs de la table non trouv\u00E9s dans le flux d''entr\u00E9e:\n\n{0}
@@ -79,11 +85,17 @@ SQLFileOutputMeta.CheckResult.TableError=La table [{0}] est introuvable dans la
SQLFileOutputMeta.CheckResult.TableNotAccessible=Impossible de lire les informations au niveau de la table, veuillez svp vous assurez que vous disposez des droits suffisants.
SQLFileOutputMeta.CheckResult.TableOk=La table [{0}] est acc\u00E9ssible. Apache Hop a r\u00E9cup\u00E9r\u00E9 les informations sur les champs.
SQLFileOutputMeta.CheckResult.UndefinedError=Apache Hop a rencontr\u00E9 une erreur\: {0}
+SQLFileOutputMeta.Content.Fieldname=Nom
+SQLFileOutputMeta.Content.Loading=Chargement
+SQLFileOutputMeta.Content.RenameTo=Renommer
+SQLFileOutputMeta.Content.SpecifyTableFields=S\u00E9lectionner champs
SQLFileOutputMeta.Error.ErrorConnecting=Impossible de se connecter \u00E0 la base de donn\u00E9es afin de v\u00E9rifier le status de la table\: {0}
SQLFileOutputMeta.Error.NoConnection=Aucune connexion d\u00E9finie au niveau de cette entr\u00E9e.
SQLFileOutputMeta.Error.NoInput=Aucun champs en provenace des autres transformations. Veuillez svp v\u00E9rifier les liens entres les entr\u00E9es.
SQLFileOutputMeta.Exception.ConnectionNotDefined=La connexion \u00E0 la base de donn\u00E9es n''a pas \u00E9t\u00E9 d\u00E9finie\!
SQLFileOutputMeta.Exception.ErrorGettingFields=Impossible de d\u00E9terminer les champs requis.
+SQLFileOutputMeta.Exception.NoFieldsSpecified=La s\u00E9lection des champs est activ\u00E9e mais la liste des champs est vide
SQLFileOutputMeta.Exception.TableNotFound=La table sp\u00E9cifi\u00E9e est introuvable\!
SQLFileOutputMeta.Exception.TableNotSpecified=La table cible n''a pas \u00E9t\u00E9 renseign\u00E9e\!
-SQLFileOutputMeta.Log.ErrorInTransform=Apache Hop a rencontr\u00E9 une erreur au niveau de cette transformation \!
+SQLFileOutputMeta.Group.SelectFields=S\u00E9lection des champs
+SQLFileOutputMeta.Log.ErrorInTransform=Apache Hop a rencontr\u00E9 une erreur au niveau de cette transformation \!
\ No newline at end of file
diff --git a/plugins/transforms/sqlfileoutput/src/test/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputMetaTest.java b/plugins/transforms/sqlfileoutput/src/test/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputMetaTest.java
index 1095c9486a..1f69492353 100644
--- a/plugins/transforms/sqlfileoutput/src/test/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputMetaTest.java
+++ b/plugins/transforms/sqlfileoutput/src/test/java/org/apache/hop/pipeline/transforms/sqlfileoutput/SQLFileOutputMetaTest.java
@@ -18,8 +18,16 @@ package org.apache.hop.pipeline.transforms.sqlfileoutput;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertFalse;
+import static org.junit.jupiter.api.Assertions.assertThrows;
import static org.junit.jupiter.api.Assertions.assertTrue;
+import java.util.List;
+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.RowMeta;
+import org.apache.hop.core.row.value.ValueMetaInteger;
+import org.apache.hop.core.row.value.ValueMetaString;
import org.apache.hop.pipeline.transform.TransformSerializationTestUtil;
import org.junit.jupiter.api.Test;
@@ -40,6 +48,11 @@ class SQLFileOutputMetaTest {
assertFalse(meta.getFile().isFileAppended());
assertFalse(meta.getFile().isTransformNrInFilename());
assertEquals(0, meta.getFile().getSplitEvery());
+
+ // Pipelines saved before these options existed must keep the old behaviour
+ assertFalse(meta.isDoNotAddInsertStatements());
+ assertFalse(meta.isSpecifyFields());
+ assertTrue(meta.getSqlFileOutputFields().isEmpty());
}
@Test
@@ -62,4 +75,94 @@ class SQLFileOutputMetaTest {
clone.getFile().isTransformNrInFilename(), meta.getFile().isTransformNrInFilename());
assertEquals(clone.getFile().getSplitEvery(), meta.getFile().getSplitEvery());
}
+
+ @Test
+ void testSerializationWithSelectedFields() throws Exception {
+ SQLFileOutputMeta meta =
+ TransformSerializationTestUtil.testSerialization(
+ "/sql-file-output-transform-fields.xml", SQLFileOutputMeta.class);
+
+ assertTrue(meta.isDoNotAddInsertStatements());
+ assertTrue(meta.isSpecifyFields());
+ assertEquals(2, meta.getSqlFileOutputFields().size());
+ assertEquals("id", meta.getSqlFileOutputFields().get(0).getName());
+ assertEquals("client_id", meta.getSqlFileOutputFields().get(0).getRename());
+ assertEquals("label", meta.getSqlFileOutputFields().get(1).getName());
+
+ SQLFileOutputMeta clone = (SQLFileOutputMeta) meta.clone();
+ assertEquals(meta.isDoNotAddInsertStatements(), clone.isDoNotAddInsertStatements());
+ assertEquals(meta.isSpecifyFields(), clone.isSpecifyFields());
+ assertEquals(meta.getSqlFileOutputFields().size(), clone.getSqlFileOutputFields().size());
+ for (int i = 0; i < meta.getSqlFileOutputFields().size(); i++) {
+ assertEquals(
+ meta.getSqlFileOutputFields().get(i).getName(),
+ clone.getSqlFileOutputFields().get(i).getName());
+ assertEquals(
+ meta.getSqlFileOutputFields().get(i).getRename(),
+ clone.getSqlFileOutputFields().get(i).getRename());
+ }
+ }
+
+ private static IRowMeta inputRowMeta() {
+ IRowMeta rowMeta = new RowMeta();
+ rowMeta.addValueMeta(new ValueMetaInteger("id"));
+ rowMeta.addValueMeta(new ValueMetaString("label"));
+ rowMeta.addValueMeta(new ValueMetaString("comment"));
+ return rowMeta;
+ }
+
+ private static SQLFileOutputField field(String name, String rename) {
+ SQLFileOutputField field = new SQLFileOutputField();
+ field.setName(name);
+ field.setRename(rename);
+ return field;
+ }
+
+ @Test
+ void testSqlRowMetaUsesAllFieldsByDefault() throws Exception {
+ SQLFileOutputMeta meta = new SQLFileOutputMeta();
+ meta.setDefault();
+
+ IRowMeta sqlRowMeta = meta.getSqlRowMeta(inputRowMeta());
+
+ assertEquals(3, sqlRowMeta.size());
+ assertEquals("id", sqlRowMeta.getValueMeta(0).getName());
+ assertEquals("label", sqlRowMeta.getValueMeta(1).getName());
+ assertEquals("comment", sqlRowMeta.getValueMeta(2).getName());
+ }
+
+ @Test
+ void testSqlRowMetaKeepsSelectedFieldsAndRenames() throws Exception {
+ SQLFileOutputMeta meta = new SQLFileOutputMeta();
+ meta.setDefault();
+ meta.setSpecifyFields(true);
+ meta.setSqlFileOutputFields(List.of(field("label", null), field("id", "client_id")));
+
+ IRowMeta sqlRowMeta = meta.getSqlRowMeta(inputRowMeta());
+
+ // Order of the grid, "comment" left out, "id" renamed
+ assertEquals(2, sqlRowMeta.size());
+ assertEquals("label", sqlRowMeta.getValueMeta(0).getName());
+ assertEquals("client_id", sqlRowMeta.getValueMeta(1).getName());
+ assertEquals(IValueMeta.TYPE_INTEGER, sqlRowMeta.getValueMeta(1).getType());
+ }
+
+ @Test
+ void testSqlRowMetaFailsWhenNoFieldIsSelected() {
+ SQLFileOutputMeta meta = new SQLFileOutputMeta();
+ meta.setDefault();
+ meta.setSpecifyFields(true);
+
+ assertThrows(HopTransformException.class, () -> meta.getSqlRowMeta(inputRowMeta()));
+ }
+
+ @Test
+ void testSqlRowMetaFailsOnUnknownField() {
+ SQLFileOutputMeta meta = new SQLFileOutputMeta();
+ meta.setDefault();
+ meta.setSpecifyFields(true);
+ meta.setSqlFileOutputFields(List.of(field("unknown", null)));
+
+ assertThrows(HopTransformException.class, () -> meta.getSqlRowMeta(inputRowMeta()));
+ }
}
diff --git a/plugins/transforms/sqlfileoutput/src/test/resources/sql-file-output-transform-fields.xml b/plugins/transforms/sqlfileoutput/src/test/resources/sql-file-output-transform-fields.xml
new file mode 100644
index 0000000000..62553b7084
--- /dev/null
+++ b/plugins/transforms/sqlfileoutput/src/test/resources/sql-file-output-transform-fields.xml
@@ -0,0 +1,68 @@
+<?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>SQL file output</name>
+ <type>SQLFileOutput</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <connection>${DATABASE_NAME}</connection>
+ <schema>public</schema>
+ <table>testtable</table>
+ <truncate>N</truncate>
+ <create>Y</create>
+ <encoding>UTF-8</encoding>
+ <dateformat/>
+ <addtoresult>N</addtoresult>
+ <startnewline>Y</startnewline>
+ <DoNotAddInsertStatements>Y</DoNotAddInsertStatements>
+ <specifyFields>Y</specifyFields>
+ <fields>
+ <field>
+ <name>id</name>
+ <rename>client_id</rename>
+ </field>
+ <field>
+ <name>label</name>
+ <rename/>
+ </field>
+ </fields>
+ <file>
+ <name>${PROJECT_HOME}/output/filename</name>
+ <extention>sql</extention>
+ <append>N</append>
+ <split>N</split>
+ <haspartno>N</haspartno>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <splitevery>0</splitevery>
+ <create_parent_folder>Y</create_parent_folder>
+ <DoNotOpenNewFileInit>Y</DoNotOpenNewFileInit>
+ </file>
+ <attributes/>
+ <GUI>
+ <xloc>432</xloc>
+ <yloc>112</yloc>
+ </GUI>
+</transform>