Робота з об'єктами великого обсягу в MS SQL і ADO
Олексій Ширшов
Вступ
Ця стаття з'явилася на світ лише завдяки вашим НЕ
перестав з'являтися питань типу: «Хто-небудь може привести приклад коду
для роботи з полями бази, що містять зображення ... використовуючи ADO і Visual C + + ... », і
тому, що мені лінь на них відповідати.
Робота в MS SQL
Давайте спочатку розберемося, як працювати з великими
об'єктами (LOB - large objects) на рівні бази даних. MS SQL Server
підтримує такі типи великих об'єктів:
image - містить бінарні дані змінної довжини.
Довжина не може перевищувати 2 гігабайт.
text - містить текстові дані змінної довжини в
кодуванні сервера (in code page of the server). Довжина не може перевищувати 2
гігабайт.
ntext - містить текстові дані в Unicode-форматі.
Довжина не може перевищувати 2 гігабайт.
Для зберігання даних всіх цих типів і низькорівневою
роботи з ними SQL Server використовує один і той же механізм.
Фізичне розміщення великих об'єктів
MS SQL Server 2000 підтримує два методи зберігання
великих об'єктів: перший метод на заради сумісності з старими версіями
і не забезпечує належної продуктивності в певних випадках, за
порівняно з новим методом. За замовчуванням сервер працює у старому режимі.
При використанні старого методу сервер завжди
розміщує дані в окремих сторінках, а покажчик на перший з них зберігає
безпосередньо в рядку даних.
ПРИМІТКА
Точніше, в рядку даних зберігається покажчик
на корінь B-tree, а не на будь-які таблиці даних. Докладніше про це, см.
наступний розділ.
Використовуючи нову стратегію, сервер може зберігати частину
даних безпосередньо в рядку таблиці. Це призводить до економії пам'яті та
збільшення продуктивності для LOB-ів невеликого розміру.
Стратегія розміщення за замовчуванням
Як структури зберігання даних використовується
B-tree. У рядку даних зберігається 16-байтним покажчик на корінь дерева --
структуру розміром 84 байта. Якщо розмір даних не перевищує 32 Кб, в кореневий
структурі зберігаються посилання на блоки даних, розташованих на цій же або інших
сторінках. Великі об'єкти зберігаються на спеціальних сторінках, на яких не можна
розміщувати ніякі інші дані, крім image, text і ntext. Проте дані цих
типів з різних таблиць можуть бути розміщені на одній сторінці. Якщо загальний
розмір даних не більше 64 байт, всі дані зберігаються в кореневій структурі.
Малюнок 1.
Якщо розмір даних більше 32 Кб, корінь дерева
посилається на проміжні вузли. Проміжні вузли розташовуються на окремих
сторінках, які не можуть містити будь-які інші дані, або
проміжні вузли інших таблиць або навіть інших колонок даної таблиці.
Покращена стратегія
У SQL Server 2000 з'явилася можливість використовувати
новий метод зберігання великих об'єктів. У ньому відсутня 16-байтним покажчик.
У рядку даних (data row) можуть знаходитися як самі дані (у випадку, якщо вони
менше заданої величини), так і корінь B-tree. Для кожної таблиці розмір
збережених великих об'єктів можна задавати індивідуально за допомогою процедури
sp_tableoption. Перевірити режим розміщення можна за допомогою інструкції
objectproperty з параметром TableTextInRowLimit. У наступному скрипті створюється
таблиця (яку ми будемо використовувати протягом всієї статті) blob_test,
потім перевіряється режим розміщення даних у цій таблиці, і, нарешті,
встановлюється розмір даних в рядку (350 байт), що автоматично ставить
поліпшену стратегію розміщення великих об'єктів у таблиці.
create table blob_test (id int identity, img image, txt text, ntxt ntext)
select case
when
OBJECTPROPERTY (object_id ( 'blob_test'), 'TableTextInRowLimit') = 0
then 'data outside the table'
else 'data in row'
end
sp_tableoption blob_test, 'text in row', 350
Замість розміру великих об'єктів у процедуру
sp_tableoption можна було передати значення On. У цьому випадку розмір
установлюється рівним 250 байтах. Вимкнути розміщення даних у рядку можна,
задавши в якості параметра значення 0 або Off. Максимальний розмір даних в
рядку дорівнює 7000 байт. Наступний малюнок ілюструє схему розподілу
даних при розмірі, що перевищує 350 байт (для нашої таблиці).
Малюнок 2
Якщо в рядку даних присутня розширюване поле
типу varchar або varbinary, то при його розширенні, якщо загальний розмір рядка
перевищить 8060 байт, частина даних з рядка може бути вивантажено на
додаткові сторінки. Іншими словами, інші поля мають пріоритет перед
LOB при нестачі простору в рядку даних. Повернемо нашу таблицю в початкове
стан, тому що наступні приклади розраховані на режим за замовчуванням:
sp_tableoption
blob_test, 'text in row', 'off'
Після перекладу таблиці в режим «дані в рядку» самі
дані в рядок не переносяться, проте зворотна дія викликає негайну
операцію з перенесення даних на окремі сторінки. При цьому вся таблиця
повністю блокується, а при великій кількості їх переносите даних операція
може зайняти тривалий час.
Робота з великими даними
У роботі з двійковими даними на рівні сервера
великого сенсу немає. Тому більшість прикладів використовує текстові дані,
хоча що описуються процедури цілком згодяться і для бінарних даних.
При роботі з LOB можна використовувати звичайні оператори
SQL (select, insert). Але іноді може знадобитися працювати не з LOB цілком, а
з його частинами. Оператори роботи з такими невеликими порціями досить незвичайні
для SQL тим, що в них використовуються покажчики, зміщення та інші низькорівневі
поняття.
Покажчик являє собою 16-байтове змінну
типу binary або varbinary. Це абстракція, що вказує на дані в конкретній
колонці конкретної рядка. Покажчик виходить шляхом виклику функції textptr,
куди передається ім'я колонки. Він може бути дорівнює NULL в тому випадку, якщо даних
не існує. Якщо покажчик дорівнює NULL, ви не можете використовувати функції
READTEXT, WRITETEXT і UPDATETEXT. Покажчик повинен містити будь-яке
значення, тому для правильної роботи цих функцій у колонці спочатку
повинні міститися дані. Для простоти ми запишемо туди наступні значення:
insert
into blob_test values (0x0, 'My wife is Rosa', 'My son is Dima')
Значення для колонки типу image повинні вказуватися в
шістнадцятковому форматі, а для типів text і ntext це повинні бути рядки.
Для всіх операторів DML, які змінюють дані, попереднє
значення всієї рядки скидається в лог транзакцій, проте для операторів
WRITETEXT і UPDATETEXT це залежить від моделі відновлення бази даних. Для
моделі Bulk logged дані не записуються в лог транзакцій, натомість
змінені сторінки позначаються особливим чином і записуються в архів логу
транзакцій при виклику відповідної операції архівування.
READTEXT
Цей оператор призначений для блочного читання великих
текстових і бінарних даних:
READTEXT
(Table.column text_ptr offset size) [HOLDLOCK]
Параметри:
table.column - таблиця і колонка;
text_ptr - дороговказ, отриманий за допомогою функції
textptr;
offset - зсув, з якого починається читання
даних;
size - розмір прочитуються даних.
Приклад:
declare @ p binary (16)
select @ p = textptr (txt)
from blob_test
where id = 1
select case
when @ p is not null then '@ p is
valid '
else '@ p is invalid'
end
if @ p is not null
READTEXT blob_test.txt @ p 0 4
Для пошуку певного текстового фрагмента потрібно
скористатися функцією PATINDEX. Вона не така зручна, як хотілося б (наприклад,
відсутня можливість шукати, починаючи з певної позиції), але цілком
підходить для простих операцій. У наступному прикладі виводиться весь текст після
слова is.
declare @ p binary (16)
declare @ idx int, @ l int
select @ p = textptr (ntxt),
@ idx = patindex ( '% is%', ntxt) -1,
@ l =
datalength (ntxt)/2 - (patindex ( '% is%', ntxt) -1)
from blob_test
where id = 1
if @ p is not null
readtext blob_test.ntxt @ p
@ idx @ l
Тут хочеться відзначити дві особливості: patindex
повертає зсув щодо початку рядка в символах, рахуючи від одиниці,
тоді як readtext сприймає зміщення від нуля, а datalength повертає довжину
даних в байтах, так що для типу ntext ми повинні поділити її на два.
Давайте замислимося, що станеться, якщо хто-небудь
спробує змінити дані між операціями одержання покажчика та його
використання. Нічого особливого, просто SQL Server видасть помилку 7123,
яка говорить, що була спроба використовувати недійсний вказівник. Однією
перевірки на NULL виявляється недостатньо. Для перевірки покажчика на дійсність
потрібно скористатися функцією textvalid. Однак ця перевірка не позбавляє нас
від проблеми, а лише допомагає виявити її. Нам потрібно, щоб для даного покажчика
дотримувалася умова повторюваного читання. Цього найпростіше добитися, використавши
у запиті хинт REPEATABLEREAD. Перепишемо приклад наступним чином:
declare @ p binary (16)
declare @ idx int, @ l int
begin tran
select @ p = textptr (ntxt),
@ idx =
patindex ( '% is%', ntxt) -1,
@ l =
datalength (ntxt)/2 - (patindex ( '% is%', ntxt) -1)
from blob_test (REPEATABLEREAD)
where id = 1
if textvalid (@ p) = 1 and @ idx> = 0 and @ l> 0
readtext blob_test.ntxt @ p
@ idx @ l
commit
Тепер код написаний «за всіма правилами»:
на рядок з ідентифікатором 1 накладається
колективна блокування, що запобігає її зміни з інших транзакцій;
зсув перевіряється на негативні значення, так
як функція patindex може повернути 0, якщо не знайде шаблон;
довжина зчитує тексту також перевіряється на
невід'ємні значення.
Функція READTEXT не поверне вам усього обсягу даних.
Розмір максимально доступних даних, які можна отримати за допомогою цієї
функції, дорівнює @ @ textsize. За замовчуванням це значення дорівнює 4 Кб. Збільшити його
можна за допомогою функції set textsize. Для скидання змінної в значення за замовчуванням
встановіть розмір, що дорівнює нулю.
WRITETEXT
Ця функція залишена тільки для сумісності. Її
замінила більш потужна UPDATETEXT, яку я розгляну пізніше.
Ось синтаксис функції WRITETEXT:
WRITETEXT
(Table.column text_ptr)
[WITH
LOG] (data)
table.column - таблиця і колонка;
text_ptr - покажчик;
with log - ігнорується для SQL Server 2000;
Data - дані. Їх розмір не може перевищувати 120 Кб.
WRITETEXT повністю перезаписує вміст стовпчика.
Для операції оновлення також актуальна проблема дійсності покажчика. Але
тут вже недостатньо просто встановити колективну блокування на оновлювану
рядок, тому що це може призвести до взаімоблокіровке, наприклад, якщо дві
транзакції одночасно отримують колективну блокування та намагаються
конвертувати її в монопольну. Для запобігання подібній ситуації
необхідно накласти блокування оновлення. У наступному прикладі проводиться
оновлення бінарних даних в першому рядку:
declare
@ p binary (16)
begin
tran
select
@ p = textptr (img)
from
blob_test (updlock)
where
id = 1
if
textvalid ( 'blob_test.img', @ p) = 1
writetext blob_test.img @ p 0x4034
commit
Більш детально механізм блокувань в MS SQL Server та
поняття рівнів ізоляції транзакцій розглянуті в попередньому номері журналу.
UPDATETEXT
Ця більш потужна функція оновлення даних, ніж
WRITETEXT. Вона також дозволяє копіювати дані з іншої колонки (правда,
не можна вказати розмір копіюються в цьому випадку даних). Ось її синтаксис:
UPDATETEXT (table_name.dest_column_name dest_text_ptr)
(NULL | insert_offset)
(NULL | delete_length)
[WITH LOG]
[inserted_data
| (table_name.src_column_name
src_text_ptr)]
table_name.dest_column_name
- Таблиця і колонка.
dest_text_ptr - покажчик на оновлювану область.
insert_offset - зсув у байтах, за яким будуть
змінюватися дані. Якщо вказується NULL, дані будуть додані до поточних
даними.
delete_length - кількість видаляються байт. Якщо
вказується NULL, дані будуть віддалені від зсуву до кінця. Для вставки даних
необхідно вказати значення 0.
with log - не має значення на SQL Server 2000.
inserted_data - вставляються дані.
table_name.src_column_name - таблиця і колонка, звідки
дані вставляються.
src_text_ptr - покажчик на вихідні дані.
Наступні два виклики аналогічні:
WRITETEXT
table.column text_ptr inserted_data
UPDATETEXT
table.column text_ptr 0 NULL inserted_data
Давайте розглянемо приклад. Припустимо, я помилився,
набираючи ім'я своєї дружини, і мені його зараз необхідно замінити:
declare @ p binary (16)
declare @ l int, @ idx int
begin tran
select @ p = textptr (txt),
@ idx =
patindex ( '% Rosa%', txt) -1,
@ l =
datalength (txt) - (patindex ( '% Rosa%', txt) -1)
from blob_test (updlock)
where id = 1
if textvalid ( 'blob_test.txt', @ p) = 1
updatetext blob_test.txt @ p @ idx
4 '[Correct name]'
commit
Мабуть, це все. Залишилося ще одна тонкість.
Дані в рядку
Читаючи Books Online, я натрапив на таку пропозицію:
After you have
turned on the text in row option, you cannot use the READTEXT, UPDATETEXT or
WRITETEXT statements, to read or modify parts of any text, ntext, or image
value stored in the table.
Ось це так! Тобто я не можу користуватися функціями,
наведеними вище, якщо таблиця знаходиться в режимі «дані в рядку»? Це
неправда. Хоча ось такий приклад може переконати кого завгодно:
declare @ p binary (16)
declare @ idx int, @ l int
select @ p = textptr (ntxt),
@ idx =
patindex ( '% is%', ntxt) -1,
@ l = datalength (ntxt)/2 - (patindex ( '% is%', ntxt) -1)
from blob_test (repeatableread)
where id = 1
if textvalid ( 'blob_test.ntxt', @ p) = 1
readtext blob_test.ntxt @ p 0
14
Він видає помилку:
You cannot use a
text pointer for a table with option 'text in row' set to ON.
Справа в тому, що в режимі «дані в рядку» покажчик
становить невірним відразу ж після закінчення транзакції. Так як SQL Server по
умовчанням знаходиться в режимі автоматичного підтвердження транзакції (auto
commit), покажчик перестає бути дійсним відразу після виконання
запиту. Щоб наш приклад запрацював, необхідно включити обидві операції
(одержання покажчика та його використання) в одну транзакцію. Крім цього, SQL
Server автоматично встановлює колективну блокування в момент одержання
покажчика для даних в рядку, так що не потрібно вдаватися до будь-яких Хінта.
Це блокування зніметься після того, як покажчик стане недійсним. Як
я сказав, це відбувається в кінці транзакції або при використанні наступних
команд:
create clustered
index
drop clustered
index
alter table
drop table
truncate table
sp_tableoption ( 'text
in row ')
sp_indexoption
Можна і вручну зробити покажчик недійсним з
допомогою виклику функції sp_invalidate_textptr.
Якщо транзакція виконується на рівні ізоляції READ
UNCOMMITTED, отриманий покажчик можна використовувати лише в операціях читання.
Операція оновлення закінчиться помилкою 7106: You cannot update a blob with a read-only text
pointer.
Робота з ADO
У цьому розділі я приведу приклади роботи з великими
об'єктами, використовуючи ADO.NET і ADO. Почнемо з простого.
Читання зображення і виведення на екран за допомогою VB6
Хоча VB6 вже не так популярний, як кілька років
тому, на ньому все-таки дуже зручно писати певного виду програми. Я іноді
просто дивуюся, як багато VS.NET перейняла з-посеред VB6, аж до іконок.
Програмуючи на C #, ви, як і раніше, в меню Project можете знайти пункт
References, а в списку подій форми - події OnLoad. Дуже зручна технологій
DataBinding, яку я і буду використовувати в прикладах, також благополучно
перекочувала в дотнет. Насправді, дуже багато знань з «колишнього життя» ви
можете використовувати в новому середовищі. Ось тільки я не розумію, чому
сумісності в ADO.NET приділено найменше уваги. Наприклад, той же самий
DataBinding не працює зі старим ADO. Замість цього потрібно «заливати» ADO-шний
Recordset в DataSet, і використовувати вже його. Ну, вистачить лірики, давайте
перейдемо до предмету розмови.
Алгоритм виведення зображення на екран з БД може бути
таким:
Підготовка з'єднання;
Відкриття з'єднання;
Вибірка даних в Recordset;
Зв'язування зображення за допомогою вбудованої технології
DataBinding.
Ось фрагменти коду з демонстраційного додатки,
реалізують цей алгоритм.
'Задаємо провайдера
conn.Provider =
"sqloledb"
sb.SimpleText = "Connecting to DB ..."
sb.Refresh
'Відкриваємо з'єднання з БД
conn.Open "localhost", "user", "psw"
sb.SimpleText = "Ready"
sb.SimpleText = "Loading image ..."
sb.Refresh
Dim ra As Long
'Створюємо Recordset
Set rs = conn.Execute ( "select * from blob_test", ra)
'Виробляємо зв'язування
даних
Set imgImg.DataSource = rs
'Тут виконується
фактична пересилання даних
'і виведення зображення на
екран
imgImg.DataField = "img"
sb.SimpleText = "Ready"
...
Якщо потрібно просто зберегти графічний об'єкт в файл
на диску, алгоритм дещо змінюється. Замість зв'язування даних потрібно відкрити
файл на запис та записати в нього дані. Проте все не так просто:
'Відкриваємо фото як
бінарний для запису
Open "c: temp_img.bmp" For Binary Access Write As # 1
Dim b () As Byte
'Виділяємо пам'ять під масив
ReDim b (Len (rs.Fields ( "img"). Value))
'Копіювання даних в
масив
b =rs.Fields ( "img"). Value
'запис у файл
Put # 1,, b
Close # 1
У цьому прикладі мені довелося скопіювати дані під
тимчасовий буфер, так як інструкція Put додає до деяких типів, екземпляри
яких ви хочете зберегти, різні заголовки. Навіщо це зроблено, мені не зовсім
зрозуміло; мабуть розробники хотіли спростити реалізацію
збереження/відновлення стану змінних програми, однак це у них не
дуже добре вийшло - для об'єктів ця інструкція не підтримується. У
випадку збереження таким чином:
Put
# 1,, rs.Fields ( "img"). Value
в файл запишеться одному лише богу відомий заголовок,
який буде заважати сприймати це фото як стандартний bmp. Тому я
змушений копіювати дані в додатковий масив байтів та зберігати вже його.
Для читання графічної інформації з файлу можна
скористатися інструкцією Get.
Все йде добре до тих пір, поки не знадобиться читати/писати
бінарні дані невеликими блоками. Тут на допомогу приходять такі методи:
AppendChunk - застосуємо до полів з атрибутом adFldLong.
Якщо метод викликаний перший раз з тих пір, як ви редагуєте поточне поле,
дані перезаписуються. Інакше - метод додає дані до існуючого
значенням. Іншими словами, якщо ви тільки почали редагувати поле, викликавши
метод AppendChunk, що містяться в ньому до цього значення будуть втрачені. Однак
наступні виклики методу будуть додавати дані до існуючого значенням. Як
тільки ви почнете редагувати інше поле, можливість додавати дані
зникне. Цей метод також можна викликати для параметрів з встановленим
атрибутом adParamLong. Для параметрів дані завжди додаються до існуючих.
GetChunk - застосуємо до полів з атрибутом adFldLong.
Повертає вказану кількість байтів з позиції, на якій закінчилося
попереднє зчитування даних. До тих пір, поки ви не перейдете до роботи з
іншим полем, дані зчитуються послідовно. Якщо ви почали працювати
з іншим полем, а потім повернулися до цього, дані знову будуть читатися з
нульового зсуву.
Ці два методи дозволяють працювати з порціями (chunks)
даних. Наприклад, ось такий код дозволяє вважати лише перші 100 байт
даних:
Dim
b () As Byte
b =
rs.Fields ( "img"). GetChunk (100)
Це все чудово, але як же рекомендації MSDN
використовувати більш гнучкий об'єкт Stream? Зараз ми і до нього доберемося.
Робота з зображенням за допомогою Stream на С + +
Я вибрав С + +, так як з VB6 ми вже попрацювали
(гарного помаленьку), і тому, що більшість питань стосується саме С + +.
(На RSDN ходять справжні індіанці.)
Алгоритм дій приблизно той же, що і в попередньому
прикладі:
Підготовка з'єднання.
Відкриття з'єднання.
Вибірка даних в Recordset.
Створення та відкриття об'єкта Stream.
Читання даних в Stream.
Робота зі Stream.
Код реалізації алгоритму наведено далі, а зараз я б
хотів зупинитися на шостому пункті, так як він виглядає занадто розпливчасте, а
ми, програмісти, і чужі невизначеності.
Об'єкт Stream (потік) призначений спеціально для
роботи з нереляціоннимі і двійковими даними. Його можливості дуже великі.
Він підтримує інтерфейс IStream, а отже, його можна
спокійно пересилати по мережі, зберігати в складовою файл і завантажувати з нього зображення.
Його можна зберігати/завантажувати в/з файлу.
Його можна зберігати/завантажувати в/з текстового рядка.
Він може завантажувати дані з об'єкта Record або
будь-якого ресурсу по URL.
Його можна клонувати.
Для ілюстрації роботи з об'єктом Stream наведу-таки
приклад на VB6, який зберігає зображення в файл без використання
інструкції Put:
Dim stream As New ADODB.stream
'Тип потоку - бінарний
stream.Type = adTypeBinary
'Відкриваємо порожній
stream.Open
'Записуємо значення поля img
stream.Write rs.Fields ( "img")
'Созраняем в файл
stream.SaveToFile
"c: temp_img.bmp"
Але чи вистачить БЕЙСІК (принаймні, VB6 в цій статті
більше не зустрінеться), перейдемо до реалізації описаного вище алгоритму.
void ShowError ()
(
CComPtr ef;
GetErrorInfo (NULL, & ef);
CComBSTR desc;
ef-> GetDescription (& desc);
USES_CONVERSION;
MessageBox (NULL, OLE2T (desc), _T ( "Error"), MB_ICONERROR);
)
void LoadPicture (IPicture ** pic)
(
// Створюємо з'єднання
HRESULT hr;
hr =
conn.CoCreateInstance (L "ADODB.Connection ");
if (FAILED (hr )){
ShowError ();
return;
)
// Встановлюємо провайдера
hr =
conn-> put_Provider (CComBSTR (L "sqloledb "));
if (FAILED (hr )){
ShowError ();
return;
)
// Відкриваємо з'єднання
hr =
conn-> Open (CComBSTR (L "Data
source = localhost "), CComBSTR (L" user "), CComBSTR (L" psw "));
if (FAILED (hr )){
ShowError ();
return;
)
// Створюємо Recordset
CComPtr rs;
hr =
rs.CoCreateInstance (L "ADODB.Recordset ");
if (FAILED (hr )){
ShowError ();
return;
)
// Відкриваємо Recordset
hr =
rs-> Open (CComVariant (L "blob_test"), CComVariant (conn ));
if (FAILED (hr )){
ShowError ();
return;
)
// Отримуємо Fields
CComPtr flds;
hr =
rs-> get_Fields (& flds);
if (FAILED (hr )){
ShowError ();
return;
)
// Отримуємо бінарне поле
CComPtr fld;
hr =
flds-> get_Item (CComVariant (L "img"), & fld);
if (FAILED (hr )){
ShowError ();
return;
)
// Прочитуємо значення
CComVariant v;
hr = fld-> get_Value (& v);
if (FAILED (hr )){
ShowError ();
return;
)
CComVariant
vtMissing (DISP_E_PARAMNOTFOUND, VT_ERROR);
// Створюємо Stream
CComPtr
stream;
hr =
stream.CoCreateInstance (L "ADODB.Stream ");
ATLASSERT (SUCCEEDED (hr ));
// Задаємо тип вмісту
hr = stream-> put_Type (adTypeBinary);
if (FAILED (hr )){
ShowError ();
return;
)
// Відкриваємо Stream
hr =
stream-> Open (vtMissing);
if (FAILED (hr )){
ShowError ();
return;
)
// Записуємо дані в Stream
hr = stream-> Write (v);
if (FAILED (hr )){
ShowError ();
return;
)
// Встановлюємо внутрішній курсор на початок
hr = stream-> put_Position (0);
if (FAILED (hr )){
ShowError ();
return;
)
// Завантажуємо картинку з Stream-а
CComQIPtr
strm (stream);
if (strm) (
hr =
OleLoadPicture (strm, 0, TRUE, IID_IPicture, (void **) pic);
if (FAILED (hr )){
ShowError ();
return;
)
)
)
Для того щоб подібний код працював, необхідно
підключити заголовки adoint.h. Можна було скористатися директивою
import для створення зручних обгорток над відповідними об'єктами і
методами ADO. Приклад вийшов би простіше, але тоді ви могли упустити деякі
деталі.
У прикладі проводиться завантаження зображення з бази
даних в об'єкт, що підтримує інтерфейс IPicture. Цей інтерфейс дозволить вам
надалі виводить (рендери) зображення або зберігати його на диск в
різних форматах. Виведення зображення на екран робиться приблизно так
(обробник WM_PAINT):
LRESULT CMainDlg:: OnPaint (UINT/* uMsg * /, WPARAM/* wParam * /, LPARAM/* lParam * /,
BOOL &/* bHandled */)
(
CPaintDC dc (m_hWnd);
if (pic) (
OLE_XSIZE_HIMETRIC SizeX;
OLE_YSIZE_HIMETRIC SizeY;
pic-> get_Height (& SizeY);
pic-> get_Width (& SizeX);
RECT rc;
GetClientRect (& rc);
// Власне висновок
pic-> Render (dc, 0,0, rc.right, rc.bottom, 0, SizeY, SizeX,-SizeY, NULL);
)
return 0;
)
Завантажувати файл в базу на С + + приблизно так само просто,
як і отримувати:
# define TESTHR (hr) do (HRESULT _hr = hr; if (FAILED (_hr)) (
IErrorInfo * ef = 0;
GetErrorInfo (0, & ef);
_com_raise_error (_hr, ef);)) while (false)
void UploadPicture2DB (PCWSTR szPath)
(
try (
CComVariant
vtMissing (DISP_E_PARAMNOTFOUND, VT_ERROR);
// Завантажуємо картинку з файлу
TESTHR (OleLoadPicturePath (szPath, NULL, NULL, 0, IID_IPicture, (void **) & pic ));
CComPtr
stream;
TESTHR (stream.CoCreateInstance (L "ADODB.Stream "));
TESTHR (stream-> put_Type (adTypeBinary ));
TESTHR (stream-> Open (vtMissing ));
CComQIPtr
strm (stream);
CComQIPtr ps (pic);
// Зберігаємо картинку в об'єкт ADODB.Stream
TESTHR (ps-> Save (strm, TRUE ));
CComPtr
rs;
TESTHR (rs.CoCreateInstance (L "ADODB.Recordset "));
TESTHR (rs-> Open (CComVariant (L "blob_test"), CComVariant (conn), adOpenStatic,
adLockOptimistic, adCmdTable ));
// Додаємо нову порожню запис
TESTHR (rs-> AddNew (vtMissing, vtMissing ));
CComPtr flds;
TESTHR (rs-> get_Fields (& flds ));
CComPtr fld;
TESTHR (flds-> get_Item (CComVariant (L "img"), & fld ));
TESTHR (stream-> put_Position (0 ));
// Заливаємо вміст Stream-а в полі img
CComVariant v;
TESTHR (stream-> Read (adReadAll, & v ));
TESTHR (fld-> put_Value (v ));
// Зберігаємо зміни до БД
TESTHR (rs-> Update (vtMissing, vtMissing ));
)
catch (_com_error & e) (
MessageBox (e.Description (), _T ( "Error"), MB_ICONERROR);
)
return;
)
Щоб максимально спростити приклад, я використав
винятку замість аналізу кодів помилок і стандартний клас _com_error, який
визначений у файлі comutil.h.
Крім цього, у прикладі немає коду відкриття з'єднання,
оскільки передбачається, що в момент виклику цієї функції з'єднання з БД вже
відкрито (глобальна змінна conn).
При читанні даних типу text або ntext, тип варіанту
буде VT_BSTR. Я не думаю, що з ним можуть виникнути якісь проблеми.
ПРИМІТКА
У випадку бінарних даних тип варіанту
VT_ARRAY | VT_UI1.
Тепер давайте розглянемо метод додавання бінарних
даних у базу за допомогою збережених процедур.
Збережені процедури і ADO.NET
Наша процедура буде виглядати наступним чином:
create proc AddBlob (@ img image)
as
insert into blob_test (img) values (@ img)
На щастя, в ADO.NET робота з потоками не дуже
змінилася (їх як і раніше потрібно відкручувати тому).
Алгоритм завантаження графічного файлу з диска в базу
може бути таким:
Отримуємо ім'я файлу.
Відкриваємо з'єднання.
Створюємо об'єкт FileStream.
Читаємо дані з файлу.
Створюємо об'єкт SqlCommand для виклику збереженої
процедури.
Одному з параметрів передаємо лічені дані.
Викликаємо методу ExecuteNonQuery об'єкта SqlCommand.
Параметри з'єднання з базою даних можна налаштувати в
вікні дизайнера, тому в коді цього робити не доведеться, треба лише викликати
функцію Open без параметрів - і з'єднання буде встановлено.
ofd1.Filter =
"(*. bmp) | *. bmp"
If ofd1.ShowDialog () =
DialogResult.OK Then
sb.Text = "connecting to
database ..."
sb.Refresh ()
'Якщо з'єднання не відкрито, відкриваємо
If conn.State <> ConnectionState.Open
Then
conn.Open ()
End If
sb.Text = "loading
image ..."
sb.Refresh ()
Dim stream As New
FileStream (ofd1.FileName, FileMode.Open)
Dim b () As Byte
ReDim b (CInt (stream.Length))
'Читання даних з файлу
stream.Read (b, 0,
CInt (stream.Length))
'Створення та підготовка до виклику збереженої процедури
Dim cmd As New
SqlClient.SqlCommand ( "AddBlob", conn)
With cmd
. CommandType = CommandType.StoredProcedure
. Parameters.Add ( "@ img", SqlDbType.Image)
With
. Parameters ( "@ img")
. Direction =
ParameterDirection.Input
. Value = b
End With
'Виклик збереженої процедури
. ExecuteNonQuery ()
End With
sb.Text = "Ready"
End If
Все виглядає досить просто, і якщо враховувати, що
все сміття прибере GC, життя стає зовсім легкою.
А що, якщо у вас вже є зображення (скажімо, у
об'єкті PictureBox) і вам потрібно зберегти його в базу? У цьому випадку потрібно
використовувати інший потік - MemoryStream. Ось як це може бути зроблено:
sb.Text = "connecting to database ..."
sb.Refresh ()
'Якщо з'єднання не
відкрито, відкриваємо
If conn.State <> ConnectionState.Open Then
conn.Open ()
End If
sb.Text = "loading image ..."
sb.Refresh ()
'Створення та підготовка до
викликом збереженої процедури
Dim cmd As New SqlClient.SqlCommand ( "AddBlob", conn)
cmd.CommandType =
CommandType.StoredProcedure
'Збереження зображення в
потік в пам'яті у форматі BMP
Dim stream As New MemoryStream ()
img1.Image.Save (stream, Imaging.ImageFormat.Bmp)
stream.Seek (0, SeekOrigin.Begin)
'Підготовка параметрів
cmd.Parameters.Add ( "@ img", SqlDbType.Image)
With cmd.Parameters ( "@ img")
. Direction =
ParameterDirection.Input
'Скористаємося зручним методом ToArray. Жалко що його немає в
FileStream-a
. Value = stream.ToArray ()
End With
'Виклик збереженої процедури
cmd.ExecuteNonQuery ()
sb.Text = "Ready"
Тепер розглянемо випадок, коли треба витягувати
зображення з бази за допомогою ADO.NET. Послідовність дій може бути
такий:
Відкрити з'єднання.
Підготувати команду (SqlCommand).
Заповнити SqlDataReader - аналог ADO Recordset в дуже
урізаному варіанті.
Вважати дані в MemoryStream.
Завантажити Image з потоку.
Перед читанням даних з DataReader-а необхідно викликати
метод Read. Ось реалізація:
sb.Text = "connecting to database ..."
sb.Refresh ()
'Установка з'єднання
If conn.State <> ConnectionState.Open Then
conn.Open ()
End If
sb.Text = "Loading image ..."
sb.Refresh ()
'Підготовка запиту на
вибірку даних
Dim cmd As New
SqlClient.SqlCommand ( "select img from
blob_test where id = 3 ", conn)
'Створення і заповнення об'єкта DataReader
Dim reader As SqlDataReader = cmd.ExecuteReader ()
Dim ms As New MemoryStream ()
'Перехід на перший рядок
reader.Read ()
Dim bb () As Byte
bb = reader.Item ( "img")
'Запис даних у потік в
пам'яті
ms.Write (bb, 0, CInt (bb.Length))
ms.Seek (0,
SeekOrigin.Begin)
'Завантажуємо графічне
зображення в Image
pb.Image = Image.FromStream (ms)
sb.Text = "Ready"
Якщо потрібно читати дані порціями, а не цілком, як
це зроблено в прикладі, скористайтеся методом GetBytes, який аналогічний
ADO-методом GetChunk. Однак якщо обсяг даних дуже великий, це не сильно
допоможе, так як вони передаються клієнту все відразу в момент виклику методу Read.
Для того щоб дані передавалися на клієнта тільки на запит, необхідно
передати як параметр методу ExecuteReader прапор
CommandBehavior.SequentialAccess. У цьому випадку дані зчитуються
безпосередньо в момент виклику GetBytes.
ПРИМІТКА
Для цього прапора є одне зауваження.
Якщо ви обираєте кілька полів, зчитувати їх значення ви повинні в тій
послідовності, в якій вони повертаються з бази. Мабуть тому прапор
називається «Послідовний доступ».
Я знаю, що багато хто не любить процедури, що зберігаються.
Дійсно, для використання збережених процедур ви повинні бути більш-менш
знайомі з T-SQL і знати загальні принципи роботи з реляційними БД. Крім цього,
вважається, що архітектура, в якій занадто великий акцент зроблено на винесення
логіки на рівень SQL Server-а, має ряд недоліків: погане масштабування,
складне супровід і налагодження. Почасти все це правда, проте особисто я все-таки
намагаюся використовувати процедури, що зберігаються скрізь, де можливо, і лише в тих
випадках, коли бізнес-логіка дійсно складна і вимагає взаємодії
декількох бізнес-компонентів, відмовляюся від їх використання. Тим, хто не
хоче використати збережені процедури для оновлення великих об'єктів, я
раджу прочитати статтю 
http://support.microsoft. com/default.aspx? scid = kb; en-us; Q309158 & ID = kb; en-us; Q309158,
в якій викладається метод читання і запису великих об'єктів за допомогою DataSet.
Останнє, на чому я б хотів зупинитися, і що
викликає найбільшу кількість проблем у початківців програмістів це робота з
великими об'єктами в Oracle. Традиційно це питання досить складний: в
про-дотнетовскую еру доступ до СУБД Oracle за допомогою OLEDB-провайдерів в
основному забезпечували дві бібліотеки: MSDAORA і OraOLEDB.
Провайдер MSDAORA - надавав слабкий сервіс (не можна
було, наприклад, дізнатися кількість рядків у вибірці) і не дозволяв працювати з
великими об'єктами в принципі. Єдиною перевагою даного провайдера
є те, що він входить в стандартний пакет MDAC - Microsoft Data Access
Components.
Провайдер OraOLEDB - рідний провайдер, який
надавав більшу функціональність, ніж MSDAORA, і дозволяв працювати з
великими об'єктами.
ПРИМІТКА
В Oracle, як і в MS SQL, великі
текстові дані відрізняються від великих бінарних даних. Перші називаються CLOB і NCLOB --
Character Large OBjects, другий - BLOB - Binary
Large OBjects. Аналогію з типами MS SQL
провести нескладно.
Отримувати і змінювати дані за допомогою об'єкта Recordset
можна точно так само, як і у випадку MS SQL Server-а. Приклад я приводив вище. Єдина
проблема пов'язана з передачею великих об'єктів в якості параметрів до збережених
процедур. Для того щоб це працювало, необхідно в об'єкті Command встановити
динамічний параметр SPPrmsLOB в TRUE.
Я не буду детально і з прикладами зупинятися на
роботі з цими провайдерами, натомість зосередившись на керованому
ADO.NET провайдера. Якщо у вас будуть питання, зверніться до документації або,
якщо зовсім погано буде, поставте питання в нашому форумі з баз даних.
Провайдер для ADO.NET не поставляється з. NET Framework
1.0, його потрібно встановлювати вручну. Завантажити останню версію можна за адресою http://www .microsoft.com/downloads/details.aspx? familyid = 4f55d429-17dc-45ea-bfb3-076d1c052524 & languageid = f49e8428-7071-4979-8a67-3cffcb0c2524 & displaylang = en.
вступну інформацію про використання цього провайдера в
ASP.NET-програм можна отримати зі статті http:// msdn.microsoft.com/vstudio/using/building/default.aspx? pull =/library/en-us/dndotnet/html/manprooracperf.asp.
Наступний приклад демонструє методи роботи з
великими об'єктами за допомогою керованого провайдера. Для його правильної роботи
вам необхідно підключити збірку System.Data.OracleClient.dll.
Sub FillDBFromStream (stream as Stream, UserName as String)
'Відкриваємо
з'єднання
Dim con As New OracleClient.OracleConnection ( "server = srv; Uid = u; pwd = p")
'Створюємо запит з одним параметром username
Dim da As New
OracleClient.OracleDataAdapter ( "select *
from USERS_STATE where
USERNAME =: username ", con)
'Додаємо параметр і встановлюємо його значення
da.SelectCommand.Parameters.Add ( "username",
Data.OracleClient.OracleType.VarChar). Value = UserName
Dim ds As New DataSet ()
'Об'єкт CommandBuilder необхідний для оновлення даних
Dim cb As New
OracleClient.OracleCommandBuilder (da)
'Виробляємо вибірку
da.Fill (ds, "UState")
'Прочитуємо дані з потоку у внутрішній
масив
Dim b () as Byte
ReDim b (CInt (ms.Length))
stream.Read (b, 0,
CInt (stream.Length))
'Якщо вибірка порожня, створюємо нову запис
If ds.Tables ( "UState"). Rows.Count =
0 Then
'Створення запису
Dim dr As DataRow =
ds.Tables ( "UState"). NewRow ()
dr ( "username") =
UserName
dr ( "b_state") = b
'Додавання запису
ds.Tables ( "UState"). Rows.Add (dr)
Else
'Оновлення запису
Dim dr As DataRow =
ds.Tables ( "UState"). Rows (0)
dr ( "b_state") = b
End If
'Зміни записуємо в базу
da.Update (ds, "UState")
End Sub
Треба сказати, що цей спосіб буде працювати, тільки
якщо в таблиці є первинний ключ. У моєму випадку, логічніше за все він
виглядає на полі username. При відсутності ключа в момент виконання команди
Update ви отримаєте наступну помилку:
Dynamic
SQL generation for the UpdateCommand is not supported against a SelectCommand
that does not return any key column information.
У документації з Oracle стверджується, що оновлення
даних краще проводити в явних транзакції.
ПРИМІТКА
За замовчуванням підключення відкривається в
режимі неявної транзакції, в якому транзакція починається в момент
виконання першої команди після закінчення попередньої транзакції. Цей режим аналогічний установці SET IMPLICIT_TRANSACTIONS ON в MS SQL Server.
Це пов'язано з тим, що транзакція, в контексті якої
проводиться оновлення, автоматично підтверджується в момент виконання
команди Update. Так що якщо ви потім бажаєте скасувати зміни - це вам так
просто не вдасться зробити. Проблема вирішується за допомогою об'єкта Transaction,
приклад використання якого, як і приклад збереження великих даних за допомогою
збережених процедур, можна знайти за адресою http:// support.microsoft.com/default.aspx? scid = kb; EN-US; Q322796.
Ну ось, мабуть, і все. Буду радий, якщо ця стаття
допомогла вам у роботі з великими об'єктами.
Список літератури
Для підготовки даної роботи були використані
матеріали з сайту http://www.rsdn.ru/
