Получение информации из баз данных, поддерживающих ODBC-драйверы
В Domino с версии 4 существуют три функции - @DbColumn, @DbLookup и @DbCommand - позволяющие извлекать информацию из баз данных, поддерживающих ODBC-драйверы. Обратите внимание, что первые две функции (@DbColumn, @DbLookup) позволяют только извлекать данные, но не добавлять, удалять или изменять их. Извлекается при этом из таблицы не более чем одна колонка значений. @DbCommand - позволяет выполнять команды, поддерживаемые конкретным ODBC-драйвером (в частности и удаление, и модификацию данных), и возвращать полученный результат.
ODBC (Open DataBase Connectivity) - интерфейс, который позволяет приложениям получать данные от систем управления базами данных (DBMS).
@DbColumn, @DbLookup и @DbCommand могут возвращать не более 64 Kб данных. Для определения количества данных, которые могут быть возвращены, воспользуйтесь следующими соотношениями:
- если возвращается текст: 2 + (2 * количество возвращенных записей) + общий размер текста во всех записях. Каждая текстовая строка имеет максимальную длину 511 байт; если возвращается только одна текстовая строка, она может иметь длину до 64 Кб.
- если возвращаются числа или даты: (10 * количество возвращенных записей) + 6 .
- пустая строка или опущено – в этом случае результаты будут "кэшированы", т.е. сохранены в виртуальной памяти для повторного использования. Каждое последующее обращение за этой информацией в рамках одной сессии (пока база данных, использующая формулу, остается открытой) будет адресовано к именно этим сохраненным данным;
- "NoCache" заставляет каждый раз извлекать текущую информацию из базы;
- "ReCache" позволяет обновить кеш с момента последнего кеширования (добавлено в R 6).
- "Fail" (Ошибка) - если встретится пустое значение, будет сгенерировано сообщение об ошибке "Null values found - canceling @Db function". Работа функции на этом завершится, и никакие данные не будут ею возвращены;
- "Discard" (Отбросить) - если встретится пустое значение, оно должно быть отброшено. Если при работе функции @DbColumn были отброшены какие-либо пустые значения, в строке состояния Notes "промелькнет" сообщение "Warning: NULL values discarded from @Db list". Работа функции завершится нормально, и она возвратит список без отброшенных значений;
- "Заменяющее значение" - если встретится пустое значение, оно должно быть заменено на указанное. Заменяющее значение по смыслу должно соответствовать типу данных колонки; например, для числовой колонки допустимо значение "311", но недопустимо "ABC". При этом заменяющее значение формально задается как текстовая строка (даже если заменяющее значение - число или дата). Если при работе функции @DbColumn происходили замены, в строке состояния Notes "промелькнет" сообщение "Warning: NULL value replaced with user-defined value in @Db list". Работа функции завершится нормально, и она возвратит список, в котором вместо пустых значений присутствуют их заменяющие. Учтите только следующий момент. Если в формуле запрошена сортировка, ODBC-драйвер сортирует список возвращаемых значений перед выполнением в нем замен. Во время сортировки все пустые значения обычно попадают в начало или конец списка. Они заменяются уже по окончании сортировки. Иногда это может привести к неправильной сортировке в возвращаемом функцией списке. Например, если было указано заменяющее значение "ххх", все "ххх" будут находиться в начале списка, если последний отсортирован в порядке возрастания.
- формула выполняется быстрее, поскольку дополнительные операции выполняются вне Domino;
- поскольку повторяющиеся значения удаляются ODBC-драйвером, в Domino может быть возвращено больше уникальных значений.
- "" - отсутствие сортировки;
- "Ascending" - сортировка в порядке возрастания;
- "Descending" - сортировка в порядке убывания.
- запрос на языке SQL ( при этом нужно использовать синтаксис SQL, "понятный" целевой DBMS);
- выражение на языке управления целевой DBMS (если целевая DBMS имеет собственный язык управления);
- имя хранимой в целевой DBMS процедуры.
J @DbColumn( "ODBC": "тип_кеша";
"источник_данных"; "ID_1":"ID_2"; "пароль_1":
"пароль_2"; "таблица"; "колонка":
"нуль_поддержка"; "Distinct": "сортировка" )
Область применения: нельзя использовать в формулах отбора, колонок, видимости объекта, редактируемой секции и заголовка окна. Возвращаемое значение не должно превышать 64 Кбайта. Не работает при установке переменной окружения NoExternalApps равной 1.
Использует для доступа к соответствующей таблице и колонке ODBC-драйвер, соответствующий указанному источнику данных. Извлекает значения из одной колонки в таблице. Если значений несколько, возвращает их как список. Дополнительно можно указать, должен ли возвращаемый список значений быть сортированным, следует ли удалять в нем повторяющиеся значения и как необходимо поступать с пустыми значениями. Основным использованием @DbColumn являются формулы ключевых слов.
Обратите ВНИМАНИЕ!!!, что @DbColumnn может только «извлекать» данные из сторонних источников, но не изменять их (удалять, добавлять, редактировать).
Параметр "ODBC" указывает, доступ к "чужой" (не Notes) базе данных должен осуществляться с помощью ODBC-драйверов. Это обязательный параметр.
Параметр тип_кеша может принимать следующие значения:
Параметр "источник_данных" задает имя внешнего источника данных. Источник данных содержит сведения о типе и местонахождении одной или нескольких таблиц базы данных. Имя источника данных должно задаваться, как текстовая строка и может содержать до 32 алфавитно-цифровых символов. @DbColumn обеспечивает доступ только к уже зарегистрированным источникам данных. Регистрация и модификация источников данных производится приложением настройки ODBC-драйверов, которое в MS Windows, Windows 95, Windows 98 и Windows NT имеется в контрольной панели (Control Panel), для Windows 2000 (Control Panel -> Administrative Tools).
Рис. 6.9 Пиктограмма и окно приложения настройки ODBC-драйверов
Параметр "ID_1" : "ID_2"
представляет собой текстовый список, содержащий идентификаторы пользователя, осуществляющего доступ к источнику данных. Параметр "пароль_1" : "пароль_2" является текстовым списком, содержащим пароли соответственно для идентификаторов "ID_1" и "ID_2". В зависимости от того, к источнику какого типа Вы обращаетесь, может потребоваться как ни одного, так и один или два идентификатора и соответствующее количество паролей. Если идентификатор вообще не нужен, укажите в качестве значений для параметров "ID_1" : "ID_2" и "пароль_1" : "пароль_2" пустую строку (""). Если необходим только первый идентификатор, можно опустить элемент списка "ID_2". Соответственно, при этом нет необходимости задавать и второй пароль. Если какой-либо идентификатор или пароль необходим, но Вы укажете для него значение "", пользователь получит диалоговое окно для ввода недостающего идентификатора и/или пароля.
Идентификаторы пользователя и пароли для доступа к источнику данных требуются только один раз на сеанс работы с базой данных Notes. Если база данных содержит несколько формул для доступа к тому же источнику данных, за все время, пока база данных Notes открыта, пользователю придется ввести свои идентификаторы и пароли только один раз. Но если пользователь откроет другую базу данных Notes и вызовет функцию, требующую доступа к тому же источнику данных, ему опять потребуется ввести идентификаторы пользователя и пароли. Однако, если Вы используете @DbColumn в фоновом агенте, Вы должны включить в формулу все необходимые идентификаторы и пароли, поскольку фоновые агенты не могут запрашивать информацию.
Параметр "таблица" задает таблицу, к которой осуществляется доступ. Это текстовая строка. Для некоторых типов источников параметр "таблица" может задаваться в формате "имя_владельца.имя_таблицы". Параметр "таблица" может также ссылаться не реальную таблицу, а на представление (виртуальная таблица, строки которой в базе данных не существуют, но основаны на данных реально существующей таблицы базы данных - термин View в DBMS [DataBase Managment System]).
Колонка, из которой следует извлечь данные, указывается параметром "колонка". Это тоже текстовая строка.
Параметр "нуль_поддержка"
определяет, как при извлечении данных должны обрабатываться пустые значения. Способ обработки пустых значений может быть одним из следующих:
Если необходимо устранить повторяющиеся значения из списка перед его возвратом Notes, используйте параметр "Distinct".
Ключевое слово "Distinct" является аналогом функции Domino @Unique, за исключением того, что повторяющиеся значения удаляются ODBC-драйвером перед возвратом данных в Domino. Использование "Distinct"
вместо @Unique имеет два преимущества:
Не все ODBC-драйверы поддерживают режим "Distinct". Если это так, указав "Distinct", Вы можете иногда получить одно нулевое значение.
Параметр "сортировка" задает способ сортировки значений ODBC-драйвером. Однако не все ODBC- драйверы его поддерживают. Можно использовать один из следующих вариантов:
На возвращаемые значения @DbColumn в формате ODBC существует аналогичное БД Domino ограничение в 64 Кбайт. Порядок расчета числа возвращаемых записей так же аналогичен @DbColumn с БД Domino
Пример. Источник данных "DBASE Files" содержит несколько баз данных формата dBase IV. Идентификатор и пароль не требуются. Выбираются только уникальные названия фирм из колонки Company в таблице из файла Customer.dbf. Пустые значения отбрасываются. Возвращаемые значения отсортированы по возрастанию.
@DbColumn( "ODBC"; "DBASE Files"; ""; "";
"Customer"; "Company" : "Discard"; "Distinct" :
"Ascending" )
J @DbLookup( "ODBC": "тип_кеша";
"источник_данных";"ID_1": "ID_2"; "пароль_1":
"пароль_2"; "таблица"; "колонка":
"нуль_поддержка"; "ключевая_колонка"; ключ;
"Distinct" : "сортировка")
Область применения: нельзя использовать в формулах отбора, колонок, заголовка окна видимости объекта, редактируемой секции и почтовых агентов. Возвращаемое значение не должно превышать 64 Кбайта. Не работает при установке переменной окружения NoExternalApps равной 1.
Использует соответствующий указанному источнику данных ODBC-драйвер для доступа к соответствующей таблице и колонкам. Извлекает и возвращает значения из указанной колонки, принадлежащие к тем записям, у которых значение в ключевой колонке совпадает с указанным параметром ключ. Если значений несколько, возвращает их как список. Дополнительно можно указать, должен ли возвращаемый список значений быть сортированным, следует ли удалять в нем повторяющиеся значения и как необходимо поступать с пустыми значениями. @DbLookup предназначена для выбора значений из записей, содержащих указанный ключ.
Параметры "ODBC": "тип_кеша"; "источник_данных"; "ID_1": "ID_2"; "пароль_1": "пароль_2"; "таблица"; "колонка": "нуль_поддержка" и параметр "Distinct": "сортировка" синтаксически и семантически эквивалентны соответствующим параметрам функции @DbColumn (ODBC). Единственное отличие - из колонки выбираются не все, а только требуемые значения.
Параметр "ключевая_колонка"
указывает, в какой колонке следует искать заданный ключ. Имя ключевой колонки должно задаваться как текстовая строка. Значения в ключевой колонке не обязательно должны быть сортированы. Ключ может быть величиной любого типа: текстовой строкой, числом, значением типа дата/время, любым списком. Значение даты и времени следует задавать в формате, который поддерживает база данных-источник и конкретный ODBC-драйвер, а не Domino; например для DB2 нужно указать "1996-01-31-12.00.00", а не "1996-01-31-12:00:00". Ключевая колонка и ключ вместе составляют как бы часть WHERE ("где") в выражении на языке SQL для выбора записей:
SELECT "колонка" FROM "таблица" WHERE "ключевая_колонка" = ключ
Функция выполняет сравнение ключа и значения в ключевой колонке и возвращает данные только из тех записей, у которых значения в ключевой колонке точно соответствуют заданному ключу. В качестве значения ключа может быть задан список. Это позволяет выбрать данные только из тех записей, у которых значения в ключевой колонке точно соответствуют одному из элементов списка.
Пример. Источник данных "dBASE Files" содержит несколько баз данных формата dBase IV. Идентификатор и пароль не требуются. Данные выбираются из таблицы в файле Customer.dbf. Приведен фрагмент этой таблицы. Необходимо по названию фирмы (Company) определять сотрудника, отвечающего за контакты с этой фирмой (Contact).
Custmr_id |
… |
Company |
… |
Contact |
… |
ALWAO |
Always Open Quick Mart |
Melissa Adams |
|||
ANDRC |
Andre's Continental Food Market |
Heeneth Ghandi |
|||
BLUEL |
Blue Lake Deli & Grocery |
G.K.Chattergee |
|||
CACTP |
Cactus Pete's Family Market |
Murray Soderholm |
|||
FAMIC |
Family Corner Market |
April Cienkewicz |
Ключевой колонкой является Company, а информация выбирается из колонки Contact. Выбираются только уникальные имена сотрудников. Пустые значения отбрасываются. Возвращаемые значения отсортированы по возрастанию. Ключ берется из поля Key. Если Key = "Andre's Continental Food Market", будет возвращено "Heeneth Ghandi". Если Key = "Andre's Continental Food Market": "Family Corner Market", будет возвращено "April Cienkewicz": "Heeneth Ghandi".
@DbLookup( "ODBC"; "dBASE Files"; ""; ""; "Customer";
"Contact" : "Discard"; "Company"; Key; "Distinct":
"Ascending" )
J @DbCommand( "ODBC" : тип_кеша;
"источник_данных"; "ID_1" : "ID_2"; "пароль_1" :
"пароль_2"; "командная_строка" :
"нуль_поддержка" )
Область применения: нельзя использовать в формулах отбора, колонок и всплывающих окон. Возвращаемое значение не должно превышать 64 Кбайта. Не работает при установке переменной окружения NoExternalApps
равной 1.
Передает обслуживающему источник данных ODBC-драйверу указанную в командной строке команду. ODBC-драйвер находит соответствующую указанному источнику данных DBMS, передает ей указанную команду для обработки и возвращает (или нет) данные, извлеченные с помощью этой команды. Если в результате выполнения командной_строки
возвращаемых значений больше чем одна колонка данных (например, SELECT * FROM Table), то в Notes передается только первая колонка как список. Возвращаемое значение может и отсутствовать, если командная строка отлична от оператора SELECT.
Параметры "ODBC": тип_кеша; "источник_данных"; "ID_1": "ID_2"; "пароль_1": "пароль_2"
и параметр "нуль_поддержка" синтаксически и семантически эквивалентны соответствующим параметрам функции @DbColumn (ODBC).
"Командная_строка" может быть одним из следующих:
Параметр "командная_строка" должен быть строкой текста. Если потребуются кавычки внутри командной строки, используйте вместо внутренних кавычек апостроф.
В документации приводится такое предупреждение: “Обратите ВНИМАНИЕ !!!, что если вы используете результат работы @DbCommand в качестве формулы ключевых слов для поля с интерфейсом типа CheckBox или Radio button, то документ будет обновляться только при либо при создании документа, либо при его загрузке на редактирование, в отличие от стандартного интерфейса для списков, когда осуществляется обновление поля при пересчете полей документа“. Однако из опыта можно сказать следующее, что при выборе опции "NoCache"
или "ReCache" в @DbCommand, и установке свойства Refresh choices on document refresh (Обновлять пункты меню при обновлении документа) у соответствующего поля, происходит реальное обновление значений независимо от типа интерфейса поля (автор, разумеется, не проверял это утверждение на всех доступных драйверах ODBC, но для FoxPro и DBASE – это так).
Пример 1. Используется база данных "pubs", которая включена в качестве примера в комплект поставки Microsoft SQL Server. Формула использует ODBC-драйвер для доступа к источнику данных PUBLISHERS, находит таблицу "authors", принадлежащую пользователю "dbo", а затем извлекает список имен из колонки "au_lname" для авторов, проживающих в Калифорнии (state='CA'), с которыми был подписан контракт (contract=1). Заметьте, что строка CA, заключенная в командной строке в апострофы, передается в DBMS ODBC-драйвером заключенной в кавычки, как того требует синтаксис SQL.
@DbCommand( "ODBC"; "PUBLISHERS"; "dbo" : ""; "vanilla" :
""; "SELECT au_lname FROM dbo.authors WHERE contract=1
AND state='CA' " )
Пример 2. Источник данных "FoxPro Files" содержит базу данных ROOMS формата FoxPro 2.6. Идентификатор и пароль не требуются. Формула изменяет значения поля NAME в этой базе на '123' для тех записей, для которых значение поля CODE равно '06'. Возвращаемое значение отсутствует.
@DbCommand( "ODBC": "NoCache"; "FoxPro Files"; "";
""; "UPDATE ROOMS SET NAME='123' where
CODE = '06' " );