ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Error inserting into Oracle database using PASSTHRU

Post new topic  Reply to topic
 Error inserting into Oracle database using PASSTHRU « View previous topic :: View next topic » 
Author Message
Ash
PostPosted: Fri Jan 11, 2002 9:50 am    Post subject: Reply with quote

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
View user's profile Send private message
ravi
PostPosted: Fri Jan 11, 2002 3:46 pm    Post subject: Reply with quote

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
View user's profile Send private message
Ash
PostPosted: Mon Jan 14, 2002 7:46 am    Post subject: Reply with quote

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
View user's profile Send private message
mpuetz
PostPosted: Tue Jan 15, 2002 4:22 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Error inserting into Oracle database using PASSTHRU
Jump to:  



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
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.