Tags
Let us now make the recording of the content of our file quoted in Part 2 in our database, remembering that for all projects I develop here in the Blog I use the tables existing one we install Oracle XE.
We’ll be recording the arquivo.txt content in our DEPARTMENT table that is already automatically created when we install our Bank.
See the links below to check the other parts of our project:
- Working with FileAdapter in BPEL – Part 1: Reading the file from directory
- Working with FileAdapter in BPEL – Part 2: Reading the file content
- Working with FileAdapter in BPEL – Part 3: Saving the file contents in the database
- Working with FileAdapter in BPEL – Part 4: Renaming or Moving a File
- Working with FileAdapter in BPEL – Part 5: Deleting a file
For this part of the project we need to use the DBAdapter to the Insert the information in the database, so I’ll use our project has created here on the Blog which is InsertDBAdapter.
Then import this project so that it is as follows:
Now in our FileAdapterBPEL project we need to call our InsertDBAdapter Service and for this we need to import into our design the WSDL and XSD InsertDBAdapter
So do Deploy Location to Your JDeveloper InsertDBAdapter this project:
In your browser go to http://localhost:10000/em
Note: The port 10000 is the number that chose to create my Domain BPEL
Login:
Click on your project: InsertDBAdapter[1.0]
Click Test
In WSDL copy the path: http://localhost:10000/soa-infra/services/default/InsertDBAdapter/insertdbadapterbpel_ep?WSDL
Paste now URL in your browser and right click View Page Source:
See below part of the WSDL
Copy the contents and create a file in your BPEL with InsertDbAdapter.wsdl name and paste the content copied above.
Should then be as structure below:
Now copy the InsertDBAdapterBPELProcess.xsd this within your InsertDBAdapter Project into our FileAdapterBPEL project in XSD folder as follows:
Now open your InsertDbAdapter.wsdl file of your project FileAdapterBPEL
See the line where the import of InsertDBAdapterBPELProcess.xsd that looks like this:
<import namespace="http://xmlns.oracle.com/UansBlogApplication/InsertDBAdapter/InsertDBAdapterBPELProcess" schemaLocation="http://localhost:10000/soa-infra/services/default/InsertDBAdapter/insertdbadapterbpel_ep?XSD=xsd/InsertDBAdapterBPELProcess.xsd"/>
Change only the schemaLocation to look like this:
<import namespace="http://xmlns.oracle.com/UansBlogApplication/InsertDBAdapter/InsertDBAdapterBPELProcess" schemaLocation="xsd/InsertDBAdapterBPELProcess.xsd"/>
Okay, this has solved the problem of our WSDL
Now go back to your composite.xml and drag the Web Service component for External References
Place the following settings:
- Name: InsertDbAdapter
- WSDL URL: Look for the WSDL InsertDbAdapter.wsdl we copy into our FileAdapterBPEL design as previous steps.
- Port Type: InsertDBAdapterBPELProcess
- Callback Port Type: —- No Callback —–
Now turn our LerArquivoBPELProcess to our Web Service InsertDbAdapter and should be as follows:
Now open our dadosArquivo.xsd file and we rename some fields to become clear in representing our table DEPARTMENT of our database XE where the data should be recorded which are within our arquivo.txt
Is as follows:
xsd:element name="Arquivo"> <xsd:complexType> <xsd:sequence> <xsd:element name="Arquivo" minOccurs="1" maxOccurs="unbounded"> <xsd:complexType> <xsd:sequence> <xsd:element name="header" type="xsd:string" nxsd:style="fixedLength" nxsd:length="5" /> <xsd:element name="detail" type="xsd:string" nxsd:style="fixedLength" nxsd:length="5" /> <xsd:element name="detail" type="xsd:string" nxsd:style="fixedLength" nxsd:length="9" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>
Change only the names, header, detail and the second detail to look like this:
xsd:element name="Arquivo"> <xsd:complexType> <xsd:sequence> <xsd:element name="Arquivo" minOccurs="1" maxOccurs="unbounded"> <xsd:complexType> <xsd:sequence> <xsd:element name="numeroDepartamento" type="xsd:string" nxsd:style="fixedLength" nxsd:length="5" /> <xsd:element name="nomeDepartamento" type="xsd:string" nxsd:style="fixedLength" nxsd:length="5" /> <xsd:element name="localDepartamento" type="xsd:string" nxsd:style="fixedLength" nxsd:length="9" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>
Now open our LerArquivoBPELProcess.bpel
Inside the Loop (LendoArquivo) below the Empty DadosProcessados box drag a For Each component will need it for our iterarmos the file contents lines.
Rename our For Each to ForEachLinhas and should look like this:
Open For Each and put the following:
- Name: ForEachLinhas
- Counter Name: ForEachContador
Click now in the Counter Values tab, and then:
- Start Value in Expression put the value 1
- Final Value in Expression click next to the icon that looks like a calculator and put the following expression:
ora:countNodes('InvokeLerArquivo_SynchRead_OutputVariable','body','ns4:Arquivo')
Note: See I’m using the function: ora:countNodes() to iterarmos our archive element that is our list as defined in our XSD dadosArquivo.xsd
Then first insert the function: ora:countNodes() and inside the parentheses of the function press the Control key + Space on your keyboard and select the option InvokeLerArquivo_SynchRead_OutputVariable now put a comma, and then again press Control + Space choice body and replace a comma, and press Control + Space and select the option ns4:arquivo and now put this last in single quotes in the case ‘ns4:arquivo’
See how it looks:
See above that the names were just after a comma and are enclosed in single quotes, it is very important or occur subsequent errors in this function
Then later we will have the following screen:
Now into our For Each drag a Assing and rename it to IniciaVariaveisInvoke down and drag an Invoke and rename it to InvokeInsertDbAdapterBPEL
Call our InvokeInsertDbAdapterBPEL for InsertDbAdapter what is in our Partner Links
In Input click the + sign to define our Input variable and click OK then
Now open our IniciaVariaveisInvoke and we will connect the output of the OutPut InvokeLerArquivo with the Input of our InsertDBAdapterBPELProcess
On this screen in From the left-hand double-click the line where we turn on the numeroDepartamento
He will be with the following Expression:
$InvokeLerArquivo_SynchRead_OutputVariable.body/ns4:Arquivo/ns4:numeroDepartamento
Then change to
number($InvokeLerArquivo_SynchRead_OutputVariable.body/ns4:Arquivo[$ForEachContador]/ns4:numeroDepartamento)
So that you understand:
I used the function number() to convert the numeroDepartamento to a numeric type as the XSD dadosArquivo.xsd it is String and the Database is a NUMBER
I used the name [$ForEachContador] after file because it is our list and will be what will repeat, so repeat each of our For Each iterate it will also lines our file
Also do the same procedure placing [$ForEachContador] for Expressions in the case it looks like this:
$InvokeLerArquivo_SynchRead_OutputVariable.body/ns4:Arquivo[$ForEachContador]/ns4:nomeDepartamento
$InvokeLerArquivo_SynchRead_OutputVariable.body/ns4:Arquivo[$ForEachContador]/ns4:localDepartamento
We will now test our process:
Make deploy locally of your project remembering to point to our file FileAdapterBPEL_cfgplan.xml
Let’s do the test to make sure everything worked perfectly.
Go in the directory /home/uans/Arquivos (or wherever you set the path as your Config Plan) and open the arquivo.txt
Inside it put the following content:
1011 SIST SAOPAULO 1012 SIST BRASILIA 1013 SIST PENAPOLI
Note: Remember that there can be no spaces at the end of the names to avoid problems
Now to make sure that your data has been successfully inserted, see in your data bank content we put in arquivo.txt above was inserted into the table correctly
Note: Remember that our process is checking our portfolio where this arquivo.txt of 5 in 5 seconds
Doubts? Leave your comment and download this Projet 
Hugs and until the Next
/:-D