Popular Posts

Friday, September 24, 2010

How to call Oracle Stored Procedures from data services

An Oracle stored procedure is a program stored in an Oracle database which allows business logic to be embedded inside database as an API. You can expose the stored procedures as web services using wso2 data services server. This example takes you through creating a simple stored procedure in an Oracle DB and expose it as a data service using WSO2 Data Services Server.


Download wso2 data services server from here
Oracle 10g or later

Step 1: Create and Populate sample database

First, open sqlplus shell and log in to DB as follows.

sqlplus /nolog

SQL*Plus: Release - Production on Wed Sep 23 22:52:01 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect sys as sysdba;
Enter password:

We should create a user and grant him the necessary privileges as follows.

SQL> create user sample identified by sample account unlock;

User created.

SQL> grant connect to sample;

Grant succeeded.

SQL> grant create session, dba to sample;

Grant succeeded.

SQL> exit;

Now logged in as the new schema user as follows.

sqlplus sample/sample@orcl

SQL*Plus: Release - Production on Wed Sep 23 22:52:01 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect sys as sysdba;
Enter password:

Create a table and insert data as follows.

SQL> create table employee (id number primary key, name varchar(100), address varchar(100));

SQL> insert into employee(id, name, address) values (1, 'charitha', 'colombo');
SQL> insert into employee(id, name, address) values (2, 'john', 'galle');
SQL> insert into employee(id, name, address) values (3, 'michel', 'otawa');
SQL> insert into employee(id, name, address) values (4, 'carl', 'dallas');
SQL> insert into employee(id, name, address) values (5, 'chanmira', 'colombo');
SQL> commit;

Now we are ready with oracle database and schema.
Lets write a simple stored procedure to insert more data to employee table.

Step 2: Writing a simple stored procedure

SQL> create or replace procedure addEmployeeSP(id number, name varchar, address varchar) is
2 begin
3 insert into employee (id, name, address) values (id, name, address);
4 end;
5 /

Step 3: Copy oracle jdbc driver

In order for wso2 data services server to communicate to oracle DB, we should download oracle jdbc driver (ojdbc14.jar) and copy in to DS_HOME/repository/components/lib directory.

Step 4: Create the data service through UI wizard

First, start wso2 data services server by running wso2server.sh {bat} which can be found at DS_HOME/bin
Then access management console through https://localhost:9443/carbon and log in using default admin credentials (admin/admin)

Click on Data Service --> Create in the left menu which will bring up data service creation wizard.

Enter a name for the data service and click on next. (For this example, I have specified "BlogExampleDataService" as the service name)

In Data Sources screen, click on Add new data source link and specify a data source ID and select RDBMS as the data source type. Then select Oracle as the Database engine and enter the db info as follows.

Driver Class = oracle.jdbc.driver.OracleDriver
JDBC URL = jdbc:oracle:thin:sample/sample@
User Name = sample
Password = sample

Click on "Test Connection" to see whether you can connect to oracle server correctly. Then click on save. Now we have created a data source hence we can proceed through the wizard.

Click on Next to move to query definition page. Select Add New Query to create a new query for our data service.

Enter a query ID (ex:- employees) and select the data source we just created from the Data Source drop down.

We can specify our SQL statement in the SQL text area. We need to call the oracle stored procedure which has been created earlier. You can enter "call addEmployeeSP(?,?,?)" as the query to call oracle stored procedure. ? denotes the input parameters which should be passed to the stored procedure.

Next, click on Add new input mapping and add three new input parameters since our query accepts 3 different parameters.

After adding all 3 input parameters, the query will be looked as follows.

We can save the query now. Our stored procedure inserts employee records in to the table therefore it does not return anything.
Because of that, the output mappings are not required for the query.

In order to check whether the records are correctly added to the database, lets create another query, selectEmployees as shown in the following screen.

Now, we have created both queries. Lets add operations which are necessary to run these queries.

Click Next in the Queries screen to proceed through the wizard which will bring up Operations page. Click on Add New Operation link. Specify operation name, addEmployee and select Query ID, employees.

Similarly, add an operation for selectEmployee query and name it as selectAllEmployees.

Click on Finish to deploy the data service. Once it is deployed, the service will be shown in the service list. Click on Try this service link to test the service.
In there, you will find two operations, addEmployee and selectAllEmployees. First invoke addEmployee operation by specifying id, name and address.

Now, if you invoke selectAllEmployees operation, you will see that the employee table has been updated with a new record.

Thats all! Drop me a mail or post your question at wso2.org forum if you have any questions about WSO2 Data Services Server.

Wednesday, September 22, 2010

Input validation of data services

Input validation is a new feature included in the latest version of WSO2 Data Services Server (WSO2 DSS). With this, the further processing of a data service request message can be stopped at the service layer without reaching the backend data source based on a pre-defined input validation logic.
This is achieved either using a set of built in validators or custom validator implemented by the service author.

There are four different built-in validators.

1. Long range validator
This can be used to validate an integer input parameter value as follows.

<param name="id" paramType="SCALAR" sqlType="INTEGER" type="IN" ordinal="1">
<validateLongRange minimum="1" maximum="40" />

2. Double range validator
This is useful when a validity of a float value has to be checked.

<param name="distance" paramType="SCALAR" sqlType="DOUBLE" type="IN" ordinal="2">
<validateDoubleRange minimum="1.5" maximum="850.45" />

3. Length validator
This can be used to check whether the input parameter value conform to the speficied string length.

<param name="name" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="3">
<validateLength minimum="2" maximum="20" />

4. Pattern validator
This validates the string value of the input parameter against a given regular expression.

<param name="indexno" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="4">
<validatePattern pattern="(?:[a-z0-9]" />

Finally, you can write your own validator based on your requirements and use it in the data service query definition. In order to do that, you must implement org.wso2.carbon.dataservices.core.validation.Validator interface in your custom validator class as follows.

import org.wso2.carbon.dataservices.core.validation.Validator;
import org.wso2.carbon.dataservices.core.validation.ValidationContext;
import org.wso2.carbon.dataservices.core.validation.ValidationException;
import org.wso2.carbon.dataservices.core.engine.ParamValue;

public class MyCustomValidator implements Validator {
public void validate(ValidationContext validationContext, String s, ParamValue paramValue)
throws ValidationException {
if (!paramValue.getScalarValue().startsWith("2")) {
throw new ValidationException("Not starting with 2!!",s,paramValue);


Then, you can build a jar with the custom validator class and place it in CARBON_HOME/repository/components/lib directory. After restarting the server, you can use it inside query definition as follows.

<param name="id" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="1">
<validateCustom class="org.test.MyCustomValidator" />

Monday, September 13, 2010

Creating value with testing

Jonathan Kohl discusses about creating value with software testing. You will find very important set of points which we must follow in daily QA/testing activities.

Is my testing work defensible? (Cem Kaner talks a lot about this.) Think of a court case. What would a jury think if you testified and described what you did as a tester and why. How did you determine priority? Why did you test some things and not test others? (100% complete testing is impossible, so you have to make decisions to optimize your work. Are those decisions well thought out, or more subconscious? What sorts of things might you be missing that you haven't thought of?)

Read the full article from here

Sunday, September 12, 2010

How to start multiple WSO2 Carbon server instances as windows services

We can start more than one WSO2 Carbon (WSO2 ESB, WSAS, G-reg, GS, Mashup, IS, BPS, BRS, BAM, DS) instance by updating the transport configuration given in mgt-transport.xml (or transports.xml in 2.X series) as explained in here.
Suppose, you need to start multiple carbon server instances as windows services instead of regular wso2server.sh executable.
Then, there is an additional setting which has to be configured.

  • Find wrapper.conf file inside CARBON_HOME/repository/conf

  • Locate "Wrapper Windows NT/2000/XP Service Properties" section

  • Update the Name of the service and Display name of the service in each instance as follows

# Name of the service

# Display name of the service
wrapper.ntservice.displayname=WSO2 Carbon