how to make jdbc adapter Synchronous in PI

[Former Member]

problem:

i have an scenario where I am sending data from jdbc adapter(oracle DB) to RFC ,now it is asynchronous,the problem is zhe sender adpter may updater more data than we want ,

my sender adapter :

the query sql ---- 'select * from ztable where flag = 0',

update sql ------''updater ztable set flag = 1 where flag = 0

reason:

firstly execute the query sql,at the same time new data with flag = 0 were putted in the table,but i dont get it,and then secondly,execute update sql,make its flag back 1,and I will never get it .

help me:

what can i do to solve this problem? make the interface synchronous ? .

thank you very much~

[iaki_vila]

Hi Sophia,

I think your problem is not to do your scenario synchronous, your problem is taken the same registers in the select and in the update. With Oracle i had the same problem the best solution that i found was to the use the clause rownum and like the UPDATE in Oracle doesn't have the rownum i did a stored procedure with the same SELECT and the UPDATE with that registers taken, if you don't have experience with oracle you can talk with your DB team or to try by yourself. Check this blog as well

Regards.

[Former Member]

thank you very much.

can you tell me how to create stored procedure and use it in pi?

[iaki_vila]

Hi Sophia,

To use a stored procedure in sender jdbc is easy, you have an example here:

Also, with Oracle database i got a correct UPDATE typing the next in the UPDATE SQL Statement  box:

DECLARE RESULT NUMBER; BEGIN RESULT := mySotoredProcedure(1); END;

The stored procedure has to be in the Database then you need PL/SQL knowledge (or someone in your team) to do the stored procedure with a third database management application like the TOAD.

Regasds.

[former_member186851]

If your using stored procedure you will get response for select queries no need to do any additional settings.

[Former Member]

thanks，I can use stored procedure select what I want,but I can't update this data,how can i do?

[justin_santhanam]

Hello Sophia,

The Stored Procedure you are writing should be able to handle updating the records that you have selected.

Thanks.

[Former Member]

my procedure is

create or replace procedure yshpro(VBELN in varchar2, details out   sys_refcursor) is begin   open details for      

     SELECT *       FROM WMS_ZLIKP     

                WHERE LIKP_VBELN = VBELN;

end yshpro;

package is :

create or replace package yshtest01 is 

   type yshtable is table of WMS_ZLIKP%ROWTYPE; 

   FUNCTION GET_WMS( VBELN  varchar2 ) RETURN yshtest01.yshtable pipelined;

end yshtest01;

create or replace package body yshtest01 as

  FUNCTION GET_WMS(VBELN varchar2) RETURN yshtest01.yshtable     pipelined IS  

  yshdetail WMS_ZLIKP%ROWTYPE;   

  Details  sys_refcursor;   

BEGIN   

   yshpro(VBELN, Details);   

  loop  FETCH Details    INTO yshdetail;         

  exit when Details%NOTFOUND;         

  /*sending values of the ref cursor row by to the PIPE */          

   pipe row(yshdetail);            

   end loop;   

  return;   

  END; 

END;

now , use  select * from table(yshtest01.get_wms('230000123')) ,I can get the data I need,but I cant update it. how to do ?

Thank you very much.

[former_member186851]

Hello Sophia,

Use Update query.

If it is not working tell me, I will try to give the exact code.

[former_member182412]

Hi Sophia,

Check the below question in JDBC FAQ SAP Note:

831162 - FAQ: XI 3.0 / PI 7.0 / PI 7.1 / PI 7.3 JDBC Adapter

Q: If I have the following configured in a JDBC Sender
Select Query:
SELECT column FROM TABLENAME WHERE FLAG = "TRUE"
Update Query:
UPDATE TABLENAME SET FLAG = "FALSE" WHERE FLAG = "TRUE"
How do I know that the JDBC adapter will not update newly added rows (rows that were added
between the time frame which the SELECT and UPDATE queries were executed) that were not read in
the initial SELECT query?
A: The SELECT and the UPDATE are run in the same DB transaction, i.e. both statements have the
same view on the database.
Make sure that both statements use the same WHERE clause. An additional requirement for the
correct operation of this scenario is the configuration of an appropriate transaction isolation
level on the database (i.e., repeatable_read or serializable). You might also consider using a
"SELECT FOR UPDATE" statement instead of a plain SELECT statement to ensure proper locking on the
database. "SELECT FOR UPDATE" is not supported in MS SQL database. In this case please make use
of an appropriate transaction isolation level on the database. For more details please contact
your DB vendors.

Regards,

Praveen.