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)));
}
This blog contains information related to .Net programs, Dynamics & many more...
Friday, June 25, 2010
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'.
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
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));
}
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.
Subscribe to:
Posts (Atom)