0

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).

Felypp Oliveira
  • 2,147
  • 1
  • 17
  • 17
P.C
  • 24
  • 6

2 Answers2

2

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.

Felypp Oliveira
  • 2,147
  • 1
  • 17
  • 17
  • 1
    I approve with a slight comment. Neither the Get variable, neither the Row-SQL steps will start by themselves. You need to put a `Generate row` with limit 1 before the get variable. – AlainD Feb 01 '18 at 16:59
  • You're right. In fact, the `Execute Row SQL Script` is not meant to start a stream, but you can start one using other steps, not only `Generate Row`, e.g. the `Data Grid` and/or `Input Table`. – Felypp Oliveira Feb 01 '18 at 17:15
  • so now I started the transformation with generate rows with limit 1 and after that I am trying to get the file name with get file name option and then I am passing the filename to Execute row SQL script and then in SQL fieldname I am passing filename. but instead of executing the .sql file its printing the uri of the file. – P.C Feb 01 '18 at 18:52
  • @P.C you could try the `Data Grid` step. On the *Meta* tab set a field named *filename* of type *String*. In the *Data* tab set the *filename* column to be the path to the sql file. Link this `Data Grid` to the `Execute Row SQL Script` step. Set there the *connection*, the *sql field name* (must be **filename**) and check the *read sql from file* option. It did the job for me. Remember that this step does not output rows from a select statement. It is meant mostly to do insert/update/delete statements. – Felypp Oliveira Feb 02 '18 at 13:43
  • I tried these exactly step by step but in my output file the output is the uri again "filename C:\Users\abc\Documents\xyz\query\record.sql" moreover I am trying to execute a select statement , execute row sql script must work , right? if not then can you suggest which option will be good to execute select statement from a file? – P.C Feb 02 '18 at 14:43
  • @P.C you can try the `Dynamic SQL row` step. In this case, the *SQL field name* option must contain the sql query. In the *Template SQL* box you must define the layout of the returned data (the types and order of columns). I'll add an example in this answer. – Felypp Oliveira Feb 02 '18 at 15:17
  • it doesn't work that way, I want a job which can read query and give a text output all by itself. defining the layout of the returned data is not an option. read sql file - execute sql - text output – P.C Feb 02 '18 at 16:02
  • @P.C just edited my answer again. I think it's what you're looking for. – Felypp Oliveira Feb 05 '18 at 15:12
0

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.

AlainD
  • 6,187
  • 3
  • 17
  • 31
  • *"If you want to get the script from a file, first read the file with a `Table input`... "*, how is a `Table input` used to read a file? As far as I know this step only executes sql selects... – Felypp Oliveira Feb 05 '18 at 15:23