[Q] Does jdb_SetSelection work with jdb_Delete?

[jolly]

Otherwise, it is not possible to do something like "DELETE FROM table WHERE column < value".

A work around is to fetch the keys from the table for that condition then delete the keys, but if there are a lot of rows then this is horrendously slow and inefficient.

Comments?

Cheeres,
JohnO

 

[DBohner-(db)]

Hi JohnO!

There is a way to do it - but you need to understand the ramifications of selecting the wrong keys.

You can dynamically create the SQL statement within an application or UBE - then submit it as a string to a submitted command to your ES... Much like you would submit a dynamic runube or CLP call from inside an application or ube.

If the keys are incorrect - ya better have good backups...

Not for weak at heart.

Daniel

 

[jolly]

BZZZT!

Just tried it out: Did a jdb_OpenTable with no index specified, jdb_SetSelection on the request handle, then a jdb_DeleteTable without specifying any index and it produced the required SQL!

Cheers,
JohnO

 

[DBohner-(db)]

JohnO,

Would it be too much to have you quote some of the code?... or... are we gonna have to pay you for that.

My suggestion was a work-around - yours seems to be the real deal!

db

 

[jolly]

Heh - no payment required; JDEList is for FREE distibution of knowledge!

Snippet follows. PLEASE NOTE: I cannot see anywhere in the JDE docs that clearly states that this should work, so this may not always work in Service Packs post SP21 where I tested it. Don't sue me if in some future Sp this causes a table to be cleared of ALL records!

code follows. Unfortunately the indentation disappears in the cut&paste.
------------------------------
B580002.c:
#include <jde.h>

#define b580002_c


/*****************************************************************************
* Source File: b580002
*
* Description: Delete F573412 Records Source File
*
* History:
* Date Programmer SAR# - Description
* ---------- ---------- -------------------------------------------
* Author 16/02/04 OLIVERJ Unknown - Created
*
* Copyright (c) J.D. Edwards World Source Company, 1996
*
* This unpublished material is proprietary to J.D. Edwards World Source Company.
* All rights reserved. The methods and techniques described herein are
* considered trade secrets and/or confidential. Reproduction or
* distribution, in whole or in part, is forbidden except by express
* written permission of J.D. Edwards World Source Company.
****************************************************************************/
/**************************************************************************
* Notes:
*
**************************************************************************/

#include <b580002.h>


/**************************************************************************
* Business Function: DeleteFromF573412ByDateUpdated
*
* Description: Delete from From F573412 By Date Updated
*
* Parameters:
* LPBHVRCOM lpBhvrCom Business Function Communications
* LPVOID lpVoid Void Parameter - DO NOT USE!
* LPDSD580002A lpDS Parameter Data Structure Pointer
*
*************************************************************************/

JDEBFRTN (ID) JDEBFWINAPI DeleteFromF573412ByDateUpdated (LPBHVRCOM lpBhvrCom, LPVOID lpVoid, LPDSD580002A lpDS)
{
/************************************************************************
* Variable declarations
************************************************************************/

HUSER hUser = (HUSER) NULL;
HREQUEST hRequestF573413 = (HREQUEST) NULL;

ID idJDBReturn = (ID) JDEDB_PASSED;

/************************************************************************
* Declare structures
************************************************************************/
SELECTSTRUCT dsSelect[1];

/************************************************************************
* Declare pointers
************************************************************************/

/************************************************************************
* Check for NULL pointers
************************************************************************/
if ((lpBhvrCom == (LPBHVRCOM) NULL) ||
(lpVoid == (LPVOID) NULL) ||
(lpDS == (LPDSD580002A) NULL))
{
jdeErrorSet (lpBhvrCom, lpVoid, (ID) 0, "4363", (LPVOID) NULL);
return ER_ERROR;
}

/************************************************************************
* Set pointers
************************************************************************/

/************************************************************************
* Main Processing
************************************************************************/
idJDBReturn = JDB_InitBhvr(lpBhvrCom,&hUser,(char *)NULL,
JDEDB_COMMIT_AUTO);

if( idJDBReturn == JDEDB_FAILED)
{
jdeSetGBRError( lpBhvrCom, lpVoid, (ID)0, "3143");
return ER_ERROR;
}

idJDBReturn = JDB_OpenTable(hUser, NID_F573413, 0, NULL,
(ushort)(0), (char *) NULL,
&hRequestF573413);

if( idJDBReturn == JDEDB_FAILED)
{
jdeSetGBRError( lpBhvrCom, lpVoid, (ID)0, "3143");
return ER_ERROR;
}

jdeNIDcpy(dsSelect[0].Item1.szDict, NID_UPMJ);
jdeNIDcpy(dsSelect[0].Item1.szTable, NID_F573413);
dsSelect[0].lpValue = &lpDS->jdDateUpdated;
dsSelect[0].nValues = 1;
dsSelect[0].nAndOr = JDEDB_ANDOR_AND;
dsSelect[0].nCmp = JDEDB_CMP_LT;

idJDBReturn = JDB_SetSelection(hRequestF573413,
dsSelect,
(ushort)1,
JDEDB_SET_REPLACE);

if( idJDBReturn == JDEDB_PASSED )
{
idJDBReturn = JDB_DeleteTable(hRequestF573413, NID_F573413,
(ID)0, (ID)NULL,
(void *)NULL,
(short) 0);
}

JDB_CloseTable(hRequestF573413);
JDB_FreeBhvr(hUser);


/************************************************************************
* Function Clean Up
************************************************************************/

return (ER_SUCCESS);
}

/* Internal function comment block */
/**************************************************************************
* Function: Ixxxxxxx_a // Replace "xxxxxxx" with source file number
* // and "a" with the function name
* Notes:
*
* Returns:
*
* Parameters:
**************************************************************************/

 

[jolly]

.. PeopleSoft got back to me on this one: While they have no official statement that this is supported they admitted that the technique is often used within their code so they don't expect it to stop working anytime soon.

Cheers,
JohnO