Generate SQL for table input

[mscahill]

Is it possible to do some scripting to generate a SQL statement for a table input? Here's what I want to do:

-Run an SQL statement to lookup the year
-Script a for loop that generates a SQL statement based on an offset from that year
-Run the SQL as a table input
-Feed the rows to a table output

Is this possible? Thanks in advance.

[ingo.klose]

Yes it is, but you need a job an two transformations.

The first transformation does the lookup, creates the SQL script (e.g. with the JS step) and puts it in a variabe, e.g. ${MY_SQL_SCRIPT}.

The second transformation has the Table Input step with a valid connection, the ${MY_SQL_SCRIPT} in the SQL box and the replace variable option activated.

Regards,
Ingo

[mscahill]

So what would that look like? Would the javascript just be a string declaration, ex:

var SQL
SQL = "SELECT * FROM mytable"

and then put this in the table input:

${SQL}

???

[ingo.klose]

No, you have to put SQL field in the stream (press Get variables at the bottom of the JS step dialog) and the put it into a KETTLE variable using the Set Variable step.

The table input should be ok.

[mscahill]

I did this, and got the error:

"There was an error executing SQL: ${SQL}
Syntax error for DATE escape sequence '{SQL}'"

[ingo.klose]

Did you activate the replace variable option?

If yes, try to output the SQL statement created and check the syntax. If possible post your 2nd transformation.

Regards,
Ingo

[mscahill]

I'm just working on the second transformation right now, using a static value for the year. This is how I have it structured for the time being.

http://www2.stetson.edu/development/transformation.bmp

[sboden]

sigh :( :D ... you can't reliable set variables in the same transformation as where you use them.

It's not the first time this is mentioned... it probably won't be the last.

Regards,
Sven

[mscahill]

So I need two transformations and a job to do this?

[mscahill]

I split it out, and I still get the same error

[sboden]

Then you're still doing something else wrong... attach your transformations and job (I assume you just used a job to run first the setting of the variable, followed by the real transformation).

Regards,
Sven

[mscahill]

How would I go about doing that? I'm using a repository.

[sboden]

Load transformations in spoon, disconnect repository and save to file?????

Regards,
Sven

[mscahill]

Can't you just explain to me how it's suppose to be done? I just want to generate a SQL statement, run it, and put the resultset in a table.

[sboden]

People tried and it didn't seem to work for you:

- Make an sql statement have have it set as variable with set variable step (valid root job or so)... you don't use ${SQL} to set a variable, only SQL
- Make a job with a table input and as contents ${SQL}, also enable "replace variables in script"
- Make a job that executes the setting of the variables followed by the transformation running the sql.

Further than that people can you not help you more if you don't supply more data.... it's like someone with a car problem telling the garage "it's a big problem, it makes a strange noise, fix my car"... but for the rest you don't show the car or give more details :D

Regards,
Sven

[mscahill]

So the first transformation would look like this, correct?

http://www2.stetson.edu/development/screenshot.bmp

[ingo.klose]

You need to add a Generate Row Step to produce at least one row for it to work.

I enclosed a screenshot.

Regards,
Ingo

[mscahill]

Ok. That worked. Now the SQL query is in the variable. I tried to link this transformation to a transformation with a table input where the SQL is ${SQL} and a table output. It's still giving me the same error:

An error occurred executing SQL: ${SQL}
Syntax error for DATE escape sequence '{SQL}'

[mscahill]

I was able to get it to work! It all runs in one transformation, as well!

I had to put the name of the variable in all steps as uppercase. It ran just fine.

Thanks for all your help!

[ingo.klose]

This setup works for me when the Variable ${SQL} contains a valid statement.

Please note, you need a valid connection defined and the "Replace variables in Script" option checked.

[mscahill]

I just reran it, and now it doesn't work. I split it into two transformations and a job again, but I still get the same error:

An error occurred executing SQL: ${SQL}
Syntax error for DATE escape sequence '{SQL}'

[mscahill]

Now it's working again, and I don't know why... :-(

I'll let you know if I run into any more problems. Thanks for the help!

[mscahill]

OK. I've got it to work, but only while the program is open. If I close and reopen, I get an "empty query" error. How do I reliably pass the query to the table input?

[sboden]

My first hunch would be that you're somehow abusing variables. But without any transformation orjob there's little to check of course :D

Regards,
Sven

[mscahill]

Hey, sorry. I just got back from vacation. Here's the transformation.

[sboden]

As I thought... you cannot "set variables" and use them in the same transformation (as where you've set them).

Regards,
Sven

[mscahill]

So I need four transformations and a job to accomplish what I'm trying to do?

[sboden]

yes, seems about right...

The reason ... not the first time we explained... and probably not the last :D

All steps in a transformation are initialized and then are put running in parallel. Most of the steps use variables during initialization phase (table input will prepare the query e.g. at initialization time).
The setting of the variables is triggered when a row arrives at the set variable step... this is after the initialization phase, so anything you set then will not be active in time for the stuff happening at initialization.
Hence in most cases you can't set variables in the same transformation as where you use them.

Regards,
Sven

[mscahill]

I'll try to split that one out and let you know if it works. I have six other tables I need to do this for. Bummer.

[mscahill]

Now I get this again: Syntax error for DATE escape sequence '{DATACUBE}'

[sboden]

For starters:
- in cons...02.ktr: you have to trigger javascript to make it work, e.g. with a row generator
- in cons...02.ktr: Fiscal is not defined... you use a field which is not in your input (since you have no input).
- in cons...02.ktr: you switched compatibility on.
- In your SQL query, you use back-ticks ` ... I doubt they're correct :D

How about a full stack trace after fixing above problems, and putting the trace level to the most detailed possible.

Regards,
Sven

[mscahill]

-agreed. This was triggered when it was one transformation, but that needs to be reimplemented in the split version
-Fiscal is defined in 01. Can't I define a variable in one transformation and use it in another? That's the whole point of this thread
-I don't know what "compatibility" means in this context
-we have field names with spaces in our data cubes to aid in ad hoc reporting. The backticks are required for MySQL
-How do I do a "full stack trace"?
-I feel like I should be RTFM, but I can't find a detailed Manual. :-)

[sboden]

For the Fiscal part... you can use variables across transformations (if you use the right setting, valid in JVM/root job)... but they don't become automatic variables... you have to get the variable from the environment and assign it to a variable.

With compatibility (in javascript) all fields in a row are of type Value (and you have to setValue() e.g. to set the value), with compatibility off the fields in a row are the actual Java values (which you assume I think).

For the stacktrace... it will be created in some file, but you can also copy the log in spoon.

For tutorials... there's a tutorial on the pentaho wiki, there are some other tutorials floating around the net.

Regards,
Sven

[mscahill]

I set the variable in 01. I'm trying to use that variable in the modified javascript value in 02. The variable FISCAL is set correctly in 01, but is undefined in 02...

I think this is what you're looking for:

2008/07/16 10:11:20 - Spoon - Starting job...
2008/07/16 10:11:20 - tmpconsecutivegiving - Starting entry [tmpconsecutivegiving_01]
2008/07/16 10:11:20 - tmpconsecutivegiving_01 - Opening filename : []
2008/07/16 10:11:20 - tmpconsecutivegiving_01 - Opening transformation: [tmpconsecutivegiving_01] in directory [/]
2008/07/16 10:11:20 - tmpconsecutivegiving_01 - Loading transformation from repository [tmpconsecutivegiving_01] in directory [/]
2008/07/16 10:11:20 - tmpconsecutivegiving_01 - Dispatching started for transformation [tmpconsecutivegiving_01]
2008/07/16 10:11:20 - tmpconsecutivegiving_01 - Nr of arguments detected:0
2008/07/16 10:11:20 - tmpconsecutivegiving_01 - This is not a replay transformation
2008/07/16 10:11:20 - tmpconsecutivegiving_01 - This transformation can be replayed with replay date: 2008/07/16 10:11:20
2008/07/16 10:11:20 - tmpconsecutivegiving_01 - Initialising 2 steps...
2008/07/16 10:11:20 - Set Variables 2.0 - Starting to run...
2008/07/16 10:11:20 - Fiscal Lookup.0 - Starting to run...
2008/07/16 10:11:20 - Fiscal Lookup.0 - Finished reading query, closing connection.
2008/07/16 10:11:20 - Fiscal Lookup.0 - Finished processing (I=1, O=0, R=0, W=1, U=0, E=0)
2008/07/16 10:11:20 - Set Variables 2.0 - Setting environment variables...
2008/07/16 10:11:20 - Set Variables 2.0 - Set variable FISCAL to value [2009]
2008/07/16 10:11:20 - Set Variables 2.0 - Finished after 1 rows.
2008/07/16 10:11:20 - Set Variables 2.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
2008/07/16 10:11:20 - tmpconsecutivegiving - Starting entry [tmpconsecutivegiving_02]
2008/07/16 10:11:20 - tmpconsecutivegiving_02 - Opening filename : []
2008/07/16 10:11:20 - tmpconsecutivegiving_02 - Opening transformation: [tmpconsecutivegiving_02] in directory [/]
2008/07/16 10:11:20 - tmpconsecutivegiving_02 - Loading transformation from repository [tmpconsecutivegiving_02] in directory [/]
2008/07/16 10:11:21 - tmpconsecutivegiving_02 - Dispatching started for transformation [tmpconsecutivegiving_02]
2008/07/16 10:11:21 - tmpconsecutivegiving_02 - Nr of arguments detected:0
2008/07/16 10:11:21 - tmpconsecutivegiving_02 - This is not a replay transformation
2008/07/16 10:11:21 - tmpconsecutivegiving_02 - This transformation can be replayed with replay date: 2008/07/16 10:11:21
2008/07/16 10:11:21 - tmpconsecutivegiving_02 - Initialising 3 steps...
2008/07/16 10:11:21 - Modified Java Script Value.0 - Starting to run...
2008/07/16 10:11:21 - Set Variables.0 - Starting to run...
2008/07/16 10:11:21 - Generate Rows.0 - Starting to run...
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : Unexpected error :
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : org.pentaho.di.core.exception.KettleValueException:
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : Javascript error:
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : ReferenceError: "Fiscal" is not defined. (script#4)
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) :
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : at org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesMod.addValues(ScriptValuesMod.java:443)
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : at org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesMod.processRow(ScriptValuesMod.java:738)
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : at org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesMod.run(ScriptValuesMod.java:798)
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : Caused by: org.mozilla.javascript.EcmaError: ReferenceError: "Fiscal" is not defined. (script#4)
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3229)
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : at org.mozilla.javascript.ScriptRuntime.constructError(ScriptRuntime.java:3219)
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : at org.mozilla.javascript.ScriptRuntime.notFoundError(ScriptRuntime.java:3292)
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : at org.mozilla.javascript.ScriptRuntime.name(ScriptRuntime.java:1570)
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : at org.mozilla.javascript.gen.c5._c0(script:4)
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : at org.mozilla.javascript.gen.c5.call(script)
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:340)
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:2758)
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : at org.mozilla.javascript.gen.c5.call(script)
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : at org.mozilla.javascript.gen.c5.exec(script)
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : at org.pentaho.di.trans.steps.scriptvalues_mod.ScriptValuesMod.addValues(ScriptValuesMod.java:356)
2008/07/16 10:11:21 - Modified Java Script Value.0 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : ... 2 more
2008/07/16 10:11:21 - Modified Java Script Value.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
2008/07/16 10:11:21 - Generate Rows.0 - Finished processing (I=0, O=0, R=0, W=1, U=0, E=0)
2008/07/16 10:11:21 - Set Variables.0 - Finished after 0 rows.
2008/07/16 10:11:21 - Set Variables.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=0)
2008/07/16 10:11:21 - tmpconsecutivegiving_02 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : Errors detected!
2008/07/16 10:11:21 - tmpconsecutivegiving_02 - ERROR (version 3.0.3, build 569 from 2008/04/25 12:30:04) : Errors detected!
2008/07/16 10:11:21 - tmpconsecutivegiving_02 - Transformation detected 1 steps with errors!
2008/07/16 10:11:21 - tmpconsecutivegiving_02 - Transformation is killing the other steps!
2008/07/16 10:11:21 - tmpconsecutivegiving_02 - Looking at step: Modified Java Script Value
2008/07/16 10:11:21 - tmpconsecutivegiving_02 - Looking at step: Set Variables
2008/07/16 10:11:21 - tmpconsecutivegiving_02 - Looking at step: Generate Rows
2008/07/16 10:11:21 - tmpconsecutivegiving - Starting entry [Mail 1 2]
2008/07/16 10:11:21 - tmpconsecutivegiving - !Job.Log.FinishedJobEntry!
2008/07/16 10:11:21 - tmpconsecutivegiving - !Job.Log.FinishedJobEntry!
2008/07/16 10:11:21 - tmpconsecutivegiving - !Job.Log.FinishedJobEntry!
2008/07/16 10:11:21 - Spoon - Job has ended.

[sboden]

You cannot just use Fiscal ... you have to define it again in the second transformation and then do something as "var Fiscal = getVariable("Fiscal");"

Regards,
Sven

[mscahill]

So I do another modified javascript value?

[sboden]

no... in the one you currently use Fiscal and where you get the error from.

Regards,
Sven

[mscahill]

I added a modified javascript value to the beginning of the second transformation (after the generate rows) to get the fiscal year, and it successfully generated the query. I then added a generate rows and a modified javascript value at the beginning of the third transformation to get the query, run it in the table input, and output the results to another table. The whole job ran successfully. I'll run the job in the replication process tonight, and let you know tomorrow if it ran successfully.

[mscahill]

This method appears to be working correctly. I'm going to apply this same method to my other tables. If you don't hear anything in a few days, you can safely assume that everything worked. Thanks for your help, Sven!