mwasoftware.co.uk

IBX Field and Parameter Names

Column Names

Firebird identifiers are typically case insensitive and are converted to upper case when processed and reported. This translates into IBX always reporting upper case column names and matching column names to field names (e.g. in TDataSet.FieldByName) using a case insensitive match.

However, in SQL Dialect 3, Firebird introduced the ability to enclose identifiers in double qoutes. This is necessary if, for example, you want a column name that is the same as an SQL Reserved word. It also allows you to have case sensitive column names, or column names containing spaces.

For Example:

Create Table MY_TABLE (

"KeyField" Integer,

"GRANT" VarChar(32),

"My Column" Float

);

IBX could readily handle case sensitive column names and isn't bothered by SQL reserved words. However, it does have the problem that the Lazarus TDataSet model includes the ability to automatically generate TField properties and which are then added to the Form's list of properties. The name of the generated property is formed by concatenating the IBX object name with the column alias name.

Pascal identifiers are also case insensitive and this could cause problems if two column names differ only in the case of their letters: the generated property names will cause a compilation error. Neither can Pascal identifiers contain spaces.

IBX handles this by forcing all column names to upper case, regardless of how they are defined in SQL. It also replaces spaces by underscores. The identifiers given to Generated column properties are then both valid Pascal and unambiguous. However, it is still necessary to handle cases where two column names differ only in their case - forcing the column names to upper case will only result in a name clash.

On the other hand, column alias Names aren't always unique anyway. For example, in the SQL:

select sum(col1), sum(col2) from MyTable;

Firebird will generate the alias name "SUM" for both cases. It will also allow you to specify the same aliasname multiple times in the same statement.

IBX handles this by checking for non-unique aliasnames when the SQL is prepared and disambiguating the column names by adding a numerical suffix (starting from one) to each non-unique column name it finds after the first one. The same approach is used when non-unique column names result after forcing the column name to upper case.

For example, with a table defined as

Create Table MY_TABLE (

TableKey Integer not null,

"My Field" VarChar(32),

"MY Field" VarChar(32),

Primary Key(TableKey)

);

The column names used by IBX will be

TABLEKEY

MY_FIELD

MY_FIELD1

respectively.

IBQuery1.FieldByName('tableKey').AsInteger

IBQuery1.FieldByName('MY_FIELD').AsString

IBQuery1.FieldByName('my_field1').AsString

Are then all valid examples for accessing the column values using TIBQuery.FieldByName.

Parameter Names

IBX supports both DSQL '?' and PSQL name style (e.g. :Param) parameters. In the latter case, these are case insensitive and can also be in a pseudo dialect 3 quoted format (e.g. :"MyParam"). Name based parameters need not be unique and when a non-unique parameter name is given in a call to ParamByName, each and every parameter with that name is set to the given value.

SQL Parameters defined using PSQL style names can then be set at run time using the ParamByName call. For example, if the select statement for a TIBQuery is

Select Col1, Col2 From MYTABLE Where Col3 = :ColValue;

Then, at run time, the parameter value can be set using

IBQuery1.ParamByName('COLVALUE').AsInteger := ...

or

IBQuery1.ParamByName('ColValue').AsInteger := ...

etc.

As with column names, parameter names are matched case insensitive.

Duplicate Parameter Names

Duplicate Parameter Names are supported by IBX and can be very useful. For example, an SQL Select Statement may be given as

Select Col1, Col2

From Table_A

Where Col3 = :arg1

UNION

Select Col4, Col5

From Table_B

Where Col6 = :arg1

In this case, "arg1" need only be set once. e.g.

IBQuery1.ParamByName('arg1').AsInteger := 3;

Both cases will be set to 3. This capability is particularly useful when using Master/Detail datasets linked by the Detail's DataSource property and the Detail is given by a query similar to the above.

OLD and NEW Parameters

These are typically used in Modify SQL statements (UPDATE) when a (e.g.) the primary key value is changed. In this case, the row to be updated needs to be selected by the original value of the primary key, while the column needs to be set to the new value. In order to support this capability, IBX allows parameter names to be prefixed by "OLD_" and "NEW_" where the former references the fields value when it was read from the dataset (i.e. before a call to TDataset.Edit), while the latter is the default and refers to the modified value set after a call to TDataset.Edit. For example, specify an Update SQL statement in the form:

UPDATE MYTABLE Set Key1 = :NEW_KEY1, COL2 = :COL2 Where Key1 = :OLD_KEY1;

to correctly handle database updates such as:

IBDataset1.Next;

IBDataset1.Edit;

IBDataset1.FieldByName('key1').AsInteger := <a new value>;

IBDataset1.Post;

Pseudo Dialect 3 Parameter Names (disabled by default)

It is also possible to use a pseudo dialect 3 format parameter name e.g.

Select Col1, Col2 From MYTABLE Where Col3 = :"ColValue";

However, in this case, the double quotes are effectively ignored and the match is again made case insensitive. e.g.

IBQuery1.ParamByName('COLVALUE').AsInteger := ...

is a valid way to set the above parameter.

Enabling Support for Pseudo Dialect 3 Parameter Names

Pseudo Dialect 3 quoted format parameter names are not believed to be very useful - but they do result in a significant client side processing overhead. The code to support these is now subject to conditional compilation and can hence be removed when not required. By default, pseudo dialect 3 format parameter names are disabled. To enable support, ALLOWDIALECT3PARAMNAMES should be defined when IBX is compiled. IBSQL.pas may be edited to make this change - see comments at this top of that file.

For example, to support an SQL statement in the format:

SELECT col1 From MYTABLE Where KeyID = :"MyKeyid";

and set using:

IBQuery1.ParamByName('"MyKeyid"').AsInteger := ...

you must define ALLOWDIALECT3PARAMNAMES at compilation time.

On the other hand, regardless of ALLOWDIALECT3PARAMNAMES

IBQuery1.ParamByName('MYKEYID').AsInteger := ...

will always work, even when double quotes are used in the SQL. Hence the limited utility of the format.

Disabling Case Insensitive Parameter Names

Even when pseudo dialect 3 format parameter names are not supported, IBX still processes parameter names case insensitive. This does result in some additional overhead due to calls to "AnsiUpperCase". This can be avoided by undefining "UseCaseSensitiveParamName" - again in IBSQL.pas. In which case, with;

SELECT col1 From MYTABLE Where KeyID = :MyKeyid;

you can only set the input parameter with:

IBQuery1.ParamByName('MyKeyid').AsInteger := ...

i.e. the case of each character in the parameter name must match exactly.

Disabling Non-unique Parameter Name Support

Non-unique parameter name support is generally very useful, However, it does impose an overhead each time a parameter is set as IBX has to pass through all the statement's parameters and check their names. When you know that all parameter names are unique and are using TIBSQL, this can be avoided by setting the TIBSQL.UniqueParamNames property to true at design time. This is a hint rather than a constraint and if set to true, IBX will simply not bother to check for more than one parameter with the same name - even if there is one.

Setting input parameter values by position

Real speed freaks will probably want to use DSQL placeholders as these avoid all the overhead of name based lookups. To do this, you would will probably be using TIBSQL and have an SQL Statement in the form:

INSERT into MyTable (col1, col2) Values(?,?);

and set the input parameters positionally e.g.

IBSQL1.Params[0].AsInteger := ...

IBSQL1.Params[1].AsString := ...

Remember to set the "GenerateParamNames" property to true when doing this otherwise IBX will complain. In 1.1.0 onwards this is a published property.

Prior to 1.1.0, IBX would still check for other parameters with the same name(!) even when they were set positionally. This behaviour has been removed making this the fastest mode of use.

It is also possible to have mixed parameter naming e.g.

INSERT into MyTable (col1, col2) Values(:COL1,?);

You can then set the parameters using:

IBSQL1.ParamByName('col1').AsInteger := ...

IBSQL1.Params[1].AsString := ...

Note that the first DSQL format placeholder is index 1 as 'COL1' is index 0.

When you set TIBSQL.GenerateParamNames to true, each positional parameter is given a generated name in the format:

IBXParam<seqno>

where the <seqno> is incremented for each positional parameter, These are numbered from zero and named parameters are ignored when generating the sequence number. Thus in the above example, you could have set the positional parameter using:

IBSQL1.ParamByName('IBXParam0').AsString := ...

SQL Generation

IBX generates Firebird compliant SQL for you:

  • when the "Generate SQL" button is clicked in a property editor.

  • when a TIBTable is opened

  • when a TIBStoredProc is executed.

The SQL generated for TIBTable and TIBStoredProc is not normally visible and the purpose of these objects is to hide the bother of SQL editing from users that are unfamiliar with SQL.

In all cases, the SQL is generated from the following rules:

  1. Column Names are as specified in the Database Schema and, for Dialect 3 schemas, double quotes are used to delimit column names that contain spaces or are mixed or lower case or contain double quotes.

  2. Parameter Names are always uppercase versions of the column name, if necessary disambiguated by adding a sequence number (starting from zero) to the end of the name. Any spaces or special characters are replaced by an underscore.

  3. In update statements, parameter names in a "Where" clause are prefixed by "OLD_".