Friday, June 25, 2010

sql query in ax

create a connection using connection object, don't need to assign any parameter here, this will help to run query. Create an statement object and assign a query in an execute method of it. Before that you need to create an SQlStatementExecutePermission that will allow to use SQL in a X++, then call assert that declares the calling code to invoke an API that is protected by a permission. after execute query a resultset is generated that you can access via .get(Datatype) method.

example is given below.



Connection con = new Connection();
Statement stmt = con.createStatement();
str sqlPassStr1;
ResultSet sqlres;
SqlStatementExecutePermission sqlStatementExecutePermission;
;

sqlPassStr1 = 'select ACCOUNTNUM, CUSTGROUP from CustTable';

sqlStatementExecutePermission = new SqlStatementExecutePermission(sqlPassStr1);
sqlStatementExecutePermission.assert();

sqlres = stmt.executeQuery( sqlPassStr1);
while(sqlres.next())
{
info(strfmt('%1 %2', sqlres.getString(1), sqlres.getString(2)));
}

Wednesday, June 23, 2010

add group by and fields selection in query

QueryBuildDataSource provides addSelectionField and addGroupByField to add fields and group on a query.

Further, what the aggregate function that you want to apply on a field you can provide it in a addSelectionField method that supports base enum SelectionField with some basic statistical operations. A sample code is given below.


Query q = new Query();
QueryBuildDataSource queryBuildDataSource;
QueryRun queryRun;
LedgerJournalTrans ledgerJournalTrans;

queryBuildDataSource = q.addDataSource(tablenum(LedgerJournalTrans));

queryBuildDataSource.addSelectionField(fieldnum(LedgerJournalTrans, JournalNum));
queryBuildDataSource.addSelectionField(fieldnum(LedgerJournalTrans,AmountCurDebit), SelectionField::Sum);
queryBuildDataSource.addSelectionField(fieldnum(LedgerJournalTrans, AmountCurCredit), SelectionField::Sum);
queryBuildDataSource.addGroupByField(fieldnum(LedgerJournalTrans, JournalNum));

queryRun = new QueryRun(q);

while(queryRun.next())
{
ledgerJournalTrans = queryRun.get(tablenum(LedgerJournalTrans));

info(strfmt('Journal Num = %1 Debit = %2 Credit = %3', ledgerJournalTrans.JournalNum, ledgerJournalTrans.AmountCurDebit, ledgerJournalTrans.AmountCurCredit));

}


An equivalent SQL query is given below.

select Journalnum, sum(AmountCurDebit), SUM(AmountCurCredit)
from LEDGERJOURNALTRANS
group by JOURNALNUM


Further you can filter the records using the addRange method in a QueryBuildDataSource class.

e.g.

queryBuildDataSource.addRange(fieldnum(LedgerJournalTrans, JournalNum)).value('000033_444')

the above statement filters the journal number '000033_444'.

Thursday, June 17, 2010

min and max in ax

Use minof and maxof to find minimum and maximum values of a table field. minof finds the minimum value of a specific field and maxof finds the maximum value of a specific field.

here's the example below.

LedgerTrans ledgerTrans;

select minof(TransDate) from ledgerTrans;

info(strfmt('%1', ledgertrans.TransDate));

select maxof(TransDate) from ledgerTrans;

info(strfmt('%1', ledgertrans.TransDate));


equivalent SQL queries for minimum and maximum date respectively.

select min(transdate) from LedgerTrans

select max(transdate) from LedgerTrans

add query range in a query object

First we need to find the datasource where we want to apply the range. Query.datasourceTable provides the reference of a querybuilddatasource. After getting the reference we can use the addRange method to add range and value in it.

Here's the demo. CustTable is a query object exist in AOT. No range is applied in a AOT query object, here the range is applied on a accountnum field of CustTable.

Query query = new Query(querystr(CustTable));
QueryRun queryRun;
QueryBuildDataSource queryBuildDataSource;
CustTable custTable;

//finding the reference of datasource.
queryBuildDataSource = query.dataSourceTable(tablenum(CustTable));

//adding range and providing the value for the range.
queryBuildDataSource.addRange(fieldnum(CustTable, AccountNum)).value('4000');

queryRun = new QueryRun(query);

while(queryRun.next())
{
custTable = queryRun.get(tablenum(CustTable));

info(strfmt('%1 ', custTable.AccountNum));

}

Thursday, May 27, 2010

create comma seperated file.

Use CommaIO file to create comma separated records or create CSV file.

create an instance of CommaIO and provide the operating mode.

These are the following modes, you can find it in FILE Macro under io_write.
'R' - Read
'W' - Write
'A' - Append
'T' - Translate
'B' - Binary

You need to assign the value of inrecorddelimeter.
and then use write method to write it. Multiple arguments can be passed in the write method of CommaIO.
CommaIO commaIO;
CustTable custTable;

//File name with the operating mode append.
commaIO = new CommaIO(@"C:\Cust.txt", 'A');
commaIO.inRecordDelimiter('\n');


while select CustTable
{
commaIO.write(custTable.AccountNum,custTable.AccountStatement, custTable.CashDisc);
}
Find the sample here.

read text file in axapta

TextBuffer is used to read the whole text file. Load file using textBuffer.fromFile and fetch the whole file string from the .getText

TextBuffer textBuffer;

textBuffer = new TextBuffer();
textBuffer.fromFile(@"C:\sohail.txt");
print textBuffer.getText();
pause;

Find the sample here.

create text file in axapta


Use BinData class to create a simple text file using axapta.
Firstly create an instance of a TextBuffer class and append text using textBuffer.AppendText. Pass it to binData what you want to write using binData.setStrData and then write using binData.saveFile.

Sample

BinData binData;
TextBuffer textBuffer;

textBuffer = new TextBuffer();
textBuffer.setText('This is a demo program');

binData = new BinData();
binData.setStrData(textBuffer.getText());
binData.saveFile(@"c:\sohail.txt");

Demo is attached here.