Доброго времени суток.
Как правильно написать команду SQL типа (Type), воспринимает ее как (Select, where, as и т.д.) в обычное поле в которое можно записывать. Поле Type имеется у таблицы в БД, оно системное, возможности переименовать ее нет.
Например:

SELECT DISTINCT
[Product].[Id] as [ProductID],
[Product].[Type] as [TypeID],

P.S.:Двойные и одинарные скобки не помогают.

Нравится

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

Однажды нужно было выполнить небольшой SQL-запрос весом в 100 килобайт:

UPDATE [dbo].[tbl_Query]
SET QueryData =  0x3c3f{ещё сто тысяч буковок и циферок}13e0a
WHERE ID = 'b10832f4-5f3f-4c94-bc05-329d9b4525cf'

Проблема была в том, что на компьютере, на котором это надо было выполнить, был установлен только Query Analyzer из MS SQL Server 2000, который отказывался открывать файлы больше 64К и даже вставлять текст такого размера из буфера обмена. Ведь 64 килобайта хватит каждому!

Помогло создание в Terrasoft XRM скрипта с таким текстом:

//-----------------------------------------
// scr_ScriptTest
//-----------------------------------------

function Main() {
        var Parameters = System.CreateObject('TSObjectLibrary.Parameters');
        try {
                Connector.DBEngine.ExecuteCustomSQL(
                "update [dbo].[tbl_Query] set QueryData =  0x3c3f{ещё сто тысяч буковок и циферок}13e0a where ID = 'b80832f4-5f2f-4c74-bc05-349d9b4525cf'",
                Parameters);
        } catch(e) {
                Log.Write(1, e.message);       
        }
       
}

Запускаем его из TSAdmin по клавишей F9, и содержимое таблицы обновилось.

Нравится

Поделиться

4 комментария

Александр, ограничение 64к не спроста. MSSQL сервер не может выполнить запрос который длиннее - это ограничено на уровне ядра СУБД. Подробности здесь
Вывод: ExecuteCustomSQL не спасет, если запрос длинный, вам его необходимо разбить на части или если одним файлом, то разделить GO (или параметром который укажите для консольной утилиты). Про утилиты можно прочитать здесь

Спасибо за ссылку.

Но:

  • Подобный запрос, только insert, нормально выполнялся из MS SQL Server Management Studio 2008 R2.
  • И в ExecuteCustomSQL таки тоже выполнился, проблема решена.
  • Там написано, что Batch size не более 65,536 * Network Packet Size, а этот Network Packet Size по умолчанию равен 4096 байт.
  • Ниже написано, что максимальный размер хранимой процедуры составляет 250 MB.
  • Запрос из update одного поля разделить затрудняюсь.

Александр, я думал вы выполняли большой sql скрипт, а не загружали данные в одно поле.
Тогда ваше решение имеет нюанс - достигнув ограничения Batch size вас не спасет ExecuteSQLText - сервер физически не сможет принять ваш запрос в виде текста и выполнить. Если необходимо загрузить БОЛЬШОЙ объект в блоб поле, то или писать кастомную утилиту и там использовать параметры или попробовать openrowset

Для решаемой задачи ограничения на Batch size (более 250 MB) не достигались, проблема была в Query Analyzer. За идею с OPENROWSET спасибо.

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

Попалась странная ошибка при попытке вызвать и использовать в Terrasoft 3.3.2 хранимую процедуру из Firebird 2.0.6.

TSFirebirdEngineLibrary.FirebirdEngine: The query must be prepared first.

Поторопился поместил вопросик в старую тему про ExecuteCustomSQL http://www.community.terrasoft.ua/blogs/2171, потом туда внесу решение, если найдется...

Нравится

Поделиться

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

Всем привет!

Есть задача, получить результат неких вычеслений. Вычесления происходят на SQL.
В данном посте написано как: http://community.terrasoft.ru/blogs/2171
Но мне возвращается пустая строка... никаких ошибок не выдаётся. Получаемый результат НЕ набор данных и.т.д... а просто число/строка

function Main()
{
        var Parameters = System.CreateObject('TSObjectLibrary.Parameters');
        var ReturnParameter = Parameters.CreateItem();
        ReturnParameter.Name = 'Result';
        ReturnParameter.ParamType = 1;          
//        ReturnParameter.ParamType = 2;          
//        ReturnParameter.DataType = pdtString;
        ReturnParameter.DataType = pdtInteger;
//        ReturnParameter.DataType = pdtGUID;
        Parameters.Add(ReturnParameter);
        Connector.DBEngine.ExecuteCustomSQL("select 1 as Result;", Parameters);
        Log.Write(1, Parameters.ItemsByName('Result').Value+"");
}

Версия 3,2,0,10. В указаном выше посте написано "Дело в том, что выходные (output) параметры были добавлены, начиная с версии 3.2.0.x (с какой точно, сейчас сказать не могу)"
Что я не так делаю?

Или подскажите пожалуйста другой способ.

Спасибо!

Нравится

2 комментария

Возможно, дело в том, что Вы на самом деле не возвращаете никаких параметров. Инструкция "select 1 as Result" возвращает набор данных, состоящих из одной колонки и одной строки. Но Вы не присваиваете это значение параметру :Result. Попробуйте, например, так:

Connector.DBEngine.ExecuteCustomSQL("select :Result = 1", Parameters);
Log.Write(1, Parameters.ItemsByName('Result').Value+"");

СПАСИБО! Всё работает.

Показать все комментарии
Публикация

Нравится

Поделиться

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 не работает.

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