| Author |
Message
|
| MB Developer |
 Posted: Tue Mar 24, 2015 3:42 am Post subject: DatabaseInput Node sample |
 |
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
Hi All,
Greetings...
I am working on DatabaseInput Node sample from infocenter.
In my PC I install Oracle XE.
After installed for Set up the database tables in oracle I will copy below code
| Code: |
DROP TABLE "DBINPUT_CUSTOMER";
CREATE TABLE "DBINPUT_CUSTOMER" (
"PKEY" VARCHAR(10) NOT NULL PRIMARY KEY,
"FIRSTNAME" VARCHAR(20),
"LASTNAME" VARCHAR(20),
"CCODE" VARCHAR(10)
);
DROP TABLE "DBINPUT_EVENTS";
CREATE TABLE "DBINPUT_EVENTS" (
"EVENT_ID" INTEGER PRIMARY KEY
"OBJECT_KEY" VARCHAR(80) NOT NULL,
"OBJECT_VERB" VARCHAR(40) NOT NULL
);
CREATE SEQUENCE "DBINPUT_SEQUENCE" (start with 1 increment by 1 nomaxvalue);
CREATE TRIGGER "DBIN_SEQ_TRIG"
BEFORE INSERT ON "DBINPUT_EVENTS"
FOR EACH ROW
BEGIN
SELECT DBINPUT_SEQUENCE.nextval INTO :NEW.EVENT_ID FROM dual;
END;
CREATE TRIGGER "DBIN_CUST_EVENT"
AFTER INSERT OR DELETE OR UPDATE ON "DBINPUT_CUSTOMER"
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF inserting THEN
INSERT INTO DBINPUT_EVENTS(OBJECT_KEY, OBJECT_VERB)
VALUES(:N.PKEY,'Create');
END IF;
IF updating THEN
INSERT INTO DBINPUT_EVENTS(OBJECT_KEY, OBJECT_VERB)
VALUES(:N.PKEY,'Update');
END IF;
IF deleting THEN
INSERT INTO DBINPUT_EVENTS(OBJECT_KEY, OBJECT_VERB)
VALUES(:O.PKEY,'Delete');
END IF;
END;
|
--> Click on Run
It will ask values for three fields
NEW
:N
:O
I will give NEW,NEW,OLD and click on Submit then it will give error like " ORA-00911: invalid character " .
Please let me know the solution for this problem...
Thanks in advance.
_________________
Thanks.... |
|
| Back to top |
|
 |
| MB Developer |
| Posted: Tue Mar 24, 2015 4:16 am Post subject: |
|
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
Hi All,
I create tables DBINPUT_CUSTOMER and DBINPUT_EVENTS.
Run the triggers DBIN_SEQ_TRIG and DBIN_CUST_EVENT successfully.
but when i insert data into table then below error will came
Script to insert a row in the database
INSERT INTO DBINPUT_CUSTOMER
VALUES ('cust1', 'Fred', 'Flintstone', 'Dev');
error ORA-04098: trigger 'FIRST.DBIN_SEQ_TRIG' is invalid and failed re-validation ORA-06512: at "FIRST.DBIN_CUST_EVENT", line 3 ORA-04088: error during execution of trigger 'FIRST.DBIN_CUST_EVENT'
I know this problem is related to DB but It is a DatabaseInput sample from MB infocenter if anybody done this please give the solution ....
_________________
Thanks.... |
|
| Back to top |
|
|
| MB Developer |
| Posted: Tue Mar 24, 2015 6:34 am Post subject: |
|
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
|
| Back to top |
|
|
| nelson |
| Posted: Tue Mar 24, 2015 8:21 am Post subject: |
|
|
Partisan
Joined: 02 Oct 2012
Posts: 313
|
I'd check authorizations...
Somewhere I read this:
| Quote: |
ORA-04098: trigger "string.string" is invalid and failed re-validation
Cause: A trigger was attempted to be retrieved for execution and was found to be invalid. This also means that compilation/authorization failed for the trigger.
Action: Options are to resolve the compilation/authorization errors, disable the trigger, or drop the trigger. |
|
|
| Back to top |
|
|
| MB Developer |
| Posted: Tue Mar 24, 2015 11:17 am Post subject: |
|
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
Thanks nelson,
It's working.... 
_________________
Thanks.... |
|
| Back to top |
|
|
| nelson |
| Posted: Tue Mar 24, 2015 11:21 am Post subject: |
|
|
Partisan
Joined: 02 Oct 2012
Posts: 313
|
| MB Developer wrote: |
Thanks nelson,
It's working.... |
Can you please share the solution. |
|
| Back to top |
|
|
| MB Developer |
| Posted: Tue Mar 24, 2015 11:29 am Post subject: |
|
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
Hi nelson,
| Quote: |
DROP TABLE "DBINPUT_CUSTOMER";
CREATE TABLE "DBINPUT_CUSTOMER" (
"PKEY" VARCHAR(10) NOT NULL PRIMARY KEY,
"FIRSTNAME" VARCHAR(20),
"LASTNAME" VARCHAR(20),
"CCODE" VARCHAR(10)
);
DROP TABLE "DBINPUT_EVENTS";
CREATE TABLE "DBINPUT_EVENTS" (
"EVENT_ID" INTEGER PRIMARY KEY
"OBJECT_KEY" VARCHAR(80) NOT NULL,
"OBJECT_VERB" VARCHAR(40) NOT NULL
);
CREATE SEQUENCE DBINPUT_SEQUENCE
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
CREATE TRIGGER "DBIN_SEQ_TRIG"
BEFORE INSERT ON "DBINPUT_EVENTS"
FOR EACH ROW
BEGIN
SELECT DBINPUT_SEQUENCE.nextval INTO :NEW.EVENT_ID FROM dual;
END;
CREATE TRIGGER "DBIN_CUST_EVENT"
AFTER INSERT OR DELETE OR UPDATE ON "DBINPUT_CUSTOMER"
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF inserting THEN
INSERT INTO DBINPUT_EVENTS(OBJECT_KEY, OBJECT_VERB)
VALUES(:N.PKEY,'Create');
END IF;
IF updating THEN
INSERT INTO DBINPUT_EVENTS(OBJECT_KEY, OBJECT_VERB)
VALUES(:N.PKEY,'Update');
END IF;
IF deleting THEN
INSERT INTO DBINPUT_EVENTS(OBJECT_KEY, OBJECT_VERB)
VALUES(:O.PKEY,'Delete');
END IF;
END;
|
After running all the above statements I will insert values into DBINPUT_EVENTS table values are 1,1,1.
and then insert values into DBINPUT_CUSTOMER table then its working.
INSERT INTO DBINPUT_CUSTOMER
VALUES ('cust1', 'Fred', 'Flintstone', 'Dev');
UPDATE DBINPUT_CUSTOMER SET FIRSTNAME = 'Barney', LASTNAME = 'Rubble' WHERE PKEY=1;
DELETE FROM DBINPUT_CUSTOMER WHERE PKEY='cust1';
_________________
Thanks.... |
|
| Back to top |
|
|
| MB Developer |
| Posted: Wed Mar 25, 2015 2:34 am Post subject: |
|
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
Hi Experts,
when i do any CURD operations in DB the below message will come in
this DBINPUTSAMPLE.FAILURE queue.
| Quote: |
<Error>
<InputMessage/>
<ExceptionList>
<RecoverableException>
<File>F:\build\slot1\S800_P\src\DataFlowEngine\ImbDataFlowNode.cpp</File>
<Line>1154</Line>
<Function>ImbDataFlowNode::createExceptionList</Function>
<Type>ComIbmDatabaseInputNode</Type>
<Name>DatabaseInput#FCMComposite_1_1</Name>
<Label>DatabaseInput.Retrieve updates from database</Label>
<Catalog>BIPmsgs</Catalog>
<Severity>3</Severity>
<Number>2230</Number>
<Text>Node throwing exception</Text>
<Insert>
<Type>14</Type>
<Text>DatabaseInput.Retrieve updates from database</Text>
</Insert>
<RecoverableException>
<File>F:\build\slot1\S800_P\src\DataFlowEngine\TemplateNodes\ImbCallableTemplateNode.cpp</File>
<Line>1434</Line>
<Function>ImbCallableTemplateNode::processMessage</Function>
<Type></Type>
<Name></Name>
<Label></Label>
<Catalog>BIPmsgs</Catalog>
<Severity>1</Severity>
<Number>3910</Number>
<Text>Retry processing failing message</Text>
<Insert>
<Type>5</Type>
<Text>Retrieve updates from database</Text>
</Insert>
</RecoverableException>
<RecoverableException>
<File>F:\build\slot1\S800_P\src\DataFlowEngine\ImbRdl\ImbRdlInputModule.cpp</File>
<Line>260</Line>
<Function>SqlInputModule::endEvent</Function>
<Type>ComIbmDatabaseInputNode</Type>
<Name>DatabaseInput#FCMComposite_1_1</Name>
<Label>DatabaseInput.Retrieve updates from database</Label>
<Catalog>BIPmsgs</Catalog>
<Severity>3</Severity>
<Number>3243</Number>
<Text>Exception thrown from user ESQL procedure EndEvent</Text>
<Insert>
<Type>5</Type>
<Text>EndEvent</Text>
</Insert>
<Insert>
<Type>5</Type>
<Text>DatabaseInput_Retrieve_updates_from_database</Text>
</Insert>
<RecoverableException>
<File>F:\build\slot1\S800_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp</File>
<Line>650</Line>
<Function>SqlStatementGroup::execute</Function>
<Type>ComIbmDatabaseInputNode</Type>
<Name>DatabaseInput#FCMComposite_1_1</Name>
<Label>DatabaseInput.Retrieve updates from database</Label>
<Catalog>BIPmsgs</Catalog>
<Severity>3</Severity>
<Number>2488</Number>
<Text>Error detected, rethrowing</Text>
<Insert>
<Type>5</Type>
<Text>.DatabaseInput_Retrieve_updates_from_database.EndEvent</Text>
</Insert>
<Insert>
<Type>5</Type>
<Text>23.3</Text>
</Insert>
<Insert>
<Type>5</Type>
<Text>DELETE FROM Database.DBINPUT_EVENTS WHERE ...</Text>
</Insert>
<DatabaseException>
<File>F:\build\slot1\S800_P\src\DataFlowEngine\ImbOdbc.cpp</File>
<Line>3259</Line>
<Function>ImbOdbcStatement::checkRcInner</Function>
<Type></Type>
<Name></Name>
<Label></Label>
<Catalog>BIPmsgs</Catalog>
<Severity>3</Severity>
<Number>2321</Number>
<Text>Root SQL exception</Text>
<Insert>
<Type>2</Type>
<Text>100</Text>
</Insert>
<Insert>
<Type>14</Type>
<Text>odbc32.dll</Text>
</Insert>
</DatabaseException>
</RecoverableException>
</RecoverableException>
</RecoverableException>
</ExceptionList>
<LocalEnvironment>
<Database>
<Input>
<Event>
<FailureCount>1</FailureCount>
<Key>11</Key>
<Usr>
<EVENT_ID>11</EVENT_ID>
<OBJECT_VERB>Delete</OBJECT_VERB>
<OBJECT_KEY>cust2</OBJECT_KEY>
</Usr>
</Event>
</Input>
</Database>
</LocalEnvironment>
</Error> |
_________________
Thanks.... |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Mar 25, 2015 3:06 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
This
| Code: |
<Text>Exception thrown from user ESQL procedure EndEvent</Text>
|
and this
| Code: |
<Text>DELETE FROM Database.DBINPUT_EVENTS WHERE ...</Text>
|
May help you solve the problem
_________________
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: Wed Mar 25, 2015 3:13 am Post subject: |
|
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
Hi smdavies99,
Thanks for giving reply ...
I modify the ESQL code then it's working but if I do any CURD operation then in out put queue FirstName and LastName values are getting.If it is insert or delete or update whatever it is below message come ....
<Customer>
<FirstName>****</FirstName>
<LastName>****</LastName>
</Customer>
_________________
Thanks.... |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Mar 25, 2015 6:00 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| you should look up the bip code, and then look at the system log to see what additional messages are being produced there - this should include the actual database error code. |
|
| Back to top |
|
|
| nelson |
| Posted: Wed Mar 25, 2015 6:15 am Post subject: |
|
|
Partisan
Joined: 02 Oct 2012
Posts: 313
|
| I recommend you to first do all your stuff in the database and make it work, then go to IIB/WMB. |
|
| Back to top |
|
|
| MB Developer |
| Posted: Thu Mar 26, 2015 5:32 am Post subject: |
|
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
Hi nelson,
I already completed work at data base and it is working good.i.e I create application table and event table write code for triggering and when I perform any action on application table insert,update or delete then in event table one row will be added like ( INSERT INTO DBINPUT_CUSTOMER
VALUES ('cust1', 'Fred', 'Flintstone', 'Dev') .
EVENT_ID OBJECT_KEY OBJECT_VERB
61 cust1 Create
---> In DB side there is no issues right.
DatabaseInput Node sample form toolkit :
1. Set up the database
2. Import and partially deploy the sample
3. Run the sample ( insert/update/delete one row in application table.)
Then in the DBINPUTSAMPLE.OUTPUT the output come from application table like below.
<Customer>
<FirstName>****</FirstName>
<LastName>****</LastName>
</Customer>
This is what I have done for DatabaseInput Node sample form toolkit.
---> I have a one question how to get the data form Event table when insert/update/delete one row in application table.
INTO DBINPUT_CUSTOMER
VALUES ('cust1', 'Fred', 'Flintstone', 'Dev')
<Event>
<EVENT_ID>****</EVENT_ID>
<OBJECT_KEY>****</OBJECT_KEY>
<OBJECT_VERB>Create</OBJECT_VERB>
</Event>
Let me know the solution ....
_________________
Thanks.... |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Mar 26, 2015 5:40 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20698
Location: LI,NY
|
So what are the real values for event id and object ID?
You should see those in the event table. You will then need to use the objectID to get back to the source table and see what the values are...
You may well need to add some key values to the event table because if object id 530809 got deleted, where will you get the value from, to know it was Jane Doe or Fred Flintstone?
Did you set up the EventId range in Dual?
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| MB Developer |
| Posted: Thu Mar 26, 2015 6:08 am Post subject: |
|
|
Disciple
Joined: 03 Feb 2014
Posts: 179
|
Hi fjb_saper,
Thanks for response..
INSERT INTO DBINPUT_CUSTOMER
VALUES ('cust1', 'Fred', 'Flintstone', 'Dev');
SELECT * FROM DBINPUT_CUSTOMER
PKEY FIRSTNAME LASTNAME CCODE
cust1 Fred Flintstone Dev
SELECT * FROM DBINPUT_EVENTS
EVENT_ID OBJECT_KEY OBJECT_VERB
61 cust1 Create
Then in the DBINPUTSAMPLE.OUTPUT the output come from application table like below.
<Customer>
<FirstName>Fred</FirstName>
<LastName>Flintstone</LastName>
</Customer>
If I update then
UPDATE DBINPUT_CUSTOMER SET FIRSTNAME = 'Barney', LASTNAME = 'Rubble' WHERE PKEY='cust1';
SELECT * FROM DBINPUT_EVENTS
EVENT_ID OBJECT_KEY OBJECT_VERB
61 cust1 Create
62 cust1 Update
Then in the DBINPUTSAMPLE.OUTPUT the output come from application table like below.
<Customer>
<FirstName>Barney</FirstName>
<LastName>Rubble</LastName>
</Customer>
_________________
Thanks.... |
|
| Back to top |
|
|
|
|
