Home Search

DriveWorks Pro 21
DBExecute

Send Feedback

DBExecute

Executes a query against a database and returns the number of affected rows.

When this function is triggered during a Specification it is possible to change the data in the database being queried.

If an ODBC connection is being used the command will not be run and the result -1 will be returned.

If using a direct SQL connection, the command will be run but the changes will never be saved.

Syntax

DBExecute( ["Connection String"] , ["SQL Command"] ,["Username"], ["Password"])

Where:

Connection String

DBExecute supports both DSN and direct SQL connection strings.

If using ODBC, a valid data source name is required, for example:

ODBC Connection String
"SystemDSNName"

If using SQL Server, a valid SQL connection string is required, for example:

SQL Connection String
"Server= ServerName;Database= DatabaseName"

SQL Command

Is a valid SQL Query command to execute, for example:

SQL DELETE Command
"DELETE FROM Currency WHERE CurrencyForCalcs IS NULL"
SQL INSERT Command
"INSERT INTO Countries VALUES ('USA','MA','Boston','Dollar')"
SQL UPDATE Command
"UPDATE Currency SET CurrencySymbol='$' WHERE CurrencyForCalcs = 'Dollar'"

Username

The username to gain access to the database (optional)

Example Title
"UserName"

Password

The password to gain access to the database (optional)

Example Title
"UserPassword"

Examples

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

How To: Troubleshoot SQL Connection