| Author |
Message
|
| kwelch |
 Posted: Fri Jan 11, 2002 1:55 pm Post subject: |
 |
|
Master
Joined: 16 May 2001
Posts: 255
|
I was finally able to get my id/password problems resolved and now find myself unable to update the ORACLE databse unless I hard code literal values.
I am using BLOB input and I am getting an error saying that a literal string is too long for a column. Sounds easy right? It doesn't tell me what column though. I have tried eliminating column for column and it doesn't seem to like any of them, which leads me to believe it's an issue with the BLOB data. The part that bothers me though is this same message flow will update a DB2(UDB) table on an NT box. I should tell you that the ORACLE database lives on a UNIX box and my MQSI process is on NT and I am using v2.0.1 of MQSI.
Does anyone have any ideas or thoughts on this? Thanks for any ideas or suggestions in advance.
Karen |
|
| Back to top |
|
 |
| ravi |
| Posted: Fri Jan 11, 2002 2:41 pm Post subject: |
|
|
Novice
Joined: 07 Nov 2001
Posts: 16
|
Hi,
i came across the similar problem while trying to insert into sybase database while it was working for DB2. It got fixed when i applied CSD1 to MQSI2.0.2. but i have never seen this documented by IBM.
i guess its the problem with the ODBC calls to the drivers other than DB2.
it works fine with the WMQI2.1.
ravi. |
|
| Back to top |
|
|
| kwelch |
| Posted: Fri Jan 11, 2002 2:49 pm Post subject: |
|
|
Master
Joined: 16 May 2001
Posts: 255
|
Hi Ravi,
So it could be the ODBC drivers are not correct? I have been told that for ORACLE we need to use the MERANT ORACLE ODBC Drivers. We did not have those installed so they just used the Oracle ODBC Drivers. Someone else said that the MERANT ones were only if MQSI were on UNIX? Can anyone confirm this? Thanks again for your help!
Karen
|
|
| Back to top |
|
|
| ravi |
| Posted: Fri Jan 11, 2002 2:58 pm Post subject: |
|
|
Novice
Joined: 07 Nov 2001
Posts: 16
|
IBM insists you to use the merant driver where ever is your MQSI(window or unix).
ravi. |
|
| Back to top |
|
|
| kwelch |
| Posted: Fri Jan 11, 2002 3:01 pm Post subject: |
|
|
Master
Joined: 16 May 2001
Posts: 255
|
Ravi,
But in your case you still had issues even with the MERANT Drivers?
Could you point me to the documentation where it says that we need to use the MERANT drivers so I can show my MQSI techies?
Thanks so much for your help!!!! It is greatly appreciated!
Karen |
|
| Back to top |
|
|
| ravi |
| Posted: Fri Jan 11, 2002 3:10 pm Post subject: |
|
|
Novice
Joined: 07 Nov 2001
Posts: 16
|
yes, i had issues even while i was using Merant driver for sybase.
About the drivers, i guess it is mentoined several places.
look in the Administration guide.
chapter2.How to configure your MQSeries --> Defining and Athourising Database Resources --> Database setup and configuration
ravi.
|
|
| Back to top |
|
|
| ravi |
| Posted: Fri Jan 11, 2002 3:13 pm Post subject: |
|
|
Novice
Joined: 07 Nov 2001
Posts: 16
|
sorry, it was chapter 2.How to configure your MQSeries Integrator network
hope you found it.
ravi. |
|
| Back to top |
|
|
| kwelch |
| Posted: Fri Jan 11, 2002 3:27 pm Post subject: |
|
|
Master
Joined: 16 May 2001
Posts: 255
|
Ravi,
No problem. I did find it but in my documentation for Windows NT it just says to select the appropriate driver for your datasource. For the Solaris and AIX it specifically says use the Merant ones. This is probably why my techies think it only pertains to the UNIX.
Thanks again.
Karen
|
|
| Back to top |
|
|
| kirani |
| Posted: Fri Jan 11, 2002 3:27 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Karen,
Can you post your ESQL code that you are using to insert into ORACLE database. What version of ORACLE dataabse are you using?
Kiran
|
|
| Back to top |
|
|
| kwelch |
| Posted: Fri Jan 11, 2002 3:38 pm Post subject: |
|
|
Master
Joined: 16 May 2001
Posts: 255
|
Kiran,
Here is the ESQL:
DECLARE PGMNAME BLOB;
SET PGMNAME = SUBSTRING(Body.BLOB FROM 1 FOR 53); /* WILL HOLD PROGNAME */
DECLARE QUEUE BLOB;
SET QUEUE = SUBSTRING(Body.BLOB FROM 54 FOR 4 ; /* WILL HOLD QUEUE */
DECLARE QMGR BLOB;
SET QMGR = SUBSTRING(Body.BLOB FROM 102 FOR 4; /* WILL HOLD QMGR */
DECLARE MSGLNGTH INTEGER;
SET MSGLNGTH = LENGTH(Body.BLOB);
DECLARE ORIGLNGTH INTEGER;
SET ORIGLNGTH = MSGLNGTH - 250; /*THE FIXED AMOUNT OF HIG FIELDS ADDED TO ORIG MSG */
DECLARE REST BLOB;
SET REST = SUBSTRING(Body.BLOB FROM 251 FOR ORIGLNGTH);
INSERT
INTO Database.T040.THO_BSE_MQLOGMSG(HIG_PGM_NM, HIG_APPL_QUE_NM, HIG_APPL_QMGR_NM, MQS_ORIG_MESSAGE)
VALUES (PGMNAME, QUEUE, QMGR, REST);
I have verified that the columns are all the correct length, except for the REST field which is the original message being logged. It is defined as LONG which I am told can be up to 2 gig. The others are 53, 48, 48 respectively.
Karen
|
|
| Back to top |
|
|
| kwelch |
| Posted: Fri Jan 11, 2002 3:53 pm Post subject: |
|
|
Master
Joined: 16 May 2001
Posts: 255
|
Kiran,
Sorry I forgot to answer your Oracle version question. I think we are using Oracle 8.1.7?I can verify for sure on Monday.
Karen |
|
| Back to top |
|
|
| kirani |
| Posted: Fri Jan 11, 2002 4:07 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Karen,
You need to consider following points when dealing with ORACLE in MQSIV2. These points are from readme.txt file of MQSI V2.0.1.
1. Using Oracle 7.3.4 Enterprise Edition For Windows NT
-------------------------------------------------------
Where Oracle 7.3.4 is to be used with MQSI V2.0.1 for Windows NT, users need to ensure they are using an Oracle 8 Client Version to access their Oracle 7 Server. Oracle 8 Clients provide the correct level of ODBC support required by MQSI V2.0.1 for Windows NT.
2. Inserting Data into BLOB Columns in Oracle DB
------------------------------------------------
The Oracle drivers do not directly support inserting MQSIv2 BLOBs into Oracle BLOB columns. The workaround here is to use ESQL like this:
DECLARE B BLOB;
...
PASSTHRU('INSERT INTO Database.table1 (blob_field) VALUES( ? )',
SUBSTRING(CAST(B AS CHAR) FROM 3 FOR (LENGTH(B)-3));
This will pass a correctly formatted character string representing the BLOB to Oracle via the driver, removing the leading X' and trailing ' from the character string.
Try converting your ESQL into something like this,
PASSTHRU(
'INSERT INTO T040.THO_BSE_MQLOGMSG(HIG_PGM_NM, HIG_APPL_QUE_NM, HIG_APPL_QMGR_NM, MQS_ORIG_MESSAGE) VALUES (?,?,?)',
SUBSTRING(CAST(PGMNAME AS CHAR) FROM 3 FOR (LENGTH(PGMNAME)-3)),
SUBSTRING(CAST(QUEUE AS CHAR) FROM 3 FOR (LENGTH(QUEUE)-3)),
SUBSTRING(CAST(REST AS CHAR) FROM 3 FOR (LENGTH(REST)-3)));
Good luck!
Kiran
|
|
| Back to top |
|
|
|
|
