Connecting to Databases through X++

In this article, I am going to explain about different ways through which one can connect to different databases for data manipulation operations.
 
In AX, the methods that I know are following. Please feel free to add more methods that you feel are also available.
 
  • ODBC Connection
  • ADO Connection
  • OleDB Connection
  • Connection class

Let us go through each of these options one by one.

ODBC Connection:

ODBC stands for Open Data Base Connectivity. It is a connection that is created to define a connection between a computer and a database stored on another system. The ODBC connection contains information needed to allow a 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 uses LoginProperty class for login information and uses Statement and ResultSet classes for carrying out DML operations. Below is an example of how to use this class.

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

    loginProp = new LoginProperty();

    loginProp.setServer(‘SUMIT’);
    loginProp.setDatabase(‘AX2009’);

    conn = new ODBCConnection(loginProp);

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

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

The above sample code is a job. Note that you may have to use respective permission classes like SQLStatementExecutePermission etc. while using in classes and any other place. Note that if you need to invoke a stored procedure then just type exec <SPName> in the executeQuery instead of the select statement.

ADO Connection:

ADO is an abbreviation for ActiveX Data Objects. ADO is a set of COM objects for accessing databases or data stores. In AX we have following objects making a collection for implementing ADO concept.

  • CCADOConnection – Helps in establishing a connection to the target database.
  • CCADOCommand – Helps in executing a command (a Text type or a Stored procedure)
  • CCADORecordSet – Stores the data
  • CCADOFields – A collection of all fields in CCADORecordSet
  • CCADOField – A single field from the collection of fields
  • CCADOParameter – A class that helps in passing parameters that a command needs or demands

The example below demonstrates the working of some of these classes:

static void dbCCADOConnection(Args _args)
{
    CCADOConnection connection = new CCADOConnection();
    CCADOCommand    ccADOCommand;
    CCADORecordSet  record;
    str connectStr = "Provider=SQLNCLI.1;Integrated Security=SSPI;"+
                     "Persist Security Info=False;Initial Catalog=AX2009;Data Source=SUMIT";

    COM     recordSet;  /*This is required to call moveNext method to parse the record set. In AX 4.0 this method was there in the CCADORecordSet class but in AX 2009 this has been deleted*/
    ;

    // Executing a SQL Statement
    try
    {
        connection.open(connectStr);
        ccADOCommand = new CCADOCommand();
        ccADOCommand.commandText("Select * from CustTable where DataAreaId = ‘CEU’");
        ccADOCommand.activeConnection(connection);
        record = ccADOCommand.execute();
        recordSet = record.recordSet();
        while (!record.EOF())
        {
            info(any2str(record.fields().itemIdx(0).value()));
            recordSet.moveNext();
        }
    }
    catch
    {
        error("An Exception has occurred");
    }

    connection.close();
}

The above sample code is a job. Note that you may have to use respective permission classes like SQLStatementExecutePermission etc. while using in classes and any other place.

OLEDB Connection:

OLEDB stands for Object Linking and Embedding, DataBase. It is a set of APIs designed by Microsoft and used for accessing different types of data stored in a uniform manner. Dynamics AX as such doesn’t have any specific classes built for this purpose. But one can make use of .Net Framework’s System.Data.OleDb namespace through AX’s COM Interoperability feature and use it in AX.

Below is an example code that depicts this scenario:

static void dbOLEDBConnection(Args _args)
{
    System.Exception                    e;
    System.Data.OleDb.OleDbConnection   objConn;
    System.Data.OleDb.OleDbCommand      cmdSelect;
    System.Data.OleDb.OleDbDataReader   reader;
    InteropPermission                   perm;
    str connectStr = "Provider=SQLNCLI.1;Integrated Security=SSPI;"+
                     "Persist Security Info=False;Initial Catalog=AX2009;Data Source=SUMIT";
    str exceptionStr;
    ;

    try
    {
        perm = new InteropPermission(InteropKind::ClrInterop);
        if (perm == null)
        {
            throw error("Error with file permissions");
        }
        perm.assert();

        objConn = new System.Data.OleDb.OleDbConnection(connectStr);
        objConn.Open();

        cmdSelect   = objConn.CreateCommand();
        cmdSelect.set_CommandText("SELECT * FROM CustTable where DATAAREAID = ‘CEU’");
        reader      = cmdSelect.ExecuteReader();

        while (reader.Read())
        {
            info(reader.GetString(0));
        }
    }
    catch(Exception::CLRError)
    {
        CodeAccessPermission::revertAssert();

        perm = new InteropPermission(InteropKind::ClrInterop);
        if (perm == null)
        {
            return;
        }
        perm.assert();

        e = ClrInterop::getLastException();

        CodeAccessPermission::revertAssert();

        while( e )
        {
            exceptionStr += e.get_Message();
            e = e.get_InnerException();
        }
        info(exceptionStr);
    }
    catch
    {
        error("An Exception has occurred");
    }

    if(objConn)
        objConn.Close();
}

Connection Class:

Connection class is mainly used for accessing the database in which a user has logged into AX i.e. Current Database and carry out the operations. This class is exetensively used in ReleaseUpdateDB classes, the classes used in data upgrades. This class cannot be run on client and should always be run on server. One more unique thing that I noticed is that the statements that you want to execute should be asserted first for permissions and then passed on to other method where they are executed. Create a class with following methods and set its RunOn property to Server.

class TestSQLExecuteClass
{
}

//This method tests the permissions for statement and then calls the method that will execute the statement
static void dbConnectionClass()
{
    ResultSet   rs;
    SqlStatementExecutePermission perm;
    ;

    perm = new SQLStatementExecutePermission("select * from CustTable where DATAAREAID = ‘CEU’");
    perm.assert();

    rs = TestSQLExecuteClass::statementExeQuery("select * from CustTable where DATAAREAID = ‘CEU’");

    while (rs.next())
    {
        info(rs.getString(1));
    }
    CodeAccessPermission::revertAssert();
}

//Executes the passed statement
private static ResultSet statementExeQuery(str _sql, Connection _con = null)
{
    ResultSet   resultSet;
    Statement   statement;
    ;

    try
    {
        if(!_con)
        {
            _con = new Connection();
        }

        statement = _con.createStatement();

        // Do not call assert() here, do it in the caller
        // BP deviation documented

        resultSet = statement.executeQuery(_sql);
    }
    catch (Exception::Error)
    {
        throw error("@SYS99562");
    }

    return resultSet;
}

Now you can call the method in a job as shown below:

static void dbConnectionClass(Args _args)
{
    ;

    TestSQLExecuteClass::dbConnectionClass();
}

These examples shown here are pretty simple and easy to understand and start with. Hope it helps you in building ‘connections’ .

 

Advertisements

4 thoughts on “Connecting to Databases through X++

  1. i got the errorplz tell me how can pass the input and out parameter to storedprocedure inextranal database in x++ using ccado classes.static void CCADOsp_123(Args _args){#CCADOCCADOParameter CCADOParameter ;CCADOConnection connection;CCADOCommand command;CCADORecordSet recordSet;CCADOFields fields;//StoredProcedure to fetch the data from Axapta Database and insert intoexternal databasestr connectionString = strfmt("Provider=SQLOLEDB.1;Password=Passw0rd;PersistSecurity Info=False;User ID=sa; Data Source=192.168.20.13,1433;InitialCatalog=copal");str data1;str data2;str data3;str data4;str data5;str data6;;try{connection = new CCADOConnection();connection.open(connectionString, #adConnectUnspecified);command = new CCADOCommand();command.activeConnection(connection);command.commandText( "exec PaySp_UpdEmpDetails_FromOtherDB ");command.commandType(#adCmdStoredProc);CCADOParameter =new CCADOParameter();CCADOParameter.name("@XZDBName");CCADOParameter.type(#adVarChar);CCADOParameter.size(100);CCADOParameter.parameter().value("AXDB_PIERIAN");CCADOParameter.parameter().Direction("AXDB_PIERIAN");CCADOParameter.name("@ImportMessage output");CCADOParameter.type(#adVarChar);CCADOParameter.size(160);CCADOParameter.parameter().output("@ImportMessage1");CCADOParameter.parameter().Direction("@ImportMessage1");command.addParameter(CCADOParameter);recordSet=Command.execute();while (!recordSet.EOF()){fields = recordSet.fields();// data1= fields.itemName(\’id\’).value();//data2 = Fields.itemName("first_name").value();//data3 = Fields.itemName("middle_name").value();//data4 = Fields.itemName("last_name").value();//input amd output parametersdata5= fields.itemName("@ImportMessage").value();data6 = Fields.itemName("@XZDBName").value();//info(strfmt(" Empoyee Name %1,%2,%3,%4,%5", data1, data2, data3, data4,data5 ));info(strfmt(" Empoyee Update has been sucessfully %5,%6", data5, data6 ));recordSet.moveNext();}}catch{error("An Exception has occurred");}connection.close();connection = null;}Error:Method \’Direction\’ in COM object of class \’ADODB.Parameter\’ returned errorcode 0x800A0BB9 (<unknown>) which means: Arguments are of the wrong type, areout of acceptable range, or are in conflict with one another.thanks ,Ashokbabu

  2. Hi,

    I need to use ADO connection to connect to external database. So, I have used the same example for testing in a job. Connection is established and no error is coming. But I am not able to retrieve the records from looping through EOF.

    Please help.

    Regards
    Ana

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s