Executes a query against a database and returns the number of affected rows.
When running this function inside a project, it is guaranteed that no changes will be made to the database:
When running this function from a specification the command will be executed as normal.
When this function is triggered during a Specification it is possible to change the data in the database being queried.
We recommend thoroughly proving the use of this function on a snapshot of the database data.
DBExecute( ["Connection String"] , ["SQL Command"] ,["Username"], ["Password"])
Where:
DBExecute supports both DSN and direct SQL connection strings.
If using ODBC, a valid data source name is required, for example:
"SystemDSNName"If using SQL Server, a valid SQL connection string is required, for example:
"Server= ServerName;Database= DatabaseName"
Is a valid SQL Query command to execute, for example:
"DELETE FROM Currency WHERE CurrencyForCalcs IS NULL"
"INSERT INTO Countries VALUES ('USA','MA','Boston','Dollar')""UPDATE Currency SET CurrencySymbol='$' WHERE CurrencyForCalcs = 'Dollar'"
The username to gain access to the database (optional)
"UserName"
The password to gain access to the database (optional)
"UserPassword"
Rule | Meaning |
|---|---|
| DbExecute("Server= DataServer;Database= Countries","DELETE FROM Currency WHERE CurrencyForCalcs IS NULL","","") | Connects to the SQL server DataServer and the Countries database. Will return the number of rows affected by the SQL command DELETE FROM in the Currency table WHERE a column named CurrencyForCalcs is NULL - e.g. 7. The connection utilizes Windows Authentication so the UserName and Password fields are blank ("") |
| DbExecute("Server= DataServer;Database= Countries","INSERT INTO Countries VALUES ('USA','MA','Boston','Dollar')","","") | Connects to the SQL server DataServer and the Countries database. Will return the number of rows affected by the SQL command INSERT INTO in the Countries table for the VALUES USA, MA, Boston and Dollar in their respective columns - e.g. 1. The connection utilizes Windows Authentication so the UserName and Password fields are blank ("") |
| DbExecute("Server= DataServer;Database= Countries","UPDATE Currency SET CurrencySymbol='$' WHERE CurrencyForCalcs = 'Dollar' ","","") | Connects to the SQL server DataServer and the Countries database. Will return the number of rows affected by the SQL command UPDATE, which would SET the column CurrencySymbol to $ WHERE a column named CurrencyForCalcs equals Dollar, in the Currency table - e.g. 175. The connection utilizes Windows Authentication so the UserName and Password fields are blank ("") |
See also