Публикация

Получение датасета по SQL-запросу без создания сервисов

Тем, кому лень дизайнить сервисы для несложной выборки, посвящается.
Развивая тему, описанную мною здесь, можно получать набор данных, используя сервис, прикрепленный к этому посту (см. вложение), и следующую функцию:

function GetDatasetBySQL(sql) {
    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 Prefix = (Connector.CurrentUser.IsAdmin) ? 'tbl_' : 'vw_';
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 Prefix = (Connector.CurrentUser.IsAdmin) ? 'tbl_' : 'vw_';
var sql =
        "select count(Name) as test " +
        "from " + Prefix  + "Contact ";
var Dataset = GetDatasetBySQL(sql);
Log.Write(1, Dataset('test'));

"+": экономия времени, не нужно особо ничего дизайнить.
"-": не предусмотрены сложные конструкции запросов с подзапросами, с exists-фильтрами и т.д. Поэтому рекомендую использовать в более простых случаях (или же доработать функцию своими силами).

Нравится

Поделиться

16 комментариев

Стас, чего-то сбилось форматирование...
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-фильтрами и т.д. Поэтому рекомендую использовать в более простых случаях (или же доработать функцию своими силами)". :)

>>Гамора Дмитрий
Дима, такого анализа я не делал, так как основная идея была не быстродействие получения экземпляра, а простота работы метода.

Показать все комментарии