Можно получить доступ ко внешним источникам данных через
—- Connecting to a Database via ODBC
In AX you can connect to outside databases through AX's built in ODBC, LoginProperty, Statement, and ResultSet Classes. Through these you can read in data to axapta, or preform update query's like delete's / updates / insert's etc. It's really very handy, espcially when connecting AX to already existing databases that AX needs data from, or needs to interact with.
Below is some example code that will connect to a Database, and loop through a select statements resultset:
OdbcConnection cn; Statement s; ResultSet rs; LoginProperty lp; str SQL; // Field Variables int Seq; str Field1; str Field2; ; // Create new Login property instance lp = new LoginProperty(); lp.setDatabase("Test"); lp.setServer("TestDBSrvr"); lp.setUserName("sa"); lp.setPassword("sa_password"); // Create new OdbcConnection instance, and pass it the LoginProperty just created above cn = new OdbcConnection(lp); // Create new Statement instance s = new Statement(); // Set the SQL statement SQL = "select * from testTable"; // Now fille the ResultSet, and pass it the Select Statement rs = s.executeQuery(SQL); while(rs.next()) { // Fill the field vairables now Seq = rs.getInt(1); Field1 = rs.getString(2); Field2 = rs,getString(3); // Below here preform some logic on the data, or do some action ... }
Получать данные надо в том же порядке, в каком они идут в запросе (см. описание функции http://msdn2.microsoft.com/En-US/library/aa177876(SQL.80).aspx)
В трехзвенной конфигурации не работает функция getDate в ResultSet – Axapta падает. Единственное пока известное решение – преобразовывать дату в строку средствами SQL сервера и делать обратное преобразование на стороне Axapta (см. 8956)
TransDate docDate; str sSQL; boolean isODBCSQLAccess, isODBCSQLServer; OdbcConnection connection; LoginProperty lp; Statement statement; ResultSet resultSet; #odbcConnectionEntries lp = new LoginProperty(); lp.setDSN('DSN'); lp.setUsername('login'); lp.setPassword('password'); connection = new OdbcConnection(lp); statement = connection.createStatement(); isODBCSQLAccess = (connection = odbcGetInfoStr(#SQL_DBMS_NAME) == 'ACCESS'); isODBCSQLServer = (connection = odbcGetInfoStr(#SQL_DBMS_NAME) == 'Microsoft SQL Server'); sSQL = 'SELECT *'; if (isODBCSQLServer) { sSQL += ', CONVERT(varchar(50), DOCUMENTDATE, 104) AS DOCDATE'; } if (isODBCAccess) { sSQL += ', DOCUMENTDATE AS DOCDATE'; } resultSet = statement.executeQuery(sSQL); docDate = str2date(resultSet.getString(i), 123);