Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How to get an application variable into a Trigger?

11 posts in Trigger Last posting was on 2010-08-25 13:59:28.0Z
Ian Branch Posted on 2010-07-30 10:13:25.0Z
From: "Ian Branch" <branch@celestial.com.au>
Subject: How to get an application variable into a Trigger?
Newsgroups: Advantage.Trigger
User-Agent: XanaNews/1.19.1.269
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 118.209.96.129
Message-ID: <4c5297b5@solutions.advantagedatabase.com>
Date: 30 Jul 2010 03:13:25 -0700
X-Trace: 30 Jul 2010 03:13:25 -0700, 118.209.96.129
Lines: 23
Path: solutions.advantagedatabase.com!118.209.96.129
Xref: solutions.advantagedatabase.com Advantage.Trigger:455
Article PK: 1136518

Hi Guys,

D2010, ADS v10.

Is it possible to pass an application variable to a Trigger? e.g. Say
I have a calculated variable within the code/application called Charge,
a ####.## variable.

I want to get that value/variable into a Trigger so I can do the
following..

IF @OldStatus <> @NewStatus THEN
INSERT INTO STATLOG VALUES (@Invnumb, NOW(), Charge , NULL, @User
,@NewStatus);
END IF;

Can this be done?

If so how please?

Regards,

Ian


Ian Branch Posted on 2010-07-30 10:17:07.0Z
From: "Ian Branch" <branch@celestial.com.au>
Subject: Re: How to get an application variable into a Trigger?
Newsgroups: Advantage.Trigger
References: <4c5297b5@solutions.advantagedatabase.com>
User-Agent: XanaNews/1.19.1.269
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 118.209.96.129
Message-ID: <4c529893@solutions.advantagedatabase.com>
Date: 30 Jul 2010 03:17:07 -0700
X-Trace: 30 Jul 2010 03:17:07 -0700, 118.209.96.129
Lines: 2
Path: solutions.advantagedatabase.com!118.209.96.129
Xref: solutions.advantagedatabase.com Advantage.Trigger:456
Article PK: 1136516

I should have mentioned, this trigger is in the DD.


Mark Wilkins Posted on 2010-08-02 21:29:28.0Z
From: "Mark Wilkins" <mark.wilkins@nospamplease>
Newsgroups: Advantage.Trigger
References: <4c5297b5@solutions.advantagedatabase.com>
In-Reply-To: <4c5297b5@solutions.advantagedatabase.com>
Subject: Re: How to get an application variable into a Trigger?
Date: Mon, 2 Aug 2010 15:29:28 -0600
Lines: 1
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8089.726
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8089.726
NNTP-Posting-Host: 10.24.40.128
Message-ID: <4c573808@solutions.advantagedatabase.com>
X-Trace: 2 Aug 2010 15:26:32 -0700, 10.24.40.128
Path: solutions.advantagedatabase.com!10.24.40.128
Xref: solutions.advantagedatabase.com Advantage.Trigger:457
Article PK: 1136519

Hi Ian,

Here are some possible ways to get the job done:
1) Add a "temporary" column to the table and store the value in that field
during the update so that the trigger has access to it.
2) Store the value in a temporary table (#tmptablewithappvalue) and read it
out of the temp table from the trigger.

I don't think either of those are very good plans. The problem is that it
is mixing server-side information with client-side information and there is
no way to enforce that the correct information is available. Any new
updates to that table would have to know about the required/expected setup
that has to be performed first, and it would almost certainly lead to future
problems (at least I'm sure I would forget something like that).

It seems maybe a better way to handle it might be to force the updates to go
through stored procedures. Take away update permissions from the table and
create one or more stored procedures to do the updates. One parameter of
the stored procedure could be the Charge variable.

Mark Wilkins
Advantage R&D

"Ian Branch" <branch@celestial.com.au> wrote in message
news:4c5297b5@solutions.advantagedatabase.com...
> Hi Guys,
>
> D2010, ADS v10.
>
> Is it possible to pass an application variable to a Trigger? e.g. Say
> I have a calculated variable within the code/application called Charge,
> a ####.## variable.
>
> I want to get that value/variable into a Trigger so I can do the
> following..
>
> IF @OldStatus <> @NewStatus THEN
> INSERT INTO STATLOG VALUES (@Invnumb, NOW(), Charge , NULL, @User
> ,@NewStatus);
> END IF;
>
> Can this be done?
>
> If so how please?
>
> Regards,
>
> Ian


Ian Branch Posted on 2010-08-03 08:58:10.0Z
From: "Ian Branch" <branch@celestial.com.au>
Subject: Re: How to get an application variable into a Trigger?
Newsgroups: Advantage.Trigger
References: <4c5297b5@solutions.advantagedatabase.com> <4c573808@solutions.advantagedatabase.com>
User-Agent: XanaNews/1.19.1.269
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 118.209.96.129
Message-ID: <4c57cc12@solutions.advantagedatabase.com>
Date: 3 Aug 2010 01:58:10 -0700
X-Trace: 3 Aug 2010 01:58:10 -0700, 118.209.96.129
Lines: 19
Path: solutions.advantagedatabase.com!118.209.96.129
Xref: solutions.advantagedatabase.com Advantage.Trigger:458
Article PK: 1136517


Mark Wilkins wrote:
>
> It seems maybe a better way to handle it might be to force the
> updates to go through stored procedures. Take away update permissions
> from the table and create one or more stored procedures to do the
> updates. One parameter of the stored procedure could be the Charge
> variable.

Hi Mark,

This is sort of the conclusion I had reached as well.

I will give it some more thought.

Thank you for your input.

Regards,

Ian


Joachim Duerr (ADS) Posted on 2010-08-16 07:48:51.0Z
From: "Joachim Duerr (ADS)" <jojo.duerr@gmx.de>
Subject: Re: How to get an application variable into a Trigger?
Newsgroups: Advantage.Trigger
References: <4c5297b5@solutions.advantagedatabase.com>
Date: Mon, 16 Aug 2010 09:48:51 +0200
User-Agent: XanaNews/1.19.1.269
X-Face: u2p+</,mb|Ah!x!/qxX5q0t:O~.<1&JzwNHYhSqcviY{~&|iDc"U.Je1A.ZeHR`d;;y#R
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 130.214.79.11
Message-ID: <4c68ec85@solutions.advantagedatabase.com>
X-Trace: 16 Aug 2010 01:45:09 -0700, 130.214.79.11
Lines: 39
Path: solutions.advantagedatabase.com!130.214.79.11
Xref: solutions.advantagedatabase.com Advantage.Trigger:459
Article PK: 1136523


Ian Branch wrote:

> Is it possible to pass an application variable to a Trigger? e.g.
>Say I have a calculated variable within the code/application called
>Charge, a ####.## variable.

CREATE FUNCTION locals
(
id_ string, value_ string
)
RETURNS string
BEGIN
try
select top 1 * from #locals;
catch all
create table #locals(id cichar(255), value memo);
end try;
if value_ is not null then
merge #locals on id=id_
when matched then update set value=value_
when not matched then insert(id,value) values(id_,value_);
endif;
return (select top 1 value from #locals where id=id_);

END;


usage:

--set a value
myvar=locals('myvar','anyvalue');

--read a stored value
myvar=locals('myvar',null);

--
Joachim Duerr
Advantage Presales
check out my new ADS book on http://www.jd-engineering.de/adsbuch


Ian Branch Posted on 2010-08-16 10:01:44.0Z
From: "Ian Branch" <branch@celestial.com.au>
Subject: Re: How to get an application variable into a Trigger?
Newsgroups: Advantage.Trigger
References: <4c5297b5@solutions.advantagedatabase.com> <4c68ec85@solutions.advantagedatabase.com>
User-Agent: XanaNews/1.19.1.269
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 118.209.252.98
Message-ID: <4c68fe78@solutions.advantagedatabase.com>
Date: 16 Aug 2010 03:01:44 -0700
X-Trace: 16 Aug 2010 03:01:44 -0700, 118.209.252.98
Lines: 28
Path: solutions.advantagedatabase.com!118.209.252.98
Xref: solutions.advantagedatabase.com Advantage.Trigger:460
Article PK: 1136522

Hi Joachim,

That looks cool.

I should be able to translate that directly into a DD Function?

i.e. 3 parameters, value_string (input, character, 10), id_string
(input, character, 10) & ReturnValue (return, character, 10).

With the following in the actual function..

try
select top 1 * from #locals;
catch all
create table #locals(id cichar(255), value memo);
end try;

if value_ is not null then
merge #locals on id=id_
when matched then update set value=value_
when not matched then insert(id,value) values(id_,value_);
endif;

return (select top 1 value from #locals where id=id_);

Regards & thanks,

Ian


Ian Branch Posted on 2010-08-16 10:38:29.0Z
From: "Ian Branch" <branch@celestial.com.au>
Subject: Re: How to get an application variable into a Trigger?
Newsgroups: Advantage.Trigger
References: <4c5297b5@solutions.advantagedatabase.com> <4c68ec85@solutions.advantagedatabase.com> <4c68fe78@solutions.advantagedatabase.com>
User-Agent: XanaNews/1.19.1.269
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 118.209.252.98
Message-ID: <4c690715@solutions.advantagedatabase.com>
Date: 16 Aug 2010 03:38:29 -0700
X-Trace: 16 Aug 2010 03:38:29 -0700, 118.209.252.98
Lines: 15
Path: solutions.advantagedatabase.com!118.209.252.98
Xref: solutions.advantagedatabase.com Advantage.Trigger:461
Article PK: 1136524

Hmmm. OK. I have the function in the DD.
Where/how do I make it visible to the application?

i.e.
procedure TForm2.Button1Click(Sender: TObject);
begin
Label1.Caption:= locals('myvar',Edit1.Text);
Label1.Caption:= locals('myvar',null);
end;

How does locals become 'visible?

Regards,

Ian


Joachim Duerr (ADS) Posted on 2010-08-16 11:54:27.0Z
From: "Joachim Duerr (ADS)" <jojo.duerr@gmx.de>
Subject: Re: How to get an application variable into a Trigger?
Newsgroups: Advantage.Trigger
References: <4c5297b5@solutions.advantagedatabase.com> <4c68ec85@solutions.advantagedatabase.com> <4c68fe78@solutions.advantagedatabase.com> <4c690715@solutions.advantagedatabase.com>
Date: Mon, 16 Aug 2010 13:54:27 +0200
User-Agent: XanaNews/1.19.1.269
X-Face: u2p+</,mb|Ah!x!/qxX5q0t:O~.<1&JzwNHYhSqcviY{~&|iDc"U.Je1A.ZeHR`d;;y#R
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 130.214.79.11
Message-ID: <4c692615@solutions.advantagedatabase.com>
X-Trace: 16 Aug 2010 05:50:45 -0700, 130.214.79.11
Lines: 32
Path: solutions.advantagedatabase.com!130.214.79.11
Xref: solutions.advantagedatabase.com Advantage.Trigger:462
Article PK: 1136520


Ian Branch wrote:

>Hmmm. OK. I have the function in the DD.
>Where/how do I make it visible to the application?
>
>i.e.
>procedure TForm2.Button1Click(Sender: TObject);
>begin
>Label1.Caption:= locals('myvar',Edit1.Text);
>Label1.Caption:= locals('myvar',null);
>end;
>
>How does locals become 'visible?

you can't make a function visible on your client...but you can invoke a
SQL command to use it:

AdsQuery1.SQL.Text:='select
locals('+QuotedStr('myvar')+','+QuotedStr(Edit1.Text)+') from
system.iota';
AdsQuery1.ExecSQL;

AdsQuery1.SQL.Text:='select locals('+QuotedStr('myvar')+',null) from
system.iota';
AdsQuery1.Open;
Label1.Caption:=AdsQuery1.Fields[0].AsString;
AdsQuery1.Close;

--
Joachim Duerr
Advantage Presales
check out my new ADS book on http://www.jd-engineering.de/adsbuch


Ian Branch Posted on 2010-08-16 21:17:36.0Z
From: "Ian Branch" <branch@celestial.com.au>
Subject: Re: How to get an application variable into a Trigger?
Newsgroups: Advantage.Trigger
References: <4c5297b5@solutions.advantagedatabase.com> <4c68ec85@solutions.advantagedatabase.com> <4c68fe78@solutions.advantagedatabase.com> <4c690715@solutions.advantagedatabase.com> <4c692615@solutions.advantagedatabase.com>
User-Agent: XanaNews/1.19.1.269
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 118.209.252.98
Message-ID: <4c699ce0@solutions.advantagedatabase.com>
Date: 16 Aug 2010 14:17:36 -0700
X-Trace: 16 Aug 2010 14:17:36 -0700, 118.209.252.98
Lines: 11
Path: solutions.advantagedatabase.com!118.209.252.98
Xref: solutions.advantagedatabase.com Advantage.Trigger:463
Article PK: 1136521

Hi Joachim,

Ahhh. After some playing I had sorta deduced that was what was
required.

Thank you for the confirmation. All part of the learning
experience..:-)

Regards,

Ian


Ian Branch Posted on 2010-08-16 21:29:06.0Z
From: "Ian Branch" <branch@celestial.com.au>
Subject: Re: How to get an application variable into a Trigger?
Newsgroups: Advantage.Trigger
References: <4c5297b5@solutions.advantagedatabase.com> <4c68ec85@solutions.advantagedatabase.com> <4c68fe78@solutions.advantagedatabase.com> <4c690715@solutions.advantagedatabase.com> <4c692615@solutions.advantagedatabase.com>
User-Agent: XanaNews/1.19.1.269
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 118.209.252.98
Message-ID: <4c699f92@solutions.advantagedatabase.com>
Date: 16 Aug 2010 14:29:06 -0700
X-Trace: 16 Aug 2010 14:29:06 -0700, 118.209.252.98
Lines: 35
Path: solutions.advantagedatabase.com!118.209.252.98
Xref: solutions.advantagedatabase.com Advantage.Trigger:464
Article PK: 1136525

Small problem.

First chance exception at $75C89617. Exception class EADSDatabaseError
with message 'AdsQuery1: Error 7200: AQE Error: State = HY000;
NativeError = 7112; [iAnywhere Solutions][Advantage SQL][ASA] Error
7112: The temporary table cannot be found. Table name: #locals **
Script error information: -- Location of error in the SQL statement
is: 155 (line: 8 column: 5)'. Process Project2.exe (5236)

I experienced this while playing around as well.

Full script comes out as..

CREATE FUNCTION locals
(
value_string CHAR ( 10 ),
id_string CHAR ( 10 )
)
RETURNS CHAR ( 10 )
BEGIN
try
select top 1 * from #locals;
catch all
create table #locals (id cichar(255), value memo);
end try;

if value_string is not null then
merge #locals on id=id_string
when matched then update set value=value_string
when not matched then insert(id,value)
values(id_string,value_string);
endif;

return (select top 1 value from #locals where id=id_string);
END;


Tom Arleth Posted on 2010-08-25 13:59:28.0Z
From: "Tom Arleth" <tom.a@compugroupmedical.dk>
Newsgroups: Advantage.Trigger
References: <4c5297b5@solutions.advantagedatabase.com>
In-Reply-To: <4c5297b5@solutions.advantagedatabase.com>
Subject: Re: How to get an application variable into a Trigger?
Date: Wed, 25 Aug 2010 15:59:28 +0200
Lines: 2
Organization: CompuGroup Medical Denmark
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8117.416
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8117.416
NNTP-Posting-Host: 62.242.32.53
Message-ID: <4c752104@solutions.advantagedatabase.com>
X-Trace: 25 Aug 2010 07:56:20 -0700, 62.242.32.53
Path: solutions.advantagedatabase.com!62.242.32.53
Xref: solutions.advantagedatabase.com Advantage.Trigger:465
Article PK: 1136526

Hi Ian,

You can make your Application modify the applicationID of the database
databaseconnection to contain the value you want to acces in the trigger.
The applicationID is accesable by the triggers.

NB. That is if you dont still use the applicationID to log usernames with
LogManager :-)

Procedure OnLogin(userName string);
begin
//in case of multiple logins by same username
SQLconnection.execute('Execute procedure
sp_SetApplicationID(userName@computername#ProcessID)');
end



--
Kind regards
Tom Arleth

"Ian Branch" <branch@celestial.com.au> skrev i meddelelsen
news:4c5297b5@solutions.advantagedatabase.com...

> Hi Guys,
>
> D2010, ADS v10.
>
> Is it possible to pass an application variable to a Trigger? e.g. Say
> I have a calculated variable within the code/application called Charge,
> a ####.## variable.
>
> I want to get that value/variable into a Trigger so I can do the
> following..
>
> IF @OldStatus <> @NewStatus THEN
> INSERT INTO STATLOG VALUES (@Invnumb, NOW(), Charge , NULL, @User
> ,@NewStatus);
> END IF;
>
> Can this be done?
>
> If so how please?
>
> Regards,
>
> Ian