Поговорим об 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

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

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

[Oz]

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

[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

[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-й джоб с отвязанным рекордсетом тоже не сразу запустишь. Поэтому простора для творчества - немеряно!

[Swetik]

Gustav, подскажите, как можно довавить формулу(вычисляемый столбец) при выводе данных с помощью ADO?

[Gustav]

Цитата:

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

Gustav, подскажите, как можно довавить формулу(вычисляемый столбец) при выводе данных с помощью ADO?

Вариантов с вычисляемым столбцом может быть два:
1. Или Вы всё вычисляете в Аксапте и в Excel подаете уже вычисленное значение (это вариант, видимо, Вас не интересует в силу своей и так понятности)
2. Или Вы выводите все данные в Excel и у же в нём потом начинаете наводить финальный порядок: форматируете, добавляете формулы и т.п. ("форматируете, добавляете" конечно же кодом X++ из Аксапты) - думаю, что Вам интересен именно этот вариант.

Для добавления формулы в Excel используйте свойство FormulaR1C1 объекта Range. И нотацию формул в стиле R1C1, а не традиционную A1. Это позволит Вам написать одну единственную формулу для всего столбца.

Запишите в Excel процесс создания формулы макрорекордером и проанализируйте код VBA - уверен, всё станет понятно. Если что, обращайтесь - разберемся дальше.

Полезно сделать еще вот что. Допустим есть такая табличка Excel:

A  B  C
----------------------
1  2  =A1+B1
2  4  =A2+B2
3  6  =A3+B3
4  8  =A4+B4
5 10  =A5+B5

Формулы в столбец С были введены традиционно, т.е. сначала в ячейку С1 была введена формула =A1+B1, а потом скопирована в остальные ячейки столбца C.
Теперь выполним команду меню Excel: Сервис/Параметры/Общие/галка "Стиль ссылок R1C1" - установить. Таблица несколько преобразится:

1  2  3
-----------------------
1  2  =RC[-2]+RC[-1]
2  4  =RC[-2]+RC[-1]
3  6  =RC[-2]+RC[-1]
4  8  =RC[-2]+RC[-1]
5 10  =RC[-2]+RC[-1]

Обратите внимание, что в столбце C теперь присутствует идеальная с нашей точки зрения формула, общая для всех ячеек.
Из кода VBA ее можно ввести, например, с помощью такого оператора (разом в весь диапазон!):

Range("C1:C5").FormulaR1C1 = "=RC[-2]+RC[-1]"

Перевести ее на X++, думаю, не составит большого труда

[Swetik]

Спасибо, я так и делала (2 вариант). Просто думала, может есть вариант- сразу при выводе задавать формулу, но в этом случае формула выводится как строка.

[Gustav]

Цитата:

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

Спасибо, я так и делала (2 вариант). Просто думала, может есть вариант- сразу при выводе задавать формулу, но в этом случае формула выводится как строка.

Да, метод CopyFromRecordset воспроизводит всё "буквально-честно". Если полю назначен строковый тип, то он значения этого поля как текст и воспроизводит. Это, в частности, очень полезно при выводе числовых строк с ведущими нулями - они сохраняются (во всяком случае, до момента, пока пользователь не войдет в ячейку и не нажмет Enter). И CopyFromRecordset, похоже, пишет в свойство Value, а нам нужно свойство FormulaR1C1. Другое дело, что можно попробовать "оживить" такую формульную строку после вывода, т.е. "встать" на этот столбец и считав строку из первой ячейки, прописать ее уже в свойство FormulaR1C1 для всего столбца. Тут нужно выбирать между усилиями по оживлению и усилиями по добавлению формулы после вывода. Хотя лично меня более привлекает именно вариант по "добавлению после", так как не надо "толкать" в Excel много лишней строковой информации, чтобы потом прочитать всего лишь из одной ячейки.

P.S. При "добавлении после" актуальной становится тема адресации к ячейкам новых добавляемых вычисляемых столбцов ("отталкиваясь" от массива уже выведенных данных). Один из вариантов (на VBA) можно посмотреть здесь во вложении. Смотреть следует в "окрестности" строки кода:
rngAL.Columns(1).FormulaR1C1 = "=MATCH(TRIM(RC[-4]),MatchLocations!" & _
"R" & CStr(rngML.Row) & "C1:" & _
"R" & CStr(rngML.Row + rngML.Rows.Count - 1) & "C1,0)"


===============================================================
P.P.S. 22.08.2006
Непосредственного отношения к данному сообщению информация ниже не имеет. Помещаю ее сюда, чтобы лишний раз не up-ать ветку.

Пара из ссылок, найденных Google'м по строке "disconnected recordset" (имеет отношение к Job_2). Так сказать, для вооружения теорией вопроса

Using Disconnected Recordsets, Part 2:

Цитата:

A disconnected Recordset can be used like an array, a collection, or a dictionary to hold a dataset. This is a valuable option for at least a couple of reasons. One reason you may want to use a Recordset to hold data rather than an array, collection, or dictionary is that all the Recordset methods that made ADO so convenient for client-server application, such as sort can be a real time saver.

Sorting Data by Using a Disconnected Recordset:

Цитата:

A disconnected recordset is essentially a database that exists only in memory; it is not tied to a physical database. You create the recordset, add records to it, and then manipulate the data, just like any other recordset. The only difference is that the moment the script terminates, the recordset, which is stored only in memory, disappears as well.

To use a disconnected recordset to sort data, you must first create the recordset, adding any fields needed to store the data. After you have created the fields, you then use the AddNew method to add new records to the recordset, using the same process used to add new records to a physical database. After the recordset has been populated, a single line of code can then sort the data on the specified field.

[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,"[ЧЧ]:ММ:СС").