Connecting to Database from WorkLight

I wanted to figure out how to use SQL Adapter provided by WorkLight to create application that talks with database, with the difference that i want to access Apache Derby instead of MySQL that is used by WorkLight, so i built this simple application that takes contactId as input then use it to execute SELECT * from CONTACT where CONTACTID=contactId query and display the result.
I followed these steps to build my application
  1. First i did open the Worklight\server\conf\worklight.properties file in the text editor and i did add a section to define the JDBC connection parameters for connecting to Derby at the end of the file like this
    
    training-jndi-name=${custom-db.1.jndi-name}
    custom-db.1.relative-jndi-name=jdbc/worklight_training
    custom-db.1.driver=org.apache.derby.jdbc.ClientDriver
    custom-db.1.url=jdbc:derby://localhost:1527/C:/data/contact
    custom-db.1.username=dbadmin
    custom-db.1.password=dbadmin
    
  2. Then i did copy the derbyclient.jar which is JDBC driver for Apache Derby in the Worklight\server\lib folder, when i was copying the derbyclient.jar i noticed that, the same directory also has the mysql-connector-java-*.jar that worklight needs for its own database connectivity
  3. After that i had to restart the server for my changes to take effect
  4. Next i did create mySQLAdapter project by following the instructions on Creating SQL Adapters
  5. I changed the mySQLAdapter-impl.js like this
    
    var procedure1Statement = WL.Server.createSQLStatement("select * from CONTACT where CONTACTID = ?");
    function procedure1(param) {
      return WL.Server.invokeSQLStatement({
        preparedStatement : procedure1Statement,
        parameters : [param]
      });
    }
    
  6. Then i did use the following mySQLAdapter.xml
    
    <?xml version="1.0" encoding="UTF-8"?>
    <wl:adapter name="mySQLAdapter"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
      xmlns:wl="http://www.worklight.com/integration"
      xmlns:sql="http://www.worklight.com/integration/sql">
    
      <displayName>mySQLAdapter</displayName>
      <description>mySQLAdapter</description>
      <connectivity>
        <connectionPolicy xsi:type="sql:SQLConnectionPolicy">
          <!-- Replace 'data-source-jndi-name' with the jndi name as defined in the data source. -->
          <!-- Example using jndi name: java:/comp/env/jdbc/ProjectDS 
             or using a place holder: ${project.db.jndi-name}       -->
             
          <dataSourceJNDIName>${training-jndi-name}</dataSourceJNDIName>
        </connectionPolicy>
        <loadConstraints maxConcurrentConnectionsPerNode="5" />
      </connectivity>
    
        <!-- Replace this with appropriate procedures -->
        <procedure name="procedure1"/>
    </wl:adapter>
    
  7. Once the adapter was ready i did deploy it on the server making sure that the deployment was successful
  8. Then i did create HelloDatabase application in the same project that has the adapter and i changed the main html to look like this
    
    <!DOCTYPE html>
    <html>
        <head>
            <meta charset="utf-8" />
            <meta name="viewport" content="width=device-width, initial-scale=1.0,
      maximum-scale=1.0, minimum-scale=1.0, user-scalable=0" />
            <title>HelloDatabase</title>
            <link rel="shortcut icon" href="images/favicon.png" />
            <link rel="apple-touch-icon" href="images/apple-touch-icon.png" />
            <link rel="stylesheet" href="css/reset.css" />
            <link rel="stylesheet" href="css/HelloDatabase.css" />        
        </head>
    
        <body onload="WL.Client.init({})" id="content" style="display: none">
         <table>
          <tr>
           <td>Contact Id</td>
           <td><input type="text" name="contactId" id="contactId"/></td>
          </tr>
          <tr>
           <td><button onclick="getContact()" title="GetContact" 
        label="GetContact">GetContact</button></td>
          </tr>
         </table>
         <div id="displayContact">
         
         </div>
         
            <script src="js/HelloDatabase.js"></script>
            <script src="js/messages.js"></script>
            <script src="js/auth.js"></script>
        </body>
    </html>
    
  9. Last step was to change the HelloDatabase.js like this
    
    function wlCommonInit(){
      // Common initialization code goes here
    }
    
    function getContact(){
      
      
      var contactId = $("contactId").getValue();
      console.log("Contact id " + contactId);
      var invocationData = {
          adapter:"mySQLAdapter",
          procedure:"procedure1",
          parameters:[contactId]
      }
      var options ={
          onSuccess:loadContactSuccess,
          onFailure:loadContactFailure
      }
      
      WL.Client.invokeProcedure(invocationData, options);
    }
    
    function loadContactSuccess(result){
      console.log("Inside loadContactSuccess " + result);
      console.log(result.invocationResult.resultSet[0].FIRSTNAME)
      $("displayContact").innerHTML = result.invocationResult.resultSet[0].FIRSTNAME + " " 
      + result.invocationResult.resultSet[0].LASTNAME +" " 
      + result.invocationResult.resultSet[0].EMAIL;
    }
    
    function loadContactFailure(result){
      console.log("Inside loadContactError " + result);
    }
    

10 comments:

Mahi said...

Hi Sunil,

Have you tried connecting to webservices which have self-signed certificates? Can you please let me know?

Naresh Nelluri said...

can u provide document for doing same with the db2 database

Megan Irvine said...

Thank you. This is a very good example.

Health Community said...
This comment has been removed by the author.
Health Community said...

Hi Sunil,

I get the following error when i try the above

[2013-03-07 12:10:23] Adapter deployment failed
[2013-03-07 12:10:23] Could not resolve placeholder 'custom-db.1.jndi-name'

It would be nice to have a suggestion that where i am going wrong.

Thanks & Regards

Senthilkumar

Anonymous said...

Anyone got a resolution for this error -

[2013-03-07 12:10:23] Adapter deployment failed
[2013-03-07 12:10:23] Could not resolve placeholder 'custom-db.1.jndi-name'

Anonymous said...

Will you post the definition of custom-db.1.jndi-name?

Lokesh Reddy said...

hi i'm beginner to worklight .
when i click i didn't get anything.
i trying for retrieval since 2 days
when i use alert box in success field i'm getting an alert box.
but when i'm using console.log or WL.Logger.debug or WL.Logger.error, i didn't get anything
anyone help please

Enjamoori Srikanth said...

could you please post about

> inserting and retrieving images to database through SQL/HTTP adapters from IBM Worklight application.

Cole said...

Awesome info! Have you ever had to connect to an Oracle database? If so, would you mind sharing the dataSourceDefinition configuration? Thanks in advance, Cole