Purpose:
The purpose of this blog post is to demonstrate how can we write a tsv (tab delimited) file in X++
- The code actually exports system database log to a TSV file
- Uses standard SysDatabaseLogReport query
- Adds a range for Warehouse table
- Uses formatted datetime string for filename
Product:
Dynamics AX 2012
Code:
Class declaration.
class MAKSysDatabaseLogService
{
#File
#define.EmptyString('')
FileIOPermission IOPermission;
SysDataBaseLog sysDataBaseLog;
container cont;
TextIo file;
FieldName fieldNameText;
str oldValueText;
str newValueText;
}
Run method. The main method of the class.
public void run()
{
container tmp;
Query query;
QueryBuildDataSource qbds;
QueryRun queryRun;
List list;
ListEnumerator enumerator;
fieldId fieldId;
str fileName;
boolean written;
System.DateTime dateTime;
str dateTimeStr;
;
dateTime = System.DateTime::get_Now();
dateTimeStr = dateTime.ToString('yyyyMMddHHmmss');
query = new query(queryStr(SysDatabaseLogReport));
qbds = query.dataSourceTable(tableNum(SysDatabaseLog));
qbds.addRange(fieldNum(SysDatabaseLog, Table)).value("158"); // Table Warehouse
queryRun = new QueryRun(query);
try
{
fileName = strFmt("%1\\Databaselog_%2.csv", ICH_DatabaseLogSetup::find().FilePath, dateTimeStr);
IOPermission = new FileIOPermission(fileName, #IO_WRITE);
IOPermission.assert();
file = new TextIo(filename, #IO_WRITE);
file.outRecordDelimiter(#delimiterCRLF);
file.outFieldDelimiter("\t");
this.writeHeader();
while (queryRun.next())
{
sysDataBaseLog = queryRun.get(tablenum(SysDataBaseLog));
fieldNameText = #EmptyString;
newvalueText = #EmptyString;
oldValueText = #EmptyString;
written = false;
list = sysDataBaseLog.getDataAslist();
enumerator = list.getEnumerator();
while (enumerator.moveNext())
{
tmp = enumerator.current();
fieldId = conpeek(tmp, 1);
fieldNameText = fieldid2pname(sysDataBaseLog.Table, fieldId);
newValueText = sysDataBaseLog.contents2Str(conpeek(tmp, 2), fieldId);
oldValueText = sysDataBaseLog.contents2Str(conpeek(tmp, 3), fieldId);
this.writeLine();
written = true;
}
if (!written)
{
this.writeLine();
}
}
info(strFmt("%1", "Database log exported successfully."));
}
catch(Exception::Error)
{
error("ERROR");
}
CodeAccessPermission::revertAssert();
}
Writes header to the file.
private void writeHeader()
{
cont = connull();
cont = conins(cont, 1, "Table name");
cont = conins(cont, 2, "Description");
cont = conins(cont, 3, "Log type");
cont = conins(cont, 4, "Created by");
cont = conins(cont, 5, "Field name");
cont = conins(cont, 6, "Value");
cont = conins(cont, 7, "Previous value");
cont = conins(cont, 8, "Created date and time");
file.writeExp(cont);
}
Writes a line to the file.
private void writeLine()
{
cont = connull();
cont = conins(cont, 1, sysDataBaseLog.tableName());
cont = conins(cont, 2, sysDataBaseLog.Description);
cont = conins(cont, 3, sysDataBaseLog.LogType);
cont = conins(cont, 4, sysDataBaseLog.createdBy);
cont = conins(cont, 5, fieldNameText);
cont = conins(cont, 6, newValueText);
cont = conins(cont, 7, oldValueText);
cont = conins(cont, 8, sysDataBaseLog.createdDateTime);
file.writeExp(cont);
}
*This post is locked for comments