6. Лекция: Применение курсоров

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

Основные понятия

Курсоры

Применение курсоров позволяет приложению выполнять выборку одной или нескольких строк за одну операцию извлечения данных. Также курсоры поддерживают возможность применения операторов UPDATE, INSERT и DELETE к текущей позиции курсора.

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

Для использования курсора требуется выполнить следующие действия:

  1. Установить атрибуты курсора, вызвав функцию SQLSetStmtAttr. Эта функция позволяет устанавливать следующие атрибуты: SQL_ATTR_CURSOR_TYPE и SQL_ATTR_CONCURRENCY, или SQL_CURSOR_SCROLLABLE и SQL_CURSOR_SENSITIVITY.
  2. Определите размер результирующего набора, вызвав функцию SQLSetStmtAttr с атрибутом SQL_ATTR_ROW_ARRAY_SIZE.
  3. Для того чтобы использовать позиционированный SQL-оператор с фразой WHERE CURRENT OF, следует определить имя курсора, вызвав функцию SQLSetCursorName.
  4. Для создания результирующего набора следует выполнить SQL-оператор SELECT, вызвав функцию SQLExecute или функцию SQLExecDirect.
  5. Если имя курсора не было предварительно определено, но требуется использовать возможность применения позиционированного SQL-оператора с фразой WHERE CURRENT OF, то следует создать имя курсора, вызвав функцию SQLGetCursorName.
  6. Для получения информации о количестве столбцов в сформированном результирующем наборе можно вызвать функцию SQLNumResultCols.
  7. Определите связывание столбцов результирующего набора с буфером, предназначенным для извлечения значения столбца (одной строки или сразу набора строк).
  8. Выполните функцию ODBC API, извлекающую строки из результирующего набора в связанный буфер (или функцию, выполняющую извлечение поочередно каждого столбца в указываемый параметром буфер, если не было выполнено предварительного связывания).
  9. Если выполняемый SQL-оператор состоял из нескольких операторов SELECT, то сформированный результирующий набор будет состоять из нескольких множеств. Для перехода к следующему множеству (результирующему набору) используется функция SQLMoreResults. В том случае, если следующее множество существует, то эта функция выполняет переход к нему и возвращает код ответа SQL_SUCCESS. Если выбраны все множества сформированного результирующего набора, то функция возвращает код ответа SQL_NO_DATA.
  10. Для освобождения дескриптора оператора вызовите функцию SQLFreeStmt. При этом для того, чтобы одновременно выполнить освобождение связанных с данным оператором буферов, используемых для извлечения значений столбцов, установите значение параметра fOption равным SQL_UNBIND.

Управление поведением курсора

Приложение, использующее функции ODBC API, управляет поведением курсора, устанавливая атрибуты оператора. Можно использовать два различных способа определения характеристик курсора:

  • определить тип курсора;
  • определить поведение курсора.

Для определения типа курсора вызывается функция SQLSetStmtAttr со значением атрибута SQL_ATTR_CURSOR_TYPE, который имеет тип SQLUINTEGER и может задаваться следующими значениями:

  • SQL_CURSOR_FORWARD_ONLY - однонаправленный курсор;
  • SQL_CURSOR_STATIC - статический курсор, определяющий, что информация, извлеченная в результирующий набор, не будет отражать изменение данных в БД, произошедшее после создания результирующего набора;
  • SQL_CURSOR_KEYSET_DRIVEN - курсор, управляемый ключом. Такой курсор позволяет "видеть" изменение и удаление строк в БД, произошедшие после создания результирующего набора, но не отображает создание новых строк. Количество строк, для которых создаются ключи, указывается атрибутом SQL_ATTR_KEYSET_SIZE;
  • SQL_CURSOR_DYNAMIC - динамический курсор, отражающий изменение данных в БД после создания результирующего набора.

На используемый тип курсора накладывает ограничение применяемый ODBC-драйвер. Так, многие драйверы не поддерживают возможность применения динамического курсора. В том случае, если при вызове функции SQLSetStmtAttr был указан недопустимый тип курсора, то драйвер заменит его на наиболее подходящий из поддерживаемых типов, а функция вернет код ответа SQLSTATE 01S02 (Опция была изменена).

Для определения поведения курсора вызывается функция SQLSetStmtAttr со значениями атрибутов SQL_ATTR_CURSOR_SCROLLABLE и SQL_ATTR_CURSOR_SENSITIVITY. Смысл этих атрибутов соответствует применению ключевых слов SCROLL и SENSITIVE в SQL-операторе DECLARE CURSOR в стандарте SQL-92.

Атрибут SQL_ATTR_CURSOR_SCROLLABLE управляет перемещаемым курсором, имеет тип SQLUINTEGER и может задаваться следующими двумя значениями:

  • SQL_NONSCROLLABLE - перемещаемый курсор, реализуется как простой однонаправленный курсор. При вызове в приложении функции SQLFetchScroll параметр FetchOrientation может принимать только значение SQL_FETCH_NEXT.
  • SQL_SCROLLABLE - перемещаемый курсор, поддерживающий двунаправленный просмотр результирующего набора, а также прямую выборку строк.

Перемещаемые курсоры для извлечения данных используют функцию SQLFetchScroll (в версии ODBC 2.x использовалась функция SQLExtendedFetched). Если простой однонаправленный курсор, используемый функцией SQLFetch, позволяет перемещаться только в одном прямом направлении и выполнять выборку только одной строки за один вызов функции, то перемещаемый курсор позволяет:

  • выполнять за один вызов функции извлечение более одной строки;
  • перемещаться в двух направлениях и на любое число строк;
  • выполнять прямую выборку строки из результирующего набора по ее номеру.

Атрибут SQL_ATTR_CURSOR_SENSITIVITY определяет чувствительность курсора к изменениям, выполняемым другими курсорами, имеет тип SQLUINTEGER и может определять режимы, задаваемые следующими значениями:

  • SQL_UNSPECIFIED - неопределенный курсор, при котором изменения могут быть сделаны как видимыми, так и невидимыми или частично видимыми. Этот режим используется по умолчанию;
  • SQL_INSENSITIVE - режим нечувствительного курсора, при котором все курсоры показывают результирующий набор без отражения изменений, выполненных для других курсоров. Нечуствительный курсор является курсором "только чтение". Он соответствует статическому типу курсора с уровнем изоляции "только чтение";
  • SQL_SENSITIVE - режим чувствительного курсора, при котором курсоры "видят" все изменения, выполненные другими курсорами.

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

Атрибуты, устанавливаемые вызовом функции SQLSetStmtAttrАтрибуты, устанавливаемые драйвером неявно для соответствия характеристик курсора
SQL_ATTR_CONCURRENCY установлен как SQL_CONCUR_READ_ONLYSQL_ATTR_CURSOR_SENSITIVITY получает значение SQL_INSENSITIVE
SQL_ATTR_CONCURRENCY установлен как SQL_CONCUR_LOCK, SQL_CONCUR_ROWVER или SQL_CONCUR_VALUESSQL_ATTR_CURSOR_SENSITIVITY получает значение SQL_UNSPECIFIED или SQL_SENSITIVE, что определяется драйвером. Значение SQL_INSENSITIVE никогда не будет назначено данному атрибуту, так как оно предусматривает режим "только чтение"
SQL_ATTR_CURSOR_SCROLLABLE установлен как SQL_NONSCROLLABLESQL_ATTR_CURSOR_TYPE получает значение SQL_CURSOR_FORWARD_ONLY
SQL_ATTR_CURSOR_SCROLLABLE установлен как SQL_SCROLLABLESQL_ATTR_CURSOR_TYPE получает значение SQL_CURSOR_STATIC, SQL_CURSOR_KEYSET_DRIVEN или SQL_CURSOR_DYNAMIC, что определяется драйвером. Значение SQL_CURSOR_FORWARD_ONLY никогда не будет назначено данному атрибуту
SQL_ATTR_CURSOR_SENSITIVITY установлен как SQL_INSENSITIVESQL_ATTR_CONCURRENCY получает значение SQL_CONCUR_READ_ONLY. SQL_ATTR_CURSOR_TYPE получает значение SQL_CURSOR_STATIC
SQL_ATTR_CURSOR_SENSITIVITY установлен как SQL_SENSITIVESQL_ATTR_CONCURRENCY получает значение SQL_CONCUR_LOCK, SQL_CONCUR_ROWVER или SQL_CONCUR_VALUES ( как определено драйвером). Атрибут SQL_ATTR_CONCURRENCY никогда не получает значение SQL_CONCUR_READ_ONLY. SQL_ATTR_CURSOR_TYPE получает значение SQL_CURSOR_FORWARD_ONLY, SQL_CURSOR_STATIC, SQL_CURSOR_KEYSET_DRIVEN или SQL_CURSOR_DYNAMIC (как определяется драйвером) SQL_ATTR_CURSOR_SENSITIVITY установлен как SQL_UNSPECIFIED SQL_ATTR_CONCURRENCY получает значение SQL_CONCUR_READ_ONLY, SQL_CONCUR_LOCK, SQL_CONCUR_ROWVER или SQL_CONCUR_VALUES ( как определено драйвером). SQL_ATTR_CURSOR_TYPE получает значение SQL_CURSOR_FORWARD_ONLY, SQL_CURSOR_STATIC, SQL_CURSOR_KEYSET_DRIVEN или SQL_CURSOR_DYNAMIC ( как определено драйвером)
SQL_ATTR_CURSOR_TYPE установлен как SQL_CURSOR_DYNAMICSQL_ATTR_SCROLLABLE получает значение SQL_SCROLLABLE. SQL_ATTR_CURSOR_SENSITIVITY получает значение SQL_SENSITIVE. (Только в том случае, если атрибут SQL_ATTR_CONCURRENCY не равен SQL_CONCUR_READ_ONLY. Обновляемые динамические курсоры всегда "видят" все изменения, сделанные в их собственной транзакции
SQL_ATTR_CURSOR_TYPE установлен как SQL_CURSOR_FORWARD_ONLYSQL_ATTR_CURSOR_SCROLLABLE получает значение SQL_NONSCROLLABLE
SQL_ATTR_CURSOR_TYPE установлен как SQL_CURSOR_KEYSET_DRIVENSQL_ATTR_SCROLLABLE получает значение SQL_SCROLLABLE. SQL_ATTR_SENSITIVITY получает значение SQL_UNSPECIFIED или SQL_SENSITIVE (в соответствии с тем, как определяется драйвером, но если атрибут SQL_ATTR_CONCURRENCY не равен SQL_CONCUR_READ_ONLY)
SQL_ATTR_CURSOR_TYPE установлен как SQL_CURSOR_STATICSQL_ATTR_SCROLLABLE получает значение SQL_SCROLLABLE. SQL_ATTR_SENSITIVITY получает значение SQL_INSENSITIVE (в том случае, если атрибут SQL_ATTR_CONCURRENCY равен SQL_CONCUR_READ_ONLY). SQL_ATTR_SENSITIVITY получает значение SQL_UNSPECIFIED или SQL_SENSITIVE (в том случае, если атрибут SQL_ATTR_CONCURRENCY не равен SQL_CONCUR_READ_ONLY)

Функция SQLSetStmtAttr позволяет установить значения атрибутов оператора и имеет следующее формальное описание:

SQLRETURN SQLSetStmtAttr(
     SQLHSTMT     StatementHandle,
     SQLINTEGER     Attribute,
     SQLPOINTER     ValuePtr,
     SQLINTEGER     StringLength);

Параметр StatementHandle ([Input]) указывает дескриптор оператора.

Параметр Attribute ([Input]) определяет атрибут, значение которого устанавливается, а параметр ValuePtr ([Input]) является указателем на значение, назначаемое атрибуту Attribute.

Параметр StringLength ([Input]) зависит от типа значения, передаваемого параметром ValuePtr, и от вида атрибута (ODBC-определяемый или определяемый драйвером) и может содержать длину строки, игнорироваться или указываться константами, такими как SQL_NTS, SQL_IS_POINTER.

Извлечение данных

Изменение позиции курсора

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

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

Для изменения или удаления любой строки из набора строк можно использовать функцию SQLSetPos. Применение функции SQLSetPos является удобной альтернативой выполнения отдельного SQL-оператора. Так, это позволяет ODBC-драйверу поддерживать позиционированное изменение даже в том случае, если сам источник данных не поддерживает позиционированный UPDATE в SQL-операторе.

Функция SQLSetPos оперирует с текущим набором строк (rowset), который создается после вызова функции SQLFetchScroll, выполняющей извлечение этого набора строк из результирующего набора, сформированного, в свою очередь, при выполнении SQL-оператора.

Функция SQLSetPos имеет следующее формальное описание:

SQLSetPos(
     SQLHSTMT     StatementHandle,
     SQLUSMALLINT     RowNumber,
     SQLUSMALLINT     Operation,
     SQLUSMALLINT     LockType);

Параметр StatementHandle ([Input]) указывает дескриптор оператора. Параметр RowNumber ([Input]) определяет позицию строки в наборе строк, над которой выполняется операция, указываемая параметром Operation. Если значение параметра RowNumber равно 0, то операция будет выполняться над каждой строкой набора строк (которая отмечена соответствующим образом в массиве операций над строками).

Параметр Operation ([Input]) определяет тип выполняемой операции и указывается следующими значениями:

SQL_POSITION
SQL_REFRESH
SQL_UPDATE
SQL_DELETE 

Отметим, что значение параметра Operation, равное SQL_ADD, начиная с версии ODBC 3.x, отменено. Однако драйверы ODBC 3.x в целях обратной совместимости поддерживают эту возможность, заменяя вызов функции SQLSetPos с данным значением параметра на вызов функции SQLBulkOperations со значением параметра Operation, равным SQL_ADD. Обратно, если приложение ODBC 3.x использует драйвер ODBC 2.x, то менеджер драйверов заменяет для параметра, равного SQL_ADD, вызов SQLBulkOperations на вызов SQLSetPos.

Параметр LockType ([Input]) определяет уровень блокировки строки при выполнении операции, указываемой параметром Operation, и может принимать следующие значения:

  • SQL_LOCK_NO_CHANGE - состояние блокировки строки не должно измениться после выполнения функции;
  • SQL_LOCK_EXCLUSIVE - выполняется блокировка строки с эксклюзивным доступом. Это гарантирует, что другое приложение не сможет получить доступ к данной строке;
  • SQL_LOCK_UNLOCK - выполняется освобождение строки.

Строка, заблокированная при выполнении функции SQLSetPos, остается заблокированной до тех пор, пока приложение не вызовет эту же функцию со значением параметра LockType, равным SQL_LOCK_UNLOCK, или функцию SQLFreeHandle для дескриптора оператора, или функцию SQLFreeStmt с опцией SQL_CLOSE. Если драйвер поддерживает транзакции, то строка, заблокированная при выполнении функции SQLSetPos, освобождается при завершении транзакции функцией SQLEndTran (как при коммите, так и при откате транзакции) в том случае, если при завершении транзакции установлено закрытие курсора.

При выполнении операций SQL_DELETE и SQL_UPDATE изменяется состояние строки. Так, в массиве состояния строк, указатель на который определяется атрибутом оператора SQL_ATTR_ROW_STATUS, все удаленные строки будут отмечены как SQL_ROW_DELETED.

Для определения во время выполнения операций, поддерживаемых используемым источником данных, следует вызвать функцию SQLGetInfo с одним из следующих значений параметра в зависимости от типа используемого курсора:

SQL_DYNAMIC_CURSOR_ATTRIBUTES1, 
SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1, 
SQL_KEYSET_CURSOR_ATTRIBUTES1, 
SQL_STATIC_CURSOR_ATTRIBUTES1.

В следующей таблице приведено описание операций, которые могут быть выполнены функцией SQLSetPos

Значение параметра OperationОписание
SQL_POSITIONПозиция курсора устанавливается на строку с номером, указанным параметром RowNumber (нумерация начинается с 1)
SQL_REFRESHПозиция курсора устанавливается на строку с номером, указанным параметром RowNumber, и драйвер обновляет данные для этой строки в буфере результирующего набора. При этом функция SQLSetPos выполняет обновление содержания и состояния строк в текущем сформированном результирующем наборе: данные в буфере обновляются, а не повторно извлекаются. В отличие от функции SQLSetPos функция SQLFetchScroll с значениями параметров FetchOrientation, равным SQL_FETCH_RELATIVE, и RowNumber, равным 0, выполняет повторное извлечение строк из результирующего набора (при этом, если драйвер допускает, то для обновляемого курсора будут отображаться добавленные строки и не отображаться - удаленные). Успешное выполнение функцией SQLSetPos обновления строки не изменяет состояния строки, равного SQL_ROW_DELETED, а состояние строки, равное SQL_ROW_ADDED, заменяет на SQL_ROW_SUCCESS (в том случае, если массив состояний строк существует). В том случае, если курсор был открыт с атрибутом оператора SQL_ATTR_CONCURRENCY SQL_CONCUR_ROWVER или SQL_CONCUR_VALUES, то в случае операции обновления, выполняемой функцией SQLSetPos, может быть определено, что строки были изменены: при этом буфер результирующего набора обновляется при извлечении строки с сервера
SQL_UPDATEУстанавливает позицию курсора на строку, указанную параметром RowNumber, и изменяет значения строки, беря их из буфера (буферов результирующего набора), определенного параметром TargetValuePtr функции SQLBindCol
SQL_DELETEУстанавливает позицию курсора на строку, определенную параметром RowNumber, и удаляет указанную строку

Массив состояния строк

Массив состояния строк указывается атрибутом оператора SQL_ATTR_ROW_STATUS_PTR. Он содержит значения состояния каждой строки результирующего набора. Состояние строки устанавливается драйвером после вызова одной из следующих функций: SQLFetch, SQLFetchScroll, SQLBulkOperations, SQLSetPos.

Массив операций над строками

Функции ODBC API позволяют выполнять одновременно (за один вызов функции) некоторую операцию над несколькими строками. Такие операции называются множественными операциями.

Массив операций над строками указывается атрибутом оператора SQL_ATTR_ROW_OPERATION_PTR. Каждый элемент массива может иметь одно из следующих значений:

  • SQL_ROW_PROCEED - строка должна быть обработана (по умолчанию);
  • SQL_ROW_IGNORE - игнорировать данную строку.

Массив операций над строками определяет, будет ли вызов функции SQLSetPos для множественной операции (bulk operation) выполняться или игнорироваться.

Применение функции SQLSetPos

При изменении данных перед вызовом функции SQLSetPos следует:

  1. Вызвать функцию SQLBindCol (или SQLSetDescRec) для каждого столбца, определив тип данных, буфер для данных и его размер. Если для столбца не назначается связываемый с ним буфер, то данные могут быть переданы последовательностью вызовов функции SQLPutData. Такие столбцы называются столбцами времени выполнения (data-at-execution columns).
  2. Для гарантии того, что изменяемый столбец может быть изменен, следует вызвать функцию SQLColAttribute.
  3. Для создания результирующего набора можно выполнить функцию SQLExecDirect или SQLExecute.
  4. Для выборки данных использовать функции SQLFetch или SQLFetchScroll.

Выполнение множественных операций

Множественная операция выполняется функцией SQLSetPos в том случае, если параметр RowNumber равен 0. Она выполняется для всех строк, у которых в массиве операций над строками установлено значение SQL_ROW_PROCEED.

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

Это может быть выполнено вызовом функции SQLSetStmtAttr для установки атрибута оператора SQL_ATTR_ROW_OPERATION_PTR, указывающего на массив элементов типа SQLUSMALLINT. Это поле также может быть установлено вызовом функции SQLSetDescField для определения SQL_DESC_ARRAY_STATUS_PTR.

При множественных операциях игнорировать можно не только обработку строк, но и обработку столбцов (например, столбцов "только для чтения"). Для этого в функции SQLBindCol игнорируемый столбец следует пометить как SQL_COLUMN_IGNORE.

Следующий пример иллюстрирует применение функции SQLSetPos.

#define ROWS 20        // Число строк 
         // в результирующем наборе
#define STATUS_LEN 6

SQLCHAR        szStatus[ROWS][STATUS_LEN], szReply[3];
SQLINTEGER     cbStatus[ROWS], cbID;
SQLUSMALLINT   rgfRowStatus[ROWS];
SQLUINTEGER    sID, crow = ROWS, irow;
SQLHSTMT       hstmtS, hstmtU;

SQLSetStmtAttr(hstmtS, SQL_ATTR_CONCURRENCY, 
               (SQLPOINTER) SQL_CONCUR_ROWVER, 0);
    // Определение типа курсора
SQLSetStmtAttr(hstmtS, SQL_ATTR_CURSOR_TYPE, 
            (SQLPOINTER) SQL_CURSOR_KEYSET_DRIVEN, 0);
    // Определение размера результирующего набора
SQLSetStmtAttr(hstmtS, SQL_ATTR_ROW_ARRAY_SIZE, 
            (SQLPOINTER) ROWS, 0);
    // Определение массива состояния строк
SQLSetStmtAttr(hstmtS, SQL_ATTR_ROW_STATUS_PTR, 
             (SQLPOINTER) rgfRowStatus, 0);
SQLSetCursorName(hstmtS, "C1", SQL_NTS);
   // Выполнение SQL-оператора
SQLExecDirect(hstmtS, 
           "SELECT ID1, STATUS FROM TBL1", SQL_NTS);
   // Выполнение "связывания" данных
SQLBindCol(hstmtS, 1, SQL_C_ULONG, &sID, 0, &cbID);
SQLBindCol(hstmtS, 2, SQL_C_CHAR, szStatus, 
           STATUS_LEN, &cbStatus);

while ((retcode == SQLFetchScroll(hstmtS, 
                  SQL_FETCH_NEXT, 0)) != SQL_ERROR) 
{
   if (retcode == SQL_NO_DATA_FOUND)
      break;
     // Отображение 20-ти извлеченных строк 
     // результирующего набора
   for (irow = 0; irow < crow; irow++) {
      if (rgfRowStatus[irow] != SQL_ROW_DELETED)
         // Отображение данных
         printf("%2d %5d %*s
", irow+1, sID, 
                 NAME_LEN-1, szStatus[irow]);
   }
   while (TRUE) {
   printf("
Укажите номер изменяемой строки или 0?");
      gets(szReply);       // Получаем номер строки
      irow = atoi(szReply);
      if (irow > 0 && irow <= crow) {
         printf("
Новое состояние?");
         gets(szStatus[irow-1]);    // Получаем новое 
                           // значение для поля STATUS
         // Изменяем текущую позицию курсора
         SQLSetPos(hstmtS, irow, SQL_POSITION,  
                   SQL_LOCK_NO_CHANGE);
         SQLPrepare(hstmtU,
          "UPDATE TBL1 SET STATUS=? 
           WHERE CURRENT OF C1", SQL_NTS);
         // Выполняем "связывание" параметра
         SQLBindParameter(hstmtU, 1, SQL_PARAM_INPUT,
                          SQL_C_CHAR, SQL_CHAR,
                          STATUS_LEN, 0, 
                          szStatus[irow], 0, NULL);
         // Выполняем изменение данных
         SQLExecute(hstmtU);  
      } else if (irow == 0) {
         break;
      }
   }
}