Dynamics AX
  RSS Feed  LinkedIn  Twitter
Want to turn you're data into a true asset? Ready to break free from the report factory?
Ready to gain true insights that are action focused for truly data informed decisions?
Want to do all of this across mutliple companies, instances of Dynamics and your other investments?
Hillstar Business Intelligence is the answer then! (www.HillstarBI.com)

Hillstar Business Intelligence for Microsoft Dynamics AX and NAV on Mobile, Desktop, Tablet


Let us prove to you how we can take the complexity out of the schema and truly enable users to answer the needed questions to run your business! Visit Hillstar Business Solutions at: www.HillstarBI.com

Monday, November 28, 2005

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

3 Comments:

Blogger Wilsont said...

Any way to catch the error during

cn = new OdbcConnection(lp);

I've tried to put a try and catch but no help

Although it has a info box telling you the error, but I have to use it via AXCOM so info box does no use for me

9:53 PM  
Blogger Chaoz said...

Hi im trying to get this to work in AX4 (version: 4.0.2501.116)

But im getting an error that says thath loginproperty.setpassword and loginpropery.setusername are not supported anymore...

Any chance you know something to work around it?

Thanks in advance

1:09 AM  
Anonymous chandru said...

in Ax 2009, there is LP.Setuserid() && setPwd. how we can connect the through ODBC without those options.please tell me how to connect the external DB via ODBC in Ax 2009 X++ code.

3:37 AM  

Post a Comment

Links to this post:

Create a Link

<< Home


Copyright 2005-2011, J. Brandon George - All rights Reserved