0

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.

JoshMc
  • 10,239
  • 2
  • 19
  • 38
Saurabh Patil
  • 4,170
  • 4
  • 32
  • 33

4 Answers4

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.

Saurabh Patil
  • 4,170
  • 4
  • 32
  • 33
0

Try setting it this way

passthru ('select CASE WHEN T2.X_FLG = '||VariableName||'

VariableName having value Y

Nishant
  • 2,975
  • 23
  • 38
  • Tried it, it doesn't work. Passthru considers first ' as end of string and doesn't concatenates with || as you expected. – Saurabh Patil Apr 27 '12 at 18:58
0
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>');

I mean to say that use double single quotes (''string value'') this will treat as a value rather than the termination of the query. Hope this will work.

And please take care this is not double quotes its single quote twice.

JoshMc
  • 10,239
  • 2
  • 19
  • 38
Mukesh Kumar
  • 1
0

You can write as

passthru('select CASE WHEN T2.X_FLG = ?', 'Y')

You simplay put ? and comma separated values after query respectively.

Muhammad Suleman
  • 2,892
  • 2
  • 25
  • 33