Samples

 

You can find a full code on the GitHub.

 

 

 

To render reports the report generator allows you to use data from various SQL sources. So as pure JavaScript doesn't have embedded methods to work with remote database, this functionality is implemented using a server PHP code. Special events of work with data on the side of JavaScript client and PHP server are intended for this.

 

 

Loading data event

To work with SQL data source you should define the onBeginProcessData event for a report object or the viewer or the designer component. The event will be invoked when designing a report as soon as you need to connect to used SQL data sources. In arguments of the event all necessary parameters of connection to a SQL data source and SQL parameters of a request too. See the PHP Event Handler section for a detailed description of how events work.

 

index.php

 

<?php

$report = new \Stimulsoft\Report\StiReport();

$report->onBeginProceddData = true;

$report->renderHtml;

?>

 

 

 

If it is necessary to process the parameters passed to the server side before passing them, you may define a JavaScript function for the event:

 

index.php

 

<?php

$report = new \Stimulsoft\Report\StiReport();

$report->onBeginProceddData = 'onBeginProcessData';

$report->renderHtml;

?>

 

function onBeginProcessData(args) {

 

}

 

 

 

The event arguments will contain all necessary parameters for connecting to the SQL source, as well as SQL query parameters.

 

index.php

 

<?php

$report = new \Stimulsoft\Report\StiReport();

$report->onBeginProcessData = 'onBeginProcessData';

$report->renderHtml();

?>

 

function onBeginProcessData(args) {

var connectionString = args.connectionString;

var queryString = args.queryString;

}

 

 

 

You can find a detailed description of available values of arguments in the Engine events chapter.

 

All parameters of connection to a data source can be changed. For example, you need to set a new connection string for specified connection and set a new SQL query for a specified data source.

 

index.php

 

<?php

$report = new \Stimulsoft\Report\StiReport();

$report->onBeginProcessData = 'onBeginProcessData';

$report->renderHtml();

?>

 

function onBeginProcessData(args) {

// Change the connection string

if (args.connection == "MyConnectionName")

args.connectionString = "Server=localhost;Database=test;uid=root;password=******;";

 

// Change the SQL query

if (args.dataSource == "MyDataSource")

args.queryString = "SELECT * FROM MyTable";

}

 

 

 

You can set parameters of an SQL query for each SQL data source, which saved in the data source as collection. This collection is also transferred in arguments of the event. It is an array from the objects, which contain parameters name, its type and value.

 

index.php

 

args.parameters = [

{

name: "ParameterString",

type: 752,

typeName: "Text",

value: "Text value"

},

{

name: "ParameterInt",

type: 3,

typeName: "Int32",

value: 20

}

];

 

 

 

You can change values of a request parameters and the type of a new value should correspond to the type of a changed parameter.

 

index.php

 

<?php

$report = new \Stimulsoft\Report\StiReport();

$report->onBeginProcessData = 'onBeginProcessData';

$report->renderHtml();

?>

 

function onBeginProcessData(args) {

// Change the SQL query parameters with the required values

if (args.parameters)

args.parameters["Parameter1"] = "TableName";

}

 

 

Information

 

All types of an SQL query will correspond to available types of database tables, to which connection is made. You can find more detailed information about an SQL query parameters in the Using parameters in an SQL query chapter.

 

 

 

To change the connection parameters on the PHP server side, you should additionally define the onBeginProcessData event of the same name in the event handler on the server side and assign a new value to a specific parameter. All other actions will be performed automatically. For example, for the specified connection, set a new connection string, and for the specified data source, set a new SQL query:

 

handler.php

 

$handler->onBeginProcessData = function ($args) {

// Change the connection string

if ($args->connection == "MyConnectionName")

$args->connectionString = "Server=localhost;Database=test;uid=root;password=******;";

 

// Change the SQL query

if ($args->dataSource == "MyDataSource")

$args->queryString = "SELECT * FROM MyTable";

 

return \Stimulsoft\StiResult::success();

};

 

 

 

This way in the onBeginProcessData event, you can find the type of database, connection name, and data source name. In addition, you can find and if needed correct the connection string and a SQL query for getting data. The changed values will not be transferred to the client-side, so in this event you can use such confidential data on the PHP server-side of as login, password in connection string, names, and prefixes of tables etc.

 

In addition, in this event you can set parameter values of a SQL query. You can find more detailed information about a SQL query in this section in the Using parameters in a SQL query chapter.

 

 

Connection data event

To view or correct loaded data before connect them and render a report you should define the onEndProcessData event for a report object or the viewer or the designer component.

 

index.php

 

<?php

$report-> = new \Stimulsoft\Report\StiReport();

$report->onEndProcessData = 'onEndProcessData';

$report->renderHtml();

?>

 

function onEndProcessData(args) {

var result = args.result;

}

 

 

 

In arguments of the event all necessary parameters of connection to a SQL data source will be transferred and the result of a request execution, which contains tables, types and data strings, received from the SQL source. You can find a detailed description of available values of arguments in the Engine Events chapter.

 

An object of data of an executed SQL query has the following structure.

 

index.php

 

args.result = {

count: 3,

columns: ["id", "username", "phone"],

types: ["int", "string", "string"],

rows: [

length: 2,

["1", "Mario Pontes", "555-6874"],

["2", "Helen Bennett", "555-2376"]

]

}

 

 

 

Available properties of the data object are specified in this table.

 

Name

Description

count

The total number of columns of a SQL data source table.

columns

The names of columns of a SQL data source table.

types

The types of columns of a SQL data source table, cast to known types for the report generator.

rows

Data strings of a SQL data source are an array from all table rows.

 

 

All data of the result of a SQL query execution can be corrected, in this case after the event completed, a report will be designed with changed data already.

 

To view or correct loaded data on the PHP server-side before sending them to the side of JavaScript client you should define the onEndProcessData event in the event handler file.

 

handler.php

 

$handler->onEndProcessData = function ($args) {

$result = $args->result;

 

return \Stimulsoft\StiResult::success();

};

 

 

 

In arguments of the event all necessary parameters of connection to a SQL data source will be transferred and the result of a request execution, which contains tables, types and data strings, received from a SQL source. You can find a detailed description of available values of arguments in the Engine events chapter.

 

An object of data of an executed SQL query has the following structure.

 

handler.php

 

$args->result->count = 3;

$args->result->columns = array("id", "username", "phone");

$args->result->types = array("int", "string", "string");

$args->result->rows = array(

array("1", "Mario Pontes", "555-6874"),

array("2", "Helen Bennett", "555-2376")

);

 

 

 

Available properties of the data object are specified in the table.

 

Name

Description

count

The total number of columns of a SQL data source table.

columns

The names of columns of a SQL data source table.

types

The types of columns of a SQL data source, cast to known types for the report generator.

rows

Data rows of a SQL data source are an array from arrays of all table arrays.

 

 

All data of the result of a SQL query execution can be corrected, in this case after the event completed, changed data will be transferred to the client-side and used to design a report.

 

 

Using parameters in an SQL query

If needed, you can use parameters in a SQL query. To do it in a data source you should add parameters to a special collection and set an appropriate type and value for each parameter by default. After this, you can use parameters in a SQL query by the following way.

 

SQL Data Source

 

SELECT * FROM @Parameter1 WHERE UserID = @Parameter2

 

 

 

All values of the parameters will be transferred to the PHP server-side as a separate collection and they can be checked and changed before a SQL query completed. To get access to values of parameters, you should use the $args->parameters in the onBeginProcessData event, for example.

 

handler.php

 

$args->parameters['Parameter1']->value = 'TableName';

$args->parameters['Parameter2']->value = 10;

 

 

Information

 

New values of parameters in this collection will not transferred to the client-side, so you can assign confidential data as values on the PHP server-side.

 

 

 

If several data source are used in a report, you should check it before assign parameters, otherwise, an error of PHP script will occur, if one of the parameters will be absent in a current data source. For example, two data source are created, both of them has one common parameter and one parameter only in the second data source.

 

handler.php

 

$args->parameters['Parameter1']->value = 'TableName';

 

if ($args->dataSource == 'DataSource2')

$args->parameters['Parameter2']->value = 10;

 

 

 

Using variable as SQL parameter

There is an ability, which allows you to use a variable as SQL parameter. To do it you should set the Allow using as SQL parameter property in the editor of report variable. After it can be used in any SQL query. The syntax will be the same as when using parameters in data source.

 

Information

 

This variable will be transferred to the collection of parameters only if it is used in a request. The parameters from data source collection are always transferred, even if they are not used in a query.

 

 

 

Escaping parameter values

All values of parameters will be automatically escaped to exclude SQL injections and compromise the security of query execution. If you don't need to escape and you control the security of safety of parameter values, automatic escaping can be disabled. To do this, set the $escapeQueryParameters property to false in the event handler:

 

index.php

 

<?php

$handler = new \Stimulsoft\StiHandler();

$handler->escapeQueryParameters = false;

$handler->renderHtml();

?>

 

 

 

After you set the specified property, the use of parameters is unsafe. You need to strictly control the values before executing SQL queries.

 

Information

 

Escaping is applied only for parameters of SQL queries, and for the variables used as parameters. If a variable is used as an expression i.e in braces, for example {VariableName}, escaping will not be applied in any case. You can find a detailed description of variable work in the Work with report variables chapter.