Как достать 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 можно получить в составе Microsoft MDAC SDK 2.6 (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 > - переменная или значение.
... - часть запроса не критичная для описания. (для наглядности).
Описание курсивом - то, что понадобится для разбора запроса.

Внешний вид записи таблицы MSysObjects (все объекты базы данных).

Connect

Database

DateCreate

DateUpdate

Flags

ForeignName

LvExtra

LvModule

LvProp

Name

Owner

ParentId

RmtInfoLong

RmtInfoShort

Type

01.10.2003 16:43:35

16.10.2003 15:26:43

447

(Blob)

r_Cash

(VarBytes)

251658241

(Blob)

(VarBytes)

Где:

DateCreate - дата и время создания объекта.
DateUpdate - дата и время последнего изменения объекта.
Flags - не изучалось.
ForeignName - имя во внешней базе данных для связанных таблиц.
Id - уникальный код объекта в базе данных.
Name - имя объекта. (Многие объекты в таблице не являются хранилищами данных, и найти их в базе данных или через Access нельзя.)
Type - тип объекта (1-таблица, 3-контейнер, 5-запрос,8-внешний индекс и.т.д.)

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

Внешний вид записей, относящихся к одному запросу в таблице 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 можно удалить процедуру, но, опять же лучше пользоваться предназначенной инструкцией - по крайней мере вы будете четко понимать, что делаете.

Описание полей и их значений относящихся к запросу (процедуре).

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

Ссылки по теме