| Документация по PostgreSQL 9.4.1 | |||
|---|---|---|---|
| Пред. | Уровень выше | Глава 33. ECPG — встраиваемый SQL в C | След. |
33.7. Использование областей дескрипторов
Области дескрипторов SQL дают возможности для более сложной обработки результатов операторов SELECT, FETCH и DESCRIBE. Область дескриптора SQL объединяет в одной структуре данные одной строки и элементы метаданных. Эти метаданные особенно полезны при выполнении динамических SQL-операторов, когда характер результирующих колонок может быть неизвестен заранее. PostgreSQL предлагает два подхода к использованию областей дескрипторов: именованные области SQL-дескрипторов и области SQLDA в структурах C.
33.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, см. Подраздел 33.7.2.
В операторах DESCRIBE и FETCH ключевые слова INTO и USING действуют примерно одинаково: они указывают вывести набор результатов и метаданные в область дескриптора.
Возникает вопрос: а как же получить данные из области дескриптора? Область дескриптора можно представить как структуру с именованными полями. Чтобы получить значение поля из заголовка и сохранить его в переменной среды, нужно выполнить команду:
EXEC SQL GET DESCRIPTOR имя :переменная_среды = поле;
В настоящее время определено только одно поле заголовка: COUNT, которое говорит, сколько областей дескрипторов элементов существует (то есть, сколько колонок содержится в результате). Переменная среды должна иметь целочисленный тип. Чтобы получить поле из области дескриптора элемента, нужно выполнить команду:
EXEC SQL GET DESCRIPTOR имя VALUE номер :переменная_среды = поле;
В качестве num можно задать обычное целое или переменную среды, содержащую целое число. Допустимые поля:
- 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 номер поле = :переменная_среды;
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;
33.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 в Подразделе 33.7.1 применимы и здесь, с дополнением. В операторе DESCRIBE можно полностью опустить ключевое слово DESCRIPTOR, если присутствует ключевое слово INTO:
EXEC SQL DESCRIBE prepared_statement INTO mysqlda;
Общая схема использования SQLDA выглядит так:
Подготовить запрос и объявить курсор для него.
Объявить SQLDA для строк результата.
Объявить SQLDA для входных параметров и инициализировать их (выделить память, задать параметры).
Открыть курсор с входной SQLDA.
Выбрать строки из курсора и сохранить их в выходной SQLDA.
Прочитать значения из выходной SQLDA в переменные среды (и преобразовать при необходимости).
Закрыть курсор.
Освободить область памяти, выделенную для входной SQLDA.
33.7.2.1. Структура данных SQLDA
Для SQLDA используются три типа данных: sqlda_t, sqlvar_t и struct sqlname.
Подсказка: Структура данных SQLDA в PostgreSQL подобна той, что используется в IBM DB2 Universal Database, так что часть технической информации по SQLDA в DB2 может быть полезна и для понимания устройства SQLDA в PostgreSQL.
33.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
Это массив колонок в наборе результатов.
33.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
Указывает на данные. Формат данных описан в Подразделе 33.4.4.
- sqlind
Указывает на индикатор NULL. 0 соответствует значению не NULL, -1 — NULL.
- sqlname
Имя поля.
33.7.2.1.3. Структура struct sqlname
Структура struct sqlname содержит имя колонки. Она включена в sqlvar_t в качестве члена. Эта структура определена так:
#define NAMEDATALEN 64
struct sqlname
{
short length;
char data[NAMEDATALEN];
};Её поля имеют следующее назначение:
- length
Содержит длину имени поля.
- data
Содержит собственно имя поля.
33.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;
...
}33.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);
33.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 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; /* 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, откройте курсор с ней.
/* Открыть курсор с входными параметрами. */
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;Всё программа показана в Примере 33-1.
Пример 33-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 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)
datconnlimit = -1 (type: 5)
datlastsysoid = 11510 (type: 1)
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)
datlastsysoid = 11510 (type: 1)
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)| Пред. | Начало | След. |
| Библиотека pgtypes | Уровень выше | Обработка ошибок |