Choose your database:
AnySQL
MySQL
MS SQL Server
PostgreSQL
SQLite
Firebird
Oracle
SQL Anywhere
DB2
MaxDB

Subscribe to our news:
Partners
Testimonials
Jim Salmons: "That is excellent news! I will look forward to a future update with these features. It will be attention to such details that further distinguishes your excellent products from the competition.

As always, I appreciate your great products and your demonstrated customer service is equal to the high quality of your products".
Alex Torin: "Several months ago I downloaded the trial versions of several Microsoft SQL administration tools that I was able to find using google. I had considerable trouble with most of them crashing... but using your product I was able to isolate and solve the problems I was having. I decided to add it to my toolkit and have recommended it to a few others".

More

Add your opinion

Customers
Download Buy

MS SQL Maestro online Help

Prev Return to chapter overview Next

DML procedures generation

MS SQL Maestro allows you to create DML (also known as CRUD) procedures automatically. CRUD is an acronym for the four essential database operations: Create, Read, Update, and Delete. The application designer has many choices for accomplishing the CRUD operations but the most efficient choice in terms of Microsoft SQL performance is to create a set of stored procedures to perform the operations.

 

The reasons for using DML Procedures instead of allowing ad hoc SQL statements are:

•The best possible performance

After the first use of each stored procedure, the plan for executing the procedure is cached in the server's procedure cache. For subsequent invocations of the stored procedure, the plan is reused. This avoids the parsing and optimization steps with their overhead.

 

•Removing of the SQL code from the other layers of the application

By removing the SQL statements from the application code, all the SQL can be kept in the database.

 

•Preventing of SQL injection attacks

Anytime a client application uses string concatenation to create SQL statements, there is a possibility of a SQL injection attack. In short, these attacks involve clever entry of SQL in the data entry fields of an application in such a way that the SQL statements executed are different from the ones intended by the programmer. They require that the application developer is careless about not cleaning any user input to prevent the attack.

 

•Preventing of casual table browsing and modifications

If an application uses ad hoc SQL statements, the users of the application must have the required permissions on the database tables. Once they are given permission on the tables, they can work with them in any application that can read and manipulate the data such as Excel, Word and various report writers. Casual examination of the data and even updates that bypass the application's business rules become possible. Stored procedures have long been used to prevent casual browsing and updates. This is implemented by granting permission to execute the CRUD stored procedures to the users and revoking permission to access the tables directly.

 

To generate DML procedure,

•select the Object | Generate DML procedures... main menu item (to create procedures for several tables) or use the corresponding popup menu item of the table's node at the Explorer tree (to create procedures for one table).
•Specify tables the procedures will be created for (in case of several tables).
•Uncheck the operations the procedures will not be created for. By default the procedures are generated for inserting, reading, updating, and deleting of table data.
•Adjust templates of procedures names.
•Select the action to perform after the generation. The created definitions can be copied to Clipboard, saved to a file, sent to SQL Script Editor or executed immediately.

 



Prev Return to chapter overview Next