На яхту заработать проще простого! Уникальный мир - игры на реальные деньги!

4. Лекция: Механизмы выборки данных

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

Схема извлечения данных с использованием ODBC API

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

ODBC API предоставляет два способа извлечения данных из результирующего набора:

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

Выполнение SQL-оператора

Результирующий набор создается при выполнении SQL-оператора SELECT. Для выполнения любого SQL-оператора первоначально должен быть создан дескриптор оператора.

Например:

SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

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

Для выполнения запроса ODBC API предоставляет следующие две функции:

  • SQLExecute - выполняет откомпилированный SQL-оператор;
  • SQLExecDirect - выполняет SQL-оператор, указываемый параметром.

Функция SQLExecDirect реализует одношаговый интерфейс, при котором процесс компиляции SQL-оператора и его выполнение осуществляется единожды при вызове данной функции.

Функции SQLPrepare и SQLExecute реализуют многошаговый интерфейс: сначала выполняется компиляция оператора и строится план выполнения, а затем возможно многократное выполнение подготовленного оператора (например, с различными значениями параметров).

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

SQLRETURN SQLExecDirect(
     SQLHSTMT     StatementHandle,
     SQLCHAR *     StatementText,
     SQLINTEGER     TextLength);

Параметр StatementHandle ([Input]) указывает дескриптор оператора, параметр StatementText ([Input]) определяет выполняемый SQL-оператор, TextLength (Input]) - длина строки *StatementText.

Функция SQLExecDirect возвращает одно из следующих значений: SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NEED_DATA, SQL_STILL_EXECUTING, SQL_ERROR, SQL_NO_DATA, SQL_INVALID_HANDLE.

Функция SQLExecDirect через ODBC-драйвер передает SQL-оператор источнику данных. При этом драйвер может изменить передаваемый SQL-оператор, приводя его к форме, соответствующей используемому источнику данных - например, изменяя передаваемые escape-последовательности (при использовании значений даты и времени, скалярных функций и функций даты/времени, предиката LIKE, внешних соединений, процедурных вызовов).

При выполнении SQL-оператора SELECT с вызовом функции SQLSetCursorName драйвер использует заданное этой функцией имя курсора. Если имя курсора явно не указывается, то драйвер самостоятельно формирует имя курсора для оператора SELECT.

Если режим работы с источником данных не установлен как автокоммит, а перед выполнением SQL-оператора транзакция не была инициализирована явным образом, то драйвер самостоятельно выполняет открытие транзакции.

Если приложение использует функцию SQLExecDirect для выполнения SQL-операторов COMMIT или ROLLBACK, то оно не будет интероперабельным между различными СУБД. Для достижения полной переносимости разрабатываемого приложения работы с базами данных следует для завершения или отката транзакции вызывать функцию ODBC API SQLEndTran.

При выполнении SQL-оператора, содержащего параметры, их значения предварительно должны быть определены вызовом функций SQLParamData и SQLPutData или SQLBindParameter. В противном случае при выполнении функции SQLExecDirect будет возвращено значение SQL_NO_DATA.

При многошаговом интерфейсе первой должна быть выполнена функция SQLPrepare, инициирующая компиляцию SQL-оператора.

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

SQLRETURN SQLPrepare(
     SQLHSTMT     StatementHandle,
     SQLCHAR *     StatementText,
     SQLINTEGER     TextLength);

Параметр StatementHandle ([Input]) указывает дескриптор оператора, параметр StatementText ([Input]) определяет текст компилируемого SQL-оператора, TextLength ([Input]) - это длина строки *StatementText.

Функция SQLExecute выполняет откомпилированный SQL-оператор. Если выполняемым SQL-оператором был оператор SELECT, то в результате выполнения SQLExecute создается результирующий набор.

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

SQLRETURN SQLExecute(
     SQLHSTMT     StatementHandle);

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

Например:

SQLHSTMT hstmtS, hstmtU;
SQLExecDirect(hstmtS, "SELECT F1, F2 FROM TBL1 ", SQL_NTS);
SQLPrepare(hstmtU,
        "UPDATE TBL1 SET F2=F2*1.3 WHERE F1=1010", SQL_NTS);
SQLExecute(hstmtU);

Обработка результирующего набора

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

Извлечение данных из результирующего набора в переменные может быть выполнено:

  • вызовом функции SQLFetch или SQLFetchScroll;
  • вызовом функции SQLGetData.

Функции SQLFetch или SQLFetchScroll всегда выполняют продвижение курсора на следующую запись. При этом функция SQLFetch, реализует механизм "однонаправленного курсора", а функция SQLFetchScroll позволяет, в зависимости от используемого источника данных, реализовывать механизм "двунаправленного курсора" и механизм "прямой выборки".

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

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

SQLRETURN SQLFetch(
     SQLHSTMT     StatementHandle);

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

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

SQLRETURN SQLGetData(
     SQLHSTMT     StatementHandle,
     SQLUSMALLINT     ColumnNumber,
     SQLSMALLINT     TargetType,
     SQLPOINTER     TargetValuePtr,
     SQLINTEGER     BufferLength,
     SQLINTEGER *     StrLen_or_IndPtr);

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

Параметр ColumnNumber ([Input]) указывает номер связываемого столбца результирующего набора (начиная с 1). По умолчанию столбец номер 0 является столбцом маркера строки, в том случае, если маркеры доступны.

Параметр TargetType ([Input]) определяет C-тип данных для буфера*TargetValuePtr в соответствии со следующей таблицей.

Идентификатор C-типа ODBC C typedef Тип C
SQL_C_CHAR SQLCHAR * unsigned char *
SQL_C_SSHORT SQLSMALLINT Short int
SQL_C_USHORT SQLUSMALLINT unsigned short int
SQL_C_SLONG SQLINTEGER long int
SQL_C_ULONG SQLUINTEGER unsigned long int
SQL_C_FLOAT SQLREAL float
SQL_C_DOUBLE SQLDOUBLE, SQLFLOAT double
SQL_C_BIT SQLCHAR unsigned char
SQL_C_STINYINT SQLSCHAR Signed char
SQL_C_UTINYINT SQLCHAR unsigned char
SQL_C_SBIGINT SQLBIGINT _int64
SQL_C_UBIGINT SQLUBIGINT unsigned _int64
SQL_C_BINARY SQLCHAR * unsigned char *
SQL_C_BOOKMARK BOOKMARK unsigned long int
SQL_C_VARBOOKMARK SQLCHAR * unsigned char *
SQL_C_TYPE_DATE SQL_DATE_STRUCT
struct tagDATE_STRUCT {     
  SQLSMALLINT year;      
  SQLUSMALLINT month;     
  SQLUSMALLINT day; 
} DATE_STRUCT;
SQL_C_TYPE_TIME SQL_TIME_STRUCT
struct tagTIME_STRUCT {
  SQLUSMALLINT hour;     
  SQLUSMALLINT minute;     
  SQLUSMALLINT second; 
} TIME_STRUCT;
SQL_C_TYPE_TIMESTAMP SQL_TIMESTAMP_STRUCT
struct tagTIMESTAMP_STRUCT {
  SQLSMALLINT year;      
  SQLUSMALLINT month;      
  SQLUSMALLINT day;     
  SQLUSMALLINT hour;      
  SQLUSMALLINT minute;      
  SQLUSMALLINT second;      
  SQLUINTEGER fraction; 
} TIMESTAMP_STRUCT;
SQL_C_NUMERIC SQL_NUMERIC_STRUCT
Struct tagSQL_NUMERIC_STRUCT {   
  SQLCHAR precision;    
  SQLSCHAR scale;     
  SQLCHAR sign[g];     
  SQLCHAR          
    val[SQL_MAX_NUMERIC_LEN]; 
} SQL_NUMERIC_STRUCT;
SQL_C_GUID SQLGUID
struct tagSQLGUID {
DWORD Data1;
WORD Data2;
WORD Data3;
BYTE Data4[8];
} SQLGUID;
Все интервальные C типы данных SSQL_INTERVAL_STRUCT
typedef struct tagSQL_INTERVAL_STRUCT
{
  SQLINTERVAL interval_type; 
  SQLSMALLINT interval_sign;
  union {
SQL_YEAR_MONTH_STRUCT   year_month;
SQL_DAY_SECOND_STRUCT   day_second;
    } intval;
} SQL_INTERVAL_STRUCT;
typedef enum 
{
 
  SQL_IS_YEAR = 1,
  SQL_IS_MONTH = 2,
  SQL_IS_DAY = 3,
  SQL_IS_HOUR = 4,
  SQL_IS_MINUTE = 5,
  SQL_IS_SECOND = 6,
  SQL_IS_YEAR_TO_MONTH = 7,
  SQL_IS_DAY_TO_HOUR = 8,
  SQL_IS_DAY_TO_MINUTE = 9,
  SQL_IS_DAY_TO_SECOND = 10,
  SQL_IS_HOUR_TO_MINUTE = 11,
  SQL_IS_HOUR_TO_SECOND = 12,
  SQL_IS_MINUTE_TO_SECOND = 13
} SQLINTERVAL;
 typedef struct tagSQL_YEAR_MONTH
{ 
   SQLUINTEGER year;
   SQLUINTEGER month; 
} SQL_YEAR_MONTH_STRUCT;
typedef struct tagSQL_DAY_SECOND
{
   SQLUINTEGER day;
   SQLUINTEGER hour;
   SQLUINTEGER minute;
   SQLUINTEGER second;
   SQLUINTEGER fraction;
} SQL_DAY_SECOND_STRUCT;

Например:

SQLCHAR      ValuePtr[50];
SQLINTEGER   ValueLenOrInd;
SQLGetData(hstmt, 1, SQL_C_CHAR, 
    ValuePtr, sizeof(ValuePtr), 
    &ValueLenOrInd);

В параметре TargetType также можно указывать идентификатор типа SQL_C_DEFAULT: в этом случае драйвер самостоятельно выбирает тип данных С, основываясь на типе данных поля источника данных.

Параметр TargetValuePtr ([Output]) определяет буфер, в который выполняется извлечение данных, а параметр ([Input]) определяет размер этого буфера в байтах. Для данных, имеющих фиксированную длину, таких как целочисленные значения, драйвер игнорирует значение параметра BufferLength.

Параметр StrLen_or_IndPtr ([Output]) определяет буфер, в котором возвращается размер данных или индикатор и может содержать следующие значения:

  • размер данных в байтах;
  • SQL_NO_TOTAL - индикатор, указывающий, что размер не может быть определен;
  • SQL_NULL_DATA - индикатор, указывающий, что данные имеют значение NULL.

Следующий пример иллюстрирует применение механизма извлечения данных при помощи функции SQLGetData.

//OBDC_Connect.cpp
#include "stdafx.h"
#include "Test_ODBC_connect.h"
#include <iostream>

#ifdef _DEBUG
#define new DEBUG_NEW
#endif
CWinApp theApp; // Объявление объекта приложения
using namespace std;
int _tmain(int argc, TCHAR* argv[], TCHAR* envp[])
{	int nRetCode = 0;
   if (!AfxWinInit(::GetModuleHandle(NULL), NULL, 
 						 ::GetCommandLine(), 0))
   {	_tprintf(_T("Ошибка инициализации MFC
"));
      nRetCode = 1;
   }
   else
   {
      std::cout<<"Begin"<<std::endl;
SQLHENV     henv;     // Дескриптор окружения
SQLHDBC     hdbc; 		// Дескриптор соединения
SQLHSTMT    hstmt; 		// Дескриптор оператора
SQLRETURN   retcode; 	// Код возврата
      /*Инициализация дескриптора окружения */
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
   std::cout<<"SQLAllocHandle создан успешно"<<std::endl;
   /* Определение версии ODBC */
  retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, 
                         (void*)SQL_OV_ODBC3, 0); 
   if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
      /* Инициализация дескриптора соединения */
      retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); 
      if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
        std::cout<<"SQLAllocHandle создан успешно "<<std::endl;
         retcode = SQLConnect(hdbc, (SQLCHAR*) "MySQLDB", SQL_NTS,
                  (SQLCHAR*) "", SQL_NTS,
                  (SQLCHAR*) "", SQL_NTS); 
         if (retcode == SQL_SUCCESS || 
 				retcode == SQL_SUCCESS_WITH_INFO){
 		/* Инициализация дескриптора оператора */
         retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); 
SQLCHAR      sqf3[50];
SQLINTEGER   sqf1,sqf2,sbf1,sbf2,sbf3;
SQLCHAR     selecttxt[] ="SELECT f1, f2, f3 FROM tbl1";
      /* Создание  результирующего набора */
retcode = SQLExecDirect(hstmt,  selecttxt,   SQL_NTS);
if (retcode == SQL_SUCCESS) {
   while (TRUE) {
    /* Выборка данных */
      retcode = SQLFetch(hstmt);
      if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
      }
      if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
         /* Извлечение данных трех полей результирующего набора */
         SQLGetData(hstmt, 1, SQL_C_ULONG, &sqf1, 0, &sbf1);
         SQLGetData(hstmt, 2, SQL_C_ULONG, &sqf2, 0, &sbf2);
         SQLGetData(hstmt, 3, SQL_C_CHAR, sqf3, 50, &sbf3);
         /* Запись в поток вывода строк результирующего набора */
std::cout<< "1: "<<sqf1<<" 2:   "<<sqf2<<"  3:  "<< sqf3<<"   "<<std::endl;
      } else {
         break;
      }
   }
}
         /* Освобождение дескрипторов */
       if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
          SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
      }
       SQLDisconnect(hdbc);
     }
    SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
  }
 }
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
return nRetCode;
}
}

//OBDC_Connect.h
#pragma once
#include "resource.h"
#ifndef _AFX_NOFORCE_LIBS
////////////////////////////////////////////////////////////////
// Win32 библиотеки
#pragma comment(lib, "odbc32.lib")
#pragma comment(lib, "odbccp32.lib")
#endif //!_AFX_NOFORCE_LIBS
#ifndef __SQL
   #include <sql.h>        // ядро
#endif
#ifndef __SQLEXT
   #include <sqlext.h>     // расширение
#endif

Связывание данных

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

SQLRETURN SQLBindCol(
     SQLHSTMT     StatementHandle,
     SQLUSMALLINT     ColumnNumber,
     SQLSMALLINT     TargetType,
     SQLPOINTER     TargetValuePtr,
     SQLINTEGER     BufferLength,
     SQLLEN *     StrLen_or_Ind);

Значения параметров функции SQLBindCol аналогичны значениям параметров функции SQLGetData. Но функция SQLBindCol указывается только один раз для каждого поля, а затем выборка данных выполняется автоматически при вызове функции SQLFetch. А при отсутствии связывания функция SQLGetData должна вызываться каждый раз для каждого поля после выполнения функции SQLFetch.

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

Например:

#define NAME_LEN 50
#define PHONE_LEN 10

SQLCHAR      szName[20], szPhone[15];
SQLINTEGER   sID, cbName, cbID, cbPhone;
SQLHSTMT      hstmt;
SQLRETURN   retcode;

retcode = SQLExecDirect(hstmt,
            "SELECT CID, NAME, PHONE FROM TBL1
             ORDER BY 2, 1, 3", 
SQL_NTS);
if (retcode == SQL_SUCCESS || 
    retcode == SQL_SUCCESS_WITH_INFO) {
/* Связывание столбцов 1, 2 и 3 */
SQLBindCol(hstmt, 1, 
           SQL_C_ULONG, 
           &sCID, 0, 
           &cbCID);
SQLBindCol(hstmt, 2, 
           SQL_C_CHAR, 
           szName, 20, 
           &cbName);
SQLBindCol(hstmt, 3, 
           SQL_C_CHAR, 
           szPhone, 15, 
           &cbPhone);
while (TRUE) {
retcode = SQLFetch(hstmt);
if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
show_error();
      }
if (retcode == SQL_SUCCESS || 
    retcode == SQL_SUCCESS_WITH_INFO){
// Значения полей успешно извлечены из 
// результирующего набора
} else {
break;
      }
   }
}

Применение ESCAPE-последовательностей

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

Escape-последовательность указывается в фигурных скобках.

Для определения значений даты и времени используется следующая форма escape-последовательности:

{тип_значения 'значение'}.

Тип значения указывается следующими символами:

  • d - для типа Date формата yyyy-mm-dd
  • t - для типа Time формата hh:mm:ss
  • ts - для типа Timestamp формата yyyy-mm-dd hh:mm:ss[.f...]

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

Пример:

UPDATE tbl1 SET OpenDate= {d '2004-01-24'} 
    WHERE FieldID=1010
UPDATE tbl1 SET OpenDate= '24-Jan-2004' 
    WHERE FieldID=1010

Escape-последовательность может передаваться как значение параметра SQL-оператора. Применение параметра позволяет в последующих реализациях быстро переходить от значения в виде escape-последовательности ("{d '2004-01-24'}") к значению, использующему естественное обозначение в соответствии с конкретным драйвером ("24-Jan-2004").

Например:

SQLCHAR    Date1[56]; // Размер даты равен 55
SQLINTEGER Date1LenOrInd = SQL_NTS;
// Определение параметров
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, 
 SQL_TYPE_DATE, 0, 0,
 Date1, sizeof(Date1), 
 &Date1LenOrInd);
// Задание значения переменной Date1 как 
// escape-последовательности.
strcpy(Date1, "{d '2004-01-24'}");
// Выполнение SQL-оператора
SQLExecDirect(hstmt, "UPDATE tbl1 SET Date1=? 
   WHERE FieldID = 1010", SQL_NTS);

Другим способом определения значения даты посредством параметра является применение структуры SQL_DATE_STRUCT. В большинстве случаев этот способ бывает более эффективен.

Например:

SQL_DATE_STRUCT Date1;
SQLINTEGER      Date1Ind = 0;
// Определение параметра
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, 
 		SQL_C_TYPE_DATE, SQL_TYPE_DATE, 0, 0,
 		&Date1, 0, &Date1Len);
// Определение полей структуры Date1
Date1.year = 2004;
Date1.month = 1;
Date1.day = 24;
// Выполнение SQL-оператора
SQLExecDirect(hstmt, "UPDATE tbl2 SET Date1=? 
    WHERE FieldID = 1010", SQL_NTS);

Для определения того, поддерживает ли конкретный ODBC-драйвер escape-последовательности для представления значений типа даты и времени, применяется функция SQLGetTypeInfo (если источник данных поддерживает типы данных для даты и времени, то он также должен поддерживать и соответствующие escape-последовательности).

Для определения того, поддерживает ли конкретный ODBC-драйвер представление значений даты и времени в виде, определяемом спецификацией ANSI SQL-92, применяется функция SQLGetInfo с опцией SQL_ANSI_SQL_DATETIME_LITERALS.