Sunday, September 20, 2009

Create an ODBC connection in Ax

In Ax, you are never on an island. You have lots of options to bring in data from other systems, whether it's through AIF, reading an XML file or.. directly connecting to another database. In this post, I'll show how to create an ODBC connection to a external database.
For this, we'll use the standard available ODBCConnection class, as opposed to CLR Interop and rely on the .NET functions.
Here goes, in 3 simple steps.
  1. Set the login property
  2. Create the connection
  3. Read from a sample table

static void myODBCConnection(Args _args)
{ ODBCConnection myODBC;
Statement myStatement;
LoginProperty myLoginProperty;
Resultset myResultset;

str mySQLStatement;
str myConnectionString;

str myDSN="yourdatasourcenamehere";
str myUserName="yourusername";
str myPassword="yourpassword";
;
myConnectionString=strfmt("DSN=%1;UID=%2;PWD=%3",myDSN,myUserName,myPassword);

myLoginProperty = new LoginProperty();
myLoginProperty.setOther(myConnectionString);

try
{
myODBC = new OdbcConnection(myLoginProperty);
myStatement=myODBC.createStatement();

mySQLStatement="SELECT Field1,Field2 FROM myTable";
myResultSet=myStatement.executeQuery(mySQLStatement);
while (myResultSet.next())
{
info(myResultSet.getString(1));
info(int2str(myResultSet.getInt(2)));
}
} catch
{
error('Unexpected error');
}
}


In earlier versions from Ax, the LoginProperty came with the methods to set the loginname and password. But Microsoft has cut these features (security reasons?). But as you can see in the example, we work around that by using the 'SetOther' method.

There's one thing you need to consider. The Ax client requires a 32 bit datasource. On a 64 bit system like Windows 2008, this may result in an error 'Data source name not found'. That's something for my next post.

3 comments:

  1. thank you very much =)

    ReplyDelete
  2. Hi,

    I am using five ODBC conenction (connection1 = this.getconnection(), connection1 = ,...) for each statement(statement = connection1.createStatement(),...). for getting different values from Different Non Ax SQL table.

    When I call the last statement Statement4.executeUpdate(SqlStatement4) the system gets hanged. In this statement I am trying to update the status of the Table which is used in Statement1. Is there an thing wrong with my logic?

    Please let me know if anything wrong..

    ReplyDelete
  3. Hi Sivakumar,

    Whithout actually seeing the code, without knowing the different db's you connect to, it's difficult so say what is wrong.

    I would advice not to nest the SQL statements when using ODBCConnection. There are known issues with Statement and ODBCConnection. For example when you don't read out all the values you request, or do not process all the records returned with Statement.

    I would setup my ODBCConnection and run my Statements sequentially, not in parallel. You can use temporary tables from Ax if you need to pass information along from one db to another.

    ReplyDelete