NAV+SQL: две подружки-хохотушки

Давным-давно, когда я и не подозревал о существовании Navision, занимался разработкой корпоративной системы одного логистического холдинга. По всем понятной терминологии – «самописки». Самописка была клиент-серверной, на связке Access 2000 + MS SQL 2000. Тогда же мне пришлось очень плотно подсесть на SQL, хранимые процедуры, функции, триггеры и т.д. и т.п. Фактически, SQL стал (да и остается по сей день) моим любимым языком. Я мог писать на нем что угодно, реализовывать любые алгоритмы, строить отчетность. И все написанное, что характерно, летало. А если вдруг переставало летать, то небольшая настройка индексов, плана исполнения ХП, обновленная статистика снова выводила базу в полет :)

По этой причине мое знакомство с Navision было очень болезненным –  там совершенно не было SQL :-( Некоторые наши сотрудники не смогли выдержать такого расклада, и ушли туда, где был сиквел, .NET и полная свобода творчества ;-)

А я остался… И какое-то время чуть ли не физически мучался. Мне просто не хватало кислороду! :-) И его, наверное, не хватило бы, но мысль о том, как подружить Navision с сиквелом, зрела (сервер БД-то по-прежнему был – SQL Server 2000). К сожалению, работать приходилось почти в полной изоляции, без общения с коллегами по цеху (в 2003 году было не очень много ресурсов на тему навижна). Это тоже сыграло свою роль – я просто тормозил.

Решение пришло само собой. Просто понадобилось сделать отчет с несколькими группировками, условиями – т.е. достаточно сложную вещь. На навижн – малореально и медленно. Выход один — хранимая процедура.

А теперь собственно, перейдем от лирики к физике. Вот функции кодеюнита, который отвечает за работу с SQL Server. Функции нужны следующие – создание подключения к сиквелу, выполнение произвольного SQL-запроса на сервере с получением ADODB.Recordset, далее функция по заполнению временной таблицы данными из полученного рекордсета.

OBJECT Codeunit 50001 SQL Management
{
  VAR
    adoCon1 : Automation "'Microsoft ActiveX Data Objects 2.8 Library'.Connection";
    Text001 : TextConst 'RUS=Не могу определить сервер, к которому подключена система!';
    Text002 : TextConst 'RUS=Не могу определить текущую базу данных!';
    Text003: TextConst 'RUS="Provider=sqloledb;data source=%1;database=%2;uid=%3;pwd=%4;"';
}

Для подключения к базе нам понадобятся виртуальные таблицы (2000000…), о которых можно почитать в рубрике «Уголок пытливого юзера».

PROCEDURE CreateNewConnection( VAR adoConnection: "Automation :
              'Microsoft ActiveX Data Objects 2.8 Library'.Connection");
VAR
  recServer : Record 2000000047; // Сервер
  recSession : Record 2000000009; // Сессия БД
  txtConnectionString : Text[250];
BEGIN

  WITH recServer DO BEGIN
    SETRANGE("My Server", TRUE);
    IF NOT FIND('-') THEN
      ERROR(Text001);
  END;

  WITH recSession DO BEGIN
    SETRANGE("Моя сессия", TRUE);
    IF NOT FIND('-') THEN
      ERROR(Text002);
  END;

  txtConnectionString := STRSUBSTNO(Text003,
                         recServer."Server Name",
                         recSession."Имя БД",
                         'user', 'password); // Эти вещи лучше вынести в настройки

  IF NOT ISCLEAR(adoConnection) THEN
    CLEAR(adoConnection);

  CREATE(adoConnection);
  adoConnection.ConnectionString(txtConnectionString);
  adoConnection.ConnectionTimeout(15);
  adoConnection.Open;
  adoConnection.CommandTimeout(60);
END;

Следующая функция выполняет SQL-запрос и возвращает результаты выполнения в рекордсете padoRec.
Не забудьте использовать SET NOCOUNT ON в начале хранимой процедуры :-)

Если запрос – это выполнение хранимой процедуры, и она возвращает несколько рекордсетов, то к каждому следующему можно получить доступ через свойство padoRec.NextRecordset.

PROCEDURE CreateQuery(ptxtSQL : Text[1024]; VAR padoRec : Automation
                      "'Microsoft ActiveX Data Objects 2.8 Library'.Recordset");
BEGIN
  //Получение ADODB.Recordset с результатами выполнения запроса txtSQL
  IF ISCLEAR(adoCon1) THEN
    CreateNewConnection(adoCon1);

  IF NOT ISCLEAR(padoRec) THEN
    CLEAR(padoRec);

  CREATE(padoRec);
  padoRec.Open(ptxtSQL, adoCon1, 3, 3, -1);
END;

Если ХП не возвращает результатов, либо они не важны, можно использовать следующую функцию (долгое время работы с ADP-проектами на Access дает о себе знать, там ведь тоже есть CurrentProject.Connection.Execute :-)

PROCEDURE ExecuteSQL(VAR ptxtQuery : Text[1024]);
BEGIN
  //Выполняет SQL-запрос (результаты не возвращаются)
  IF ISCLEAR(adoCon1) THEN
    CreateNewConnection(adoCon1);

  adoCon1.Execute(ltxtQuery);
END;

И последний шаг – заполнение временной таблицы данными из рекордсета. Ну тут все просто :-)

 ...
unitMSSQLMgt.CreateQuery(txtSQL, adoRec);

WITH recTmpCustomer DO BEGIN
  RESET;
  DELETEALL;

  IF adoRec.EOF THEN
    EXIT;

  WHILE NOT adoRec.EOF DO BEGIN
    INIT;
    "No." := adoRec.Fields.Item('Customer_No').Value;
    ...
     // Заполняем поля временной таблицы
     ...
    INSERT;
    adoRec.MoveNext;
  END;
END;

Далее, таблицу можно указать как источник данных для формы, и поехали :-) Код служит верой и правдой уже долго. Правда, на этом тема работы связки Navision+SQL не заканчивается, а только начинается. Нюансы…

На сегодня все. В следующей статье я расскажу вам о том, какие открытия ждали меня дальше.