Connecting SQL Data Adapters
To generate reports, the report generator allows using data from various SQL sources. Since pure JavaScript doesn’t have built-in methods for working with remote databases, this functionality is implemented using server-side Python code.
Modifying connection parameters and data retrieval
Working with SQL data sources does not require any additional actions; all data adapters are already connected and configured. If it is necessary to process parameters used for connecting to the data, the onBeginProcessData event of the report object is provided. This event can be triggered on either the client- or server-side. The event arguments will contain all necessary parameters for connecting to the SQL data source, as well as the SQL query parameters. Detailed descriptions of the available argument values can be found in the Report Engine Events section.
All connection parameters to the data source can be modified both on the client- and server-sides. For example, you may need to change the SQL query on the client-side and the connection string on the server-side:
app.py |
from stimulsoft_reports.events import StiDataEventArgs from stimulsoft_reports.report import StiReport
def beginProcessData(args: StiDataEventArgs): if args.connection == 'MyConnectionName': args.connectionString = 'Server=localhost;Database=test;uid=root;password=******;'
report = StiReport() report.onBeginProcessData += beginProcessData report.onBeginProcessData += 'beginProcessData' report.loadFile(url_for('static', filename='reports/SimpleList.mrt')) report.render()
|
report.html |
<script> function beginProcessData(args) { if (args.dataSource == "MyDataSource") args.queryString = "SELECT * FROM ProductsTable"; } </script>
|
For each SQL data source, you can specify SQL query parameters, which are stored in the data source itself as a collection. This collection is also passed in the event arguments. It is an array of objects containing the parameter name, type, and value, for example:
app.py |
from stimulsoft_reports.events import StiDataEventArgs
def beginProcessData(args: StiDataEventArgs): args.parameters = [ { name: 'ParameterString', type: 752, typeName: 'Text', value: 'Text value' }, { name: 'ParameterInt', type: 3, typeName: 'Int32', value: 20 } ]
|
It is allowed to modify the values of query parameters, however, the type of the new value must match the type of the parameter being modified, for example:
app.py |
from stimulsoft_reports.events import StiDataEventArgs
def beginProcessData(args: StiDataEventArgs): if args.parameters != None: args.parameters['Parameter1'].value = 'TableName'
|
Information |
All SQL query parameter types will correspond to the available types of the database tables being connected to. More details about SQL query parameters are discussed in this section in the chapter Using Parameters in SQL Queries.
|
Thus, in the onBeginProcessData event, you can determine the database type, connection name, and data source name, as well as view and, if necessary, adjust the connection string and SQL query for data retrieval, and set the query parameter values. When modifying argument values on the server-side, the modified values will not be passed to the client-side, allowing the use of confidential data such as login and password in the connection string, table names, prefixes, etc.
To view or adjust the loaded data before connecting and generating the report, the onEndProcessData event of the report object is provided. The event arguments will include all necessary connection parameters to the SQL data source, as well as the query result, containing column names, column types, and data rows retrieved from the SQL source. A detailed description of the available argument values can be found in the Report Engine Events section.
The data object of the executed SQL query has the following structure:
app.py |
from stimulsoft_reports.events import StiDataEventArgs from stimulsoft_reports.report import StiReport
def endProcessData(args: StiDataEventArgs): args.result = { columns: ['id', 'username', 'phone'], types: ['int', 'string', 'string'], rows: [ [1, 'Mario Pontes', '555-6874'], [2, 'Helen Bennett', '555-2376'] ] }
report = StiReport() report.onEndProcessData += endProcessData report.onEndProcessData += 'endProcessData' report.loadFile(url_for('static', filename='reports/SimpleList.mrt')) report.render()
|
report.html |
<script> function endProcessData(args) { args.result = { columns: ["id", "username", "phone"], types: ["int", "string", "string"], rows: [ [1, "Mario Pontes", "555-6874"], [2, "Helen Bennett", "555-2376"] ] }; } </script>
|
The available properties of the data object are listed in the table:
Name |
Description |
columns |
Column names of the SQL data source table. |
types |
Column types of the SQL data source table, converted to known types for the report generator. |
rows |
Data rows from the SQL data source, represented as an array of arrays of all table rows. |
All data from the SQL query result can be adjusted, and in this case, after the event is completed, the report will be generated using the modified data.
Using parameters in an SQL query
If necessary, you can use parameters in an SQL query. To do this, add parameters to a special collection in the data source and set the required type and default value for each parameter. After that, the parameters can be used in the SQL query as follows:
SQL Data Source |
SELECT * FROM @Parameter1 WHERE UserID = @Parameter2
|
All parameter values will be sent to the server-side as a separate collection and can be reviewed and modified before executing the SQL query. To access the parameter values, use the args.parameters collection in the onBeginProcessData event, for example:
app.py |
from stimulsoft_reports.events import StiDataEventArgs
def beginProcessData(args: StiDataEventArgs): if args.dataSource == 'DataSourceWithParams': args.parameters['Parameter1'].value = 'TableName' args.parameters['Parameter2'].value = 10
|
Information |
New parameter values in this collection will not be sent to the client-side, so it is permissible on the Python server-side to assign confidential data as values.
|
Using report variables as SQL parameters
It is possible to use a variable as an SQL parameter. To do this, set the property Allow using as SQL parameter in the report variable editor, after which it can be used in any SQL query. The syntax will be exactly the same as when using parameters in the data source.
Information |
Such a variable will be included in the parameters collection only if it is used in the query. Parameters from the data source collection are always passed, even if they are not used in the query.
|
All parameter values will be automatically escaped to prevent SQL injections and ensure query execution security. If escaping is not required and you control the security of parameter values yourself, automatic escaping can be disabled. To do this, set the escapeQueryParameters property to False in the event handler:
app.py |
from stimulsoft_reports.report import StiReport
@app.route('/report', methods = ['GET', 'POST']) def report(): report = StiReport() report.handler.escapeQueryParameters = False if report.processRequest(request): return report.getFrameworkResponse()
|
After setting the specified property, using parameters becomes unsafe, and you must strictly control the values before executing SQL queries.
Information |
Escaping applies only to SQL query parameters and variables used as parameters. If a variable is used as an expression, i.e., within curly braces, such as {VariableName}, escaping will not be applied in any case. A detailed description of how variables work can be found in the Working with Report Variables section.
|