topic [resolved] SQL bind variables in Talend Studio

[resolved] SQL bind variables

Anonymous — Sat, 16 Nov 2024 13:22:38 GMT

Hi,
Is it possible to use bind variable in SQL statement?

I'd like to perform the following query unsing a tOracleInput:
select * from project where project_type_id = rojectTypeID;
The bind variable projectTypeID could get its value from a context.
The bind variable is much more secure than append a string to the query.
Cheers,
Seb

Re: [resolved] SQL bind variables

Anonymous — Fri, 25 Jun 2010 21:23:01 GMT

to my knowledge this is not possible right out of the box-- fortunately, Talend is the most flexible ETL tool out there so I am sure you can get this to work.
I would suggest looking into the database input components. you can probably modify them to use context variables and bind variables.

Re: [resolved] SQL bind variables

Anonymous — Tue, 29 Jun 2010 10:23:34 GMT

Thanks for the information.
Too bad that it is not directly possible.
Seb

Re: [resolved] SQL bind variables

Anonymous — Fri, 02 Jul 2010 10:09:34 GMT

Bind variables are those variables which are used as a copy of main variable means it takes initiate value of main variable for which this bind variable is declared.

Re: [resolved] SQL bind variables

Anonymous — Fri, 02 Jul 2010 11:44:44 GMT

Yes this requirement is so common that I completely agree that directly binding for example context variables in sql statements would be a very welcomed addition, expecially in an enterprise ETL tool some people expect this feature for granted... parametrized queries...
Resorting always to manual string concatenation seems always a bad hacked in thing...

Re: [resolved] SQL bind variables

Anonymous — Fri, 02 Jul 2010 11:52:18 GMT

I've opened a feature request, in case monitor or comment it
http://www.talendforge.org/bugs/view.php?id=14120

Re: [resolved] SQL bind variables

Anonymous — Mon, 19 Jul 2010 22:06:54 GMT

Hi
A Use PreparedStatement option has been added to all db row components (Advanced settings section) since 4.0
From our point of view, such an option is more interesting when you have parameters that change for every input row, than on input components.
I think that this answer to the request.
Regards,

Re: [resolved] SQL bind variables

Anonymous — Mon, 19 Jul 2010 22:56:35 GMT

Hi mhirt,
Can you please explain what is wrong with using a Prepared Statement in the input components?
Why not let the user decide whether to use a PreparedStatement or a simple statement. (This is how my tJDBCInput extension does it).
Has there been a discussion or vote on this? Who decided not to implement it?
I find it important because of several reasons:
1) a PS with bind variables is safer because it prevents SQL injection attacks
2) a PS with bind variables simplifies development of JDBC based "on premise" solutions, where the customer can choose the db vendor of their data warehouse. Consider an input query that selects records based on a start date. If bind variables are used the java Date parameter can be passed to the PS and the JDBC driver would take care of the formatting. If bind variables are not used, the ETL developer is responsible for doing the vendor specific adaptations, which results in ugly code & duplication.
Please reconsider issue http://www.talendforge.org/bugs/view.php?id=14120
Regards,
Amnon

Re: [resolved] SQL bind variables

Anonymous — Mon, 19 Jul 2010 23:26:05 GMT

Technically, Prepared Statement are opened only once by loop in a t*Input (in the begin part of the component).
Iterating 1000 time over a t*Input would create / close 1000 Prepared Statement over the database which is definitely wrong.
In a row component, prepared statement will be created only once (begin part) but will benefits of the main part to be reused for the 1000 rows and this is the exactly the purpose of Prepared Statements.
M.

Re: [resolved] SQL bind variables

Anonymous — Mon, 19 Jul 2010 23:34:49 GMT

Why would anyone iterate 100 times of the t*input?
In the input component I modified it seems that a PS is created only once.
Would you like to examine the code?
Amnon

Re: [resolved] SQL bind variables

Anonymous — Thu, 29 Jul 2010 14:18:35 GMT

Is it possible to have a discussion on this matter?
I am not sure I understand why it is better to support a prepared statement and bind variables in a t*Row component than it is in a t*Input component.
Amnon

Re: [resolved] SQL bind variables

Anonymous — Fri, 22 Oct 2010 10:34:46 GMT

HI,
I tried the PS of tOracleRow.
See the picture below for the implementation.
But It failed to execute because of wrong java code.
Can you explain me why?
Of course this is a very simple case.
Thanks
Seb
Resolved see my next post

Re: [resolved] SQL bind variables

Anonymous — Fri, 22 Oct 2010 13:14:12 GMT

Hi Again,
I've found my problem.
The schema was not defined correctly.
See the picture below.

Re: [resolved] SQL bind variables

Anonymous — Thu, 16 Dec 2010 13:12:53 GMT

Talend team: prepared statements are not only for "parameters that change for every input row". With them we don't have to worry about constant formating. Compare:
"select a from tab where date = " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(globalmap.get("somedate"))
or this:
"select a from tab where date = ?"
?

Re: [resolved] SQL bind variables

guymage — Wed, 19 Jan 2011 06:08:02 GMT

Hi,
Does anyone know how to do a prepared statements in the lookup of a tMap ?
With a lookup I use to use a tOracleInput (with no prepared statement), I tried with a tOracleRow but it does not work.
For information, my changing parameter is a context variable initialized every row.
Thanks

Re: [resolved] SQL bind variables

Anonymous — Wed, 19 Jan 2011 09:38:41 GMT

I know, wait till I attach rarjpeg .
Since lookup is done in a separate method, you have to pass the lookup key via globalMap. tMap's Expr. key :
null == globalMap.put("row12.TABLE_NAME", row12.TABLE_NAME) || true ? row12.TABLE_NAME : null
This expression puts the value to globalMap and returns it
In tMap settings set Lookup model to "reload at each row"
In tXxxRow globalMap.get() as one of prepared statement parameters
Then you use tParseRecordSet and connect it's output to tMap as lookup row
upd: Attached. You can unpack the job from the screenshot

Re: [resolved] SQL bind variables

guymage — Wed, 19 Jan 2011 21:19:55 GMT

Thank you for your answer, but I can not find your attached job ?

Re: [resolved] SQL bind variables

guymage — Fri, 21 Jan 2011 00:39:02 GMT

Ok, I found the Job (thank you Ilya).
Now it works perfectly, thanks a lot

Re: [resolved] SQL bind variables

Anonymous — Fri, 25 Feb 2011 18:15:19 GMT

I completely agree.
In addition, with bind variables the DB engine does not need to parse the statement each time that it is executed (at least with Oracle).

Hi mhirt,
Can you please explain what is wrong with using a Prepared Statement in the input components?
Why not let the user decide whether to use a PreparedStatement or a simple statement. (This is how my tJDBCInput extension does it).
Has there been a discussion or vote on this? Who decided not to implement it?
I find it important because of several reasons:
1) a PS with bind variables is safer because it prevents SQL injection attacks
2) a PS with bind variables simplifies development of JDBC based "on premise" solutions, where the customer can choose the db vendor of their data warehouse. Consider an input query that selects records based on a start date. If bind variables are used the java Date parameter can be passed to the PS and the JDBC driver would take care of the formatting. If bind variables are not used, the ETL developer is responsible for doing the vendor specific adaptations, which results in ugly code & duplication.
Please reconsider issue http://www.talendforge.org/bugs/view.php?id=14120