Технология Клиент-Сервер 2007'2

Home Поиск Издания Контакты k-press.ru


Продолжается подписка на наши издания! Вы не забыли подписаться?

COM Automation Feature

Автор: Родион Константинов
Опубликовано: 06.12.2002
Версия текста: 1.0
Введение
Архитектура
Установка
Работа с COM-объектами в PL/SQL
Заключение

Введение

Впервые COM Automation Feature появилась в Oracle 8i на платформе Microsoft Windows и сразу же привлекла внимание разработчиков. Эта нововведение позволяет на сервере базы данных Oracle в хранимых процедурах PL/SQL и Java использовать технологию Component Object Model (COM). С ее помощью разработчику предоставляется механизм доступа к свойствам и методам COM-объектов, причем как к локальным, так и к удаленным (DCOM).

Технология COM – это объектно-ориентированная спецификация, предложенная компанией Microsoft для упрощения интеграции различных программ между собой. Каждый COM-объект, который создается в соответствии с этой спецификацией, может взаимодействовать с другими COM-объектами. Одним из расширений этой технологией является так называемая “автоматизация” (Automation), позволяющая одному приложению программно управлять другим. В ней различаются клиентская часть (automation controller) и серверная часть (automation object). Сервер автоматизации – это объект, который управляется клиентом.

Архитектура

Центральным элементом технологии COM Automation является интерфейс IDispatch, позволяющий клиенту делать запросы к объекту-серверу. В обязательном порядке IDispatch обеспечивает следующую функциональность:

Oracle COM Automation Feature является оболочкой над интерфейсом IDispatch, и предоставляет доступ ко всем его методам через удобный PL/SQL API. Также доступ к интерфейсу IDispatch возможен из хранимых процедур Java (использование Java в данной статье не рассматривается).

На рисунке 1 показана архитектура Oracle COM Automation Feature.


Рисунок 1. Архитектура Oracle COM Automation Feature.

Программный интерфейс COM Automation Feature реализован через вызовы внешних процедур Oracle (external procedure).

Алгоритм работы выглядит следующим образом:

Установка


Рисунок 2. Выбор опции Oracle COM Automation Feature.

В процессе инсталляции, в каталог ORACLE_HOME\bin помещаются файлы динамических библиотек, все остальные файлы будут располагаться в каталоге ORACLE_HOME\com.

После создания экземпляра базы данных необходимо сконфигурировать сетевой прослушивающий процесс Oracle Listener. В случае использования инструмента Net Configuration Assistant, файлы listener.ora и tnsnames.ora создаются автоматически, но их можно создать и вручную. Далее приведены примеры этих файлов. 

#пример файла listener.ora
LISTENER =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = 
      (PROTOCOL = TCP)
      (HOST = myserver)
      (PORT = 1521)
    )
    (ADDRESS = 
      (PROTOCOL = IPC)
      (KEY = EXTPROC0)
    )
  )
)

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME=ORCL)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db)
    ) 
  )

#пример файла tnsnames.ora

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS =
        (PROTOCOL = IPC)
        (KEY = EXTPROC0)
    )
    (CONNECT_DATA = (SID = plsextproc))
    )
  )

Проверить правильность конфигурации можно, запустив утилиту LSNRCTL с параметром status – должен присутствовать сервис PLSExtProc. 

C:\>Lsnrctl status
. . . 
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

В ходе установки пакет ORDCOM, который предоставляет PL/SQL API для работы с COM, не компилируется. Разработчик должен скомпилировать его самостоятельно в схеме приложения.

Для демонстрации работы создадим пользователя testcom и дадим ему необходимые полномочия: 

CREATE USER testcom IDENTIFIED BY testcom
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

GRANT connect, resource TO testcom;
GRANT create library TO testcom;

Необходимо отметить, что пользователь, в схеме которого будет компилироваться пакет ORDCOM, должен иметь привилегию CREATE LIBRARY.

Войдем от имени вновь созданного пользователя в среду SQL*Plus и создадим пакет ORDCOM: 

CONNECT testcom/testcom;
@c:\oracle\product\10.2.0\db\com\comwrap.sql

Работа с COM-объектами в PL/SQL

В Oracle COM Automation Feature предоставляются следующие подпрограммы:

Общая схема работы с COM-объектом такова: 

DECLARE
  
  -- все методы возвращают результат работы - значение типа HRESULT
  -- в шестнадцатиричном формате имеет вид 0x800nnnnn в десятичном -214nnnnnnn
  hResult      binary_integer := 0;

  
  -- переменные для хранения ссылок на объекты
  AppHandle       binary_integer := -1;
  WorkbooksHandle binary_integer := -1;
  WorkbookHandle  binary_integer := -1;
  DummyHandle     binary_integer := -1;

BEGIN
  
  -- в качестве первого параметра передается 
  -- строковый идентификатор COM-объекта – 
  -- Program ID, имеющий соответствующий ему Globally Unique Identifier (GUID),
  -- 128-битное число, однозначно идентифицирующее COM-объект
  hResult := OrdCOM.CreateObject('Excel.Application', 0, '', AppHandle);
    
  -- обработка ошибок
  -- Вызов любого метода API(кроме самого GetLastError) очищает информацию о
  -- предыдущих ошибках
  IF ( hResult !=0 ) THEN
    OrdCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
    dbms_output.put_line ('Error creating application, aborting: ' || hResult);
    dbms_output.put_line(error_src);
    dbms_output.put_line(error_description);
    dbms_output.put_line(error_helpfile);
  END IF;  
  
  hResult := OrdCOM.GetProperty(AppHandle, 'WorkBooks', 0, WorkBooksHandle);
  
  -- Передача параметров в вызываемый метод
  -- инициализация коллекции параметров
  OrdCOM.InitArg();
  
  -- для вызова метода добавления новой рабочей книги WorkBooks.Add
  -- используется шаблон xlWBATWorksheet -4167, I4-тип данных
  -- вызов SetArg инициализирует первый параметр
  -- последующие вызовы будут инициализировать второй, третий и т.д параметры.
  OrdCOM.SetArg(-4167,'I4'); 

  hResult := OrdCOM.Invoke(WorkbooksHandle, 'Add',1,WorkbookHandle);    

  hResult := OrdCOM.Invoke(AppHandle, 'Quit', 0, DummyHandle);  
  
  -- уничтожение объекта
  hResult := OrdCOM.Destroy(AppHandle);
END;

Мы рассмотрели стандартную схему работы с COM-объектом. Компания Oracle облегчила работу тем разработчикам, которые будут использовать COM Automation Future для работы с файлами формата Microsoft Office (Word, Excel, PowerPoint), в каталоге ORACLE_HOME\com\demos имеются наглядные примеры и готовые пакеты процедурреализующие стандартные действия с учетом специфики каждого типа файла.

Например, пакет ORDExcel предоставляет следующие возможности для работы с MS Excel:

Пример использования в реальных проектах

Теперь посмотрим, как Oracle COM Automation Feature можно использовать в реальных проектах. Как разработчик хранилищ данных, я регулярно сталкиваюсь с ситуацией, когда часть необходимых для загрузки данных заказчик хранит в нескольких (иногда их большое количество) файлах формата MS Excel. Если формат листов представляет собой таблицу, то можно такой файл подключить к БД Oracle при помощи механизма General Connectivity или экспортировать в текстовый формат и загрузить данные утилитой SQL*Loader. Но в случае, когда формат сложный, скажем, в виде отчета, для его обработки необходимо написать парсер на каком-либо из высокоуровневых языков. Такое разделение ETL на клиентскую и серверную часть вносит дополнительное усложнение в процесс разработки. Использование же COM Automation дает возможность обойтись только сервером БД и языком PL/SQL.

Файл может содержать отчетность за несколько дней и имеет следующий формат:


Рисунок 3. Формат файла MS Excel для загрузки в СУБД Oracle.

Для работы с уже имеющимися файлами я добавил три функции в пакет стандартный пакет ORDExcel:

Исходный код указанных функций приведен ниже. 

FUNCTION CreateExcelApplication(servername VARCHAR2) RETURN binary_integer IS
BEGIN
  dbms_output.put_line('Creating Excel application...');
  i := OrdCOM.CreateObject(
    'Excel.Application', 0, servername,applicationToken);
  

  IF ( i != 0 ) THEN
    ORDCOM.GetLastError(
      error_src, error_description, error_helpfile, error_helpID);
    dbms_output.put_line(error_src);
    dbms_output.put_line(error_description);
    dbms_output.put_line(error_helpfile);
  END IF;
    
  dbms_output.put_line('Invoking Workbooks...');

  i := ORDCOM.GetProperty(applicationToken, 'WorkBooks', 0, WorkBooksToken);
  IF ( i != 0 ) THEN
    ORDCOM.GetLastError(
      error_src, error_description, error_helpfile, error_helpID);
    dbms_output.put_line(error_src);
    dbms_output.put_line(error_description);
    dbms_output.put_line(error_helpfile);
  END IF;
  

  RETURN i;
END CreateExcelApplication;

FUNCTION OpenExcelFile(filename VARCHAR2, sheetname VARCHAR2) 
  RETURN binary_integer IS
BEGIN
  dbms_output.put_line('Opening Excel file ' || filename ||' ...');
  ORDCOM.InitArg();
  ORDCOM.SetArg(filename, 'BSTR');

  i := ORDCOM.Invoke(WorkBooksToken, 'Open', 1, DummyToken);
  IF ( i != 0 ) THEN
    ORDCOM.GetLastError(
      error_src, error_description, error_helpfile, error_helpID);
    dbms_output.put_line(error_src);
    dbms_output.put_line(error_description);
    dbms_output.put_line(error_helpfile);
  END IF;

  dbms_output.put_line('Opening WorkBook');
  

  i := ORDCOM.GetProperty(
    applicationToken, 'ActiveWorkbook', 0, WorkBookToken);
  IF ( i != 0 ) THEN
    ORDCOM.GetLastError(
      error_src, error_description, error_helpfile, error_helpID);
    dbms_output.put_line(error_src);
    dbms_output.put_line(error_description);
    dbms_output.put_line(error_helpfile);
  END IF;

   dbms_output.put_line('Invoking WorkSheets..');

  i := ORDCOM.GetProperty(applicationToken, 'WorkSheets', 0, WorkSheetToken1);
  IF ( i != 0 ) THEN
    ORDCOM.GetLastError(
      error_src, error_description, error_helpfile, error_helpID);
    dbms_output.put_line(error_src);
    dbms_output.put_line(error_description);
    dbms_output.put_line(error_helpfile);
  END IF;

  dbms_output.put_line('Invoking WorkSheet');
  ORDCOM.InitArg();
  ORDCOM.SetArg(sheetname,'BSTR');
  
  i := ORDCOM.GetProperty(WorkBookToken, 'Sheets', 1, WorkSheetToken);
  -- можно получить ссылку на активный лист, а не на заданный именем
  -- i := ORDCOM.GetProperty(
  --   applicationToken, 'ActiveSheet', 0, WorkSheetToken);
  IF ( i != 0 ) THEN
    ORDCOM.GetLastError(
      error_src, error_description, error_helpfile, error_helpID);
    dbms_output.put_line(error_src);
    dbms_output.put_line(error_description);
    dbms_output.put_line(error_helpfile);
  END IF;

  dbms_output.put_line('Opened ');

  RETURN i;
END OpenExcelFile;

FUNCTION ConvertFormulaA1(
  formulaR1C1 VARCHAR2) RETURN VARCHAR2 IS
res VARCHAR2(10);
BEGIN
  ORDCOM.InitArg();
  ORDCOM.SetArg(formulaR1C1,'BSTR');
  ORDCOM.SetArg(-4150,'I4');
  ORDCOM.SetArg(-4151,'I4');
  
  i := ORDCOM.Invoke(applicationToken, 'ConvertFormula', 3, res);
  IF ( i != 0 ) THEN
    ORDCOM.GetLastError(
      error_src, error_description, error_helpfile, error_helpID);
    dbms_output.put_line(error_src);
    dbms_output.put_line(error_description);
    dbms_output.put_line(error_helpfile);
  END IF;
  

  RETURN res;
END ConvertFormulaA1;

Для хранения данных полученных из файла MS Excel создадим таблицу SHOPS_REPORTS_TBL 

CREATE TABLE SHOPS_REPORTS_TBL
(
  SHOP_NAME    VARCHAR2(32),
  MANAGER_NAME VARCHAR2(128),
  PERIOD       DATE,
  SALE         NUMBER(10,2),
  PURCHASE     NUMBER(10,2),
  CHARGES      NUMBER(10,2)
);

Следующий пример кода загружает данные из файла MS Excel с данными о продажах в таблицу SHOPS_REPORTS_TBL 

DECLARE 
  result INTEGER;
  range_count INTEGER;
  rec shops_reports_tbl%ROWTYPE;
BEGIN
  -- создаем объект Excel.Application
  result := ORDExcel.CreateExcelApplication('');
  -- открываем файл c:\load_data.xls и лист Sheet1
  result := ORDExcel.OpenExcelFile('c:\Data\Sales\load_data.xls', 'Sheet1');

  -- читаем Фамилию ответственного менеджера из ячейки B1
  rec.manager_name := ORDExcel.GetDataStr('B1');
  dbms_output.put_line(shop_name);
  
  -- читаем наименование магазина из ячейки B3
  rec.shop_name := ORDExcel.GetDataStr('B3');
  dbms_output.put_line(manager);
  
  -- цикл по 31 дню
  FOR range_count IN 4..34 LOOP
    BEGIN
      dbms_output.put_line(ORDExcel.ConverFormulaA1('R5C' || range_count) );
      -- т.к. данные расположены горизонтально в столбцах D5 E5 F5... то 
      -- ссылку задаем в стиле R1C1 а затем конвертируем в стиль A1
      rec.period := ORDExcel.GetDataDate(
        ORDExcel.ConverFormulaA1('R5C' || range_count));
      IF rec.period IS NULL THEN
        -- если данные заканчиваются раньше, выходим из цикла
        EXIT;
      END IF;
      dbms_output.put_line(measure_date);
      
      -- читаем значения показателей
      rec.sale     := ORDExcel.GetDataReal(
        ORDExcel.ConverFormulaA1('R6C' || range_count));
      rec.purchase := ORDExcel.GetDataReal(ORDExcel.ConverFormulaA1(
        'R7C' || range_count));
      rec.charges  := ORDExcel.GetDataReal(ORDExcel.ConverFormulaA1(
        'R8C' || range_count));
      
      -- записываем в таблицу данные за один день
      INSERT INTO shops_reports_tbl VALUES rec;
    END;
  END LOOP;
  COMMIT;
    
  result := ORDExcel.ExitExcel();
END;

Просмотрев содержимое таблицы, можно убедиться в правильности работы процедуры загрузки: 

SELECT * FROM shops_reports_tbl

SHOP_NAME  MANAGER_NAME     PERIOD       SALE       PURCHASE   CHARGES
---------------------------------------------------------------------------------------------
Химки-33  Иванова И.И.    01.01.2007  254584,00  67700,00   15000,00
Химки-33  Иванова И.И.    02.01.2007  348900,00  118500,00  16000,00

Заключение

С использованием COM Automation Future разработчики приложений для СУБД Oracle могут задействовать всю мощь технологии COM. Классическим примером использования служит работа с файлами MS Ofice в хранимых процедурах PL/SQL. Необходимо отметить, что COM Automation Future доступен только для СУБД Oracle на платформе MS Windows.

Любой из материалов, опубликованных на этом сервере, не может быть воспроизведен в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав.

Copyright © 1994-2016 ООО "К-Пресс"