tag:blogger.com,1999:blog-15319370.post3732404779466472673..comments2024-03-05T11:16:00.846+01:00Comments on Roland Bouman's blog: Parameterizing SQL statements in the Kettle Table Input step: Variables vs Parametersrpboumanhttp://www.blogger.com/profile/13365137747952711328noreply@blogger.comBlogger13125tag:blogger.com,1999:blog-15319370.post-54994085928397060082021-05-13T13:13:26.780+02:002021-05-13T13:13:26.780+02:00Hi @Unknown, you asked
"wanna save the outpu...Hi @Unknown, you asked<br /><br />"wanna save the output of the SQL query into a transformation parameter value."<br /><br />yes you can - use the "Set Variables" step.<br /><br />https://wiki.pentaho.com/display/EAI/Set+Variables<br /><br />However, the new variable value will not be seen immediately in the transformation after setting it. That's because all the steps are already running. <br /><br />The new value will be visible from the next step in the job that started the transformation that changed the parameter value.<br /><br />I hope this helps!rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-23252785363718661042021-05-13T08:54:23.715+02:002021-05-13T08:54:23.715+02:00I wanna do it the other way around. I wanna save t...I wanna do it the other way around. I wanna save the output of the SQL query into a transformation parameter value. Can someone please tell me how to do it?Anonymoushttps://www.blogger.com/profile/05288203529319684372noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-10486645776033450902019-08-27T22:19:22.607+02:002019-08-27T22:19:22.607+02:00Hi Puneet,
I am assuming you are using the ? plac...Hi Puneet,<br /><br />I am assuming you are using the ? placeholder in a table input step? Those placeholders are used as JDBC bind variables. The can be bound if you configure the input step (see: https://help.pentaho.com/Documentation/8.2/Products/Data_Integration/Transformation_Step_Reference/Table_Input) and then the fields from the incoming stream will be bound by position.<br /><br />I don't think you can bind a list to that. You could write something like:<br /><br /><br />SELECT * FROM t WHERE c in (?,?,?)<br /><br /><br />and then have just as many fields in your incoming step. The drawback, apart from the positional binding, is that you need to know in advance how many values there will be in the list.<br /><br />Alternatively, you might check the "Replace variables in script?" checkbox, and use variables (or, parameters at the transformation level, which are exposed as variables within the scope of the transformation). <br /><br />You could for example write:<br /><br />SELECT * FROM t WHERE c IN (${MY_LIST_OF_VALUES})<br /><br />and then ensure that the variable MY_LIST_OF_VALUES has the string value '1,2,3'; then the resulting SQL will read:<br /><br />SELECT * FROM t WHERE c IN (1,2,3)<br /><br /><br />Keep in mind that when you do this, any appearance of a variable placeholder in the SQL will be replaced with the string value of the variable - like a macro substitution. The drawback here is that you'll need to ensure yourself the resulting statement, after variable substitution, is still valid SQL, and apart from that, within one transformation execution, the variable value will always be constant - you cannot set the variable within the transformation, it has to be controlled from the calling job.<br /><br />HTH.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-10600162341262705412019-08-27T19:55:31.534+02:002019-08-27T19:55:31.534+02:00How do we pass a value to ? in case we want to run...How do we pass a value to ? in case we want to run --> where column in ?puneetnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-74678291826561875382016-08-05T11:54:39.497+02:002016-08-05T11:54:39.497+02:00@gattrinn,
"do we have any option to pass dy...@gattrinn,<br /><br />"do we have any option to pass dynamic column in SQL query ?"<br /><br />Yes. All steps that accept a SQL statement in their config can be represented in part or in their entirety by a kettle variable or parameter. So, you can use this to dynamically set columns as well. <br /><br />However, you will have to take care that the remainder of your transformation is resilient to a dynamic column make up of the data stream as well. <br /><br />Another thing you might want to look into in making your transformations more dynamic is metadata injection, which essentially lets you configure your steps dynamically (data driven) http://wiki.pentaho.com/display/EAI/ETL+Metadata+Injectionrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-49413316144397264082016-08-05T11:44:09.740+02:002016-08-05T11:44:09.740+02:00do we have any option to pass dynamic column in SQ...do we have any option to pass dynamic column in SQL query ?gattrinnhttps://www.blogger.com/profile/06401624309816741075noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-19027301680222700642016-04-29T07:54:00.433+02:002016-04-29T07:54:00.433+02:00Thank you so much! Now i know how to pass variable...Thank you so much! Now i know how to pass variable into sql script. Oh yeah!!!SNGhttps://www.blogger.com/profile/14174773265690698776noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-50529979286073627282014-07-22T15:06:03.840+02:002014-07-22T15:06:03.840+02:00Muthu, cool! Glad that worked.Muthu, cool! Glad that worked.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-60339820660755163032014-07-22T14:37:18.780+02:002014-07-22T14:37:18.780+02:00Thanks man, I tried and it worked :)Thanks man, I tried and it worked :)Muthuhttps://www.blogger.com/profile/11302305215281475899noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-5638503656202469332014-07-22T13:04:17.460+02:002014-07-22T13:04:17.460+02:00Muthu, I can but I don't want to.
- Just cre...Muthu, I can but I don't want to. <br /><br />- Just create a new transformation. <br /><br />- Within the transformation, create a new Database connection. Use variables for the fields you want to parameterize. For example, use ${HOST} for hostname, ${PORT} for portnumber etcetera. <br /><br />- open the transormation settings and in the "Parameters" tab, add a parameter for each variable you used. In the paramters tab, only use the parameter name, and leave out the ${...} markers (those are only appropriate when *referencing* a variable.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-47468464524993157732014-07-22T12:12:39.240+02:002014-07-22T12:12:39.240+02:00Can you please provide the steps and screen shots ...Can you please provide the steps and screen shots like the previous one for passing db connections (hostname, Dbname, username and password) as a variables.Muthuhttps://www.blogger.com/profile/11302305215281475899noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-40907868674829635922014-07-22T11:08:10.562+02:002014-07-22T11:08:10.562+02:00Muthu, yes, except for the database type, all elem...Muthu, yes, except for the database type, all elements of kettle database connections (host, port, credentials etc) can be specified as variables, and can thus be set using parameters.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-71062872999253842452014-07-22T10:14:00.798+02:002014-07-22T10:14:00.798+02:00Thanks, can you please explain how to pass the Con...Thanks, can you please explain how to pass the Connection - Hostname and Database name as parameter?Muthuhttps://www.blogger.com/profile/11302305215281475899noreply@blogger.com