Quick Tip: Accessing Third Party Table Buffers using anonymous tables

  • Article

David Meego - Click for blog homepageI have been spending a lot of time recently working on consulting engagement in Malaysia. This project involves a large amount of custom Dexterity code, some of which involves accessing tables in other third party product's dictionaries.

In the past, I have covered Cross Dictionary Dexterity Development techniques and the usual method I recommended for accessing data from third party tables is to use the execute() function to execute code in the third party product to set key fields in a table buffer, get the table record and then return the required data. 

Below is an example of that technique to retrieve data from Fixed Assets: 

 

MBS_FA_General_Information_MSTR_ByIndex Function

function returns integer OUT_Error;
in long IN_Asset_Index;
out string OUT_Asset_ID;
out integer OUT_Asset_ID_Suffix;
out string OUT_Asset_Description;

local text code;
local string compiler_error;

pragma(disable warning LiteralStringUsed);
if MBS_FA_Installed() then

 {Build the pass-through sanScript code.}
clear code;
code = code + "in long IN_Asset_Index;";
code = code + "out string OUT_Asset_ID;";
code = code + "out integer OUT_Asset_ID_Suffix;";
code = code + "out string OUT_Asset_Description;";
code = code + "out integer OUT_Error;";

 code = code + "clear table FA_General_Information_MSTR;";
code = code + "'Asset Index' of table FA_General_Information_MSTR = IN_Asset_Index;";
code = code + "get table FA_General_Information_MSTR by number 1;";
code = code + "if err() = OKAY then";
code = code + " OUT_Asset_ID = 'Asset ID' of table FA_General_Information_MSTR;";
code = code + " OUT_Asset_ID_Suffix = 'Asset ID Suffix' of table FA_General_Information_MSTR;";
code = code + " OUT_Asset_Description = 'Asset Description' of table FA_General_Information_MSTR;";
code = code + "else";
code = code + " clear OUT_Asset_ID;";
code = code + " clear OUT_Asset_ID_Suffix;";
code = code + " clear OUT_Asset_Description;";
code = code + "end if;";
code = code + "OUT_Error = err();";

 

 {Execute the code.}
 if execute(MBS_FA_PROD_ID, code, compiler_error, IN_Asset_Index, OUT_Asset_ID, OUT_Asset_ID_Suffix, OUT_Asset_Description, OUT_Error) <> 0 then
  {A compiler error occurred. Display the error.}
  warning compiler_error;
 end if;
 
else
 clear OUT_Asset_ID;
 clear OUT_Asset_ID_Suffix;
 clear OUT_Asset_Description;
 OUT_Error = -1;
end if;
pragma(enable warning LiteralStringUsed);

 

 

The method above is great for getting a single record (1 to 1 relationship), but how can you move through a range of records in a third party table (1 to many relationship)?

Here are some methods I have used previously for handling moving through a range of records:

  1. Using similar code to the example above, you can pass in the parameters to set the range and the previous values for the key fields (blank will get first). Then issue a get next table command and return the key fields and any other additional data needed. If the EOF (End of File) is reached then clear the table buffer so that the returned parameters are blank. This code can be called once to get the first record and then from inside a while loop to get the next record. Each time we are passing back the parameters for the range and the previous records key fields.  This method is good for reading values, but not so useful if you need to manipulate/save data or access every field in the table.
     
  2. Create a global procedure in your dictionary which has the parameters for the fields you need from the third party table. Then use an execute() function to create the range on the third party table, get the first record and then loop through the table using a while loop with a get next table statement. Inside the while loop use a call with name <procedure> in dictionary <id> command to call back to your dictionary. This code will then loop through the table and call back into your code for each record.
     
  3. Not Recommended: Before we had the execute() command, you could create a duplicate table definition in my dictionary which could access the physical table at the database level. This technique has issues if the original table structure gets changed. It does not work for temporary tables and will bypass Dexterity table triggers (if any) on the original table.

 

So, now I want to introduce a new method that I worked out that allows full access to the third party table almost as though it was actually a table in your dictionary. The concept is execute() some code in the third party dictionary to pass back the table buffer you want as an anonymous table. It requires a procedure in your code which can be called from the execute() statement (similar to method 2 above).

  

Execute Code Example

local integer l_key;

local text code;
local string compiler_error;

l_key = 1;

pragma(disable warning LiteralStringUsed);

if MBS_FA_Installed() then
{Build the pass-through sanScript code.}
clear code;
code = code + "in integer IN_Key;";
code = code + "local anonymous table Master_Table;";
code = code + "open table Master_Table with name technicalname(table FA_General_Information_MSTR);";

 code = code + "call with name " + QUOTE + technicalname(script MBS_FA_General_Information_MSTR_Callback) + QUOTE+ " in dictionary " + str(Runtime_GetCurrentProductID())
+ ", table Master_Table, IN_Key; ";

 {Execute the code.}
if execute(MBS_FA_PROD_ID, code, compiler_error, l_key) <> 0 then
{A compiler error occurred. Display the error.}
warning compiler_error;
end if;
end if;

pragma(enable warning LiteralStringUsed);

 

 

MBS_FA_General_Information_MSTR_Callback Procedure

inout anonymous table FA_General_Information_MSTR;
in integer IN_Key;

get first table FA_General_Information_MSTR by number IN_Key;

warning column("'Asset ID'") of table FA_General_Information_MSTR;

 

 

Once you have the anonymous table in your code you can perform any action you like on the table. The only exception is that you should use the column() function to refer to the columns or fields. If you require other parameters to be passed, you can add them to your callback procedure and then pass them via the execute() statement, such as the IN_Key parameter in the example above.

Note: This technique will not work for TEMP tables as it will just create a new instance of the temporary table with zero records.  Also, don't expect the Dexterity Script Log to be able to identify the anonymous table passed to the callback script... it will probably show in the logs as [Not Found]

In our project we used a cross dictionary trigger on a Fixed Assets procedure to capture one table buffer that was passed as a parameter. We then used a variation of the above technique to pass the captured table buffer as an anonymous table parameter through to the execute() statement, which in turn passed it back to our callback procedure as a second anonymous table parameter along with the additional table buffer from the execute() statement itself.

I hope you find this technique useful.  

David

14-Feb-2013: Added Notes with additional information.