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 » Oracle Compatiable with broker

Post new topic  Reply to topic Goto page 1, 2  Next
 Oracle Compatiable with broker « View previous topic :: View next topic » 
Author Message
rajmq
PostPosted: Wed Jun 04, 2008 2:06 am    Post subject: Oracle Compatiable with broker Reply with quote

Partisan

Joined: 29 Sep 2002
Posts: 331
Location: USA
All,

In oracle 10g version there is a advantage to trap the failure data. Basically if the main insert statement is failed due data integrity issue then it can stored in the error table using DML error logging facility.

It can be achieved using and syntax is :
LOG ERRORS [INTO [schema.]Table] [('simple expression')] [REJECT LIMIT integer|UNLIMITED]

But I want to know whether the broker esql can support this new DML syntax’s?


_________________
IBM Certified System Administrator - WebSphere MQ V6.0
IBM Certified System Administrator - WebSphere Business Integration Message Broker V6.0
Back to top
View user's profile Send private message
rajmq
PostPosted: Thu Jun 05, 2008 11:48 pm    Post subject: Reply with quote

Partisan

Joined: 29 Sep 2002
Posts: 331
Location: USA
Any thoughts !!!

TIA
_________________
IBM Certified System Administrator - WebSphere MQ V6.0
IBM Certified System Administrator - WebSphere Business Integration Message Broker V6.0
Back to top
View user's profile Send private message
smdavies99
PostPosted: Fri Jun 06, 2008 12:53 am    Post subject: It depends Reply with quote

Jedi Council

Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
There are two ways this can work.

1) If your SQL is done via a 'PASSTHRU' then the different syntax could well work as (AFAIK) the SQL command is passed without any SQL validation

2) If you wrap your ORacle SQL inside a stored procedure then Broker knows absolutely nothing about the syntax being used inside the Stored Procedure.

For maintainability, I would use option 2) above. Then you can change the SQL inside the stored proc without having to change the broker flow at all.

Stephen D
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995

Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions.
Back to top
View user's profile Send private message
rajmq
PostPosted: Fri Jun 06, 2008 3:36 am    Post subject: Reply with quote

Partisan

Joined: 29 Sep 2002
Posts: 331
Location: USA
Thanks, if i think option 1.

PASSTHRU('INSERT INTO user1.stocktable (stock_id, quantity)
values (?, ?)', InputBody.Transaction.Id,
InputBody.Transaction.Quantity)
LOG ERRORS INTO test.err$_TEST_TRANSACTIONS
REJECT LIMIT UNLIMITED;

Still i am getting syntax error in the eslq editior ? anything wrong in the above.


_________________
IBM Certified System Administrator - WebSphere MQ V6.0
IBM Certified System Administrator - WebSphere Business Integration Message Broker V6.0
Back to top
View user's profile Send private message
jcv
PostPosted: Fri Jun 06, 2008 4:10 am    Post subject: Reply with quote

Chevalier

Joined: 07 May 2007
Posts: 411
Location: Zagreb
Maybe you should write error logging clause as continuation (part) of insert into command, instead of passthru command.
Back to top
View user's profile Send private message Visit poster's website
rajmq
PostPosted: Fri Jun 06, 2008 4:53 am    Post subject: Reply with quote

Partisan

Joined: 29 Sep 2002
Posts: 331
Location: USA
I can write the error logging clause but how do i call the continuation of insert statement ?

TIA


_________________
IBM Certified System Administrator - WebSphere MQ V6.0
IBM Certified System Administrator - WebSphere Business Integration Message Broker V6.0
Back to top
View user's profile Send private message
rajmq
PostPosted: Mon Jun 09, 2008 12:22 am    Post subject: Reply with quote

Partisan

Joined: 29 Sep 2002
Posts: 331
Location: USA
Any help for the above ?



_________________
IBM Certified System Administrator - WebSphere MQ V6.0
IBM Certified System Administrator - WebSphere Business Integration Message Broker V6.0
Back to top
View user's profile Send private message
Gaya3
PostPosted: Mon Jun 09, 2008 1:11 am    Post subject: Reply with quote

Jedi

Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
rajmq wrote:
Thanks, if i think option 1.

PASSTHRU('INSERT INTO user1.stocktable (stock_id, quantity)
values (?, ?)', InputBody.Transaction.Id,
InputBody.Transaction.Quantity)
LOG ERRORS INTO test.err$_TEST_TRANSACTIONS
REJECT LIMIT UNLIMITED;

Still i am getting syntax error in the eslq editior ? anything wrong in the above.


This will throw syntax error as
LOG ERRORS INTO test.err$_TEST_TRANSACTIONS
REJECT LIMIT UNLIMITED;

is not understandable by MB ESQL

Try the below one...

PASSTHRU('INSERT INTO user1.stocktable (stock_id, quantity)
values (?, ?) LOG ERRORS INTO test.err$_TEST_TRANSACTIONS
REJECT LIMIT UNLIMITED',
InputBody.Transaction.Id,
InputBody.Transaction.Quantity)

Regards
Gayathri
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die
Back to top
View user's profile Send private message
rajmq
PostPosted: Mon Jun 09, 2008 5:03 am    Post subject: Reply with quote

Partisan

Joined: 29 Sep 2002
Posts: 331
Location: USA
Thanks Gaya

Now there is no compilation error but while testing this I don't see record in the table.

Also if I see the broker trace, I don't see any status (sql return code) for PASSTHROU execution statement ...



_________________
IBM Certified System Administrator - WebSphere MQ V6.0
IBM Certified System Administrator - WebSphere Business Integration Message Broker V6.0
Back to top
View user's profile Send private message
Gaya3
PostPosted: Mon Jun 09, 2008 5:20 am    Post subject: Reply with quote

Jedi

Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
rajmq wrote:

Now there is no compilation error but while testing this I don't see record in the table.

Also if I see the broker trace, I don't see any status (sql return code) for PASSTHROU execution statement ...


AFIK, PASSTHRU wont give you any SQL Return code.

are you getting the log file created at Database side.

Employ a trace to your flow and find out..are you pasing data in a proper way

Regards
Gayathri
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die
Back to top
View user's profile Send private message
jcv
PostPosted: Mon Jun 09, 2008 5:26 am    Post subject: Reply with quote

Chevalier

Joined: 07 May 2007
Posts: 411
Location: Zagreb
I've found that ODBC may not provide some advanced DBMS-specific features. Does that apply here?
Back to top
View user's profile Send private message Visit poster's website
rajmq
PostPosted: Mon Jun 09, 2008 6:24 am    Post subject: Reply with quote

Partisan

Joined: 29 Sep 2002
Posts: 331
Location: USA
If i use PASSTHORU alone i am able to see the record in the database
without DML logging syntax..

PASSTHRU('INSERT INTO user1.stocktable (stock_id, quantity)
values (?, ?) ',
InputBody.Transaction.Id,
InputBody.Transaction.Quantity)

If i add the DML functions,error record is not inserted in the below error table..
LOG ERRORS INTO test.err$_TEST_TRANSACTIONS
REJECT LIMIT UNLIMITED',



_________________
IBM Certified System Administrator - WebSphere MQ V6.0
IBM Certified System Administrator - WebSphere Business Integration Message Broker V6.0
Back to top
View user's profile Send private message
Gaya3
PostPosted: Mon Jun 09, 2008 6:33 am    Post subject: Reply with quote

Jedi

Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
PASSTHRU wont validate the SQL statement.

it will directly handove the SQL statement to backend (Database) and there it executes.

so the DML structure will be executed as it is.

what you can do here is
Try the SQL command at Oracle side and confirm whether its working or not at first.
then do the modification

Regards
Gayathri
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die
Back to top
View user's profile Send private message
rajmq
PostPosted: Mon Jun 09, 2008 6:49 am    Post subject: Reply with quote

Partisan

Joined: 29 Sep 2002
Posts: 331
Location: USA
Yes Already i have tried that and it is working !!

query like ;

INSERT INTO user1.stocktable (stock_id, quantity)
values (123, 10)'
LOG ERRORS INTO test.err$_TEST_TRANSACTIONS
REJECT LIMIT UNLIMITED;

but ESQL, i am using DML function after Value clause not in the end of syntax !!

PASSTHRU('INSERT INTO user1.stocktable (stock_id, quantity)
values (?, ?) LOG ERRORS INTO test.err$_TEST_TRANSACTIONS
REJECT LIMIT UNLIMITED',
InputBody.Transaction.Id,
InputBody.Transaction.Quantity)



_________________
IBM Certified System Administrator - WebSphere MQ V6.0
IBM Certified System Administrator - WebSphere Business Integration Message Broker V6.0
Back to top
View user's profile Send private message
jcv
PostPosted: Mon Jun 09, 2008 7:56 am    Post subject: Reply with quote

Chevalier

Joined: 07 May 2007
Posts: 411
Location: Zagreb
Yes, my remark regarding ODBC was totaly misplaced and unneccessary. Is "throw exception on database error" node property relevant here?
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic  Reply to topic Goto page 1, 2  Next Page 1 of 2

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Oracle Compatiable with broker
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.