Статьи Королевства Дельфи

Как достать SQL запрос из *.mdb без MS Access




Вступление.

Я много видел разных стран..., но это для того, чтобы сказать, что я все-таки зауважал корпорацию Microsoft, после подробного знакомства с Линукс-ом. И вот почему. Операционная система Windows - наиболее простая и доступная для пользователей, кто не посвятил свою жизнь компьютеру. Ни в одной коммерческой, а тем более бесплатной, системе нет настолько простых и доступных элементов настройки как в Windows. И это только моя точка зрения. Я не хочу разводить дебаты на эту тему, потому, что хочу рассказать о своих наработках и исследованиях. Они касаются, по моему мнению, одной из лучших и развитых локальных баз данных - Microsoft Jet или mdb. При определенных усилиях можно написать даже неплохую сетевую программу на базе mdb.

Зачем это нужно?

За годы моей работы с mdb (около 6 лет) я один раз столкнулся с ситуацией, когда базу данных Access не удалось восстановить после внезапного отключения питания (Об UPC-ах и речи не было). Да и необходимость восстановления возникала всего раз 5. К тому же, поддержка Jet встроена в Windows, и нет необходимости искать (покупать) и устанавливать драйвер для базы данных. Все остальные форматы более подвержены разрушению, или состоят из множества файлов; при отсутствии одного из этих файлов говорить о целостности данных сложновато. Я готов обсудить этот факт.

О чем речь?

Речь идет о том, что базой данных mdb можно прекрасно пользоваться, не имея MS Office и Access. Все данные, необходимые для хранения и изменения информации можно хранить в mdb базе данных имея Delphi и подключенный ActiveX ADO и ADOX. Все эти компоненты поставляются с Windows, и вам не нужно приобретать MS Office только для того, чтобы сохранять таблицы и запросы к ним (и не только к ним :-) ) в базе данных mdb. Подробную справку по ADO, ADO MD и ADOX можно получить в составе (13379 Kb), хотя я скачал этот пакет только ради документации. Где-то, летом 2002 года я поставил перед собой задачу - может ли простой программист уйти от использования крякнутых программ (мне было бы обидно за свою 2-3-х летнюю работу, если бы ее крякнули ;)) и пользоваться тем, что дают бесплатно, или за доступные деньги. Так что я пришел к выводу – можно. В настоящий момент у меня уже есть довольно приличное приложение (собственной разработки), которое я использую вместо Access. В базе данных mdb понятия запрос и процедура различны, но для простоты изложения я буду использовать термин запрос.

История.


Начал с простого окна, в котором было TMemo - для текста запроса, и кнопка для выполнения этого запроса. CheckBox - для указания, возвращать мне результат запроса, или нет. Второе окно открывалось с DBGrid-ом, в котором был результат выполнения запроса. Третье окно - ListBox, который содержал список таблиц и запросов базы данных (макросы, отчеты и формы Access я не умею доставать и сейчас, даже не знаю где это прячут). По двойному щелчку на элементе списка открывалось все то-же окно с DBGrid-ом, где можно было посмотреть содержимое таблицы или запроса.

Первую базу данных я создал с помощью системного менеджера ODBC - там есть такая возможность! Первые таблицы приходилось создавать с помощью инструкций SQL. Я был приятно удивлен, что Access умеет через SQL такие вещи, которые нигде в справке по Access не описаны. К этим возможностям относится параметр DEFAULT в инструкции CREATE TABLE. В справке к Access о нем нет ни слова! А в справке по InterBase – есть. Я попробовал – очень прекрасно устанавливаются значения по умолчанию для поля создаваемой таблицы. Короче говоря, кто ищет – найдет. Первые запросы и процедуры приходилось сохранять в текстовом виде, чтобы после корректировки удалить из базы данных сохраненный запрос и внести откорректированный. Потом я попытался достать текст запроса из базы данных через ADO – не получилось, не получилось и до сих пор. Пришлось выбрать другой путь. Если это делает Microsoft – почему не могу это делать я?

Так вот, если посмотреть в системные таблицы, то там есть вся необходимая информация (или почти вся). Используя ее можно написать парсер, который будет собирать текст запроса, используя формат записи самой Microsoft. А сохранить потом измененный запрос (помним: или процедуру) с помощь инструкции CREATE VIEW или CREATE PROCEDURE.

Формат хранения SQL запроса в Access.
Сразу оговорюсь, что все это возможно только с правами администратора на базу данных (Еще один плюс в пользу Access).

Ниже привожу таблицу с описанием всего, что мне удалось раскопать по этому поводу. Используя эту информацию, я написал парсер, который собирает это все в текст запроса. Я не претендую на полноту изложения, потому, что еще не полностью разобрал эту информацию, но возможно это поможет кому-то. Буду рад помощи, если кто что-то знает по этой теме. По крайней мере процентов 70 запросов расшифровываются и выполняются так как было задумано.

Соглашения по обозначениям: Если что-то не описано – я не разбирался – не было необходимости, или не наводило на мысль.
  • Знаки ????? обозначают, что я очень сомневаюсь в правильности описанной информации.
  • Пустые ячейки — в моей практике не встречалось.
  • [Что-то] – обобщенный тип значения, например если в поле встречается только 1 или 2 или 3 – я пишу Integer, даже если
  • тип поля – текстовый.
  • < N > - переменная или значение.
  • ... - часть запроса не критичная для описания. (для наглядности).
  • Описание курсивом – то, что понадобится для разбора запроса.
1. Внешний вид записи таблицы MSysObjects (все объекты базы данных).
Connect Database DateCreate DateUpdate Flags ForeignName Id Lv LvExtra LvModule LvProp Name Owner ParentId RmtInfoLong RmtInfoShort Type
01.10.2003 16:43:35 16.10.2003 15:26:43 0 447 (Blob) (Blob) (Blob) (Blob) r_Cash (VarBytes) 251658241 (Blob) (VarBytes) 1
Где:
  • DateCreate – дата и время создания объекта.
  • DateUpdate – дата и время последнего изменения объекта.
  • Flags – не изучалось.
  • ForeignName – имя во внешней базе данных для связанных таблиц.
  • Id – уникальный код объекта в базе данных.
  • Name – имя объекта. (Многие объекты в таблице не являются хранилищами данных, и найти их в базе данных или через Access нельзя.)
  • Type – тип объекта (1-таблица, 3-контейнер, 5-запрос,8-внешний индекс и.т.д.)




Из этой таблицы мне пригодились всего два параметра – Id и Name. Имя запроса мне известно, а все записи в другой системной таблице, относящиеся к этому запросу я нахожу при помощи поля Id.

2.Внешний вид записей, относящихся к одному запросу в таблице MSysQueries (в ней хранится структура всех запросов и процедур).
Attribute Expression Flag LvExtra Name1 Name2 ObjectId Order
0 0 -2147483636 (VARBYTES)
255 -2147483636 (VARBYTES)
5 Staff_list -2147483636 (VARBYTES)
5 Personal -2147483636 (VARBYTES)
6 [Staff_list].[P_code] 0 -2147483636 (VARBYTES)
6 [Staff_list].[Name] 0 -2147483636 (VARBYTES)
6 [Staff_list].[Br] 0 -2147483636 (VARBYTES)
6 [Staff_list].[Room] 0 -2147483636 (VARBYTES)
6 [Personal].[Fam] 0 -2147483636 (VARBYTES)
7 [Staff_list].[Room]=[Personal].[Room] 2 Staff_list Personal -2147483636 (VARBYTES)
7 [Staff_list].[Br]=[Personal].[Br] 2 Staff_list Personal -2147483636 (VARBYTES)
7 [Staff_list].[P_code]=[Personal].[P_code] 2 Staff_list Personal -2147483636 (VARBYTES)
Хотя в Access и не делается различие между запросом и процедурой, на самом деле оно есть в ADO. Запросом считается простой запрос SQL без параметров, который называется VIEW. Все запросы на изменение структуры таблиц, запросы с параметрами, запросы на объединение и пр... считаются процедурами и выбираютя из базы данных как views или procedures соответственно. Запросы сохраняются в базу данных соответственно с помощью CREATE VIEW, а процедуры – CREATE PROCEDURE. Если вы добавили в запрос параметры, он преобразовался в процедуру, и обратно сохранять его нужно уже с помощью CREATE PROCEDURE. Да, и перед сохранением измененного запроса не забывайте удалять из базы предыдущий – DROP VIEW или DROP PROCEDURE . Кстати запрос (view) удаляется и инструкцией DROP TABLE, однако я бы не рекомендовал ею пользоваться, потому что ошибка в имени, или невнимательность – и вы удалите вместо запроса таблицу. С помощью DROP VIEW таблицу удалить нельзя. Этот вариант более безопасен. С помощью DROP VIEW можно удалить процедуру, но, опять же лучше пользоваться предназначенной инструкцией – по крайней мере вы будете четко понимать, что делаете.

3. Описание полей и их значений относящихся к запросу (процедуре).
Формат хранения запросов в Access (MsysQueries) Значение ObjectID и имя запроса находится в таблице MsysObjects
ПолеЗначениеОписаниеСубПолеЗначениеОписание
Attribute0 Разделитель запросовObjectID[LongInt]Этот же ID содержится во всех остальных записях, относящихся к этому запросу
255 Пустая запись (я не встречал ее заполненой) Идет после Attribute 0 всегда
1Тип запроса, определяется полем Flag. Присутствует не всегда. Если запись отсутствует, то это (скорее всего, да других вариантов и не встречалось) запрос SELECT Flag1SELECT ... FROM
2INSERT ... INTO
3UPDATE ... SET
4UPDATE ... SELECT
5DELETE
6TRANSFORM
7MODIFY, CREATE TABLE, DROP
8
9UNION
10
11EXECUTE
Expression[Text]Параметры для Execute
[Text]Текст процедуры для Flag=7
Name1[Text]Имя процедуры для Execute
2Параметры запроса Flag1Bit (boolean по Delphi)
2Byte (Tinyint)
3Short (SmallInt)
4Integer
5Currency
6Real
7Float
8TdateTime
9
10String([LvExtra]) (Char..., Text...)
11Image !!!
12
13
14
15UNIQUEIDENTIFIER
16Decimal
LvExtra[Integer]Длина параметра для [String] и т. д. где имеет смысл
Запись с аттрибутом 3 я так и не разобрал, это только ход моих размышлений.
3Предикаты (Скорее всего битовое поле) ????? Flag0,1ALL
2DISTINCT
3SELECT DISTINCT *
4WITH OWNERACCESS OPTION
5Выборка *
8DISTINCT ROW ???
16TOP <N> Поле Name1 - <N>
48TOP <N> PERCENT Поле Name1 - <N>
4Внешняя база данныхName1[Text]Путь к внешней базе данных ( IN )
5Исходные таблицы или текст отдельного блока для UNION Expression[Text]Для UNION содержит в каждой строке текст блока UNION SELECT
Для SELECTName1[Text]Имя таблицы для выборки
Для SELECTName2[Text]Алиас таблицы
6Имя поля секции SELECT Expression[Text]Имя поля
Name1[Text]Алиас поля {<Expression> as <Name1>}
7Конструкция и тип объединения JOIN Expression[Text]<Поле1>{ = | <> | > | < }<Поле2>
Flag1INNER JOIN
2LEFT JOIN
3RIGHT JOIN
Name1[Text]Имя или алиас Таблицы1
Name2[Text]Имя или алиас Таблицы2
8Секция WHERE[Expression][Text]Условие WHERE полностью
9Секция GROUP BY[Expression][Text]Условие GROUP BY полностью
10Секция HAVING[Expression][Text]Условие HAVING полностью
11Секция ORDER BY[Expression][Text]Условие ORDER BY полностью
Используя эту информацию можно вытащить и собрать текст запроса из базы данных Access. Если кто знает другой способ, всегда рад помощи, да и сам готов помочь или поделиться знаниями. Создание такого парсера – довольно хорошая возможность разобраться с SQL. Напрмер я не прорабатывал варианты, когда в инструкции SQL используются нестандартные функции, и как в Access это все будет сохранено, я не знаю. Эта статья – не техническая документация, а попытка поделиться опытом.

Всем удачи!

Шкут Александр (AlexS.)
25 декабря 2003г.
Специально для


Содержание раздела