| Author |
Message
|
| Pete |
 Posted: Fri Dec 10, 2004 11:03 am Post subject: Error inserting Blob to Oracle in Passthru |
 |
|
Apprentice
Joined: 25 Nov 2003
Posts: 43
|
Hi,
I'm getting ORA-01008 - Not all variables bound from the following code:
DECLARE BLOB_DATA BLOB;
SET BLOB_DATA = InputRoot."BLOB"."BLOB";
PASSTHRU('INSERT INTO <schema>.<table> (SAVE_MQMD_MSG_ID, DATA, PACKET_ID)
values (?, ?, ?)', BLOB_STRING,
SUBSTRING(CAST(BLOB_DATA AS CHAR) FROM 3
FOR LENGTH(BLOB_DATA)-3)), '');
WBIv5
Oracle10G
AIX
Any ideas?
Thanks,
Pete |
|
| Back to top |
|
 |
| JT |
| Posted: Mon Dec 13, 2004 8:29 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
| Quote: |
DECLARE BLOB_DATA BLOB;
SET BLOB_DATA = InputRoot."BLOB"."BLOB";
PASSTHRU('INSERT INTO <schema>.<table> (SAVE_MQMD_MSG_ID, DATA, PACKET_ID)
values (?, ?, ?)', BLOB_STRING,
SUBSTRING(CAST(BLOB_DATA AS CHAR) FROM 3
FOR LENGTH(BLOB_DATA)-3)), ''); |
Please post all of your ESQL code. |
|
| Back to top |
|
|
| Pete |
| Posted: Tue Dec 14, 2004 6:06 am Post subject: |
|
|
Apprentice
Joined: 25 Nov 2003
Posts: 43
|
We are persisting the message data, keyed on the message id.
Against DB2 I have just inserted the BLOB directly into a BLOB column, the problem arises against Oracle.
Code is as follows:
| Code: |
DECLARE BLOB_MQMDID BLOB;
DECLARE BLOB_MQMDID_STRING CHARACTER ;
DECLARE BLOB_MQMDID_STRING_LENGTH INT;
DECLARE BLOB_DATA BLOB;
DECLARE BLOB_DATA_STRING CHARACTER;
DECLARE BLOB_DATA_STRING_LENGTH INT;
--
-- The unique message identifier (MQMD.ID)
--
SET BLOB_MQMDID = CAST(InputRoot.MQMD.MsgId AS BLOB);
SET BLOB_MQMDID_STRING = CAST(BLOB_MQMDID AS CHARACTER);
-- Subtract 3 for the X''
SET BLOB_MQMDID_STRING_LENGTH = ENGTH(BLOB_MQMDID_STRING) - 3;
-- Now remove the leading X' and trailing '.
SET BLOB_MQMDID_STRING = SUBSTRING(BLOB_MQMDID_STRING FROM 3 FOR BLOB_MQMDID_STRING_LENGTH);
--
-- The message data (BLOB.BLOB)
--
SET BLOB_DATA = CAST(InputRoot."BLOB"."BLOB" AS BLOB);
SET BLOB_DATA_STRING = CAST(BLOB_DATA AS CHARACTER);
-- Subtract 3 for the X''
SET BLOB_DATA_STRING_LENGTH = LENGTH(BLOB_DATA_STRING) - 3;
-- Now remove the leading X' and trailing '.
SET BLOB_DATA_STRING = SUBSTRING(BLOB_DATA_STRING FROM 3 FOR BLOB_DATA_STRING_LENGTH);
--
-- Insert the data and unique identifier
--
PASSTHRU('INSERT INTO WBIADMIN.APILIST02(SAVE_MSG_ID, DATA, PACKET_ID) values (?, ?, ?)', BLOB_MQMDID_STRING, BLOB_DATA_STRING, NULL);
END;
|
|
|
| Back to top |
|
|
| JT |
| Posted: Thu Dec 16, 2004 6:51 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
Pete,
There doesn't appear to be anything wrong with the ESQL, except the following typo:
| Quote: |
-- Subtract 3 for the X''
SET BLOB_MQMDID_STRING_LENGTH = LENGTH(BLOB_MQMDID_STRING) - 3; |
I was able to execute your code (as is) successfully in the following configuration: Solaris 2.8, Oracle 9.2, WBIMB v5.0.2
How are the database elements defined? |
|
| Back to top |
|
|
| Pete |
| Posted: Fri Dec 17, 2004 1:27 am Post subject: |
|
|
Apprentice
Joined: 25 Nov 2003
Posts: 43
|
JT,
Thanks for your reply.
The table description is:
SAVE_MSG_ID, NOT NULL, VARCHAR2(252)
DATA, NOT NULL, BLOB
PACKET_ID, NOT NULL, VARCHAR2(150)
I failed to mention we are using the Oracle 10g client in my earlier post.
Thanks again,
Pete |
|
| Back to top |
|
|
| JT |
| Posted: Fri Dec 17, 2004 6:40 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
| Quote: |
| PACKET_ID, NOT NULL, VARCHAR2(250) |
You defined PACKET_ID to not allow NULLs, yet you're attempting to insert a NULL value. Although that's not the condition that is causing the error you're receiving, you will have to make an adjustment at some point. That condition should result in an ORA-01400 cannot insert NULL error.
Using your data definitions, I was again, able to successfully insert a record into the database. I originally defined the database elements as:
SAVE_MSG_ID, NOT NULL, BLOB
DATA, NOT NULL, CLOB
PACKET_ID, NULL, VARCHAR2(255)
Perhaps it's a difference between the 10g & 9i clients. Do you have access to a 9i client? |
|
| Back to top |
|
|
| Pete |
| Posted: Mon Dec 20, 2004 12:32 am Post subject: |
|
|
Apprentice
Joined: 25 Nov 2003
Posts: 43
|
Your correct regarding the NOT NULL, that column is infact nullable.
As for the Oracle client, I will be looking to get a 9i version but it wont be untill the new year now.
Thanks for your help,
Have a Happy Christmas!
Pete |
|
| Back to top |
|
|
| JT |
| Posted: Mon Dec 20, 2004 7:25 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
| And a Merry Christmas to you and your family. |
|
| Back to top |
|
|
| Ian |
| Posted: Mon Apr 11, 2005 5:24 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
To clarify the following point :
WBIMBv5.0 does not support Oracle 10g. This includes via both Oracle 9i and 10g clients.
The reason for this is as follows :
1. The WBIMBv5.0 reference to database support refers to the database client which is local to the broker
2. The WBIMBv5.0 SOE indicates support for Oracle 9i Release 2 but does not include Oracle 10g
3. WBIMBv5.0 uses the DataDirect 4.1 Oracle driver which does not support Oracle 10g
4. It is my understanding that in Oracle 10g they have changed the way LOB datatypes are defined/returned to the driver manager and hence the ORA-01008 error
_________________
Regards, Ian |
|
| Back to top |
|
|
| Pete |
| Posted: Mon Apr 11, 2005 8:54 am Post subject: |
|
|
Apprentice
Joined: 25 Nov 2003
Posts: 43
|
Ian,
Thanks alot for the info.
I've now left that project, however the client I'm currently working for, as far as I am aware is planning to use 10g, which from the sounds of it may have to be re-thought.....
Cheers,
Pete |
|
| Back to top |
|
|
| Pete |
| Posted: Tue Apr 12, 2005 3:38 am Post subject: |
|
|
Apprentice
Joined: 25 Nov 2003
Posts: 43
|
Ian,
The client I am currently working for already has Oracle10g.
Is there a current IBM issues list for WBIMB using Oracle10g? If so can you send me a copy pls?
Thanks,
Pete |
|
| Back to top |
|
|
| Ian |
| Posted: Thu Apr 28, 2005 7:13 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Hi Pete,
I have not had the opportunity to log on to this forum for a while and have just picked up this question ...
In reply to your question, I am not aware of an "WBIMB Oracle 10g" issues list.
However, it is worth noting that only WBIMB v5 on Linux Intel supports Oracle 10g.
Support for this is enabled at FP4 and FP5 via interim fix LCR016.
You need to request LCR016 from IBM support.
For further information on this topic, refer to my post here : http://www.mqseries.net/phpBB/viewtopic.php?t=21775
_________________
Regards, Ian |
|
| Back to top |
|
|
|
|
