Версия для печати
Как достать SQL запрос из *.mdb без MS Access
http://www.delphikingdom.com/asp/viewitem.asp?catalogID=889Шкут Александр
дата публикации 29-12-2003 16:27Как достать 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. Внешний вид записи таблицы MSysObjects (все объекты базы данных).
- Знаки ????? обозначают, что я очень сомневаюсь в правильности описанной информации.
- Пустые ячейки — в моей практике не встречалось.
- [Что-то] – обобщенный тип значения, например если в поле встречается только 1 или 2 или 3 – я пишу Integer, даже если
- тип поля – текстовый.
- < N > - переменная или значение.
- ... - часть запроса не критичная для описания. (для наглядности).
- Описание курсивом – то, что понадобится для разбора запроса.
Где:
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
3. Описание полей и их значений относящихся к запросу (процедуре).или 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 [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] Имя поля 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 это все будет сохранено, я не знаю. Эта статья – не техническая документация, а попытка поделиться опытом.
Всем удачи!Шкут Александр (AlexS.)
25 декабря 2003г.
Специально для Королевства Delphi