John, I read your post and I think I understand it. That is a testimony more to your skill in explaining it than to my cognitive abilities. However, I think someone like Jeff is more likely than me, at this point in my app dev learning curve, to use something like this.
I am going to come back to this post in about two months and try it. OT-I went to www.activeaero.com and noticed it was a fusebox site. Are you using PLUM with fusebox? Or using PLUM for other projects? Thanks for the post, Mark Fuqua -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of John Stanley Sent: Thursday, July 21, 2005 8:55 AM To: '[email protected]' Subject: [plum] Extending the UpdateRecord CustomTag. Let me start off by saying that this is by no means the only or even a good solution to the question posed here. There may already be functionality somewhere in the PLUM generated code that I have not seen. So if I missed it, I apologize to the authors. Either way, it was fun to develop ;-). In working with the very robust PLUM architecture I have come up with a few occasions where I needed the update for a record to be more than just simple data entry. What if I wanted to harness the awesome powers of the framework to validate my form data using the already existing custom tags ValidateForm and UpdateRecord while using my own Stored Procedures for the backend data manipulation? I could create a stored proc call in the DatabaseBlocks.cfc for each procedure I would need in my application, but then I am concerned about persisting procs that may not get used that often in memory. I could modify the UpdateRecord custom tag for each procedure I need to call and then pass in the proc name to it. It turns out that the last option in the paragraph above was what I ended up doing, well sort of. What I did was create another attribute in the UpdateRecord custom tag to pass an existing stored procedure name to. Then with two new CustomTags I am able to pass any stored procedure that exists in the db into the UpdateRecord CustomTag, which generates a dynamic CFStoredProc statement, executes it and bypasses the normal UpdateRecord functionality. This completely eliminates the need for any code relating to specific stored procedures. This works with stored procedures that have variable name value pairs in the arguments declaration portion of the procedures, or just the variable declarations. This also works whether the key being updated is at the front of the arguments list or the back. Below are the steps you can use to utilize the functionality I have outline above: 1. Create the stored procedures that you need. 2. Create a table in your db using the attached sql script storedProcedureArgumentLists.sql 3. Populate the table with the following: a. Stored Procedure Name b. List of Arguments: This list must be comma delimited, and must NOT include the primary key argument that is being updated. c. Whether the primary key argument appears first or last in the arguments declaration in the stored procedure. i. 1 means it is first ii.0 means it is last 4. Copy the three attached custom tags into your custom tag directory: a. UpdateRecord (this will overwrite the existing UpdateRecord tag so back it up if you are concerned about it) b. PrepareProcArguments c. PrepareProcArgumentsProcParamDataTypes 5. In your edit action page append the call to the UpdateRecord custom tag with the attribute [procedure="yourProcedureNameHere"] (without the brackets). That should be it. Limitations: 1. In the CustomTag PrepareProcArgumentsProcParamDataTypes only four data types are utilized (varchar,bit,integer and timestamp) this functionality can be easily extended to include others by editing the switch statement. If you are unsure, just put an abort just after the cfif statement on line 20 on the UpdateRecord tag and dump the array ThisTag.column which will give you all of the data types of the columns being passed to the proc. 2. This functionality has not been tested with procs that have primary keys other than integer, but the code is designed to allow that to happen. 3. If you put a stored procedure name in the action page that causes the query returning the arguments list to return nothing (one that does not exist or one that is not in the StoredProcedureArgumentsList table), no error will be generated and as such it may appear that the update was successful until closer inspection of the actual data in the row being updated. 4. This assumes that ALL of the fields being sent to the UpdateRecord tag are being used by the stored procedure. If you try using this, let me know how it goes, if you have any questions, find any bugs or anything. Also can the performance gurus maybe look into it for possible performance and system implications. Thanks alot. John Stanley Web Application Developer Active Aero Group http://www.activeaero.com 734-547-7200 office <<storedProcedureArgumentLists.sql>> <<storedProcedureArgumentLists.sql>> <<UpdateRecord.cfm>> <<PrepareProcArguments.cfm>> <<PrepareProcArgumentsProcParamDataTypes.cfm>> ********************************************************************** You can subscribe to and unsubscribe from lists, and you can change your subscriptions between normal and digest modes here: http://www.productivityenhancement.com/support/DiscussionListsForm.cfm **********************************************************************
