Concatenating Row Values in T-SQL

Assuming you have a table like


and you want to print out a row containing all the players and games for a given gameId like


Then you can use query

DECLARE @temp TABLE (GameId INT, PlayerName CHAR(8), Score INT)

INSERT INTO @temp ([GameId],[PlayerName],Score) VALUES (1,'John',91)
INSERT INTO @temp ([GameId],[PlayerName],Score) VALUES (1,'Mary',100)
INSERT INTO @temp ([GameId],[PlayerName],Score) VALUES (2,'Sam',98)

select * from @temp

    SELECT ', ' + PlayerName + ':' + CAST(Score AS VARCHAR(8))
    FROM @temp
    WHERE (GameId = Results.GameId)
	--instead of returning rowset, let's return xml
	--try to set path to a non-empty tag to see the xml
    FOR XML PATH(''))
  --delete the first , and space
  ,1,2,'') AS NameScorePairs
FROM @temp Results
GROUP BY GameId				
Posted in Database | Tagged , , , , | Leave a comment

Build Your Vagrant Box From Scratch

Introduction: Vagrant Box

Did you ever experience the pain after your DEV PC crashed? Or the tedious steps to help a new hire to set up his DEV environment? If so, you may be interested in reading the post.

The post will give step-by-step instruction on how to build a virtual DEV enviroment and use Vagrant to make it reusable and shareable. The final artifact is called a BOX in Vagrant world (

Despite there are many public boxes available, we sometimes need to build it from scratch for organization’s security compliance. Packer ( is a tool that helps your automate the build steps, but inevitably you need to build a box manually at exploring phase. And this post will focus on the manual process.

What to Include in the Box

The box we are going to build will include the following:

  • Ubuntu 14.04: with dos2unix, curl, ssh server installed, and with OpenOffice removed
  • Oracle JDK 1.8.0_144, with JCE
  • GIT 2.11.0
  • Eclipse: Oxygen, with code formatter, Gradelw and Maven pre-configured
  • Maven standalone
  • Nodejs, with npm 4.3.1, grunt and bower
  • docker 1.32
  • docker-compose 1.17.0

How to Build Virtual Machine

The following assuming your host is Windows.

  1. Download and Install VirtualBox 5.1.26  from
  2. Download its Guest addition
  3. Download and install Vagrant
  4. Download Ubuntu 14.04 desktop and install it to VirtualBox
    • user name/password: vagrant/vagrant
  5. Shutdown Ubuntu
  6. Customize your virtual machine’s CPU, memory, dual display from VirtualBox settings
  7. Restart Ubuntu, the guest addition should be installed automatically. If not, unmount and remount the cd to the downloaded guest addition ISO file. The disk autorun should kick off automatically and it will install the guest addotion.
  8. Remove Open Office:
    • sudo apt-get remove –purge libreoffice*
    • sudo apt-get clean
    • sudo apt-get autoremove
  9. Remove Amazon application (Note don’t do sudo apt-get remove unity-webapps-common)
    • /usr/share/applications/ubuntu-amazon-default.desktop
  10. Add vagrant user to su
    • $ sudo  visudo
      # Add the following line to the end of the file. 
      vagrant ALL=(ALL) NOPASSWD:ALL
  11. Install vagrant public key
    • mkdir -p /home/vagrant/.ssh
    • $ wget –no-check-certificate –O /home/vagrant/.ssh/authorized_keys
    • $ chmod 0700 /home/vagrant/.ssh
    • chmod 0600 /home/vagrant/.ssh/authorized_keys
    • chown –R vagrant /home/vagrant/.ssh
  12. Install ssh server
    • sudo apt-get install -y openssh-server
    • check file /etc/ssh/sshd_config to ensure:
      • Port 22
      • PubKeyAuthentication yes
      • AuthorizedKeysFile  .ssh/authorized_keys


    • $sudo service ssh restart
  13. sudo apt-get dos2unix
  14. Hide Ubuntu taskbar and enable workspace
    • UbuntuSettings.png
  15. Optionally, you can pause here and build your first base box and test it. I omit this step to keep the post short. In fact, you can build a new box after each of the following steps to make reusable intermediate boxes. 
  16. Install Java
    • sudo add-apt-repository ppa:webupd8team/java
    • sudo apt-get update
    • sudo apt-get install oracle-java8-installer
    • Ensure the file /etc/profile.d/ presents , and contains a line
    • Download JCE from java website to /vagrant, and copy the two jars into /usr/lib/jvm/java-8-oracle/jre/lib/security
    • rm the downloaded files
  17. Install GIT
    • sudo apt-get install git
  18. Install Maven
    • cd /opt
    • sudo wget
    • sudo tar -xvzf apache-maven-3.5.2-bin.tar.gz
    • sudo mv apache-maven-3.5.2 maven
    • sudo nano /etc/profile.d/
      export M2_HOME=/opt/maven
      export PATH=${M2_HOME}/bin:${PATH}
    • sudo chmod +x /etc/profile.d/
    • sudo rm apache-maven-3.5.2-bin.tar.gz
    • log out then log in
    • Edit the /opt/maven/conf/settings.xml to have your maven local and remote repos. For example
  19. Install Eclipse
    • Download Eclipse Linux version from 
    • cd ~/Download


    • tar -zxvf eclipse-inst-linux64.tar.gz


    • sudo chown -R vagrant:vagrant eclipseinstaller


    • cd eclipse-installer


    • ./eclipse-inst


    • select IDE for Java EE Developers

      Use default /home/vagrant/eclipse/jee-oxygen

      use default workspace dir /home/vagrant/eclipse-workspace

      right click the icon and lock to lanucher

      import formatter from file google-java-styleguide.xml


    • sudo nano /etc/profile.d/

      export PATH=/home/vagrant/eclipse/jee-oxygen/eclipse:${PATH}


    • sudo chmod +x /etc/profile.d/
    • Open the preference -> maven -> user settings to ensure the file is pointing to /opt/maven/conf/settings.xml
  20. Install Docker
    • sudo apt-get update
    • sudo apt-get install apt-transport-https ca-certificates curl software-properties-common
    • curl -fsSL | sudo apt-key add –
    • sudo apt-key fingerprint 0EBFCD88                                Verify that you now have the key with the fingerprint 9DC8 5822 9FC7 DD38 854A E2D8 8D81 803C 0EBF CD88
    • sudo add-apt-repository “deb [arch=amd64] $(lsb_release -cs) stable”
    • sudo apt-get install docker-ce
  21. Install docker-compse
  22. Install Node.js

Build and Test Your Vagrant Box

  1. In your Ubuntu:
    • $ sudo dd if=/dev/zero of=/EMPTY bs=1M
    • $ sudo rm -f /EMPTY
    • $ sudo cat /dev/null > ~/.bash_history && history -c $$ exit
  2. Then shutdown your VM. Do the following from the host Windows:
    • vagrant package –output
    • vagrant box add ecs-java
  3. Test it from your Windows
    • Create a folder
    • Create a file named  Vagrantfile and save it with the following content
      • Vagrant.configure(“2”) do |config| = “mydev”
        config.vm.provider “virtualbox” do |vb|
        vb.gui = true
    • Run “vagrant up”
    • Wait until command finish
    • Now you can either “vagrant ssh” into it, or you can log into your VM from Ubuntu UI.

Congratulations. You have your DEV environment ready for you!

Share Your Box

Now you can share your box with your colleagues. You can either upload your box to a Vagrant repository, or put it into your shared directory (for example \\myShareDir). Then you insert a line to your vagrant file after the line “” and give it to your colleague.

config.vm.box_url = “file:////mySharedHost/mySharedDir/”

After he installed Vagrant and VirtualBox, he can just simply run “vagrant up”.




Posted in Tool and Debug, Uncategorized | Tagged , , , , , , , , | Leave a comment

CXF SOAP Client Example to Use Plain Text Password Authentication

CXF SOAP client can be configured to support various web authentication strategies via its out WSS4JOutInterceptor. For example it can be configured to support plain text username/password or digest based authentication. This post will show the plain text case. 

First of all, you need to add cxf-rt-ws-security and cxf-rt-frontend-jaxws to your dependencies.

With that you can use the following code to to access a  secured endpoint (which is MyService in this example).

//Example code

createSecureService(MyService.class, "",1000, 2000, "user", "password");

private <S> S createSecureService(Class<S> serviceClass, String url, long connectionTimeout,
  long receiveTimeout, String username, String password) {
  JaxWsProxyFactoryBean jaxWsFactory = new JaxWsProxyFactoryBean();
  S service = (S) jaxWsFactory.create();
  Client client = ClientProxy.getClient(service);
  Endpoint cxfEndpoint = client.getEndpoint();
  Map<String, Object> outProps = new HashMap<String, Object>();
  outProps.put(WSHandlerConstants.ACTION, WSHandlerConstants.USERNAME_TOKEN);
  outProps.put(WSHandlerConstants.USER, username);
  outProps.put(WSHandlerConstants.PASSWORD_TYPE, WSConstants.PW_TEXT);
  outProps.put(WSHandlerConstants.PW_CALLBACK_REF, new CallbackHandler() {
    public void handle(Callback[] callbacks) throws IOException, UnsupportedCallbackException {
      .forEach(callback -> callback.setPassword(password));
  WSS4JOutInterceptor wssOut = new WSS4JOutInterceptor(outProps);
  configureClient(connectionTimeout, receiveTimeout, client);
  return service;

private void configureClient(long connectionTimeout, long receiveTimeout, Client client) {
  HTTPConduit http = (HTTPConduit) client.getConduit();
  HTTPClientPolicy httpClientPolicy = new HTTPClientPolicy();

Posted in Java | Tagged , , , , | Leave a comment

CXF REST Client Parses Badgefish JSON Data

Badgerfish is one of the JSON conventions supported by CXF. To use it with CXF RS Client, you need to add both cxf-rt-rs-extension-providers and cxf-rt-rs-client to your dependencies. Then you can create a JSON provider and pass it to the web client to make request. The following example calls the D&B Direct API sandbox to retrieve some sample data.

Bean declaration:

public class SampleData{

Code to map JSON to the bean:

JSONProvider<SampleData> provider = new JSONProvider<>();
//Configure the provider as needed

WebClient client =
WebClient.create("", Collections.singletonList(badgerFishJson));
client = client.accept("application/json").path("/V5.0/organizations")
.query("CountryISOAlpha2Code", "US").query("SubjectName", "GORMAN2 MANUFACTURING")
.query("match", true).query("MatchTypeText", "Basic").query("TerritoryName", "CA");
client = client.header("Authorization", "xxxxxx");
SampleData result = client.get(SampleData.class);

Posted in Java | Tagged , , , | Leave a comment

JConsole Connection Failed

It is very annoying when you see JConsole failed to attach to a runtime with simply tells you “The connection to pid did not succeed. Would you like to try again?”. This is a completely useless message.

How to troubleshooting JConsole connection failure? You can start it with option

jconsole -J-Djava.util.logging.config.file="e:\\"

And the file should look like

//Log level is the level of java.util.logging.Level
handlers = java.util.logging.ConsoleHandler
.level = INFO
java.util.logging.ConsoleHandler.level = FINEST
java.util.logging.ConsoleHandler.formatter = \
java.util.logging.SimpleFormatter = FINEST = FINER

With this, you will see a separated log dialog popped out when you start Jconsole and it will print out more information about the failure.

Posted in Tool and Debug | Tagged , , | Leave a comment

ActiveMQ Message Expiration

When a message is produced, you can specify its timeToLive in milliseconds. This duration is added to the message every time when the message failed to be consumed. If there is no attempt to consume the message (i.e. consumer detached) within the next TTL duration, the message is considered expired.

There are different strategies to handle expired messages by the broker. The broker can be configured to automatically discard the expired message

    <sharedDeadLetterStrategy processExpired="false" />

or keep it into the DLQ for another configured period of time(the example below uses 5 minutes)

    <sharedDeadLetterStrategy processExpired="true" expiration="300000" />

The sharedDeadLetterStrategy above can be replaced by individualDeadLetterStrategy.

Posted in Java | Tagged , , , , , | Leave a comment

Isn’t our code beautiful?

Are we programmers artists? Everyone has to agree once they see our artworks. Our two secret paint brushes are GIT and Gource.

To see your art own artwork, follow the two steps:

  • install Gource
  • run command “gource” from your local git repo directory or check advanced options (gource –help)

The following screenshot was copied from Google image search result.


Posted in Tool and Debug | Tagged , | Leave a comment