| Author |
Message
|
| cloucas |
 Posted: Tue Jan 09, 2007 2:05 am Post subject: Accessing an sql database from ESQL |
 |
|
Apprentice
Joined: 10 Mar 2006
Posts: 37
|
I am developing a message flow which is composed of an MQInput, Compute and MQOutput nodes.
The message coming into the MQInput Node is composed of just one string element, customerCode. Based on the value of the element I contsruct a host request in the Compute node and send it to the MQOutput node sucessfully.
I would now like to use the string value received in the input node to look up the customerName from a database table which I would then like to insert into my request message as well. I have an SQL 2005 Server database configured and given the broker user access to it. I have also created a DSN on the broker server and inserted the DSN name as the Data Source property of the compute node.
I then try to access the database like below:
SET OutputRoot.MRM.LAST_NAME = SELECT Name FROM ELSTestDB.CIFInquiries;
where ELSTestDB is the DSN name of the ODBC connection.
The ESQL editor gives me a warning "Identifier ELSTestDB cannot be resolved". I have also tried
SET OutputRoot.MRM.LAST_NAME = SELECT Name FROM Database.CIFInquiries;, but I get the warnng "Unresolvable database table reference Database.CIFInquiries.";
I also tried to create a database definition, schema and table, but no avail.
How should I correctly go about to set up the database connection and access it from ESQL? I have looked in manuals but found no clear procedure.
Thanks in advance
Chris |
|
| Back to top |
|
 |
| Vitor |
| Posted: Tue Jan 09, 2007 2:19 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
Which version of MB? I found the section in the v6 Info Centre "Selecting data from database columns" informative in respect of your situation.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| cloucas |
| Posted: Tue Jan 09, 2007 2:37 am Post subject: |
|
|
Apprentice
Joined: 10 Mar 2006
Posts: 37
|
We use 5.1
I looked a bit into that as well, but couldn't get to the cause of the problem with referencing the database from the esql.....unless I am missing something
Thanks
Chris |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Jan 09, 2007 2:38 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
THe warning you get is just that. It's a warning, and it only applies in the Toolkit.
You can ignore the warning.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| gregop |
| Posted: Tue Jan 09, 2007 2:41 am Post subject: |
|
|
Voyager
Joined: 24 Nov 2006
Posts: 81
|
| Quote: |
SET OutputRoot.MRM.LAST_NAME = SELECT Name FROM Database.CIFInquiries;, but I get the warnng "Unresolvable database table reference Database.CIFInquiries.";
|
Try: SET OutputRoot.MRM.LAST_NAME = SELECT T.Name FROM Database.CIFInquiries AS T ;
Don't worry about the Unresolvable warning. You'll get that on DB selects unless you import the database definition (and I've never worked at a shop that does). |
|
| Back to top |
|
|
| cloucas |
| Posted: Tue Jan 09, 2007 11:50 pm Post subject: |
|
|
Apprentice
Joined: 10 Mar 2006
Posts: 37
|
I have done the above and get the following error:
RecoverableException BIP2519E: (, 1.1) : Error executing SQL statement 'SELECT T.Name FROM CIFInquiries T WHERE (T.CIF)=(?)' against datasource 'ELSTestDB' with parameters ''01035355', '.
The following error occurred during execution of a database SQL statement against datasource 'ELSTestDB'. The SQL statement was 'SELECT T.Name FROM CIFInquiries T WHERE (T.CIF)=(?)'. The parameters passed were ''01035355', '.
2007-01-10 08:08:41.829364 2480 DatabaseException BIP2324E: Unsupported datatype '-9' fetched from database table column 'Name'.
A message flow node which performs a database fetch returned a datatype not supported by the message broker. The database table column 'Name' contain an unsupported datatype identified by '-9'.
Either update the message flow to eliminate the reference to the database column with the unsupported datatype, or modify the database schema to change the column to be of a type supported by the message broker.
I don't understand why the broker tries to pass 2 parameters to the query while the select statement only accepts one.....any ideas? |
|
| Back to top |
|
|
| gregop |
| Posted: Wed Jan 10, 2007 12:37 am Post subject: |
|
|
Voyager
Joined: 24 Nov 2006
Posts: 81
|
What is the datatype of Name ?
You can see the supported data types by searching info center for:
'external source data types' |
|
| Back to top |
|
|
| cloucas |
| Posted: Wed Jan 10, 2007 1:55 am Post subject: |
|
|
Apprentice
Joined: 10 Mar 2006
Posts: 37
|
thanks for this, i had to change type nvarchar as it is not supported....almost there as i am now reading the value from the database table. I now get the following error:
2007-01-10 11:45:35.005249 1824 ParserException BIP5339E: Mismatch between message tree and logical definition
While writing the message, the message tree did not match the logical structure of the message. The problem was encountered while attempting to write element 'Name' in message set 'A2GJ920002001'.
This problem is usually caused by a badly-structured input message. It can also be caused by a badly-structured output message tree. Check that the structure of the input message matches the logical structure of the message. Check that elements defined as simple elements do not have child elements. If necessary, check that elements occur in the correct sequence, and with the correct number of repeats. If you have modified the message set or message type property in your message flow, check that these are correct.
The field in the OutputRoot.MRM is defined as string.....any ideas? |
|
| Back to top |
|
|
| gregop |
| Posted: Wed Jan 10, 2007 2:27 am Post subject: |
|
|
Voyager
Joined: 24 Nov 2006
Posts: 81
|
Your MRM message is expecting some data in element Name that is not present in OutputRoot.MRM
If you can't see the problem in your esql place a Trace node with pattern ${Root} prior to your MQOutput node. Then run a user trace. |
|
| Back to top |
|
|
| cloucas |
| Posted: Wed Jan 10, 2007 4:05 am Post subject: |
|
|
Apprentice
Joined: 10 Mar 2006
Posts: 37
|
Finally got this to work....it appears that the returned object of the query was indeed a complex type and thus I needed to reference the Name element to get to my desired value.
Thanks a lot for your help! |
|
| Back to top |
|
|
|
|
