Bootstrap your JDBC Data Source XML

Design on the WLS JDBC Data Source XML files.


 


The Story 

Have you ever wondered  how is to see all the JDBC info in 1  table only  that provides a "360°" overview around all the data sources your WLS-FMW domain has ?

Of course you can use the WLS Admin Console to check for such details  but  the details are spread on more tabs and per each data source.

Hello the  jdbc xml files. All the files are in the same 1 single folder: DOMAIN_HOME/config/jdbc.

For this script we will use the same  pattern as we did on the previous script for the config.xml:  Bootstrap your config.xml.

Note: Since we covered all the details in the previous post there is no need to add them here again. The same tools, resources and links were used  for this scrips also.

The first steps

Before  going to run the script we will need to do some extra steps since we now have more xml files  and all of them have some  common details that will raise some issues.

Note: Copy-paste all the DOMAIN_HOME/config/jdbc xml files into a new location.

1. First we need to merge all the jdbc xml files into 1 single big file:

e.g. $ cat *.xml > all_first_jdbc.xml

2. Then we need to remove all the xml header rows (<?xml version='1.0' encoding='UTF-8'?>) from the entire file - no worries we will add it again but this time it will be 1 single row per the entire file:

e.g. $ sed "/<?xml version='1.0' encoding='UTF-8'?>/d" all_first_jdbc.xml > all_jdbc.xml

3. The last step is now to add the xml header row as 1 row only and to define the new root element (jdbc_list) for this file:

<?xml version='1.0' encoding='UTF-8'?>

<jdbc_list>
... [here are all the <jdbc-data-source> elements]
</jdbc_list>

That is all.

Note: Implement the above steps based on your tools and your specific OS.

Since we don't want to end up with a very long table and scroll (even) to much to the right side, the  output file follows the same structure as the JDBC xml file and defines the 5 main sections:

• jdbc-driver-params
• jdbc-connection-pool-params
• jdbc-data-source-params
• jdbc-xa-params
• jdbc-oracle-params

Note: If you check the MBeans you will notice the same grouping pattern is used.


The script 

You will find the script as xsl here, on GitHub: wls-jdbc-xml-xsl-Bootstrap.


How to use it

Even if the "import" option could be used - as the previous option #1- we will use only the Saxon tool to generate the HTML. So, yes, we will focus only on the previous option #2.

Now, just run the below: 

java -jar saxon9he.jar -s:all_jdbc.xml -xsl:jdbc_list.xsl -o:all_jdbc.html

 

• all_jdbc.xml is the source (-s) xml file that covers all the data sources.
• jdbc_list.xsl is the XSLT template used (-xsl).
• all_jdbc.html  is the HTML output  (-o).
• Of course, you can rename your files; but use the same pattern. 

Other details 


First of all the rule is: if a parameter is not in the xml file then it has the default value.  If you want to know the value then you can check the WLS Admin Console for  it or -even better- you can check the specific MBean.

Since the idea was now to highlight  that a default value is in use,  you will see that a pattern like "default parameter_name" is found in the table. This is also to make you aware you need to check for the default value but, more important, is to keep track on the specific parameter and to link the table header with the details found. This is more to confirm that whatever the scripts prints in some cells it is relevant based on the table's header.

Note: The only exception is with the "properties"; here the details are tricky and such no "default parameter_name" message was printed. Still, there is a default value and you should check for it.


The next main idea is that for some of the groups there are no details in some of the data sources xml files.  The main such  groups are the  jdbc-xa-params  and the jdbc-oracle-params. Here we first need to make sure that such details are only covered per the relevant data sources; this is why you will see we first count how many such relevant data sources are found  and only for those  we provide details and  also we add that a "default value" is in use.  For some other data sources such details are not relevant and it could be wrong to say that a default value is in use just because the parameter is not part of the data source xml file.

The script was tested mostly on a 12c WLS domain.




Possible Issues 


Please note that some JDBC files include some passwords with the bellow pattern:

<password-encrypted><password></password-encrypted>

Please change  the "<password>"  value to "****" or other basic string and the script will work.

Bootstrap your config.xml

Design on the config.xml






I will start first with the resources I used to create this post:

• Saxonica - Saxon processor for XSLT, XQuery, and XML
• Saxon-HE: the open source Saxon XSLT and XQuery processor, developed by Saxonica

• Bootstrap
• Bootstrap Studio
• W3Schools
• W3.JS JavaScript library 
• Export HTML Table Data to Excel using JavaScript
• Unicode - Arrows
• Oracle Brand Color 
  

All of the above products and links are equally important and were used to create the current script.


The story 

The config.xml  is the "kernel" of all the Oracle FMW 11g /12c domains.

This is the  "big-picture" of the Oracle WebLogic Server [WLS] domain and the WLS is the "kernel"  for all the Oracle FMW 11g or 12c products.

Make sure you never play with this file without knowing what you add or remove from it and always do a backup before whatever the future updates.

Config.xml is a an xml  file and it provides a one stop only to understand your   domain-environment.

If your domain is a small one then just by scrolling up and down and "Crtl+F" should be enough to  have all the info you need.

If the parameters have the default values then sometimes you will not find them in the config.xml. Once you update them from the Admin Console  the parameters will also be visible in the config.xml no matter the value you define - even if you later switch back to the default value.

Note: I will not go into the xml vs json story; for now the main file is an xml one so end of story.


The config.xml can be  a very long  file. Especially if you have not only a basic WLS domain but a FMW one.

So let's say you just installed a SOA Suite/BPM Suite/OSB  and  voilà just a number of default ~ 90  apps  and ~ 120 libraries; by default you have not only 90+120 xml elements but (90+120) * 7 different xml tags and all are only about the apps and libraries you have.

You will also end up  with  like ~ 25 xml rows to describe the data sources;  so  add here 25 * 3 more rows - is  multiplied by 3  since there are 3 xml tags used to describe each of the data source.

And, of course, you need to  count the many  xml tags  about the servers from the  domain and... boy it  gets big !  Good luck to   parse this xml  file using only your eyes.

Of course if you have access  to the Admin Console then maybe you will not need to open this config.xml file. Maybe.

But how about a scenario when you end up with only this config.xml file and nothing more ? 


Of course you can use the "Ctrl+F" or even some nice grep or other Notepad++ plugins to help you.  Or you can stop for 1 sec, understand that you have an xml file at the end  and [once you did a backup of this file]  handle it as any xml files.

Hello XSLT ! Yes  you can now  transform this xml file into a nice HTML one.

And since is HTML file why not add some nice design features ?

Hello Bootstrap !

For now I'm very much into the Bootstrap framework:  is easy to add the links from the remote repository [so you don't have to install it on your machine and this will make sense later] , the documentation is full of details so is easy to  find and add what you need and it looks  very nice. 

On top of this, you can also use a tool like Bootstrap Studio to create a pattern of your future files.


In addition to all of this you can add some custom CSS here and there and even the nice and light JS library from the w3school.  Like for example to add a search option.

You can also find a nice JS script that will export all the data to excel... since no matter how much we like to think about the new fancy buzz words we will always end up using the 3 main Office tools.




The XSL script 

You will find the script as xsl here: GitHub: config-xml-xslt.

There is no need to describe it into full details. Is a combination of basic xslt-xpath per each main config.xml components + the Bootstrap design + some extra CSS details + some JS script+ basic HTML.

If you are not into the Oracle WLS-FMW products then this  script will make no sense and will be for no help. If you are into such Oracle WLS-FMW products  then just download the script and use it and there is no need to add more; once you see the output you will understand it.



How to use it


Here you have 2 main options:

option #1

The option #1  is the fastest one and you just need  to open your config.xml file in a Notepad-like tool and to add the next line below the  xml first row. Is like an import pattern.

<?xml-stylesheet  type= "text/xsl" href = "config.xsl"?>

So at the  end your config.xml  file will start with:

<?xml version='1.0' encoding='UTF-8'?>
<?xml-stylesheet  type= "text/xsl" href = "config.xsl"?>


You can then open the config.xml file using the Firefox browser and voilà the new approach: you now have a nice "web-site".

You can also use some other  Chrome-based browsers but for this you need to  add the option "--allow-file-access-from-files".
Check  Google for "How can I make XSLT work in chrome?". 

However I think we should always have al least 2 main browsers: Firefox and a Chrome-based one.


Right-click and check the "View page source" now; yes:  it is still an xml file.

Note: The recommended pattern is to use the below option #2.



option #2

The option #2 is to use the Saxon tool to generate the HTML file using the config.xml  and the config.xsl files. Now, you will end up with an HTML file and  you can use whatever the browsers you want.

Actually, if you want to use the XSLT 2  then you will need  the Saxon tool since browsers now have no support  for  version 2; it only covers the version 1... I'm somehow disappointed by this but whatever.

Make sure you "Download Latest Version" of the Saxon XSLT and XQuery Processor -  The Saxon XSLT and XQuery processor, developed by Saxonica:

 

Place inside a folder that has the config.xml & config.xsl files the 3 new files:

• saxon9he.jar
• saxon9-test.jar
• saxon9-xqj.jar

Note: Never use the DOMAIN_HOME/config location; always copy the config.xml file into a different location and use the Saxon tools.

Now, just run the below:

java -jar saxon9he.jar -s:config.xml -xsl:config.xsl -o:config.html

• config.xml is the source (-s) config.xml file.
• config.xsl is the XSLT template used (-xsl).
• config.html  is the HTML output  (-o).
• Of course, you can rename your files; but use the same pattern.

>pwd
/home/leo/XSLT_CONFIG-XML

>ls
config.xml  config.xsl  saxon9he.jar  saxon9-test.jar  saxon9-xqj.jar

>java -jar saxon9he.jar -s:config.xml -xsl:config.xsl -o:config.html


Now we can now see the new HTML file:

 




Other details

Do keep in mind that this script  covers the main domain settings- xml elements.  That is if you want more of such elements to be included you will need to manually add them in the script.


In addition, there is also the chance to see many  empty columns-rows  and this is because for those xml elements  there is currently no value found in your  config.xml file. The scripts  only reflects  what the config.xml file has and nothing more.

Is this a problem ? No, because this script has the purpose only to help you with some design and a more friendly look-and feel of the config.xml and that is all. In addition, the focus was to add the main details that are needed for an admin and not the entire config.xml details; for some in deep details you will still need to manually check the config.xml.

If you want to create  a script  that will be more complex and where only the elements having some value should be  printed and whatever more you think you need I'm sorry  but it gets already more complicated and parsing a config.xml file  is not the best idea to start with. If you need something more complex then use the time to create a script that connects to the MBeans and select your data from the MBeans directly.

April 2020 Update: for a more complex alternative check the Well Known Attributes WLST.pλ.

The idea around this demo script was to have something very fast and that looks fancy enough and that could be even updated in the future but again all this to be done -maybe not always elegant- simple and fast.




Note: For the  design I used the Oracle Brand Color -since is about an Oracle product (Oracle WLS- FMW) and I also like the Oracle's color palette- but you can customize it and use your own color palette.


In the near future a new script  that covers the JDBC details [based on all the data sources related xml files] will be shared.


Note: The script was tested mostly on a 12c WLS domain.

BPEL: Credit Card Request demo

BPEL: Credit Card Request demo & For Each





This tutorial is an appendix of the previous "BPMN: Credit Card Request demo".

Now that you exposed a BPM project as a web service you can consume it...and generate 10, 100, 1000 or even more credit card requests just like that. Of course, with dummy data but still, the number could be big!

NOTE: Even if "sky is the limit" please note you will end up  sending an email notification using your own email account. If the number of emails  sent in the same time is too big then the email account provider might see this as a DDoS attack or at least  will think there is something odd. So make sure you don't go with too many  requests that  will be rejected from the start based on the below rule:

(monthly expenses div (estimated income div  12) * 100 > 70%  AND  "Unemployed" 

If you use the below web site you can even "bake" a big CSV file as the input:

• DATABAKE to Generate realistic test data 

Note: There is also the possibility to place this future pattern inside of the previous composite; however, even the "The NeverEnding Story" has its  own "The end".

In addition to the above "bake" tool there is also the below Oracle Live SQL option:

• Live SQL - Learn and share SQL

The Oracle Live SQL is full of features ... but you will have to find them.

Back to the SOA story since now is no more about BPM but about SOA...because we don't need the BPMN Component.  

This tutorial goes around 3 main ideas: 

• how to generate an XML Schema (XSD file) based on an Excel-CSV file. 
• the File Adapter technology. 
• BPEL component with the "ForEach" activity. 

Below you can find  attached  the demo CSV file. Since the file itself was really big I removed most of the rows; you can add more rows with your own values. Download the CSV file since you will soon use it:

• CUSTOMERLIST.csv

And now the pictures....

A picture is worth a thousand words:












Always start "Empty":





Not much to see for now since... is "Empty":





The File Adapter + CSV to XML Schema 

Since we will read this file.... we will place the File Adapter in the Exposed Services; this is how we will trigger this composite.





































And now we need to end up with the XML Schema (XSD) for the future messages.... based on the CSV:











Always check the columns and make sure it  reflects what you want. Worst case play with the settings so at the end you have the data  you need:





In my case all data is about "String" and in case some values should be reflected as numbers I will handle this in the BPEL or the BPMN component; at the end its up to  you how you want to implement all this:













Save the XSD file  so in the future you can reuse it without going with all the previous steps:







Now back to the File adapter:













All done here.



The Credit Card Request BPM web service  

The previous BPM credit card request process is like any other web service...SOAP web service that is. So we need the WSDL from the EM Console

• localhost:7011/em

Copy the entire link of this WSDL and use it inside a SOAP External Reference:













All done.

It looks so so complicated.... 😋



The BPEL   

We will use now the BPEL Component  that will read the file and For Each row will invoke the BPM external web service... One by one each row from the CSV will be used as a message for a new credit card request.





Do keep in mind we just created a File Adapter Exposed Service so we will use it as a WSDL since even this File Adapter acts as a web service and has a WSDL.

Such WSDL files are  to be found in the "WSDL" folder:




Make sure you don't select  the checkbox to expose this BPEL as a web service- no need for this:




Now "wire" those 3 elements:






Open the BPEL component:





We will use the For Each activity:






....for each row inside the CSV file.... do something.






For example, for each row inside the CSV file...invoke the external Reference:





Create a variable to keep the message/data that will be sent to the external web service:




Since the external BPM service  returns nothing, no need for an Output.









Now, for each row inside the CSV file... assign the (input) message  to the message (format) requested by the external service. You will actually map the input from CSV to the variable used to invoke this  external service:






However, before we implement the mapping.... we  still need to go back to the For Each itself  since we need some more settings.






This For Each is like any "for-loop" statement so we will need  an extra parameter: a  counter - to know when we need to finish-stop whatever we need to do.

Note: Keep in mind this "counter" name since we will use it again in some min.

How about the idea:  do whatever you want ... till you have no more time or till you have no more objects to play with etc .

For this story the idea is:  start with the first (1) row and read all of them  one by one till  the end of all the rows; till the end of ...how many rows we have. We can count the rows to  know how many we have and when we will stop:






Is about the "CardRequest" element since this one has all the  fields we need like SSN, name, etc:




Back to the Assign-Invoke activities that will be performed for all rows from the CSV file:



Open the Assign activity:




Since the input from the CSV and the BPM external service have the same schema...we can just map 1-1 element having the same name:





And this should be all that we need.....  but there are "n" rows  inside the CSV and we need to add this info on the mapping - assign  diagram.

The "n" in this case is the "counter" we defined on the For Each activity itself (starting from 1 till the "count of all the elements" number).

The rule is simple:  we use the counter variable as an element for the root of the message; or the parent of the message.... the kids are the individual elements-fields like SSN, name etc:









And we go with this pattern for all the kids- elements:





Note: In case you ask if there is an alternative to  all the above steps;  yes there is....But this will be covered in a different tutorial.

For now we used the For Each activity inside the BPEL Component and the above steps are the rule.



All done.



The Deployment   

We  can now deploy this composite and test it by placing a CUSTOMERLIST.csv file inside the  specific INPUT folder.  After some time (aka the amount of time you specified) the magic will happen.





Check the  BPM Workist Console for the new tasks generated or even the Database table if  some credit card requests were rejected by default. And also check the emails.

The BPM Woklist Console:

• http://localhost:7011/bpm/workspace/