Of course the fun part about programming is doing it for yourself, so I'm
sure you don't want me to just send you the code. Here is the pseudo-code
for what I'm doing (prefaced with a couple of notes):
Notes
1. This "module" is always called as a CFINCLUDE, so it automatically has
access to all of the variables in the calling template.
2. I'm using FormURL2Attributes, so I know that all variables will be in a
single scope (in this case the attributes scope).
3. This was written to work with SQL Server, but I'm sure it would be easy
to produce a variation for Oracle, Sybase, etc.
4. This routine, as currently written, only supports SPs that return 1 or 0
result sets, but it wouldn't be too difficult to change that as well.
Here's what the routine does:
1. Accepts input parameters (which are manually placed in the attributes
scope in the calling template) that describe the Stored Procedure name and
the name of a query to hold the result set. (This query name has a default,
which I usually use, so I generally only set the Stored Procedure name).
2. Query the database to find out what the possible input parameters are for
the stored procedure (In SQL Server there's a system stored procedure that
will do this for you).
3. Start to build a SQL SP call (i.e., set a variable = "Exec {SP Name} ").
4. Loop through the list of parameters returned by step 2.
4a. Check to see if it is an INPUT parameter.
4b. If it is, check to see if a variable with the same name exists in the
"attributes" scope.
4c. If it does, add it to the SQL statement that was started in step 3.
(i.e., add "@Param = {cf_variable_value}").
Step 4c does special processing which checks the datatype of the expected
parameter and does a proper passing of the cf variable (e.g., uses Val() if
it's a numeric parameter).
5. Call the stored procedure using <CFQUERY> and placing the generated SQL
statement inside the tag.
There is also some special processing to deal with BIT fields. This is
because BIT fields are often represented by checkboxes on forms, so even
though there may not be a corresponding variable in the attributes scope, I
still may need to pass a value (e.g., if someone unchecks a box I may need
to set the value to "0").
That's basically it. Using this template I can create a form which submits
to an action page. Then on the action page, after I do any validations, I
just set attributes.SPName, and do a <cfinclude> of the above module and the
stored procedure is called and passed all of the parameters automatically.
It's so simple, yet it saves so much time. After doing this, I can't
imagine manually writing SP calls (or even any SQL within a CFQUERY tag).
Another bonus that I've discovered with this is that I almost always just
let the query name default, so that the result set is stored in a query
called qrySP. This means that I've kind of created my own generic query
scope, so any templates that display information from a query can have all
of the variables scoped with "qrySP.". This makes those templates more
easily reusable, as I can run any stored procedure and as long as it returns
the proper column names it will work with any template.
If anyone has any comments or suggestions to refine this further I'd be
pleased to hear about them.
Thanks,
Bob
-----Original Message-----
From: Derek Hamilton [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 23, 2001 5:39 PM
To: Fusebox
Subject: Re: Musings on Attributes (was Best Practices...)
Bob,
Just wanted to ask, plead, beg for more information on that stored
procedures tag. I also use SPs extensively and would really appreciate
anything that's been developed for automating development with these!
So, PLEASE PLEASE PLEASE would you be willing to share/hint at/anything this
code?
Thanks!
Derek Hamilton
Systems Developer
Digital Gear
on 3/23/01 1:11 PM, the esteemed Bob Silverberg at [EMAIL PROTECTED]
postulated:
> I've been following this thread with interest (and a few laughs), so I'll
> throw in my 2 cents.
>
> I developed a couple of small web sites with Fusebox based on info that I
> gleaned from fusebox.org, and I really liked it. The next project I had
to
> tackle was a major web application. Not a web site, but a full-blown
> business application that was previously written in VB. We had to
redevelop
> it with a lot of new functionality and deliver it with a browser-based
> interface. There are two components - an intranet that is used for a lot
of
> administration tasks and an extranet that partners use to interact with
the
> system.
>
> So the system is about 90% data processing and about 10% presentation (as
> opposed to most web sites). I spent a few days trying to figure out how
to
> build such a complex system using fusebox, and I came up with a solution
> that is (I believe) very similar to XFB (although I have to admin that I
> haven't read the info on Hal's site yet). maybe I'm just dense, but I
found
> that moving to a hierarchical circuit app design made it much easier for
me
> to understand and implement.
>
> So that's my take on XFB (or what I understand XFB to be).
>
> Now, onto the attributes scope. I found that having a common scope that
all
> of my form and url variables were put into, and into which I could add my
> own variables, allowed me to write a routine that has saved me dozens, if
> not hundreds of hours. Due to the nature of the site, I have written
dozens
> of stored procedures. Stored procedures are used for all data
manipulation
> and extraction. In the past I have had to write a call to each stored
> procedure (either using CFQUERY or CFSTOREDPROC), but using the attributes
> scope I was able to write one routine that would basically take anything
> that was placed in the attributes scope, and that was expected by the
stored
> procedure, and would dynamically build the call. Although the app
requires
> easily over a hundred different calls to stored procedures, I haven't had
to
> write a single one.
>
> Now, I could use any scope for that, it doesn't have to be "attributes",
but
> I do find that a common scope, to which everything is "automatically"
added
> is incredibly useful.
>
> One other thing that comes to mind about simply using the variables scope
> for everything, rather than attributes or another "made up" scope (using a
> structure) is that I commonly use a variation on the CF_REUSEFORM tag,
which
> depends on there being values in two different scopes, "variables" and
> "attributes". If I was to use the "variables" scope to replace the
> "attributes" scope I'd have to rethink the way this tag is being used
(which
> might not be such a bad thing, but hey the tag works pretty well).
>
> Have a great weekend,
> Bob
>
> -----Original Message-----
> From: Roger B. [mailto:[EMAIL PROTECTED]]
> Sent: March 23, 2001 2:48 PM
> To: Fusebox
> Subject: RE: Musings on Attributes (was Best Practices...)
>
>
>> I have to be honest here... I'm not convinced of the nested Fuseboxes.
>
> Steve,
>
> Same here... the whole XFB nesting thing strikes me as a beautiful mess
that
> solves a non-problem. Custom tags are a fundamental part of CF, and to me,
> they have always seemed the cleanest, most natural way of calling another
> 'box.
>
>
> --
> Roger
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
