cancel
Showing results for 
Search instead for 
Did you mean: 

how to use sql variables in jdbc send adapter??

brian_luo
Participant
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Answers (2)

Answers (2)

Former Member
0 Kudos

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>

former_member732072
Active Participant
0 Kudos

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

brian_luo
Participant
0 Kudos

hi Prakash & Kalpesh,thx 4 ur answers.

but i think this way that u gave me can be used in the jdbc receive adapter.

maybe, it isnt suitable in the jdbc send adapter.

Edited by: brian Luo on Apr 20, 2009 8:03 AM

Former Member
0 Kudos

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

brian_luo
Participant
0 Kudos

Thanks for kalpesh and Srini.

now i will try the way.but am not sure i will succeed,so maybe i will need ur helps yet.

brian

brian_luo
Participant
0 Kudos

hello kalpesh,

how can i pass the FLAG value as input parameter?

i know we can pass the value to input variable of store procedure via coding when we develop webpages.

but in the send adapter, i dont know how to send the value.

waitin 4 u answers.

Thx!

brian

Former Member
Former Member
0 Kudos

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

brian_luo
Participant
0 Kudos

thank u,Srini.

ill see ur mateirals.

now, the db of send side is MySql in my test environment.

Former Member
0 Kudos

Hi

Sql wonot be a problem, try and lemme know the outcome

Srini

brian_luo
Participant
0 Kudos

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

Former Member
0 Kudos

Hi Bruo,

gr8

S u can store in u r DB and pass it to the Sender Interface.

rgds

srini

brian_luo
Participant
0 Kudos

then in the jdbc sender channel, i can call a store procedure that can get the value from aforementioned table and pass the value as input parameter.right?

Thx Srini,

Brian

Former Member
0 Kudos

Hi Brain ,

S as u said.

Donot forget to update u r record after the select in the stored procedure.

Srini

brian_luo
Participant
0 Kudos

ok,thx 4 ur help,Srini.