CODEWITHSUNDEEP
pentahopentaho-spoonpentaho-data-integration
P.C
P.C

Reputation: 34

Execute row SQL script can't read .sql file. I am getting this error

2018/02/01 11:20:00 - Execute row SQL script.0 - We can not find field [C:\Users\abc\Documents\xyz\query\record.sql] in input stream!

My steps involve -

  1. get variables - input
  2. Execute row SQL script - where I am trying to read my query file
  3. text file output

In Execute row SQL script I have given C:\Users\abc\Documents\xyz\query\record.sql in SQL fieldname.

EDIT

Clarifying, I want the job to read query (from files) and give a text output all by itself (run and output the select query result).

Upvotes: 0

Views: 1837

Answers (2)

Felypp Oliveira
Felypp Oliveira

Reputation: 2197

I'm copying/pasting the explanation from the docs of two config options from the Execute Row SQL Script step:

SQL field name: The field that either contains the SQL to execute or optionally specifies the path to a file that contains SQL to execute

and

Read SQL from file: If checked, then the SQL field name option specifies a file that contains SQL to execute, otherwise the SQL field name option specifies the actual SQL to execute. (Available since 5.0)

So you can't specify a file path in the SQL field name option, but you can specify a field from the stream that contains a file path to the desired sql. Otherwise, you should define in this option a field that contains the sql text.

Hope it helps.

EDIT 1

As you need to execute a select query from a file, I would recommend the step Dynamic SQL row, just as the following image.

how dynamic sql row works

I think it will do the job.

EDIT 2

The following just do the dynamic sql execution. First, you need to load the sql queries into the stream. After that, a job will be executed for each sql. In this job, a variable containing the sql query is set and a transformation is executed next. This transformation basically is a Input Table step that fills dynamically the sql box with the previous set sql variable.

pentaho dynamic sql execution

Now I think it's what you're looking for. Hope it helps.

Upvotes: 2

AlainD
AlainD

Reputation: 6356

To read an SQL table use the step Table input, and copy paste your query into the big text area.

Note that, unless you have complex join in your query, you rarely have to import sql scripts thanks to a very handy Get SQL Statement button.

Start with a select statement without parameters. Once it works, replace the variables with ${variable}, and tick the Replace variable in script check box. (Warn there are some restrictions due to java SQL Prepared Statement, for example you may not have a variable in the table name).

If you want to get the script from a file, first read the file with a Table input in a column and select this column in the Insert data from step of the Table input.

If you still have variable parameters either in your file, either in the name of the tables you are selecting from, then use a Modified Javascript or a Regex evaluator, or any other step to replace the variables with they value, and give the resulting SQL script to the table via the Insert data from step.

If you have a bunch of file.sql to read, do not forget to check the Execute for each row button.

Hope this helps.

Upvotes: 0

Related Questions