4D v14.3

SQL EXECUTE SCRIPT

Home

 
4D v14.3
SQL
SQL EXECUTE SCRIPT

SQL EXECUTE SCRIPT 


 

SQL EXECUTE SCRIPT ( scriptPath ; errorAction {; attribName ; attribValue} {; attribName2 ; attribValue2 ; ... ; attribNameN ; attribValueN} ) 
Parameter Type   Description
scriptPath  Text in Complete pathname of file containing SQL script to execute
errorAction  Longint in Action to carry out in case of error during script execution
attribName  Text in Name of attribute to use
attribValue  Text in Value of attribute

The SQL EXECUTE SCRIPT command is used to execute a series of SQL statements placed in the script file designated by scriptPath. This command can only be executed on a local machine (local 4D or stored procedure on 4D Server). It works with the current database (internal or external database).

Note: This command cannot be used with an external connection that is opened directly or via ODBC.

Pass the complete pathname of the text file containing the SQL statements to be executed in the scriptPath parameter. The pathname must be expressed using the syntax of the current system. If you pass an empty string ("") in scriptPath, a standard Open document dialog box will be displayed so that the user can select the script file to be executed. 

Note: The SQL EXPORT DATABASE and SQL EXPORT SELECTION commands automatically generate this script file.

The errorAction parameter is used to configure the functioning of the command when an error occurs during script execution. You can pass one of the three following constants, placed in the SQL theme:

Constant Type Value Comment
SQL On error abort Longint 1 In the event of an error, 4D immediately stops script execution.
SQL On error confirm Longint 2 In the event of an error, 4D displays a dialog box describing the error and allowing the user to interrupt or continue script execution.
SQL On error continue Longint 3 In the event of an error, 4D ignores it and continues script execution. 

The attribName and attribValue parameters must be passed by pairs. These parameters are intended to be used to specify specific attributes for the script execution. In the current version of 4D, a single attribute can be passed in attribName, available via the following constant, placed in the SQL theme:

Constant Type Value Comment
SQL use access rights String SQL_Use_Access_Rights Used to restrict the access rights to be applied during execution of the SQL commands of the script. When you use this attribute, you must pass 0 or 1 in attribValue:
  • attribValue = 1: 4D uses the access rights of the current 4D user.
  • attribValue = 0 (or attribute not specified): 4D does not restrict access, the Designer rights are used.

If the 4D log file is activated (via the selectors 28 or 45 of the SET DATABASE PARAMETER command), each SQL command executed will generate an entry with the following information:

  • Type of SQL command
  • Number of records affected by the command
  • Duration of command execution
  • For each error encountered:
    • the error code
    • the error text if available

If the script is executed correctly (no error occurs), the OK system variable is set to 1. In the event of an error, the OK system variable is set to 0 or not according to the errorAction parameter:

  • If errorAction is SQL On error abort (value 1), OK is set to 0.
  • If errorAction is SQL On error confirm (value 2), the OK variable is set to 0 if the user chooses to stop the operation and 1 if they choose to continue .
  • If errorAction is SQL On error continue (value 3), the OK variable is always 1.

Note: If you use this command to execute memory-consuming actions such as massive data imports, you can consider calling the ALTER DATABASE SQL command in order to temporarily disable the SQL options.

 
PROPERTIES 

Product: 4D
Theme: SQL
Number: 1089

The OK variable is changed by the commandDifferent in remote mode

 
HISTORY 

Created: 4D v12

 
ARTICLE USAGE

4D Language Reference ( 4D v12.4)
4D Language Reference ( 4D v14 R3)
4D Language Reference ( 4D v14 R2)
4D Language Reference ( 4D v13.5)
4D Language Reference ( 4D v14.3)
4D Language Reference ( 4D v14 R4)