Pages

Wednesday, April 2, 2008

Read data from other databases

Here is a small exsample on how to do that from version 4.0 and greater:
ODBCConnection                connection;
SqlSystem sqlSystem = new SqlSystem();
LoginProperty loginProperty = sqlSystem.createLoginProperty();
Statement statement;
ResultSet resultSet;
SqlStatementExecutePermission sqlStatementExecutePermission;
str sqlStatement;
int columnId;
;
loginProperty = sqlSystem.createLoginProperty();
loginProperty.setServer('MyServer');
loginProperty.setDatabase('MyDatabase');

connection = new ODBCConnection(loginProperty);

statement = connection.createStatement();

sqlStatement = "select * from MyTable";

sqlStatementExecutePermission = new SqlStatementExecutePermission(sqlStatement);
sqlStatementExecutePermission.assert();

resultSet = statement.executeQuery(sqlStatement);

while (resultSet.next())
{
// Get data from colum 2
columnId = 2;
print resultSet.getString(columnId);
}
pause;

Make sure the code is executed on the AOS, as only the AOS is allowed to access databases outside AX.

2 comments:

Amber said...

I'm trying to implement this code and I get the following error on the loginProperty.setUsername line:
Cannot execute the required database operation.
This function is no longer supported.

I'm coding in AX 5.0. Do you know any way around this??

Palle Agermark said...

setUsername is not used in my sample, and you should not use it either 4.0 or 2009. From version 4.0 all access to databases is done under the credentials of the usersname of the AOS service and you can't change that.