Connecting to a Database via ODBC

In AX you can connect to outside databases through AX's built in ODBC,
LoginProperty, Statement, and ResultSet Classes. Through these you can read in data to axapta, or preform update query's like delete's / updates / insert's etc.
It's really very handy, espcially when connecting AX to already existing databases that AX needs data from, or needs to interact with.

Below is some example code that will connect to a Database, and loop through a select statements resultset:


OdbcConnection cn;
Statement s;
ResultSet rs;
LoginProperty lp;
str SQL;

// Field Variables
int Seq;
str Field1;
str Field2;
;

// Create new Login property instance
lp = new LoginProperty();
lp.setDatabase("Test");
lp.setServer("TestDBSrvr");
lp.setUserName("sa");
lp.setPassword("sa_password");

// Create new OdbcConnection instance, and pass it the LoginProperty just created above
cn = new OdbcConnection(lp);

// Create new Statement instance
s = cn.CreateStatement();

// Set the SQL statement
SQL = "select * from testTable";

// Now fille the ResultSet, and pass it the Select Statement
rs = s.executeQuery(SQL);

while(rs.next())
{
// Fill the field vairables now
Seq = rs.getInt(1);
Field1 = rs.getString(2);
Field2 = rs,getString(3);

// Below here preform some logic on the data, or do some action
...

}


Basically that is it! Of course you can get more in depth, but that is a good example of taking and reading data from another database through ODBC from within AX!

The section in red was edited when pointed out by a vistor to the site. I had it listed as: s = new Statement(); but really it should be s = cn.CreateStatement();

Find a job at: www.DynamicsAXJobs.com