Accessing Databases with HTML5 WebSockets and Apache Camel

As I have said on my previous post, I've been working with several companies with the most diverse use cases and one that really brought my attention was the requirement to access databases using HTML5 WebSockets which seems to be a clever thing to do with the right tools.

So, basically what I've setup for this is represented on the picture below:

Let's go over the implementation details of all of the components described here:

From right to left, the backend datasource I had to use was MySQL that you can download from http://www.mysql.com/downloads/. Then, I used Apache Camel as the backend framework accessing the database and also exposing a TCP-based component (Mina in this case). Moving to the left, we have the Kaazing WebSocket Gateway connecting to the Apache Camel framework and exposing WebSocket connectivity to the web clients (browser-based, mobile-based and desktop thick client applications).

The web-browser sample client is pretty straightforward and with a few lines of code you can get the functionality. There are sample javascript clients shipped with the Kaazing WebSocket Gateway that you can re-use or modify to your needs. The Kaazing WebSocket Gateway documentation set is also helpful and good starting point. Check it out: http://tech.kaazing.com/documentation/html5/3.3/o_dev_js.html

Then the only change you have to make in the Kaazing WebSocket Gateway is in the gateway-config.xml (under $KWG_HOME/conf) to include the service you want to expose to the web clients and where you want to connect to the backend framework with access to the database.

Here is what I have included on my configuration:

<!-- Demo JDBC Backend System Config -->

  <!-- Proxy service --> 

  <service>

<accept>ws://${gateway.hostname}:${gateway.extras.port}/jdbc</accept> 

<type>proxy</type>

<properties>

<connect>tcp://${gateway.hostname}:8888</connect>

</properties>

   </service>

Check the Kaazing WebSocket Gateway documentation to get more familiar with these settings.

Last but not least, the Apache Camel route hosting the Mina endpoint and the database connectivity:

 <camelContext xmlns="http://camel.apache.org/schema/spring">

    <package>com.kaazing.gateway.demo.db</package>

    <route id="WSMinaJDBCSample">

        <from uri="mina:tcp://localhost:8888?timeout=30000"/>

        <log message="RECEIVED: ${body}"/>

        <setBody>

        <simple>select * from customers where customers.email = '${body}'</simple>

        </setBody>

        <to uri="jdbc:myDataSource"/>

        <log message="GENERATED: ${body}"/>

    </route>

</camelContext>

<!-- MySQL datasource -->

  <bean class="org.springframework.jdbc.datasource.DriverManagerDataSource" id="myDataSource">

    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>

    <property name="url" value="jdbc:mysql://localhost:3306/test"/>

    <property name="username" value="root"/>

    <!-- <property name="password" value="XXXXX"/>  -->

  </bean>

The MySQL database setup is very simple and you can use any sample database tables you want. Just make sure you update the Apache Camel route and your environment settings accordingly.

Code will be available soon...

UDPATE:
Sample code is now available at: https://github.com/mjabali/DBOverWS

JMS and Database Integration with Apache ServiceMix, Camel and ActiveMQ

I've been asked to create a simple project to demonstrate how Apache ActiveMQ, Camel and ServiceMix could be used in the same integration context. Then, for the specific customer I was talking to, I decided to create something related to the world they were used to.

This example will process messages as they are delivered on an input queue and I thought that XML would be a good format to also demonstrate how to parse the payload and generate an SQL statement  before hitting the database.

In summary, for each message going to the input queue, we're going to create a record in the database table and then generate a response message on the output queue.

I'm assuming you're already have Apache ServiceMix downloaded and installed in your machine. If not, it's time to download it… I recommend you to just go to http://fusesource.com/downloads/ and download FUSE ESB.

I'm also assuming that you already have Maven installed but if not feel free to download and install it from maven.apache.org

Here are the steps to create this short and simple demonstration:

1) Download and install MySQL database from http://dev.mysql.com/downloads/
You can use the default test database that's shipped with MySQL and the only thing you have to do is to create a table to insert test records. To do that, connect to the database  and run the following SQL statement:

CREATE table PARTNER_METRIC(partner_id int, time_occurred DATE, status_code int, perf_time long);


2) Install the following features in ServiceMix:

features:install camel-jdbc
features:install camel-jms


3) Install commons-dbcp bundle:

osgi:install -s mvn:org.apache.servicemix.bundles/org.apache.servicemix.bundles.commons-dbcp/1.2.2_6


4) When you do this you will receive a bundle id, e.g. 229.  Enable dynamic imports on this bundle so that it can pick up the appropriate database driver.

dev:dynamic-import 229


5) Using ServiceMix hot deploy feature to deploy the MySQL JDBC driver:

cp /Applications/MySQL/mysql-connector-java-5.1.18/mysql-connector-java-5.1.18-bin.jar  /Users/mjabali/Fuse/apache-servicemix-4.4.1-fuse-00-08/deploy/


Then you can verify that the driver gets deployed correctly with the following command:

karaf@root> list |grep -i mysql


and you should get a message similar to the following:

[ 227] [Active     ] [            ] [       ] [   60] Sun Microsystems' JDBC Driver for MySQL (5.1.18)


6) Download the source files of this sample and extract them on the directory of your choice. So, let's review the JMS and Database connection settings  in the following resource files:

$PROJECT_HOME/src/test/resources/sample/RiderAutoPartsPartnerTest.xml
$PROJECT_HOME/src/main/resources/META-INF/spring/beans.xml file

These are the same file albeit named differently. One is used by the bundle and the other by our test case.

We'll be also using the default Apache ActiveMQ instance for the messaging aspect of this tutorial.
To check whether the ActiveMQ is installed, up and running you can run the following command:

karaf@root> osgi:list |grep activemq-broker


and you should see an output similar to this

[  66] [Active     ] [Created     ] [       ] [   50] activemq-broker.xml (0.0.0)


You can see the default broker's definition within the activemq-broker.xml file under $SERVICEMIX_HOME/etc/activemq-broker.xml

The database source definition is on the beans.xml file and you may want to adjust it to reflect the settings on your environment. Here is a copy of the definitions I have on my machine:

<bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">


    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>

    <property name="url" value="jdbc:mysql://localhost/test"/>

    <property name="username" value="root"/>

    <property name="password" value="XXXX"/>

</bean>

7) Let's compile the code then… Run the following command on the root directory of the project

mvn clean install -Dmaven.test.skip=true


If the code builds up successfully then you're ready to deploy on ServiceMix.

8) To deploy the bundle into ServiceMix, run the following command:

karaf@root>osgi:install mvn:com.fusesource.fusebyexample/camel-jms-dbase/1.0-SNAPSHOT


A confirmation that the bundle was deployed successfully should be returned to you and a message similar to the follow one should be displayed:

Bundle ID: 228


Then, you are ready to start the bundle and verify if has started correctly. Follow the instructions below to do that:

To start the bundle:

karaf@root>osgi:start <bundle ID>


To verify the bundle has started correctly:

karaf@root>osgi:list |grep <bundle ID>


and then a message like the following should be displayed:

[ 228] [Active     ] [            ] [Started] [   60] Camel JMS Database Example (1.0.0.SNAPSHOT)


You can also immediately turn on the trace logging capability for Camel and get additional output in the ServiceMix log facility. To enable the trace logging just run:

karaf@root>set TRACE org.apache.camel


and then to see the ServiceMix log, run:

karaf@root>log:display


You should be able to see messages with the TRACE identifier similar to the below:

11:46:17,095 | TRACE | tnerRequestQueue | JmsMessageListenerContainer      | ?                                   ? | 94 - org.springframework.jms - 3.0.5.RELEASE | Consumer [ActiveMQMessageConsumer { value=ID:titan.local-54477-1319240594120-6:1:1:1, started=true }] of session [PooledSession { ActiveMQSession {id=ID:titan.local-54477-1319240594120-6:1:1,started=true} }] did not receive a message


9) To test this sample project all you have to do is to send a sample message (there is one included in the $PROJECT_HOME/src/test/resources/sample directory) to the ActiveMQ queue that the Camel route is listening for. You can use any approach you like but writing a JMS Test client is pretty simple and there are tons of examples on the Internet and also available in the ActiveMQ sample directory. You can also use tools like HermesJMS which adds a little UI for you so you don't need to create a test program.

Either way, after sending a message to the partnerRequestQueue (default queue name used on this tutorial) you should see a couple of messages in the ServiceMix console like those below:

karaf@root>  **** toSql returning: INSERT INTO PARTNER_METRIC (partner_id, time_occurred, status_code, perf_time) VALUES ('123', '200911150815', '200', '9876')

 **** fromSql returning: Sample message to be returned on reply queue


You can verify that the values were inserted into the MySQL database running a simple query like:

select * from PARTNER_METRIC;


It is also recommended to check the ServiceMix log to make sure there are no exceptions there.

The sample code for this tutorial is available on GitHub: https://github.com/mjabali/JMS-DB-Sample

I also would like to thank my colleague Susan Javurek for putting an extra effort on this and helping out with the sample code.

UPDATE: I also would like to say a big thanks to Claus Ibsen (@davsclaus) for reminding me that all the manual steps related to OSGi bundles installation could be automated using the feature capability that consists in deploy multiple related bundles into a larger unit of deployment.

Enjoy the ride...

How to Declare Variables in MS-SQL Server Management Studio

Most of the projects I work on have some kind of database interaction and some of them require variables to be the passed to the queries at runtime.

I typically test my queries against the database using Microsoft SQL Server Management Studio and being able to use variable on my queries there is a big help.

Here is a quick hint to use variables with MS-SQL Server Management Studio:


-- Database to use
USE OSBC;

-- Declare the variable you need
DECLARE @AcctNumber varchar;

-- Set the value of the variable
BEGIN
SET @AcctNumber = 1
END

-- Run the query testing the paramenters
select * from contacts
WHERE AccountNumber = @AcctNumber


Here is a screenshot of my test:

Pretty simple stuff yet useful...