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

Subscribe to our news:
Partners
Testimonials
Adam Allen: "I just have to say WOW!!! I'm so glad that you guys have developed something that is so useful for MySql. I am able to graphically view my tables, and I don't even have to think about my queries because I can visually design them. I just stumbled upon this, and I'm already very excited about it".
Torstein Krogh: "Some time ago, I tested all available tools on the market for maintaining MySQL and MySQL Maestro came up on top. My opinion is that MySQL should offer it as a free tool for all paying customers! They have nothing today, and doing MySQL DOS commands is not getting any new customers".

More

Add your opinion

SQL Maestro for MySQL online help

Prev Return to chapter overview Next

DML procedures generation

SQL Maestro for MySQL 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 MySQL 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