Connecting to Databases through X++ PART -I

Connecting to Databases through X++ PART -I

In This series of post we will discuss about all possible ways through which we can connect to different databases.

     1.    ODBC Connection (Open Data Base Connection)

     2.    ADO Connection (ActiveX Data Objects)

      3. OLEDB Connection (Object Linking and Embedding, Database)

      4. Connection class

    1.  ODBC Connection:

ODBC used to define a connection between a computer and a database stored on another system. The ODBC connection allows computer user to access the information stored in a database that is not local to that computer. In Dynamics AX, we  have ODBCConnection class to carry out this type of database connection need. This class further uses LoginProperty class for login information and uses Statement and ResultSet classes for carrying out DML operations.

Here is an example of how to use this class.

static void theAxapta_ODBCConnection(Args _args)
{
    LoginProperty   loginProp;
    ODBCConnection  conn;
    Resultset       resultSet, resultSetCount;
    Statement       statement1, statement2;
    ;

    loginProp = new LoginProperty();

    loginProp.setServer(‘theAxapta’);//you can use IP address as well
    loginProp.setDatabase(‘AXDEVDB′);

    conn = new ODBCConnection(loginProp);

    statement1  = conn.createStatement();
    resultSet   = statement1.executeQuery("SELECT * from CustTable where DATAAREAID = ‘CEU’");

    while (resultSet.next())
    {
        info(resultSet.getString(1));
    }
}

Other related posts:

Connecting to Databases through X++ PART -I

Connecting to Databases through X++ PART -II

Connecting to Databases through X++ PART -III

Connecting to Databases through X++ PART -IV

-Harry

AX Tools–Spell Checker

AX Tools–Spell Checker

You can download the project from here:
Spell Checker – AX 2009
Spell Checker – AX 4.0


-Harry

How to compare UTCDATETIME with DATE only in reports Filter

How to compare UTCDATETIME with DATE only in reports Filter

Some time we need to companre UTCDateTime with DATE datatype. Generally we faced this issue during reports dialog box.

here is a the solution to compare the UTCDateTime and DATE datatypes in axapta.

public class ReportRun extends ObjectRun
{
    DialogField             DialogFromDate;
    DialogField             DialogToDate;
    DialogField             DialogPaymId;
    UtcDateTime               FromDate;
    UtcDateTime               ToDate;
    Name                    PaymId;

}

public boolean getFromDailog()
{
        ;

        fromDate    = clrSystemDateTime2UtcDateTime(dialogFromDate.value());
        todate      = clrSystemDateTime2UtcDateTime(dialogTodate.value() + 1);
        PaymId      = DialogPaymId.value();
        return true;
}

-Harry

Error when accessing form “BI generation options”

Error when accessing form “BI generation options

I got a error when i click on following link
Administration > Setup > Business analysis > OLAP > BI generation options

Cannot execute the required database operation. The SQL database has issued an error and in Application event viewer on AOS server you can find following entry The database reported (session 3 (Admin)): 
[Microsoft][SQL Native Client][SQL Server]Invalid column name 'usergroup3'.. 

The SQL statement was: 
DELETE FROM [DBO].BIUDMROLES WHERE USERGROUPID IN  usergroup1,usergroup2,usergroup3)"

Possible Reason:
The problem is caused by minor bug in Classes\ SrsStatementQuery method deleteInvalidBIUdmRoles() Right now the while looks like:

while select udmRoles
{
   select userGroupInfo where userGroupInfo.Id == udmRoles.UserGroupId;
   if (!userGroupInfo)
   {
      if (strlen(list) > 0)
     {
        list += #comma;
     }
     list += udmRoles.UserGroupId;
  }
}

Suggested Solution:

while select udmRoles
{
   select userGroupInfo where userGroupInfo.Id == udmRoles.UserGroupId;
   if (!userGroupInfo)
   {
      if (strlen(list) > 0)
     {
        list += #comma;
     }
list +="'"+ udmRoles.UserGroupId+"'";
  }
}
-Harry