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

Subscribe to our news:
Partners
Testimonials
Neil McPherson: "Thanks very much for your advice. I would just like to add that SQL Maestro makes life so much easier to work with Firebird, I have tried some of the other management tools but Maestro is such a nicely organized product and it has never let me down".
Stephen Arrowel, Database Administrator: "We are in the process of implementing Firebird solutions at multiple levels in our international organization. We expect that SQL Maestro Group will do nothing short of revolutionize the way we develop and maintain our Firebird databases. The continuous improvement and development means that the product is extremely flexible and will grow with us. The service and responsiveness of the Support Team has been exceptional. They have devoted countless hours to understanding our needs, so that we could get a Firebird administration tool which would be so simple and effective in use. SQL Maestro Group is helping Sytrax sail into the 21st Century".

More

Add your opinion

Customers
Download Buy

Firebird Maestro online Help

Prev Return to chapter overview Next

DML procedures generation

Firebird 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 Firebird 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