Execution of SQL scripts
This topic explains why, when and how Ventura SQL Studio executes the SQL scripts in the recordset definitions.
Why does Ventura SQL Studio need to execute the SQL scripts?
This is necessary for collecting resultset information. Ventura needs to know column names, data types and other properties for the resultset(s) produced by an SQL script.
In some special situation you might want to prevent Ventura SQL Studio from executing parts of the script. You can prevent execution of SQL statements during code-generation time using the @DesignMode parameter.
When is SQL script executed?
- When recordset C# code is generated;
- When you click [Collect] to refresh the "resultset definitions" and "updateable table" lists;
- When you click [Run query];
- When you click [Preview recordset code];
- When you click [Raw schema];
- When you run "Auto Create recordsets".
SQL statements that modify the database
A simple SELECT * FROM statement does not modify the database.
SQL statements that modify the database are for example: INSERT, UPDATE, DELETE. A stored procedure, run by the EXEC keyword might also modify the database.
Ventura SQL Studio executes SQL scripts in a database transaction, and that transaction is rolled back. Unfortunately the use of transactions can not totally prevent changes to the database. The identity seed of an identity (auto increment) column does not revert to the original value when a transaction is rolled back. This means that the identity value keeps going up, and gaps in the numbering will be created.
If the SQL script contains data-modifying statements and you absolutely want to prevent Ventura SQL Studio from modifying the production database, then it is absolutely necessary to use a backup of the production database for code generation.
With the @DesignMode parameter you can prevent parts of the SQL script from being executed by Ventura SQL Studio.
The resultsets returned by ADO.NET must be constant: The SQL script must always produce the same number of resultsets with exactly the same columns.
There can not be any difference in the resultsets (and columns) produced by the SQL script at different moments.
The resultset output at code generation time must exactly match the output at runtime.
This is an example of an illegal SQL script:
IF <condition> THEN
SELECT * FROM CUSTOMERS
See Careful with Control-of-Flow keywords for more information and examples.
Should resultset output be inconsistent, runtime errors will occur.