The language differs from SQLite in that it does not support any DDL-operations, and only the
read-only SELECT ... FROM
family from the DML syntax elements.
Control properties can also be referenced in queries
Queries in SQL are limited to reference table columns, but in the Mobilengine-brand data-binding, you can also query the properties of form controls.
Scalar properties can be referenced in SQL contexts that expect scalar values.
In the FROM
clause of a query statement, where the SQL
syntax expects tabular data, only table-value properties are
valid.
In the sample below, the table
control's recordset
query
references a table-value
variable.
<form...> <declarations> <let id="letTwee" shape="table" value='{SELECT q.more_words FROM randomWord q}'/> </declarations> ... <table label="A table 'recordset' that queries a table-value variable" recordset='{SELECT u.more_wordsFROM letTwee u
}' record="y"> <row> <cell> <textview text='{y.more_words}'/> </cell> </row> </table> ...
The tables queried in a statement need to be named explicitly using an alias
Any table-value expression that you insert inside the FROM
clause of a
query statement needs to be named explicitly using an alias. You need to use the appropriate
table alias when referencing the columns of a
reftable.
Every selected reference table column needs to be specified exlicitly using the
table-name
.
column-name
syntax.
Selected columns must be named
Whether the query statement references reference table columns, table expression columns, or control properties, each selected column must have an explicit name, that is, an alias, unless the name of the selected table column or property is unique within the query statement.
In the sample below, the two selected columns have unique names within the query statement, so they don't require a further alias.
Figure 212. If the names of the selected control properties or reference columns are unique, no alias is required
On the other hand, in the sample code below, the recordset
of the
table
control has a query statement that references two properties with
the same name (text
), and two columns with the same name
(sampleColumn
), so all four references need a column
alias.
... <textbox id="input1" text="tobacconist"/> <textbox id="input2" text="hovercraft"/> <table id="tableNwroo" recordset='{SELECT input1.text a, input2.text b, c.sampleColumn d, e.sampleColumn f FROM sillyTalk c, sillyTalk e WHERE c.sampleColumn != e.sampleColumn}
' record="g"> <row> <cell> <textview text='{g.a}'/> </cell> <cell> <textview text='{g.b}'/> </cell> <cell> <textview text='{g.d}'/> </cell> <cell> <textview text='{g.f}'/> </cell> </row> </table> ...
Figure 213. The table
control references two textbox
es, and
the same reference table column twice, and displays their joined values
Automatic column alias assignment
If you need to reference a single selected column that you did not explicitly name in the
data-binding expression, use the name v
, which is automatically assigned by
the parser if there is only one column returned by the SELECT
statement,
and it does not have an explicit or implicit
name.
Figure 214. You can reference the automatically named single-column return value of a
data-binding query using the name v
Note, however, that this is an edge case: when the SELECT
statement
returns more than one columns, columns must be named as usual, because automatic column
alias assignment only works with single
columns.
<!-- If the query returns more than one columns, every column needs to have a column alias <table recordset='{SELECT UPPER(a.sampleColumn), a.sampleColumn FROM sillyTalk a}' record="b"> <row> <cell> <textview text='{b.v}'/> </cell> <cell> <textview text='{b.sampleColumn}'/> </cell> </row> </table> -->