trunk/doc/readme.field%2Bparam.names

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:

a. 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.

b. 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.

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


Revision:	19
Committed:	Mon Jul 7 13:00:15 2014 UTC (9 years, 9 months ago) by tony
File size:	10528 byte(s)
Log Message:	Committing updates for Release R1-1-0
#	Content
1	IBX Field and Parameter Names
2	=============================
3
4	Column Names
5	------------
6
7	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.
8
9	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.
10
11	For Example:
12
13	Create Table MY_TABLE (
14	"KeyField" Integer,
15	"GRANT" VarChar(32),
16	"My Column" Float
17	);
18
19	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.
20
21	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.
22
23	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.
24
25	On the other hand, column alias Names aren't always unique anyway. For example, in the SQL:
26
27	select sum(col1), sum(col2) from MyTable;
28
29	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.
30
31	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.
32
33	For example, with a table defined as
34
35	Create Table MY_TABLE (
36	TableKey Integer not null,
37	"My Field" VarChar(32),
38	"MY Field" VarChar(32),
39	Primary Key(TableKey)
40	);
41
42	The column names used by IBX will be
43
44	TABLEKEY
45	MY_FIELD
46	MY_FIELD1
47
48	respectively.
49
50	IBQuery1.FieldByName('tableKey').AsInteger
51	IBQuery1.FieldByName('MY_FIELD').AsString
52	IBQuery1.FieldByName('my_field1').AsString
53
54	Are then all valid examples for accessing the column values using TIBQuery.FieldByName.
55
56	Parameter Names
57	---------------
58
59	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.
60
61	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
62
63	Select Col1, Col2 From MYTABLE Where Col3 = :ColValue;
64
65	Then, at run time, the parameter value can be set using
66
67	IBQuery1.ParamByName('COLVALUE').AsInteger := ...
68
69	or
70
71	IBQuery1.ParamByName('ColValue').AsInteger := ...
72
73	etc.
74
75	As with column names, parameter names are matched case insensitive.
76
77	Duplicate Parameter Names
78	-------------------------
79
80	Duplicate Parameter Names are supported by IBX and can be very useful. For example, an SQL Select Statement may be given as
81
82	Select Col1, Col2
83	From Table_A
84	Where Col3 = :arg1
85	UNION
86	Select Col4, Col5
87	From Table_B
88	Where Col6 = :arg1
89
90
91	In this case, "arg1" need only be set once. e.g.
92
93	IBQuery1.ParamByName('arg1').AsInteger := 3;
94
95	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.
96
97	OLD and NEW Parameters
98	----------------------
99
100	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:
101
102	UPDATE MYTABLE Set Key1 = :NEW_KEY1, COL2 = :COL2 Where Key1 = :OLD_KEY1;
103
104	to correctly handle database updates such as:
105
106	IBDataset1.Next;
107	IBDataset1.Edit;
108	IBDataset1.FieldByName('key1').AsInteger := <a new value>;
109	IBDataset1.Post;
110
111	Pseudo Dialect 3 Parameter Names (disabled by default)
112	--------------------------------
113
114	It is also possible to use a pseudo dialect 3 format parameter name e.g.
115
116	Select Col1, Col2 From MYTABLE Where Col3 = :"ColValue";
117
118	However, in this case, the double quotes are effectively ignored and the match is again made case insensitive. e.g.
119
120	IBQuery1.ParamByName('COLVALUE').AsInteger := ...
121
122	is a valid way to set the above parameter.
123
124
125	Enabling Support for Pseudo Dialect 3 Parameter Names
126	------------------------------------------------------
127
128	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.
129
130	For example, to support an SQL statement in the format:
131
132	SELECT col1 From MYTABLE Where KeyID = :"MyKeyid";
133
134	and set using:
135
136	IBQuery1.ParamByName('"MyKeyid"').AsInteger := ...
137
138	you must define ALLOWDIALECT3PARAMNAMES at compilation time.
139
140	On the other hand, regardless of ALLOWDIALECT3PARAMNAMES
141
142	IBQuery1.ParamByName('MYKEYID').AsInteger := ...
143
144	will always work, even when double quotes are used in the SQL. Hence the limited utility of the format.
145
146	Disabling Case Insensitive Parameter Names
147	------------------------------------------
148
149	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;
150
151	SELECT col1 From MYTABLE Where KeyID = :MyKeyid;
152
153	you can only set the input parameter with:
154
155	IBQuery1.ParamByName('MyKeyid').AsInteger := ...
156
157	i.e. the case of each character in the parameter name must match exactly.
158
159	Disabling Non-unique Parameter Name Support
160	-------------------------------------------
161
162	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.
163
164	Setting input parameter values by position
165	------------------------------------------
166
167	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:
168
169	INSERT into MyTable (col1, col2) Values(?,?);
170
171	and set the input parameters positionally e.g.
172
173	IBSQL1.Params[0].AsInteger := ...
174	IBSQL1.Params[1].AsString := ...
175
176	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.
177
178	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.
179
180	It is also possible to have mixed parameter naming e.g.
181
182	INSERT into MyTable (col1, col2) Values(:COL1,?);
183
184	You can then set the parameters using:
185
186	IBSQL1.ParamByName('col1').AsInteger := ...
187	IBSQL1.Params[1].AsString := ...
188
189	Note that the first DSQL format placeholder is index 1 as 'COL1' is index 0.
190
191	When you set TIBSQL.GenerateParamNames to true, each positional parameter is given a generated name in the format:
192
193	IBXParam<seqno>
194
195	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:
196
197	IBSQL1.ParamByName('IBXParam0').AsString := ...
198
199	SQL Generation
200	--------------
201
202	IBX generates Firebird compliant SQL for you:
203
204	- when the "Generate SQL" button is clicked in a property editor.
205	- when a TIBTable is opened
206	- when a TIBStoredProc is executed.
207
208	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.
209
210	In all cases, the SQL is generated from the following rules:
211
212	a. 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.
213
214	b. 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.
215
216	c. In update statements, parameter names in a "Where" clause are prefixed by "OLD_".
217
218
219
220
221
222
223
224
225