jdb_deletetable api and table joins

Does anybody know if it is possible to use the jde_deletetable api to delete records from two joined tables? Looking at the api documentation it mentions joins in the idInstance parameter but I can’t find any examples of how to use it this way.

I think you can do a jdb_OpenTable with no index specified and then a jdb_SetSelection on the request handle and then finally a jdb_DeleteTable to delete the selected records from a single table but I’m not sure about joined tables.

What I’m trying to achieve is to delete records from two tables with a certain data selection, but unfortunately the second table doesn’t contain the field I want to select on. Currently I’m having to read the first table in a loop for the data selection and then delete the matching records inside the loop from the second table, but this is not very efficient and both tables could potentially have a large number of records.

I’ve also tried to delete the records using a joined business view but this didn’t work as the generated SQL was incorrect (I think this is a limitation of the toolset).

I'm currently trying this on 9.0 TR 9.1.5.3

Any help would be greatly appreciated.

Thanks,

Neal

I have never used it but there is an API called JDB_DeleteView

So maybe ????
JDB_OpenViewExtended
JDB_SetSelectionX
JDB_DeleteView
JDB_CloseView

If you try this, post back and let us know what it does. I am curious.

Thanks! I'll give it a go.

Hi Neal,

How are things?

Looking your API, you need a table index etc. The one Brian has mentioned looks much better for what you are trying to do.
I've posted both below, just in case you don't have info on them both


JDB_DeleteTable deletes a row from a specified table using the passed index.
Syntax

JDBDB_RESULT JDB_DeleteTable(HREQUEST hRequest, NID szTable, ID idInstance, ID idIndex, void*lpKeyStruct, short nNumKeys);

hRequest
Valid request handle
SzTable
Table ID to delete
idInstance
Instance ID of table. Used if joins, otherwise pass zero
IdIndex
Index ID to use for delete or NULL. If NULL, then primary index will be used
lpKeyStruct
Pointer key structure or NULL. If no structure is to be used, then pass NULL
nNumKeys
Number of keys to be used. if ON, then the request is valid for caching
Return Value

Return Value
Description
JDEDB_PASSED
Return value if this succeeds
JDEDB_FAILED
Return value if this fails

Example
/* Declare variables associated with JDB_DeleteTable */
HREQUEST Request = NULL;
JDEDB_Result rcode;
NID szTable = NID_F0101;
ID idIndex = ID_F0101_Address;
Key1_F0101 dsKeySentStruct;
int nNumKeys =1;
ParseNumbericString(&dsKeySentStruct.aban8,_J("1001"));
rcode=JDB_DeleteTable(hRequest,sztable, 0,idIndex,&dsKeySentStruct, nNumKeys);
if (rcode !=JDEDB_PASSED)
{
jdePrintf(_J("JDB_DeleteTable failed\n"));
} /*END IF */


JDB_DeleteView deletes a record from table involved in a business view.
Syntax
JDBDB_RESULT JDB_DeleteView(HREQUEST hRequest, void*lpKey);
hRequest
Valid request handle
lpKey
Pointer key structure or NULL. If no structure is to be used, then pass NULL.
Return Value
Return Value
Description

JDEDB_PASSED
Return value if this succeeds
JDEDB_FAILED
Return value if this fails

Cheers John. Many thanks for that.