Práce s container sloupci v T-SQL

Někdy je nutné přistupovat ke container datům v AX z uložené procedury přimo v T-SQL. Bohužel, není možné pro tento přístup využít Business Connector, proto se musíme spokojit pouze s COM connectorem. Z tohoto důvodu nelze tento přistup využít v AX 2009, protože ten již COM connector nepodporuje.

Jak tedy přistoupit ke container datům v T-SQL? Použijte následující kód jako příklad:

 USE DynamicsAX40_1;
GO

DECLARE @_hResult int;
DECLARE @_ax int;
DECLARE @_axRecord int;
DECLARE @_recFound bit;

-- Create an instance of AxaptaCOMConnector.Axapta3 class
EXEC @_hResult = sp_OACreate 'AxaptaCOMConnector.Axapta3', @_ax OUTPUT;
IF @_hResult <> 0
  GOTO handleAxError;
 
-- Log on to AX
EXEC @_hResult = sp_OAMethod @_ax, 'LogonAs', NULL, 'userName', 'domain', 'proxyAccountName', 'proxyAccountDomain', 'proxyAccountPassword', 'company', 'language', 'objectServer', 'configuration';
IF @_hResult <> 0
  GOTO handleAxError;

-- Create instance of IAxaptaRecord
EXEC @_hResult = sp_OAMethod @_ax, 'CreateRecord', @_axRecord OUTPUT, 'SalesTableDelete';
IF @_hResult <> 0
  GOTO handleAxError;

-- Execute statement on Ax Business Logic
EXEC @_hResult = sp_OAMethod @_axRecord, 'ExecuteStmt', NULL, 'SELECT * FROM %1';
IF @_hResult <> 0
   GOTO handleAxError;

-- Read value of property Found to check if there are some records returned
EXEC @_hResult = sp_OAGetProperty @_axRecord, 'Found', @_recFound OUTPUT;
IF @_hResult <> 0
    GOTO handleAxRecordError;

-- Iterate through the records in the table to get detailed information
WHILE @_recFound = 1
BEGIN
  DECLARE @_recLabel varchar(200);
    
    EXEC @_hResult = sp_OAGetProperty @_axRecord, 'Caption', @_recLabel OUTPUT;
 IF @_hResult <> 0
     GOTO handleAxRecordError;
   
    PRINT @_recLabel;
 
    -- Get the container - BEGIN
  
    DECLARE @_container int;
  DECLARE @_containerRecord int;
    DECLARE @_containerRecordCaption varchar(200);
  
    EXEC @_hResult = sp_OAGetProperty @_axRecord, 'Field', @_container OUTPUT, 'SalesTable';
  IF @_hResult <> 0
     GOTO handleAxRecordError;
   
    EXEC @_hResult = sp_OAMethod @_container, 'Peek', @_containerRecord OUTPUT, '1';
  IF @_hResult <> 0
     EXEC sp_OAGetErrorInfo @_container;
  
    EXEC @_hResult = sp_OAGetProperty @_containerRecord, 'Caption', @_containerRecordCaption OUTPUT;
    IF @_hResult <> 0
     EXEC sp_OAGetErrorInfo @_containerRecord;
    ELSE
     PRINT N'SalesTable column inhalt caption: ' + @_containerRecordCaption;
        
    -- Clean up
   EXEC sp_OADestroy @_container;
   EXEC sp_OADestroy @_containerRecord;
     
    -- Get the container - END
    
    EXEC @_hResult = sp_OAMethod @_axRecord, 'Next';
   IF @_hResult <> 0
     GOTO handleAxRecordError;
   
    EXEC @_hResult = sp_OAGetProperty @_axRecord, 'Found', @_recFound OUTPUT;
   IF @_hResult <> 0
     GOTO handleAxRecordError;
END;

GOTO quit;

handleAxError:
EXEC sp_OAGetErrorInfo @_ax;
GOTO quit;

handleAxRecordError:
EXEC sp_OAGetErrorInfo @_axRecord;
GOTO quit;

quit:
-- Log off from Ax
EXEC sp_OAMeThod @_ax, 'Logoff';

-- Don't forget to release all objects created
EXEC sp_OADestroy @_axRecord;
EXEC sp_OADestroy @_ax;
GO

Martin F