Topic
  • 3 replies
  • Latest Post - ‏2013-04-29T05:52:03Z by Developer11
Developer11
Developer11
78 Posts

Pinned topic Query on SQL-Execute

‏2013-04-24T08:52:31Z |

Hi All,

Is it possible to exeucte a SQL statement by joining 2 tables from  2 different data Sources in DataPower?

Example:

Update Table1 set Column='A' from Table1 INNER JOIN Table2 on Table1.A = Table2.B

 

Table1 belongs to DataSource1

Table2 belongs to DataSource2

 

Please advise

 

Thanks

  • JoeMorganNTST
    JoeMorganNTST
    427 Posts

    Re: Query on SQL-Execute

    ‏2013-04-24T14:07:24Z  

    I'm going to say "I doubt it".  Can you just create a stored procedure on the DB for Table1 and call that stored proc?

  • smadan
    smadan
    1 Post

    Re: Query on SQL-Execute

    ‏2013-04-24T18:43:52Z  

    This operation is not supported in DataPower.

    However, a potential workaround to achieve what you are attempting may be to isolate/retrieve the rows ahead of time to particular values of "A" and "B" and then you could do the joining itself in XSLT. While this may not be the most efficient solution, it may potentially allow you to accomplish your task.

  • Developer11
    Developer11
    78 Posts

    Re: Query on SQL-Execute

    ‏2013-04-29T05:52:03Z  
    • smadan
    • ‏2013-04-24T18:43:52Z

    This operation is not supported in DataPower.

    However, a potential workaround to achieve what you are attempting may be to isolate/retrieve the rows ahead of time to particular values of "A" and "B" and then you could do the joining itself in XSLT. While this may not be the most efficient solution, it may potentially allow you to accomplish your task.

    Thanks for the responses.

     

    Currently the processing is done in 2 steps

    Step1 : Fetch the records from table1

    step 2: Update the records on table 2.

    This requires a for-each loop and as you might already know, for each update we have to call one "sql-execute" function, which is impacting the performance.

     

    I am trying to find a way to reduce the number of sql calls.