Skip to end of metadata
Go to start of metadata

File to JDBC:
This scenario deals with reading a XML file from shared location using file adapter and inserting the records to SQL Server database.

Integration Repository Configurations
Note:

This scenario assumes that basic settings such as Software Component and Namespaces are already created.

Data Type:

1.    Expand the Integration Objects and go to the Data Types.

a.    Create  Data type name as XITraining_Out_XX (Group ID)

                                                            i.     GroupID

                                                                1.    Occurrence: 0..1

                                                                2.    Type: String

                                                           ii.     Text

                                                                 1.    Occurrence: 0..1

                                                                 2.    Type: String

2.    Create the data type for inbound interface.

a.    XITraining_JDBC_In_00

                                                              i.     Insert

1.     XITraining (this is table name which we are going to reference).

a.     Action (This is attribute category node which is attached to XITraining node.)

                                           i.     Type: String

                                         ii.     Category: Attribute

                                         iii.     Occurrence: Required

b.   Access

                                               i.     GroupID:

                                                               1.     Type: String

                                                                2.    Occurrence: 1

                                                 ii.     Rec_Date

                                                             1.     Type: String

                                                           2.    Occurrence: 1

Note: Please create your structure strictly according to following structure otherwise JDBC adapter will throw unexpected errors during run time. JDBC only supports communication in certain format according to the scenario. We are preparing scenario for Asynchronous communication and we are going to insert the records to the SQL Table. After creating the structure it should look like following screen.

3.    Save the newly created Data type.

Message Type:

Go to Message Type Option and right make sure that you already have a message type with name XITraining_Out_XX.

  1. Create one more message type XITraining_JDBC_In_XX using Data type XITraining_JDBC_In_XX using the F4 help.
  2. Save newly created message type.

Message Interface

  1. Go to the Message Interface option make sure that you already have a message interface with name XITraining_Asynch_Out_XX
  2. Create one more Message Interface XITraining_JDBC_Asynch_In_XX.
  3. Select mode as Asynchronous and direction as Inbound for second interface.
  4. Save the interface definition.

Message Mapping:

  1. Go to Mapping Objects and go to Message Mapping section. Right click and create a new mapping
  2. Use name as XITraining_Out_XX_To_XITraining_JDBC_In_XX.
  3. Select XITraining_Out_XX as source message type.
  4. Select XITraining_JDBC_In_XX as target message type.
  5. Map GroupID to GroupID and Text to Text.
  6. From standard function selection option, select "Date" which will display all the date related standard functions. Double click the "Date" from target message and use "current Date" function for mapping to Date field from target message (If you are not able to locate this function, please ask instructor and they will help you out)
    7. Follow same procedure above to go to default values option. This will display multiple options for defining defaults during mapping. Click first option which will display a CONSTANT block in the mapping editor. Double click it to change the default value to "INSERT" and map it to the OPTION field from target structure.

8. Save the Message mapping.
After completing the mapping your mapping should look like
Interface Mapping:

  1. Go to Mapping Objects and create a new Interface Mapping.
  2. Use XITraining_Asynch_Out_XX_To_XITraining_JDBC_Asynch_In_XX as Interface mapping name.
  3. Select Source interface as XITraining_Asynch_Out_XX and Target interface as XITraining_JDBC_Asynch_In_XX
  4. Click "Read Interfaces"
  5. Select your message mapping (XITraining_Out_XX_To_XITraining_JDBC_In_XX)
  6. Save the Interface mapping.

Note: Please activate all the changes. Try to activate all the created or changed objects in one go. If you are having trouble in activating the objects try activating it one by one. While activating individual objects make sure that you are following the sequence Fault Message Types, Software Component (if changed) Data Type, Message Type, Message Interface, Message mapping and Interface Mapping. If not activated, these objects will not be visible in directory while doing the configuration.
Integration Directory changes

Login to Integration Directory.

Check if we have Business System XXXXXX available in the system (Ask instructors if you don't have the business system available).

  1. Make sure that you have you business scenario in the tool. Locate XITraining_XX scenario and check all your objects created in scenario exist there.
  1. Go to the "Service without Party" and locate BS_FILE_SERVER business system. Right click on this and assign this business system to your scenario.
  1. Go to your scenario and go the "Service without Party" make sure that communication channel File_Sender_XITraining_XX exists under the BS_FILE_SERVER with following options.
    1. Communication Channel 1: File_Sender_XITraining_XX

                 i.     Adapter Type: File

                 ii.     Direction: Sender

                 iii.     File Type: Binary

                 iv.     QoS: Exactly Once

                 v.     Processing Mode: Delete. (Please don't select TEST mode)

                vi.     Polling Interval: 10 Secs

                 vii.     File Path: file path

                 viii.     File Name: XITraining_File_Sender_XX.xml all other settings should be left as default value proposed by tools.
                 ix.      # Go to the Business XXXXXX and create a new communication channel with following properties:

    1. Name: JDBC_Receiver_LocalSystem_XX
    2. Give all details here.
    3. Make sure that you are giving correct values in JDBC driver related information. We recommend that you copy paste these values from this document instead of typing manually to avoid the typing errors.

Protocol: JDBC 2.0

JDBC Driver: com.microsoft.jdbc.sqlserver.SQLServerDriver

Connection: jdbc:microsoft:sqlserver://XXXXXX:1433;DatabaseName=XI

User Name:

Password:

Receiver Determination

  1. Go to the Receiver Determination and make sure that you already have a receiver system existing from scenario 1. You will see 2 options on this screen one for selecting the receivers which should be in the middle of the screen and second one configuring the receivers which should be at the bottom of screen.

    Add another line in the receiver selection screen. Select XXXXXX as second receiver. Save the changes. After saving you will see newly added service in "configure Receiver" option. Drop this new service in this option to see the values below it. Ideally nothing should be defined under XXXXXX service but if you get some option below, consult your instructor for more clarification.
                                          # Right click under interface determination option and select the "Create New Specific" option. This should bring a new screen where you can select the inbound interface.

  1. Click on F4 help for selecting the inbound interface. This list will display all the interfaces related XXXXXX business system. Try to locate your interface XITraining_JDBC_Asynch_In_XX. If you are not able to locate your interface then click "ALL" radio button option from the top menu which will display the entire inbound interface created in the integration repository. Now you should be able to locate your interface.
  1. Click to select the corresponding interface mapping. Ideally this should just display one interface determination.

               # Go back to the original screen and refresh it.

  1. Create the receiver agreement by selecting "JDBC_Receiver_LocalSystem_XX" as the communication channel.
  2. Check that we already have a sender agreement with name "File_Sender_XITraining_XX" as the sender communication channel.

Now that all the design and configuration is done we are ready to test the interface. Drop the file in the source directory, as per the polling interval mentioned in the sender file adapter, the file will be deleted from the source directory and the records will get updated in the database directory as this is a file to JDBC scenario.