| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Error inserting into Oracle database using PASSTHRU |
« View previous topic :: View next topic » |
| Author |
Message
|
| Ash |
 Posted: Fri Jan 11, 2002 9:50 am Post subject: |
 |
|
Novice
Joined: 07 Jan 2002
Posts: 17
|
Hi,
I am trying to insert a record into a Oracle 8.1.7 database from MQSI v2.0.2 on Solaris 2.7 but keep on getting the following error message....
DatabaseException BIP2322E: Database error: SQL State 'HY000'; Native Error Code '926'; Error Text '[MERANT][ODBC Oracle 8 driver][Oracle 8]ORA-00926: missing VALUES keyword '.
The ESQL that I am using is :
PASSTHRU('INSERT INTO Database.LOGDB.T_LOG (SRC_APP, DST_APP, MESSAGE_BODY)
VALUES (?, ?, ?)',
Root.XML.LOGHDR.SRCAPP
,Root.XML.LOGHDR.DSTAPP
,BITSTREAM(Body)
);
Is there something wrong with the syntax of the PASSTHRU statement?
I am using PASSTHRU for the first time and am going absolutely nuts trying to figure out why I keep getting the above error as I have got the VALUES keyword in my statement.
Much Appreciated.
Cheers.
Ash. |
|
| Back to top |
|
 |
| ravi |
| Posted: Fri Jan 11, 2002 3:46 pm Post subject: |
|
|
Novice
Joined: 07 Nov 2001
Posts: 16
|
There is no need of Database keyword. but it is supposed to give the error as 'syntax error near the keyword database.
i suppose it should be working without Database keyword as follows :
PASSTHRU('Insert into LOGDB.T_LOG (SRC_APP, DST_APP, MESSAGE_BODY)
Values (?, ?, ?)',
Root.XML.LOGHDR.SRCAPP
,Root.XML.LOGHDR.DSTAPP
,BITSTREAM(Body)
);
|
|
| Back to top |
|
|
| Ash |
| Posted: Mon Jan 14, 2002 7:46 am Post subject: |
|
|
Novice
Joined: 07 Jan 2002
Posts: 17
|
Thanks Ravi.
It worked without the Database keyword.
The syntax of the Insert statement mentions the Database keyword to explicitly state that this is a database insert.I guess you don't need that if you are using PASSTHRU as the statement is passed on directly to the Database.
However, I have now got a different issue that's cropped up.
The data that is being inserted into the MESSAGE_BODY column is in hex i.e. '3CABCDXY' etc.
The same Insert statement without PASSTHRU with MQSI v2.0.1 & Oracle 8.1.5 used to populate the MESSAGE_BODY column (defined as a CLOB in Oracle) with a character string that was human readable.
After upgrading to MQSI V2.0.2 & Oracle 8.1.7, the same data was being inserted in hex format.
This is what prompted me to try out the PASSTHRU statement hoping that this would work.However, the result is the same with PASSTHRU i.e. hex characters in the database.
Instead of the BITSTREAM function, I then tried to use the CAST as suggested in the readme and the topic just above this.
The resulting ESQL looked like this:
DECLARE MSGBODY BLOB;
SET MSGBODY = Root."BLOB"."BLOB";
PASSTHRU('INSERT INTO LOGDB.T_LOG (SRC_APP, DST_APP, MESSAGE_BODY)
VALUES (?, ?, ?)',
Root.XML.LOGHDR.SRCAPP
,Root.XML.LOGHDR.DSTAPP
,SUBSTRING(CAST(MSGBODY AS CHAR) FROM 3 FOR (LENGTH(MSGBODY)-3))
);
This still inserts hex characters into the database instead of a text string.
How can I get the original text string inserted into Oracle instead of the hex characters.
I still can't understand how we were getting a proper character string inserted into Oracle with the BITSTREAM function in MQSI v2.0.1 and now end up getting hex characters inserted.
Regards,
Ash |
|
| Back to top |
|
|
| mpuetz |
| Posted: Tue Jan 15, 2002 4:22 pm Post subject: |
|
|
Centurion
Joined: 05 Jul 2001
Posts: 149
Location: IBM/Central WebSphere Services
|
Hi,
you can convert the message body from BLOB to readable CHAR using the techniques discussed in the BLOB to CHAR topic.
_________________
Mathias Puetz
IBM/Central WebSphere Services
WebSphere Business Integration Specialist |
|
| 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
|
|
|
|
