cancel
Showing results for 
Search instead for 
Did you mean: 

Sender jdbc adapter

Former Member
0 Kudos

   I have 1 query which is related to my requirement.

My scenario is     JDbc---->PI------->Jdbc .Here i want fetch data from database through the sender jdbc and insert values in receive jdbc.

Here my doubt was i want to fetch the data from more than a table and those tables not having any relationship(Primary key and  foreign key and rows data are separated ).then how can i call in single statement?

Querying two tables without any relation?

Thanks

Ramanjaneya Reddy

Accepted Solutions (0)

Answers (6)

Answers (6)

Former Member
0 Kudos

HI Harneesh,

     I have one quick question

you mean to say ,even my tables are Independence to each other , i can write stored procedure right?

can u plz  elabrate more on this?

give me some example on this ?

Former Member
0 Kudos

Yes, I guess the best way is to build up a Stored Procedure (where you select data from the different tables), and call the SP from PI with the EXECUTE command value (in the JDBC Structure)

  <StatementName5>

<storedProcedureName action=” EXECUTE”>

    <table>realStoredProcedureeName</table>

<param1 [isInput=”true”] [isOutput=true] type=SQLDatatype>val1</param1>

</storedProcedureName >

  </StatementName5>

former_member184720
Active Contributor
0 Kudos

Hi Rama - Yes you can write.

Not an expert but Just execute two select queries in your procedure something like below.

CREATE/ALTER PROCEDURE <<NAME>>

AS

BEGIN

    SELECT  * FROM <<table1>>;

    SELECT  * FROM <<table2>>;

END

else you can even define a temp table in your procedure with all the coloums in two tables and move the records into that. Later you can execute select * from temp table.

*you'll have null values in the resultset and you have to handle it in the mapping.

rajasekhar_reddy14
Active Contributor
0 Kudos

Hi Reddy,

Yes you can use stored procedure at receiver end but not at sender side because standard JDBC sender doesnot support SP calls, so you need find work around.

Thank you,

Raj

ambrish_mishra
Active Contributor
0 Kudos

Hi Raja & Reddy,

From PI 7.1 onwards, SP are supported in the sender JDBC side as well.

Having said that, you do need a foreigh key in order to fetch the data from the 2 tables through the stored procedure.

Hope it helps

Ambrish

Former Member
0 Kudos

Hi Raja!!

Actually, using a SP at sender JDBC side is supported.

Check note: 941317 - XI / PI JDBC Adapter: Known Problems / Incompatibilities

former_member184720
Active Contributor
0 Kudos

Hi Ambrish - Are you sure??

I don't think we need to have a relationship between the tables to extract the data using a stored procedure.

i think executing two select queries independently should extract all the information from two tables and if that doesn't work then the other option is to creating a temp table with all the columns from both the tables and executing a select query on that temp table will result all the rows from two tables.

@Rama : Did yo try that??

Regards,

Hareesh

ambrish_mishra
Active Contributor
0 Kudos

Hi Hareesh,

We do need a relationship between 2 tables to merge the data.

Ambrish

former_member184720
Active Contributor
0 Kudos

Sorry Ambrish.. don't have any data base to try but don't you think below is possible?

create a common table which has all the fields from both the tables.

insert the first table data into common table(give blank value for the second table fields)

insert the second table data into common table(give blank values for first table fields)

Regards,

Hareesh

ambrish_mishra
Active Contributor
0 Kudos

The table design you suggested does not have a key field and there is no relationship between the data. I am not sure if it matches Reddy's requirement. If the idea was to do it this way, why not send the data independently through 2 sender JDBCs as I initially suggested.

Cheers,

Ambrish

former_member184720
Active Contributor
0 Kudos

Agreed!! Don't know why do we even need PI to transfer the data between the tables as mentioned by Raja.(a simple stored procedure does that)

i was just trying to answer his question "you mean to say ,even my tables are Independence to each other , i can write stored procedure right?"

However letz wait Rama's comment..

Regards,

Hareesh

Former Member
0 Kudos

Hi GUYS THANKS  FOR QUICK REPLY.

AS-IS scenario:

Jdbc Sender (2 or more tables)-Every table have one select query( the bda guy executed those Queryies manually )  and those data there are enter in Receiver tables(Tb1,Tb2,tb3)

source tables structure's and table are diff and each table carry one select query here they want to remove the manual process they want to use PI interfaces here .

as per my knowledge  ihave take data:

i will careate datatype of sender like this format:

^rows

row1table1

row2oftable1

row1oftablw2

.....

Here my question  was, is there any option to combined(join) 2 or more select queries in a single??

if  not how can i achive this problem

former_member184720
Active Contributor
0 Kudos

Hi Rama -

How can we write a join without having any relationship between two tables? If there is such a way your DBA should've used it instead of executing the queries manually.

to state in other way, If you can extract all the information(from all the tables) using a single SQL query then you can use the same query in the JDBC sender channel which is not possible in your case.

as your tables are independent(there is no foreign key relationship) then you have to write an Stored proc to transfer data from one table to other. But here comes the question why we need PI to do that(as raja sekhar mentioned) as they can schedule that stored procedure to achieve that.

for some reasons, If you want to use PI then either executing the stored procedure/create separate channels for each table

Regards,

Hareesh

rajasekhar_reddy14
Active Contributor
0 Kudos

Why you want to use PI here? ask DB guy to write a proceded to pull data from different tables and update it new table.

Former Member
0 Kudos

Hi,

If a complex select statement is too difficult, you can try using the JDBCLookup function in the message mapping.  By using this, you can retrieve data from multiple tables regardless of their relation.  You will still need a primary JDBC sender channel as a trigger, and then in the message mapping, use the JDBCLookup to retrieve data from other tables.

To use this, you'll need to create a JDBC receiver with the parameters of the database.  After that, create an External Definition object with Category "dbtab" and Messages "From Table Definitions".  There will be a Communication Channel field where you can choose the JDBC receiver channel created and then the table that you'll want to access.  After creating the external definition object,  use the JDBCLookup function in Message Mapping (under Conversions category).  You'll need to select the external def object created from within this JDBCLookup function.  You'll then see 3 columns with only the 2nd column populated.  These are the fields available in the table (seeing this also means that you are able to successfully connect to the db).  Move the fields to the left (1st column) to make these the SELECT parameters and then move fields to the right (3rd column) for the output of the lookup. 

We've used this for several integrations and a watchout point is that this approach will add an extra failure point. If the lookup channel goes down, you will see a mapping error as the fields will not be produced (failed lookup).

I hope this helps.

ambrish_mishra
Active Contributor
0 Kudos

Hello,

You can derive data from 2 tables using a stored procedure but I am wondering how would a SQL developer write a query(SP) without foreign key relationship.

If you pick the data from different tables through multiple sender JDBC adapters and merge the messages in PI, you will need to correlate the messages and you will also need a linkage between messages.

Hope it helps.

Ambrish

baskar_gopalakrishnan2
Active Contributor
0 Kudos

Search online to find trick to select tables that has no primary key relationships.  I see some link

as below.

http://billfellows.blogspot.com/2011/04/find-tables-without-primary-key.html

http://datacharmer.blogspot.com/2011/09/finding-tables-without-primary-keys.html

You basically write the query in select field of the sender jdbc channel.