This example shows how to use the SQL data sources with parameters in the report. First, you need to add the Stimulsoft libraries and scripts, required for the component to work. All code should be added in the <head> block of the HTML page:
<?php
require_once 'vendor/autoload.php';
?>

...

<?php
$js = new \Stimulsoft\StiJavaScript(\Stimulsoft\StiComponentType::Viewer);
$js->renderHtml();
?>

Next, in the <script> block, create and configure an event handler:
<script type="text/javascript">
<?php
$handler = new \Stimulsoft\StiHandler();
$handler->renderHtml();

Next, create the viewer with the necessary options and define the onBeginProcessData event. If value set to true, this event will be passed to the server-side event handler:
$options = new \Stimulsoft\Viewer\StiViewerOptions();
$options->appearance->fullScreenMode = true;

$viewer = new \Stimulsoft\Viewer\StiViewer($options);
$viewer->onBeginProcessData = true;

Next, create and load a report. The loadFile() method does not load the report object on the server side, it only generates the necessary JavaScript code. The report will be loaded into a JavaScript object on the client side:
$report = new \Stimulsoft\Report\StiReport();
$report->loadFile('reports/SimpleListSQLParameters.mrt');
$viewer->report = $report;
?>

Finally, render the necessary JavaScript code and visual HTML part of the component, and close the </script> block. The rendered code will be placed inside the specified HTML element:
function onLoad() {
	<?php
	$viewer->renderHtml('viewerContent');
	?>
}
</script>

...

<body onload="onLoad();">
<div id="viewerContent"></div>
</body>

Finally, process SQL data source parameters on the server-side in the onBeginProcessData() method. In this method, you can check and change all connection and query parameters, these values will not be passed to the client side. By default, all server-side events are located in the handler.php file:
$handler->onBeginProcessData = function ($args)
{
    // You can change the connection string
    if ($args->connection == 'MyConnectionName')
        $args->connectionString = 'Server=localhost;Database=test;uid=root;password=******;';

    // You can change the SQL query
    if ($args->dataSource == 'MyDataSource')
        $args->queryString = 'SELECT * FROM MyTable';

    // You can change the SQL query parameters with the required values
    // For example: SELECT * FROM @Parameter1 WHERE Id = @Parameter2 AND Date > @Parameter3
    if ($args->dataSource == 'MyDataSourceWithParams') {
        $args->parameters['Parameter1']->value = 'TableName';
        $args->parameters['Parameter2']->value = 10;
        $args->parameters['Parameter3']->value = '2019-01-20';
    }

    // Values for 'SimpleListSQLParameters.mrt' report template
    if ($args->dataSource == 'customers') {
        $args->parameters['Country']->value = "Germany";
    }

    // You can send a successful result
    return StiResult::success();
    // You can send an informational message
    //return StiResult::success('Some warning or other useful information.');
    // You can send an error message
    //return StiResult::error('Message about any connection error.');
};

Auf dem Screenshot unten Sie können das Ergebnis des Beispiel-Codes ansehen:

Using Parameters in SQL Query

By using this website, you agree to the use of cookies for analytics and personalized content. Cookies store useful information on your computer to help us improve efficiency and usability. For more information, please read the privacy policy and cookie policy.