Sunday, 24 January 2010

AX - SQL query another database via ODBC

Recently I was involved in migrating a large data set from another database. The standard Excel import/export is a great tool for migrating data. Once you start importing over 500 records - it can get very slow.
Here is some code to do an ODBC connection to execute a select statement.
server static void main(Args args)
{
    LoginProperty                   loginProperty;
    ODBCConnection                  connection;
    Statement                       statement;
    ResultSet                       resultSet;
    SqlSystem                       sqlSystem;
    SqlStatementExecutePermission   sqlStatementExecutePermission;
    str                             sql = 'SELECT * FROM Vendor';
    ;

    sqlSystem = new sqlSystem();
    loginProperty = sqlSystem.createLoginProperty();
    // Set server - if you dont setServer. It will use current server the AOS is on.
    loginProperty.setServer('localhost');
    loginProperty.setDatabase('OtherDb');
    connection = new ODBCConnection(loginProperty);
    statement = connection.createStatement();

    //Assert
    sqlStatementExecutePermission = new SqlStatementExecutePermission(sql);
    sqlStatementExecutePermission.assert();

    //BP Deviation Documented
    resultset = statement.executeQuery(sql);
    CodeAccessPermission::revertAssert();
    while ( resultset.next() )
    {
        info(resultset.getString(1));
    }

}
You have use a class and run on server. If you create a job it wont work because it will run client side.

3 comments:

BINUM said...

Also check out this blog
http://dynamics-ax-live.blogspot.com/2009/09/create-odbc-connection-in-ax.html

oracle fusion procurement online training said...

Credit score reporting isn't always something, however, oracle fusion procurement manner in which viable avail with options to offer the customer or the industrial organization company with all of the credit score and debt data
Thank you for sharing real records. it's miles an exquisite informative publish. your article is absolutely too nicely.
thank for sharing information
oracle fusion procurement online training
oracle fusion procurement training

ORACLE FUSION PROCUREMENT said...

Hi,
this is very interesting blog.
Thanks for sharing such a interesting blog.
oracle fusion SCM online training