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.