3

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.

Alex James
  • 696
  • 5
  • 13
  • You'll need to use execute block -- you can pass parameters. – nater Jan 25 '17 at 20:27
  • 1
    @nater: thanks but I'd rather stick needles in my eyes. execute block seems like an abomination to me. – Alex James Jan 25 '17 at 20:39
  • Are you sure parameters won't do? Your example doesn't illustrate that. – Thijs van Dien Jan 26 '17 at 00:07
  • @ThijsvanDien: Thanks, but how would I write my example to use a parameter for the number 2 in it, for example? – Alex James Jan 26 '17 at 09:51
  • @AlexJames depends of habits, what you are used to. To me it looks like those are variables living somewhere outside of statements, blocks, procedures and other SQL standard entities, that are abomination. Depends on personal point of view. – Arioch 'The Jan 26 '17 at 11:33
  • @ThijsvanDien AFAIR parameters exist for single statements, not for the scripts. Thus to apply those parameters to a script he has to wrap it into EXECUTE BLOCK and we are back at square one – Arioch 'The Jan 26 '17 at 11:36
  • @Arioch'The: Thanks, but as for "variables living somewhere outside of statements, blocks, procedures" I was only looking at a mechanism to pass values between invocations of Sql statements, something any useful scripting language ought to be able to do without recourse to, and the limitations of, what can be done in EXECUTE BLOCK. – Alex James Jan 26 '17 at 16:37
  • There aren't really any limitations to what an `EXECUTE BLOCK` can do; with `EXECUTE STATEMENT` you can run any DML you want and pass variables from PSQL as parameters and/or read the result(s) back into variables. – Thijs van Dien Jan 26 '17 at 23:50
  • @ThijsvanDien not exactly. Like I have an upgrade script, that makes some interim calculations and then finally it might have data that it can not decide automatically and has to pass the decision to user, so the script should check if there still is data in temporary tables and if there is not (all processed) then drop those tables. IOW I have to conditionally use DDL statements. So in my script I have to put EXECUTE BLOCK to dive into PSQL (if-then-else) realm and within that block I have to call EXECUTE STATEMENT (dive into "interactive" DML-or-DDL SQL realm). Smewht "indirect" approach :-D – Arioch 'The Jan 27 '17 at 08:35
  • @AlexJames "something any useful scripting language ought to be able to do" - you nailed it. You ask for a scripting language. JavaScript or Lua or Python or Rexx or some other scripting language. But SQL is not - it is data-oriented set theory language :-D I understand your wish to KISS and DRY and in part I share it, I am the one of those who would like to see DB-level constants implemented in Firebird DDL some day for example. Still I would not call one abomination to ETL language like SQL DML any better than other. Vars and blocks - they're both ugly hacks outside ETL essence of SQL DML – Arioch 'The Jan 27 '17 at 08:40

2 Answers2

2

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.

Community
  • 1
  • 1
MartynA
  • 30,454
  • 4
  • 32
  • 73
  • Yes, it was your answer to that one that set me thinking about this. Anyway, thanks, I can stop fretting about it now. – Alex James Jan 27 '17 at 15:38
  • Firebird has explicit temporary tables whose contents are transaction or connection specific. As to the transactions: DDL in Firebird is transactional: you need to specify the transaction boundary yourself (although some tools have an "auto-commit DDL" setting), but objects created in a transaction cannot be used in DML in that same transaction. – Mark Rotteveel Jan 27 '17 at 19:09
  • BTW: great suggestion to use temporary tables, in some cases you could also consider the context functions: https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-functions-scalarfuncs.html#fblangref25-functions-workcontext – Mark Rotteveel Jan 27 '17 at 19:14
1

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197