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:

  1. MahiApril 17, 2012 at 8:19 AM

    Hi Sunil,

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

    ReplyDelete
  2. UnknownJuly 26, 2012 at 6:26 AM

    can u provide document for doing same with the db2 database

    ReplyDelete
  3. Megan IrvineAugust 1, 2012 at 7:27 AM

    Thank you. This is a very good example.

    ReplyDelete
  4. Health CommunityMarch 6, 2013 at 10:42 PM

    This comment has been removed by the author.

    ReplyDelete
  5. Health CommunityMarch 6, 2013 at 10:47 PM

    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

    ReplyDelete
  6. AnonymousMay 22, 2013 at 12:07 PM

    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'

    ReplyDelete
  7. AnonymousMay 30, 2013 at 1:13 PM

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

    ReplyDelete
  8. UnknownMarch 26, 2014 at 12:05 AM

    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

    ReplyDelete
  9. SrikanthMay 15, 2014 at 8:04 AM

    could you please post about

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

    ReplyDelete
  10. ColeJune 12, 2014 at 11:07 AM

    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

    ReplyDelete