Queries in OpenCReports are SQL queries for database connections, or data files files in certain formats. The queries are declared like this:
<Queries>
<Query name="..." ... />
...
</Queries>
SQL queries for MariaDB, PostgreSQL and ODBC
datasources may be declared two ways, either
as the XML value for <Query>:
<Query
name="myquery"
datasource="mysource">
SELECT * FROM some_table
</Query>
or as the value attribute:
<Query
name="myquery"
datasource="mysource"
value="SELECT * FROM some_table" />
Note, that the XML attribute datasource="..."
must match a previously declared datasource.
The SQL query can be any SELECT
statement.
Queries for CSV, XML, JSON and spreadsheet datasources
may be declared two ways. Either as the XML value for
<Query>:
<Query name="myquery" datasource="mysource" >xmldata.xml</Query>
or as the value attribute:
<Query
name="myquery"
datasource="mysource"
value="'xmldata.xml'" />
Example query for a spreadsheet:
<Query
name="mysheet"
datasource="mysource"
value="'Sheet1'" />
Notes:
The XML attribute datasource="..."
must match a previously declared datasource.
It is recommended that the value="..."
form is used, since it's not ensured that
whitespace before or after the file name
is trimmed in the first variant if the XML
is "beautified". The file name that the
OpenCReports library receives must be correct
in order to use it.
For CSV, XML and JSON files, the value
in the query declaration is the file name.
This file must be in the correct format for the
datasource type.
For spreadsheets, the value
in the query declaration is the sheet label.
The optional type declaration for columns
in the XML and JSON file formats, or for CSV,
the complete lack of it can be supplemented
with a memory array using the optional
coltypes="..." and
cols="..." attributes.
For details, see the Array queries.
Queries for array datasources may be declared two ways.
Either as the XML value for
<Query>:
<Query
name="myquery"
datasource="mysource"
coltypes="'coltypes'"
rows="30"
cols="6"
>array</Query>
or as the value attribute:
<Query
name="myquery"
datasource="mysource"
value="'array'"
coltypes="'coltypes'"
rows="30"
cols="6" />
Notes:
The XML attribute datasource="..."
must match a previously declared datasource.
It is recommended that the value="..."
form is used, since it's not ensured that
whitespace before or after the symbol name
is trimmed in the first variant if the XML
is "beautified". The symbol name that the
OpenCReports library receives must be correct
in order to use it. The array name must match
the correct global symbol name. The library
discovers this symbol using the
Array discovery function, by
default via dlsym().
Similarly to the array symbol name, the
coltypes="..." array name
must match the correct global symbol name.
The library discovers this symbol using the
Array discovery function, by
default via dlsym().
The value for cols
must match the second dimension of the
data array. It may be omitted if the
Array discovery function is smarter
than the default implementation and returns
the arrays' dimensions.
The value for rows
must match the number of data rows in the array,
excluding the title row. I.e. it must be one less
than the first dimension of the array. It may be
omitted if the
Array discovery function is smarter
than the default implementation and returns the
arrays' dimensions.
Failing to fulfill the above may cause crashes or wrong data to be used in the report.
A regular follower query is declared by adding
the follower_for="..." attribute.
The value for follower_for="..."
is the name of a previously
declared query. Example:
<Query name="myquery1" datasource="mysource1" value="'SELECT * FROM table1'" /> <Query name="myquery2" datasource="mysource2" value="'SELECT * FROM table1'" follower_for="myquery1" />
In this example, two queries of two different datasources are used. This is one of the advantages of using follower queries, i.e. data from different databases may be used. Nowadays, with foreign queries implemented in e.g. PostgreSQL, its use case is more limited.
See Section 2.2.4.1.2 for explanation.
The follower matching expression is specified
with the follower_expr="..."
attribute. Example:
<Query name="myquery1" datasource="mysource1" value="'SELECT * FROM table1'" /> <Query name="myquery2" datasource="mysource2" value="'SELECT * FROM table1'" follower_for="myquery1" follower_expr="myquery1.id = myquery2.id" />