Skip to Content
In PI 7.0 and XI 3.0, we have been able to import the metadata from RFCs and IDOCs from SAP. Many times, we wish we can also import metadata from SQL tables we wish to access also. This will eliminate a lot of typing and typo-mistakes.

This feature is now available with PI 7.1. The only requirement is that we have to have a JDBC receiver communication channel.

In this blog, I will discuss the steps to import metadata from SQL tables.

For demostration purposes, I have a table, “Table1”, in MS SQLServer. I will import the metadata as an external definiton into PI 7.1’s Enterprise Service Repository (ESR).

Below is the simple procedure:

  1. Create a JDBC Receiver Communication Channel.

    In the Integration Directory, created a JDBC receiver communication channel. The table name is “Table1” in the database “xirig” of MS SQL Server 2005.

    Using the MS SQL Server Management Studio, we see the table layout as follow:
    image

    The communication channel configuration is as follow.
    image

  2. Import the Metadata of “Table1”.

    In the ESR, create an External Definition in a namespace.

    Right-click on a namespace and select “New”, as below:
    image

    Select “External Definitions” and enter “Table1” (or any name) for the name. image

    With the create screen, select “dbtab” in the dropdown for “Category” and click on the wizard to import the external definition:
    image

    In the wizard, select the Communication Component and the Communication Channel from step 1, and click “Continue”:
    image

    Enter “Table1”, or use the “Display” button to see all the available tables and select a table. Click “Finish”:
    image

    We see the following when this is done.
    image

    When we tried to use it in a mapping, we will see the following:
    image

To report this post you need to login first.

41 Comments

You must be Logged on to comment or reply to a post.

  1. Bhavesh Kantilal
    Bill,
    Excellent feature!
    The way things are going ,is the plan from SAP to put XI consultants out of job by providing such interesting  features? 🙂
    Cant wait to figure 7.1 out!

    Regards
    Bhavesh

    (0) 
    1. Henrique Pinto
      On the contrary.

      With such changes, SAP expects XI consultants to focus on the important stuff instead of wasting time on small things. Hence, they can evolve into business process experts instead of just being technical consultants. 🙂

      Best regards,
      Henrique.

      (0) 
  2. Bhavesh Kantilal
    Hi Bill,
    The Schema genereated seems to be the schema of Sender JDBC adapters.

    Does the feature support Receiver Schema’s? Considering that we can have multiple operations on the Receiver JDBC adapters I know this might not be a very feasible solution, but I am still curious.

    Regards
    Bhavesh

    (0) 
    1. William Li Post author
      Hi,

      Yes, the imported schema does match the result of a select statement of the JDBC sender comm chan.

      Right now, there is no option to generate a schema that can be used by the JDBC receiver comm chan.  This might be a little difficult due to the different actions, and columns to be included in the where clause.

      Regards,
      Bill

      (0) 
  3. Hello Bill,

    Is the feature available for all the databases supported by JDBC Adapter??

    EX: Oracle / Sysbase / DB2

    Thanks,
    Naveen

    (0) 
  4. Sheetal Deshmukh
    During the migration process problems have been reported for this blog. The blog content may look corrupt due to not supported HTML code on this platform. Please adjust the blog content manually before moving it to an official community.
    (0) 
  5. Aamir Suhail
    Thanx for coming up with series.
    I just have a small questions regarding the scope of Receiver JDBC adapter you have configured.
    I m assuming that the use of this Receiver JDBC adapter is only to import the Table structure,am i right?
    If i m doing a JDBC to IDOC scenario,then i need to configure 1 sender JDBC adapter(to pick values from databse),1 receiver JDBC adapter(to import Table structure as an external definition) and 1 receiver IDOC adapter,is this correct?
    Thanx
    Aamir
    (0) 
      1. Aamir Suhail
        Thanx Bill
        Yeah ofcourse we can use this receiver JDBC communication channel in other scenarios,i just wanted to make sure its scope in JDBC to IDOC kind of scenarios.
        Thanx
        Aamir
        (0) 
  6. Tommy Lee
    Hi,

    I am connecting to MySQL database instead.

    When I am importing the Table Definition in Enterprise Service Builder, I am getting this error.

    Serialized cause: com.sap.aii.mapping.lookup.LookupException: Exception during processing the payload. Error when calling an adapter by using the communication channel CC_JDBCLookUp (Party: , Service: PT1_BC, Object ID: b088d4ef332833a89ee816faa88b5089) XI AF API call failed. Module exception: (No information available). Cause Exception: ‘Error processing request in sax parser: Error when executing statement for table/stored proc. ‘metadataRequest’ (structure ‘statement’): com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”lookuptable” WHERE 1<0′ at line 1’.

    Any idea?

    (0) 
    1. William Li Post author
      Hi,
      Does the JDBC receiver comm chan work with other scenarios?  If so, then I suspect there is a bug.

      It would probably be best to create a customer msg and work with support directly to resolve the issue.

      Regards,
      Bill

      (0) 
  7. Hello Bill,

    I’m attempting to extract metadata from our SAP database, which is ORACLE, and I’m able to successfully connect to it, when I try to get the SAP table structure I get the following error:

    Cause Exception: ‘Error processing request in sax parser: Error when executing statement for table/stored proc. ‘metadataRequest’ (structure ‘statement’): java.sql.SQLException: ORA-00942: table or view does not exist

    I’ve asked our DBA for assistance and he setup a diagnostic trace in the database, however the JDBC call from PI doesn’t trigger the trace.

    I hope you can shed some light on this issue. 

    Thank you,
    Gene.

    (0) 
  8. Madina Shiela Mendoza
    Hi!
      We are currently migrating objects from XI to PI for an application that uses the JDBC adapter (oracle driver). We are getting the following issue in the communication channel monitoring
    Message processing failed. Cause: com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. ‘S_BONUSPRM’ (structure ‘Statement’): java.sql.SQLException: ORA-01843: not a valid month

    Error processing request in sax parser: Error when executing statement for table/stored proc. ‘S_BONUSPRM’ (structure ‘Statement’): java.sql.SQLException: ORA-01843: not a valid month

    What could be the root cause? Thanks.

    (0) 
    1. William Li Post author
      Hi,
      You did not specifiy the upgraded PI version, I have to assume it is PI 7.1x.

      In PI 7.1x, the sax parse is used.  The error is not an Oracle error, but is a date/time formatting error, which is detected by the sax parser.

      Check the format of ‘S_BONUSPRM’ to determine what is expected.  In some situations, this is caused by the difference between European and US’s handling of month-day.  US date is month before day, and European is day before month.

      Regards,
      Bill

      (0) 
        1. William Li Post author
          Once the msg has been mapped, as in this case, the saxparser is no longer involved.  This still appears to be a date format problem.

          Is the database still the same?  If so, then this sounds like an JDBC adapter problem.

          Can you examine the insert target message between XI and PI to see if there are any differences?

          Bill

          (0) 
          1. Madina Shiela Mendoza
            Hi Bill,
               Database is still the same. The insert target message between XI and PI are exactly the same. I ran it in XI, it was successful. In PI it is failing. What could be the JDBC Adapter problem? Could it be Oracle driver inconsistency between XI and PI? Thanks so much.
            Madz
            (0) 
                1. William Li Post author
                  For PI 7.0 and below, you have to use Visual Administrator.

                  For PI 7.1 and above, you have to use NWA.

                  If you do not have access to them, ask your basis person to find out.

                  (0) 
                  1. Madina Shiela Mendoza
                    According to infra team,they were the same but installed in the different libraries. The lib structure has been changed. According to him, normally the same adapter config should continue to work… It is just that they need to put the driver in a different library when deploying. Their deployment procedure changed.

                    Could this have affected how it functions overall?

                    (0) 
                    1. William Li Post author
                      The library used to deploy is different in 7.1.  What they said is correct, as long as the same version of driver is used.  What version of the Oracle driver is used?

                      Based on the error, it is date formatting.

                      I cannot tell you why it is different between XI and PI 7.1.

                      (0) 
                      1. Madina Shiela Mendoza
                        You are correct that this is indeed date formatting issue. I have corrected the issue. In XI, the date is being inserted as dd-MM-yyyy while in PI, it is being inserted as dd-MON-yyyy. Weird thing is that, in XI this was working before but in PI, Oracle wants a different date format.

                        Thanks for all the help and accommodating my questions!

                        (0) 
      1. SAP User
        I get the below error while importing the metadata from table in the external definition dbtab, I am using the receiver communication channel,the channel is working fine for file to jdbc,any help appreciated

        Error in Adapter engine CC
        Error processing request in sax parser: Error when executing statement for table/stored proc. ‘metadataRequest’ (structure ‘statement’): com.ibm.db2.jcc.b.SqlException: Invalid argument: unknown column name COLUMN_NAME

        Error in ED
        Error occurred while attempting to connect to database (LOOKUPEXCEPTION)

        Exception during processing the payload. Error when
        calling an adapter by using the communication channel
        CC_GeoRestrictions_JDBC_Receiver (Party: , Service:
        BC_GeoRestrictions_SAP_To_Legacy, Object ID: c0adebcbf7603e82a2b6a1b100139c8b)
        XI AF API call failed. Module exception: (No information
        available). Cause Exception: ‘Error processing request
        in sax parser: Error when executing statement for table/stored
        proc. ‘metadataRequest’ (structure ‘statement’): com.ibm.db2.jcc.b.SqlException:
        Invalid argument: unknown column name COLUMN_NAME’.
        com.sap.aii.mapping.lookup.LookupException: Error when
        calling an adapter by using the communication channel
        CC_GeoRestrictions_JDBC_Receiver (Party: , Service:
        BC_GeoRestrictions_SAP_To_Legacy, Object ID: c0adebcbf7603e82a2b6a1b100139c8b)
        XI AF API call failed. Module exception: (No information
        available). Cause Exception: ‘Error processing request
        in sax parser: Error when executing statement for table/stored
        proc. ‘metadataRequest’ (structure ‘statement’): com.ibm.db2.jcc.b.SqlException:
        Invalid argument: unknown column name COLUMN_NAME’.

        (0) 
  9. Rakesh Sharma
    Hi,

    I have a simple doubt, as we import RFC request and response messages into ESR, can’t we do the same for Database Stored Procedures and got the request and response msg structures…

    Thanks,
    Raki

    (0) 
  10. Roni Fabian
    Hi,

      followed this to import meta data from db2 but unable to import showing the error “table not found” if you go to the display option with out giving any input in the table field. 

    (0) 
    1. William Li Post author
      Hi,
      In most cases when this does not work is due to inconsistent JDBC drivers version when compared to the database version.

      Can you compare your DB2 database version and download the corresponding JDBC drivers and compare to what has been deployed on the PI system?

      Regards,
      Bill

      (0) 
      1. Sridevi Gopal
        Hi Bill,

        I have the same issue when i connect to DB2 database, i am able to see only the Base source file and not the table ?? is there any specific changes to be done on the DB2 end.

        Thankx,
        Shree

        (0) 
  11. Gagandeep Batra

    Hi Li,

    I have simple question ?

    where we can use the same structure and How?

    or it is for just reference?

    means I got the structure like that

    But in Mapping we need structure like that: so how can we directly use that structure?

    Regards

    GB

    (0) 
    1. William Li Post author

      Hi,

      What you need to do is to create a data type containing only the row elements.  Then, in any other data types where you want to use it, then you can use the dropdown for the element (e.g. access) to select it from the “Data Type”.

      Regards,

      William

      (0) 
  12. Sugata Bagchi

    Hi All,

    For MySQL server use the connection string in below pattern –

    jdbc:mysql://<DB Host>:<port>/<DBName>?sessionVariables=sql_Mode=ANSI

    (0) 

Leave a Reply