|
Prepared statements and stored proceduresMany of the more mature databases support the concept of prepared statements. What are they? They can be thought of as a kind of compiled template for the SQL that an application wants to run, that can be customized using variable parameters. Prepared statements offer two major benefits:
Prepared statements are so useful that they are the only feature that PDO will emulate for drivers that don't support them. This ensures that an application will be able to use the same data access paradigm regardless of the capabilities of the database.
Example #1 Repeated inserts using prepared statements This example performs an INSERT query by substituting a name and a value for the named placeholders.
<?php
Example #2 Repeated inserts using prepared statements This example performs an INSERT query by substituting a name and a value for the positional ? placeholders.
<?php
Example #3 Fetching data using prepared statements This example fetches data based on a key value supplied by a form. The user input is automatically quoted, so there is no risk of a SQL injection attack.
<?php
Example #4 Calling a stored procedure with an output parameter If the database driver supports it, an application may also bind parameters for output as well as input. Output parameters are typically used to retrieve values from stored procedures. Output parameters are slightly more complex to use than input parameters, in that a developer must know how large a given parameter might be when they bind it. If the value turns out to be larger than the size they suggested, an error is raised.
<?php
Example #5 Calling a stored procedure with an input/output parameter Developers may also specify parameters that hold values both input and output; the syntax is similar to output parameters. In this next example, the string 'hello' is passed into the stored procedure, and when it returns, hello is replaced with the return value of the procedure.
<?php
Example #6 Invalid use of placeholder
<?php |