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
-
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
- 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
- After that i had to restart the server for my changes to take effect
- Next i did create mySQLAdapter project by following the instructions on Creating SQL Adapters
- 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]
});
}
- 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>
- Once the adapter was ready i did deploy it on the server making sure that the deployment was successful
- 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>
- 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:
Hi Sunil,
Have you tried connecting to webservices which have self-signed certificates? Can you please let me know?
can u provide document for doing same with the db2 database
Thank you. This is a very good example.
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
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'
Will you post the definition of custom-db.1.jndi-name?
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
could you please post about
> inserting and retrieving images to database through SQL/HTTP adapters from IBM Worklight application.
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
Post a Comment