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.
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.