Selecting data using the Database Adapter (BPEL DbAdapter) Oracle SOA Suite 11g

Oracle Database Adapter enables Oracle SOA Suite to communicate with the database, including Oracle database servers, and any relational databases that follow the ANSI SQL standard and provide JDBC drivers, performing SELECT operations, UPDATE and INSERT using an adapter configuration wizard, where the inputs / outputs are XML, easily seen as input parameters and and results converted into XML.

In this article on the Database Adapter (DbAdapter) BPEL we select some data from a database table and show in the screen Enterprise Manager  (EM)

More specifically, we will spend a Identifier number of a customer to show us all the data from this customer in the employees table.

For our tests, we will create a project called SelecDBAdapter

Click File >> New

Will open a screen like below:

Then select Generic Project

Project Name SelecDBAdapter place and select the SOA Technologies Project name and click the small arrow> to the name go right and be as below:

Note: See the Directory that it will automatically fill in the name will be created where the project name you entered in the Project Name

In my case: /home/uans/jdeveloper/mywork/UansBlogApplication/SelecDBAdapter

Click Next and then:

Below click Finish leaving the Composite Template in Empty Composite

Now we create an empty project will create a BPEL

Now right click Componets > Insert > BPEL Process

Click BPEL 2.0 Specification

In place name SelecDBAdapterBPELProcess

In Template select Synchronous BPEL Process and click OK

Let selected option Expose as a service SOAP, as this will be responsible for creating a WSDL default which will change for our tests.

Ready, it should look like this:

We will now change our WSDL created for inserting our input parameters that will be used in our DBAdapter and inserting our output parameters to be displayed in the screen Enterprise Manager (EM)

To do this, click on our project created and xsd folder double-click the xsd that was automatically created SelecDBAdapterBPELProcess.xsd

 

Agora vamos alterar nossos parâmetros de entrada e saída.

Inside the tag <element name = “Process”> on input, change to idCliente and type change to integer, in the <element name = “processResponse”> in result, change and add the parameters as follows:

<element name=”idCliente” type=”integer”/>
<element name=”Nome” type=”string”/>
<element name=”Profissao” type=”string”/>
<element name=”Salario” type=”integer”/>
<element name=”NumeroDepartamento” type=”integer”/>

Here’s how it went:

Now let’s create our Database Adapter (DBAdapter)

In External References click the right button and select the Database Adapter option

On the next screen click Next

Service Name in place SelectEmployeeDBAdapter and click Next

On the next screen make sure that your Connection is correct in the case, pointing to the correct database, and JNDI Name is also with the correct name.

If you have not created yet, do the following:

Click the + icon next to the name of your Connection

On the next screen fill as the image below:

Note: In my case, I am pointing to the base of my Oracle XE, in your case you can put the data regarding the basis on which you will connect and then click Test Connection to make sure you have correctly entered the information from your bank Data and click OK

You should then look like this:

If you do not know the name that should be in JNDI Name, check my Post: “Adding a new database to DbAdapter the Console (Web) SOA Suite 11g“ for this post I describe how to set up a new base to DbAdapter (WEB), but if you still have doubt where is this name, enter the Web Console as follows:

In your browser go to http://localhost:10000/console

Note: The port 10000 is the number that I chose to create my Domain.

Login:

Click Deployments

Click DbAdapter

Click Configuration

Click Outbound Connection Pools and then click the  +  beside javax.resource.cci.ConnectionFactory

Below are my connections connections and there we have the I’m using in DBAdapter in the case: eis/DB/OracleXe and this will be the name of the JNDI name that we will use in our DBAdapter

Returning to our DBADapter, then we have the screen below, then click Next:

In the screenshot below, select Perform an Operation on a Table and select the Select option and click Next:

Now click Import Tables for caring about our table which we will do Select

On the next screen in place Name Filter %EMPLOYEE%

Note: I am using % so you can see that it is possible to use the percent sign to find the table if you do not know the exact name, and then click Query and below will be shown under Available at your table, but if one appears see the creation of your settings if your User has the appropriate permissions to access this table and/or the Owner is correct.

Now click on your table and click the arrow > to your table go to the right side as shown below and click OK.

Below you made the import correctly, now that the EMPLOYEE table appears on our screen, now click Next:

The next screen shows a relationship between the tables that the BPEL itself creates as the modeling of this table, do not touch anything and click Next

In the next step, the screen is shown in the data that will appear in the Select, but if it makes a connection with the tables as the modeling of the table.

Click Next to continue.

Below that he has done the SELECT for us and now we just need to put the parameter that we will go after the BPEL to select the data you want, then click Add…

In the box that opened place the IdCliente name and click OK.

Below that our parameter has appeared, now click Edit to add the parameter to the clause Where

On this screen click Add.. and see that right he will put to as follows:

Now in the Second Argument box is marked Literal, then change to Parameter as below, see it now appears the IdCliente that you had set as a parameter and then click OK.

It will then be as follows and click Next:

Note: See our parameter got the #idCliente symbol, that is, it needs this sign # to understand that it will in front of this symbol will be our input parameter.

Click Next again:

Now click Finish

Then we have the following:

Now place the mouse cursor over the BPEL, if the blue box ai means until an arrow orange, then hold the left mouse button and drag to the other small arrow of your DBAdapter:

Now double-click on your BPEL:

On the right side on the screen in BPEL Constructs click the Invoke component and drag to the center of your BPEL:

Drag the small arrow that appears next to your Invoke for your DBAdapter that will appear in Partner Links

After that will be shown the following screen:

Then the input tab click the green + sign to create the input variable in the event that will name your input

And see that it will create another canister with the variable name filled in the case Invoke1_SelectEmployeeDBAdapterSelect_InputVariable, then click OK

Now in OutPut tab again click the green + sign to create the output variable in case their OutPut which will be namedInvoke1_SelectEmployeeDBAdapterSelect_OutputVariable

Now place the following in Name: SelectEmployee_Invoke and click OK

We will then have the following screen after we clicked OK.

Drag now one Assign to above your Invoke and one for below its invoke as follows:

The first Assing1 rename it to Variaveis_Entrada and the second to Variaveis_Saida, you just double-click on the name Assign1 and later in Assign2 name

Now double-click on the Variaveis_Entrada box, click the + sign inputVariable to get client:IdCliente, and drag to the ns2: IdCliente that will be in Invoke1_SelectEmployeeDBAdapterSelect_InputVariable the right side as shown below and click OK.

Note: This Invoke1_SelectEmployeeDBAdapterSelect_InputVariable is the same variable we set for the Invoke we created above.

Now double-click on the Variaveis_Saída box, which is below its SelectEmployee_Invoke

The left hand side of the screen, click the + sign Invoke1_SelectEmployeeDBAdapterSelect_OutputVariable to get ns2:empno

Do the same on the right in outputVariable to get client:IdCliente

Now do the ligament as below and click OK

Faça o deploy do seu projeto para testarmos:

Right-click on your project (SelectDBAdapter) click Deploy > SelectDBAdapter…

Click Next

Select the Overwrite any existing composites with the same revision ID and uncheck the option Keep running instances on after redeployment and click Next

Select the localhost option and click Next

Click Next again

Now click Finish

In its JDeveloper and below, click the Deployment tab and see if the message —- Deployment finished. —- If you see your deployment was executed successfully, if it does not check the steps above again to see if you forgot to do something.

Let’s do the test to make sure everything worked perfectly.

In your browser go to http://localhost:10000/em

Note: The port 10000 is the number that I chose to create my Domain.

Login:

Click on your project: SelectDBAdapter[1.0]

Click Test

Below input in 7839 put that in my case is a Customer ID that I have in my database and click Test Web Service

If no response appears as below, your project is successfully completed.

 

Doubts? Leave your comment and download this Projet 🙂

 

Hugs and to the Next

/:-D