USING statement

[asfernandes]

This feature was discussed in fb-devel in thread "New statement: EXECUTE SQL" in 2022.

---

The USING Statement

Overview

The USING statement is a new DSQL extension designed to bridge the gap between standard DSQL statements and the
powerful but verbose EXECUTE BLOCK.

When adapting a standard DSQL command to use EXECUTE BLOCK (for instance, to utilize sub-routines or reuse a single
input parameter in multiple places), the developer is currently forced to explicitly declare all input parameters and,
more tediously, all output fields.

The USING statement simplifies this workflow. It provides the ability to declare parameters and sub-routines while
allowing the engine to infer outputs automatically from the contained SQL command.

Syntax

USING [ ( <input_parameter_list> ) ]
    [ <subroutines> ]
DO <sql_command>

Note: At least one of <input_parameter_list> or <subroutines> must be present. A USING ... DO statement
without parameters and without subroutines is invalid.

Components

• <input_parameter_list>: A strictly typed list of parameters. These can be bound to values using the ?
  placeholder.
• <subroutines>: Standard PSQL function or procedure declarations.
• <sql_command>: The DSQL statement to execute. Supported statements include:
  • SELECT
  • INSERT (with or without RETURNING)
  • UPDATE (with or without RETURNING)
  • UPDATE OR INSERT (with or without RETURNING)
  • DELETE (with or without RETURNING)
  • MERGE (with or without RETURNING)
  • CALL
  • EXECUTE PROCEDURE

Key Features

1. Inferred Outputs: Unlike EXECUTE BLOCK, you do not need to explicitly declare a RETURNS (...) clause. The
   output columns are automatically inferred from the <sql_command> in the DO clause.
2. Statement Type Transparency: The API returns the statement type of the inner <sql_command> (e.g., if the
   inner command is a SELECT, the client sees a SELECT statement).
3. Parameter Reuse: Input parameters declared in the USING clause can be used multiple times within the script
   using named references (e.g., :p1), while only requiring a single bind from the client application.
4. Mixed Parameter Binding: You can mix declared parameters (bound via ? in the declaration) and direct
   positional parameters (using ? inside the DO command).

Examples

1. Basic Parameter Reuse and Subroutines

This example demonstrates declaring typed parameters, defining local functions/procedures, and using them in a query.

using (p1 integer = ?, p2 integer = ?)
    -- Declare a local function
    declare function subfunc (i1 integer) returns integer
    as
    begin
        return i1;
    end

    -- Declare a local procedure
    declare procedure subproc (i1 integer) returns (o1 integer)
    as
    begin
        o1 = i1;
        suspend;
    end
do
-- The main query
select subfunc(:p1) + o1
    from subproc(:p2 + ?)

In this scenario:

1. The client binds values to p1 and p2.
2. The client binds a third value to the ? inside the DO clause.
3. The result set structure is inferred from the SELECT statement.

2. Simplifying Parameter Reuse

Without USING, inserting the same bind value into multiple columns requires sending the data twice.

Standard DSQL:

insert into generic_table (col_a, col_b) values (?, ?);
-- Client must bind: [100, 100]

With USING:

using (val integer = ?)
do insert into generic_table (col_a, col_b) values (:val, :val);
-- Client binds: [100]

Comparison

Feature	Standard DSQL	EXECUTE BLOCK	USING
Subroutines	No	Yes	Yes
Input Declarations	Implicit (Positional)	Explicit	Hybrid (implicit and explicit)
Output Declarations	Inferred	Explicit (RETURNS)	Inferred
Verbosity	Low	High	Medium
Use Case	Simple queries	Complex logic, loops, no result set inference	Reusing params, subroutines, standard queries

[sim1984]

Let me ask you right away: is this supported in EXECUTE STATEMENT for named and/or positional parameters? I didn't notice any changes to the internal parser in the extds subsystem in the code.

[aafemt]

IMHO, named parameters in DSQL would be simpler to implement and use while providing the same functionality.

Compare

using (val integer = ?)
do insert into generic_table (col_a, col_b) values (:val, :val);

with

insert into generic_table (col_a, col_b) values (:val, :val);

and if you venture into non-standard territory, there is no advantage of

using (p1 integer = ?, p2 integer = ?)
    -- Declare a local function
    declare function subfunc (i1 integer) returns integer
    as
    begin
        return i1;
    end

    -- Declare a local procedure
    declare procedure subproc (i1 integer) returns (o1 integer)
    as
    begin
        o1 = i1;
        suspend;
    end
do
-- The main query
select subfunc(:p1) + o1
    from subproc(:p2 + ?)

over

-- Declare a local function
declare function subfunc (i1 integer) returns integer
  as
    begin
        return i1;
    end

-- Declare a local procedure
declare procedure subproc (i1 integer) returns (o1 integer)
  as
    begin
        o1 = i1;
        suspend;
    end

-- The main query
select subfunc(:p1) + o1
    from subproc(:p2 + ?)

[sim1984]

Error determining size of CHAR/VARCHAR column obtained from declare function.

using
  declare function f()
  returns varchar(1)
  as
  begin
    return '1';
  end
do
select
  f()
from rdb$database;

INPUT message field count: 0

OUTPUT message field count: 1
01: sqltype: 448 VARYING Nullable scale: 0 subtype: 0 len: 32765 charset: 0 SYSTEM.NONE
  :  name: F  alias: F
  : table:   schema:   owner:

F    

using
  declare function f()
  returns char(1)
  as
  begin
    return '1';
  end
do
select
  f()
from rdb$database;

INPUT message field count: 0

OUTPUT message field count: 1
01: sqltype: 452 TEXT Nullable scale: 0 subtype: 0 len: 0 charset: 0 SYSTEM.NONE
  :  name: F  alias: F
  : table:   schema:   owner:

F
======
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 0, actual 1

For BLOB, the subtype is not stored.

using
  declare function f()
  returns blob sub_type text
  as
  begin
    return '1';
  end
do
select
  f()
from rdb$database;

INPUT message field count: 0

OUTPUT message field count: 1
01: sqltype: 520 BLOB Nullable scale: 0 subtype: 0 len: 8
  :  name: F  alias: F
  : table:   schema:   owner:

                F
=================
              0:1
==============================================================================
F:
BLOB display set to subtype 1. This BLOB: subtype = 0
==============================================================================

[asfernandes]

Error determining size of CHAR/VARCHAR column obtained from declare function.

For BLOB, the subtype is not stored.

Fixed, thanks.

[sim1984]

Let me ask you right away: is this supported in EXECUTE STATEMENT for named and/or positional parameters? I didn't notice any changes to the internal parser in the extds subsystem in the code.

execute block
returns (j bigint, s varchar(20))
as
begin
  for
    execute statement (q'{
      using(i int = ?, s varchar(20) = ?)
      do
        select :i, :s from rdb$database
      }'
    ) (1, '123')
    into j, s
  do
    suspend;
end

                    J S
===================== ====================
                    1 123

Good
But named parameters don't work.

Case 1:

execute block
returns (j bigint, s varchar(20))
as
begin
  for
    execute statement (q'{
      using(i int = ?, s varchar(20) = ?)
      do
        select :i, :s from rdb$database
      }'
    ) (i := 1, s := '123')
    into j, s
  do
    suspend;
end

                    J S
===================== ====================
Statement failed, SQLSTATE = 42000
Input parameter 'I' is not used in SQL query text
-At block line: 5, col: 3

case 2:

execute block
returns (j bigint, s varchar(20))
as
begin
  for
    execute statement (q'{
      using(i int = :i, s varchar(20) = :s)
      do
        select :i, :s from rdb$database
      }'
    ) (i := 1, s := '123')
    into j, s
  do
    suspend;
end

                    J S
===================== ====================
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 2, column 21
-:
-At block line: 5, col: 3

But standards DSQL work.

execute block
returns (j bigint, s varchar(20))
as
begin
  for
    execute statement (q'{
      select
        cast(:i as int),
        cast(:s as varchar(20))
      from rdb$database
    }'
    ) (i := 1, s := '123')
    into j, s
  do
    suspend;
end

                    J S
===================== ====================
                    1 123

IMHO. I think option 1 needs to be made workable. I can't see how option 2 can be made workable, given that the parameter marker can be in both the using clause and within DSQL. That is, parameter names for ES could very well be taken from the using declaration and then ignored.

And by the way, don't forget that the CALL operator within EXECUTE STATEMENT isn't ideal either. The same logic can be applied there: if the procedure is called with named arguments, take their names as parameters; in other cases, only positional arguments can be used.

For other DSQL statements, keep the current logic for backward compatibility.

[asfernandes]

I committed a fix for EXECUTE STATEMENT.
But it will continue to use passAsIs, so param = :name should not be used.
Syntax contine to be param = ?.
I and think = ? should be made optional, both for USING and EXECUTE BLOCK.

[sim1984]

I and think = ? should be made optional, both for USING and EXECUTE BLOCK.

What does this mean? Here's the syntax:

      using(i int, s varchar(20))
      do
        select :i, :s from rdb$database

I don't think this is a good idea.

[sim1984]

Your fix corrects named parameters for USING statements. However, the CALL statement still doesn't work. I understand this isn't directly related to the PR, but the solution should be similar. I suspect the following is the cause:

SET TERM ^;

CREATE PROCEDURE SP_TEST(I INT)
RETURNS (J INT)
AS
BEGIN
  J = I;
END^

SET TERM ;^

SET SQLDA_DISPLAY ON;

CALL SP_TEST(I => ?, J => ?)

INPUT message field count: 1
01: sqltype: 496 LONG Nullable scale: 0 subtype: 0 len: 4
  :  name:   alias:
  : table:   schema:   owner:

OUTPUT message field count: 1
01: sqltype: 496 LONG Nullable scale: 0 subtype: 0 len: 4
  :  name: J  alias: J
  : table: SP_TEST  schema: PUBLIC  owner: SYSDBA
Statement failed, SQLSTATE = 07002
Dynamic SQL Error
-SQLDA error
-No SQLDA for input values provided

No name/alias defined for input parameters.

See #7825

[sim1984]

I'm currently satisfied with the USING statement. The only thing missing is documentation on using named parameters in EXECUTE STATEMENT.