Board index » delphi » application user name in IB trigger

application user name in IB trigger

  Hi!

  I don't know if the subject describes my problem well enough
but here is a bit longer description:

  I want to have creation time, creator's name, change time and
changer's name on every row in a Interbase table. I want to do a
trigger for it. So the trigger for insert would look like:
        before insert:
          new.creationtime = "NOW";
          new.creator = USER;

With times or IB username there is no problems, but how can I
use applications user name here? I don't want to give every
application user a different IB user name.

  If there is a session id info available in IB it could
be one way (like Oracle userenv('SESSIONID'), but IB doesn't have
that function). When user logs in the application, IB saves the
session id and application user name in a table. Then
in the triggers IB gets the application user name with the
session id. One problem is how you delete the session id info
if the application doesn't terminate correctly...

  If you have any suggestions on this problem, let me hear it!

  BTW: Can I get list of db users with a SQL clause??? I didn't
find the system table that holds users...

  Veikko Vaataja
  veikko.vaat...@abo.fi

 

Re:application user name in IB trigger


Veikko V{{t{j{ TKKK (vvaat...@news.abo.fi) wrote:
:   I don't know if the subject describes my problem well enough
: but here is a bit longer description:

:   I want to have creation time, creator's name, change time and
: changer's name on every row in a Interbase table. I want to do a
: trigger for it. So the trigger for insert would look like:
:         before insert:
:           new.creationtime = "NOW";
:           new.creator = USER;

: With times or IB username there is no problems, but how can I
: use applications user name here? I don't want to give every
: application user a different IB user name.

:   If there is a session id info available in IB it could
: be one way (like Oracle userenv('SESSIONID'), but IB doesn't have
: that function). When user logs in the application, IB saves the
: session id and application user name in a table. Then
: in the triggers IB gets the application user name with the
: session id. One problem is how you delete the session id info
: if the application doesn't terminate correctly...

There is no way to pass any parameters to an InterBase trigger. Therefore,
what it can use for column values must be what is available within the
InterBase system. As an alternative, though, you can do this at the front-
end application.

The Delphi TTable and TQuery components have a BeforePost event. In a
procedure based on this event, you can use TTable/TQuery methods to put
application data into the table. This would fulfill the need you descr-
ibed and still be invisible to the end-user. Here's an example (LoginName
being a String variable containing the user's application name):

  procedure TForm1.Table1BeforePost(DataSet: TDataset);
  begin
    Table1.FieldByName('USERNAME').AsString := LoginName;
  end;

:   BTW: Can I get list of db users with a SQL clause??? I didn't
: find the system table that holds users...

The list of Local InterBase Server (LIBS) users is contained in the table
USERS in the database ISC4.GDB, which can be found in the directory
\IBLOCAL (or in whatever directory to which the RootDirectory setting in
the [InterBase] section in WIN.INI points). This is a standard LIBS table
and you can query it with standard SQL statements.

--
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
_/ Steve Koterski               _/   The opinions expressed here are    _/
_/ koter...@borland.com         _/         exclusively my own           _/
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

Re:application user name in IB trigger


Veikko V{{t{j{ TKKK (vvaat...@news.abo.fi) wrote:
:   Hi!
:   I don't know if the subject describes my problem well enough
: but here is a bit longer description:

:   I want to have creation time, creator's name, change time and
: changer's name on every row in a Interbase table. I want to do a
: trigger for it. So the trigger for insert would look like:
:         before insert:

In situations like this, one could write a user defined function
attached to the database that uses the native o/s facilities
to get the proces name,id, translate environment variables/
logicals/symbols etc. If you run an app such as UNIX->OpenVMS
as an example, the UDF would be in a shared library on the
DEC box which may be a problem if the data you want is on the
UNIX box and so on.

There are simpler solutions like passing data from the app into
work tables that are referenced in your code and so on. One app
on OpenVMS we wrote used the external relation feature. The
physical name referenced a logical name which referenced unique
files created in the users home directory that contained data.
Each user attaching created a unique file name and defined the
logical to this file. This avoided using functions (client had
not compilers and they could not maintain macro-32 code).
However, this worked because each process would open the
external file - no guarantee that this is true for all boxes/releases.
In addition, good 'old Gbak exhibits in 3.x a {*word*193} trait
of inisting that the external relation exist. It duly backs
the relation up and if restored turns it into an internal
relation (table) without warning. Thus, your work table no
longer references the temp files. This is avoided by dropping
the relation and adding it via script the surrounds the backup/
restore data.

--
Nigel Campbell            Voice: (613) 738-1338 ext 3016   P.O. Box 9707,Stn.T
Business Intelligence       FAX: (613) 738-0002            3755 Riverside Dr.
Cognos Incorporated    Internet: nig...@cognos.com         Ottawa ON K1G 4K9
                      URL: http://www.cognos.com           CANADA  

Other Threads