Small SQL Tip

Search a string in a table column which is an XML…..

Oh man! I have to search for a particular string inside an XML column of a table.

Found a reference in a site. Modified a little bit. If you find it easy use it, wait lets make this some bit complex — the string should have special character ‘ inside it.

Table Name: TABLE

Column Name : TABLE_XML

XML Structure:

TABLEDATA/FirstName

TABLEDATA/LastName

TABLEDATA/TABLE_ID

  1. I want to Query all the first Name and last name details, who has ‘ in their name. Eg: Jay O’Connell etc…

select def.* from TABLE,

XMLTABLE(‘$D/TABLEDATA‘ PASSING TABLE_XML as “D”

columns

firstname  varchar(200)  path  ‘FirstName‘,

lastname  varchar(200)  path  ‘LastName‘,

….) AS def where (def.firstname like ‘%”% or def.lastname  like ‘%”%) with ur;

 

Result:

FirstName            LastName

Jay                          O’Connell

R’Dia                      Lortez

 

 

That’s it…Any queries …posttttttttt please

Advertisements

Interaction styles in BPM

When using an MQ/MQ JMS Adapter interaction styles to BPEL Processes, it’s quite good to use synchronous binding for the interface if the interaction is not request and response. Because if a message passes to BPEL flow and fails at any point in BPEL process which is not handled properly then the thread does not get released and might end up in dead lock situation which consumes all the instances defined at Adapter end. This slows down the process and SIB gets filled up. One more thing is synchronous style acts as asynchronous if there is response coming back and also retry count which is automatically set to ‘4’ can be changed to ‘0’ manually which reduces the number of retries if the process get failed at any point.

Note: Based on my knowledge, Can discuss more on the topic if needed.

Failed Event Manager in WPS

You develop a module and run it under Web Sphere Process Server, it fails throwing a ‘404 Page not Found’ exception. This might be for two reasons : one due to unavailability of an application that is deployed in the server or may be the remote application that you are trying to access in not available. So , this leads to your module failure. Now coming to point how does  Failed Event Manager helps in this scenario. Lets see.

When ever any event(its nothing but an instance of the test)  is triggered a default event is being created inside the server.For example like _PI:123453.666654.4455 . This event id is helpful to identify the test case that we ran and also to debug or trace a particular failure. So when an event fails this event id makes an entry inside the Failed Event Manager. This you can see in the Admin Console view of a Web Sphere Process Server under Integration Applications. Under this category you can see Failed Event manager . Now go inside and explore the tab ‘Get All Failed events’. you can see an entry of recently failed event’s Id inside the list. So here you can check the event id and go for some of the prospects of the event and you can explore the options present there. So, once the remote service or application is Up and Running you can directly resubmit the event id and see the status as success-ful or failed in submission of the events and can identify your self about the services.

So, Finally What I  say is that Failed Event Manager helps out in identifying the  status of running applications and also for performing several operations on the events like re-submit, delete  etc.

Post me on this article if any doubts.