Delete and Insert problem with JDBC Receiver

[Former Member]

HI Experts,

I need to delete the table (all records) first then need to insert records in the Sql Database.

after ececuting the Interface table is deleted and only first record is inserted in the database. I want to insert all the records which are from the outbound payload.

I am using 2 statements 1 is for DELETE and 2nd is INSERT

MT_IB

_STATEMENTDELETE

__EMP_QUALIFICATION

___ACTION(delete)

Table

____access (didnt mapped in the mapping)

Xyz

Mno

_STATEMENTINSERT

__EMP_QUALIFICATION

___ACTION(insert)

Table

____access (Mapped)

Xyz

Mno

I didnt found and errors in the comm channel, while checking the paylod in the comm channel i see only 1 record is inserted.

key tag mandatory is disabled in the comm channel.

is there any thing wrong in my structure?

Thanks in Advance

Vankadoath

[Former Member · Accepted solution]

>

> HI Experts,
> I  need to delete the table (all records)  first then need to insert records in the Sql Database.
> after ececuting the Interface table is deleted and only first record is inserted in the database. I want to insert all the records which are from the outbound payload.
> 
> I am using 2 statements 1 is for DELETE and 2nd is INSERT
> MT_IB
> _STATEMENTDELETE
> __EMP_QUALIFICATION
> ___ACTION(delete)
>        Table
> ____access (didnt mapped in the mapping)
>           Xyz
>           Mno
> _STATEMENTINSERT
> __EMP_QUALIFICATION
> ___ACTION(insert)
>        Table
> ____access (Mapped)
>           Xyz
>           Mno
> 
> I didnt found and errors in the comm channel, while checking the paylod in the comm channel i see only 1 record is inserted.
> key tag mandatory is disabled in the comm channel.
> is there any thing wrong in my structure?
> Thanks in Advance
> Vankadoath

I think that the error is that you have to pass in insert structure, one raw at time.

[Former Member · Accepted solution]

Thanks for the rpl,

how to pass one row at a time in the Insert structure? could you pls elaborate this.

i will get n number of records in one payload from the R3.

Thanks!

[Former Member · Accepted solution]

Do one thing...

call stored procedure and in that stored procedure first delete all the records from the table n then instert all the records.

I cam across the same scenario and it is done by the stored procedure.

chirag

[Former Member · Accepted solution]

HI Chirag,

Thans for the reply I am not aware of storedprocedure. can you pls guide me the process and how to build the structure(stored procedure) for this requirement and mapping details.

Thanks in Adv..

[Former Member · Accepted solution]

hi,

u can refer this help for stored procedure structure.

http://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/frameset.htm

/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

chirag

[Former Member · Accepted solution]

>

> Do one thing...
> 
> call stored procedure and in that stored procedure first delete all the records from the table n then instert all the records.
> 
> I cam across the same scenario and it is done by the stored procedure.
> 
> chirag

Usually i prefer SP for jdbc adapter (unfortunately jdbc statement have some restriction)...

For Stored Procedure, refer to this:

http://wiki.sdn.sap.com/wiki/display/XI/Advantages-DisadvantagesofStoredProcedureinJDBCScenario

/people/luis.melgar/blog/2008/05/13/synchronous-soap-to-jdbc--end-to-end-walkthrough

For SP:

http://download.oracle.com/docs/html/B16022_01/ch3.htm#i1017636

[Former Member]

Hi,

please set your occurence of 'access' is 0 to unbounded.

for ex:

Statement_delete

dbTableName

action attribute

table

access

Statement_Insert

dbTableName

action attribute

table

access 0 to unbounded

Thanks,

Sateesh