JDBC Receiver SQL_DML Delete first N records

[Former Member]

Hello, experts!

We need to delete N first records from a DBF file with the following columns CODE, NAME and some other attributes.

I use Receiver JDBC Adapter with additional DBF driver.

I tried to use action SQL_DML with the following access value:

DELETE FROM Table_name WHERE CODE IN (SELECT TOP 10 CODE FROM Table_name).

But it deletes only one record instead of deleting ten.

Could you help me to find out why is that so and how to delete first N records?

BR,

Vika

[stefan_grube]

> DELETE FROM Table_name WHERE CODE IN (SELECT TOP 10 CODE FROM Table_name).

> But it deletes only one record instead of deleting ten.

Have you tested this command directly on database with a database tool?

[Former Member]

Hello, Stefan!

Which database tool do you mean? I have just DBF file. To watch it's content I use MS Access.

I tried to perform query with action SQL_QUERY and access SELECT TOP 10 * FROM Table_name and it worked.

BR,

Vika

[stefan_grube]

A dbf "file" is in fact a dbase database.

http://en.wikipedia.org/wiki/DBase

Every databse has its own syntax, of course based on SQL.

Your SQL statement looks correct to me, and i am sure it would work for MS SQL,

but a should confirm with a database tool (I suppose there should be any) to see, if the issue is the SQl command itself, or the JDBC adapter.

When you can watch entries in this dbf file with MS Access, is it also possible to execute SQL statements?

[Former Member]

I tried and figured out, that it is possible to perform the request in MS Access.

But MS Access Query Constructor added * after DELETE, so in MS Access my query looks like DELETE * FROM Table_name WHERE CODE IN (SELECT TOP 10 CODE FROM Table_name).

I tried this query in PI, but got error in JDBC Communication Channel:

Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'Table_name' (structure 'STATEMENT'): java.sql.SQLException: [StelsDBF JDBC driver] Can't parse SQL query: [SQL Engine API] Incorrect syntax near the word 'DELETE' (position: 8). See realized SQL specification in the driver documentation. SQL query was: 'DELETE * FROM Table_name WHERE CODE IN (SELECT TOP 10 CODE FROM Table_name)'

So, it looks like my query is correct.

What can it be then?

BR,

Vika

[stefan_grube]

I am sorry, I have no experience with dbf files.

I use similar commands for MS SQL and it works well.

You should google for this issue. There should be a forum for dbase or dbf files.

[Former Member]

Thanks anyway.

BR,

Vika