Публикация

Использование ExecuteCustomSQL

Нравится

Поделиться

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

Добрый день! Что-то не получается выполнить запрос из примера, создаю новый скрипт с кодом и запускаю:

function Main() {
	var SQL = "select name from tbl_Account where Name like '%компа%'";
	var Res = GetSimpleSQLResult(SQL);
	Log.Write(1, Res[1]);
}
 
 
function GetSimpleSQLResult(SQL) {
    if (IsEmptyValue(SQL)) {
        return;
    }
    SQL = SQL.toUpperCase().substr(7, SQL.length);
    var BeforeFromStr = SQL.split('FROM')[0].split(',');
    var SortedArray = new Array();
    var flag = 0;
    for (var i = 0; i < BeforeFromStr.length; i ++) {
        if (BeforeFromStr[i].indexOf('(') >= 0 &&
!IsEmptyValue(BeforeFromStr[i + 1])) {
            SortedArray[flag] = BeforeFromStr[i] + ', ' +
BeforeFromStr[i + 1];
            i++;
        } else {
            SortedArray[flag] = BeforeFromStr[i];
        }
        flag++;
    }
    var AfterFromStr = SQL.split('FROM')[1];
    var Parameters = System.CreateObject('TSObjectLibrary.Parameters');
    var DeclareStr = 'declare ';
    var SelectStr = ' select top 1 ';
    for (var i = 1; i < SortedArray.length + 1; i++ ) {
        DeclareStr += '@t' + i + ' nvarchar(250), ';
        SelectStr += ':P' + i + ' = ' + SortedArray[i - 1] + ', ';
        var ReturnParameter = Parameters.CreateItem();
        ReturnParameter.Name = 'P' + i;
        //ReturnParameter.ParamType = 2;
        ReturnParameter.DataType = pdtString;
        Parameters.Add(ReturnParameter);
    }
    DeclareStr = DeclareStr.substr(0, DeclareStr.length - 2);
    SelectStr = SelectStr.substr(0, SelectStr.length - 2);
    if (!IsEmptyValue(AfterFromStr)) {
        var SQltext = DeclareStr + SelectStr + ' FROM ' + AfterFromStr;
    } else {
        var SQltext = DeclareStr + SelectStr;
    }
    var Table = new Array();
    try {
        Connector.DBEngine.ExecuteCustomSQL(SQltext, Parameters);
    } catch (e) {
        Log.Write(1, e.message);
                                return Table;
    }
    for (var i = 1; i < Parameters.Count + 1; i++ ) {
        Table[i - 1] = Parameters.ItemsByName('P' + i).Value;
    }
    return Table;
}

Возвращается в логе пустая строка:

[08.11.28 11.31.09.906]	(W)

ReturnParameter.ParamType = 2; - Взял в комментарий т.к. ругается: Объект не поддерживает это св-во или метод

Добрый день!

>> ReturnParameter.ParamType = 2; - Взял в комментарий т.к. ругается: Объект не поддерживает это св-во или метод

А какая у Вас версия билда? Дело в том, что выходные (output) параметры были добавлены, начиная с версии 3.2.0.x (с какой точно, сейчас сказать не могу). Если у Вас версия младше, то этот функционал у Вас, к сожалению, пока не заработает.

>> var SQL = "select name from tbl_Account where Name like '%компа%'";
var Res = GetSimpleSQLResult(SQL);
Log.Write(1, Res[1]);

В запросе Вы выбираете одно поле, т.е. в массиве у Вас будет один элемент, сл-но результат будет в нулевом элементе массива Res[0], а Вы вытягиваете из Res[1].

Предыдущая попытка была действительно 3.1.0.23, попробовал на 3.2.1.4, выдаёт ошибку:

Неверный входящий поток табличных данных (TDS) по протоколу удаленного вызова процедур (RPC). Параметр 3 (""): тип данных 0xE7 имеет недопустимую длину данных или длину метаданных

независимо от того Log.Write(1, Res[0]); или Log.Write(1, Res[1]);

насчёт: "то этот функционал у Вас, к сожалению, пока не заработает." - какой именно? Вы имеете ввиду что на версии 3.1.0.23 вообще не получиться использовать ExecuteCustomSQL?

1. В 3.1.0.23 Вы можете смело использовать ExecuteCustomSQL, но только с входящими параметрами. Но, к сожалению, эта версия output-параметры не поддерживает.

2. По поводу "Неверный входящий поток табличных данных (TDS) по протоколу удаленного вызова процедур (RPC). Параметр 3 (""): тип данных 0xE7 имеет недопустимую длину данных или длину метаданных".
У Вас скорее всего MS SQL 2005, для которого эту функцию нужно отдельно адаптировать. Я уже подправил содержимое этого поста, указав версии СУБД для 2-х вариантов функций (MS SQL 2000 и Oracle 10g).

Функция отредактирована.... теперь имеет следующий вид и выше изложенная ошибка исправлена.

function GetSimpleSQLResult(SQL) {
	if (IsEmptyValue(SQL)) {
		return;
	}
	SQL = SQL.toUpperCase().substr(7, SQL.length);
	var BeforeFromStr = SQL.split('FROM')[0].split(',');
	var SortedArray = new Array();
	var flag = 0;
	for (var i = 0; i < BeforeFromStr.length; i ++) {
		if (BeforeFromStr[i].indexOf('(') >= 0 && !IsEmptyValue(BeforeFromStr[i + 1])) {
			SortedArray[flag] = BeforeFromStr[i] + ', ' + BeforeFromStr[i + 1];
			i++;	
		} else {
			SortedArray[flag] = BeforeFromStr[i]; 	
		}
		flag++;
	}
	var AfterFromStr = SQL.split('FROM')[1];
	var Parameters = System.CreateObject('TSObjectLibrary.Parameters');
	var SelectStr = ' select top 1 ';
	for (var i = 1; i < SortedArray.length + 1; i++ ) {
		SelectStr += ':P' + i + ' = ' + SortedArray[i - 1] + ', ';	
		var ReturnParameter = Parameters.CreateItem();	
		ReturnParameter.Name = 'P' + i;
		ReturnParameter.ParamType = 2;
		ReturnParameter.DataType = pdtGUID;
		Parameters.Add(ReturnParameter);	
	}
	SelectStr = SelectStr.substr(0, SelectStr.length - 2);
	if (!IsEmptyValue(AfterFromStr)) {
		var SQltext = SelectStr + ' FROM ' + AfterFromStr; 
	} else {
		var SQltext = SelectStr;	
	}
	var Table = new Array();
	try {
		Connector.DBEngine.ExecuteCustomSQL(SQltext, Parameters);
	} catch (e) {
		Log.Write(1, e.message);
        return Table;         		
	}
	for (var i = 1; i < Parameters.Count + 1; i++ ) {
		Table[i - 1] = Parameters.ItemsByName('P' + i).Value;
	}
	return Table;          
}

Обновил.

Может быть еще стоит добавить в эту функцию замену vw_ на tbl_, в случае если она запускается под админом? Потому что многие могут забывать об этом...

Спасибо! Сегодня пригодилась функция! :twisted:

--
www.it-sfera.com.ua

А можно ли на основании данной возможности сделать стандартный Dataset (для использования его в отчетах FastReport, например)?

Используя вышеперечисленные функции - нет. Боюсь, что не получится.

Расчехлим боян... поднимем старую тему, может кто сразу подскажет в чем дело.

Имеем скрипт

var Parameters = System.CreateObject('TSObjectLibrary.Parameters');
CreateSPParameter(Parameters, 'AccountID', pdtGUID, AccountID);
CreateSPParameter(Parameters, 'KeeperID', pdtGUID, KeeperID);	
var SQL = 'EXECUTE PROCEDURE tsp_UpdateKeeper (:KeeperID, :AccountID)';
Connector.DBEngine.ExecuteCustomSQL(SQL, Parameters);

Получаем на последней строчке ошибку:

TSFirebirdEngineLibrary.FirebirdEngine: The query must be prepared first.
EXECUTE PROCEDURE tsp_UpdateKeeper (:KeeperID, :AccountID)

Terrasoft 3.2.2.240, Firebird 2.0.6

В хранимке простенький UpdateQuery и нужные параметры.
Что ж он хочет то от меня?

Если написать просто текст запроса например такой

var SQL = 'update "tbl_Contact" set "KeeperID" = :KeeperID where "tbl_Contact"."AccountID" = :AccountID;';

то все работает, то есть тонкость где-то в вызове хранимки

Александр, а если отказаться от параметров и собрать запрос вставив в sql значения параметров (благо в Firebird GUID строки)?

Боюсь, отказаться придется даже от такого вызова, так как аналога EXECUTE AS из SQL2005 я в Firebird не нашел, а запуск с ограниченными правами результата может не дать в решаемой конкретной задаче...
Начал переписывать триггеры, буду делать через них.
Вопрос про вызов хп приобретает характер исследовательский...
заменить параметры на конкретные значения при вызове хп сейчас попробовал - такая же ошибка

Александр, так в том то и красота, что на запуск этой хранимки вы можете давать доступ например даже public, а на таблицы которые она меняет нет, хотя самой процедуре насколько я помню еще дополнительно необходимо раздать доступ на таблицы...

А вот насчет раздачи прав на таблицу для процедуры я и не знал, признаюсь честно [facepalm].
Александр, Спасибо!

Но! все равно вопрос вызова процедуры firebird и ошибки это не снимает

Что-то в 2008-ом MS SQL не работает.

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