At present if I wanted to use a "global variable" in a procedure, function or trigger the only way I can see is to write it into a table, and then open the table and retrieve it.

It would be nice to have a single varchar global variable that I could set in SQL, Delphi or whatever and then access through a reserved word like I can CURRENT_DATE etc. VARCHAR because that give the opportunity for several "fields".

Roy Lambert

OK, now this is really low priority, wouldn't you admit?

In almost every database project, I create a table for such purposes:

CREATE TABLE "system"
(
"ValueName" VARCHAR(20) NOT NULL,
"IntegerValue" INTEGER,
"FloatValue" FLOAT,
"DateTimeValue" TIMESTAMP,
"BoolValue" BOOLEAN,
"StringValue" VARCHAR(200) COLLATE "ANSI",
CONSTRAINT "ValueName" UNIQUE ("ValueName")
)

Then, in a data module that is used by all other Delphi modules, I have
global functions, such as:

function GetSysStr(ValName: string; var Found: Boolean): string;
begin
 tblSystem.Open;
 Found := tblSystem.FindKey([ValName]);
 if Found then
   Result := tblSystemStringValue.AsString
 else
   Result := EmptyStr;
 tblSystem.Close;
end;

procedure PutSysStr(Valname: string; StrVal: string);
begin
 tblSystem.Open;
 if not tblSystem.FindKey([ValName]) then begin
   tblSystem.Append;
   tblSystemValueName.AsString := ValName;
 end else
   tblSystem.Edit;
 tblSystemBoolValue.AsBoolean := BoolVal;
 tblSystem.Post;
 tblSystem.Close;
end;

.... and other similar ones for the other field types.

Of course, you could easily create EDB functions to do the same thing at the
SQL level (and probably use those in the Delphi code above).

--
David Cornelius
CorneliusConcepts.com

Roy,

<< At present if I wanted to use a "global variable" in a procedure,
function or trigger the only way I can see is to write it into a table, and
then open the table and retrieve it.

It would be nice to have a single varchar global variable that I could set
in SQL, Delphi or whatever and then access through a reserved word like I
can CURRENT_DATE etc. VARCHAR because that give the opportunity for several
"fields". >>

If you want "fields", then why not just use a table ?

--
Tim Young
Elevate Software
www.elevatesoft.com

David

>OK, now this is really low priority, wouldn't you admit?

Yup

Roy Lambert

Tim

>If you want "fields", then why not just use a table ?

Performance. I can't test it but I'm guessing this should be quite a bit faster than using a table. Especially since most of the time it would only be carrying a single piece of information.

Roy Lambert

Roy,

<< Performance. I can't test it but I'm guessing this should be quite a bit
faster than using a table. Especially since most of the time it would only
be carrying a single piece of information. >>

If you're constantly writing to the table column(s), then you *might* see a
bit of a performance lag with a disk-based table.  However, if you're mostly
just reading from the table column(s), then it will be basically as fast as
a normal variable since the current row will be kept in memory, and
referenced/loaded just like a normal variable reference.  In fact, with EDB
both variables, parameters, and columns use the same common value class and
functionality.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>If you're constantly writing to the table column(s), then you *might* see a
>bit of a performance lag with a disk-based table. However, if you're mostly
>just reading from the table column(s), then it will be basically as fast as
>a normal variable since the current row will be kept in memory, and
>referenced/loaded just like a normal variable reference. In fact, with EDB
>both variables, parameters, and columns use the same common value class and
>functionality.

OK, let me give an example of how I was thinking of using it then yu can tell me how to make it fly

Logging triggers, now I've found out you can have lots of the same type of trigger, I want to be able to "turn off" for certain actions so in metacode something like

open controltable
if controltablefield is true then
...
do the logging
...
end

My thought was opening the table is going to take some time - inconsequential for the majority of occasions when a user is updating a table but if I have a batch process doing it running through 20k records it will be more significant.

If I'm wrong I withdraw my request.

Roy Lambert

Roy,

<< Logging triggers, now I've found out you can have lots of the same type
of trigger, I want to be able to "turn off" for certain actions so in
metacode something like

open controltable
if controltablefield is true then
..
do the logging
..
end

My thought was opening the table is going to take some time -
inconsequential for the majority of occasions when a user is updating a
table but if I have a batch process doing it running through 20k records it
will be more significant. >>

What you want to do is this in the trigger:

1) Use a sensitive cursor for the control table
2) Manually PREPARE a:

SELECT * FROM ControlTable

for the control table cursor.

3) OPEN the cursor
4) Read the value that you want, etc.
5) CLOSE the cursor

The SELECT query will get prepared once when it is first executed, and then
it will stay prepared and ready to go for all executions of the trigger
until the physical table for which it is defined is closed.  The only thing
that will happen for each execution is the open/close of the cursor, which
is much smaller set of operations.

We've also got an enhancement request in place for disabling triggers with
an SQL statement, so that will probably be the better bet for you when it
becomes available.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>What you want to do is this in the trigger:
>
>1) Use a sensitive cursor for the control table
>2) Manually PREPARE a:

being thick - inside the trigger or outside?

>
>SELECT * FROM ControlTable
>
>for the control table cursor.

>3) OPEN the cursor
>4) Read the value that you want, etc.
>5) CLOSE the cursor
>
>The SELECT query will get prepared once when it is first executed, and then
>it will stay prepared and ready to go for all executions of the trigger
>until the physical table for which it is defined is closed. The only thing
>that will happen for each execution is the open/close of the cursor, which
>is much smaller set of operations.

Sounds reasonable

>We've also got an enhancement request in place for disabling triggers with
>an SQL statement, so that will probably be the better bet for you when it
>becomes available.

I don't suppose it could be made to turn off RI as well could it?

Roy Lambert

Roy,

<< being thick - inside the trigger or outside? >>

Inside the trigger.

<< I don't suppose it could be made to turn off RI as well could it? >>

Not in the same switch, no.  But yes, we've also got that on the list.

--
Tim Young
Elevate Software
www.elevatesoft.com