How to: Connect to an External Database from X++ Code [AX 2012] [AX 2009]

Create a DSN

---

To create a Data Source Name (DSN) go to Administrative Tools > Data Sources (ODBC).

Create the DSN on the tier where the X++ code will call the DSN from. This will be either on the client computer or on the Application Object Server (AOS) computer.

Note
Ongoing maintenance is simpler if the DSN is created on the AOS tier.

X++ Code Example with ODBC

---

The following X++ code example uses ODBC to connect to an external database. The code example assumes that you have already created the DSN in Windows.

X++

// X++, Main method in a class.
static public void Main(Args _args)
{
    LoginProperty loginProperty;
    OdbcConnection odbcConnection;
    Statement statement;
    ResultSet resultSet;
    str sql, criteria;
    SqlStatementExecutePermission perm;
    ;

    // Set the information on the ODBC.
    loginProperty = new LoginProperty();
    loginProperty.setDSN("dsnName");
    loginProperty.setDatabase("databaseName");

    //Create a connection to external database.
    odbcConnection = new OdbcConnection(loginProperty);

    if (odbcConnection)
    {
        sql = "SELECT * FROM MYTABLE WHERE FIELD = "
            + criteria
            + " ORDER BY FIELD1, FIELD2 ASC ;";

        //Assert permission for executing the sql string.
        perm = new SqlStatementExecutePermission(sql);
        perm.assert();

        //Prepare the sql statement.
        statement = odbcConnection.createStatement();
        resultSet = statement.executeQuery(sql);

        //Cause the sql statement to run,
        //then loop through each row in the result.
        while (resultSet.next())
        {
            //It is not possible to get field 3 and then 1.
            //Always get fields in numerical order, such as 1 then 2 the 3 etc.
            print resultSet.getString(1);
            print resultSet.getString(3);
        }

        //Close the connection.
        resultSet.close();
        statement.close();
    }
    else
    {
        error("Failed to log on to the database through ODBC.");
    }
}

32 Bit and 64 Bit Windows Operating System

The preceding code example can run on either the client tier or the server tier. The following table shows how the operating system architecture affects the choice of tier.

	32 bit Windows	64 bit Windows
Client tier (MorphX)	Runnable.	Not runnable.
Server tier (AOS)	Runnable.	Runnable. Consider adding the server keyword to the declaration of the Main method.

If you want to do Insert/Update operation through this code then you must use following code.

int      _returnId;

sql = " Your insert / Update Query"

 //Prepare the sql statement.
statement = odbcConnection.createStatement();
_returnId = statement.executeUpdate(sql);

reference :1. Microsoft Documentation MSDN 2. ArtofCreation 3. GalaxyofDynamics

Enable/Disable Fact boxes, Preview Panes in AX2012 UX

In Microsoft Dynamics AX 2012, if you want to disable factboxes/preview panes across application then you can do so.
It's not recommended to do this activity but this will help in troubleshooting performance issues assoicated with opening forms in ax2012 by controlling 'Timeout' parameter as below.

Go to ->System Administration->Setup->Client performance options 

Visit  Ax2012-client-performance-options for detailed explaination by Ax performance team.

Thanks axperf team :)

reference from : http://learnax.blogspot.in/2012/02/enabledisable-fact-boxes-preview-panes.html

Sequence of methods in form and table in AX

Form:

Sequence of Methods calls while opening the Form

Form — init ()

Form — Datasource — init ()

Form — run ()

Form — Datasource — execute Query ()

Form — Datasource — active ()

Sequence of Methods calls while closing the Form

Form — canClose ()

Form — close ()

Sequence of Methods calls while creating the record in the Form

Form — Datasource — create ()

Form — Datasource — initValue ()

Table — initValue ()

Form — Datasource — active ()

Sequence of Method calls while saving the record in the Form

Form — Datasource — ValidateWrite ()

Table — ValidateWrite ()

Form — Datasource — write ()

Table — insert ()

Sequence of Method calls while deleting the record in the Form

Form — Datasource — validatedelete ()

Table — validatedelete ()

Table — delete ()

Form — Datasource — active ()

Sequence of Methods calls while modifying the fields in the Form

Table — validateField ()

Table — modifiedField ()

Table:

When you press CTR+N
initValue()->

When you change data in a field
validateField() -> validateFieldValue() -> ModifiedField() -> ModifiedFieldValue()

When you close the table after entering some data
validateWrite() – > Insert() -> aosValidateInsert()

When you Save the Record for the first time
validateWrite() ->Insert() – > aosValidateInsert()

When you modify the record and saving
validateWrite() -> update() – > aosValidateUpdate()

When you delete the record
validateDelete() -> delete() -> aosValidateDelete()


reference : http://axvuongbao.blogspot.in/2013/09/sequence-of-methods-in-form-and-table.html

Simple UI Builder Class in SSRS Report in AX 2012

This UI Builder class is simple class with less code and less effort. UI Builder Class is needed when you want to customize your dialog which pop ups when you open a Report. UI Builder Class helps you to add run time lookups and other controls on the dialog form.

Step1 : Your Class must extends SrsReportDataContractUIBuilder

class SimpleDemoUIBuilder extends SrsReportDataContractUIBuilder
{
DialogField dialogEmplId;
DialogField dialogName;
boolean enable;
SimpleDemoContract contract;

}// two fields emplId and Name will reflect in the lookup in dialog form at the time of report opening.

Step2 : Override the build method

public void build()
{
contract = this.dataContractObject();
dialogEmplId = this.addDialogField(methodStr(SimpleDemoContract, parmEmplId),contract);

}// this method used for adding the field  which is from contract class.

Step3 : Write this below code to get lookup

private void emplIdLookup(FormStringControl emplIdlookup)
{
Query query = new Query();
QueryBuildDataSource qbds_EmplTable;
SysTableLookup sysTableLookup;

// Create an instance of SysTableLookup with the current calling form control.
sysTableLookup = SysTableLookup::newParameters(tableNum(FilterDemo), emplIdlookup);
// Add fields to be shown in the lookup form.
sysTableLookup.addLookupfield(fieldNum(FilterDemo,EmplId));
sysTableLookup.addLookupfield(fieldNum(FilterDemo,Name));
qbds_EmplTable = query.addDataSource(tableNum(FilterDemo));
sysTableLookup.parmQuery(query);
// Perform the lookup
sysTableLookup.performFormLookup();
}

Step4 : Override this method

public void getFromDialog()
{
contract = this.dataContractObject();
super();
}

Step5 : Override this method

public void initializeFields()
{
contract = this.dataContractObject();
}

Step6 : Override this method

public void postBuild()
{
super();
dialogEmplId = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(SimpleDemoContract,parmEmplId));dialogEmplId.registerOverrideMethod(methodStr(FormStringControl, lookup),
methodStr(SimpleDemoUIBuilder,emplIdLookup), this);dialogEmplId.lookupButton(2);
}

the Contract Class code and DP class have already been added to the blog. please check it for further reference but one important part has to be added to the contract class i.e in class declaration, the following part has to be updated.

[DataContractAttribute,SysOperationContractProcessingAttribute(classstr(SimpleDemoUIBuilder))]

reference : http://mfmujahidmim.wordpress.com/2012/12/18/simple-ui-builder-class/

Working with SSRS reports in Dynamics AX 2012

Microsoft Dynamics AX provides a model-based approach for creating reports by providing project templates and modeling tools that are incorporated into the Microsoft Visual Studio development environment. The reporting features provided by Microsoft Dynamics AX are tightly integrated with SQL Server Reporting Services.

Reporting Services is a server-based reporting platform that provides comprehensive reporting functionality for a variety of data sources. The reporting framework includes a set of tools for you to define reports in the Microsoft Visual Studio development environment. The report development experience takes advantage of extended SQL Server tools and components fully integrated into the Microsoft Visual Studio environment. By using the reporting APIs, you can integrate or extend data and report processing in custom applications. The following table provides links to more information on these Microsoft technologies:

You can refere following url for more information about report development with SSRS,Dynamics AX 2012. 

MSDN - Report Development AX 2012

Setting SysEntryPointAttribute for Services [AX 2012]

The SysEntryPointAttribute indicates what authorization checks are performed for a method that is called on the server. This attribute must be set for all service operations. For more information about how to set attributes on X++ methods, see Syntax for Attribute Classes.

The AIF Document Service Wizard automatically decorates service operations with [SysEntryPointAttribute(true)]. When you develop custom services you must use theSysEntryPointAttribute to decorate each service operation.

Setting :

The following table describes the possible values for the constructor of the SysEntryPointAttribute class.

[SysEntryPointAttribute(true)] - Indicates authorization checks are performed on the calling user for all tables accessed by the method.

[SysEntryPointAttribute(false)] - Indicates authorization checks are not performed on any tables accessed by the method.

Example :

[AifDocumentCreateAttribute, SysEntryPointAttribute(true)]
public AifEntityKeyList create(CustCustomer _custCustomer)
{
    return this.createList(_custCustomer);
}

reference : http://technet.microsoft.com/en-us/library/hh801193.aspx