JDBC sender structure issue

[Former Member]

HI

I have JDBC to Proxy scenario. I have to pull data from two views and then post it to ECC system. They want to do this as single innterface,

Firtly when I used receiver communication channel and tried to pull the metadata to ESR, I am not able to do that. I think we can pull only metadat of JDBC tables to the PI system

Secondly. the structure of the tables are like this

Fistrt view

Table_XYZ

     Customer

     MobileNumber

Second view

Table_ABC

     EmpNumber

     Phone

     Address

     Region

How should I create the datat type for this? I mean sender data type. Can these two views be integrated in one interface?

Another point that I noted is this: Customer from first table and EmpNumber from secon table are going to the same field in ECC table. Both views are updated in the same ECC table

I created the Receiver data type for proxy like this:

DT_xyz

     Table_XYz

          Customer

          MobileNumber

     Table_ABC

          EmpNumber

          Phone

          Address

          Region

I gave both Table_XYZ and Table_ABC as unbounded. Is this structure correct?

Is it possible to do this as single interface? Is it possible to impport views into PO

[manoj_khavatkopp · Accepted solution]

Hi Midhun,

Your source data _ type structure is wrong it should be like this :

MT_Source

       Rows.... 0..n

            Field1

            Field2

            Fieldn

For receiving from two view their need to have any forign key/primary key relationship between those to view depending on this you can write a sql query in your sender jdbc channel.

Br,

Manoj

[Former Member · Accepted solution]

Hi

What about the receiver structure? Receiver is proxy. How should that strcuture be?

Regards

Harish

[manoj_khavatkopp · Accepted solution]

Midhun,

If Mapping is one to one you can use the same structure for Proxy too .

Br,

Manoj

[Former Member]

Query SQL Statement select * from Table_ABC, Table_XYS

UPDATE <TEST>

can I use these two select all data from both views?

[nitindeshpande]

Hello Midhun,

How can you write 2 select statement in a single JDBC sender channel? You need to join the 2 tables and then select the rows you need. Its not suggested to select all the rows, because tables may included so many unwanted rows.

Regards,

Nitin

[nikhil_bose]

+

Alternatively a view does this join in the DBMS (Sender) as well.

Regards,

Nikhil

[former_member182412]

Hi Midhun,

You have two options to do this.

• Write a join statement in PI sender JDBC channel from both the tables if the primary and foreign key relation exist, mention both tables fields in the sender structure.
• Write a stored procedure in database side to retrieve the data from two tables and call the stored procedure in sender JDBC channel.

Regards,

Praveen.

[Former Member]

HI Praveen

I am taking data from two views, should there be primary/foreign key relationship in both views? If it there in only one view, then can I take from that view at least

[nikhil_bose]

yes, if it is single view or stored procedure, at sender message type is like

MT_View

- Records (0...n)

-- column 1

-- column 2

....

Target structure is depends on how ECC wants the records. You can split them in different internal tables using mapping.

Regards,

Nikhil

[former_member437549]

Hi Midhun,

Souce Datatype should be like this:

Table_DT

    row (0...unbounded)

        Customer/EmpNumber

         Mobilenumber

         Phone

         Address

         region

and in Sender CC

Query SQL Statement:  Select a.Customer, a.MobileNumber, b.Phone, b.Address, b.region from Table_XYZ a, Table_ABC b where a.FLAG = '0' and a.Customer = b.EmpNumber.

The above Query will fetch data from two tables based on joint condition.

Regards,

Siva.