How to use function query-database() to return data from the database in SOA Suite 11g

I created a new user in database

create user arpit identified by arpit;

then grant dba to arpit;

Now just log in to database and

I just created a simple table in database

create table customer(name varchar(20),empid varchar(20));

Then i inserted some data in to the table as can be seen




NOw log in to admin console

http://localhost:port/console and go to

Services--->Jdbc---->Data Sources and create a new data source





Provide some data source name as per your requirement



Provide database details

Test the configuration and target it to soa_server1 and say finish





So now you can see the new data source created in the list as shown





As per oracle document

the syntax for query -database is

oraext:query-database(sqlquery as string, rowset as boolean, row as boolean, data source as string)

and these are the arguments

Arguments:

sqlquery - The SQL query to perform.

rowset - Indicates if the rows should be enclosed in an element.

row - Indicates if each row should be enclosed in an element.

data source - Either a JDBC connect string (jdbc:oracle:thin:username/password@host:port:sid) or a JNDI name for the database.


Now i am creating just a simple bpel process that is a default asynchronous bpel process,i will just modify it for my use case.

I choose the default option for creating a bpel process and it fetches me following xsd file

<?xml version="1.0" encoding="UTF-8"?>
<schema attributeFormDefault="unqualified"
elementFormDefault="qualified"
targetNamespace="http://xmlns.oracle.com/arpit_jws/QueryDatabase/BPELProcess1"
xmlns="http://www.w3.org/2001/XMLSchema">
<element name="process">
<complexType>
<sequence>
<element name="input" type="string"/>
</sequence>
</complexType>
</element>
<element name="processResponse">
<complexType>
<sequence>
<element name="result" type="string"/>
</sequence>
</complexType>
</element>
</schema>

Now as you can see we have one input variable and one output variable but for our results to be stored we must have two elements in output and again since we don't know how many rows will be returned from sql query so we too have to design our xsd in according to that.


I would recommend to refer to this post

http://soa-bpel-esb.blogspot.com/2010/04/how-to-create-business-process-for.html

This was one of my best post for understanding namespace so if you are clear with this we will go ahead and design our xsd.

So based on that i have changed my xsd as below

<?xml version="1.0" encoding="UTF-8"?>
<schema attributeFormDefault="unqualified" elementFormDefault="qualified"
targetNamespace="http://xmlns.oracle.com/arpit_jws/QueryDatabase/BPELProcess1"
xmlns:arpit="http://xmlns.oracle.com/arpit_jws/QueryDatabase/BPELProcess1"
xmlns="http://www.w3.org/2001/XMLSchema">
<element name="process" type="string"/>
<element name="processResponse" type="arpit:productType"/>
<complexType name="productType">
<sequence>
<element name="listElement" type="arpit:listElementType"
maxOccurs="unbounded"/>
</sequence>
</complexType>
<complexType name="listElementType">
<sequence maxOccurs="unbounded">
<element name="itemID" type="string"/>
<element name="itemValue" type="string"/>
</sequence>
</complexType>
</schema>


DAts it once you have created your xsd you are done with most of the complexity,

Create a new string variable as shown below




NOw just drag and drop a assign activity in the bpel process and copy the input variable to the new string variable you have created.





NOw create one more copy operation and use the data base function to query the database.



this is the expression in my case as per my configuration

oraext:query-database(bpws:getVariableData('query'),true(),true(),'jdbc/DataSource1')

So now the process is complete now deploy it to the server.


Just to let you know what exactly output i am expecting.

I have created a database connection to the schema i have created and i will run an sql query

select * from customer it will fetch me the result of all the records in the table customer as can be seen



MY aim is to get the same output from my process.

So i will pass the same input to my bpel process




NOw you can check the flow of output to get the result as shown



Further you can use your own transformation to copy it to our own created variable .
Hope this would have given you some idea on how to work with query-database()