Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Сохранение переменных в столбце  [new]
Testor1
Member

Откуда:
Сообщений: 679
Всем привет!

нужен скрипт, который может сохранять имя и значение переменной/ых в столбец. Тип столбца - тот с которым работает скрипт.
Необходима, также иметь простой и удобный способ считывания значения переменной из столбца.

Я реализовал функцию
которая принимает параметры (имя переменной и значение) и сохраняет значение в виде nvarchar(4000) в формате {@variable|@value}, но столкнулся с проблемой парсинга. В случае если значение переменно содержит символ '}', то нет возможности корректно получить значение @value.

Не работал с XML в SQL, он может решить проблему? Если да, кто может помочь с функцией сохранения и получения переменной в/из XML ?

CREATE FUNCTION [dbo].[dump_var](@variable_name NVARCHAR(100)=NULL, @variable_value SQL_VARIANT=NULL, @delimiter NCHAR(1)='|') 

RETURNS NVARCHAR(4000)
AS
BEGIN

	
	RETURN N'{'+@variable_name + 
		   @delimiter + 
			CASE 
				WHEN @variable_value IS NULL THEN N'NULL'
				WHEN SQL_VARIANT_PROPERTY (@variable_value , 'BaseType')  IN ('DATE') THEN CONVERT(NVARCHAR(10),@variable_value,20)  
				WHEN SQL_VARIANT_PROPERTY (@variable_value , 'BaseType')  IN ('DATETIME','DATETIME2') THEN CONVERT(NVARCHAR(20),@variable_value,20)  
				ELSE CAST(@variable_value AS NVARCHAR(4000))
				END+N'}' 
	 

END
31 окт 12, 19:46    [13404712]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
А зачем вам придумывать какие то форматы со скобками? Сохраняйте просто значение и не придумывайте себе геморроя
31 окт 12, 23:47    [13405482]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
Testor1
Member

Откуда:
Сообщений: 679
Mind
А зачем вам придумывать какие то форматы со скобками? Сохраняйте просто значение и не придумывайте себе геморроя


{|} - связка переменная + ключ. в столбце может быть несколько таких пар. для удобного парсинга.

кажется нашел простое решение проблемы с помощью REPLACE при вставке заменяем одиночные символы {,|,} на двойные, перед получением значений заменяем {{,||,}} на одинарные.

Сейчас проверю
1 ноя 12, 08:38    [13406090]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
Glory
Member

Откуда:
Сообщений: 104760
Testor1
которая принимает параметры (имя переменной и значение) и сохраняет значение в виде nvarchar(4000) в формате {@variable|@value}, но столкнулся с проблемой парсинга. В случае если значение переменно содержит символ '}', то нет возможности корректно получить значение @value.

А просто через запятую @variable,@value ???
1 ноя 12, 10:43    [13406761]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
Testor1
Member

Откуда:
Сообщений: 679
Glory
Testor1
которая принимает параметры (имя переменной и значение) и сохраняет значение в виде nvarchar(4000) в формате {@variable|@value}, но столкнулся с проблемой парсинга. В случае если значение переменно содержит символ '}', то нет возможности корректно получить значение @value.

А просто через запятую @variable,@value ???


в некоторых случаях значение @value может содержать текст, который набрал пользователь. по опыту скажу, пользователи бывают очень талантливые и порой такие комбинации набирают ...
1 ноя 12, 10:52    [13406818]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
Glory
Member

Откуда:
Сообщений: 104760
Testor1
в некоторых случаях значение @value может содержать текст, который набрал пользователь. по опыту скажу, пользователи бывают очень талантливые и порой такие комбинации набирают ...

И что ? Имя переменной не может содержать запятую. Значит первая запятая и будет разделителем
1 ноя 12, 10:57    [13406846]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
Testor1
Member

Откуда:
Сообщений: 679
Glory
Testor1
в некоторых случаях значение @value может содержать текст, который набрал пользователь. по опыту скажу, пользователи бывают очень талантливые и порой такие комбинации набирают ...

И что ? Имя переменной не может содержать запятую. Значит первая запятая и будет разделителем


пример

@smsmsg,вася пупкин, пошел...на @работу,@ussdmsg,01.,342
ну и как такое парсить ?

{@smsmsg|вася пупкин, пошел...на @работу},{@ussdmsg|01.,342}
такое хоть понятней, но защита все же нужна
1 ноя 12, 11:01    [13406880]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
Glory
Member

Откуда:
Сообщений: 104760
Testor1
@smsmsg,вася пупкин, пошел...на @работу,@ussdmsg,01.,342
ну и как такое парсить ?

{@smsmsg|вася пупкин, пошел...на @работу},{@ussdmsg|01.,342}
такое хоть понятней, но защита все же нужна


@smsmsg,вася пупкин, пошел...на @работу < Это CR+LF, а не просто так
@ussdmsg,01.,342 < Это тоже CR+LF, а не просто так

ЗЫ
А хранить плоско структурированные данные в одном поле - это извращение. Имхо

Сообщение было отредактировано: 1 ноя 12, 11:05
1 ноя 12, 11:04    [13406900]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
Testor1
Member

Откуда:
Сообщений: 679
Glory
Testor1
@smsmsg,вася пупкин, пошел...на @работу,@ussdmsg,01.,342
ну и как такое парсить ?

{@smsmsg|вася пупкин, пошел...на @работу},{@ussdmsg|01.,342}
такое хоть понятней, но защита все же нужна


@smsmsg,вася пупкин, пошел...на @работу < Это CR+LF, а не просто так
@ussdmsg,01.,342 < Это тоже CR+LF, а не просто так

ЗЫ
А хранить плоско структурированные данные в одном поле - это извращение. Имхо



USE [SMMS]
GO
/****** Object:  StoredProcedure [dbo].[uspLogError]    Script Date: 11/01/2012 11:10:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[uspLogError] 
	@Variables [nvarchar] (4000) = NULL,
    @ErrorLogID [int] = 0 OUTPUT  -- Contains the ErrorLogID of the row inserted
                                  -- by uspLogError in the ErrorLog table.

AS
BEGIN
    SET NOCOUNT ON;

    -- Output parameter value of 0 indicates that error 
    -- information was not logged.
    SET @ErrorLogID = 0;

    BEGIN TRY
        -- Return if there is no error information to log.
        IF ERROR_NUMBER() IS NULL
            RETURN;

        -- Return if inside an uncommittable transaction.
        -- Data insertion/modification is not allowed when 
        -- a transaction is in an uncommittable state.
        IF XACT_STATE() = -1
        BEGIN
            PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' 
                + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
            RETURN;
        END;

        INSERT [dbo].[ErrorLog] 
            (
            [UserName], 
            [ErrorNumber], 
            [ErrorSeverity], 
            [ErrorState], 
            [ErrorProcedure], 
            [ErrorLine], 
            [ErrorMessage],
            [Variables]
            ) 
        VALUES 
            (
            SYSTEM_USER, 
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE(),
            @Variables
            );

        -- Pass back the ErrorLogID of the row inserted
        SELECT @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred in stored procedure uspLogError: ';
        EXECUTE [dbo].[uspPrintError];
        RETURN -1;
    END CATCH
END; 



Предложи свой вариант
1 ноя 12, 11:10    [13406941]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
Glory
Member

Откуда:
Сообщений: 104760
Testor1
Предложи свой вариант

Элементарная нормализация говорит о том, что список переменных должен хранится в отдельной таблице из 4х полей
1 ноя 12, 11:18    [13406980]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
Testor1
Member

Откуда:
Сообщений: 679
Glory
Testor1
Предложи свой вариант

Элементарная нормализация говорит о том, что список переменных должен хранится в отдельной таблице из 4х полей


:) это теория :) я с ней согласен.

я показываю свое решение. оно работает.

покажи свое решение, которое практичней.

		BEGIN CATCH 
		
			IF (XACT_STATE()) <> 0 ROLLBACK TRANSACTION;
			
			SET @variables = dbo.dump_var('@var1',@var1,'|')
								+ dbo.dump_var('@var2',@var2,'|');

			EXEC dbo.uspLogError @variables = @variables;
			EXEC dbo.usp_RethrowError;
		
		END CATCH
1 ноя 12, 11:24    [13407020]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
Glory
Member

Откуда:
Сообщений: 104760
Testor1
покажи свое решение, которое практичней.

Я уже показал. Вы невнимательны
1 ноя 12, 11:27    [13407037]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
Testor1
Member

Откуда:
Сообщений: 679
Glory
Testor1
покажи свое решение, которое практичней.

Я уже показал. Вы невнимательны


Готовый код показывали ? или как нужно ?
1 ноя 12, 11:30    [13407056]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
Glory
Member

Откуда:
Сообщений: 104760
Testor1
Готовый код показывали ? или как нужно ?

Ага. Полностью систему запостил.
1 ноя 12, 11:32    [13407074]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
Testor1
Если Ваше решение работает и Вы им довольны - о чем первоначальное сообщение?

P.S. Решение через XML
declare @var1 int,
        @var2 varchar(10)


declare @xml xml

select @var1 = 3, @var2 = '41"'

Select @xml = (
select @var1 as var1, @var2 as var2 
for xml path)

select @xml
1 ноя 12, 11:38    [13407119]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
Testor1,

Чтобы предложить альтернативное решение, нужно знать для чего и как будет использоваться сей список значений. А также версию сервера.
1 ноя 12, 11:40    [13407142]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
Testor1
Member

Откуда:
Сообщений: 679
Кот Матроскин
Testor1
Если Ваше решение работает и Вы им довольны - о чем первоначальное сообщение?

P.S. Решение через XML
declare @var1 int,
        @var2 varchar(10)


declare @xml xml

select @var1 = 3, @var2 = '41"'

Select @xml = (
select @var1 as var1, @var2 as var2 
for xml path)

select @xml



я тоже начал склоняться к этому варианту, но он не экономный.

в этом варианте есть защита, от случая, когда значение переменной будет содержать текст тега?
1 ноя 12, 11:44    [13407174]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
Testor1
Member

Откуда:
Сообщений: 679
invm
Testor1,

Чтобы предложить альтернативное решение, нужно знать для чего и как будет использоваться сей список значений. А также версию сервера.


Пока MSSQL 2008 r2

использую для двух целей
1. при catch сохраняю значение переменных в таблицу ошибок для последующего анализа.
2. в некоторых случаях сохраняю значения введенные пользователем вместе с другими переменными, а также пересылаю пользователям ответ с копией их запроса.
1 ноя 12, 11:52    [13407237]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
Testor1
использую для двух целей
1. при catch сохраняю значение переменных в таблицу ошибок для последующего анализа.
2. в некоторых случаях сохраняю значения введенные пользователем вместе с другими переменными, а также пересылаю пользователям ответ с копией их запроса.
Т.е. сначала все кучкуем в одну строку, а потом ее парсим. Даете гарантию, что не возникнет задачи поиска по значениям переменных?

В общем, как уже рекомендовали, делаете хранение значений переменных в обычной таблице. Для передачи списка значений в вашу процедуру, используете табличный тип.
1 ноя 12, 12:09    [13407418]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
Testor1
Member

Откуда:
Сообщений: 679
invm
Testor1
использую для двух целей
1. при catch сохраняю значение переменных в таблицу ошибок для последующего анализа.
2. в некоторых случаях сохраняю значения введенные пользователем вместе с другими переменными, а также пересылаю пользователям ответ с копией их запроса.
Т.е. сначала все кучкуем в одну строку, а потом ее парсим. Даете гарантию, что не возникнет задачи поиска по значениям переменных?

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


типа так ?

		BEGIN CATCH 
		
			IF (XACT_STATE()) <> 0 ROLLBACK TRANSACTION;
			

                        DECLARE @varaibales TABLE (@varaible NVARCHAR(100), @value slq_variant) 
			INSERT @varaibles VALUES('@var1', @var1);
                        INSERT @varaibles VALUES('@var2', @var2);

			EXEC dbo.uspLogError @variables = @variables;
			EXEC dbo.usp_RethrowError;
		
               END CATCH 
1 ноя 12, 12:18    [13407492]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
Testor1,

Создаем табличный тип
create type dbo.VariableValueList as table
(
 variable nvarchar(100) not null primary key,
 value sql_variant
);

Используем его
		BEGIN CATCH 
		
			IF (XACT_STATE()) <> 0 ROLLBACK TRANSACTION;
			

                        DECLARE @varaibales dbo.VariableValueList;
			INSERT @varaibles VALUES('@var1', @var1);
                        INSERT @varaibles VALUES('@var2', @var2);

			EXEC dbo.uspLogError @variables = @variables;
			EXEC dbo.usp_RethrowError;
		
               END CATCH 
1 ноя 12, 12:25    [13407559]     Ответить | Цитировать Сообщить модератору
 Re: Сохранение переменных в столбце  [new]
Testor1
Member

Откуда:
Сообщений: 679
invm
Testor1,

Создаем табличный тип
create type dbo.VariableValueList as table
(
 variable nvarchar(100) not null primary key,
 value sql_variant
);

Используем его
		BEGIN CATCH 
		
			IF (XACT_STATE()) <> 0 ROLLBACK TRANSACTION;
			

                        DECLARE @varaibales dbo.VariableValueList;
			INSERT @varaibles VALUES('@var1', @var1);
                        INSERT @varaibles VALUES('@var2', @var2);

			EXEC dbo.uspLogError @variables = @variables;
			EXEC dbo.usp_RethrowError;
		
               END CATCH 



USE [SMMS]
GO
/****** Object:  StoredProcedure [dbo].[uspLogError]    Script Date: 11/01/2012 12:41:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[uspLogError] 
	@variables tvp_variables READONLY,
    @ErrorLogID [int] = 0 OUTPUT  -- Contains the ErrorLogID of the row inserted
                                  -- by uspLogError in the ErrorLog table.

AS
BEGIN

	DECLARE @record_id INT; 


    SET NOCOUNT ON;

    -- Output parameter value of 0 indicates that error 
    -- information was not logged.
    SET @ErrorLogID = 0;

    BEGIN TRY
        -- Return if there is no error information to log.
        IF ERROR_NUMBER() IS NULL
            RETURN;

        -- Return if inside an uncommittable transaction.
        -- Data insertion/modification is not allowed when 
        -- a transaction is in an uncommittable state.
        IF XACT_STATE() = -1
        BEGIN
            PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' 
                + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
            RETURN;
        END;

        INSERT [dbo].[ErrorLog] 
            (
            [UserName], 
            [ErrorNumber], 
            [ErrorSeverity], 
            [ErrorState], 
            [ErrorProcedure], 
            [ErrorLine], 
            [ErrorMessage]) 
        VALUES 
            (
            SYSTEM_USER, 
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE()
            );

		SET @ErrorLogId = @@IDENTITY;

		INSERT INTO tb_variables_errors(ErrorLogId, variable, value) 
		SELECT @ErrorLogId, variable, value FROM @variables;

        -- Pass back the ErrorLogID of the row inserted
        SELECT @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred in stored procedure uspLogError: ';
        EXECUTE [dbo].[uspPrintError];
        RETURN -1;
    END CATCH
END; 
1 ноя 12, 13:07    [13407975]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить