Вы на НеОфициальном сайте факультета ЭиП

На нашем портале ежедневно выкладываются материалы способные помочь студентам. Курсовые, шпаргалки, ответы и еще куча всего что может понадобиться в учебе!
Главная Контакты Карта сайта
 
Где мы?

Реклама


Лекции по SQL. Часть 6.

Просмотров: 1601 Автор: admin

Лекции по SQL

 

1.1.1.1. SQLSetScrollOptions

 

Функция SQLSetScrollOptions устанавливает опции, управляющие поведени­ем курсора, ассоциированного с оператором в трех аспектах: сетевые взаимодей­ствия, чувствительность к изменениям результирующего множества и размер ре­зуль­тирующего множества. Прототип:


RETCODE SQLSetScrollOptions(

   hstmt, // идентификатор оператора

   UWORD  fConcurrency // определяет управление сетевым взаимодействием и

// может принимать значения:

// SQL_CONCUR_READ_ONLY - модификация не допускается

// SQL_CONCUR_LOCK - курсор использует самый низкий уровень блокировки // для обеспечения возможности модификации.

// SQL_CONCUR_ROWVER - (МК) курсор использует пассивную защиту,  срав­// ни­­вая  версии строк по значениям ROWID (SQLBase) или TIMESTAMP //(Sybase)

// SQL_CONCUR_VALUES - пассивная защита с использованием сравнения зна­-

// че­ний

   SDWORD  crowKeyset,  // Число строк, для которого следует буферизовать //клю­чиили одно из значений:

//  SQL_SCROLL_FORWARD_ONLY - курсор способен передвигаться только

//впе­ред

// SQL_SCROLL_STATIC - курсор не видит изменения данных

// SQL_SCROLL_KEYSET_DRIVEN - драйвер устанавливает crowKeyset равным

// значению crowRowset. Если crowKeyset > crowRowset то crowKeyset есть число

// строк в keyset, которое должно быть сохранено драйвером в буфере. Это озна­//чает, что курсор является смешанным перемещаемым - он управляется keyset

// в пределах  keyset и является динамическим вне его.

   UWORD   crowRowset  // число строк в результирующем множестве

);

      

Функция SQLSetScrollOptions, если она вызывается, должна быть вызвана  до SQLPrepare или SQLExecDirect. Если  SQLSetScrollOptions не вызывается,  то crowRowset получает значение по умолчанию, равное 1, crowKeyset равно по умол­чанию SQL_SCROLL_FORWARD_ONLY,  и fConcurrency равно  SQL_­CON­CUR_READ_ONLY.


1.1.1.2.Модификация данных результирующего множества

 

В процессе перемещения по результирующему множеству можно изменить зна­чения, ко­то­рые были извлечены. Прямо сделать это можно только создав фра­зу WHERE, в которой перечислены все значения полей текущей строки (да и то если такая строка единственная) и выполнить SQL-оператор для обновления или удаления этой строки. Это очень громоздко. К счастью, ODBC предо­став­ля­ет более удобный метод.

Для обновления или удаления можно использовать позиционируемые UP­DATE или DELETE, которые используют текущую позицию курсора для опре­де­ле­ния на какую строку нуж­но воздействовать. Чтобы выяснить, под­дер­жи­вает ли ис­точ­ник позиционируемые опе­ра­торы спросите SQLGetInfo с fInfoType= SQL_POSITIONED_STATEMENTS. Возвращается 32-би­­то­вая  маска, которая мо­жет содержать SQL_PS_POSITIONED_DELETE, SQL_PS_POSI­TIONED­_UPDATE и SQL_PS_SELECT_FOR_UPDATE. (не нашел поддер­жи­ва­ю­щих драй­­ве­ров).

 

Чтобы выполнить позиционируемый UPDATE или DELETE, программа дол­жна выполнить следующие шаги:

1)  оператор SELECT, создающий  множество для позиционируемых UPDATE / DELETE дол­жен содержать фразу  FOR UPDATE. Его синтаксис:


select [all | distinct] <список полей>from <список таблиц>

[where <предикат> ] for update of <список полей>


Для некоторых источников фраза for update  может вызвать блокировку записей по одной или сразу всего множества.

Для выполнения позиционируемых UPDATE / DELETE необходимо имя кур­сора . Курсор создается автоматически, его имя определяется источником дан­­ных при выполнении select и имеет длину до 18 символов. Для выборки име­ни курсора нужно вызвать функцию SQLGetCursorName.


RETCODE SqlGetCursorName (

   HSTMT hstmt,  // идентификатор оператора

   UCHAR FAR * szCursor,   // возвращаемое имя курсора

   SWORD cbCursorMax,  // максимальная длина szCursor

   SWORD FAR * pcbCursor // действительная длина szCursor

);

            Программа может сама дать имя курсору оператора с помощью фун­кции

RETCODESQLSetCursorName (

   HSTMT hstmt,

   UCHAR FAR * szCursor, // имя курсора

   SWORD cbCursor  // длина имени или SQL_NTS

);

  

После  того, как программа определила имя курсора, который ссылается на select for update, она обязана установить курсор на желаемую строку с помощью SQLFetch или SQLExtendedFetch. И, наконец, можно выполнить позиционируе­мый UPDATE или DELETE, которые имеют форму:


update <таблица>set <столбец>=<выражение>[, <столбец>=<выражение>]...

where current of <имя курсора>

 

или

 

delete from <таблица> where current of <имя курсора>

 

Операторы UPDATE или DELETE должны быть выполнены с идентифика­то­рами оператора, отличными от того, который использовался для select for update. Но эти два идентификатора оператора должны принадлежать одному и тому же соединению.

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

RETCODE SQLSetPos (

   HSTMT hstmt,  // идентификатор оператора

   UWORD irow, // позиция записи в результирующем множестве. Если irow=0, то      // операция выполняется для всех строк результата

   UWORD fOption, // операция, которую надо выполнить. Может принимать

// значения : SQL_POSITION, SQL_REFRESH, SQL_UPDATE, SQL_DELETE,

// SQL_ADD

   UWORD  fLock  // специфицирует способ блокировки строки после выполнения

// операции и может принимать значения  SQL_LOCK_NO_CHANGE, SQL_LOCK_EXCLUSIVE, SQL_LOCK_UNLOCK

)          

Операции:

SQL_POSITION  - результирующее множество позиционируется на irow.

SQL_REFRESH  -  курсор позиционируется на указанную строку и освежает дан­ные  буфера для этой строки. (повторное чтение)

SQL_UPDATE  -      курсор позиционируется на указанную строку и обновляет ее содержимое текущим буфером. (есть особенности)

SQL_DELETE  -  указанная строка удаляется. Текущая строка становится не­о­пре­­де­лен­ной. Остается ли удаленная строка видимой - зависит от типа курсора. Она невидима для динамических курсоров, но видима для прочих.

SQL_ADD  -  добавляется новая строка. Куда она добавляется и будет ли она ви­ди­ма в результирующем множестве зависит от драйвера. Новая строка будет на­пол­нена данными из буфера, соответствующего irow. Обычнопрограмма разме­щает буфер для дополнительной строки для этих целей.

 

1.1.1.3. Выборка нескольких результирующих множеств

 

Иногда единственный SQL-оператор может возвращать несколько результи­ру­ю­щих  множеств, а некоторые драйверы позволяют запускать более одного SQL-оператора. Процедуры также могут содержать более одного SQL-опера­то­ра. Наконец, операторы SELECT, UPDATE, DELETE, INSERT, которые выполня­ют­ся  с массивами параметров, могут возвращать несколько результирующих мно­жеств. В таких ситуациях программе необходим способ перемещения от од­но­го результирующего множества к другому. Это достигается с помощью фун­кции SQLMoreResults.

RETCODE SQLMoreResults (HSTMT hstmt);

Эта функция выполняет перемещение на следующее ожидаемое результирующее множество. Если их больше нет, то возвращается SQL_NO_DATA_FOUND.

Пример:

rc=SQLExecDirect (hstmt, “select * from klient; select * from dohod;”);

// извлекаем данные из klient

do {

   rc=SQLFetch(hstmt);

} while (rc!=SQL_NO_DATA_FOUND);

// переключаемся на следующее множество

SQLMoreResults(hstmt);

// теперь данные из dohod

do {

   rc=SQLFetch(hstmt);

} while (rc!=SQL_NO_DATA_FOUND);

 

1.1.2.    Функции управления каталогом

 

Все функции управления каталогом имеют семь общих параметров:

 

HSTMT hstmt  // идентификатор оператора

UCHAR FAR * szTableQualifier  // квалификатор таблицы. Примером квали­фи­ка­то­ра может служить полный путь до таблицы (dBase). Чтобы выяснить, ка­ково специфическое имя источника данных для квалификатора, до вызова SQL­Alloc­Stmt следует вызвать SQLGetInfo с fInfoType=SQL_QUALIFIER_TERM. (Для dBase получено “Directory”). Не все источники поддерживают квалификаторы таблиц. Спросить об этом можно у SQLGetInfo с fInfoType=SQL_MAX_QUA­LIFIER_­NAME_LEN. Возврат нуля означает, что источник не поддерживает квалификаторы таблиц.

SWORD cbTableQualifier - длина квалификатора таблицы

UCHAR FAR *szTableOwner // имя собственника таблицы

Чтобы выяснить, поддерживает ли источник имена собственников, вызовите SQL­GetInfo с fInfoType=SQL_MAX_OWNER_NAME_LEN. Возврат нуля означа­ет  отсутствие поддержки. Для определения имени собственника вызовите SQL­GetInfo с fInfoType=SQL­_OWNER_TERM.

            SWORD cbTableOwner // длина имени собственника

            UCHAR FAR * szTableName // имя таблицы

            SWORD cbTableName // длина имени таблицы         

 

Все три имени - квалификатор, собственник, таблицы допускают шаблоны поиска с использованием символов ‘%’,’-’           , а также определенные драйвером ESCAPE-символы. % означает любую последовательность символов, а ‘-’ - отдельный символ. Справиться о ESCAPE-символе у драйвера можно обратив­шись  к SQLGetInfo с fInfoType=SQL_SEARCH_PATTERN_ESCAPE. (для dBase - ‘\’).

            Все функции управления каталогом возвращают стандартные результирую­щие множе­ства.

 

1.1.2.1. SQLTables


RETCODE SQLTables(

HSTMT hstmt,

UCHAR FAR * szTableQualifier,

SWORD cbTableQualifier,

UCHAR FAR * szTableOwner,

SWORD cbTableOwner,

UCHAR FAR *szTableName,

SWORD cbTableName,

UCHAR FAR *szTableType,

SWORD cbTableType);


Если szTableQualifier - строка % и szTableOwner и szTableName - пустые строки, результирующее множество содержит список имеющихся квалификато­ров для источника и все столбцы, кроме TABLE_QUALIFIER содержат NULL.

Если  szTableOwner - “%”, а szTableQualifier и szTableName - пустые строки, то результат содержит список владельцев (TABLE_OWNER), а остальные столбцы - NULL.

            szTableType может содержать строки вида “%”,“TABLE”,”VIEW”,”TABLE,VIEW” или быть пустым. Множество типов состоит из :

"TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM" .

SQLTables возвращает стандартное результирующее множество, упорядо­чен­ное по TABLE_TYPE, TABLE_QUALIFIER, TABLE_OWNER, и TABLE_­NAME.  Множество имеет следующие столбцы:

TABLE_QUALIFIER   Varchar(128)

TABLE_OWNER         Varchar(128)

TABLE_NAME            Varchar(128)

TABLE_TYPE              Varchar(128)


1.1.2.2. SQLColumns


SQLColumns возвращает список имен столбцов в указанных таблицах .

RETCODE SQLColumns(

HSTMT hstmt,

UCHAR FAR *szTableQualifier,

SWORD cbTableQualifier,

UCHAR FAR *szTableOwner,

SWORD cbTableOwner,

UCHAR FAR *szTableName,

SWORD cbTableName,

UCHAR FAR *szColumnName,

SWORD cbColumnName);


Функция обычно вызывается перед выполнением SQL - оператора. Резуль­ти­рующее множество содержит столбцы:

TABLE_QUALIFIER            Varchar(128)

TABLE_OWNER             Varchar(128)

TABLE_NAME                 Varchar(128)

COLUMN_NAME             Varchar(128)

DATA_TYPE                Smallint  - SQL тип данных

PRECISION                  Integer       

LENGTH                                Integer          

SCALE                                 Smallint         

RADIX                                   Smallint         

NULLABLE                  Smallint

REMARKS                   Varchar(254)        


Пример:

#define STR_LEN 128+1

#define REM_LEN 254+1

/* Описание данных для приема результата  */


UCHAR  szQualifier[STR_LEN], szOwner[STR_LEN];

UCHAR  szTableName[STR_LEN], szColName[STR_LEN];

UCHAR  szTypeName[STR_LEN], szRemarks[REM_LEN];

SDWORD Precision, Length;

SWORD  DataType, Scale, Radix, Nullable;

/* Переменные для приема длин полей */

SDWORD cbQualifier, cbOwner, cbTableName, cbColName;

SDWORD cbTypeName, cbRemarks, cbDataType, cbPrecision;


SDWORD cbLength, cbScale, cbRadix, cbNullable;


/* Все квалификаторы, все владельцы, в таблице  EMPLOYEE все столбцы */

 

retcode=SQLColumns(hstmt, NULL, 0, NULL, 0, "EMPLOYEE",

SQL_NTS, NULL, 0);


if (retcode == SQL_SUCCESS) {

            /* Связывание столбцов */

            SQLBindCol(hstmt, 1, SQL_C_CHAR, szQualifier, STR_LEN,&cbQualifier);

            SQLBindCol(hstmt, 2, SQL_C_CHAR, szOwner, STR_LEN, &cbOwner);

            SQLBindCol(hstmt, 3, SQL_C_CHAR, szTableName, STR_LEN,&cbTableName);


SQLBindCol(hstmt, 4, SQL_C_CHAR, szColName, STR_LEN, &cbColName);

            SQLBindCol(hstmt, 5, SQL_C_SSHORT, &DataType, 0, &cbDataType);

            SQLBindCol(hstmt, 6, SQL_C_CHAR, szTypeName, STR_LEN, &cbTypeName);

            SQLBindCol(hstmt, 7, SQL_C_SLONG, &Precision, 0, &cbPrecision);

            SQLBindCol(hstmt, 8, SQL_C_SLONG, &Length, 0, &cbLength);

            SQLBindCol(hstmt, 9, SQL_C_SSHORT, &Scale, 0, &cbScale);

            SQLBindCol(hstmt, 10, SQL_C_SSHORT, &Radix, 0, &cbRadix);

            SQLBindCol(hstmt, 11, SQL_C_SSHORT, &Nullable, 0, &cbNullable);


SQLBindCol(hstmt, 12, SQL_C_CHAR, szRemarks, REM_LEN, &cbRemarks);


            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;

                        }

            }

}

Среди других функций, работающих с каталогом упомянем:

SQLStatistics выполняет выборку данных об одной таблице и ее индексах.

SQLPrimaryKeys возвращает имена полей, образующих первичный ключ.

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

SQLSpecialColumns выбирает следующие данные для одной таблицы:

- оптимальный набор полей, уникальный набор полей, уникальным образом иден­тифицирующих запись.

- столбцы, которые автоматически обновляются при изменении записи.   

SQLColumnPrivileges возвращает список столбцов и ассоциированные с ними привилегии.

 

1.1.3.    Транзакции

 

Транзакцией называется группа из одного и более SQL-операторов, выпол­ня­е­мых как единый функциональный блок, когда все они должны быть завер­ше­ны или все должны быть НЕ выполнены. Транзакция должна быть либо заверше­на, либо должен быть выполнен ее откат. Завершение транзакции означает поме­ще­ние ее результатов в БД. Откат не восстанавливает изменения, его осущест­вле­ние равносильно тому, что операторы в этой транзакции никогда не выполня­лись.

Некоторые драйверы вообще не поддерживают транзакции, другие имеют ограничения. Чтобы выяснить какой уровень обработки транзакций поддер­жи­ва­ет драйвер следует обратиться к SQLGetInfo с fInfoType=SQL_TXN_CAPABLE.

Возможные возвращаемые значения:

SQL_TC_NONE,  SQL_TC_DML, SQL_TC_DDL_COMMIT, SQL_TC_DDL_IGNORE, SQL_TX_ALL.

Первое означает, что обработка транзакций не поддерживается. Три следующих возможных значения означают, что DDL (Data Definition Language) операторы поддерживаются. SQL_TC_DML означает, что DDL (Data Manipulation Language) операторыбу­дут вызывать ошибку при выполнении их в транзакции. SQL_TC_COMMIT указывает, что DDL-операторы будут вызывать завершение транзакции. SQL_TC_DDL_IGNORE означает, что DDL-операторы будут игно­ри­ро­ваться в транзакции. SQL_TX_ALL означает полную поддержку.

Далее следует определить разрешены ли повторные активные транзакции. Для этого следует обратиться к SQLGetInfo с fInfoType=SQL_MULTIPLE_ACTIVE_TXN.  Возвращается Y/N.

Существует два режима выполнения транзакций - ручной и автоматический. В автоматическом режиме драйвер открывает транзакцию в начале исполнения SQL­-­оператора и закрывает по его завершении. В ручном режиме драйвер от­кры­вает транзакцию при запуске SQL-оператора, если она не была открыта. За­­крыть транзакцию должна программа. Для установки режима следует вызвать SQL­SetConnectOption с fOption=SQL_AUTOCOMMIT и vParam, равным SQL­_­AUTOCOMMIT_ON (автоматический) или SQL_AUTOCOMMIT_OFF (ручной). Для завершения или отката транзакции используется функция SQLTransact.


1.1.3.1. SQLTransact

Функция имеет прототип:

RETCODE SQLTransact (HENV henv, HDBC hdbc, UWORD fType);

fType может принимать значения SQL_COMMIT и SQL_ROLLBACK. Если hdbc равно SQL_NULL_HDBC, а henv - правильный идентификатор окружения, то ме­не­джер драйверов попытается завершить или откатить транзакции во всех сое­ди­не­ниях. SQL_SUCCESS будет возвращено только если будет получено SQL­_SUCCESS во всех соединениях. Чтобы узнать какое соединение получило SQL_ERROR нужно вызвать SQLError для каждого соединения. Если же hdbc - правильное соединение, то henv игнорируется.

Чтобы определить как транзакции влияют на курсоры следует вызвать SQLGetInfo с fOption, равным SQL_CURSOR_ROLLBACK_BEHAVIOR  и SQL­_CURSOR_COMMIT_BEHAVIOR. Если хотя бы одно из этих значений воз­вра­щает SQL_CB_DELETE, то это означает, что SQLTransact закрывает и уда­ля­ет все открытые курсоры и отбрасывает их результаты. SQLTranzact не осво­бож­да­ет идентификаторы операторов, но их операторы требуют выполнения SQLPre­pare. Программа может их использовать повторно или может вызвать SQL­Free­Stmt.

Если значения SQL_CURSOR_ROLLBACK_BEHAVIOR или SQL_CURSOR­_­COMMIT_BEHAVIOR равны SQL_CB_CLOSE, SQLTransact закрывает все от­кры­тые курсоры  всех hstmt, относящихся к hdbc. Все hstmt остаются размещен­ными  и подготовленными, то есть можно вызвать SQLExecute без SQLPrepare.

Если значения SQL_CURSOR_ROLLBACK_BEHAVIOR или SQL_CURSOR­_­COMMIT_BEHAVIOR равны SQL_CB_PRESERVE, SQLTransact не влияет на открытые курсоры, связанные с hdbc и они остаются в прежнем положении.

 

1.1.3.2. Уровни изоляции

Уровень изоляции транзакций соединения определяет каким образом тран­зак­ции изолируются друг от друга, когда они используют одни и те же данные. Рассмотрим взаимодействия транзакций, в которых проявляется их вредное вза­и­мо­влияние. Пусть t1 и t2 - транзакции.

 

Грязное чтение.

1. t1 изменяет строку данных.

2. t2 читает эту строку

3. t1 выполняет откат.

Теперь t2 работает со строкой, которая никогда не существовала в БД.

Неповторяемое  чтение.

1. t1 читает строку

2. t2 обновляет или удаляет эту строку

3. t2 завершается

Если t1 попытается повторить чтение, то либо этой строки уже нет, либо она содержит другие данные.

Иллюзии. (Фантомы)

1. t1 выбирает множество строк, удовлетворяющих некоторому критерию поиска.

2. t2 добавляет новую строку тоже удовлетворяющую этому критерию.

Если t1 повторно выполнит свой запрос, то результат будет содержать новую строку.

 

Чтобы предотвратить эти неприятности ODBC предоставляет возможность изолировать транзакции одну от другой с помощью установки с помощью фун­кции SQL­Set­ConnectOption одного из пяти воз­можных уровней изоляции:

 

SQL_TXN_READ_UNCOMMITED

Уровень не имеет ограничения на чтение данных. Следовательно, данные, ко­то­рые обновляются, удаляются, добавляются могут быть всем видимы, даже ес­ли  эти изменения не завершены. Этот уровень обеспечивает максимальную па­ра­ллельность и минимальную изоляцию. Это лучший вариант в том случае, когда мно­го транзакций читает данные, но лишь одна их обновляет. Его не следует ис­поль­зовать когда возможно несколько одновременных изменений, так как дан­ные могут быть легко испорчены.

 

SQL_TXN_READ_COMMITED

Данные незавершенных транзакций не будут читаться. Уровень отсекает толь­ко грязное чтение.


SQL_TXN_REAPETABLE_READ

Уровень гарантирует повторяемость чтения. Иллюзии возможны. Назначе­ние этого уровня может повлечь за собой блокировку выбранных данных. Уро­вень часто является приемлемым  при модификации данных несколькими поль­зователями

 

SQL_TXN_SERIALIZABLE

Требование сериализуемости  - довольно жесткое, но гарантирует сохра­не­ние целостности данных при их модификации несколькими  пользователями.

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

 

SQL_TXN_VERSIONING

Транзакции имеют версии, если драйвер (или СУБД) сохраняет целостность данных с помощью отдельных версий страницы данных для каждой транзакции. Этот метод обеспечивает обеспечить такую же целостность, что и требование се­ри­ализуемости, но дает более высокую степень параллельности.

 

К сожалению, большинство драйверов не имеют уровней выше SQL_­TXN­_READ_COMMITED.

Схема блокировок, обеспечиваемая различными уровнями изоляции, пол­ностью управляется источником данных. Программа не должна выполнять дей­ствия для блокировки и освобождения записей и таблиц. В большинстве случаев лучше установить подходящий уровень изоляции и положиться на драйвер. Не­ко­торые возможности для управления блокировками предоставляют SQLSet­Stmt­Option и SQLSetPos. Однако, как правило, драйверы, поддерживающие транзакции, не поддерживают блокировок с помощью SQLSetPos.

 



Популярные новости

Статистика сайта



Rambler's Top100



 
Copyright © НеОфициальный сайт факультета ЭиП