| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Error when implementing DatabaseInput node |
« View previous topic :: View next topic » |
| Author |
Message
|
| MB Developer |
 Posted: Fri Oct 17, 2014 1:40 am Post subject: Error when implementing DatabaseInput node |
 |
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
Hi Experts,
Greetings,
Msg Flow : DatabaseInput ---> Pass through --> MQ Output
DatabaseInput :- Data Source : DBINPUT
MQ Output :- Queue Name : DBINPUT
Right Click on DatabaseInput --> Open ESQL
CODE :
| Code: |
CREATE DATABASEEVENT MODULE DBInput_Node_MsgFlow_Database_Input
CREATE PROCEDURE ReadEvents( IN NewEvents REFERENCE )
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE LIKE 'D%'
BEGIN
RESIGNAL; /* pass the error back to the node */
/* To choose to handle Database errors yourself, delete the RESIGNAL statement above
* and uncomment the following procedure call */
-- CALL HandleDatabaseError('ReadEvents');
END;
--@!{ ******************** "ReadEvents" autogenerated code (0) ********************
-- If you want the tool to generate code for you, do not delete the comments that denote the start or end
-- of autogenerated code. Any changes that you make between the start and end comments is lost if you
-- generate code again.
/* For example*/
-- SET NewEvents.Event[] = SELECT <MyEventTableName>.* AS Usr,
-- <MyEventTableName>.<MyEventTablePrimaryKey> AS Key
-- FROM Database.<MySchemaName>.<MyEventTableName>;
/*
* Optionally, if your event table has a column to store state (events are not removed from
* the table after processing), use the WHERE clause to filter the events.
*/
-- SET NewEvents.Event[] = SELECT <MyEventTableName>.* AS Usr,
-- <MyEventTableName>.<MyEventTablePrimaryKey> AS Key
-- FROM Database.<MySchemaName>.<MyEventTableName>
-- WHERE <MyEventTableName>.<MyStatusColumnName> = <MyNewEventStatusValue>;
--@!} ******************** "ReadEvents" autogenerated code (0) ********************
[color=red] SET NewEvents.Event[] = SELECT DataSource.EVENTSTORE.* AS Usr,EVENTSTORE.EVENT_ID AS Key FROM DataSource.EVENTSTORE
WHERE EVENTSTORE.EVENT_STATUS = 0;[/color]
END;
CREATE PROCEDURE BuildMessage(IN DispatchedEvent REFERENCE)
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE LIKE 'D%'
BEGIN
RESIGNAL; /* pass the error back to the node */
/* To choose to handle Database errors yourself, delete the RESIGNAL statement above
* and uncomment the following procedure call */
-- CALL HandleDatabaseError('BuildMessage');
END;
/* Here you use the event data in the local environment to retrieve the application data. */
--@!{ ******************** "BuildMessage" autogenerated code (0) ********************
-- If you want the tool to generate code for you, do not delete the comments that denote the start or end
-- of autogenerated code. Any changes that you make between the start and end comments is lost if you
-- generate code again.
/* For example */
-- SET Root.DataObject.<MyApplicationTableName>[] =
-- SELECT <MyApplicationTableName>.*
-- FROM Database.<MySchemaName>.<MyApplicationTableName>
-- WHERE <MyApplicationTableName>.<MyApplicationTablePrimaryKey> = DispatchedEvent.Usr.<MyEventTableForeignKey>;
--@!} ******************** "BuildMessage" autogenerated code (0) ********************
RETURN;
END;
/*
* EndEvent updates the event table to record the event as processed.
* EndEvent is called after the message flow has processed the event.
* EndEvent is called as part of the message flow transaction. This transaction also involves
* BuildMessage. The transaction will be committed when this procedure ends.
*
* Parameters:
* IN DispatchedEvent REFERENCE. A Reference to a ROW containing the event data for the current
* dispatched event. This is a copy of one of the events added to
* NewEvents by ReadEvents procedure.
*/
CREATE PROCEDURE EndEvent(IN DispatchedEvent REFERENCE)
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE LIKE 'D%'
BEGIN
RESIGNAL; /* pass the error back to the node */
/* To choose to handle Database errors yourself, delete the RESIGNAL statement above
* and uncomment the following procedure call */
-- CALL HandleDatabaseError('EndEvent');
END;
/* Here you update the event table to ensure that this event is not processed again. */
--@!{ ******************** "EndEvent" autogenerated code (0) ********************
-- If you want the tool to generate code for you, do not delete the comments that denote the start or end
-- of autogenerated code. Any changes that you make between the start and end comments is lost if you
-- generate code again.
/* For example */
-- DELETE FROM Database.<MySchemaName>.<MyEventTableName>
-- WHERE <MyEventTableName>.<MyEventTablePrimaryKey> = DispatchedEvent.Usr.<MyEventTablePrimaryKey>;
/* Optionally, if your event table has a column to store state, you can update the events here. */
-- UPDATE Database.<MySchemaName>.<MyEventTableName>
-- SET <MyStatusColumnName> = <MyProcessedEventStatusValue>
-- WHERE <MyEventTableName>.<MyEventTablePrimaryKey> = DispatchedEvent.Usr.<MyEventTablePrimaryKey>;
--@!} ******************** "EndEvent" autogenerated code (0) ********************
[b]
[color=red]UPDATE Database.DispatchedEvent.EVENTSTORE
SET EVENT_STATUS = 1
WHERE EVENTSTORE.EVENT_ID = DispatchedEvent.Usr.EVENT_ID;[/color]
RETURN;[/b]
END;
CREATE PROCEDURE HandleDatabaseError( IN FunctionName CHARACTER )
BEGIN
/* Throw a different exception; this could be changed. */
DECLARE message CHARACTER 'Exception occured calling Database Input Node function: ' || FunctionName;
THROW USER EXCEPTION VALUES( SQLCODE, SQLSTATE, SQLNATIVEERROR, SQLERRORTEXT, message );
END;
END MODULE;
|
In that above ESQL I just change only below codes
SET NewEvents.Event[] = SELECT DataSource.EVENTSTORE.* AS Usr,EVENTSTORE.EVENT_ID AS Key FROM DataSource.EVENTSTORE
WHERE EVENTSTORE.EVENT_STATUS = 0;
UPDATE Database.DispatchedEvent.EVENTSTORE
SET EVENT_STATUS = 1
WHERE EVENTSTORE.EVENT_ID = DispatchedEvent.Usr.EVENT_ID;
RETURN;
Data base : ORACLE XE
3 tables are created CUSTOMER,STOKE and EVENTSTORE
and TRIGGER is
CREATE TRIGGER "STOKEEVENT_CREATE2"
BEFORE INSERT ON "STOKE" REFERENCING NEW N
FOR EACH ROW
BEGIN
INSERT INTO EVENTSTORE(OBJECT_KEY,OBJECT_NAME,OBJECT_FUNCTION,EVENT_PRIORITY,EVENT_STATUS)
VALUES (:N.PKEY,'stoke','Create',1,0)
END;
But when Deploy msg flow Error will occurred i.e
| Code: |
Begin running task [Deploying [DBInput_Node_MsgFlow.msgflow] to execution group [DBInput]]
BIP2087E: Broker MB8BROKER was unable to process the internal configuration message.
The entire internal configuration message failed to be processed successfully.
Use the messages following this message to determine the reasons for the failure. If the problem cannot be resolved after reviewing these messages, contact your IBM Support center. Enabling service trace may help determine the cause of the failure.
BIP4041E: Execution group 'DBInput' received an administration request that encountered an exception.
While attempting to process an administration request, an exception was encountered. No updates have been made to the configuration of the execution group.
Review related error messages to determine why the administration request failed.
BIP2432E: (.DBInput_Node_MsgFlow_Database_Input.ReadEvents, 47.34) : The correlation name 'Database.EVENTSTORE.' is not valid. Those in scope are: Environment, LocalEnvironment, Root, Body, Properties, ExceptionList, DestinationList, NewEvents, EVENTSTORE.
The first element of a field reference must be a valid correlation name, from those in scope. This message may sometimes be due to an incorrectly formed or spelled expression which is not intended to be a field reference being parsed as if it were a field reference because the parser does not recognize it.
Correct the syntax of your ESQL expression in node '.DBInput_Node_MsgFlow_Database_Input.ReadEvents', around line and column '47.34', then redeploy the message flow.
The task was unsuccessful: The deployment was unsuccessful. Check error messages above for explanation.
|
Please give me any solution for above problem.
When I insert ant record in STOKE ,Event table want to updated..
Thanks in Advance
_________________
Thanks.... |
|
| Back to top |
|
 |
| MB Developer |
| Posted: Fri Oct 17, 2014 1:58 am Post subject: |
|
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
Hi All,
I changed code in ESQL like below
SET NewEvents.Event[] = PASSTHRU('SELECT Database.EVENTSTORE.* AS Usr,EVENTSTORE.EVENT_ID AS Key FROM Database.EVENTSTORE WHERE EVENTSTORE.EVENT_STATUS = 0');
then It will deployed but when I insert row database error will code :
INSERT INTO STOKE VALUES (101,19,'Flintstone', 10000,10);
ORA-04098: trigger 'DBADMIN.STOKEEVENT_CREATE2' is invalid and failed re-validation.
_________________
Thanks.... |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Oct 17, 2014 5:08 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Looks like a problem with your trigger definition 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| MB Developer |
| Posted: Sun Oct 19, 2014 7:40 pm Post subject: |
|
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
Yes fjb_saper,
But I tried different ways to write trigger but same error will came.
Is there any other way to implement DatabaseInput node ,If Yes then please help me .....
My aim is Just implement DatabaseInput Node and
find uses of DatabaseInput node..and
why we are using DatabaseInput node instead of write DB code in compute node ?
_________________
Thanks.... |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Sun Oct 19, 2014 8:35 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Look at the table definitions, the trigger table definitions and at the way your trigger is written...
The message tells you the insert is good. It's the insert into the trigger table that is the problem...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| vicentius |
| Posted: Sun Oct 19, 2014 11:14 pm Post subject: |
|
|
Apprentice
Joined: 01 Mar 2013
Posts: 28
|
The trigger code is incorrect, hence it fails validation. Correct the trigger and compile it.
Oracle Docs |
|
| Back to top |
|
|
| MB Developer |
| Posted: Fri Oct 24, 2014 1:05 am Post subject: |
|
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
Hi fjb_saper and All,
once check my tables and trigger please find the error ....
| Code: |
CREATE TABLE "CUSTOMER"
( "PKEY" VARCHAR2(10) NOT NULL ENABLE,
"FIRSTNAME" VARCHAR2(20),
"LASTNAME" VARCHAR2(20),
"CCODE" VARCHAR2(10),
PRIMARY KEY ("PKEY") ENABLE
) ;
----------------------------
CREATE TABLE "STOKE"
( "PKEY" VARCHAR2(10) NOT NULL ENABLE,
"CUSTID" VARCHAR2(20),
"COMPANY" VARCHAR2(20),
"STRIKEPRICE" VARCHAR2(10),
"UNITS" VARCHAR2(10),
PRIMARY KEY ("PKEY") ENABLE
) ;
------------------------------------------------------------------------
CREATE TABLE "EVENTSTORE"
( "EVENT_ID" NUMBER(*,0),
"OBJECT_KEY" VARCHAR2(80) NOT NULL ENABLE,
"OBJECT_NAME" VARCHAR2(80) NOT NULL ENABLE,
"OBJECT_FUNCTION" VARCHAR2(80) NOT NULL ENABLE,
"EVENT_PRIORITY" VARCHAR2(80) NOT NULL ENABLE,
"EVENT_TIME" VARCHAR2(80) NOT NULL ENABLE,
"EVENT_STATUS" VARCHAR2(80) NOT NULL ENABLE,
PRIMARY KEY ("EVENT_ID") ENABLE
) ;
-------------------------------
3 tables are created CUSTOMER,STOKE and EVENTSTORE
and TRIGGER is
CREATE TRIGGER "STOKEEVENT_CREATE2"
BEFORE INSERT ON "STOKE" REFERENCING NEW N
FOR EACH ROW
BEGIN
INSERT INTO EVENTSTORE(OBJECT_KEY,OBJECT_NAME,OBJECT_FUNCTION,EVENT_PRIORITY,EVENT_STATUS)
VALUES (:N.PKEY,'stoke','Create',1,0)
END;
|
_________________
Thanks.... |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Oct 24, 2014 1:27 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Well,
How many items are in your insert?
How many column are in the Table Definiiton that can be NULL?
And that's just for starters.
_________________
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 |
|
|
| MB Developer |
| Posted: Fri Oct 24, 2014 2:02 am Post subject: |
|
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
Hi smdavies99,
thanks for response ...
when I insert row in STOKE table then below database error will code :
INSERT INTO STOKE VALUES (101,19,'Flintstone', 10000,10);
ORA-04098: trigger 'DBADMIN.STOKEEVENT_CREATE2' is invalid and failed re-validation.
_________________
Thanks.... |
|
| Back to top |
|
|
| vicentius |
| Posted: Fri Oct 24, 2014 2:13 am Post subject: |
|
|
Apprentice
Joined: 01 Mar 2013
Posts: 28
|
| Because the trigger code is incorrect. |
|
| Back to top |
|
|
| MB Developer |
| Posted: Fri Oct 24, 2014 2:33 am Post subject: |
|
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
Hi vicentius,
When creating trigger it is successfully created.
But anyway please send me the code for 2 tables and trigger otherwise correct above trigger code.
_________________
Thanks.... |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Oct 24, 2014 5:42 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
You have had pointers to the solution to your problem.
Why don't you go and see your DBA's and ask them for help becaise the problem really isn in the Bropker product? They get paid to do this sort of thing for you. We don't.
_________________
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 |
|
|
|
|
|
| |
|
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
|
|
|
|
