1.1 ОБЩИЕ ПРИНЦИПЫ РАБОТЫ С SQL

ESQL/C - инструмент разработки приложений для пользователей, желающих разрабатывать программный код на языке С возможностью пользоваться языком SQL. При создании программы пишется С-программа, в нее включается заголовочные файлы, SQL-описания, запускается препроцессор, который преобразует SQL-описания в С-код. После этого полученный С-код компилируется и линкуется.

Общим моментом для всех языков, использующих встроенный SQL (ESQL/C, 4GL, SPL) являются

• можно встраивать операторы SQL в исходную программу так, как если они были бы выполняемыми оператора основного языка;
• можно использовать программные переменные в выражениях SQL таким же образом, каким используются литерные значения.

В ESQL/C операторы SQL записываются с помощью предшествующего ему символа $, либо слова exec SQL.

Выполнение оператора SQL фактически является вызовом сервера БД как отдельной программы. Информация должна передаваться от программы-клиента к программе-сервера и возвращаться обратно. Часть этих взаимодействий осуществляется через т.н. главные переменные (Host-переменные, собственные переменные). В объявлении главных переменных в ESQL/C им предшествует знак $ или описание производится внутри блока:

Exec SQL begin declare section . . . . . Exec SQL end declare section

Тип и класс хранения главных переменных определяется аналогично С (automatic, static, external). Как и в С возможны массивы (одно-, двумерные), структуры и указатели главных переменных. Инициализация переменных также аналогична С.

При использовании главных переменных в SQL-операторах им предшествует знак $ или : (стандарт ANSI). Вне описаний главные переменные используются обычным для С способом без какого-либо префикса.

Ниже приведено соответствие между ESQL/C-переменными и С-переменными:

SQL-тип	SQL/C-тип	C-тип
Char(n)		Char array[n+1]
Date	Date	Long int
Time		Long int
Datetime	Datetime	Long int
Decimal	Decimal	Short int
Float		Double
Interval	Interval	Long int
Serial		Long int

Собственные переменные могут быть использованы как параметры функции посредством указания ключевого слова 'parameter'.

Исходный файл с программой на встроенном SQL (ESQL/C) должен иметь расширение .ec (например, source.ec).

Вызов компилятора ESQL/C при работе с СУБД PostgreSQL выполняется командой pgcci. Ниже приведен упрощенный вариант синтаксиса команды pgcci (трансляция файла с программой на ESQL/C в стандарте СУБД Informix):

pgcci <имя_файла_без расширения>

1.2. ОБРАБОТКА ОШИБОК

Сервер базы данных всегда возвращает код ошибки и некоторую другую информацию о выполнении операции в структуре данных, называемой областью связи SQL (SQL Communication Area - sqlca).

Поле sqlcode структуры sqlca содержит основной код возврата.

sqlca.sqlcode = 0 - успешное выполнение;

sqlca.sqlcode < 0 - код ошибки (детализированное значение кода ошибки может содержаться во втором элементе поля sqlerrd;

0< sqlca.sqlcode < 100 - в зависимости от описания;

sqlca.sqlcode = SQLFOUND - больше не найдено строк (#define SQLFOUND 100).

1.3. ВЫБОРКА ЕДИНСТВЕННОЙ СТРОКИ

Процедуру выборки единственной строки легко можно пояснить следующим примером:

Единственное отличие записанных конструкций от классического SQL - наличие спецификатора into. Этот спецификатор задает главные переменные, в которые помещаются данные. Главные переменные используются как получатели данных и в спецификаторе Where.

Замечания.

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

1.4. ОБРАБОТКА NULL-ЗНАЧЕНИЙ

Поскольку в языке С нет возможности убедиться, имеет ли элемент таблицы какое-либо значение, ESQL/C делает это для своих главных переменных., называемых переменными-индикаторами. Переменная-индикатор является дополнительной переменной, ассоциированной с главной переменной, в которую могут поступать NULL-значения. Когда сервер БД помещает данные в главную переменную, он также устанавливает специальное значение в переменную-индикатор, которое показывает, не являются ли эти данные NULL-значением.

Переменная-индикатор описывется как обычная главная переменная целого типа, а при использовании отделяется от главной переменной, в которую передаются значения, знаками ":", "$" или словом indicator.

Примеры равнозначных использований переменных-индикаторов:

При выборе оператором Select NULL-значения переменная-индикатор (если она используется) получает значение -1. В случае нормального возврата переменной индикатор равен 0. В случае, если индикатор не используется, то результат зависит от режима генерации программы:

• если программа компилировалась с флагом -icheck, ESQL/C генерирует ошибку и установит sqlca.sqlcode в отрицательное значение при возврате NULL-значения;
• если программа компилировалась без указанного флага, при отсутствии индикатора ошибка не генерируется.

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

Замечание: функция rstrdate() в приведенном примере присваивает главной переменной значения по умолчанию, конвертируя строку в дату.

Ниже приведен набор наиболее распространенных функций работы с датами:

1. int rdatestr(date jdate,char *str); - преобразование даты из внутреннего представления в строку;

2. int rstrdate(char *str, long *jdate); - преобразование даты во внутреннее представление из строки;

3. int rdayofweek(long jdate): - возвращает день недели из внутреннего представленя даты;

4. int rjulmdy(long jdate, short mdy[3]); - создает массив из трех целых, содержащих месяц, день и год из внутреннего представления даты;

5. int rmdrjul(short mdy[3],long jdate); - создает внутреннее представление даты из массива из трех целых чисел (месяц, день и год);

6. void rtoday(long *today); - помещает текущую дату в ее внутреннее представление.

1.5. ОБРАБОТКА НЕСКОЛЬКИХ СТРОК

Обработка многострочного запроса осуществляется в два этапа:

• Программа запускает запрос, не возвращая никаких данных;
• Программа запрашивает строки данных по одной на каждое требование.

Указанные операции выполняются с помощью специального объекта данных, называемых курсором, который является структурой данных, отслеживающей внутреннее состояние запроса. Последовательность программных операций при работе с курсором:

1. Программа объявляет курсор и ассоциированный с ним оператор Select. Операция приводит к выделению памяти для хранения курсора (оператор Declare)
2. Программа открывает курсор. Это приводит к началу выполнения ассоциированного оператора Select, а также к распознаванию наличия ошибок (оператор Open)
3. Программа считывает строку данных в главные переменные и обрабатывает их (оператор Fetch)
4. Программа закрывает курсор после прочтения последней строки (оператор Close)

Множество строк, возвращаемое предложением Select, носит название активного множества.

Курсор может находиться в одном из двух состояний: открытом и закрытом. Когда курсор открыт, он связан с активным множеством и может указывать либо на текущую строку, либо между строк, либо перед первой строкой, либо после последней строки. Если курсор находится в закрытом состоянии, то он не связан с активным множеством, хотя и остается связанным с предложением Select.

а) объявление курсора

Курсор объявляется с помощью оператора Declare. Этот оператор задает имя курсора, специфицирует его исполнение и ассоциирует курсор с оператором Select.

В приведенном фрагменте оператор Declare связан с оператором Select, записанным строкой ниже.

Оператор Declare устанавливает свойства курсора, выделяет под него память для последующего его использования.

Курсор может задаваться в двух режимах: последовательном и скроллирующем. Последовательный курсор позволяет просматривать активное множество только в последовательном порядке, а также при необходимости удаления или модификации активного множества. Скроллирующий курсор позволяет просматривать строки из активного множества в произвольном порядке.

Пример объявления скроллирующего курсора.

б) открытие курсора

По оператору Open активизируется курсор, ассоциированный с ним оператор Select передается серверу, который начинает поиск строк до момента конструирования первой строки результата (упреждающий поиск). Оператор не возвращает строк и устанавливает код возврата в sqlcode.

Код возврата sqlcode=0 означат, что оператор Select синтаксически правилен и курсор готов для использования.

в) курсорная выборка строк

Оператор Fetch именует курсор, переводит его к определенной строке активного множества и выбирает значения из этой строки, а т.ж. может задавать имена главных переменных. Способ перехода от строки к строке определяется дополнительными спецификациями.

Имена главных переменных могут быть указаны в спецификации into либо в операторе Select, либо в операторе Fetch (но не в обоих сразу). Вторая форма имеет то преимущество, что разные строки могут быть считаны в разные переменные.

Последовательный курсор выбирает следующую строку из активного множества. Он может только один раз прочитать таблицу, будучи открытым один раз. Единственной дополнительной спецификацией, применимой к последовательному курсору, является значение по умолчанию next.

Пример использования последовательного курсора.

При формировании активного множества для последовательного курсора сервер, стараясь использовать как меньше ресурсов, сохраняет только одну строку, которая считается следующей.

Скроллирующий курсор выбирает строку активного множества, используя спецификации:

• next - выбирает следующую строку активного множества;
• previous (prior) - выбирает предыдущую строку активного множества;
• first - выбирает первую строку активного множества;
• last - выбирает последнюю строку активного множества;
• current - выбирает текущую строку акт. множества (ранее прочитанную);
• relative - выбирает n-ю строку относительно текущей позиции курсора из активного множества (n><=0);
• absolute - выбирает n-ю строку активного множества (нумерация с 1).

Примеры записи скроллирующих курсоров:

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

Пример использования скроллирующего курсора (выбор каждой второй строки из некоторого набора).

1.6. ДИНАМИЧЕСКИЙ SQL

С помощью динамического SQL выполняет формирование оператора SQL для его последующего выполнения. 3 этапа:

1. Программа собирает текст оператора SQL в виде символьной строки в программной переменной.
2. Она выполняет оператор Prepare, который обращается к серверу БД на предмет изучения текста оператора и подготовки его для выполнения.
3. Она использует оператор Execute или Fetch для выполнения подготовленного оператора.

а) Подготовка оператора.

Динамический оператор SQL похож на любой другой оператор SQL, за исключением того, что он не может содержать имена главных переменных. Это приводит к двум ограничениям:

• если динамический оператор является оператором Select, в него нельзя включать спецификатор Into (в нем д.б. главные переменные, а они не допускаются);
• в любом месте, где по смыслу должна стоять главная переменная, в качестве заполнителя указывается знак вопроса.

В операторе Prepare указывается идентификатор динамически

Пример.

Замечание: запрещенными для динамического формирования являются операторы, непосредственно связанные с динамическим SQL (Prepare, Execute) и управления курсором (Open, Fetch,...), а т.ж. операторы работы с базой данных (Create database, Database,...).

Результатом подготовки оператора является структура данных, представляющая этот оператор (это не строка символов), которая используется в дальнейшем при выполнении.

Оператор Prepare не ограничивает символьную строку одним оператором.

б) Выполнение подготовленного SQL

Подготовленный оператор можно многократно выполнять. Для выполнения операторов, отличных от Select, используется оператор Execute. В операторе Execute указывается идентификатор оператора и список главных переменных, подставляемых в оператор вместо знака вопроса.

Для выполнения динамически подготовленного оператора Select он подключается к курсору и в дальнейшем используется с помощью курсорных средств. Ниже приведен пример последовательности операторов, динамически подготавливающих оператор SQL и выполняющих его.

Последовательность выполнения приведенных выше операторов:

1. Символьная строка, содержащая оператор Select помещается в программную переменную select_2. Она содержит два заполнителя, отмеченных знаком "?".
2. Оператор Prepare преобразует символьную строку в структуру данных, связанную с именем q_orders.
3. Объявляется курсор cu_orders и связывается с именем подготовленного оператора.
4. При открытии курсора начинается выполнение подготовленного оператора. Спецификация using в операторе Open представляет имена двух главных переменных, содержимое которых заменяет знаки вопроса в выполняемом операторе.
5. Спецификатор into оператора Fetch специфицирует главные переменные, которые должны принимать значения столбцов строки, выбранной по курсору.

Замечание.

Указатели позиции "?" нельзя использовать вместо идентификаторов SQL, таких как имя БД, таблицы или столбца: эти идентификаторы должны указываться в тексте оператора при его подготовке. Если эти имена неизвестны при написании оператора, они м.б. получены через пользовательский ввод.

Последнее замечание поясняется следующим примером.

1.7. ПРИМЕРЫ ИСПОЛЬЗОВАНИЯ ВСТРОЕННОГО SQL

Пример 1. Использование журнализованных транзакций при выполнении операций.

Пример 2. Удаление строк, содержащих дубликаты записей.

Пример 3. Обновление с использованием курсора: замена имен в некоторой компании.