| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Urgent: String Data getting corrupted when getting from DB |
« View previous topic :: View next topic » |
| Author |
Message
|
| satish |
 Posted: Sun Dec 01, 2002 3:47 am Post subject: Urgent: String Data getting corrupted when getting from DB |
 |
|
Newbie
Joined: 26 Nov 2001
Posts: 9
|
Hi all,
I am have the following configuration:
WMQI 2.1 with CSD2 on Win 2000, accessing a Oracle DB Ver8.1.7 on an other Win2000 machine. the ODBC driver I am using is "Microsoft ODBC Driver for Oracle Ver 2.0".
I have a simple table with one character field and one number field.
In my compute node, I am doing a simple select for the character field, my query looks like this
declare J INTEGER;
declare str char;
set J = CAST(InputRoot.XML.EMP.EMPNO as INTEGER);
set str = CAST(THE (select ITEM T.EMPNAME from Database.SYSTEM.TEST AS T where T.EMPNO = J) AS CHAR);
SET OutputRoot.XML.Employee.EMPNAME = rtrim(str);
Regardless of what data is in the table, I am getting some junk characters after 3 characters. But number field is comming correctly. The field defined in Oracle is Character.
If anyone of you have faced similar problem, please let me know if there is anything I have to do.
I tried with different drivers:
1. with DataDirect4.1 Oracle - > I am unable to insert record but getting the same problem regarding select.
2. with Microsoft ODBC for Oracle 2.573 -> It is getting hung, when tryign to connect, my virtual memory is increasing to 2GB.
Any help in this regard is highly appreciated..
thanks in advance,
satish |
|
| Back to top |
|
 |
| Ian |
| Posted: Mon Dec 02, 2002 3:23 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Hi Satish,
Websphere MQ Integrator only supports specific drivers to connect to a database.
Refer to the WMQIv2.1 Administarion Guide :
| Quote: |
Defining an ODBC connection for the broker database
Oracle8
When you define a data source for Oracle8 you must choose the driver named "MQSeries MERANT 3.7 32-BIT Oracle8".
|
Note, this reference is applicable to both the broker and user database connection(s).
| Quote: |
WMQI 2.1 with CSD2 on Win 2000, accessing a Oracle DB Ver8.1.7 on an other Win2000 machine. the ODBC driver I am using is "Microsoft ODBC Driver for Oracle Ver 2.0".
|
In short, your configuration is not supported as you are using the "Microsoft ODBC Driver for Oracle" to connect to an Oracle database whereas you must to use the "MQSeries MERANT 3.7 32-BIT Oracle8" supplied with Websphere MQ Integrator.
Further, in a configuration where the database is remote, WMQI must still use the prescribed local driver which must then be accompanied by the relevant database client and server configuration.
In your case, this translates into something like :
The Datasource specified in the WMQI messageflow to connect to an Oracle database must have a matching ODBC Datasource which uses the "MQSeries MERANT 3.7 32-BIT Oracle8" driver. The "ServerName" for this ODBC definition must then have an entry in the Oracle "tnsnames.ora" which maps the local Oracle Client definition to the remote Oracle Server.
_________________
Regards, Ian |
|
| Back to top |
|
|
| satish |
| Posted: Mon Dec 02, 2002 4:15 am Post subject: |
|
|
Newbie
Joined: 26 Nov 2001
Posts: 9
|
Hi Ian,
Thanks for replying to the mail.
I have a small challenge, I do not have that supplement CD which contain this driver. I looked into the web and found that merant driver is now DataDirect driver, and in their website they provide only evaluation dowload of 4.1 version.
I also read in this list(I think this was from kirani) that for simple operations the default Microsoft provided driver must be sufficient.
Is there any workaround or is there any place where I can get that Merant driver V3.7.
Has anybody tried any other driver? Can you please let me know, the operations which I am looking at is simple Select, Insert and Update.
thanks once again,
satish |
|
| Back to top |
|
|
| Ian |
| Posted: Mon Dec 02, 2002 5:09 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Hi Satish,
When accessing an Oracle database from broker running under Windows, Websphere MQ Integrator only supports accessing this database via the "MQSeries MERANT 3.7 32-BIT Oracle8" driver.
Your following comment is not correct :
| satish wrote: |
for simple operations the default Microsoft provided driver might be sufficient
|
Further, the "MQSeries MERANT" drivers are supplied as part of the main WMQI installation (off the main CD) so if you have WMQI installed you will have the "MQSeries MERANT 3.7 32-BIT Oracle8" driver installed.
Go to the "ODBC Data Source Administrator" \ "System DSN" and select "Add" to create a new ODBC datasource definition. Scroll to the bottom of the list of drivers shown and you will see two entries "MQSeries MERANT 3.7 32-BIT Oracle8" and "MQSeries MERANT 3.7 32-BIT Sybase", select the Oracle one 
With respect to accessing databases and the supplemental CD, there is a "Prerequisite Software" requirement that MDAC2.5 is installed on your Windows NT or 2000 machine. This is either available via the supplemental CD or via the Microsoft website. [Note, I think MDAC2.5 or higher is already installed with Windows 2000 ?]
Refer to Websphere MQ Integrator for Windows NT and 2000 Version 2.1 - Installation Guide :
| Quote: |
Prerequisite Software
Microsoft Data Access Components (MDAC) Version 2.5
WebSphere MQ Integrator Version 2.1 uses ODBC drivers that require
MDAC Version 2.5. The WebSphere MQ Integrator Version 2.1
installation program checks the level of MDAC installed on your
system and issues a warning if you need to update your system.
MDAC Version 2.5 is provided on the Supplemental CD supplied with
the product. It is also available from the Microsoft Web site at
http://www.microsoft.com.
|
Refer to IBM WebSphere MQ Integrator Version 2.1 for Windows NT/2000 README for Supplemental CD :
| Quote: |
This CD contains supplemental materials to help setup your WebSphere
MQ Integrator for NT/2000 environment. The directory structure of
this CD is as follows:
MDAC25NT: This directory contains installable images to bring the
Microsoft DataAccess component on Windows NT up to the
2.5 level.
This is required in order to support ODBC access to
Oracle and Sybase databases by WMQI.
docs : This directory contains the packaged searchable
documentation in English for WebSphere MQ Integrator. You
require Adobe Acrobat Reader in order to view this. If
you do not already have Adobe Acrobat Reader you can
download it via www.adobe.com. To view the material you
should invoke Adobe Acrobat Reader against home.pdf in
the docs subdirectory.
|
I strongly recommend you ensure you are running with the correct driver and then verify whether there is still an issue or not.
_________________
Regards, Ian |
|
| Back to top |
|
|
| satish |
| Posted: Mon Dec 02, 2002 6:33 am Post subject: |
|
|
Newbie
Joined: 26 Nov 2001
Posts: 9
|
Hi,
I am not sure whether the previous post got posted or went to your private inbox. please forgive me if I am bothering you.
I have to get this going at any cost...
You are very correct, that Merant ODBC Driver was there with the main CD, I am not sure but was not installed last time and when I re-installed I was able to see it.
I have configured Merant Driver to point to theOracle and I had done the changes to tnsnames.ora. I restarted, redployed my broker
I now have a even stanger challenge. Both my insert and Select are now completly failing with the following error
2002-12-02 20:20:02.217999 1896 SqlExternalDbStmt::executeStmt , 1, 1, PROD.WORLD, SELECT T.EMPNAME FROM SYSTEM.TEST T WHERE (T.EMPNO)=(?), 2,
2002-12-02 20:20:02.217999 1896 ImbOdbcHandle::checkRcInner , -1
2002-12-02 20:20:02.217999 1896 ImbOdbcHandle::checkRcInner , IM001, 0, [Microsoft][ODBC Driver Manager] Driver does not support this function
my select stament remains the same.
the intresting thing is I am not able to see any merant error, i am alwys getting thwsi Microsoft odbc error? is this how you get the error displayed or am I doing something wrong.
I have MADC 2.5 installed on my machine.
please let me know if I am missing anything.
thanks once again..
regards,
satish |
|
| Back to top |
|
|
| Ian |
| Posted: Mon Dec 02, 2002 8:23 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Hi Satish,
Errors can originate from a number of sources and these differences will be reflected in the error message(s) written out in the WMQI syslog and ODBC trace.
For example the error could be within the database, driver or driver manager.
The general format for database errors is : [Driver Manager][Driver]"Error text" or [Driver Manager][Driver][Database]"Error text"
The error message you have included here would appear to indicate that you are still using the incorrect driver.
Suggest you verify that the datasource specified on the node in the messageflow matches the ODBC definition that you want to use.
A second suggestion would be to verify that you have configured the ODBC definition correctly (in particular the SQLDescribeParam, these settings are mandatory, not optional).
| Quote: |
Oracle8
When you define a data source for Oracle8 you must choose the driver named
"MQSeries MERANT 3.7 32-BIT Oracle8". When you configure this driver you
must:
--> Under the Advanced tab of the driver settings, ensure that the Enable
SQLDescribeParam box is checked.
--> Create a new registry subkey for each of your data source names (DSNs)
which reference an Oracle database, by using REGEDIT to navigate to:
HKEY_LOCAL_MACHINE
SOFTWARE
ODBC
ODBC.INI
Right-click on the DSN, and select New -> String Value. Specify WorkArounds
for the string, and set the value to 536870912.
|
It would also be good practise to confirm that you can access the remote database using SQLPlus where the command format is :
sqlplus username/password@servicename
where :
-> username is the DataSourceUserID/ServiceUserID as per the mqsicreatebroker command
-> password is the DataSourceUserPassword/ServiceUserPassword as per the mqsicreatebroker command
-> servivename is the service name as found in the tnsnames.ora files on the remote server
_________________
Regards, Ian |
|
| Back to top |
|
|
| satish |
| Posted: Mon Dec 02, 2002 11:42 pm Post subject: |
|
|
Newbie
Joined: 26 Nov 2001
Posts: 9
|
Ian,
Thanks very much for all the effort you took to help me.
It is now working perfectly fine.
thanks once again,
satish |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
