| Author |
Message
|
| mqhelpless |
 Posted: Wed Jul 13, 2005 10:46 pm Post subject: ESQL Eval & Concatenation |
 |
|
Apprentice
Joined: 13 Jul 2005
Posts: 33
|
I am trying to achieve reading data from different tables based on some variable value. Thus I have the following statement:
Eval('Set INT_X = THE (Select Count(*) FROM Database.MQSI.' || TableZ || ' AS T WHERE T.Field1 = ' || INT_A || ')');
where INT_X, INT_A, TableZ are variables, and TableZ will give me the correct table from which I should read my data.
However, the above syntax keeps giving me problem. I tried doing away with the EVAL, i.e.
Set INT_X = THE (Select Count(*) FROM Database.MQSI.TableZ AS T WHERE T.Field1 = INT_A)
and it works fine. Can anyone give me a pointer? Thanks. |
|
| Back to top |
|
 |
| mgk |
| Posted: Thu Jul 14, 2005 3:27 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
I would suggest you do not use EVAL for this, look at using PASSTHRU for dynamic DB access. It is similar, but it works MUCH faster.
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Jul 14, 2005 3:37 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I wouldn't use Passthrough either.
I'd use the { }.
something like
| Code: |
| Set INT_{variable} = THE (SELECT Count(*) from Database.MQSI.{TableZ} as T where T.Field1 = INT_A; |
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Jul 14, 2005 12:25 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
If that were possible I would have suggested it, but unfortunately it is not. You cannot use { ... } on part of a variable name, only on an element name in a field reference.
You also cannot use { ... } to dynamically specify a database table name in V5.
Hence, passthru being the easiest / fastest way to achieve this result.
Cheers,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Jul 14, 2005 1:07 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Ah, well. I'm out of practice.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| mqhelpless |
| Posted: Thu Jul 14, 2005 5:03 pm Post subject: |
|
|
Apprentice
Joined: 13 Jul 2005
Posts: 33
|
| Hi, thanks for the pointers. I will try it out. |
|
| Back to top |
|
|
| mqhelpless |
| Posted: Thu Jul 14, 2005 9:39 pm Post subject: |
|
|
Apprentice
Joined: 13 Jul 2005
Posts: 33
|
Tried doing the following:
Set INT_X = PASSTHRU('Select Count(*) FROM Database.MQSI.' || TableZ || ' AS T WHERE T.Field1 = ?', INT_A);
but keep hitting error. It was ok for deployment, but when throw in a message, the event log complains :
" ...the parameters passed were ''123456',' .... "
There seems to be 2 values passed in (?). I also tried casting INT_A into character set but to no avail. |
|
| Back to top |
|
|
| kirani |
| Posted: Thu Jul 14, 2005 9:54 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
not sure if this will work ..
| Code: |
Set INT_X = PASSTHRU('Select Count(*) FROM Database.MQSI.? AS T WHERE T.Field1 = ?', 'TableZ', INT_A);
|
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| mqhelpless |
| Posted: Thu Jul 14, 2005 10:30 pm Post subject: |
|
|
Apprentice
Joined: 13 Jul 2005
Posts: 33
|
| Nope. Doesn't seem to work. By the way, I'm using version 2.1. |
|
| Back to top |
|
|
| martinrydman |
| Posted: Fri Jul 15, 2005 12:55 am Post subject: |
|
|
Centurion
Joined: 30 Jan 2004
Posts: 139
Location: Gothenburg, Sweden
|
Hi,
You should drop the Database part of the table ref when using PASSTHRU
/Martin |
|
| Back to top |
|
|
| mqhelpless |
| Posted: Fri Jul 15, 2005 1:02 am Post subject: |
|
|
Apprentice
Joined: 13 Jul 2005
Posts: 33
|
| Yup martin, tried that also. Somehow it keeps running into problem with Eval or Passthru. Specifically, it keeps giving "the data types of the opreands for the operation = are not compatible. SQLSTATE=42818". |
|
| Back to top |
|
|
| mgk |
| Posted: Fri Jul 15, 2005 1:28 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi.
You can't pass the table name in as a parameter marker, you have to do something like this:
| Code: |
| Set INT_X = PASSTHRU('Select Count(*) FROM MQSI.' || TableZ || ' AS T WHERE T.Field1 = ?', INT_A); |
And you must not use the Database correlation name in a passthru as already pointed out. If it helps, what you are doing with passthru is constructing a complete ODBC statement. It must be valid ODBC for your DB, and any parameter markers (?) must be matched by the same number of parameters following the string
Cheers,
MGK
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| fschofer |
| Posted: Fri Jul 15, 2005 2:13 am Post subject: |
|
|
Knight
Joined: 02 Jul 2001
Posts: 524
Location: Mainz, Germany
|
Hi,
there's also no MQSI.XYZ AS T in PASSTHRU, use MQSI.XYZ T instead.
Greetings
Frank |
|
| Back to top |
|
|
| mgk |
| Posted: Fri Jul 15, 2005 4:51 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
fschofer wrote:
| Quote: |
| there's also no MQSI.XYZ AS T in PASSTHRU, use MQSI.XYZ T instead. |
This is not quite correct the presence of the AS clause will actually be dependent on the type of database being invoked (DB2, Oracle etc), as some will allow the AS and some will not.
The key point is that the string you give to passthru MUST be acceptable to the DB you are passing it to. What is acceptable will vary from DB to DB, which is one of the reasons why is is better to use the Brokers ESQL DB functions and statements (SELECT, INSERT etc) where possible so the user does not have to care about the DB differences, the broker will take care of these differences itself.
In this case passthru is required to dynamically choose to the table to execute the query on.
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| mqhelpless |
| Posted: Sun Jul 17, 2005 5:24 pm Post subject: |
|
|
Apprentice
Joined: 13 Jul 2005
Posts: 33
|
Hi Thanks for all your pointers. It finally works, by doing away with Database.MQSI.TableZ and using MQSI.TableZ instead.
I have another question. I'm wondering whether using Eval or Passthru is a better option. Can anyone provide any clues? Thanks. |
|
| Back to top |
|
|
|
|
