--- In firebird-support@yahoogroups.com, "Roberto Freitas"
<solanofreitas@...> wrote:

>
> Well, this question is about an existing and very successfull
> application concerning a Distributed Database, which tables are
> simultaneily in different SQL Servers.
> In this situation, it has been necessary the application to pass a
> kind of "inoffensive" string to the database server, so we can decide
> some actions in a trigger depending on that string value, as I've
> been doing with SQL Server, through string connection parameters.
> I haven't yet gived up to do it with FireBird ...

You can not do this right now with Firebird. There are a huge number
of connection libraries to Firebird, so I do not know if the
connection string is the easiest place for this to be implemented.

Something to achieve the same thing you CAN do right now in Firebird
1.5 follows:

----

CREATE TABLE MYTABLE
(
CONNECTIONNO INTEGER NOT NULL,
INOFFENSIVESTRING VARCHAR(20),
PRIMARY KEY (CONNECTIONNO)
);

SET TERM ^;

CREATE OR ALTER PROCEDURE SP_REGISTERCON AS
(
INOFFENSIVESTRING VARCHAR(20)
)
BEGIN
DELETE FROM MYTABLE WHERE CONNECTIONNO = CURRENT_CONNECTION;
INSERT INTO MYTABLE (CONNECTIONNO, INOFFENSIVESTRING) VALUES
(CURRENT_CONNECTION, :INOFFENSIVESTRING);
END
^

CREATE OR ALTER PROCEDURE SP_UNREGISTERCON AS
BEGIN
DELETE FROM MYTABLE WHERE CONNECTIONNO = CURRENT_CONNECTION;
END
^

CREATE OR ALTER PROCEDURE SP_GETINOFFENSIVESTRING
RETURNS
(
INOFFENSIVESTRING VARCHAR(20)
)
AS
BEGIN
SELECT INOFFENSIVESTRING
FROM MYTABLE
WHERE CONNECTIONNO = CURRENT_CONNECTION
INTO :INOFFENSIVESTRING;

SUSPEND;
END
^

SET TERM ; ^

COMMIT WORK;

---

When your application connects, create a transaction and run the
SP_REGISTERCON procedure, and then commit it.

When your application disconnects gracefully, do the same but with the
SP_UNREGISTERCON procedure.

Then your triggers can then run

select INOFENSIVESTRING
from SP_GETINOFFENSIVESTRING
into :INOFFENSIVESTRING;

Whenever required.

You will notice my register does a delete first. This is because the
connection number resets after a backup-restore, and if an old
connection was abandoned ungracefully, it's record will still be there.

This could be expanded to store multiple environment variables for a
connection. Of course FB 2 already has a similar feature right out of
the box in context variables.

Good luck

Adam