Allowing Users to Add and Edit Data
In this section, you will learn how to display records from a database and control
read/write access to individual records based on a user's role and his relationship to the
record. The following rules will apply:
- All users will be able to see active races.
- All logged-in users will be able to add races.
- "User" users will only be able to edit and delete races that they added themselves.
- "Admin" users will be able to see, edit, and delete all races.
The interface will look like this when a "User" user logs in:
This first demo displays all the races, but they are not yet sortable. It also allows all users to edit all data.
We will add code that prevents users from editing other people's records.
We will also add code to allow any user who is logged in to add a race.
We will now make the table headings into links that, when clicked on, will change the
order in which the records appear.
That completes the page for displaying data. In the next sections, we will learn how to add,
edit and delete races.
Adding Data using Stored Procedures
A stored procedure is a database object that holds a callable query, allowing parameters to be passed in.
Benefits of Stored Procedures
The major benefits of stored procedures.
- Performance. Because their execution plan does not have to be determined at runtime, stored procedures typically run faster than straight queries.
- Security. Stored procedures provide a means for accomplishing a specific task and do not provide the same kind of direct access to tables as straight queries do.
- Simplicity and Reusability. Complex queries can be saved as stored procedures, so developers do not have to write the same difficult query over and over.
Calling Stored Procedures with ColdFusion
In ColdFusion, stored procedures can be called directly within a <cfquery> tag using the database-specific syntax for executing them. They can also be called using the <cfstoredproc> tag.
<cfstoredproc> and <cfprocparam>
The <cfstoredproc> tag works much like the <cfquery> tag, except that, rather than a straight query, it contains (or can contain) one or more <cfprocparam> tags for passing parameters to the stored procedure.
||Required. The name of the stored procedure.
||Required. The name of the data source.
||Overrides username set up in ColdFusion Administrator.
||Overrides password set up in ColdFusion Administrator.
||Maximum rows to get at a time from server.
||Turns debugging display on or off.
||Sets cfstoredproc.statusCode to the status code returned by the stored procedure.
||The name of the structure to hold the variables returned by the stored procedure. By default, the structure is stored in cfstoredproc.
Using <cfprocparam>, parameters are passed into a stored procedure in the same order as they are set in the stored procedure itself.
||Possible values are in, out, or inout depending on whether a value is being sent to and/or received from the stored procedure.
||Required if type is in or inout. The variable name that will hold the data returned by the stored procedure.
||Required if type is in and optional if type is inout. Value passed to stored procedure.
||Required. The SQL data type (e.g, cf_sql_integer, cf_sql_varchar, etc.)
||The maximum number of characters of an in or inout value. "0" means no limit.
||The number of digits after the decimal of a numeric parameter. "0" means no limit.
||The in or inout parameter passes a null value. The value attribute is ignored.
The following three examples show different methods for inserting
- Using <cfquery> with a standard INSERT statement.
- Using <cfquery> with a call to a stored procedure.
- Using <cfstoredproc>.
Stored procedures can also return one or more recordsets. The <cfprocresult> tag is used to store these recordsets as ColdFusion queries. It works much like the name attribute of the <cfquery> tag.
||Required. The name of the recordset.
||A number indicating which recordset to store (only used if multiple recordsets are returned by the stored procedure).
||The maximum number of records to retrieve for that recordset.
Editing and Deleting Data
The page for editing and deleting data is shown below.