Monday, March 5, 2012

How to call MySQL stored procedures from WSO2 Data Services Server

 Stored procedures allows you to expose pre defined queries as an api allowing users to consume them in a much faster way.

Requirements.

MySQL Database server
WSO2 Data Service Server (DSS) 2.6.3
MySQL JDBC driver

Note: <DSS_HOME> is the root folder of your unpacked DSS server.

1.) Unpack WSO2 DSS and drop MySQL JDBC driver in <DSS_HOME>/repository/componets/lib folder.
2) Start the server using the start script <DSS_HOME>/bin/wso2server.sh  (In linux or wso2server.bat in windows)

Prepare Database

use following queries to generate a database with a stored procedure. [1]


DROP DATABASE IF EXISTS DATASERVICE_SAMPLE;
CREATE DATABASE DATASERVICE_SAMPLE;
GRANT ALL ON DATASERVICE_SAMPLE.* TO 'dsuser'@'localhost' IDENTIFIED BY 'user123';

USE DATASERVICE_SAMPLE;

DROP TABLE IF EXISTS Employees;

CREATE TABLE Employees(
        employeeNumber INTEGER,
        lastName VARCHAR(50),
        firstName VARCHAR(50),
        extension VARCHAR(10),
        email VARCHAR(100),
        officeCode VARCHAR(10),
        reportsTo INTEGER,
        jobTitle VARCHAR(50) 
);


insert into Employees values (1002,'Murphy','Diane','x5800','dmurphy@classicmodelcars.com','1',null,'President');
insert into Employees values (1056,'Patterson','Mary','x4611','mpatterso@classicmodelcars.com','1',1002,'VP Sales');
insert into Employees values (1076,'Firrelli','Jeff','x9273','jfirrelli@classicmodelcars.com','1',1002,'VP Marketing');
insert into Employees values (1088,'Patterson','William','x4871','wpatterson@classicmodelcars.com','6',1056,'Sales Manager (APAC)');
insert into Employees values (1102,'Bondur','Gerard','x5408','gbondur@classicmodelcars.com','4',1056,'Sale Manager (EMEA)');
insert into Employees values (1143,'Bow','Anthony','x5428','abow@classicmodelcars.com','1',1056,'Sales Manager (NA)');
insert into Employees values (1165,'Jennings','Leslie','x3291','ljennings@classicmodelcars.com','1',1143,'Sales Rep');

DROP PROCEDURE If EXISTS getEmployee;
CREATE PROCEDURE getEmployee(empNo INTEGER) SELECT employeeNumber,lastName,firstName,email FROM Employees where employeeNumber = empNo;
 Create Data Service.

1) click on DataService -----> Create

2) Add new Data Source.



3) Click next and Add new Query.


Here ? denotes the parameter required for the SQL query.

4) Add input parameter mapping 

Since we need to have an input parameter for the Data Service lets define it.


5) Add output Mapping.

There are elements in the output result we need to map. There are four output Mappings we need to define and i following screenshot shows adding one of them.


After adding all of them, Here how it looks like.


6) Click save and go to next page to add new operation. Click on "Add query params as operation params" to add input parameters for operation.


7) Click save and then click finish to deploy the Data Service. After few seconds refresh the web services list to view newly added Data Service.

8) Click on "Try this Service" to test the service. And then test your StoredProcedure exposed as a data service through WSO2 Data Services Server.

(here i have used 1002 as the employeeNo)




143 comments:

  1. Hi friends, This is Chandrika from Chennai. I am a technology freak. I have read your blog and got some valuable information from this blog. Your technical information is really useful for me. Keep update your blog.
    Regards..
    Oracle Training

    ReplyDelete
    Replies
    1. Useful Information
      Freshers jobs way provide freshers jobs ,walkins,direct recruitment,openings for freshers and experienced and recruiting experienced and freshers Walkins

      Delete
    2. Java Training Institutes Java Training Institutes
      Java Spring Hibernate Training Institutes in Chennai J2EE Training Institutes in Chennai J2EE Training Institutes in Chennai Java Spring Hibernate Training Institutes in Chennai | Hibernate Training Institutes in Chennai Core Java Training Institutes in Chennai Core Java Training Institutes in Chennai

      Hibernate Online Training Hibernate Online Training Hibernate Training in Chennai Hibernate Training in Chennai Java Online Training Java Online Training Hibernate Training Institutes in ChennaiHibernate Training Institutes in Chennai

      Delete
  2. Hi,
    All, I will also provide the wso2 products solution's and concerns..

    Regards
    Durga

    ReplyDelete
  3. Thank you so much..You share now informative effective..

    Informatica Training in Chennai

    ReplyDelete
    Replies
    1. I have read your blog its very attractive and impressive. I like it your blog.

      Digital Marketing Company in Chennai Digital Marketing Agency

      Delete
  4. Oracle DBA Training in Chennai
    Thanks for sharing this informative blog. I did Oracle DBA Certification in Greens Technology at Adyar. This is really useful for me to make a bright career..

    ReplyDelete
  5. Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly,
    but it’s no need to do it, because you have explained the concepts very well. It was crystal clear, keep sharing..
    Websphere Training in Chennai

    ReplyDelete
  6. Data warehousing Training in Chennai
    I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly..

    ReplyDelete
  7. Selenium Training in Chennai
    Wonderful blog.. Thanks for sharing informative blog.. its very useful to me..

    ReplyDelete
  8. Oracle Training in chennai
    Thanks for sharing such a great information..Its really nice and informative..

    ReplyDelete
  9. SAP Training in Chennai
    This post is really nice and informative. The explanation given is really comprehensive and informative..

    ReplyDelete
  10. This information is impressive..I am inspired with your post writing style & how continuously you describe this topic. After reading your post,thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic
    Android Training In Chennai In Chennai

    ReplyDelete
  11. Pretty article! I found some useful information in your blog, it was awesome to read,thanks for sharing this great content to my vision, keep sharing..
    Unix Training In Chennai

    ReplyDelete
  12. I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing..
    SalesForce Training in Chennai

    ReplyDelete
  13. There are lots of information about latest technology and how to get trained in them, like Best Hadoop Training In Chennai in Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies Hadoop Training in Chennai By the way you are running a great blog. Thanks for sharing this blogs..

    ReplyDelete
  14. Awesome! Thanks for providing informative article.
    http://www.crazybulksale.com/crazy-bulk-reviews/

    ReplyDelete
  15. This is really an awesome article. Thank you for sharing this.It is worth reading for everyone. Visit us:
    Oracle Training in Chennai

    ReplyDelete
  16. very nice blogs!!! i have to learning for lot of information for this sites...Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.
    Oracle DBA Training in Chennai

    ReplyDelete
  17. great article!!!!!This is very importent information for us.I like all content and information.I have read it.You know more about this please visit again.
    Oracle RAC Training in Chennai

    ReplyDelete
  18. Wonderful tips, very helpful well explained. Your post is definitely incredible. I will refer this to my friend.
    SalesForce Training in Chennai

    ReplyDelete
  19. I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly.
    Java Training in Chennai

    ReplyDelete
  20. Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog.
    PHP Training in Chennai

    ReplyDelete
  21. Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.Nice article i was really impressed by seeing this article, it was very interesting and it is very useful for me..
    Android Training in Chennai

    ReplyDelete
  22. Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog.
    SAP Training in Chennai

    ReplyDelete
  23. Excellent information with unique content and it is very useful to know about the information based on blogs.
    Hadoop Training in Chennai

    ReplyDelete
  24. i like this containt i read cotaint realy nice cotaint read more http://www.mycrazybulkreviews.com/

    ReplyDelete
  25. Excellent information with unique content and it is very useful to know about the information based on blogs.
    Hadoop Training In Chennai | oracle apps financials Training In Chennai | advanced plsql Training In Chennai

    ReplyDelete
  26. I have read your blog and got some valuable information from this blog. buy crazy bulk
    http://www.buycrazybulks.com/

    ReplyDelete
  27. Good to see you post again. I’m still at it. Glad you are too!
    http://www.buycrazybulks.com/

    ReplyDelete
  28. I am very impressed with the article I have just read,so nice http://www.weightlossopinion.com/

    ReplyDelete
  29. Excellent information with unique content and it is very useful to know about the information based on blogs. Phenq Review

    ReplyDelete
  30. Bed Bug Exterminator DC It is really a great and useful piece of information. I am glad that you shared this helpful info with us. Please keep us up to date like this. Thank you for sharing.

    ReplyDelete
  31. Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog.
    Oracle Training In Chennai

    ReplyDelete
  32. I am reading ur post from the beginning, it was so interesting to read & i feel thanks to you for posting such a good blog, keep updates regularly.Best Hadoop Training Institute In Chennai

    ReplyDelete
  33. very nice blogs!!! i have to learning for lot of information for this sites...Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.
    Pega Training In Chennai

    ReplyDelete
  34. Hello sir your article or work is so nice and full informative with each and singal point and i loved to reading that kind article which is available in your website so thanks and yes keep updating more stuff on this topic. Below check some stuff,
    Happy Holi Messages ,
    Happy Holi Sms ,
    Happy Holi Quotes ,
    Holi Images Pictures for Facebook and Whatsapp Status ,
    Holi Photos Images ,
    Happy Holi Wallpapers ,
    Happy Holi Pictures Greeting
    Happy Holi 2016

    ReplyDelete
  35. Hi

    Complete info the overcome from the PC virus ;

    http://removemicrofileextension.snack.ws
    http://removemicrofileextension.blogspot.in
    http://www.removemicrofileextension.hol.es
    http://removemicrofileextension.wikidot.com
    http://uninstallmicrofileextension.jimdo.com
    http://uninstallmicrofileextension.tumblr.com
    http://uninstallmicrofileextension.blog.com
    http://removemicrofileextension.over-blog.com

    ReplyDelete
  36. Great blog..You have clearly explained about the MYSQL..Step by step explanation is too good to understand..Its very useful for me to understand..Keep on sharing..
    Informatica training in chennai

    ReplyDelete
  37. Crazy bulk is one of the ideal options to build quality and strongest muscles masses just in 2 to 3 weeks. This is the greatest product for muscle growth that provides the good muscle size. The person who is looking for the right muscle gaining supplement they can choose this crazy bulk product to gain powerful muscles as well as the crazy bulk helps to increase your stamina. http://www.crazybulkstacks.com/

    ReplyDelete
  38. Great idea for fantastic apps thanks for sharing cool sharing ..:) Contact Us Content

    ReplyDelete
  39. Crazy bulk is one of the ideal options to build quality and trongest muscles masses just in 2 to 3 weeks. This is the greatest product for muscle growth that provides the good muscle size. The person who is looking for the right muscle gaining supplement they can choose this crazy bulk product to gain powerful muscles as well as the crazy bulk helps to increase your stamina. http://www.crazybulkstacks.com/

    ReplyDelete
  40. Crazy Bulk - Crazy bulk is one of the ideal options to build quality and strongest muscles masses just in 2 to 3 weeks. This is the greatest product for muscle growth & Read Crazy Bulk Review first. Provides the good muscle size. The person who is looking for the right muscle gaining supplement they can choose this crazy bulk product to gain powerful muscles as well as the crazy bulk helps to increase your stamina.
    http://www.crazybulkstacks.com/

    ReplyDelete
  41. Really brilliant post many apps Telephony Applications, Wordpress Plugins, Android IOS, Website Design And Development at most cost effective and affordable rates. Company About Us

    ReplyDelete
  42. he WordPress Simple Paypal Shopping Cart is a simple to utilize and lightweight WordPress module that permits you to offer items or administrations online in a single tick from your WordPress blog
    wordpress cart

    ReplyDelete
  43. Truyen ngon tinh hay la the loai truyen tinh cam
    Truyen teen hay la nhung truyen tinh yeu tuoi teen
    Don doc tai trang doc truyen online.
    Thanks for the best blog.it was very useful for me.keep sharing such ideas in the future as well. Thanks for giving me the useful information. I think I need it!
    Happy Wheels , FNAF World , Five Nights At Freddy's

    ReplyDelete
  44. this very good blog regarding the musql database language and you can check may latest blog which is focused on the Whatsapp Cool Status and Funny Whatsapp Status

    ReplyDelete
  45. Crazy bulk is one of the ideal options to build quality and strongest muscles masses just in 2 to 3 weeks. This is the greatest product for muscle growth that provides the good muscle size. The person who is looking for the right muscle gaining supplement they can choose this crazy bulk product to gain powerful muscles as well as the crazy bulk helps to increase your stamina. http://www.crazybulkstacks.com/

    ReplyDelete
  46. Data base sql and Mysql topics are more important and interested..
    Best SEO training in hyderabad all modules are clearly solved and practice with guide.seo training in hyderabadAnd clearly getting knowledge of seo.

    ReplyDelete
  47. All the best blogs that is very useful for keeping me share the ideas
    of the future as well this is really what I was looking for, and I am
    very happy to come here. Thank you very much
    earn to die
    earn to die 2
    earn to die 3
    Hi! I’ve been reading your blog for a while now and finally got the
    earn to die 4
    courage to go ahead and give youu a shout out from
    earn to die 6
    Austin Texas! Just wanted to tell
    earn to die 5
    Hi! I’ve been reading your blog for a while now and finally got the
    happy wheels
    strike force heroes
    slitherio
    you keep up the fantastic work!my weblog
    age of war

    ReplyDelete
  48. "Crazy bulk is one of the ideal options to build quality and strongest muscles masses just in 2 to 3 weeks. This is the greatest product for muscle growth that provides the good muscle size. The person who is looking for the right muscle gaining supplement they can choose this crazy bulk product to gain powerful muscles as well as the crazy bulk helps to increase your stamina.


    >>> http://www.crazybulkstacks.com/

    ReplyDelete
  49. Nice blog. You shared nice post. Thanks for sharing.

    web design company in chennai

    ReplyDelete
  50. This is the best application on the drop today. There access to enjoy great moments of relaxation: age of war 2|
    age of war 5
    Great! Thanks for sharing the information.Summon creatures to fight enemy units and demolish the opposing castle. Your castle is equipped with a crossbow, which you can use to shoot enemies age of war 6. Make sure you upgrade skills to increase your chances of winning battles.
    The goal of Age of War is to survive longer than the computer and to outlast him you’ll need to train the right troops while balancing your offence and defence in this high paced, quick thinking flash game age of war 4
    . Train troops of you own to combat the computers. As you kill off the computer troops, you will gain EXP and you will eventually advance to the next age.
    Choose a starter deck and prepare for an epic war!age of war 3

    Command your units in each battle to attack the enemy’s castle, while protecting your own base earn to die 2. Earn and upgrade cards to help you conquer the land.age of war

    happy wheels | tank trouble
    Thanks for the best blog.it was very useful for me.keep sharing such ideas in the future as well.this was actually what i was looking for,and i am glad to came here!
    cubefield It contains a plethora of tools and objects for level building such as harpoon guns,blocks and vans. Users can upload their maps to a public server where they are accessible

    ReplyDelete
  51. Wel done great work i have got here thank you for sharing,
    Forskolin Fuel

    ReplyDelete
  52. Great! Thanks for sharing the information. That is very helpful for increasing my knowledge in this fiel
    Red Ball | | duck life | Slitherio
    Red Ball 2 | Red Ball 3 | Red Ball 4

    ReplyDelete
  53. Thank you for sharing this knowledge in a blogpost.Really simple and even more effective and this worked great, very useful tips
    salesforce training in hyderabad

    ReplyDelete
  54. Good post. Happy to visit your blog. Thanks for sharing.

    digital marketing training in chennai

    ReplyDelete
  55. You’ve written nice post, I am gonna bookmark this page, thanks for info. I actually appreciate your own position and I will be sure to come back here.
    happy wheels | friv4school

    ReplyDelete
  56. Useful Information
    one and only affiliate agency in south INDIA, earn money online from affiliate network in india

    ReplyDelete
  57. Useful Information
    Freshers jobs way provide freshers jobs ,walkins,direct recruitment,openings for freshers and experienced and recruiting experienced and freshers Walkins

    ReplyDelete
  58. useful information

    Online recruitment for bank jobs and government jobs and you can get Notification and application to apply

    online for bank jobs and govt jobs

    ReplyDelete
  59. Useful information:
    General Awareness and Current Affairs with Latest Current Affairs Quiz, GK Updates, General Knowledge Last 6 Months Current Affairs, Placement Papers, Model Papers, Interview Questions @ examvalue.com

    ReplyDelete
  60. Good post. Thanks for sharing such a informative post.

    web design courses in chennai

    ReplyDelete
  61. Nice blog. Thanks for sharing such a useful post.

    php training in chennai

    ReplyDelete
  62. useful information

    Online recruitment for bank jobs and government jobs and you can get Notification and application to apply online for bank jobs and govt jobs

    ReplyDelete
  63. Useful information:
    General Awareness and Current Affairs with Latest Current Affairs Quiz, GK Updates, General Knowledge Last 6 Months Current Affairs, Placement Papers, Model Papers, Interview Questions @ examvalue.com

    ReplyDelete
  64. Rolex Watches Authentic Mens & Ladies Rolex Datejust, President Watches for Sale at JavyEstrella.com.

    ReplyDelete
  65. Score Hero download é um dos excelentse jogos de futebol para telemóveis e dispositivos que usam Android de 2 ou superior. baixar Photo Grid é um aplicativo de edição de foto, fazer colagem com muitos quadros atraentes.

    ReplyDelete
  66. • Great post and informative blog.it was awesome to read, thanks for sharing this great content to my vision.
    informatica Training in Chennai



    ReplyDelete
  67. Excellent post!!!. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.
    tibco training in chennai

    ReplyDelete
  68. Thanks for sharing such a nice article. It is really useful to me and all people. One think in my mind came while reading that it is not wasting of time.
    fireboy and watergirl
    Geometry Dash

    ReplyDelete
  69. The blog or and best that is extremely useful to keep I can share the ideas. Age Of War 2
    Big Farm | Slitherio | Tank Trouble
    Of the future as this is really what I was looking for, I am very comfortable and pleased to come here. Thank you very much.
    Happy Wheels | Goodgeme Empire | Slither.io

    ReplyDelete
  70. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.

    Discover our website bounty of free online games now!
    Our website has the biggest collection of free online games. Totally new games are added every day!

    age of war 2
    gold Miner 2
    unfair Mario 2
    cubefield 2
    tanki Online 2

    ReplyDelete
  71. Busy life circumstances than the current world history. Must try to earn money to support themselves, their families and pay for living. But not so that you neglect your own health, but more must cherish it.
    geometry dash , geometry dash game, geometry dash 2.0

    ReplyDelete
  72. I used to play games after long day of work tired or bored. And I would recommend to everyone a very interesting game and attractive for both color and sound
    run 2 , run 2 game, run 2 unblocked

    ReplyDelete
  73. How often do you play games on free time or after hours of work stress? I usually come to the game as a way to balance life is full of pressure. And I would recommend to everyone that I loved the game
    strike force heroes 2, strike force heroes 2 game, strike force heroes 3 , strike force heroes 2

    ReplyDelete
  74. I wanted to thank you for this excellent read!! I definitely loved every little bit of it.Cheers for the info!!!! & This is the perfect blog for anyone who wants to know about this topic
    baixar facebook , baixar facebook gratis , facebook baixar

    ReplyDelete
  75. Your article made me very impressed. I enjoyed it, I would expect at your website, or more articles
    baixar whatsapp,baixar whatsapp gratis, whatsapp baixar,

    ReplyDelete
  76. In your article, points caught my attention the most is how your prose, to give me a deep impression. Wish you would write more. good luck!
    return man 2 game ,return man 2 , return man

    ReplyDelete
  77. I think what you say on here is the best thing. Looking forward for our lives become increasingly more beautiful
    subway surfers game , subway surfer , subway surfers

    ReplyDelete
  78. اصبح مجال العاب بنات يعرف انتشارا واسعا مؤخرا لانه يملك عدة معجبين وخاصة البنات وهي بدورها تحتوي على عدة انواع منها العاب التلبيس والطبخ والمكياج والكتير وهذا ما جعلنا نفتح موقع جديد مختص في العاب بنات جديدة لكي يتمتع كل عشاق هذا الصنف باللعب والاستمتاع وزيارتكم لنا تسعدنا كتيرا وهو متجدد اسبوعيا ويقدم باقة جيدة من الالعاب المميزة والمشهورة في النت وستعجبكم اكيد .

    العاب فلاش تعرف انتشارا مميزا وغير عادي في الايام الاخيرة وكل الفئات تلعبها لانها مسلية وممتعة جدا وبعد ان اصبح لها معجبين كتر في نفس الوقت كترت المواقع الخاصة بالالعاب وتتميز الالعاب بكترة اصنافها فهناك انواع خاصة بالاولاد واخرى بالبنات وايضا هناك انواع يلعبها الكل متل العاب طبخ متلا فيمكن للاولاد لعبها والبنات ايضا وهناك اصناف يلعبها الاولاد فقط على سبيل المتال العاب سيارات و العاب سباق زد على ذلك العاب كرة القدم و العاب فرايف وللبنات انواع ايضا يلعبونها بكترة واكترها العاب تلبيس بنات و ايضا العاب مكياج و العاب قص الشعر والعديد من الاصناف الاخرى وهناك مواقع تتضمن كل هذه الانواع ولكن يوجد مواقع اخرى تتضمن صنف واحد فقط متلا وتكون متخصصة به وهم الاكتر وجودا بالنت لان المواقع الكبيرة والمعروفة على الصعيد العالمي هي التي تحتوي على تشكيلة تحتوي على كل الاصناف

    ReplyDelete
  79. Thank you for such a sweet tutorial - all this time later, I've found it and love the end result. I appreciate the time you spent sharing your skills.
    Facebook Lite

    ReplyDelete
  80. for beginners like me need a lot of reading and searching for information on various blogs. and articles that you share a very nice and inspires me . cara menggugurkan kandungan

    ReplyDelete
  81. Great post! We explain about this information easy understanding.Keep it well works!!!
    Selenium Training in Chennai

    ReplyDelete
  82. Your blog posts are more interesting and impressive. I think there are many people like and visit it regularly, including me.I actually appreciate your own position and I will be sure to come back here.
    hola launcher l run 2 l run 2 unblocked l run game l play run game

    ReplyDelete
  83. This is a great article. It gave me a lot of useful information. thank you very much. Can you play more games at :
    run 2 | supermechs | wings.io |run 2 game | wingsio | super mechs

    ReplyDelete
  84. This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharng this information,this is useful to me...
    Android Training in Chennai
    Ios Training in Chennai

    ReplyDelete
  85. This is a very good article material and it is very useful for us all. thank you . cara menggugurkan kandungan

    ReplyDelete
  86. You have done great work with image explanation. Thanks to shared this informative details with us. Keep updating.
    DBA course syllabus | DBA training courses

    ReplyDelete
  87. Not all are true. Everyone has their own way of thinking but I think they have to reconsider. I like to argue for the most accurate results.
    http://fivenightsatfreddysplay.com

    ReplyDelete
  88. This blog is so nice to me. I will continue to come here again and again. Visit my link as well. Good luck
    cara menggugurkan kandungan

    ReplyDelete
  89. Useful information. i am looking SAP HANA Online Training with real time project.

    ReplyDelete