BPEL

Working with FileAdapter in BPEL – Part 3: Saving the file contents in the database

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:

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:

Imagem Blog UansCarvalho

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:

Imagem Blog UansCarvalho

Click on your project: InsertDBAdapter[1.0]

Imagem Blog UansCarvalho

Click Test

Imagem Blog UansCarvalho

In WSDL copy the path: http://localhost:10000/soa-infra/services/default/InsertDBAdapter/insertdbadapterbpel_ep?WSDL

Imagem Blog UansCarvalho

Paste now URL in your browser and right click View Page Source:

See below part of the WSDL

Imagem Blog UansCarvalho

Copy the contents and create a file in your BPEL with InsertDbAdapter.wsdl name and paste the content copied above.

Imagem Blog UansCarvalho

Should then be as structure below:

Imagem Blog UansCarvalho

Now copy the InsertDBAdapterBPELProcess.xsd this within your InsertDBAdapter Project into our FileAdapterBPEL project in XSD folder as follows:

Imagem Blog UansCarvalho

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

Imagem Blog UansCarvalho

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 —–

Imagem Blog UansCarvalho

Now turn our LerArquivoBPELProcess to our Web Service InsertDbAdapter and should be as follows:

Imagem Blog UansCarvalho

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:

Imagem Blog UansCarvalho

Open For Each and put the following:

  • Name: ForEachLinhas
  • Counter Name: ForEachContador

Imagem Blog UansCarvalho

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:

Imagem Blog UansCarvalho

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:

Imagem Blog UansCarvalho

Now into our For Each drag a Assing and rename it to IniciaVariaveisInvoke down and drag an Invoke and rename it to InvokeInsertDbAdapterBPEL

Imagem Blog UansCarvalho

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

Imagem Blog UansCarvalho

Now open our IniciaVariaveisInvoke and we will connect the output of the OutPut InvokeLerArquivo with the Input of our InsertDBAdapterBPELProcess

Imagem Blog UansCarvalho

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

Imagem Blog UansCarvalho

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

Imagem Blog UansCarvalho

 

Doubts? Leave your comment and download this Projet :-)

 

Hugs and until the Next

/:-D

 

 

Criei este Blog destinado a desenvolvedores e interessados na Tecnologia Oracle SOA Suite, minha idéia é que no futuro ele sirva de referência para Analistas e Desenvolvedores desde aprendizes a especialistas. Já existem alguns Posts interessantes que abordam um pouco do meu dia a dia, no passar do tempo irei alimentar este Blog com o máximo de informações possíveis, por isso peço por gentileza aos interessados e afins que caso tenham alguma dúvida, sugestão ou crítica que me digam para que eu possa da melhor forma suprir as expectativas e na medida do possível sanar todas as duvidas que vierem a surgir ou até mesmo criar novos Posts abordando algum tópico que venha a ser relevante para todos os interessados. Obrigado Uans Carvalho

Leave a Reply

Your email address will not be published. Required fields are marked *