Отправляет email-рассылки с помощью сервиса Sendsay

MS SQL Server

Рассылка закрыта

При закрытии подписчики были переданы в рассылку "Вопросы и ответы по MS SQL Server" на которую и рекомендуем вам подписаться.

Вы можете найти рассылки сходной тематики в Каталоге рассылок.

←   Май 2002   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Автор
Сергей

Статистика

17.597 подписчиков
-20 за неделю
←   Все выпуски   →

MS SQL Server - дело тонкое...


Информационный Канал Subscribe.Ru
#098<<  #099

СОДЕРЖАНИЕ

1.ИНФОРМАЦИЯ О ПРОЕКТЕ SQL.RU
2.СОВЕТЫ
2.1.XML в MS SQL Server 2000 и технологиях доступа к данным (продолжение)
2.1.1.XML на стороне сервера. FOR XML
2.1.2.SQLXML-классы в .NET
2.1.3.FOR XML на стороне клиента
3.ССЫЛКИ НА СТАТЬИ
3.1.Отечественные статьи
3.2.Новые технические статьи Microsoft
4.ФОРУМ SQL.RU
4.1.Самые популярные темы недели
4.2.Вопросы остались без ответа

ИНФОРМАЦИЯ О ПРОЕКТЕ SQL.RU

В связи с переносом сервера на новый хостинг, наш сайт SQL.RU может быть временно недоступен вечером в пятницу и во все выходные. После переноса, некоторые разделы сайта будут недоступны в течение нескольких недель, которые потребуются для завершения переноса содержимого и скриптов на ASP.NET. Старый форум будет доступен в режиме read-only до окончания перехода на новый форум, который первоначально будет обладать меньшей функциональностью, но она будет увеличена в ближайшие недели с учетом ваших пожеланий. Мы с удовольствием ответим на ваши вопросы и прислушаемся к вашим пожеланиям, которые Вы можете оставить в нашем форуме: Переход SQL.RU под .NET

Александр Сибилёв (admin@sql.ru)

[Содержание]

СОВЕТЫ

XML в MS SQL Server 2000 и технологиях доступа к данным (продолжение)
По материалам конференции "Корпоративные базы данных 2002"

Автор: Алексей Шуленин

1. Введение
2. Сохранить как XML
3. Получить как поток XML

4. XML на стороне сервера. FOR XML

Следующая ступень эволюции - ADO 2.6 и SQL Server 2000. В SQL Server 2000 в синтаксис Т-SQL был добавлен предикат FOR XML для оператора SELECT, что позволило получать XML-текст как результаты запроса на стороне сервера. Рассмотрим запрос SELECT c.ContactName, c.ContactTitle, o.OrderDate FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID FOR XML AUTO. Вначале SQL Server традиционным способом выполняет ту часть запроса, которая находится до FOR XML. Затем к полученному множеству записей сервер применяет преобразование в XML. Если выполнить этот запрос из Query Analyzer, то видно, что содержимое XML-документа разбито по записям длиной 2033 символа Unicode одноколоночного recordset'а. Вообще говоря, это не есть ни recordset, ни XML. Его нельзя использовать как результат подзапросов, хранимых функций и всего остального, что предполагает дальнейшую обработку на SQL Server. Это нечто предназначено только для передачи клиенту, где из него уже происходит сборка полноценного документа. Таким образом, несмотря на то, что в отличие от Скриптов 1 и 2, в Скрипте 3 XML фактически получается на сервере, все XPath-, updategrams- и прочие запросы выполняются на клиенте, поскольку встроенный тип XML в настоящее время в SQL Server отсутствует.

static void Execute_FORXMLQuery_ADODB()
{
   ...
      ADODB.StreamClass str = new ADODB.StreamClass();
      str.Open(System.Type.Missing, ADODB.ConnectModeEnum.adModeUnknown, ADODB.StreamOpenOptionsEnum.adOpenStreamUnspecified, "", "");

      ADODB.CommandClass cmd = new ADODB.CommandClass();
      cmd.ActiveConnection = cnn;
      cmd.CommandText = "SELECT '<Root>' " +
            "SELECT c.ContactName, c.ContactTitle, o.OrderDate FROM Customers c " +
            "INNER JOIN Orders o ON c.CustomerID = o.CustomerID " +
            "WHERE c.ContactName = ? AND year(o.OrderDate) = ? FOR XML AUTO " +
            "SELECT '</Root>'";       cmd.Parameters.Append(cmd.CreateParameter("@Name", ADODB.DataTypeEnum.adVarWChar, ADODB.ParameterDirectionEnum.adParamInput, 30, null));
      cmd.Parameters.Append(cmd.CreateParameter("@Year", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, 4, null));
      cmd.Dialect = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}";
      cmd.Properties["Output Stream"].Value = str;
      cmd.Properties["Output Encoding"].Value = "UTF-8";

      object RecsAffected = null, Params = new object[] {"Maria Larsson", 1997};
      cmd.Execute(out RecsAffected, ref Params, (int)ADODB.ExecuteOptionEnum.adExecuteStream);

      MSXML2.DOMDocument40Class xmlDoc = new MSXML2.DOMDocument40Class();
      xmlDoc.load(str);
}

Скрипт 3

Скрипт 3 выполняет тот же запрос, что и в предыдущих примерах, за исключением того, что я его слегка разнообразил передачей параметров: он показывает список всех заказов, сделанных определенным клиентом за определенный год. Результат заключается в скобки <Root> … </Root> для получения well-formed документа. Вместо этого можно использовать cmd.Properties["xml root"].Value = "Root". XML возвращается на клиента в объекте ADODB.Stream. Его можно сохранить сразу в файл - str.SaveToFile(f.FullName, ADODB.SaveOptionsEnum.adSaveCreateOverWrite); , а можно передать как поток в документ типа MSXML2.DOMDocument40Class. В любом случае понятно, что над результатами запроса можно вести дальнейшую работу средствами DOM. Аналогично Скрипту 2 для работы с XML-документами здесь используется библиотека СОМ, а не .NET, поскольку ADODB.Stream нельзя преобразовать к System.IO.Stream, чтобы загрузить в System.Xml.XmlDocument. Передавать же, сохраняя в промежуточный файл, как делалось в Скрипте 1, можно, но неизящно. Свойство Dialect класса ADODB.Command говорит провайдеру, какой тип команды используется. Возможные значения приведены в Табл.1

Тип командыЗначение в ADOКонстанта в OLE DB
Запрос Transact-SQL{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}DBGUID_SQL
Запрос XPath{EC2A4293-E898-11D2-B1B7-00C04F680C56}DBGUID_XPATH
Запрос в XML-шаблоне{5D531CB2-E6Ed-11D2-B252-00C04F681B71}DBGUID_MSSQLXML
Поведение провайдера по умолчанию{C8B521FB-5CF3-11CE-ADE5-00AA0044773D}DBGUID_DEFAULT

Табл.1

Объекты типа Stream появились в ADO 2.5 для поддержки некоторых типов нереляционной информации. Вспомним, что первоначальная спецификация OLE DB в 1996 г. описывала универсальный доступ в рамках прямоугольных recordset'ов для обеспечения совместимости с ранними API, имевшими дело с реляционными данными (ODBC, DB-Library, DAO). Однако несмотря на развитость аппарата реляционной алгебры далеко не все источники удавалось свести к этому классу. Да и потом, иерархические базы данных все-таки предшествовали реляционным, поскольку эта модель, видимо, более естественно отвечает образу мышления. Время шло, диссонанс между объектно-ориентированной средой разработки и реляционной архитектурой хранения (плоские таблицы, связанные отношениями) проявлялся все более отчетливо. В ADO 1.5 была сделана попытка сгладить эту проблему с помощью провайдера MSDataShape. Спецификация OLE DB 1.5 предусматривала новый тип полей - Chapter column. Предполагалось, что индивидуальные уровни иерархии можно представить в виде отдельных rowset'ов, и chapter указывает для родительской записи множество ее детей в дочернем rowset'e. Однако все это было хорошо в однородных иерархиях, когда дочерняя запись имеет тот же набор полей, что и родительская. Например, очевидный негомогенный источник - файловая система - сюда уже не вписывается. Чтобы уложить древовидную структуру в прямоугольную с минимальными переделками и потерями производительности (скажем, не заводя для каждой записи число полей, соответствующее полному набору всех возможных атрибутов узла в дереве, большая часть из которых будет, очевидно, пустовать) в ADO 2.5 были введены классы Record и Stream. Набор полей (атрибутов) записи (файла) может разниться не только с родительской записью (папкой), но и меняться от записи к записи (например, в зависимости от типа файла). Наименьший общий знаменатель полей, присущих всем записям, формировал колонки привычного Recordset. Класс Stream соответствовал содержанию файла. Таким образом, появилась возможность получения результата запроса в виде потoка, чем мы и воспользовались в Скрипте 3. XML естественным образом решает проблему представления древовидных иерархий. Как и RDS, MSDataShape в настоящее время поддерживается по соображениям совместимости, но развиваться в дальнейшем не будет.
Для предиката FOR XML существует три возможных опции: AUTO, RAW и EXPLICIT. Действие первой мы уже видели. Она дает SQL Server указание сформировать простое вложенное дерево, где иерархия (вложенность) определяется порядком связывания таблиц в запросе. Каждой таблице из FROM, хотя бы одно поле которой попадает в вывод, ставится в соответствие элемент, имя которого равно имени / псевдониму таблицы. Поля таблиц отвечают атрибутам элементов. Отсюда следует, что все поля в запросе должны быть поименованы. Недопустимо, например, SELECT 1 FOR XML… Опция RAW формирует плоский неиерархический документ независимо от отношений между таблицами в запросе. Он подобен тому, что мы видели на рис.1. Каждая запись результата соответствует элементу с именем row. Опция EXPLICIT наиболее гибкая из трех и позволяет получить XML произвольной структуры, однако recordset, по которому он строится, должен следовать определенным правилам расположения записей и именования полей, чтобы однозначно задать желаемую структуру дерева. Я не буду сейчас подробно расписывать эти правила, т.к. несмотря на гибкость, способ этот достаточно громоздок и на практике применяется редко. В основном он используется самим SQL Server'ом для преобразования реляционной структуры к аннотированной схеме (см.п.9). Подробно узнать про опцию EXPLICIT можно в документации на SQL Server (см. XML and Internet Support -> Using EXPLICIT Mode).
Поддерживаются параметризованные запросы с FOR XML и процедуры, возвращающие SELECT … FOR XML. Передача параметров осуществляется стандартно при помощи коллекции Parameters объекта ADODB.Command. Поля типа text / ntext возвращаются в виде текста, поля типа image - в виде их XPath-пути. FOR XML …, Binary Base64 возвращает их в кодировке Base64 и для опции RAW это единственный возможный способ вывести значения BLOB-типов. FOR XML AUTO, ELEMENTS отображает поля не на атрибуты, а на подэлементы. SELECT TOP 0 ... FOR XML AUTO, XMLData дает схему XML-результата в формате XDR. Начиная с SQLXML 2.0 включена поддержка XSD-схем и утилита для конвертации XDR в XSD (cvtschema.ехе).

[Содержание]

5. SQLXML-классы в .NET

После выхода SQL Server 2000 в августе 2000 г. дополнительная функциональность, в плане развития поддержки XML, выпускалась в виде веб-релизов под названием SQLXML. SQLXML 1.0 содержал Updategrams, XML Bulkload и усовершенствования в части производительности. В версии 2.0 ввели аннотированные XSD-схемы, FOR XML на стороне клиента, SQLXMLOLEDB-провайдер и SQLXML Managed Classes (в плане интеграции с ADO.Net). Наконец, веб-релиз версии 3.0, вышедший в марте 2002 г. и последний на момент подготовки доклада, добавляет к этому поддержку SOAP, превращая SQL Server в Web-сервис. Некоторые из перечисленных возможностей будут здесь разобраны.
Начнем с того, что в дальнейшем в примерах мы будем использовать управляемые (managed) библиотеки System. Data (ADO .Net) и Microsoft.Data.SqlXml. Первая является штатной функциональностью Visual Studio .Net, а вторая устанавливается в составе SQLXML Web Release 2.0 и выше. Если Вы экспериментируете с приводимыми здесь примерами, пожалуйста, отметьте эти библиотеки в References для своего проекта. Это вполне естественный выбор при использовании .Net-средства разработки, который избавляет, например, от возни с СОМ-маршалингом, при передаче массива интерфейсов IDispatch (Variant) в параметры ADODB.Command.. Не следует, впрочем, полагать, что с этого момента все рассматриваемые задачи решаются только с помощью SQLXML Managed Classes. Большинство разбираемых примеров вполне реализуются средствами классического СОМовского ADO подобно тому, как показывалось в п.п. 2 - 4. Однако с помощью новых технологий доступа к данным, специально предназначенных для работы в .NET Framework, этого можно достичь проще и быстрее. Скрипт 4 демонстрирует предыдущий пример (FOR XML-запрос на стороне сервера), переписанный с ADODB на SqlXml.

using System.Xml;
using Microsoft.Data.SqlXml;
...
static void Execute_FORXMLQuery_SQLXML()
{
      SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLOLEDB;...");
      cmd.CommandText = "SELECT c.ContactName, c.ContactTitle, o.OrderDate FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID WHERE c.ContactName = ? and year(o.OrderDate) = ? FOR XML AUTO";
      cmd.RootTag = "Root";
      cmd.CommandType = SqlXmlCommandType.Sql;
      cmd.OutputEncoding = "UTF-8";
      cmd.CreateParameter().Value = "Maria Larsson";
      cmd.CreateParameter().Value = 1997;
      Stream str = cmd.ExecuteStream();

      XmlDocument xml = new XmlDocument();
      xml.Load(str);
...
}

Скрипт 4

Логика примера не изменилась - выполняется SQL-запрос, результаты получаются в виде XML и передаются для последующей возможной работы в System.Xml.XmlDocument (аналог DOMDocument). Кроме того, для целей демонстрации XML-поток сохраняется в файл, который затем открывается в браузере. Класс SqlXml.Command для получения результатов в виде XML-потока имеет два метода: ExecuteStream() и ExecuteToStream(). Их отличие состоит в том, что первый создает новый Stream, а второй пишет в уже существующий. Кроме того, результаты запроса можно сразу получить как XmlReader (метод ExecuteXmlReader). В отличие от класса XmlDocument (DOM) XmlReader является более легковесным, он не требует загрузки всего документа в память. В терминологии баз данных его ближайшим аналогом будет forward-only read-only курсор. Свойство Dialect класса Command в ADO соответствует свойству CommandType, а Output Encoding и XML Root перешли из расширенных свойств в стандартные (OutputEncoding и RootTag).

[Содержание]

6. FOR XML на стороне клиента.

Провайдер SQLXMLOLEDB является типичным сервисным провайдером, как уже упоминавшиеся ранее MSDataShape и MSPersist, в том плане, что он получает данные от другого провайдера, а не непосредственно из источника. SQLXMLOLEDB позволяет преобразовывать recordset в XML непосредственно на клиенте. Таким образом, провайдер SQLOLEDB, стоящий между SQLXMLOLEDB и SQL Server, получает от сервера не XML-поток, а обычный recordset и передает его SQLXMLOLEDB, который уже занимается превращением реляционной информации в XML. Скрипт 5 демонстрирует FOR XML на стороне клиента.

public static void Execute_FORXMLQuery_OnClient()
{
      ...
      cmd.CommandText = "SELECT c.ContactName, COUNT(o.OrderDate) as 'Кол-во заказов' FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID WHERE o.OrderDate between ? and ? GROUP BY c.ContactName FOR XML NESTED";
      cmd.CreateParameter().Value = "19970101";
      cmd.CreateParameter().Value = "19971231 23:59:59";
      cmd.ClientSideXml = true;
      cmd.OutputEncoding = "UTF-8"; cmd.RootTag = "Корень";
      Stream str = cmd.ExecuteStream();
      StreamReader sr = new StreamReader(str);
      FileInfo f = new FileInfo("..\\Results\\FORXMLQueryResults.xml");
      StreamWriter sw = new StreamWriter(f.FullName, false, System.Text.Encoding.UTF8);
      sw.WriteLine("<!--Этот XML сделан на стороне клиента-->");
      str.Position = 0; sw.Write(sr.ReadToEnd());
      sr.Close(); sw.Close();
      ...
}

Скрипт 5

При помощи SQL Profiler можно оттрассировать и сравнить запросы, которые в действительности обрабатываются сервером при выполнении Скриптов 4 и 5.

Скрипт 4:

exec sp_executesql N'SELECT '''' SELECT c.ContactName, c.ContactTitle, o.OrderDate FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID WHERE c.ContactName = @P1 AND year(o.OrderDate) = @P2 FOR XML AUTO SELECT ''''', N'@P1 nvarchar(30),@P2 int', N'Maria Larsson', 1997

Скрипт 5:

exec sp_executesql N'SELECT c.ContactName, COUNT(o.OrderDate) as ''Кол-во заказов'' FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID WHERE o.OrderDate between @P1 and @P2 GROUP BY c.ContactName ', N'@P1 nvarchar(8),@P2 nvarchar(17)', N'19970101', N'19971231 23:59:59'

В Скрипте 5 я слегка изменил текст запроса, чтобы показать использование GROUP BY (этот предикат не разрешается в серверных FOR XML-запросах). Запрос считает количество заказов, сделанных каждым клиентом за определенный период времени. Обратите внимание на отсутствие предиката FOR XML во втором случае. Это значит, что преобразование recordset'a в XML действительно происходит в данном случае на клиенте.
Несмотря на бОльшие издержки на стороне клиента такая схема работы является более универсальной и позволяет получать клиенту XML от тех источников данных, которые его не поддерживают и не будут. Для этого будет достаточно в строке соединения:

"Provider= SQLXMLOLEDB;DataProvider=SQLOLEDB, …"

поставить вместо SQL Server соответствующего OLE DB-провайдера. К сожалению, для этого придется подождать следующего, 4-го, веб-релиза. Пока SQLXMLOLEDB умеет работать только с SQL Server 2000.
При клиентском XML-форматировании не поддерживается возврат в качестве результата одной команды нескольких recordset'ов, зато допускается предикат GROUP BY с агрегатными функциями. Режим FOR XML NESTED клиентского форматирования примерно соответствует FOR XML AUTO серверного, за исключением того, что вместо псевдонимов в имена элементов ставятся настоящие названия таблиц. О других различиях можно прочитать в документации на SQLXML 3.0.

ПРОДОЛЖЕНИЕ СЛЕДУЕТ

[Содержание]

ССЫЛКИ НА СТАТЬИ

Отечественные статьи

Проблема незапланированных запросов к базе данных
Автор: Владимир Некрасов.
Мощность любой базы данных больше, чем мощность пользовательских интерфейсов к ней. База данных потенциально предоставляет гораздо больше способов получения и отображения данных, чем это можно запрограммировать. В то же время, при эксплуатации информационной системы, пользователям всякий раз не хватает запросов, интерфейсов и отчетов... [подробнее]
Изучаем XML
Автор: Floris Lambrechts.
Это очень краткое введение в XML. Вы встретитесь с Eddy - мета-котом, контролем синтаксиса XML и несколькими DTD. Не волнуйтесь, мы все поясним ;-) ... [подробнее]

[Содержание]

Новые технические статьи Microsoft

BUG: SQL Server Books Online May be Misleading Regarding the Meaning of Max Worker Threads (Q272428)
How to Set Up Server Proxy with SQL Server 6.5 (Q185638)
PRB: Possible Thread Starvation Detected Message Reported in the SQL Server Error Log (Q262973)
BUG: Changing Max Worker Threads Requires You to Restart SQL Server (Q269486)
FIX: SQLMail Extended Stored Procedures May Fail with an Access Violation on SQL Server 2000 SP2 when SQL Server is Run As a Windows NT Service (Q320407)
BUG: VSS Database Truncates Stored Procedures Larger Than 4 KB (Q313993)
HOW TO: Verify and Change the System Administrator Password by Using MSDE (Q322336)
BUG: Incremental Article Additions to an Existing Merge Publication Do Not Work When You Use FTP (Q320600)
FIX: Access Violation May Occur If You Log Off After You Cancel A Linked Server Query (Q319962)
FIX: Parallel Query May Fail with Error Message 1203 and an Assertion (Q319184)
HOW TO: Set Up Development Environment from MSDN Professional Subscription (Q318124)
PRB: Schema Definition of Base Table at Subscriber Does Not Match Table in Publisher Database (Q304346)
PRB: DTS FTP Task Does Not Allow User to Return to Parent Directory from an Empty Folder (Q300365)
PRB: Datetime Rounding Up Behavior Undesirable (Q135861)
PRB: Renaming Stored Procedure, View or Trigger Does Not Update SYSCOMMENTS Table (Q243198)
FIX: ITransactionResourceAsync::TMDown not Delivered to Resource Manager (Q234673)
FIX: A Query That Uses a Large Search String in the CONTAINS Clause May Fail with Error Msg 7607 (Q317486)
INFO: Using Analysis Server Format Properties with the Office XP PivotTable Component (Q318122)

[Содержание]

ФОРУМ SQL.RU

Самые популярные темы недели

База данных - хранилище объектов.
Не инициализируется база
Как обработать исключение, возникшее в триггере
Задачка по клиету VB6
Как лучше организовать структуру таблицы?
Блокировки
MSSQL 2000, QA - странная ошибка
Клиент на VB6
Установка сервера <
Select @IDN=ID FROM @TABLENAME - КАК???
Запрос на удаление выполняется вечно
"Зависание" транзакции

[Содержание]

Вопросы остались без ответа

DBCC adduserobject
Log explorer 3.0 beta version...
DTS password
1С MS SQL 2000
Общение Paradox 9 и SQL Serevr
Clustering MSSQL Server
Row fetch limit exceeded
Help Me!!!: SQL-> ADO -> VBS -> IE
что может случиться со счетчиками identity ?

[Содержание]

#098<<  #099
Вопросы, предложения, коментарии, замечания, критику и т.п. присылайте Александру Гладченко на адрес: mssqlhelp@pisem.net
sql.ru
Описание рассылки
 Поиск на SQL.RU
Искать: 
Результатов на странице: Формат:
Тип запроса: Раздел:
МИНИФОРМА
ПОДПИСКИ
http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу
В избранное