| Author |
Message
|
| pvemuri |
 Posted: Tue Jan 20, 2004 2:13 pm Post subject: ODBC ERROR |
 |
|
Voyager
Joined: 05 Jan 2004
Posts: 83
Location: CA, USA
|
hi all,
I have a compute node in my message flow. It uses the SAMPLE database of my DB2 ( V 8.1 enetrprise server edition). I used a basic ESQL statement in the compute node to select some values from the DEPARTMENT table.
| Code: |
| SET some XML element = THE (SELECT ITEM DEPARTMENT.LOCATION FROM DEPARTMENT WHERE (DEPARTMENT.DEPTNO)=('E21') ); |
The trace node's output says that
| Quote: |
DatabaseException BIP2321E: Database error: ODBC return code '-1'. The message broker encountered an error whilst executing a database operation. The ODBC return code was '-1'. Error Text '[IBM][CLI Driver][DB2/NT] SQL0204N "MQSIUID.DEPARTMENT" is an undefined name. SQLSTATE=42704 '. The error has the following diagnostic information: SQL State '42S02' SQL Native Error Code '-204' SQL Error Text '[IBM][CLI Driver][DB2/NT]SQL0204N "MQSIUID.DEPARTMENT" is an undefined name. SQLSTATE=42704
|
I tried everyt thing like: using the table name as "Database.Department / Database.db2admin.Department / just Department / SAMPLE.Department etc" in my ESQL script. I also added the SAMPLE database and the DEPARTMENT table to my COMPUTE node. Also double checked the ODBC configurations. Still the error persists. There is some ODBC problem and I am unable to figure out what it may be. Your help is appreciated.
Thanks,
Phani. |
|
| Back to top |
|
 |
| Missam |
| Posted: Tue Jan 20, 2004 2:19 pm Post subject: |
|
|
Chevalier
Joined: 16 Oct 2003
Posts: 424
|
Hey Did u added the Datasource and Table name to input pane of compute node
try this
SET some XML element = THE (SELECT ITEM D.LOCATION FROM Database.DEPARTMENT AS D WHERE D.DEPTNO='E21' ); |
|
| Back to top |
|
|
| Meow |
| Posted: Tue Jan 20, 2004 3:05 pm Post subject: |
|
|
Voyager
Joined: 25 Jun 2003
Posts: 95
|
As long as you give the valid data source name and table name in the compute node this should be fine.
so you can add
say your DSN name is ODBCDSN
then you can add ODBCDSN.EMPLOYEE and still use
Database.Department.
Let me know if i am wrong.
Thanks,
meow |
|
| Back to top |
|
|
| pvemuri |
| Posted: Tue Jan 20, 2004 4:44 pm Post subject: clueless about this ODBC config - tried everything |
|
|
Voyager
Joined: 05 Jan 2004
Posts: 83
Location: CA, USA
|
dear sam and meow,
Thank you both for the replies,
I added the Database name and both table names to the input pane of the COMPUTE node. Still the error's same. Also I tried using Database.DEPARTMENT, SAMPLE.DEPARTMENT ( SAMPLE is my ODBC name) and lot other ways. Still the error persists. I tried various ESQL queries also. I am completely clueless. Any help will bail me out.
Thanks in advance,
Phani. |
|
| Back to top |
|
|
| CoolDude |
| Posted: Tue Jan 20, 2004 7:02 pm Post subject: |
|
|
Apprentice
Joined: 17 Jan 2004
Posts: 39
|
Hey
Can you post the exact error message that you are getting? What does the trace files say?
Looks like a ODBC error.
Try this
Add SAMPLE to your input pane of the compute node
Set OutputRoot.XML.Query.Result = THE (SELECT ITEM D.location from Database.Department as D where D.DeptNo = 'E21') ;
also make sure that your broker id has permissions to query that database.
Thanks, 
_________________
Correct Me from Wrong  . If i am correct Appreciate Me  |
|
| Back to top |
|
|
| Missam |
| Posted: Tue Jan 20, 2004 7:42 pm Post subject: |
|
|
Chevalier
Joined: 16 Oct 2003
Posts: 424
|
Hey Did u see this
| Quote: |
Error Text '[IBM][CLI Driver][DB2/NT] SQL0204N "MQSIUID.DEPARTMENT" is an undefined name. SQLSTATE=42704
|
What is MQSIUID.DEPARTMENT
Is this your ODBC Name
Did u Check This Connection Is Working.May Be you are missing some thing here. |
|
| Back to top |
|
|
| EddieA |
| Posted: Tue Jan 20, 2004 8:14 pm Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
DEPARTMENT is the table it's trying to read.
MQSIUID is the schema (or userid) it's using.
So, does MQSUID have permissions to read DEPARTMENT. If not, then 'force' a Schema on the ESQL by adding it's name between Database and DEPARTMENT: Database.Schema.DEPARTMENT.
Does DEPARTMENT exist in thge Database you are pointing to with your ODBC.
The ODBC name should be the 'local' name of the Database where DEPARTMENT is a table. This is also what you Add to the Input pane of the Compute node.
If this is Windows, you can confirm that the ODBC does actually connect, and that the UserID is valid by selecting Configure in the ODBC Control Panel.
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| pvemuri |
| Posted: Tue Jan 20, 2004 8:52 pm Post subject: ERROR RESOLVED |
|
|
Voyager
Joined: 05 Jan 2004
Posts: 83
Location: CA, USA
|
My dear gurus , fellow newbie's and friends...
Thanks to your invaluable replies ... I resolved the issue.
My query is as follows :
| Code: |
SET OutputRoot.XML.Request.Country = THE
(SELECT ITEM D1.LOCATION FROM
Database.Admin.DEPARTMENT AS D1
WHERE D1.DEPTNO = THE
(SELECT E1.WORKDEPT
FROM Database.Admin.EMPLOYEE AS E1
WHERE E1.EMPNO = InputRoot.XML.Request.EmpNo));
|
Instead of using "Database.Admin.DEPARTMENT" I was trying to use "Database.MQSIUID.DEPARTMENT". I completed all the formalities like adding the Database name to the input pane of the COMPUTE node and giving sufficient authorizations to the Databases etc; The broker was connnecting to the DB as MQSIUID where as the database was created with ADMIN being the User ID. It was just a matter of authorizations. Sorry for bothering you all for this silmple reason.... and I appreciate your replies.
Thanks,
Phani. |
|
| Back to top |
|
|
| CoolDude |
| Posted: Wed Jan 21, 2004 5:37 am Post subject: |
|
|
Apprentice
Joined: 17 Jan 2004
Posts: 39
|
Is Admin your schema name?
The solution specified by Eddie is used worldwide.
Whether to specify the schema name should be forced or not is dependent on your ODBC configuration.
Unless you get the correct schema name of the database that you are trying to query upon, you cannot get it working always(esp when connecting to external databases).
The solutions that were posted in this forum are not invaluable.
Thanks,
_________________
Correct Me from Wrong . If i am correct Appreciate Me |
|
| Back to top |
|
|
|
|
