CODEWITHSUNDEEP
firebird
Alex James
Alex James

Reputation: 696

How to declare/use local variables in client-side DML scripts for Firebird

I'm new to Firebird DML.

In TransactSQL I can declare and use variables freely and simply, as in

declare @myID int

select @myID = 42

and use these variables in SELECT statements

select * from mytable where id = @myID

and to pass values between different statements.

My question is, simply, how can I declare and use local variables in a client-side Firebird DML script?

Using, say, FlameRobin, I can execute a series of DML statements in one go, e.g.

update mytable set price = 2 * price;
select * from mytable;

but I'm completely stumped on how to declare and use local variables to pass values amongst them.

Googling around, I've come across the EXECUTE BLOCK construct for Firebird's PSql, which evidently supports DECLARE VARIABLE, but it looks cumbersome, seems to contain some bizarre restrictions I've never encountered in TransactSQL and doesn't seem to be intended for the kind of client-side DML scripting I'm trying to do.

Upvotes: 3

Views: 2012

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109157

The only way to have real variables - as you already discovered - is to use execute block (which for all intents and purposes is an anonymous stored procedure), or - of course - a normal stored procedure.

As indicated in the answer by MartynA, you can also use a table to have variables, although I would suggest that you use a global temporary table for that, as that keeps the variables 'private' to the connection or transaction.

Yet another option (that I had completely forgotten about earlier) is to use the context variables using rdb$get_context and rdb$set_context, although that can become a bit cumbersome. For example to set a variable x you can use:

select rdb$set_context('USER_SESSION', 'x', 5) from rdb$database;

The return value of this is 0 if the variable was newly created and 1 if the variable already existed. Values are converted to a VARCHAR(255), so they need to be types that can be cast to VARCHAR(255). You can remove a variable by setting it to NULL. Instead of USER_SESSION you can also use USER_TRANSACTION.

The USER_SESSION context is bound to the current connection. Variables in USER_TRANSACTION only exist in the transaction in which they have been set. When the transaction ends, the context and all the variables defined in it are destroyed.

Obtaining a variable can be done with

select rdb$get_context('USER_SESSION', 'x') from rdb$database;

Which returns NULL if the variable does not exist, or otherwise a VARCHAR(255) with the value.

Upvotes: 1

MartynA
MartynA

Reputation: 30735

I've been struggling with exactly this problem for the last couple of days to try and finish my answer to this q: sql and fifo simple select; it took me about half an hour to write and test a TransactSQL way of doing what the OP had asked.

I would have gone the EXECUTE BLOCK route which has been suggested to you, but I kept running into DML things that apparently aren't allowed in these so I gave up on doing it the politically correct way.

So instead I did it in a way that might have Firebird purists aghast: instead of trying to declare variables, just create a temporary table containing columns corresponding to the variables you would have used, and an ID one to make it easy to extract values from it, and then store the variable's values in a single row in it. Something like:

create table variables(ID int, myID int, myStringVar charchar(80), [etc...])

Obviously you can initialise this table however you like and update the column values as you go along. Accessing a value in it is a bit more cumbersome than accessing a variable's value, but not by much, as in

update mytable set somecolumn = 666 where id = (select myID from variables where ID = 1)

I'm sure you get the idea. The only minor irritation I found with doing things this way, and tbh I'm not sure whether it's a Firebird thing or a FlameRobin one is that it seems necessary to wrap the creation of db objects like tables and views up in a transaction that gets committed before you attempt a block of statements that uses them.

Upvotes: 2

Related Questions