insert
связанный сервер
триггер
Хранимая процедура
Технические вопросы
5.x
Insert из BPM в таблицу на связанном сервере (Firebird 2.0)
Буторин Александр
13 марта 2015 08:21

Доброго времени суток, коллеги. При интеграции BPM с внешней БД возникла необходимость вставлять даннные из карточки BPM в таблицу на связанном сервере (Firebird 2.0). Для работы с таблицами связанного сервера содали представления. С селектами из этих представлений никаких проблем не было. Но вот с инсертами и апдейтами возникла проблемка - после инсерта(апдейта или делита) транзакции подвисали минут на 5 и выполнить очередной запрос к этому представлению было невозможно.
Нашли выход из этой ситуевины:
1. Создали на связанном сервере хранимую процедуру (пишет данные в таблицу и возвращает одно значение).
2. На sql сервере в представлении создали триггер который срабатывает на инсерт, пинает хранимку на связанном сервере и передает параметры в эту хранимку.
3. В карточку BPM добавили кнопку и повесили на нее скрипт которые инсертит данные из полей карточки в представление.
После данные манипуляций все работает отлично, данные инсертятся, транзакции не подвисают.

Но вот возник вопросик. Как я написал в первом пункте, хранимка возвращает одно значение и мы не можем приложить ума как его перехватить чтобы вернуть это значение в текстовое поле в карточку BPM. Ниже код хранимки, триггера и скрипта инсерта.

Код хранимки.

BEGIN

INSERT INTO NAPRAVLENIE(NAPRAVLENIE.NAPRAVLENIE_NUM,NAPRAVLENIE.USLUGA_1) VALUES (:NUM,:U1);
NEW_OUTPARAM=gen_id(BP_GEN,1);

  SUSPEND;
END

Код триггера.

USE [BPMonline]
GO
/****** Object:  Trigger [dbo].[IOI_napr]    Script Date: 03/11/2015 17:56:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[IOI_napr]ON [dbo].[napr]
INSTEAD OF INSERT
AS
BEGIN
declare @U1 varchar(250)
declare @NUM varchar (250)
declare @NAP_NUM varchar (250)
SET @U1=(SELECT i.USLUGA_1 FROM inserted i)
SET @NUM=(SELECT i.NAPRAVLENIE_NUM FROM inserted i)
execute ('select * from BP_nap_ins('''+@NUM+''','''+@U1+''')') at DMS
 
END;

Код скрипта инсерта кнопки.

var U1=Page.TextEdit5.Value; // для теста передаем только 1 значение
var insert = new Insert(UserConnection).Into("napr").Set("USLUGA_1", Column.Parameter(U1)).Execute();
        return true;

если вы полнить инсерт из sql менеджера то в результах я вижу, что хранимка вернула значение.

каким оброзом можно запихнуть возвращаемое значение в переменную и использовать ее дальше в BPM ?
Спасибо что дочитали :)

Нравится

25 комментариев
Буторин Александр
13 марта 2015 10:17

если в скрипт кнопки добавить 

Page.TextEdit14.SetValue(insert);

то в TextEdit14 пишется 1 (единица), но передоваемое значение не равно 1.

Олейников Владимир Владимирович
17 марта 2015 12:22

Попробуйте указанный код, вместо Insert

var selectSQL = new CustomQuery(UserConnection, sqlText);
using (var dbExecutor = UserConnection.EnsureDBConnection()) {
	using(var dr = selectSQL.ExecuteReader(dbExecutor)) {
		while (dr.Read()) {
			PreviousMonth = dr.GetValue(0).ToString();
		}
	}
}
Буторин Александр
17 марта 2015 16:22

Спасибо за ответ, пошли немного другим путем. Запихнули текст триггера еще в одну хранимку, и через OPENQUERY пнули хранимку на связанном сервере. Таким макаром удалось вернуть значение в переменную. Сейчас бытаюсь разобраться как вызвать хранимку из формы.

может кому не сложно написать коды вызова хранимки:

в хранимку передаем значение из поля TextEdit5.
вернуть значение нужно в TextEdit14

почитал немного тут http://www.community.terrasoft.ru/blogs/7708

вот, что наваял:

var U1=Page.TextEdit5.Value;
//var NAP_NUM = null;
var dataValueTypeManager = (DataValueTypeManager)UserConnection.AppManagerProvider.GetManager("DataValueTypeManager");
var proc = new StoredProcedure(UserConnection, "BNS_to_DMS");
StoredProcedure.WithParameter(U1);
StoredProcedure.WithOutputParameter("NAP_NUM", dataValueTypeManager.GetInstanceByName("Text"));
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
        dbExecutor.StartTransaction(System.Data.IsolationLevel.ReadUncommitted);
        Page.DataSource.ActiveRow.Save();
        StoredProcedure.Execute(dbExecutor);}
SetCloseStatus(1);
//Connector.DBEngine.ExecuteCustomSQL(SQLText);
//var insert = new Insert(UserConnection).Into("napr").Set("USLUGA_1", Column.Parameter(U1)).Execute();
 	Page.TextEdit14.SetValue(NAP_NUM);
	return true;

но валится много ошибок при компиляции.

PS: извиняйте что туплю, я не программист

Буторин Александр
17 марта 2015 16:27

код хранимки

USE BPMonline;
IF OBJECT_ID ( 'dbo.BPM_to_DMS', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.BPM_to_DMS;
GO
CREATE PROCEDURE dbo.BPM_to_DMS
    @U1 varchar(250),
    @NAP_NUM varchar (250) OUTPUT
    --@U2 varchar (250) 
  AS 
 
    SET NOCOUNT ON;
 
 set @NAP_NUM = (select * from OPENQUERY (dms,'select * from BP_nap_ins(''+@U1+'')'))
 
 GO
Буторин Александр
18 марта 2015 15:40

итак, удалось запустить хранимку, передать ей значение в U1, но никак не могу разобраться как вернуть из нее значение в TextEdit14. необходимое значение возвращается в NAP_NUM.

код вызова хранимки: 

var U1=Page.TextEdit5.Value;
//var NAP_NUM;
var dataValueTypeManager = (DataValueTypeManager)UserConnection.AppManagerProvider.GetManager("DataValueTypeManager");
var storedProcedure = new StoredProcedure(UserConnection, "BPM_to_DMS");
storedProcedure.WithParameter(U1);
storedProcedure.WithOutputParameter("NAP_NUM", dataValueTypeManager.GetInstanceByName("Text"));
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
        dbExecutor.StartTransaction(System.Data.IsolationLevel.ReadUncommitted);
        storedProcedure.Execute(dbExecutor);
				dbExecutor.CommitTransaction();
}
 
 	//Page.TextEdit14.SetValue(NAP_NUM);
	return true;

можно хотябы пример похожий (в примере с поиском дублей ничего не понятно)

PS: когда все допилю обещаю написать полный мануал по вызову хранимой процедуры на связанном сервере firebird 2.0

Олейников Владимир Владимирович
18 марта 2015 18:03

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

Буторин Александр
19 марта 2015 08:08

"Олейников Владимир Владимирович" написал:

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


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

Олейников Владимир Владимирович
19 марта 2015 08:28

там все очень просто. UserConnection - Переменная которая хранит в себе параметры подключения.
sqlText - сюда можно запихнуть любой SQL запрос, в том числе и вызов хранимой процедуры.

while (dr.Read()) {
PreviousMonth = dr.GetValue(0).ToString();
}

"dr" - сохраняет в себе структуру возвращенных данных

Буторин Александр
19 марта 2015 09:18

"Олейников Владимир Владимирович" написал:

там все очень просто. UserConnection - Переменная которая хранит в себе параметры подключения.

sqlText - сюда можно запихнуть любой SQL запрос, в том числе и вызов хранимой процедуры.

while (dr.Read()) {

PreviousMonth = dr.GetValue(0).ToString();

}

"dr" - сохраняет в себе структуру возвращенных данных


спасибо, будем пробовать

Буторин Александр
19 марта 2015 12:31

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

забал сказать что хранимка возвращает не просто число, а текст, по сути номер направления ( пример 15326/2015)

Буторин Александр
19 марта 2015 12:53

"Олейников Владимир Владимирович" написал:

там все очень просто. UserConnection - Переменная которая хранит в себе параметры подключения.

sqlText - сюда можно запихнуть любой SQL запрос, в том числе и вызов хранимой процедуры.

while (dr.Read()) {

PreviousMonth = dr.GetValue(0).ToString();

}

"dr" - сохраняет в себе структуру возвращенных данных

Владимир, ругается на PreviousMonth,

The name 'PreviousMonth' does not exist in the current context
DMSEditPage.cs
2918

Олейников Владимир Владимирович
19 марта 2015 13:01

PreviousMonth - это локальная переменная, которая создается и туда записывается значение. Можете вместо этого кода вписать
следующий код
Page.TextEdit14.SetValue(dr.GetValue(0).ToString());
только вместо "0" подставте нужный индекс. Посмотрите какой он по счету в выборке в запросе

Буторин Александр
19 марта 2015 14:28

"Олейников Владимир Владимирович" написал:

PreviousMonth - это локальная переменная, которая создается и туда записывается значение. Можете вместо этого кода вписать

следующий код

Page.TextEdit14.SetValue(dr.GetValue(0).ToString());

только вместо "0" подставте нужный индекс. Посмотрите какой он по счету в выборке в запросе


Владимир, попробовал подставить Page.TextEdit14.SetValue(dr.GetValue(0).ToString());

скрипт отрабатывает но в TextEdit14 ничего не возвращает(пробовал разные индексы(0 1 2), а вообще запись по счету первая).

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

var U1=Page.TextEdit5.Value;
//var result = 5;
var dataValueTypeManager = (DataValueTypeManager)UserConnection.AppManagerProvider.GetManager("DataValueTypeManager");
var storedProcedure = new StoredProcedure(UserConnection, "BPM_to_DMS");
storedProcedure.WithParameter(U1);
storedProcedure.WithOutputParameter("NAP_NUM", dataValueTypeManager.GetInstanceByName("Text"));
using (var dbExecutor = UserConnection.EnsureDBConnection()) {
        dbExecutor.StartTransaction(System.Data.IsolationLevel.ReadUncommitted);
       // storedProcedure.Execute(dbExecutor);
	{
        using(var dr =storedProcedure.ExecuteReader(dbExecutor)) {
                while (dr.Read()) {
                     Page.TextEdit14.SetValue(dr.GetValue(0).ToString());
 
                }
        }
}
			dbExecutor.CommitTransaction();
 
}
 
 
	return true;

Профайлер перехватывает выполнение и показывает что в @NAP_NUM вернулось нужное значение

Олейников Владимир Владимирович
19 марта 2015 14:39

Попробуйте в дебаге посмотреть что вам возвращает переменная "dr" и все увидите

Олейников Владимир Владимирович
19 марта 2015 14:42
var dataValueTypeManager = (DataValueTypeManager)UserConnection.AppManagerProvider.GetManager("DataValueTypeManager");
var proc = new StoredProcedure(UserConnection, "BNS_to_DMS");
StoredProcedure.WithParameter(U1);
StoredProcedure.WithOutputParameter("NAP_NUM", dataValueTypeManager.GetInstanceByName("Text"));
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
        dbExecutor.StartTransaction(System.Data.IsolationLevel.ReadUncommitted);
        Page.DataSource.ActiveRow.Save();
        StoredProcedure.Execute(dbExecutor);}
SetCloseStatus(1);
//Connector.DBEngine.ExecuteCustomSQL(SQLText);
//var insert = new Insert(UserConnection).Into("napr").Set("USLUGA_1", Column.Parameter(U1)).Execute();
        Page.TextEdit14.SetValue(NAP_NUM);
        return true;

Вместо этого кода напишите мой

Буторин Александр
19 марта 2015 14:51

"Олейников Владимир Владимирович" написал:

var dataValueTypeManager = (DataValueTypeManager)UserConnection.AppManagerProvider.GetManager("DataValueTypeManager");

var proc = new StoredProcedure(UserConnection, "BNS_to_DMS");

StoredProcedure.WithParameter(U1);

StoredProcedure.WithOutputParameter("NAP_NUM", dataValueTypeManager.GetInstanceByName("Text"));

using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {

        dbExecutor.StartTransaction(System.Data.IsolationLevel.ReadUncommitted);

        Page.DataSource.ActiveRow.Save();

        StoredProcedure.Execute(dbExecutor);}

SetCloseStatus(1);

//Connector.DBEngine.ExecuteCustomSQL(SQLText);

//var insert = new Insert(UserConnection).Into("napr").Set("USLUGA_1", Column.Parameter(U1)).Execute();

        Page.TextEdit14.SetValue(NAP_NUM);

        return true;

Вместо этого кода напишите мой


Владимир, простите что туплю, но я не знаю как через ваш код вызвать хранимку и передать в нее параметр, межете пример привести? если не сложно

Буторин Александр
19 марта 2015 15:17

вот что выдает дебагер

Олейников Владимир Владимирович
19 марта 2015 17:03
var sqlText = string.Format("ProcName {0},{1}", "value1", "value2")

Объявляем переменную, в которой хранится вызов хранимой процедуры с переметрами "value1", "value2"

var selectSQL = new CustomQuery(UserConnection, sqlText);
using (var dbExecutor = UserConnection.EnsureDBConnection()) {
        using(var dr = selectSQL.ExecuteReader(dbExecutor)) {
                while (dr.Read()) {
                        PreviousMonth = dr.GetValue(0).ToString();
                }
        }
}
Олейников Владимир Владимирович
19 марта 2015 17:14

хранимая процедура вызывается как и любая SQL функция здесь

Буторин Александр
19 марта 2015 17:44

"Олейников Владимир Владимирович" написал:

хранимая процедура вызывается как и любая SQL функция здесь


спасибо за ответ, вот что вышло, 

var U1=Page.TextEdit5.Value;
var Proc = string.Format("BPM_TO_DMS {0},{1}", "U1", "NAP_NUM");
	var selectSQL = new CustomQuery(UserConnection, Proc);
using (var dbExecutor = UserConnection.EnsureDBConnection()) {
        using(var dr = selectSQL.ExecuteReader(dbExecutor)) {
                while (dr.Read()) {
                        Page.TextEdit14.SetValue(dr.GetValue(1).ToString());
                }
        }
}
return true;

но при выполнении ничего не происходит, дебагер выдал все переменные по null, профайлер вы дал просто строку "BPM_TO_DMS U1,NAP_NUM" куда копать?

Олейников Владимир Владимирович
19 марта 2015 17:49
var Proc = string.Format("BPM_TO_DMS {0},{1}", "U1", "NAP_NUM");

Вы не коректно написали, UI Надо писать без кавычек, это имя переменной, в которой хранится значение передаваемой в хранимку переменной.

Буторин Александр
20 марта 2015 08:31

"Олейников Владимир Владимирович" написал:

Вы не коректно написали, UI Надо писать без кавычек, это имя переменной, в которой хранится значение передаваемой в хранимку переменной.


Владимир, если написать таким образом то просит объявить переменную NAP_NUM, если ее объявить то просит записать туда какаое-нить значение. Если передать туда 2 значения то процедура не срабатывает т.к. в процедуре ожидается 1 параметр входящий и 1 исходящий, а таким образом мы передаем туда 2 входящих. Может подскажите каким образом обявить NAP_NUM исходящим параметром? Спасибо.

Олейников Владимир Владимирович
20 марта 2015 11:25

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

var Proc = string.Format("BPM_TO_DMS {0},{1} ...", "U1", "NAP_NUM", ...);
Буторин Александр
20 марта 2015 13:11

"Олейников Владимир Владимирович" написал:

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

var Proc = string.Format("BPM_TO_DMS {0},{1} ...", "U1", "NAP_NUM", ...);


Владимир, это я понял, но мне нужно передать в хранимку 1 параметр, и хранимка должна вернуть 1 параметр. для этого в хранимке объявлен переменная NAP_NUM OUTPUT (в которую возвращается результат работы хранимки). елси вызывать хранимку через var proc = new StoredProcedure то там можно указать какой из параметров входящий а какой выходящий. Может вы подскажете каким образом в вашем скрипте указать что NAP_NUM это OUTPUT параметр? спасибо за помощь

Олейников Владимир Владимирович
20 марта 2015 14:11

В конце хранимки сделать выборку нужного вам параметра

DECLARE @d INT = 0
-- Какие-то действия с переменной
SELECT  @d
Показать все комментарии
Подписаться на связанный сервер