Тем, кому лень дизайнить сервисы для несложной выборки, посвящается.
Развивая тему, описанную мною здесь, можно получать набор данных, используя сервис, прикрепленный к этому посту (см. вложение), и следующую функцию:
var sq = Services.GetNewItemByUSI('sq_Select');
sql = sql.replace('select', '');
var sqlWithoutTop = '';
if ((sql.indexOf('top') != -1) && (sql.indexOf('top') 10)) {
sqlWithoutTop = Trim(sql.replace('top', ''));
sqlWithoutTop = sqlWithoutTop.substring(sqlWithoutTop.indexOf(' '), sqlWithoutTop.length);
}
var FindFrom = 'from';
var FromArray = (sqlWithoutTop == '') ?
sql.split('from') :sqlWithoutTop.split('from');
if (FromArray.length > 0) {
FindFrom = 'from' + FromArray[FromArray.length - 1];
}
var SQLColumns = (sqlWithoutTop == '') ?
Trim(sql.substring(0, sql.indexOf(FindFrom))).split(',') :
Trim(sqlWithoutTop.substring(0, sqlWithoutTop.indexOf(FindFrom))).split(',');
var Columns = sq.Items(0).Columns;
var ColCount = SQLColumns.length;
for (var i = 0; i ColCount; i++) {
if (!Assigned(Columns.ItemsByAlias(SQLColumns[i]))) {
var NewCol = Columns.CreateConstColumn();
var NewColAlias = SQLColumns[i];
NewColAlias = (NewColAlias.indexOf(' as') != -1) ?
NewColAlias.substring(NewColAlias.indexOf(' as') + 3,
NewColAlias.length) : NewColAlias;
NewCol.ColumnAlias = Trim(NewColAlias);
Columns.Add(NewCol);
}
}
Columns.ItemsByAlias('SQLColumn').SQLText = sql + '/*';
var ds = sq.Open();
return ds;
}
Использовать эту функцию можно так:
var sql =
"select Name " +
"from " + Prefix + "Contact " +
"where not AccountID is null " +
"group by Name " +
"having not Name like '%test%' " +
"order by Name asc ";
var Dataset = GetDatasetBySQL(sql);
Log.Write(1, Dataset('Name'));
Поддерживается использование агрегатных функций, только в этом случае нужно обязательно дать полям выборки алиасы:
var sql =
"select count(Name) as test " +
"from " + Prefix + "Contact ";
var Dataset = GetDatasetBySQL(sql);
Log.Write(1, Dataset('test'));
"+": экономия времени, не нужно особо ничего дизайнить.
"-": не предусмотрены сложные конструкции запросов с подзапросами, с exists-фильтрами и т.д. Поэтому рекомендую использовать в более простых случаях (или же доработать функцию своими силами).
Стас, чего-то сбилось форматирование...
1. Я не увидел где ты работаешь с фильтрами Where, сортировкой Order by и группировкой Group By.
2. Непонятно почему ты создаешь Const колонки, а не General.
А так идея хорошая, сам когда-то порывался сделать :) , но учитываю сложность написания sql - парсера, пока забросил эту затею.
Стас, можно попробовать парсер от mssql - MSSQLParser.dll (это ActiveX библиотека), он правда очень простой, но "допилить" его можно, он и под Oracle работает. Он разбирает sql на составляющие - пример
И я еще не очень уверен, что его можно просто так ставить клиентам... но попробовать можно :)
>>1. Я не увидел где ты работаешь с фильтрами Where, сортировкой Order by и группировкой Group By.
>>2. Непонятно почему ты создаешь Const колонки, а не General.
Саня, использую твой же "хитрый" способ, который ты показывал для получения данных из хранимок. Соот-но, обрабатывать группировку и сортировку не нужно (по идее).
По поводу парсера - попробую.
>>не очень уверен, что его можно просто так ставить клиентам
Это чисто "проектный" функционал, поскольку вся ответственность на разработчике и его "прямых" руках. А так, почему бы и нет... :)
Не понял :) Ты парсишь текст запроса и строишь SelectQuery, так вот не видно где ты фильтры добавляешь.... Или это только набросок и скрипт так сказать для "затравки"? :)
Нет, это вся функция. Попробую объяснить:
- сначала проверяю наличие top-а
- далее нахожу ту часть, которая идет после from-а.
Все это нужно просто для того, чтобы получить список колонок (т.е. секцию select-а).
А потом в первую CustomSQL-колонку прописываю весь запрос целиком, со всеми группировками, сортировками, фильтрами. И строю const-колонки для того, чтобы можно было корректно что-то вообще получать из датасета. В итоге получается запрос вида:
SELECT Name as test from tbl_Contact where not AccountID is null group by Name having not Name like '%test%' order by Name asc /* AS [SQLColumn], */ if 1=0 select getdate() AS [Fake], N'' AS [test] FROM [dbo].[tbl_AccountGroup] AS [tbl_AccountGroup]
Помнишь такое? Это ж твоя была когда-то идея... :)
Естесственно, что такой запрос нельзя использовать полноценно, я имею в виду для редактирования, вставки, удаления данных. Только для выборки.
Просто часто лень создавать сервисы для довольно простых выборок. :)
"S.Kalishenko" написал:Саня, использую твой же "хитрый" способ, который ты показывал для получения данных из хранимок.
Та да )) я вот только увидел строчку
Columns.ItemsByAlias('SQLColumn').SQLText = sql + '/*';
сразу понятно стало ;).
Олег Лабьяк,
разработчик,
3-я линия Службы поддержки Terrasoft.
Теперь понял... я было подумал, что ты написал простенький парсер sql...
Забыл прикрепить вложение. :(
Сейчас уже сообщение полноценное.
По-моему есть что-то порочное в том, чтобы по тексту строить SelectQuery, из которого потом где-то в ядре получается текст.
Вы не правы, намного проще написать в конфигурации, что-то типа:
var selectQuery = ConvertToSelectQuery("select Name from Table1");
которое, потом скрипт преобразует в универсальный формат, который уже будет выполняться на 3-х СУБД.
Стас, в прикрепленном сервисе SQL-запроса не хватает следующего SQL-кода для поля Fake:
*/ IF 1=0 SELECT getdate()
Прикрепил исправленный запрос.
Еще столкнулся с тем, что, когда я передаю в функцию SQL-запрос вида:
select distinct OwnerID from tbl_OrderOffering
у меня в результате формируется датасет с именем поля 'distinct OwnerID'.
Чтобы формировалось правильное имя поля OwnerID, нужно заменить строчку в функции GetDatasetBySQL(sql)
var NewColAlias = SQLColumns[i];
на строчку
var NewColAlias = SQLColumns[i].replace('distinct', '');
Можно учесть и другие варианты кроме слова distinct.
Стас, спасибо за ценный материал.
Вопрос: проводилось ли исследование предложенного способа на ресурсоемкость? Имеется в виду время создания сервиса и выполнения запроса, по сравнению с традиционным задизайненным запросом в SelectQuery?
>>Кошкаров Андрей
Спасибо, Андрей, за Ваши наблюдения и комментарии. Извините за задержку с ответом, был в отпуске. Действительно, в этом варианте получения датасета не учтен ряд опций, таких как distinct и другие. В принципе, об этом я и предупреждал: "не предусмотрены сложные конструкции запросов с подзапросами, с exists-фильтрами и т.д. Поэтому рекомендую использовать в более простых случаях (или же доработать функцию своими силами)". :)
>>Гамора Дмитрий
Дима, такого анализа я не делал, так как основная идея была не быстродействие получения экземпляра, а простота работы метода.