WMB ESQL passing constants to passthru

Question

I am working on IBM Websphere Message Broker v6.0 and writing a message flow in ESQL. I am using passthru to fire a select query on database.

Now the problem is passthru uses ' to start and end its parameter string. And we use ' in SQL to denote constants - something like WHERE T1.col1 = 'Y'. So when I am using ' in the passthru parameter it is considering it as teh end of the string.

Here is the ESQL code:

SET result1 = passthru ('select CASE WHEN T2.X_FLG = 'Y' AND (SELECT D2.RGN FROM V1.X AS D2
WHERE D2.DLR = V.DLR AND (D2.RGN = '500' OR D2.RGN = '600')) FROM V1
<left join all the tabels>');

Now the problem is passthru parameter string ends in first ' that is just before Y - the parameter to database select query. ESQL compiler is giving me compiler error saying its a syntax error.

Is there some character which ahs to be appended before ' so that to recognise it as the charater ' and not as end of string to passthru?? Something like \' or /' ? I have tried all this but nothing seems to work.

Answer 1

Figured it out! There is another clause (should I say) which allows us to pass the constant values to the SQL statement and that is written after the ' of the SQL query completes. The places where we need the constant values should have ? and it marks the places for insertion. The insertion takes in the order we place the constant values in the "values" tag. So, the above passthru query can be written as:

SET result1 = passthru ('select CASE WHEN T2.X_FLG = ? AND (SELECT D2.RGN FROM V1.X AS D2
WHERE D2.DLR = V.DLR AND (D2.RGN = ? OR D2.RGN = ?)) FROM V1
<left join all the tables>' to Database.{datasource_name} values('Y','500','600');

Remember, the ? should not be enclosed in the brackets.