Friday, May 31, 2013

Use SQL Adapter in IBM Worklight 5.0.x

I prepare a simple demo app for team to knowledge sharing to achieve a related goal into worklight.
then I think about why not share on blog by following a great dialog stated "Human knowledge belongs to the world" in movie (I don't remember which one :).

In this blog entry I am not going in much about introduction of Worklight and some of its component but expecting a reader to do few reading using a Google or IBM info center. So

IBM Worklight provide multiple adapters to interact with different media and work as mediator between mobile application and enterprise system. please follow the link to read Overview of IBM Worklight  Adapters. This entry is cover the SQL Adapter.


What is SQL Adapter?

The name itself is expressing, it provides access to enterprise databases to execute the parametrized SQL queries and stored procedures that retrieve or update data in the database.
Below image is illustrate high-level process involve.
 
 

Its Time for Action

Before start assuming you have already setup Worklight environment and now follow the steps is to create a SQL Adapter in Worklight.I have running my MySQL database on my local system. Please follow the step 1 to use this script add table into your schema.
Step 1:
CREATE TABLE `product` (
  `productID` int(11) NOT NULL AUTO_INCREMENT,
  `productName` varchar(255) DEFAULT NULL,
  `Qty` int(10) DEFAULT '0',
  `Model` varchar(255) DEFAULT NULL,
  `price` int(10) DEFAULT '0',
  PRIMARY KEY (`productID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

now next step is to create Worklight Adapter.

Step 2:

after hit the finish button this will create the adapter with name Product under adapters folder include 2 files Product.xml and Product-impl.js.

  • Product.xml (to define Data Source for DB and declare procedures you target to invoke)
  • Product-impl.js ( to defined queries to invoke against every procedure you defined in Product.xml)

Now open the Product.xml file and do define the Datasource like this.
<connectivity>
<connectionpolicy xsi:type="sql:SQLConnectionPolicy">
   <datasourcedefinition>
     <driverclass>com.mysql.jdbc.Driver</driverclass>
     <url>jdbc:mysql://localhost:3306/demo</url>
     <user>root</user>
     <password>root</password>
   </datasourcedefinition>
</connectionpolicy>
  <loadconstraints maxconcurrentconnectionspernode="5">
</loadconstraints>
</connectivity>
Note: You have to include “mysql-connector-java-5.1.24-bin.jar” library. If you don’t have one, then download here Download.

if you see your Product-impl.js file you find all you procedures in Step 2 image are automatically generate structure for developer to just change the column name and redefined the query as per need like below. for short explanation I am include two procedure code snippet here one is getProducts fetch all the project and another one is to insert.

/* Select Statement */
var selectProductStatement = WL.Server.createSQLStatement("select "+
"productID, productName, Qty, Model, price from product "+
"ORDER BY productName");

function getProducts() {
 return WL.Server.invokeSQLStatement({
  preparedStatement : selectProductStatement,
  parameters : []
 });
}
/* Insert Statement */
var insertProductStatment = WL.Server
  .createSQLStatement("insert into product "+
" (productName, Qty, Model, price) values (?, ?, ?, ?)");

function addProduct(productName, qty, model, price) {
 return WL.Server.invokeSQLStatement({
  preparedStatement : insertProductStatment,
  parameters : [ productName, qty, model, price ]
 });
}
for testing adapter you can right click on adapter folder -> run as -> Deploy as Worklight adapter. once its deployed successfully you .
now to call this procedure in client side adding a example of Add product.

function addProductRec() {
   var validate = true;
   var query = {};
   if ($("#prod_name").val() != "") {
        query.productName = $("#prod_name").val();
   } else {
        validate = false; 
   }

   if ($("#prod_model").val() != "") {
        query.Model = $("#prod_model").val();
   } else {
        validate = false; 
   }
   
   if ($("#prod_qty").val() != "") {
        query.Qty = $("#prod_qty").val();
   } else {
        validate = false;
   }

   if ($("#prod_price").val() != "") {
        query.price = $("#prod_price").val();
   } else {
        validate = false;
   }

   if (validate) {
       var queryData = JSON.parse(JSON.stringify(query));

       WL.Client.invokeProcedure({
                adapter : "Product",
  procedure : "addProduct",
  parameters : [ queryData.productName, queryData.Qty,
                      queryData.Model, queryData.price ]
  }, {
                  onSuccess : function(result) {
                  $.mobile.changePage("#list", {
          transition : "slide",
   reverse : false,
   changeHash : false
           });
                 getProductsRec();
  },
    onFailure : failureCallback
  });
 } else {
            alert("All fields required");
 }
}
now we are done and its time for testing.
Worklight Console : Screen 1
Worklight Console : Application Screen



Please click here to download the source code.

31 comments:

Ahsan said...

Though , i am not programming in Java , but this tutorial made lot of sense.
Very informative.
Thanks

Saifuddin said...

Hi @Ahsan,

Thanks for your input. I appreciated it,

Unknown said...

Good tutorial ..
how if i wanto to create a simple login apps using SQL Adapter and mysql in ibm Worklight?
Please tell me the source code ..

Saifuddin said...

Hi @samsul,

Thanks for your feedback, Yes If you like to authenticate a with SQL Adapter just add another procedure and use the select statement to compare username and password value and return boolean to check on client side.

Regarding code I am working on next tutorial to target authentication with using Form Based Authenticator in Worklight.

hope it helps.

Unknown said...

I hope the next tutorial will be published quickly by by you. I need it so much and I have limited time for the login authentication.
Help me please.!

Anonymous said...

This is good information.
could you also tried to call oracle procedure or function and return the values actually im stuck in it could you please provide an example for that

Saifuddin said...

Hi Anonymous,

Thanks for your feedback!

To invoke stored procedure there is method defined WL.Server.invokeSQLStoredProcedure accept json block of parameters.

if you are following the same example just add procedure in mySQL.

DELIMITER $$
CREATE PROCEDURE `getAllProducts`()
BEGIN
SELECT * FROM test.product;
END

then add procedure entry in adapter Product.xml and use the below line of code in Product-impl.js.

function getAllProducts(){

return WL.Server.invokeSQLStoredProcedure({
procedure : "getAllProducts",
parameters : []
});
}

Now Deploy worklight adapter and then invoke Worklight procedure with eclipse IDE.

Deploy Steps: Right click on adatper name Run As-> Deploy Worklight adapter
Invoke Steps: Right click on adatper name Run As-> invoke Worklight procedure

Anonymous said...

in mySQL you write select statement inside procedure but in oracle you cannot write you need either result set or OUT parameter to get these values.
We have a oracle DataBase stored procedure which returns a value. This value could be an out parameter or a result set . We are trying to call this oracle stored procedure through Work light SQL Adapter by using (WL.Server.invokeSQLStoredProcedure) and getting this error
“FWLSE0101E: Cause by : HelloWorklightProjectjava.sql.SQLException: ORA-06550:line 1, PLS-00221: it is not a procedure or not defined….”

If we do not return any value and execute same procedure it works fine which means that on oracle side there is no issue of procedure.
If we call a oracle function and return a value not result set through (WL.Server.createSQLStatement, WL.Server.invokeSQLStatement) there is no Issue.
--------


Im stuck with this will appreciate if you can help me Thanks

Saifuddin said...

Hi Anonymous,

Worklight SQL adapter does not support out parameters. [Reference].

hope if helps.

Dollar sharma said...

Hi i want to authenticate login page with mysql ....kindly help me in that...i will be thankful

Saifuddin said...

Hi Dollar Sharma,

Please check my previous comment reply to @Samsul, in the same.

hope it helps.

Dollar sharma said...

Kindly help me with the source code and procedure.....

Arun said...

hi thnks 4r these useful tutorial..

while i run these application on mobilesimulator it says that "loading please wait.." and take more time. is't anyother problem or config needed.

thanks in advance

Srikanth said...

Thanks ....thanks...
thanks a lot bro..


I cant explain in my words....
u helped me a lot..
once again
thanking you... _/\_

Saifuddin said...

Hi Srikanth,

Thanks for your feedback, I am glad this tutorial helps.

@Arun
Firstly, sorry for late reply, but I never encounter this issue and it might appear when application unable to connect server mostly when the default ip is used instead of System IP or Server IP in application-descriptor.xml.

hope it helps.

Thanks,

anish said...

hi
i need simple example to store data using sqladaptor in mysql (no checks no security simple code).

Saifuddin said...

Hi Anish,

I believe this already been covered in this blog entry, why not just give it a try :), you can also download this example code in the mentioned url.

Unknown said...

please tell me. how to insert the value in mysql using eclipse kepler worklight.

i've doing simple hybrid android worklight application. i done a select *from database is successfuly runed but insert query is not inserted. please help me.

Unknown said...

please tell me. how to insert the value in mysql using eclipse kepler worklight.

i've doing simple hybrid android worklight application. i done a select *from database is successfuly runed but insert query is not inserted. please help me.

Saifuddin said...

Hi Tema,

Please download the code from given download code url in the same.



Unknown said...

Hi muhammad. i've receive your code.
i work that code.that code is runing but this code for "select " query only. but i need insert code. i will check the insert coding it's work but i have to use html text productname,qty ,model,price it's does not get the value in .js.

Saifuddin said...

Sorry, I didn't get what you mean but things you're looking for are very straight forward the addProduct function defined in adapter js file using INSERT query, while addProductRec function is defined under the common folder .js file which use to call this procedure.

Hope it helps.

Unknown said...

yeah sir. but i want to solution for how give the value in html text field then that value stored in mysql database. that is my solution.

i have to check it insert query

var insertProductStatment = WL.Server
.createSQLStatement("insert into products (productName, Qty, Model, price) values ('sony ipad','10','high','5000')");

function addProduct() {

return WL.Server.invokeSQLStatement({
preparedStatement : insertProductStatment,
parameters : [ ]
});
}


this code is worked. but i need insert to html-xml-js-mysql

Unknown said...

I am a beginner please help me.
i want to insert name,usn to stud table which are read from text input feilds(named p1 and p2)

//code in common folder js file

function addSQLRecords(){ //i have linked this function to the submit button
var invocationData = {
adapter : 'jsonadapter',
procedure : 'addJsonadapters',
parameters : [p1,p2]
};

WL.Client.invokeProcedure(invocationData,{
onSuccess : addSQLQueerySuccess,
onFailure : addSQLQueeryFailure
});
}

function adddSQLQueerySuccess(){
WL.Logger.debug("insert success");

}

function loadSQLQueeryFailure(result){
WL.Logger.error("insert failure");
}

//code in sql adapter js file


var addStatement = WL.Server.createSQLStatement("insert into stud (name, usn) values (?,?)");

function addJsonadapter(p1,p2) {

return WL.Server.invokeSQLStatement({
preparedStatement : addStatement,
parameters : [p1,p2]
});
}


this code is not working what do i have to change to make it insert data into the table?

Saifuddin said...

Hi Veeresh gadag,

procedure : 'addJsonadapters' // line
Procedure name you're calling is not exist, remove the 's' character in "addJsonadapters" would make it work.

hope it helps.

Anonymous said...

a very relevant tutorial bhaijan.
keep up the good work.

Srikanth said...
This comment has been removed by the author.
Srikanth said...
This comment has been removed by the author.
Srikanth said...

Hello Sir,
Could you please post another one about
How to insert and retrieve photos into database using sql/http adapter through worklight application.

Nowhere i found about this topic.

This might be helpful to many learners.

Saifuddin said...

Thanks for your feedback Enjamoori.

Sure, I'll share whenever I get a chance :)

Srikanth said...

Thanks for a quick reply.

hope i can get another example of inserting and retrieving images from database using sql/http adapter from Worklight application :)