Monday, April 13, 2015

Useful DB2 functions




Convert a CLOB (having text data) to String:


  SELECT Col1,CAST(CLOB_Col2 as varchar(2000)), Col3 FROM "Schema"."Table"


Convert a CLOB column (having XML data) to String


  SELECT XMLSERIALIZE(Col1 AS VARCHAR(600)), Col2 FROM "Schema"."Table"
Keep watching for more!

Tuesday, January 7, 2014

Localize names & titles of Liferay pages

Ability to localize the page names is a very useful feature of Lifeary and essential for any I18n/L10n implementation. I will show you how you can do it using Lifeary 6.1.

Below screenshot describes how you can do it:



Manage pages > [select your page] > Name > Other Languages >
  1. Select the language you want to add translation for
  2. Type/paste the translation in the Textbox
  3. Use '+' and '-' buttons to add/remove translations.

Note: From Liferay 6.2 onwards, you can also localize FriendlyURLs of your pages. Learn more about this feature here.

Hope this helps!

Thursday, September 20, 2012

How to get Liferay's 'Custom Fields' in Reports with MySQL Database

Appetizer: [I tend to explain the Use-case of the topic before jumping on to it. You can skip this Appetizer section (as I like to call it) if you are already aware of Liferay's Expando API -or- if you are too hungry to start the main course :-)]


There are number of commonly used attributes provided by Liferay for entities like Users, Blogs, Web Contents, etc. Even then, we come across situations where we need additional attribute(s). Liferay provides a very useful feature called "Custom Fields". You can add additional attributes to these entities using Control Panel > Portal > Custom Fields as shown in the below screenshot:



In most of the projects where Liferay integrates with external systems, we need to use Custom Fields to relate the identity of Users in external systems with the Liferay User. I too have used Custom Fields when integrated Liferay with Plateau, Moodle, and SAP.

Custom Fields are also known as 'Expando' attributes (cool name, huh?). You can create Custom Fields for almost all the entities like User, Page, Organization, Blog, Message Board, etc.

It may interest you to know that Liferay's 'Web Forms' portlet also uses Expando APIs to store definition of Web Forms as well as the form data.

In most of our applications we also need to provide "Reports" [for example: Number of active users, Number of users who logged-in in month x, Number of forum discussions, Users participating in different forums, etc]. You may use Jasper Reports or BIRT or any other reporting engine of your choice for your Reporting requirements.

Reports understand SQL. They talk to the Database via the Reporting engine like Jasper Report, BIRT, etc. So, knowledge of Liferay's Expando tables is essential for writing SQL that involves custom fields.

Liferay stores Custom Fields in below 4 tables known as 'Expando' tables:
  1. ExpandoTable - Definition of the custom fields table
  2. ExpandoRow - Maps the row/record of the Liferay entity - to custom field record
  3. ExpandoColumn - Stores the names of the custom fields
  4. ExpandoValue - Stores actual data of the custom field
I think we have had enough appetizers to start the main course now. :-)

Main Course:
I have added 2 custom fields namely 'ProjectId' and 'LmsUserId' to User object. I want to print all the attributes of a user - including ProjectId and LMSUserId:

MySQL provides a very useful function called GROUP_CONCAT. Like PIVOT Tables of Excel, it concats all the values of a column into single value (separated by comma). We will combine this with IF() function to separate out values as shown below:

SELECT u.userId, u.emailAddress, u.firstName, u.lastName,
   GROUP_CONCAT(IF(ec.name='projectId', ev.data_, NULL)) AS `ProjectId`,
   GROUP_CONCAT(IF(ec.name='lmsUserId', ev.data_, NULL)) AS `LmsUserId`
FROM User_ u JOIN ExpandoValue ev ON u.userId = ev.classPK
   JOIN ExpandoColumn ec ON ec.columnId = ev.columnId  GROUP BY u.userId;

The output of this query will look like this:


Hope this helps!

Do provide your feedback if you think I can improve this post. You can also subscribe to my blog by providing your email id in 'Follow by Email' section in the Right column.

References

Sunday, July 29, 2012

Consuming "HTTPS" Web Services in Java

Recently we had a requirement to consume a Secure web service (i.e. SSL enabled endpoint). This is quite common requirement that may interest many of you. I would not be surprised if you find terms like HTTPS, SSL, Certificates, KeyStore, TrustStore, etc. scary. I too didn't understand most of these terms till few years ago.

So let me first give a brief (unofficial) explanation of some of these scary terms.
  • SSL (Secure Socket Layer) is a protocol to encrypt information.
  • HTTPS is nothing but HTTP + SSL. HTTPS ensures that the information being transferred over HTTP is secured from theft.
  • SSL Certificate contains Public Key + Identity information of the website/company which is verified by the Certification authority (visit Reference #3, #4 if you don't understand this).
  • TrustStore: file that contains the server certificates that are required by JVM for accepting SSL connections with trusted servers (simply: to trust outbound connections).
  • KeyStore: file that contains client certificates that are required by JVM for establishing an SSL connection with a server that requires client certificate authentication.

Note: Technically, there is no difference between TrustStore & KeyStore. They both are essentially used for storing SSL Certificates.

Enough background? Okay! Time to go back to the original topic!

Here is a logical diagram of the process of consuming HTTPS enabled Web Services:
Oversimplified diagram of Java Client to consume Secure Web Service


Important: You don't need to change your java program to consume a SECURE web service Vs. a NON-SECURE web service.

You just need to import the SSL Certificate of the Secure Web Service to the TrustStore using a JVM tool called "keytool" (I will not go into the steps of importing certs using keytool).

JVM comes with a Certificate Store called 'cacerts'. It resides in "\jre\lib\security\cacerts". 'cacerts' is JVM's default TrustStore as well as default KeyStore. 

You can also create your own Certificate Store using "keytool". If you are using your own TrustStore instead of using 'cacerts', you have to inform JVM to look into it while searching for certificates using below property:
java -Djavax.net.ssl.trustStore=.\key.jks
If your Web Service client is a web application, then you need to set this property for your Web server's JVM. For example, I modified tomcat's setenv.bat file to add this JVM property:

set "JAVA_OPTS=... -Djavax.net.ssl.trustStore=%CATALINA_HOME%/trustStore/key.jks"


Isn't it easier than you thought it would be? :-)



References:
  1. http://ws.apache.org/xmlrpc/ssl.html
  2. http://www.eu-sol.net/science/bioinformatics/tutorials/secure-biomoby-web-services/how-to-call-a-secure-web-service 
  3. http://info.ssl.com/article.aspx?id=10241
  4. http://www.symantec.com/theme.jsp?themeid=how-ssl-works