Несмотря на то, что язык программирования ТБ.Скрипт не предоставляет прикладному разработчику стандартных средств использования языка 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.
Если нужно подключиться к MS SQL под пользователем SQL сервера, то данный параметр заменяется на UserID=<User>; Password=<psw>;
Нужно обратить внимание на свойство CursorLocation объекта ADODB.Recordset. Оно может принимать одно из значений: adUseClient - клиентский курсор, adUseServer - серверный курсор. Принципиальная разница этих 2 режимов заключается в том, с клиентским курсор все записи SQL-запроса сразу же после выполнения «выфетчиваются» на клиента, а с серверным курсором записи «выфетчиваются» на клиента по мере выполнения метода MoveNext. Отсюда вытекает следующее правило, что если заранее известно, что результат выполнения SQL-запроса возвращает небольшое число записей, например SELECT TOP, то нужно использовать клиентский курсор. Если предполагается, что SQL-запрос может вернуть много записей, то используется серверный курсор. Не каждый ADO-провайдер к СУБД и не каждая СУБД поддерживает серверные курсоры. MS SQL начиная с версии 7.0 sp1 поддерживает серверные курсоры.
Параметры метода Open объекта ADODB.Recordset имеют следующий смысл.
Данная комбинация параметров является оптимальной для открытия запроса в MS SQL и требует минимально возможное количество ресурсов от ADO и MS SQL.
Так как «прямые» SQL-запросы, показанные на данном примере, идут в обход сервера данных ТБ, то данный подход имеет ряд отрицательных моментов. Поскольку сервер данных является мультиплексором, то он управляет количеством физических подключение к СУБД. На практике это означает, что даже когда работает 70 клиентов ТБ, то число реальных подключений к СУБД редко превышает 20-30. В данном же случае каждое клиентское место будет порождать от 1-ого и более подключений к СУБД, что в случае с 70 рабочими местами будет порождать от 70 подключений к СУБД. Это может оказаться неприемлемым решением для предприятия, где сервер СУБД с ограниченным числом лицензий и выбранной политикой лицензирования. Создание подключения к СУБД только на момент выполнения SQL-запроса требует накладных расходов и затрат по времени на каждое новое подключение.
Другой неприятный момент заключается в том, что «прямые» SQL-запросы резко повышают вероятность возникновения «мёртвых блокировок» (dead lock), которые на практике проявляются в зависании SQL-запроса или возникновении соответствующей ошибки в произвольных местах прикладного кода, где раньше вроде «как все работало». Для предотвращения подобной ситуации рекомендуется изменения в документах производить обычными средствами языка программирования ТБ.Скрипт, а выборку данных в «прямых» SQL-запросах сопровождать хинтом «WITH (NOLOCK)». Но даже при соблюдении данных рекомендаций вероятность возникновения dead lock остаётся.
Наконец, «прямые» SQL-запросы можно использовать для модификации данных. Если модифицируемые данные находятся в информационной базе, контролируемой сервером данных ТБ, то сделанные таким образом модифкации пойдут в обход и вразрез с алгоритмами сервера и нарушат нормальную работу алгоритмов репликации, логирования изменений, а также могут сказаться на работе сервера расчетов.