update statement problem for jdbc adapter

[Former Member]

Hi all

In the jdbc sender adapter, I configured as follows:

Query statement

select * from pickdiff where tid is null

Update statement

delete from pickdiff where tid is null

I got following error message:

Database-level error reported by JDBC driver while executing statement 'delete from pickdiff where tid is null'. The JDBC driver returned the following error message: 'java.sql.SQLException: [SQLServer 2000 Driver for JDBC][SQLServer]Die Unterabfrage gab mehr als einen Wert zurück. Das ist ungültig, wenn die Unterabfrage auf =, !=, <, <= , >, >= folgt oder als Ausdruck verwendet wird.'. For details, contact your database server vendor.

If I change the Update statement to

update pickdiff set tid = 'sapxi' where tid is null

Then everything is ok.

Doese any one have some idea about this problem?

Regards

Hui

[Former Member]

Hi,

The below statements are from SAP help...

http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/frameset.htm

*****************************************************************************************

The UPDATE statement must alter exactly those data records that have been selected by the SELECT statement. You can ensure this is the case by using an identical WHERE clause. (See Processing Parameters, SQL Statement for Query, and SQL Statement for Update below).

&#9679; Processing can only be performed correctly when the isolation level for transaction is set to repeatable_read or serializable.

SQL statement for query: SELECT * FROM table WHERE processed = 0;

SQL statement for update: UPDATE table SET processed = 1 WHERE processed = 0;

processed is the indicator in the database.

***********************************************************************************

please see if setting the isolation level would help....Also are you getting this error always or is it intermitent ?

Thanks,

Renjith

[Former Member]

Hi Hui,

instead of <i>select</i> take <i>select max</i>.

This makes sure that you only select one record.

Regards ;arop

[Former Member]

hi Mario,

that's not what I expected.

Tks

Regards,

Hui

[bhavesh_kantilal]

Looks like there is some constraint / authroization issue on the databse.

Can you try to use a DB client and then exectue the same Delete query using the same user id and see what the error is.

Also try contacting your DB vendor and chekcing if there is some constraint on Delete?

Regards

Bhavesh

[Former Member]

problem solved.

the reason is that there is a delete trigger for the table and there is a small bug in the tigger when the number of the deleted records is bigger than 1.

regards,

hui

[bhavesh_kantilal]

Hi,

><i>delete pickdiff where tid is null</i>

This is not a valid SQL statement. pickdiff is the name of the table. So basically what this SQL statement implies is, delete table pickdiff where the columns tid have value null.

The statement should be

Delete from pickdiff where tid is null

Regards

Bhavesh

[Former Member]

sorry,

it's a typing mistake, actually I did input the correct the statement like you said.