on 04-20-2009 2:05 AM
Hi,
i wanna use a variable in the sql statement in the jdbc send adapter.
i know i can key sql statement in the "Query SQL Statement" in the jdbc send adapter.but i dont know how to use variables in the sql statement.
i mean, for example, the sql statement is "SELECT * FROM t_student WHERE READFLAG = 0", but now i wanna instead of "0"(the value of the READFLAG) using a variable.
and if i can use a variable to replace the "0"(the value of the READFLAG above sql statement), then how can i send a value to the variable??
request help in the regard.
thx in advance.
Brian
Hi
Try to use Stored procedure to achieve this
for SQL DB Sender Stored procedure with XI Sender Adapter supported
but with oracle it has some restrictions ,
Am not sure but with service pack SP15 and with oracle 10.2 series Sender JDBC supports Stored procedure or else u cannot go with Stored procedure with Oracle
refer sapnote : 941317
thanks
Srini
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Use the place holders to pass the data at runtime. Check the below structure for more details.
Hope this helps.
Thanks and Regards,
Kalpesh
<root>
<stmt>
<Student action="SQL_QUERY">
<access> SELECT * FROM t_student WHERE READFLAG = '$FLAG$ </access>
<key>
<FLAG>0</FLAG>
</key>
</Student>
</stmt>
</root>
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Brian,
Please look at the following link and see if it answers your questions. There is a section with an example that says (Using Place Holders).
http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm
Best Regards
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Brian,
As per my knowledge, for JDBC Sender Adapter, UPDATE stmt should be executed along with SELECT statement. If placeholder like feature is not supported in Sender side, think of using the stored procedure which contains exactly one SELECT statement using SQL EXECUTE. This is just an suggestion in which you can pass the FLAG value as input parameter to stored procedure and will return the RESULT SET as the output parameter based on the FLAG value.
Hope this helps.
Thanks and Regards,
Kalpesh
Hi
Check
http://help.sap.com/saphelp_nw2004s/helpdata/en/22/b4d13b633f7748b4d34f3191529946/frameset.htm
What is ur DB ??
Srini
Hi Bruo,
One more point try to write u r Update statement in the Stored procedure which would be dynamic and real.Since u r passing Key value against those key values in DB flag status has to be changed.
Update statement in the Sender JDBC Adapter configuration , try to write dummy update statement which wonot effect the table.
Srini
Srini,
im successful using stored procedure.
BUT,heh,i wonder whether i can key the value outside the pi, i mean, i dont wanna pass the value in the "Query SQL Statement" of the jdbc sender channel.
the approach(stored procedure) is still to need us key the value in the channel.
i wanna the user can pass the value as input parameter by themselves outside the pi.
i think maybe i can create a table in the db that store the values that keyed by users. is right??
Thx Srini,
Brian
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.