SQLConnection program in AX

server static void main(Args args)
{
Connection con = new Connection();
Statement stmt = con.createStatement();
ResultSet r;
str sql;
SqlStatementExecutePermission perm;
;

sql = strfmt('SELECT VALUE FROM SQLSYSTEMVARIABLES');

// Set code access permission to help protect the use of
// Statement.executeUpdate.
perm = new SqlStatementExecutePermission(sql);
perm.assert();

try
{
r = stmt.executeQuery(sql);
while (r.next())
{
info(Strfmt("%1", r.getString(1)));
}
}
catch (exception::Error)
{
Error("An error occured in the query.");
}
// Code access permission scope ends here.
CodeAccessPermission::revertAssert();
}

Writing values to CSV in AX

static void testWriteToCSV(Args _args)
{
#File
CommaTextIo commaTextIo;
FileIOPermission permission;
SalesTable sales;
str fileName = @"C:\MyFileName.csv";
;
permission = new FileIOPermission(fileName,#io_write);
permission.assert();
commaTextIo = new CommaTextIo(fileName,#io_write);
while select sales
{
commaTextIo.writeExp([Sales.SalesId,Sales.CustAccount]);
}
CodeAccessPermission::revertAssert();

}

How to Write/ Read the Comma seperated values to/from a text file ?

The following sample code gives idea that how to write or read the values to a text file

static void testWriteAndRead(Args _args)
{
CommaIo io;
container con;
FileIoPermission perm;
int i;
;
#define.MyFile(@"c:\MyFileName.txt")
#define.MyFileWrite("w")
#define.MyFileRead("r")

perm = new FileIoPermission(#MyFile, #MyFileWrite);
if (perm == null)
{
return;
}
// Grants permission to execute the CommaIo.new method.
// CommaIo.new runs under code access security.
perm.assert();

// Write
io = new CommaIo(#MyFile, #MyFileWrite);

con = [1,"MyText",1.324,"Last field"]; // Assign the entries in the container according to record layout.
io.writeExp(con); // write this record according to file format (record/field delimiters).
// End Write

//Read
io = new CommaIo(#MyFile, #MyFileRead);
if (io != null)
{
con = io.read();
for(i=1;i<=conLen(con);i++)
{
info(strFmt("%1",conpeek(con,i)));
}
}
//End Read
// Close the code access permission scope.
CodeAccessPermission::revertAssert();

}

Array in AX

Arrays hold the values of any single type

static void testArray(Args _args)
{
Array ary = new Array (Types::String);
int i;
;
ary.value(1, "abc");
ary.value(2, "xyz");
ary.value(5, "ijk");
ary.value(3, "lmn");

info(strfmt("%1", ary.toString()));
info(strfmt("%1", ary.definitionString()));
info(strfmt("%1", ary.lastIndex())); // Return the last index value of the Array

for(i=1;i<=ary.lastIndex();i++)
{
info(strfmt("%1", ary.value(i)));
}

}

List in AX

List is the structure which contain values of any same type, where we can access sequentially.

static void testList(Args _args)
{
List li = new List(Types::Integer);
ListEnumerator enumerator;
ListIterator lItr;
;
// Add some elements to the list
li.addStart(3);
li.addEnd(1);
li.addEnd(2);

info(strfmt("%1", li.definitionString())); // returns the "Type" of the list
info(strfmt("%1", li.toString())); // output --> <3,1,2>

enumerator = li.getEnumerator();
while(enumerator.moveNext())
{
info(Strfmt("Enumerator %1 ",enumerator.current()));
}

lItr = new ListIterator (li);
while(lItr.more()) // checks whether there are more elements in the list
{
info(strFmt("Iterator %1",lItr.value()));
lItr.next(); // Skips to the next element
}

}

Map In AX

The "Map" allows to associate one value (the key) with another value. Both the key and value can be any valid type.

static void testMap(Args _args)
{
Map m = new Map(Types::STRING, Types::INTEGER);
MapIterator mi;
MapEnumerator me = m.getEnumerator();


int i,test;
str keyId = "abc";
;

m.insert("abc", 37);
m.insert("def", 102);

i = m.lookup("abc");
test = m.exists(keyId)? m.lookup(keyId): 0;
m.insert(keyid, test + 1); // updation

if (m.exists("abc"))
info(strFmt("%1",m.lookup("abc")));


mi = new MapIterator(m);
while (mi.more())
{
info(strFmt("%1 Iterator", mi.key()));
info(strFmt("%1", mi.value()));

mi.next();
}


while (me.moveNext())
{
info(strFmt("%1 Enumertor",me.currentKey()));
info(strFmt("%1",me.currentValue()));
}

}

Set In AX

Set contains values of the same type, where value is unique and always sorted on a value

static void testSet(Args _args)
{
Set set = new Set(Types::Integer);
SetIterator sItr;
SetEnumerator sEnum ;

;
set.add(100);
set.add(101);
set.add(102);


info(strFmt("%1",set.toString()));
info(strFmt("%1",set.elements())); //No of elements
info(strFmt("%1",set.in(100))); //To see if a value already is added, use the in method:

set.remove(100);

info(strFmt("%1",set.elements())); //No of elements

// Getting values by using SetEnumerator

sEnum = set.getEnumerator();

while (sEnum.moveNext())
{
info(strFmt("%1",sEnum.current()));
}

// Getting values by using SetIterator

sItr = new SetIterator(set); // initializing set to setIterator
while(sItr.more())
{

info(strFmt("%1",sItr.value()));
sItr.next();
}


}

QueryRangeFilter in AX

static void QueryRangeFilter(Args _args)
{
Query query;
QueryBuildDataSource datasource;
QueryBuildRange range;
QueryFilter filter;
QueryRun queryRun;
int countAfterChange = 0, countBeforeChange = 0;

query = new Query();
datasource = query.addDataSource(tableNum(CustTable));
datasource = datasource.addDataSource(tableNum(SalesTable));
datasource.joinMode(JoinMode::InnerJoin);
datasource.relations(true);

datasource.addLink(fieldNum(CustTable, AccountNum),
fieldNum(SalesTable, CustAccount));

filter = query.addQueryFilter(datasource,
fieldStr(SalesTable, CurrencyCode));

filter.value(SysQuery::value('USD'));

//range = datasource.addRange(fieldNum(SalesTable, CurrencyCode));
//range.value(SysQuery::value('EUR'));

queryRun = new QueryRun(query);
while (queryRun.next())
{
countBeforeChange ++;
if (queryRun.changed(tableNum(CustTable)))
countAfterChange ++;
}

info(strFmt("CountAfterChange : %1", countAfterChange ));
info(strFmt("CountBeforeChange : %1", countBeforeChange ));
}