Использование прямых SQL-запросов из прикладных проектов ТБ.Студии

Несмотря на то, что язык программирования ТБ.Скрипт не предоставляет прикладному разработчику стандартных средств использования языка SQL для работы с таблицами баз данных, в системе имеется возможность обращаться к таблицам с «прямыми» SQL-запросами в обход сервера данных. Для этого можно использовать механизмы библиотек ADO, с которыми можно работать через стандартные интерфейсы OLE Automation.

Для того, чтобы такой доступ был возможен, на клиентском рабочем месте (на котором работает код, написанный на ТБ.Скрипт) должны быть установлены клиентские библиотеки ADO. Они входя в состав СУБД. В частности, при работе с MS SQL Server для установки клиентских библиотек ADO можно воспользоваться инсталлятором MS SQL Server и выбрать при установке пункт «Client Tools».

Приведем пример процедуры, которая открывает прямой SQL-запрос и выбирает из него данные в массив.

proc OpenWorkAreaSQL(ИмяБД: Строка; SQLЗапрос :Строка; ИмяКартотеки :Строка=""; Пакет:Integer=0);
Var vConnection, vRecordset, vFields :AutoObject;
Var i :Integer;
  FRecordsCount = 0;
  ИмяТекКартотеки = ИмяКартотеки;
  vConnection = AutoObject.Create("ADODB.Connection");
  vConnection.ConnectionString = 
    "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;" +
    "Data Source=" + STD.Константы.ComputerName + ";Initial Catalog=" + 
    STD.Константы.DatabaseName + 
    ";Use Procedure for Prepare=1;" +
    "Auto Translate=True;Packet Size=16384;Application Name=TBADOLink;Workstation ID=STAND3X64;' + 
    'Use Encryption for Data=False;Tag with column collation when possible=False";
  try
    vConnection.Invoke("Open", vConnection.ConnectionString, '', '', STD.Константы.adConnectUnspecified);
    try
      vRecordset = AutoObject.Create("ADODB.Recordset");
      vRecordset.CursorLocation = STD.Константы.adUseServer;
      vRecordset.Invoke("Open", SQLЗапрос, vConnection, STD.Константы.adOpenForwardOnly, 
        STD.Константы.adLockReadOnly, STD.Константы.adCmdText);
      try
        while vRecordset <> nil and vRecordset.State = 0 do
          vRecordset = vRecordset.NextRecordset(i);
        end;
        vFields = vRecordset.Fields;
        FFieldsCount = Integer(vFields.Count);
        for i = 0..FFieldsCount - 1 do
          FFieldsName[i+1] = String(vFields.Item[i].Name);
        end;
        while not Logical(vRecordset.EOF) do
           FRecordsCount = FRecordsCount + 1;
           for i = 1..FFieldsCount do
             FData[i,FRecordsCount] = vFields.Item[i-1].Value;
           end;
           vRecordset.MoveNext;
        end;
      finally
        vFields = nil;
      end;
      vRecordset.Invoke("Close");
    finally
      vRecordset = nil;
      vConnection.Invoke("Close");
    end;
  finally
    vConnection = nil;
  end;
end;

Тут нужно пояснить параметры свойства ConnectionString объекта ADODB.Connection.

• Provider= - ADO провайдер. Для разных СУБД может быть разным. В случае с MS SQL - это "SQLOLEDB.1"
• Integrated Security=SSPI - Авторизация через учетную запись пользователя, под которым запущено приложение.

Если нужно подключиться к MS SQL под пользователем SQL сервера, то данный параметр заменяется на UserID=<User>; Password=<psw>;

• Data Source= - имя компьютера на котором расположен SQL-сервер. Если на компьютере несколько SQL-серверов, то нужно указать его Instance. Пример: Data Source=Stand2; Data Source=Stand2\SQL2005. Stand2 - это имя компьютера на котором расположен основной SQL-сервер. SQL2005 - дополнительный Instance SQL-сервера на компьютере Stand2. Особо надо отметить, что имя компьютера обязательно должно быть только из букв латинского алфавита и не начинаться с цифры. Если СУБД расположена на том же компьютере, что и клиент, то данный параметр можно опустить
• Application Name= - Имя приложения, которое будет отображаться в списке активных подключений в Enterprise Manager и в профилировщике
• Initial Catalog= - Имя базы данных

Нужно обратить внимание на свойство CursorLocation объекта ADODB.Recordset. Оно может принимать одно из значений: adUseClient - клиентский курсор, adUseServer - серверный курсор. Принципиальная разница этих 2 режимов заключается в том, с клиентским курсор все записи SQL-запроса сразу же после выполнения «выфетчиваются» на клиента, а с серверным курсором записи «выфетчиваются» на клиента по мере выполнения метода MoveNext. Отсюда вытекает следующее правило, что если заранее известно, что результат выполнения SQL-запроса возвращает небольшое число записей, например SELECT TOP, то нужно использовать клиентский курсор. Если предполагается, что SQL-запрос может вернуть много записей, то используется серверный курсор. Не каждый ADO-провайдер к СУБД и не каждая СУБД поддерживает серверные курсоры. MS SQL начиная с версии 7.0 sp1 поддерживает серверные курсоры.

Параметры метода Open объекта ADODB.Recordset имеют следующий смысл.

• adOpenForwardOnly - курсор однонаправленный без кэширования, что означает, что после метода MoveNext память от предыдущей записи освобождается, а метод MovePrev приведет к ошибке ADO
• adLockReadOnly - записи доступны только для чтения
• adCmdText - SQL-запрос передается в виде текста

Данная комбинация параметров является оптимальной для открытия запроса в MS SQL и требует минимально возможное количество ресурсов от ADO и MS SQL.

Так как «прямые» SQL-запросы, показанные на данном примере, идут в обход сервера данных ТБ, то данный подход имеет ряд отрицательных моментов. Поскольку сервер данных является мультиплексором, то он управляет количеством физических подключение к СУБД. На практике это означает, что даже когда работает 70 клиентов ТБ, то число реальных подключений к СУБД редко превышает 20-30. В данном же случае каждое клиентское место будет порождать от 1-ого и более подключений к СУБД, что в случае с 70 рабочими местами будет порождать от 70 подключений к СУБД. Это может оказаться неприемлемым решением для предприятия, где сервер СУБД с ограниченным числом лицензий и выбранной политикой лицензирования. Создание подключения к СУБД только на момент выполнения SQL-запроса требует накладных расходов и затрат по времени на каждое новое подключение.

Другой неприятный момент заключается в том, что «прямые» SQL-запросы резко повышают вероятность возникновения «мёртвых блокировок» (dead lock), которые на практике проявляются в зависании SQL-запроса или возникновении соответствующей ошибки в произвольных местах прикладного кода, где раньше вроде «как все работало». Для предотвращения подобной ситуации рекомендуется изменения в документах производить обычными средствами языка программирования ТБ.Скрипт, а выборку данных в «прямых» SQL-запросах сопровождать хинтом «WITH (NOLOCK)». Но даже при соблюдении данных рекомендаций вероятность возникновения dead lock остаётся.

Наконец, «прямые» SQL-запросы можно использовать для модификации данных. Если модифицируемые данные находятся в информационной базе, контролируемой сервером данных ТБ, то сделанные таким образом модифкации пойдут в обход и вразрез с алгоритмами сервера и нарушат нормальную работу алгоритмов репликации, логирования изменений, а также могут сказаться на работе сервера расчетов.