34.7. Использование областей дескрипторов #
Области дескрипторов SQL дают возможности для более сложной обработки результатов операторов SELECT
, FETCH
и DESCRIBE
. Область дескриптора SQL объединяет в одной структуре данные одной строки и элементы метаданных. Эти метаданные особенно полезны при выполнении динамических SQL-операторов, когда характер результирующих столбцов может быть неизвестен заранее. Postgres Pro предлагает два подхода к использованию областей дескрипторов: именованные области SQL-дескрипторов и области SQLDA в структурах C.
34.7.1. Именованные области SQL-дескрипторов #
Именованная область SQL-дескриптора состоит из заголовка, содержащего сведения обо всём дескрипторе, и одного или нескольких дескрипторов элементов, которые по сути описывают отдельные столбцы в строке результата.
Прежде чем вы сможете использовать область SQL-дескриптора, её нужно выделить:
EXEC SQL ALLOCATE DESCRIPTOR идентификатор
;
Заданный идентификатор играет роль «имени переменной» области дескриптора. Когда дескриптор оказывается ненужным, его следует освободить:
EXEC SQL DEALLOCATE DESCRIPTOR идентификатор
;
Чтобы воспользоваться областью дескриптора, её нужно указать в качестве целевого объекта в предложении INTO
, вместо перечисления переменных среды:
EXEC SQL FETCH NEXT FROM mycursor INTO SQL DESCRIPTOR mydesc;
Если набор результатов пуст, в области дескриптора будут тем не менее содержаться метаданные из запроса, то есть имена полей.
Получить метаданные набора результатов для ещё не выполненных подготовленных запросов можно, воспользовавшись оператором DESCRIBE
:
EXEC SQL BEGIN DECLARE SECTION; char *sql_stmt = "SELECT * FROM table1"; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE stmt1 FROM :sql_stmt; EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc;
До PostgreSQL версии 9.0 ключевое слово SQL
было необязательным, так что предложения DESCRIPTOR
и SQL DESCRIPTOR
создавали именованные области SQL-дескрипторов. Теперь оно стало обязательным; без слова SQL
создаются области SQLDA, см. Подраздел 34.7.2.
В операторах DESCRIBE
и FETCH
ключевые слова INTO
и USING
действуют примерно одинаково: они указывают вывести набор результатов и метаданные в область дескриптора.
Возникает вопрос: а как же получить данные из области дескриптора? Область дескриптора можно представить как структуру с именованными полями. Чтобы получить значение поля из заголовка и сохранить его в переменной среды C, нужно выполнить команду:
EXEC SQL GET DESCRIPTORимя
:переменная_среды
=поле
;
В настоящее время определено только одно поле заголовка: COUNT
, которое говорит, сколько областей дескрипторов элементов существует (то есть, сколько столбцов содержится в результате). Переменная среды C должна иметь целочисленный тип. Чтобы получить поле из области дескриптора элемента, нужно выполнить команду:
EXEC SQL GET DESCRIPTORимя
VALUEномер
:переменная_среды
=поле
;
В качестве num
можно задать обычное целое или переменную среды C, содержащую целое число. Допустимые поля:
CARDINALITY
(integer) #число строк в наборе результатов
DATA
#собственно элемент данных (тип данных поля зависит от запроса)
DATETIME_INTERVAL_CODE
(целое) #Когда
TYPE
равно9
,DATETIME_INTERVAL_CODE
содержит значение1
дляDATE
,2
дляTIME
,3
дляTIMESTAMP
,4
дляTIME WITH TIME ZONE
, либо5
дляTIMESTAMP WITH TIME ZONE
.DATETIME_INTERVAL_PRECISION
(целое) #не реализовано
INDICATOR
(целое) #индикатор (отмечающий значение NULL или усечение значения)
KEY_MEMBER
(целое) #не реализовано
LENGTH
(целое) #длина данных в символах
NAME
(строка) #имя столбца
NULLABLE
(целое) #не реализовано
OCTET_LENGTH
(целое) #длина символьного представления данных в байтах
PRECISION
(целое) #точность (для типа
numeric
)RETURNED_LENGTH
(целое) #длина данных в символах
RETURNED_OCTET_LENGTH
(целое) #длина символьного представления данных в байтах
SCALE
(целое) #масштаб (для типа
numeric
)TYPE
(целое) #числовой код типа данных столбца
В операторах EXECUTE
, DECLARE
и OPEN
ключевые слова INTO
и USING
действуют по-разному. Область дескриптора также можно сформировать вручную, чтобы передать входные параметры запросу или курсору, а команда USING SQL DESCRIPTOR
даёт возможность передать входные аргументы параметризованному запросу. Оператор, формирующий именованную область SQL-дескриптора, выглядит так: имя
EXEC SQL SET DESCRIPTORимя
VALUEномер
поле
= :переменная_среды
;
Postgres Pro поддерживает выборку сразу нескольких записей в одном операторе FETCH
и может сохранить их данные в переменной среды С, если эта переменная — массив. Например:
EXEC SQL BEGIN DECLARE SECTION; int id[5]; EXEC SQL END DECLARE SECTION; EXEC SQL FETCH 5 FROM mycursor INTO SQL DESCRIPTOR mydesc; EXEC SQL GET DESCRIPTOR mydesc VALUE 1 :id = DATA;
34.7.2. Области дескрипторов SQLDA #
Область дескриптора SQLDA представляет собой структуру языка C, в которую можно получить набор результатов и метаданные запроса. Одна такая структура содержит одну запись из набора данных.
EXEC SQL include sqlda.h; sqlda_t *mysqlda; EXEC SQL FETCH 3 FROM mycursor INTO DESCRIPTOR mysqlda;
Заметьте, что ключевое слово SQL
в этом случае опускается. Замечания относительно применения ключевых слов INTO
и USING
в Подразделе 34.7.1 применимы и здесь, с дополнением. В операторе DESCRIBE
можно полностью опустить ключевое слово DESCRIPTOR
, если присутствует ключевое слово INTO
:
EXEC SQL DESCRIBE prepared_statement INTO mysqlda;
Общая схема использования SQLDA выглядит так:
Подготовить запрос и объявить курсор для него.
Объявить SQLDA для строк результата.
Объявить SQLDA для входных параметров и инициализировать их (выделить память, задать параметры).
Открыть курсор с входной SQLDA.
Выбрать строки из курсора и сохранить их в выходной SQLDA.
Прочитать значения из выходной SQLDA в переменные среды (и преобразовать при необходимости).
Закрыть курсор.
Освободить область памяти, выделенную для входной SQLDA.
34.7.2.1. Структура данных SQLDA #
Для SQLDA используются три типа данных: sqlda_t
, sqlvar_t
и struct sqlname
.
Подсказка
Структура данных SQLDA в Postgres Pro подобна той, что используется в IBM DB2 Universal Database, так что часть технической информации по SQLDA в DB2 может быть полезна и для понимания устройства SQLDA в Postgres Pro.
34.7.2.1.1. Структура sqlda_t #
Тип структуры sqlda_t
представляет тип собственно SQLDA. Эта структура описывает одну запись. Две или более структур sqlda_t
могут объединяться в связанный список по указателям в поле desc_next
, и таким образом образовывать упорядоченный набор строк. Поэтому, когда выбираются две или более строк, приложение может прочитать их, проследуя по указателям desc_next
во всех узлах sqlda_t
.
Тип sqlda_t
определяется так:
struct sqlda_struct { char sqldaid[8]; long sqldabc; short sqln; short sqld; struct sqlda_struct *desc_next; struct sqlvar_struct sqlvar[1]; }; typedef struct sqlda_struct sqlda_t;
Его поля имеют следующее назначение:
sqldaid
#Содержит строковую константу
"SQLDA "
.sqldabc
#Содержит размер выделенного пространства в байтах.
sqln
#Содержит число входных параметров для параметризованного запроса, когда передаётся в операторы
OPEN
,DECLARE
илиEXECUTE
с ключевым словомUSING
. В структуре, выводимой операторамиSELECT
,EXECUTE
илиFETCH
, данное значение совпадает сsqld
.sqld
#Содержит число полей в наборе результатов.
desc_next
#Если запрос выдаёт несколько записей, возвращается несколько связанных структур SQLDA, а
desc_next
содержит указатель на следующую запись в списке.sqlvar
#Это массив столбцов в наборе результатов.
34.7.2.1.2. Структура sqlvar_t #
Тип структуры sqlvar_t
содержит значение столбца и метаданные, в частности, тип и длину. Эта структура определяется так:
struct sqlvar_struct { short sqltype; short sqllen; char *sqldata; short *sqlind; struct sqlname sqlname; }; typedef struct sqlvar_struct sqlvar_t;
Её поля имеют следующее назначение:
sqltype
#Содержит идентификатор типа данного поля. Возможные значения перечислены в
enum ECPGttype
вecpgtype.h
.sqllen
#Содержит двоичную длину поля, например 4 байта для
ECPGt_int
.sqldata
#Указывает на данные. Формат данных описан в Подразделе 34.4.4.
sqlind
#Указывает на индикатор NULL. 0 соответствует значению не NULL, -1 — NULL.
sqlname
#Имя поля.
34.7.2.1.3. Структура struct sqlname #
Структура struct sqlname
содержит имя столбца. Она включена в sqlvar_t
в качестве члена. Эта структура определена так:
#define NAMEDATALEN 64 struct sqlname { short length; char data[NAMEDATALEN]; };
Её поля имеют следующее назначение:
34.7.2.2. Получение набора результатов с применением SQLDA #
Чтобы получить набор результатов запроса через SQLDA, нужно проделать примерно следующее:
Объявить структуру
sqlda_t
для получения набора результатов.Выполнить команды
FETCH
/EXECUTE
/DESCRIBE
для обработки запроса с указанной SQLDA.Определить число записей в наборе результатов, прочитав
sqln
, член структурыsqlda_t
.Получить значения каждого столбца из элементов
sqlvar[0]
,sqlvar[1]
и т. д., составляющих массив, включённый в структуруsqlda_t
.Перейти к следующей строке (структуре
sqlda_t
) по указателюdesc_next
, члену структурыsqlda_t
.При необходимости повторить эти действия.
Далее показывается, как получить набор результатов через SQLDA.
Сначала объявите структуру sqlda_t
, в которую будет помещён набор результатов.
sqlda_t *sqlda1;
Затем укажите эту SQLDA в команде. В данном примере это команда FETCH
.
EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;
Обработайте все строки в цикле с переходом по связанному списку.
sqlda_t *cur_sqlda; for (cur_sqlda = sqlda1; cur_sqlda != NULL; cur_sqlda = cur_sqlda->desc_next) { ... }
Внутри этого цикла реализуйте ещё один цикл чтения данных каждого столбца (структуры sqlvar_t
) в строке.
for (i = 0; i < cur_sqlda->sqld; i++) { sqlvar_t v = cur_sqlda->sqlvar[i]; char *sqldata = v.sqldata; short sqllen = v.sqllen; ... }
Чтобы получить значение столбца, прочитайте значение поля sqltype
, члена структуры sqlvar_t
. Затем выберите подходящий способ, в зависимости от типа столбца, копирования данных из поля sqlvar
в переменную среды С.
char var_buf[1024]; switch (v.sqltype) { case ECPGt_char: memset(&var_buf, 0, sizeof(var_buf)); memcpy(&var_buf, sqldata, (sizeof(var_buf) <= sqllen ? sizeof(var_buf) - 1 : sqllen)); break; case ECPGt_int: /* integer */ memcpy(&intval, sqldata, sqllen); snprintf(var_buf, sizeof(var_buf), "%d", intval); break; ... }
34.7.2.3. Передача значений параметров через SQLDA #
Чтобы передать параметры подготовленному запросу через SQLDA, нужно проделать примерно следующее:
Создать подготовленный запрос (подготовленный оператор)
Объявить структуру sqlda_t в качестве входной SQLDA.
Выделить область памяти (структуру sqlda_t) для входной SQLDA.
Установить (скопировать) входные значения в выделенной памяти.
Открыть курсор, указав входную SQLDA.
Рассмотрим это на примере.
Сначала создайте подготовленный оператор.
EXEC SQL BEGIN DECLARE SECTION; char query[1024] = "SELECT d.oid, * FROM pg_database d, pg_stat_database s WHERE d.oid = s.datid AND (d.datname = ? OR d.oid = ?)"; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE stmt1 FROM :query;
Затем выделите память для SQLDA и установите число входных параметров в поле sqln
, члене структуры sqlda_t
. Когда для подготовленного запроса требуются два или более входных параметров, приложение должно выделить дополнительное место в памяти, размер которого вычисляется как (число параметров - 1) * sizeof(sqlvar_t). В показанном здесь примере выделяется место для двух параметров.
sqlda_t *sqlda2; sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t)); memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t)); sqlda2->sqln = 2; /* число входных переменных */
Выделив память, сохраните значения параметров в массиве sqlvar[]
. (Этот же массив используется для значений столбцов, когда SQLDA получает набор результатов.) В данном примере передаются два параметра: "postgres"
(строкового типа) и 1
(целочисленного типа).
sqlda2->sqlvar[0].sqltype = ECPGt_char; sqlda2->sqlvar[0].sqldata = "postgres"; sqlda2->sqlvar[0].sqllen = 8; int intval = 1; sqlda2->sqlvar[1].sqltype = ECPGt_int; sqlda2->sqlvar[1].sqldata = (char *) &intval; sqlda2->sqlvar[1].sqllen = sizeof(intval);
Откройте курсор с указанием ранее созданной SQLDA, чтобы входные параметры были переданы подготовленному оператору.
EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2;
Наконец, закончив использование входных SQLDA, необходимо явно освободить выделенную для них память, в отличие от SQLDA, получающих результаты запросов.
free(sqlda2);
34.7.2.4. Пример приложения, использующего SQLDA #
Представленный здесь пример программы показывает, как выбрать из системных каталогов статистику доступа к базам данных, определённых входными параметрами.
Это приложение соединяет записи двух системных таблиц, pg_database и pg_stat_database по OID базы данных, и также выбирает и показывает статистику, принимая два входных параметра (база данных postgres
и OID 1
).
Сначала создайте SQLDA для ввода параметров и SQLDA для вывода результатов.
EXEC SQL include sqlda.h; sqlda_t *sqlda1; /* выходной дескриптор */ sqlda_t *sqlda2; /* входной дескриптор */
Затем подключитесь к базе данных, подготовьте оператор и объявите курсор для подготовленного оператора.
int main(void) { EXEC SQL BEGIN DECLARE SECTION; char query[1024] = "SELECT d.oid,* FROM pg_database d, pg_stat_database s WHERE d.oid=s.datid AND ( d.datname=? OR d.oid=? )"; EXEC SQL END DECLARE SECTION; EXEC SQL CONNECT TO testdb AS con1 USER testuser; EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; EXEC SQL PREPARE stmt1 FROM :query; EXEC SQL DECLARE cur1 CURSOR FOR stmt1;
Затем запишите некоторые значения параметров во входную SQLDA. Выделите память для входной SQLDA и установите количество параметров в sqln
. Запишите тип, значение и длину значения в поля sqltype
, sqldata
и sqllen
структуры sqlvar
.
/* Создать структуру SQLDA для входных параметров. */ sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t)); memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t)); sqlda2->sqln = 2; /* число входных переменных */ sqlda2->sqlvar[0].sqltype = ECPGt_char; sqlda2->sqlvar[0].sqldata = "postgres"; sqlda2->sqlvar[0].sqllen = 8; intval = 1; sqlda2->sqlvar[1].sqltype = ECPGt_int; sqlda2->sqlvar[1].sqldata = (char *)&intval; sqlda2->sqlvar[1].sqllen = sizeof(intval);
Подготовив входную SQLDA, откройте курсор с ней.
/* Открыть курсор с входными параметрами. */ EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2;
Выберите строки из открытого курсора в выходную SQLDA. (Обычно, чтобы выбрать все строки в наборе результатов, нужно повторять FETCH
в цикле.)
while (1) { sqlda_t *cur_sqlda; /* Назначить дескриптор курсору */ EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;
Затем прочитайте выбранные записи из SQLDA, следуя по связанному списку структуры sqlda_t
.
for (cur_sqlda = sqlda1 ; cur_sqlda != NULL ; cur_sqlda = cur_sqlda->desc_next) { ...
Прочитайте все столбцы первой записи. Количество столбцов хранится в поле sqld
, а данные первого столбца в sqlvar[0]
, оба эти поля — члены структуры sqlda_t
.
/* Вывести каждый столбец в строке. */ for (i = 0; i < sqlda1->sqld; i++) { sqlvar_t v = sqlda1->sqlvar[i]; char *sqldata = v.sqldata; short sqllen = v.sqllen; strncpy(name_buf, v.sqlname.data, v.sqlname.length); name_buf[v.sqlname.length] = '\0';
Теперь данные столбцов сохранены в переменной v
. Скопируйте все элементы данных в переменные среды, определив тип столбца по полю v.sqltype
.
switch (v.sqltype) { int intval; double doubleval; unsigned long long int longlongval; case ECPGt_char: memset(&var_buf, 0, sizeof(var_buf)); memcpy(&var_buf, sqldata, (sizeof(var_buf) <= sqllen ? sizeof(var_buf)-1 : sqllen)); break; case ECPGt_int: /* integer */ memcpy(&intval, sqldata, sqllen); snprintf(var_buf, sizeof(var_buf), "%d", intval); break; ... default: ... } printf("%s = %s (type: %d)\n", name_buf, var_buf, v.sqltype); }
Закончив обработку всех записей, закройте курсор и отключитесь от базы данных.
EXEC SQL CLOSE cur1; EXEC SQL COMMIT; EXEC SQL DISCONNECT ALL;
Вся программа показана в Примере 34.1.
Пример 34.1. Пример программы на базе SQLDA
#include <stdlib.h> #include <string.h> #include <stdlib.h> #include <stdio.h> #include <unistd.h> EXEC SQL include sqlda.h; sqlda_t *sqlda1; /* дескриптор для выходных данных */ sqlda_t *sqlda2; /* дескриптор для входных данных */ EXEC SQL WHENEVER NOT FOUND DO BREAK; EXEC SQL WHENEVER SQLERROR STOP; int main(void) { EXEC SQL BEGIN DECLARE SECTION; char query[1024] = "SELECT d.oid,* FROM pg_database d, pg_stat_database s WHERE d.oid=s.datid AND ( d.datname=? OR d.oid=? )"; int intval; unsigned long long int longlongval; EXEC SQL END DECLARE SECTION; EXEC SQL CONNECT TO uptimedb AS con1 USER uptime; EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; EXEC SQL PREPARE stmt1 FROM :query; EXEC SQL DECLARE cur1 CURSOR FOR stmt1; /* Создать структуру SQLDA для входных параметров */ sqlda2 = (sqlda_t *)malloc(sizeof(sqlda_t) + sizeof(sqlvar_t)); memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t)); sqlda2->sqln = 2; /* число входных переменных */ sqlda2->sqlvar[0].sqltype = ECPGt_char; sqlda2->sqlvar[0].sqldata = "postgres"; sqlda2->sqlvar[0].sqllen = 8; intval = 1; sqlda2->sqlvar[1].sqltype = ECPGt_int; sqlda2->sqlvar[1].sqldata = (char *) &intval; sqlda2->sqlvar[1].sqllen = sizeof(intval); /* Открыть курсор с входными параметрами. */ EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2; while (1) { sqlda_t *cur_sqlda; /* Присвоить дескриптор курсору */ EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1; for (cur_sqlda = sqlda1 ; cur_sqlda != NULL ; cur_sqlda = cur_sqlda->desc_next) { int i; char name_buf[1024]; char var_buf[1024]; /* Напечатать каждый столбец в строке. */ for (i=0 ; i<cur_sqlda->sqld ; i++) { sqlvar_t v = cur_sqlda->sqlvar[i]; char *sqldata = v.sqldata; short sqllen = v.sqllen; strncpy(name_buf, v.sqlname.data, v.sqlname.length); name_buf[v.sqlname.length] = '\0'; switch (v.sqltype) { case ECPGt_char: memset(&var_buf, 0, sizeof(var_buf)); memcpy(&var_buf, sqldata, (sizeof(var_buf)<=sqllen ? sizeof(var_buf)-1 : sqllen) ); break; case ECPGt_int: /* integer */ memcpy(&intval, sqldata, sqllen); snprintf(var_buf, sizeof(var_buf), "%d", intval); break; case ECPGt_long_long: /* bigint */ memcpy(&longlongval, sqldata, sqllen); snprintf(var_buf, sizeof(var_buf), "%lld", longlongval); break; default: { int i; memset(var_buf, 0, sizeof(var_buf)); for (i = 0; i < sqllen; i++) { char tmpbuf[16]; snprintf(tmpbuf, sizeof(tmpbuf), "%02x ", (unsigned char) sqldata[i]); strncat(var_buf, tmpbuf, sizeof(var_buf)); } } break; } printf("%s = %s (type: %d)\n", name_buf, var_buf, v.sqltype); } printf("\n"); } } EXEC SQL CLOSE cur1; EXEC SQL COMMIT; EXEC SQL DISCONNECT ALL; return 0; }
Вывод этой программы должен быть примерно таким (некоторые числа будут меняться).
oid = 1 (type: 1) datname = template1 (type: 1) datdba = 10 (type: 1) encoding = 0 (type: 5) datistemplate = t (type: 1) datallowconn = t (type: 1) dathasloginevt = f (type: 1) datconnlimit = -1 (type: 5) datfrozenxid = 379 (type: 1) dattablespace = 1663 (type: 1) datconfig = (type: 1) datacl = {=c/uptime,uptime=CTc/uptime} (type: 1) datid = 1 (type: 1) datname = template1 (type: 1) numbackends = 0 (type: 5) xact_commit = 113606 (type: 9) xact_rollback = 0 (type: 9) blks_read = 130 (type: 9) blks_hit = 7341714 (type: 9) tup_returned = 38262679 (type: 9) tup_fetched = 1836281 (type: 9) tup_inserted = 0 (type: 9) tup_updated = 0 (type: 9) tup_deleted = 0 (type: 9) oid = 11511 (type: 1) datname = postgres (type: 1) datdba = 10 (type: 1) encoding = 0 (type: 5) datistemplate = f (type: 1) datallowconn = t (type: 1) dathasloginevt = f (type: 1) datconnlimit = -1 (type: 5) datfrozenxid = 379 (type: 1) dattablespace = 1663 (type: 1) datconfig = (type: 1) datacl = (type: 1) datid = 11511 (type: 1) datname = postgres (type: 1) numbackends = 0 (type: 5) xact_commit = 221069 (type: 9) xact_rollback = 18 (type: 9) blks_read = 1176 (type: 9) blks_hit = 13943750 (type: 9) tup_returned = 77410091 (type: 9) tup_fetched = 3253694 (type: 9) tup_inserted = 0 (type: 9) tup_updated = 0 (type: 9) tup_deleted = 0 (type: 9)