JDBC Adapter: DELETE and INSERT in Mapping

[Former Member]

Hi,

I have a JMS to JDBC interface. While this is straightforward enough, there is a requirement to clear the contents of the table before carrying out the insert

What I've tried is:

<MT_Message>
   <Statement>
        <TableClear>
             <action> .....attribute mapped to SQL_DML
             <access> ....element mapped to DELETE FROM table
        <TableInsert>
              <action> .....attribute mapped INSERT constant
              <table> ......element mapped to Table name
              <access> ...allthe fields in the table

The DELETE statement works and the Insert statement work but they won't work together. Is this possible at all? What other options are there in this case?

[Former Member · Accepted solution]

Damien,

Try 2 different Statement level tags one each for Delete and Insert. The other easy option would be calling a stored procedure which does the task.

Regards,

Jai Shankar

[Former Member · Accepted solution]

Jai Shankar's suggestion worked. Thanks a million. 10 points on the way.

I forgot to thank you Mudit for your suggestion too.

[Former Member · Accepted solution]

>>Jai Shankar's suggestion worked. Thanks a million. 10 points on the way

Am really glad to hear that.

BTW, did you opt for a stored proc or 2 different stmt tags?

Regards,

Jai Shankar

[Former Member · Accepted solution]

I went for two different Statement tags so the structure of the Message Type is:

<MT_Message>
  <StatementDelete>
         <TableClear>
             <action> ....attribute mapped to SQL_DML
             <access> ...element mapped to DELETE FROM Table
  <StatementInsert>
         <TableInsert>
              <action> ....attribute mapped to INSERT constant
              <table>  ....element mapped to table name
              <access> ...all the columns in my table

[Former Member · Accepted solution]

Thanks for the update

Regards,

Jai Shankar

[Former Member]

Hi;

If i am not wront then you want the delete and the insert to take place in a single transaction . If some error occours while insert then delete also gets rolled back.

this will be done if you have

<Delete1>

<dbTableName action=”DELETE”>

<Insert1>

<dbTableName action=”INSERT”>

Mudit