Relationship Between Table I/O and JDE Database APIs

Purpose of Document

This document explains the relationship between Table I/O which can be implemented using the Event Rule editor on Named Event Rule design, Form Design Aid, Report Design Aid and Table Design Aid.

Information on the JDEBase APIs can be found on the following documents:


EnterpriseOne 8.98 APIs
  > Application Program Interface
     > JDEBase
        . Initialization
        . Table View
        . Request Settings
        . Data Manipulation

The most common Request settings can be used:

 

Application Programming Interface
The EnterpriseOne published Application Programming Interface (API) set is a collection of functions or routines that perform predefined tasks. The API set allows third party applications to interface with EnterpriseOne applications. The published API set makes it easy for you to develop solutions for customers without learning the complexities of EnterpriseOne.



This document is intended for the EnterpriseOne Developer who will be making use of Table I/O and the JDEBase APIs. 

Simplified Relationship Between Table I/O and APIs

 

Table I/ODatabase APIExample
Table.Open JDB_OpenTable()
  • Application Program Interface > JDEBase > Table View > JDB_Open Table
  • JDEDB_RESULT JDB_OpenTable(HUSER hUser, NID szTable, ID idIndex, NID *lpColSelect, unsigned short nNumCols, char * szOverrideDS, HREQUEST * hRequest);
  • Refer to JDB_OpenTable() Section from <Document 1533578.1> E1: BSFN: How to Implement SELECT DISTINCT, SELECT ALL and function COUNT(*) through JDE APIs
Table.Insert JDB_InsertTable()
  • Application Program Interface > JDEBase > Data Manipulation > JDB_InsertTable
  • JDEDB_RESULT JDB_InsertTable (HREQUEST hRequest, NID szTable, ID idInstance, void * lpStruct);
  • sql> INSERT INTO CRPDTA.F0101 (ABAN8, ABALKY, ABTAX, ABALPH, ...) VALUES (852963.000000,'SDONGMYE',' ',' ',' ',...)
Table.Update JDB_UpdateTable()
JDB_UpdateTableExtended()
  • Application Program Interface > JDEBase > Data Manipulation > JDB_UpdateTableColsExtended
  • JDEDB_RESULT PASCAL _export JDB_UpdateTableColsExtended(HREQUEST hRequest, NID szTable, ID idInstance, ID idIndex, void * lpKeyStruct, short nNumKeys, void * lpStruct, NID * pID, short nNum, JDB_API_OPTION nUpdateOption);
  • sql> UPDATE CRPDTA.F0101 SET ABALPH='TO BE DELETED' WHERE ( ABAN8 = 852963.000000 )
  • Update specific columns assigned only
Table.Update
(option) Check for Record Change		 JDB_UpdateTable()
JDB_UpdateTableExtended()
  • Application Program Interface > JDEBase > Data Manipulation > JDB_UpdateTableColsExtended
  • JDEDB_RESULT PASCAL _export JDB_UpdateTableColsExtended(HREQUEST hRequest, NID szTable, ID idInstance, ID idIndex, void * lpKeyStruct, short nNumKeys, void * lpStruct, NID * pID, short nNum, JDB_API_OPTION nUpdateOption);
  • sq1> UPDATE CRPDTA.F0101 SET ABAN8=852963.000000,ABALKY='SDONGMYE',ABTAX=' ',ABALPH='TO BE DELETED',ABDC=' ',... WHERE ( ABAN8 = 852963.000000 )
  • Update all the columns
Table.Delete JDB_SelectKeyed()
JDB_DeleteTable()
  • Application Program Interface > JDEBase > Data Manipulation > JDB_DeleteTable
  • JDBDB_RESULT JDB_DeleteTable(HREQUEST hRequest, NID szTable, ID idInstance, ID idIndex, void*lpKeyStruct, short nNumKeys);
  • sql> DELETE FROM CRPDTA.F0101 WHERE ( ABAN8 = 852963.000000 )
Table.Select JDB_SetSelection()
  • Application Program Interface > JDEBase > Request Settings > JDB_SetSelection
  • JDEDB_RESULT JDB_SetSelection(HREQUEST hRequest, LPSELECT lpSelect, unsigned short nNum, JDEDB_SET nSet);
  • JDB_SetSelection enables users to define any type of selection on a table associated with a specific initialized request handle. This API should be called prior to performing JDB_Select. This API is equivalent to a where clause in an SQL statement.
Table.Fetch Next JDB_SelectKeyed()
JDB_Fetch()
  • Application Program Interface > JDEBase > Data Manipulation > JDB_SelectKeyed
  • JDEDB_RESULT JDB_SelectKeyed(HREQUEST hRequest, ID idIndex, void * lpKey, short nNumKeys);
  • Application Program Interface > JDEBase > Data Manipulation > JDB_Fetch
  • JDEDB_RESULT JDB_Fetch(HREQUEST hRequest, void * lpValue, int nLock);
  • JDB_SelectKeyed performs a select on a table using the identified index and any custom selection and sequencing. Sending a zero in the number of keys to be used will cause the index to be ignored; however, if custom selection was identified it will always be used in this API. This only selects records matching the index (if used) exactly.
  • sql> SELECT * FROM CRPDTA.F0101 WHERE ( ABAN8 = 852963.000000 ) ORDER BY ABAN8 ASC
Table.Fetch Single JDB_FetchKeyed()
  • Application Program Interface > JDEBase > Data Manipulation > JDB_FetchKeyed
  • JDB_FetchKeyed performs a select as well as fetch results. This API attempts to use the cache when fetching on business views. This should be used when you do not expect multiple records to be returned as a result of your query.
  • JDEDB_RESULT JDB_FetchKeyed(HREQUEST hRequest, ID idIndex, void * lpKeyStruct, short nNumKeys, void * lpValue, int nLock);
  • sql> SELECT * FROM CRPDTA.F0101 WHERE ( ABAN8 = 852963.000000 )
Table.Select All JDB_SelectAll()
  • Application Program Interface > JDEBase > Data Manipulation > JDB_SelectAll
  • JDEDB_RESULT JDB_SelectAll(HREQUEST hRequest);
  • JDB_SelectAll performs a select on a table without a WHERE clause. This causes all records to be selected.
  • sql> SELECT * FROM CRPDTA.F0101
Table.Close JDB_CloseTable()
  • Application Program Interface > JDEBase > Table View > JDB_CloseTable
  • JDEDB_RESULT JDB_CloseTable(HREQUEST hRequest);


In case looping has to be implemented (either for aggregation or for additional sub-routine), example of internal BSFN IXT4111Z1_GetF0911JELN() in XT4111Z1.c,

Example 1 - Looping routine

    a. Table I/O in Event Rule

F0911.Select
   VA evt_szCompanyKey_Input = TK Document Company
   VA evt_szDocumentType_Input = TK Document Type
   VA evt_mnDocVoucherInvoiceE_Input = TK Document (Voucher, Invoice, etc.)
   VA evt_szNameAlphaExpl_Input = TK Name - Alpha Explanation
F0911.Fetch Next
   VA evt_mnJEntryLineNo_output <- TK Journal Entry Line Number
While SV File_IO_Status is equal to CO SUCCESS
 
  /* Additional routine if needed */

   F0911.Fetch Next
     VA evt_mnJEntryLineNo_output <- TK Journal Entry Line Number
End While

      b. Database APIs

void IXT4111Z1_GetF0911JELN(LPBHVRCOM lpBhvrCom,
                            LPVOID lpVoid,
                            HUSER hUser,
                            HCACHE hCacheGL,
                            HCURSOR hCursorGL,
                            LPF41UI001 lpdsF41UI001,
                            LPDSDXT4111Z1E lpDS,
                            LPMATH_NUMERIC lpmnSummarizeGL)
{
/************************************************************************
* Variable declarations
************************************************************************/
ID JDBReturn = JDEDB_FAILED ;
NID szTableID = NID_F0911 ;
ID IndexID = ID_F0911_LC_DCT_EXA__DOC_KCO;
HREQUEST hRequest = (HREQUEST) NULL ;
int nRecordFound = 0 ;

/************************************************************************
* Declare structures
************************************************************************/
F0911 dsF0911 = {0} ;
KEY17_F0911 dsF0911Key17 = {0} ;

/************************************************************************
* Main Processing
************************************************************************/
JDBReturn = JDB_OpenTable(hUser, szTableID, IndexID, 0, (ushort) 0,
                         (JCHAR *) _J('\0'), &hRequest);

   if (JDBReturn == JDEDB_PASSED)
   {
     MathCopy(&dsF0911Key17.gldoc, &lpDS->mnDocumentNumber);
     jdeStrcpy((JCHAR *)(dsF0911Key17.gldct), (const JCHAR *)(lpdsF41UI001->itdct));
     jdeStrcpy((JCHAR *)(dsF0911Key17.glkco), (const JCHAR *)(lpdsF41UI001->itkco));
     jdeStrcpy((JCHAR *)(dsF0911Key17.glexa), (const JCHAR *)(lpdsF41UI001->ittrex));

     JDB_ClearSelection(hRequest) ;

     JDBReturn = JDB_SelectKeyed(hRequest, (ID) 0,
                              (void *) &dsF0911Key17,
                              (short) 4) ;

     if (JDBReturn == JDEDB_PASSED)
     {
        JDBReturn = JDB_Fetch(hRequest, (void *) &dsF0911, (int) 0);

        while (JDBReturn == JDEDB_PASSED)
       {
         nRecordFound = IXT4111Z1_FindGLCache(lpBhvrCom,lpVoid,hCacheGL,
                                                &dsF0911, lpmnSummarizeGL) ;

         JDBReturn = JDB_Fetch(hRequest, (void *) &dsF0911, (int) 0);
       }
     }
   }

   if (hRequest)
   {
     JDB_CloseTable(hRequest) ;
   }
} /* IXT4111Z1_GetF0911JELN */




Note: