Documentation
Back to website >

ExecuteSQL (Pascalscript)

Description

This function executes any SQL command related to the current database (clients). So that the same command is not called more than once by the report generator, it should be entered in the main event of the report under BeforePrint.

Syntax

function ExecuteSQL(var Report:TppReport;const Statement:String; var Affected:Integer; var ResultMsg:String ): boolean;

Arguments

The object Report must always be specified and is always called Report. It is the object of the current report. The SQL command to be executed is specified in the string constant statement. The number of affected rows is passed back in the integer value Affected. In the ResultMsg string, a Select command returns the first field of the first record in the result set. This makes it possible to retrieve a value from the SQL server. If an error occurs, ResultMsg contains the error text. The result of ExecuteSQL is True if the function could be executed without errors. If the SQL server has detected an error, the function result is False and the error text is transmitted in ResultMsg.

Example

var
  ResultMsg : String;
  Statement : String;
  Affected : Integer;
begin
  Statement:='UPDATE Artikel SET EkNetto=0.0';
  if ExecuteSQL(Report, Statement, Affected, 
    ResultMsg)=False then
  begin
    ShowMessage('The following error occurred: '+
       ResultMsg);
  end else
  begin
    ShowMessage('There are '+IntToStr(Affected)+
    ' records are affected.');
  end;
end;

Rules to understand the script can be found here.

In this example, all purchase prices in the article master are set to the amount value 0.00. If no error occurs, the number of affected data records is returned in Affected. If an error occurs, the error text passed in ResultMsg is displayed.

If a SELECT command is used instead of an UPDATE command, note that only the first field is returned as a result in ResultMsg. If several data records are returned, only the first data record is taken into account.

ShowMessage shows: There are 18 records are affected.

The number of data records depends of course on the number of your article data.