Popular Posts

Friday, January 4, 2008

Call MySQL stored procedures using WSAS data services

WSO2 Web services application server (WSAS) is shipped with a lot of useful features which can be used effectively in a production system. One of these cool functionalities is exposing relational data using WSAS data services.

Lets see how we can call an existing stored procedure using an WSAS data service. Here I'm going to use a MySQL DB instance for the demonstration purposes. You may use any DBMS as your preference.

Step 1

Create a data base
mysql>create database employeedb;

Step 2
Create a table and inseert values.
mysql>create table employee (id INTEGER, name varchar(20), address varchar(30));

mysql>insert into employee values (1, 'Charitha', 'Colombo');

Repeat the insert statement with more data.

Step 3

Create a stored procedure.

CREATE PROCEDURE getEmployee(empid INTEGER) select id, name, address from employee where id=empid;

Step 4
We haven't used WSO2 WSAS data service configuration yet. All of the above are basic MySQL database setting up steps. Now, we can create a data service configuration using WSAS management console. Lets start our WSAS instance by running WSAS_HOME/bin/wso2wsas.bat {sh}

Then log in to the management console using default administrator credentials (admin/admin). Next, select 'Services' from the left navigation and click on 'Define data service'

Step 5
Now you are in the first step of the data service configuration wizard. Provide a unique name for your data service. Then, select 'RDBMS' as the data source. You will get a pop-up window as given in the following screen.

Step 6

In the above pop-up window, select 'MySQL' as the database type. Then enter the following values for the remaining fields.
Driver class = com.mysql.jdbc.Driver
JDBC URL = jdbc:mysql://localhost:3306/employeedb
user = root
password = root

Next, click on 'Test Connection' to verify the communication between WSAS and our database.
If the connection is successful, click 'OK' in the pop-up window.

Step 7

Now we are done with the first step of the data service configuration. Click 'Next' to proceed to the step 2.

Click on 'New Query' button to add a query to our data service. You will get 'Add new Query' pop-up window. Fill the values as given in the screen shot.

Note that, the SQL statement field contains the stored procedure call (call getEmployee(?)).

Click 'OK' in the pop-up window. Then select 'Next' to continue through the wizard. You will be directed to the step 3 of the data service configuration.

Step 8

Click on 'Add new operation' button. 'Add new operation' pop-up window will be displayed. Enter a unique name for the operation. Select 'query1' from the 'Query' drop down menu.

Click 'Finish' to save our data service.

If everything is successful, you will see the deployed data service in the 'Services and service group management' page. Now, we need to verify whether the stored procedure call generate correct results. In order to do that, we may use 'Tryit', one of the very helpful tools in WSAS which can be used to verify simple service invocations.

Step 9

Select the newly created data service from the 'Services and service group management' page.
Select 'Tryit'.
Click on the operation button after entering empid.

As you can see above, our MySQL stored procedure has been invoked by data service. We can enhance the data service configuration to handle more complex stored procedures.


crystal said...

Thank you so much!!polo shirt men'ssweate,Burberry Polo Shirts lacoste sweater, ralph lauren Columbia Jackets,ski clothing. Free Shipping, PayPal Payment. Enjoy your shopping experience on mensclothingus.com.You can find the father who desire fashionable, intellectual mens clothing simultaneously.

crystal said...

Awesome!!!Best wishes for you !!cheap polo shirts is the father of the summer should be prepared to most commonly used item, it has both style and shape of Ralph Lauren Polo, and vest with a random function polo ralph lauren, so that in the short-sleeved apply to both on many occasions, the pink and black color men's polo shirts brought into effect, lightweight cotton, linen texture to demonstrate masculine temperament and sense of fashion exhaustively.

crystal said...

God bless you!I really agree with your opinions.Also,there are some new fashion things here,gillette razor blades.gillette mach3 razor bladesfor men.As for ladies,gillette venus razor blades must the best gift for you in summer,gillette fusion blades are all the best choice for you.

crystal said...

Perfect!!You are a outstanding person!Have you ever wore chaussures puma,Here are the most popular puma CAT,Puma shoes store gives some preview of puma speed cat,and casual but no sweat puma basket.

swimingfidh said...


swimingfidh said...

There are ed hardy shirts
,pretty ed hardy shirt for men,

ed hardy womens in the ed hardy online store

designed by ed hardy ,
many cheap ed hardy shirt ,glasses,caps,trouers ed hardy shirts on sale ,

You can go to edhardyshirts.com to have a look ,you may find one of ed hardy clothing fit for you
Top qualitymen's jacket,
These cheap jacket are on sale now,you can find
north face jackets inmage on our web
Ralph Lauren Polo Shirtsbuberry polo shirts

Do you wannaghd hair straighteners for you own , we have many
cheap ghd hair straightenersin style and great,you can choose one from these
hair straighteners
Authentic chaussure puma
chaussure sport
And chaussure nike shoes

swimingfidh said...

Come here to have a look of our Wholesale Jeans
Many fashionMens Jeans ,eye-catching
Womens Jeans ,and special out standing
Blue Jeans ,you can spend less money on our
Discount Jeans but gain really fine jeans, absolutely a great bargain.
China Wholesale
wholesale from china
buy products wholesale
China Wholesalers