34.7. Использование областей дескрипторов#
34.7. Использование областей дескрипторов
Область SQL-дескриптора - это более сложный метод обработки результата операторов SELECT
, FETCH
или DESCRIBE
. Область SQL-дескриптора группирует данные одной строки вместе с метаданными в одну структуру данных. Метаданные особенно полезны при выполнении динамических SQL-запросов, где характеристики столбцов результата могут быть неизвестны заранее. В PostgreSQL предусмотрены два способа использования областей дескрипторов SQL: именованные области дескрипторов SQL и структуры C-типа SQLDA.
34.7.1. Именованные области дескрипторов SQL
Именованная область дескриптора SQL состоит из заголовка, который содержит информацию обо всем дескрипторе, и одной или нескольких областей дескриптора элемента, которые в основном описывают каждый столбец в строке результата.
Перед тем, как использовать область дескриптора SQL, необходимо выделить ее:
EXEC SQL ALLOCATE DESCRIPTOR identifier
;
Идентификатор служит в качестве “имени переменной” области дескриптора. Когда вам больше не нужен дескриптор, вы должны его деаллоцировать:
EXEC SQL DEALLOCATE DESCRIPTOR identifier
;
Для использования области дескриптора укажите ее в качестве цели хранения в предложении
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
для получения результата и метаданных в области дескриптора.
Теперь, как получить данные из области дескриптора? Вы можете представить область дескриптора как структуру с именованными полями. Чтобы извлечь значение поля из заголовка и сохранить его в переменную хоста, используйте следующую команду:
EXEC SQL GET DESCRIPTORname
:hostvar
=field
;
В настоящее время определено только одно поле заголовка:
COUNT
, которое указывает, сколько областей
дескрипторов элементов существует (то есть, сколько столбцов содержится в
результате). Переменная хоста должна иметь целочисленный тип. Чтобы
получить поле из области дескриптора элемента, используйте следующую
команду:
EXEC SQL GET DESCRIPTORname
VALUEnum
:hostvar
=field
;
num
может быть литеральным целым числом или переменной хоста, содержащей целое число. Возможные поля:
CARDINALITY
(integer)количество строк в наборе результатов
DATA
фактический элемент данных (следовательно, тип данных этого поля зависит от запроса)
DATETIME_INTERVAL_CODE
(integer)Когда
TYPE
равно9
,DATETIME_INTERVAL_CODE
будет иметь значение1
дляDATE
,2
дляTIME
,3
дляTIMESTAMP
,4
дляTIME WITH TIME ZONE
или5
дляTIMESTAMP WITH TIME ZONE
.DATETIME_INTERVAL_PRECISION
(integer)не реализовано
INDICATOR
(integer)индикатор (указывающий на нулевое значение или усечение значения)
KEY_MEMBER
(integer)не реализовано
LENGTH
(integer)длина данных в символах
NAME
(string)имя столбца
NULLABLE
(integer)не реализовано
OCTET_LENGTH
(integer)длина представления символа данных в байтах
PRECISION
(integer)точность (для типа
numeric
)RETURNED_LENGTH
(integer)длина данных в символах
RETURNED_OCTET_LENGTH
(integer)длина представления символа данных в байтах
SCALE
(integer)масштаб (для типа
numeric
)TYPE
(integer)числовой код типа данных столбца
В операторах EXECUTE
, DECLARE
и OPEN
эффект ключевых слов INTO
и USING
различается. Дескрипторная область также может быть создана вручную для
предоставления входных параметров для запроса или курсора, и
USING SQL DESCRIPTOR
является способом передачи входных параметров в параметризованный запрос. Оператор
для создания именованной дескрипторной области SQL приведен ниже:
name
EXEC SQL SET DESCRIPTORname
VALUEnum
field
= :hostvar
;
PostgreSQL поддерживает извлечение более одной записи в одном операторе 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 в PostgreSQL аналогична структуре данных в IBM DB2 Universal Database, поэтому некоторая техническая информация о SQLDA в DB2 может помочь лучше понять SQLDA в PostgreSQL.
34.7.2.1.1. sqlda_t Structure
Тип структуры 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 Structure
Тип структуры 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
Указывает на индикатор нулевого значения. 0 означает не нулевое значение, -1 означает нулевое значение.
sqlname
Имя поля.
34.7.2.1.3. структура sqlname Структура
Структура struct sqlname
содержит имя столбца. Она используется в качестве члена структуры sqlvar_t
. Определение структуры выглядит следующим образом:
#define NAMEDATALEN 64 struct sqlname { short length; char data[NAMEDATALEN]; };
Значение полей:
length
Содержит длину имени поля.
data
Содержит фактическое имя поля.
34.7.2.2. Получение набора результатов с использованием SQLDA
Общие шаги для получения набора результатов запроса через SQLDA:
Объявите структуру
sqlda_t
, чтобы получить набор результатов.Выполните команды
FETCH
/EXECUTE
/DESCRIBE
для обработки запроса, указывая объявленный SQLDA.Проверьте количество записей в наборе результатов, посмотрев на
sqln
, член структурыsqlda_t
.Получите значения каждого столбца из членов
sqlda_t
структурыsqlvar[0]
,sqlvar[1]
и т.д.Перейдите к следующей строке (
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; /* number of input variables */
После выделения памяти сохраните значения параметров в массиве 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;
Наконец, после использования входных SQLDAs, выделенное пространство памяти должно быть явно освобождено, в отличие от SQLDAs, используемых для получения результатов запроса.
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; /* an output descriptor */ sqlda_t *sqlda2; /* an input descriptor */
Далее, подключитесь к базе данных, подготовьте оператор и объявите курсор для подготовленного оператора.
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
.
/* Create SQLDA structure for input parameters. */ sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t)); memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t)); sqlda2->sqln = 2; /* number of input variables */ 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 откройте курсор с входным SQLDA.
/* Open a cursor with input parameters. */ EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2;
Извлеките строки в выходной SQLDA из открытого курсора.
(Обычно вам нужно вызывать команду FETCH
повторно
в цикле, чтобы извлечь все строки из набора результатов).
while (1) { sqlda_t *cur_sqlda; /* Assign descriptor to the cursor */ 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
.
/* Print every column in a row. */ 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; /* descriptor for output */ sqlda_t *sqlda2; /* descriptor for input */ 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; /* Create an SQLDA structure for an input parameter */ sqlda2 = (sqlda_t *)malloc(sizeof(sqlda_t) + sizeof(sqlvar_t)); memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t)); sqlda2->sqln = 2; /* a number of input variables */ 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); /* Open a cursor with input parameters. */ EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2; while (1) { sqlda_t *cur_sqlda; /* Assign descriptor to the cursor */ 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]; /* Print every column in a row. */ 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) 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) 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)