Поговорим об ADO

[Gustav]

Уважаемые коллеги, насобирав ссылки для ветки Класс ODBCConnection , а также вооружившись информацией из других источников, решил сам немного поэкспериментировать с темой ADO в Аксапте. Результаты мне понравились. В качестве примера выкладываю свой демоджоб.

Джобик делает следующее:
- Аксапта выполняет запрос к БД (у меня – Oracle); запрос – непосредственно к базе, на «нормальном» SQL, не на аксаптовском «диалекте».
- Результаты запроса (некоторые поля таблицы «Список сотрудников») помещаются в новую рабочую книгу Excel; для вставки данных на рабочий лист используется метод CopyFromRecordset объекта Range.
- Книга сохраняется, Excel закрывается (Excel невидим на протяжении всего процесса, книгу можно посмотреть по окончании в соответствующей заданной Вами папке).
- Далее Аксапта открывает эту новую книгу Excel уже не «как Excel c ячейками А1», а так, как если бы это была полноценная таблица БД (открывает - как ADO-шный Recordset).
- Строки этой таблицы считываются в Аксапту и выводятся в info-окне.

Перед запуском джоба необходимо:
1. Прописать Вашу папку для сохранения тестовой рабочей книги Excel;
2. Прописать параметры подключения к Вашей БД в зависимости от БД: SQL Server или Oracle, раскомментировав одно и закомментировав соответственно другое.

static void Job_TestADO_1(Args _args)
{
// НЕОБХОДИМО ПЕРЕД ЗАПУСКОМ:
    // 1. прописать Вашу тестовую папку
    str filePath = "C:\\Gustav\\ADOtest\\";
    // 2. прописать Ваши параметры ConnectionString, 
    // раскомментировав нужное и закомментировав ненужное
    // ЛИБО для Oracle:
    str dbCnnString = "Provider=MSDAORA;" +
                      "Password="    + "yourpassword"    + ";" +
                      "User ID="     + "youruserid"      + ";" +
                      "Data Source=" + "yourdatasource";
    // ЛИБО для SQL Server:
    //str dbCnnString = "Provider=SQLOLEDB;" +
    //                  "DATABASE=" + "yourdatabase" + ";" +
    //                  "SERVER="   + "yourserver"   + ";" +
    //                  "UID="      + "youruserid"   + ";" +
    //                  "PWD="      + "yourpassword";
    COM xlApp;            // Excel.Application
    COM wbks, wbk;        // Workbooks, Workbook
    COM wkss, wks;        // Worksheets, Worksheet
    COM rng, cell, rngCR; // все Range
    COM font;             // Range.Font
    COM cnnDB,    rstDB;    // ADO: Connection, Recordset
    COM cnnExcel, rstExcel; // ADO: Connection, Recordset
    COM flds, fld;          // ADO: Fields, Field
    int i, iMax;
    str strSQL, strInfo;
// ============================================================================
    // СНАЧАЛА ДВЕ ВЛОЖЕННЫЕ ФУНКЦИИ
// ----------------------------------------------------------------------------
    // функция преобразует полученное через ADO вариантное значение из Excel
    // в значение, пригодное к дальнейшему использованию в Axapta
    // (в данном демо-джобе не используется - она сама по себе демо :-) )
    anytype adoValueFromExcel(COMVariant _val, int _type)
    {
        switch (_type)
        {
            // список констант соответствует типам данных, распознаваемых ADO для Excel
            case   5:      return _val.double();    // adDouble
            case   6:      return _val.currency();  // adCurrency
            case   7:      return _val.date();      // adDate
            case  11:      return _val.boolean();   // adBoolean
            case 202, 203: return _val.bStr();      // adVarWChar, adLongVarWChar("memo")
        }
        return '';
    }
// ----------------------------------------------------------------------------
    // функция преобразует полученное через ADO вариантное значение из Excel
    // в СТРОКОВОЕ значение, пригодное к использованию в INFO-окне Axapta
    // (используется в данном демо-джобе)
    str adoInfoValFromExcel(COMVariant _val, int _type)
    {
        switch (_type)
        {
            case   5:      return num2str(_val.double(),-1,-1,1,0);
            case   6:      return num2str(_val.currency(),-1,-1,1,0);
            case   7:      return date2str(_val.date(),123,2,2,2,2,4);
            case  11:      return int2str(_val.boolean());
            case 202, 203: return _val.bStr();
        }
        return '';
    }
// ============================================================================
    // ТЕПЕРЬ ОСНОВНОЙ ПРОЦЕСС
    // готовим информацию БД для передачи в Excel
    cnnDB = new COM("ADODB.Connection");
    cnnDB.connectionString(dbCnnString);
    cnnDB.Open();
    strSQL = "SELECT Name, PhoneLocal, EmplId, CellularPhone, UserId, BirthDate, RecId " +
             "FROM EmplTable " +
             "ORDER BY Name";
    rstDB = new COM("ADODB.Recordset");
    rstDB.open(strSQL, cnnDB);
    // готовим новую рабочую книгу Excel для приема данных из БД:
    xlApp = new COM("Excel.Application");
    wbks = xlApp.Workbooks();
    wbk  = wbks.Add();
    wkss = wbk.Worksheets();
    wks  = wkss.Item(1);
    wks.Name("AdoTestSheet");
    rng  = wks.Range("A1");
    // выводим строку имен полей (1-я строка листа Excel)
    flds = rstDB.Fields();
    iMax = flds.Count() - 1;
    for (i = 0; i <= iMax; i += 1)
    {
        fld = flds.Item(i);
        cell = rng.Offset(0, i);
        cell.Value2(fld.Name());
    }
    rngCR = rng.CurrentRegion();
    font = rngCR.Font();
    font.Bold(true); // делаем выведенные заголовки жирным шрифтом
    // выводим данные, начиная со 2-й строки листа Excel - больше было подготовки! :-)
    cell = rng.Offset(1, 0);
    cell.CopyFromRecordset(rstDB); // вот она, "изюминка"!
    // сохраняем книгу и закрываем Excel
    if (WinAPI::fileExists(filePath + "AdoTestBook.xls"))
        WinAPI::deleteFile(filePath + "AdoTestBook.xls");
    wbk.SaveAs(filePath + "AdoTestBook.xls");
    wbk.Close();
    xlApp.Quit();
    rstDB.Close();
    cnnDB.Close();
// ----------------------------------------------------------------------------
    // открываем через ADO ранее сохраненную книгу Excel как-будто таблицу БД !
    // (сейчас нет ячеек типа A1 и B1, а есть "настоящая" таблица с полями и записями)
    cnnExcel = new COM("ADODB.Connection");
    cnnExcel.connectionString("Provider=Microsoft.Jet.OLEDB.4.0;" +
                              "Data Source=" + filePath + "AdoTestBook.xls;" +
                              "Extended Properties='Excel 8.0;HDR=Yes'");
    cnnExcel.Open();
    rstExcel = new COM("ADODB.Recordset");
    rstExcel.Open("SELECT * FROM [AdoTestSheet$]", cnnExcel);
    // выводим в Info строку заголовков из Excel
    flds = rstExcel.Fields();
    iMax = flds.Count() - 1;
    strInfo = '';
    for (i = 0; i <= iMax; i += 1)
    {
        fld = flds.Item(i);
        strInfo = strInfo + fld.Name() + " -- ";
    }
    info(strInfo);
    info("----------------------------------------------------------------");
    // выводим в Info данные из Excel
    while (!rstExcel.EOF())
    {
        strInfo = '';
        for (i = 0; i <= iMax; i += 1)
        {
            fld = flds.Item(i);
            strInfo = strInfo + adoInfoValFromExcel(fld.Value(), fld.Type()) + " -- ";
        }
        info(strInfo);
        rstExcel.MoveNext();
    }
    rstExcel.Close();
    cnnExcel.Close();
}

[macklakov]

Лучше в аксовскую базу со стороны не лезть. Получишь блокировки, в самый напряженный момент.

[Gustav]

Цитата:

Сообщение от macklakov

Лучше в аксовскую базу со стороны не лезть. Получишь блокировки, в самый напряженный момент.

Нууу... второй год у себя лазаем SELECT-ами (довольно сложными)... вроде ничего такого критичного замечено не было...

[Recoilme]

Цитата:

Сообщение от macklakov

Лучше в аксовскую базу со стороны не лезть. Получишь блокировки, в самый напряженный момент.

По уму так лучше вообще никуда никогда не лезть, только тогда сложно зарплату будет получить, в самый нужный момент

[Gustav]

Цитата:

Сообщение от macklakov

Лучше в аксовскую базу со стороны не лезть. Получишь блокировки, в самый напряженный момент.

Однако, нашёлся «рояль в кустах». Теперь лезем не стороны, а изнутри!
На мой взгляд – просто чума…

Recordset в оперативной памяти (как массив)! Без объекта Connection! Сам по себе, без привязки к источнику данных. При этом обладает свойствами обычного Recordset-а (работают всякие MoveLast, MoveFirst и т.п. – конечно, надо потестировать, но MoveFirst работает точно – см. джоб ниже). Можно добавлять записи, редактировать их. Состав полей тоже определяем сами.

В качестве примера – джоб ниже, опять-таки выводящий в Excel список сотрудников. Ничего предварительно настраивать не надо, просто запускайте.

static void Job_TestADO_2(Args _args)
{
// ----------------------------------------------------------------------------
// ADODB.Recordset в оперативной памяти без привязки к источнику данных
// ----------------------------------------------------------------------------
    EmplTable emplTable;
    COM rstAxa;    // ADO: Recordset
    COM flds, fld; // ADO: Fields, Field
    COM xlApp;            // Excel.Application
    COM wbks, wbk;        // Workbooks, Workbook
    COM wkss, wks;        // Worksheets, Worksheet
    COM rng, cell, rngCR; // все Range
    COM font;             // Range.Font
    COM entCol;           // Range.EntireColumn
    COM actWin;           // Excel.Application.ActiveWindow
    int i, iMax;
// ============================================================================
    // СНАЧАЛА ВЛОЖЕННАЯ ФУНКЦИЯ
// ----------------------------------------------------------------------------
    // функция для задания типа поля нашего Recordset-а в оперативной памяти
    // в данном демо-джобе используется для наглядности
    // для простоты используем всего 3 типа данных: число, строка и дата
    int adoTypeToExcel(str _type)
    {
        switch (_type)
        {
        // используются значения констант перечисления DateTypeEnum из топика TypeProperty (ADO)
        // (см. справку по ADO в файле ADO210.CHM - можно поискать на своем компе)
            case 'num' : return   5; // adDouble
            case 'str' : return   8; // adBSTR
            case 'date': return 133; // adDBDate
        }
        return 8;
    }
// ============================================================================
    // ТЕПЕРЬ ОСНОВНОЙ ПРОЦЕСС
    // Recordset создается в оперативной памяти - без Connection!
    rstAxa = new COM('ADODB.Recordset');
    // формируем структуру нашего Recordset-а в "мозгах"
    flds = rstAxa.Fields();
    flds.Append('Name'         , adoTypeToExcel('str' ));
    flds.Append('PhoneLocal'   , adoTypeToExcel('str' ));
    flds.Append('EmplId'       , adoTypeToExcel('str' ));
    flds.Append('CellularPhone', adoTypeToExcel('str' ));
    flds.Append('UserId'       , adoTypeToExcel('str' ));
    flds.Append('BirthDate'    , adoTypeToExcel('date'));
    flds.Append('RecId'        , adoTypeToExcel('num' ));
    // и наконец открываем его
    rstAxa.Open();
// ----------------------------------------------------------------------------
    while select emplTable order by Name
    {
    // бежим по таблице "Список сотрудников" Axapta
    // и добавляем записи (выбранные поля) в Recordset в оперативной памяти
        rstAxa.AddNew();
            fld = flds.Item('Name'         ); fld.Value(emplTable.Name         );
            fld = flds.Item('PhoneLocal'   ); fld.Value(emplTable.PhoneLocal   );
            fld = flds.Item('EmplId'       ); fld.Value(emplTable.EmplId       );
            fld = flds.Item('CellularPhone'); fld.Value(emplTable.CellularPhone);
            fld = flds.Item('UserId'       ); fld.Value(emplTable.UserId       );
            fld = flds.Item('BirthDate'    ); fld.Value(emplTable.BirthDate    );
            fld = flds.Item('RecId'        ); fld.Value(emplTable.RecId        );
        rstAxa.Update();
    }
    // к этому моменту в оперативной памяти сформирована НАША таблица "Список сотрудников"
// ----------------------------------------------------------------------------
    // перед выгрузкой в Excel встанем на первую запись НАШЕЙ ТАБЛИЦЫ В ПАМЯТИ
    // и изменим имя сотрудника, дату его рождения и ID
    // НЕ БОЙТЕСЬ!!! Аксаптовская таблица EmplTable при этом НЕ ЗАТРАГИВАЕТСЯ!!! :)))
    rstAxa.MoveFirst();
    fld = flds.Item('Name'     ); fld.Value('Самый молодой cотрудник с очень длинным именем');
    fld = flds.Item('BirthDate'); fld.Value(1\3\2006); // 1-е марта 2006 года
    fld = flds.Item('EmplId'   ); fld.Value('000333'); 
    // потом обратите внимание, что в Excel ведущие нули у 000333 сохранятся!
    rstAxa.Update();
// ----------------------------------------------------------------------------
    // готовим новую рабочую книгу Excel для приема данных из Axapta:
    xlApp = new COM('Excel.Application');
    xlApp.Visible(true);
    wbks = xlApp.Workbooks();
    wbk  = wbks.Add();
    wkss = wbk.Worksheets();
    wks  = wkss.Item(1);
    wks.Name('AdoTestSheet');
    rng  = wks.Range('A1');
// ----------------------------------------------------------------------------
    // выводим строку имен полей (1-я строка листа Excel)
    flds = rstAxa.Fields();
    iMax = flds.Count() - 1;
    for (i = 0; i <= iMax; i += 1)
    {
        fld = flds.Item(i);
        cell = rng.Offset(0, i);
        cell.Value2(fld.Name());
    }
    rngCR = rng.CurrentRegion();
    font = rngCR.Font();
    font.Bold(true); // делаем выведенные заголовки жирным шрифтом
// ----------------------------------------------------------------------------
    // выводим данные, начиная со 2-й строки листа Excel
    cell = rng.Offset(1, 0);
    cell.CopyFromRecordset(rstAxa);
// ----------------------------------------------------------------------------
    // подгоняем ширину столбцов Excel
    rngCR = rng.CurrentRegion();
    entCol = rngCR.EntireColumn();
    entCol.AutoFit();
    // замораживаем строку заголовков Excel
    cell.Select();
    actWin = xlApp.ActiveWindow();
    actWin.FreezePanes(true);
// ----------------------------------------------------------------------------
    rstAxa.Close();
}

[Gustav]

Упс! Забыл сказать! У меня Excel 2000 (увы, до сих пор...). Так вот, мало ли джобы будут ругаться в Excel 2002-2003 и выше на оператор:
cell.Value(fld.Name());

В этом случае его нужно подправить, добавив "десяточку", вот так:
cell.Value(10, fld.Name());

Альтернатива - можно заменить Value на Value2. (P.S. Позже я так и исправил)

Подробности здесь: Axapta программирует Excel на VBA

[Oz]

Просто во всём нужно находить оптимальное соотношение. А то бывает, что зарплату в нужный момент не удаётся получить как раз из-за того, что кто-то куда-то не туда слазил

[Sada]

Благодаря таким топикам, я когданибуть всетаки научусь писать под АДО!!!
Очень полезный топик!!! спасибо!!

[konopello]

Даааа, а больше всего мне понравилась скорость работы данного joba, просто обалдеть.

[Sada]

2Gustav
Я поискал на своем компе в ADO210.CHM, нашол, а в нем не нашол ничего, подскажите ели не сложно путь...
(конкретно меня интересует real)
C уважением - Sada...

[Gustav]

Цитата:

Сообщение от Sada

2Gustav
Я поискал на своем компе в ADO210.CHM, нашол, а в нем не нашол ничего, подскажите ели не сложно путь...
(конкретно меня интересует real)
C уважением - Sada...

-Microsoft ActiveX Data Objects (ADO)
--Microsoft ADO Programmer's Reference
---ADO API Reference
----ADO Properties
-----Type Property (ADO)

Это если раскрывать узлы на закладке "Содержание".
Но есть другой путь: встать на вторую закладку "Указатель" и в поле "Введите ключевое слово для поиска" начать набирать "type property"

[gl00mie]

Цитата:

Сообщение от Gustav

-Microsoft ActiveX Data Objects (ADO)
--Microsoft ADO Programmer's Reference
---ADO API Reference
----ADO Properties
-----Type Property (ADO)
есть другой путь: встать на вторую закладку "Указатель" и в поле "Введите ключевое слово для поиска" начать набирать "type property"

К слову, в поставке с MDAC идут два небесполезных файлика (они лежат в %ProgramFiles%\Common Files\System\ado), один из которых - adojavas.inc - можно с помощью поиска и замены легко переделать в макрос на X++ для определения всяческих ADO-констант

[gl00mie]

Цитата:

Сообщение от gl00mie

%ProgramFiles%\Common Files\System\ado\adojavas.inc можно легко переделать в макрос на X++ для определения всяческих ADO-констант

Оказывается, все уже есть - макрос CCADO и классы ссADO* в Аксапте...

[Gustav]

Цитата:

Сообщение от gl00mie

Оказывается, все уже есть - макрос CCADO и классы ссADO* в Аксапте...

Ну и чего расстраиваться?
В Аксапте есть класс ComExcelDocument_RU , которым пользуются далеко не 100 % разработчиков, судя по опросу Какой способ для связи Axapta с Excel вы предпочитаете?
Что касается ADO, то класс CCADORecordSet в исходном виде без доделок выглядит бедненько: из навигационных методов только один MoveNext. IMHO без перекраивания этого класса 2-й джоб с отвязанным рекордсетом тоже не сразу запустишь. Поэтому простора для творчества - немеряно!

[IvanOFF]

Возникла проблема с импортом через ADO значения из ячейки Excel с типом "время" (AX 4.0 SP2 EE). Почему-то значение такой ячейки распознается как дата.
Я использую класс, который написал gl00mie (в classDeclaration написано // gl00mie, import data from Excel via ADO, 20061220). В методе getFieldValue() на строке

fld = flds.Item(_fldId);

инициализируется значение переменной fld. Когда дело доходит до ячейки с типом "время", значение fld.Type() равно 5, что соответствует дате (согласно макросу CCADO), и fld.Value() возвращает 01.01.1900.

В чем может быть проблема? Вообще, кто-нибудь сталкивался с импортом времени из Excel через ADO?

[Gustav]

В Excel нет отдельного типа "Время" (не путать с форматом, отображающим только время, без даты). Есть единый тип "Дата", в котором собственно дата является целым числом (до запятой), а время дробным (после запятой). Особенно хорошо это видно при программировании на VBA: есть тип Date, но нет Time.

Чтобы загрузить время в Аксапту следует до импорта из Excel в самом Excel:
- либо превратить время в вещественный тип путем назначения столбцу формата General (Общий);
- либо превратить его в вещественный тип, умножить на 86400 и тащить в Аксапту в виде общего количества секунд;
- либо распарсить исходное время на часы, минуты и секунды, импортировать эти 3 значения по отдельности и уже в Аксапте собирать,
- либо превратить в текст вида "09:30:00" при помощи функции вроде =ТЕКСТ(A1,"[ЧЧ]:ММ:СС").

[IvanOFF]

Цитата:

Сообщение от Gustav

В Excel нет отдельного типа "Время" (не путать с форматом, отображающим только время, без даты). Есть единый тип "Дата", в котором собственно дата является целым числом (до запятой), а время дробным (после запятой). Особенно хорошо это видно при программировании на VBA: есть тип Date, но нет Time.

Чтобы загрузить время в Аксапту следует до импорта из Excel в самом Excel:
- либо превратить время в вещественный тип путем назначения столбцу формата General (Общий);
- либо превратить его в вещественный тип, умножить на 86400 и тащить в Аксапту в виде общего количества секунд;
- либо распарсить исходное время на часы, минуты и секунды, импортировать эти 3 значения по отдельности и уже в Аксапте собирать,
- либо превратить в текст вида "09:30:00" при помощи функции вроде =ТЕКСТ(A1,"[ЧЧ]:ММ:СС").

Спасибо! Теперь все понятно!

[sta[z]]

Возникла проблема с экспортом через ADO в Excel полей с типом дата. Если переносить как adbSTR, то это не устраивает конечных пользователей (не работает нормльно фильтрация). Если переносить как adDBDate, то datenull() отображается в Excel как 02.01.1900. Применение формата "ДД.ММ.ГГГГ" к ячейке не помогает. Можно ли как-нибудь передать дату так, чтобы пустая дата не отображалась?

[sta[z]]

Сам нашел выход. Если значение поля в Axapte datenull(), то нужно писать в ADO field.value() не значение поля, а просто 0. А в Excel у активного листа выставить в false параметр DisplayZeros(0).

[ice]

Цитата:

Сообщение от sta[z]

Сам нашел выход. Если значение поля в Axapte datenull(), то нужно писать в ADO field.value() не значение поля, а просто 0. А в Excel у активного листа выставить в false параметр DisplayZeros(0).

а если просто не заполнять поле в таких случаях?