Blog

Name is Anant Dubey and the intent to create this blog is to discuss the problems and issues that developer face in the dynamics AX development and to share the new things that come up with the new version of AX.

Monday, 14 November 2016

How to connect or access external database from AX 2012

Sometimes we have to communicate with outside of Dynamics Ax with in boundaries of Dynamics ax.
Consider following scenario where I have to communicate with StudentDb in Sql server and insert rows in Student Info. We can do this with odbc connection.


Now create New AX job and paste following code there


LoginProperty loginProperty;

OdbcConnection odbcConnection;

Statement statement;

ResultSet resultSet;

LoginProperty myLoginProperty;

str sql, criteria;

int output;

SqlStatementExecutePermission perm;

str myUserName="dynamicworld\\aliraza.zaidi";

str myPassword="abcd";

str myConnectionString;

 

;

 

 

 

 

myConnectionString=strfmt("UID=%1;PWD=%2",myUserName,myPassword);

myLoginProperty = new LoginProperty();

myLoginProperty.setServer("WIN-IKPOSIU2SGD");

myLoginProperty.setDatabase("studentdb");

myLoginProperty.setOther(myConnectionString);

 

//Create a connection to external database.

odbcConnection = new OdbcConnection(myLoginProperty);

 

if (odbcConnection)

{

 

sql ="INSERT INTO ..[StudentInfo]([FirstName],[LastName]) VALUES ('aliraza','zaidi')";

//Assert permission for executing the sql string.

perm = new SqlStatementExecutePermission(sql);

perm.assert();

 

//Prepare the sql statement.

statement = odbcConnection.createStatement();

output = statement.executeUpdate(sql);

 

 

statement.close();

}

else

{

error("Failed to log on to the database through ODBC.");
}

Ref:- https://community.dynamics.com/ax/b/alirazatechblog/archive/2014/10/18/connect-to-an-external-database-from-dynamics-ax-2012