Change JDBC select SQL query's where clause at runtime

[Former Member]

Hi,

I have one JDBC sender channel with Select SQL Query which helps in fetching data from JDE table. We have defined where clause to fetch some particular records i.e. if ABC = NULL then fetch the record.. and then update query is used to turn that flag to "Y" i.e. ABC ="Y" so that next time the channel don't pick the same records again.

Now the problem is that if we want to fetch some records again whose flag has been set to "Y" we cannot change the select query again and again in the production environment.

Is there any way to change the select query of the JDBC channel at runtime e.g. Picking up some flat file where the conditions for the particular records to be picked are defined (ID=123) and changing the WHERE clause of select query in the JDBC channel for those records to be picked without using BPM.

Thank you!

With regards,

Simran

[Former Member · Accepted solution]

Hi

Now the problem is that if we want to fetch some records again whose flag has been set to "Y" we cannot change the select query again and again in the production environment.

Hope above would be a business requirement where in u r DB data needs to be fectched , so write application which connected to DB to change the status back to F so that u can pick and change into Y.

If u r not able to do it with application do it in SQL PLus level.

U can also do with stored procedure also .

Srini

[Former Member · Accepted solution]

Thanks Srini and Jitender.

Could you please throw some light on how this will work with the help of stored procedures or SQL plus?

We cannot change the flag to null to fetch the record again(against business logic).

We just want to change the variable of the WHERE clause.

This procedure will run in very rare case when the records are fetched from JDE table(JDBC SENDER) and goes into error due to some or the other reason in MDM(FILE RECEIVER). So for those records that went into error e.g with ID = 1,2,3,4 but has been fetched by XI once(leading Flag ABC ="Y"), I want the where condition to be set for ID with the help of stored procedure as you recomended?

Thank you!

With regards,

Simran

Edited by: simranpreet sandhu on Apr 20, 2009 1:01 PM

[Former Member · Accepted solution]

Hi Simran,

As u said this wud be rare case , y cannot u write a Oracle application to update those records to actual status back , this needs a client's understanding for this action to be done.

What is u r DB ?? For oracle this Sender stored procedure wonot work.

Anyways check : Sap Note : 941317

Even for Stored procedure u need to sent the Where condition varaible to be slected , but i see some problem .

If 50 records are selected first and converted to "Y" by sender JDBC.Now u need to select only 10 records to be selected of 'Y' status which u feel wrong , then even though pasing 'Y' in select wud b of no use as it wud select all 50 records.

So better use a application developed in u r respective DB to update those records back to intial stage.

and make Sender jdbc to run in normal way.

rgds

srini

[Former Member · Accepted solution]

Srini,

DB connection is with MS SQL server.

I think you are interpreting it in wrong way. The second time for fetching the erroneous records I want to change the where clause with different condition i.e. ID irrespective of the value of Flag.

Is it possible with the help of stored procedure?

So better use a application developed in u r respective DB to update those records back to initial stage.

This is not possible

Thanx,

Simran

[Former Member · Accepted solution]

Hi Simran,

Yes, it is possible with the help of store procedure. In SQL Server there is a IF-Else Conditional block, which you can use inside store procedure. Create a SP with 2 additional parameter something like FlagReadAgain and KeyField. Pass the flag always as True or False. For the Key field pass the record key which you want to read again if you are passing the Flag as True else pass it as 0 or whatever.

In the procedure check if the value of flag is True -> If yes, Read the entry with key name specified in where part.

In the else part put the query which you are using for the normal flow.

IF (FlagReadAgain) = TRUE
BEGIN
   SELECT EmpName, EmpAddress
   FROM Employee
   WHERE EmpID = 'E001' 
END
ELSE
   BEGIN
     SELECT EmpName, EmpAddress
     FROM Employee
     WHERE Status = 'N' 
   END

You may also use the approach which Srini mentioned. Create an application to restore any value to intial stage so that it can be picked up by XI when it poll again.

Choose any approach which you like.

Regards,

Jitender Chauhan

[Former Member · Accepted solution]

Hi

Well seing u r limitations u can use Stored procedure to achieve this , SQL with Stored procedure in Sender JDBC Adapter wonot be a problem.

In Stored procedure itself you should handle Update Statement instead of JDBC Sender Adapter config having the update statement, fool it with a dummy update.

any more queries lemme know

Srini

[Former Member]

Hi,

Just a suggestion: use feature of placeholder which replaces the content at runtime. More information at following link.

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Hope this helps.

Thanks and Regards,

Kalpesh

[Former Member]

Hi Simran,

Its not possible to change a query at runtime in the JDBC Adapter.

However you can use SQL EXECUTE statement with parameters to execute a stored procedure and in the store procedure you can use conditions which will be executed on the basis of values of that variable.

Something like Execute MyFunction 123

But make sure you use exactly one SELECT statement.

Regards,

Jitender