SpellSQL - библиотека для прямого доступа к таблицам баз данных

При работе с базами 1С:Предприятие остро стоит вопрос о формировании SQL-запросов для получения данных. Такие запросы используются как внутри самой 1С так и для выгрузки/загрузки данных в другие БД. Существует много способов работы с SQL-запросами. Это и ADO, и библиотека Rainbow (Радуга), и ToySQL, и ODBCSQL. Все они наделены своими достоинствами и недостатками.

Когда я только начинал изучать язык SQL и его применение в 1С, то мое внимание остановилось на библиотеке ODBCSQL. Малое количество методов и способность возвращать все значения в виде строки делали ее очень интересной для начинающего пользователя. К тому же, с помощью этой библиотеки можно было легко получать данные из любых БД, а не только DBF или SQL, которые использует сама 1С, лишь бы был драйвер ODBC.

Однако в процессе работы выявились несколько недостатков, которые здорово осложняли жизнь в некоторых случаях.

Во-первых, значения полей можно получать только по порядку их следования в запросе. Не смертельно, но неприятно.

Во-вторых, если в запросе попадалось поле со значением типа NULL, то возвращалось значение предыдущего поля из этой выборки.

Однако эта библиотека очень широко используется в разработках. Вот и пришлось потратить немного времени и создать ее аналог SpellSQL.

Краткая информация.

Библиотека SpellSQL является аналогом ODBCSQL. Кроме процедур, которые содержатся в ODBCSQL, эта библиотека имеет несколько дополнительных методов и свойств (см. Список процедур).

Работать с базами, подключенными в монопольном режиме, по-прежнему нельзя. Подключаться к БД в монопольном режиме с помощью этой библиотеки также нельзя.

Компонента построена на ADODB. Поэтому Вам необходимо иметь на своей машине и ADO. Если на Вашей машине все-таки нет ADO, то его можно установить из пакета MSOffice или скачать из Internet. В литературе рекомендуют загрузить программу mdac_type.exe по адресу http://www.microsoft.com/data. Это самораспаковывающаяся программа установки. Содержит все компоненты необходимые для поддержки кода ADO на клиентском компьютере и не требует пользовательских установок.

Кроме этого, при подключении к базам 1С потребуется библиотека Cmpaund.dll. Она входит в этот архив. Не забудьте зарегистрировать ее.

Установка.

Скопируйте в каталог, где лежат исполняемые файлы 1С, обычно C:\Program Fils\1Cv77\BIN, файлы SpellSQL.dll, SpellSQL.als, Compound.dll. библиотеки необходимо зарегистрировать.
Regsvr32 <полный путь\>spellsql.dll

Regsvr32 <полный путь\>compound.dll

Для работы с разными источниками данных необходимо установить соответствующий ODBC драйвер. Для dbf таблиц 1С, рекомендуется использовать драйвер Visual FoxPro.

При переходе на новую библиотеку никаких особенных действий предпринимать не нужно. Достаточно просто поменять имя класса при создании объекта. Т.е. CreateObject(“ODBCSQL.RarusSQL”) = CreateObject(“SpellSQL.SQLtools”). Все остальные методы остались без изменений.

Использование.

Использование заключается в формировании запроса к базе на языке SQL и чтении информации, которую возвратит драйвер ODBC.
Пример. Выбрать всех сотрудников, фамилии которых начинаются на букву “C”.
SQL=CreateObject("SpellSQL.SQLtools");
source__="DRIVER=SQLServer;SERVER=MYSQL;UID=sa;DATABASE=calcsql;APP=1CV7";

Result = SQL.Connect(source__,"driver_connect",""); // установка соединения с базой

if Result=1 Then

Cmd=”select code from sc2 where descr like ‘C%’ and isfolder > 1”;

if SQL.ExecuteSQL(Cmd)=1 Then // выполняем запрос

// если успешно, последовательно читаем данные

While SQL.Fetch()=1 Do

Code=SQL.GetColByName(“Code”);

//что-то делаем...

//...

enddo;

Else

Message("Command execute failed");

endif;

SQL.CloseConnection(); // закрываем неиспользуемое соединение

endif;

Список свойств.

Название	Описание
Connection	Объект Connection ADODB
Command	Объект Command ADODB
RecordSet	Объект RecordSet ADODB
Errors	Объект Errors ADODB

Все свойства делались в расчете на использование в других языках программирования. Использование их в 1С не рассматривалось.

Список методов.

Название	Описание	Возврат
Int Connect(strDataSource, strUserName, strPassword)	Осуществляет соединение с базой данных. DateSource – источник данных, заданный при настройке ODBC (DSN source) или строка соединения (DSNless source). UserName – имя пользователя (DSN) или “driver_connect” (DSNless) Password – пароль пользователя (DSN) или пустая строка (DSNless)	1 – действие выполнено успешно 0 – ошибка
Int Connect1C(strDBcatalog, strUserName, strPassword)	Осуществляет соединение с базой данных 1С. DBcatalog – имя каталога ИБ 1С. UserName – имя пользователя 1С. Необязательный Password – пароль пользователя 1С. Необязательный. При использовании этой функции, если не указано имя пользователя или указан неверный пароль, происходит вызов диалога подключений к БД 1С. В этом диалоге можно изменить пользователя и ввести правильный пароль. При вводе верного пароля происходит соединение с БД 1С. При этом регистрируется соединение в фале регистрации соединений Links.tmp. До тех пор пока соединение будет активно, в мониторе будет видна запись об этом соединении. Все имена пользователей SpellSQL начинаются со SpellSQL_ (Например, SpellSQL_Админиcтратор) Формируется запись в журнале регистрации о соединении с БД.	1 – действие выполнено успешно 0 – ошибка
CloseConnection()	Закрывает соединение При уничтожении объекта, а так же при создании нового соединения происходит автоматическое отключение от текущего соединения, но не стесняйтесь лишний раз использовать этот метод при выходе из процедуры или закрытии формы. Формируется запись в журнале регистрации о завершении соединения с БД.
Int ExecuteSQL(strCommand)	Передает SQL запрос драйверу базы на выполнение. StrCommand – строка, содержащая SQL запрос к базе.	1 – действие выполнено успешно 0 - ошибка
Int Fetch(intDirect)	Проверяет, есть ли еще записи в выборке, и получает очередную запись. Direct – направление выборки. 0 – вперед, 1 – назад. При установке указателя в начало выборки (BOF) или в конец (EOF) возвращается 0. По умолчанию – 0.	1 – есть данные в выборке 0 – данных нет
String GetColName(intColumnNumber)	Получить имя поля текущей записи по номеру колонки в запросе	Имя заданного поля как строку
String GetColType(ColumnId)	Получить тип значения поля текущей записи по номеру колонки в запросе или по ее имени	Тип данных из заданного поля как строку
String GetCol(ColumnId)	Получить значение поля текущей записи по наименованию или номеру	Данные из заданного поля как строку
String GetColByName(strColumnName)	Получить значение поля текущей записи по наименованию	Данные из заданного поля как строку
String GetColByNum(intColumnNumber)	Получить значение поля текущей записи по номеру колонки в запросе	Данные из заданного поля как строку
String GetColByNum(intColumnNumber)	Получить значение поля текущей записи по номеру колонки в запросе	Данные из заданного поля как строку
String Mode1C()	Возвращает тип соединения с ИБ 1С. Имеет смысл только после использования метода Connect1C	“DBF”/”SQL”
Int NumCols()	Возвращает количество колонок в выборке.	Количество колонок
Long NumRec()	Возвращает количество записей в выборке.	Количество записей
Int StateSQL()	Возвращает состояние соединения SQL. Подробнее см. документацию ADO.	Состояние соединения
String ConnectStr()	Возвращает строку соединения.	Количество колонок
String B10toB36(lngNumber)	Переводит число из 10-ной системы счисления в 36-ричную	Число в 36-ричной системе
Long B36toB10(strNumber)	Переводит число из 36-ричной системы в 10-ную	Число в 10-ной системе
SaveQuery(intFlag)	Устанавливает/снимает флаг сохранения запроса. В ADO есть параметр сохранения запроса, который позволяет сохранить запрос до следующего использования. Я еще сам не очень разобрался, что он делает, но полагаю, что под SQL запрос будет оптимизирован и сохранен сервером. Flag – параметр. 0 – не сохранять запрос 1 – сохранять.	Нет

Нумерация колонок в выборке производится, начиная с 1 и до NumCols().

Примеры.

TestSpellSQL.ert

Универсальный отчет. Позволяет интерактивно выполнять SQL запросы к любой базе. Используются 3 вида подключений.

Все вопросы, замечания и пожелания можно присылать по электронной почте – spell@elsite.ru или [email protected]

С уважением, Шкапин Вадим (SpellKeeper)