axapta table insert / update

Question

hi folks,

and once again an axapta-question ( running on ax 2009 and sql-server 2008 r2 ): which is exactly the point of time, when inserted or updated datasets are stored in the regarding database?

the aim is to call a stored procedure on the sql-server which transfers data from the ax-tables ( eg inventtable ) to another ( not generated with axapta ) table. executing the stored procedure via odbc from axapta on one of the table-methods ( even after super() call ) triggers the stored procedure, but the data which was just added or modified in ax isn't found while selecting via smss ( select * from dbo.inventtable ).

the only place i know yet where the data is already stored in db is on the methods on the datasource on the regarding form, but this would be quiet ugly, since the data could be edited via n forms from ax.

so is there a way to put the call on the table instead on the forms' datasources?

thanks for hints in advance!

Answer 1

Putting the call after the super() call in the insert() or update() table method is the correct place to do that, however unless you do an uncommitted read the select statement in your stored procedure won't see the data until after the transaction in AX is committed.

Could you use the ODBC connection from X++ to write to the external table directly instead of indirectly via a stored procedure?

Answer 2

The AX data is "stored" in the database at the point of doInsert()/doUpdate() or super() calls in insert()/update() methods.

However, as Jay mentioned, the records will not be visible to other transactions (unless you explicitly allow dirty/uncommitted selects). So it may not be visible to your stored procedure.

I would not recommend calling stored procedures in insert()/update() anyway as this has performance implications, and you are now depending on yet another database being alive!

The way to go:

1. log insert/update in a separate table for that purpose (consider using standard database logging).
2. From the other database regularly monitor the log for new records (say every 15 seconds).
3. Do your insert/update in the other database based on a join of the log and the AX table.

Log table layout (one of millions):

• LogType - 1=insert, 2=update, 3=delete
• LogStatus - 0=not transferred, 1=under transfer, 2=transferred, 3=error???
• RefRecId - RecId of AX record
• RefTableId - TableId of AX table (if you need to log more than one table)
• RefCompanyId - Company of AX record (maybe the table is shared virtually)

Recommendations:

1. Use RecId as the join key and remember to enable RecId index on the AX table.
2. Remember to select on DATAAREAID (must be spelled this way) as well.
3. Update the LogStatus to 1 before the join and to 2 after the join and update.