Re: CFSTOREDPROC kind of sucks
That is very nice Brad. Thanks for that perspective. >First of all, it is my preference to explicitly check for an existing >duplicate value prior to insertion instead of allowing SQL server to >throw the error. The column constraint is my fall-back. >Either way, if you want your stored procedures to be able to pass back >useful information about problems or successes it encountered, I would >recommend using the return code and an @outputMessage output variable. > >Your proc can test for the condition and set the output message into an >output variable and return the desired code. This way, if the database >driver provider or DBMS itself ever changes their error codes in a >future version it won't bork your code. > >CREATE PROCEDURE myProc >@outputMessage varchar(255) OUTPUT, >@input1 int, >@input2 int > >AS >BEGIN > > IF badCondition is true > BEGIN > SET @outputMessage = 'Here is a detailed and useful error > message' > RETURN 75 -- Your special "code" for this error > END > >-- otherwise... > > > SET @outputMessage = 'Execution Successful' > RETURN 0 > >END > >The cfstoredproc tag give you the return code, and you can then check it >for whatever problems may have occurred. You can then use the value of >@outputMessage to return to the user if you so choose. > >~Brad > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325780 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFSTOREDPROC kind of sucks
On Wed, Aug 26, 2009 at 4:04 PM, Tony Bentley wrote: > > > When this is thrown, the message states "[Macromedia][SQLServer JDBC Driver]" > and then the RAISERROR message following. Nice for debugging but not so nice > for passing the message and error code to a handler. > > I would really like to be able to use the error codes and have a handler > method based on which code it passed back. Not really possible. Kind of hokey > not to throw a SQL exception (and pass back a value instead) since you would > want SQL Server to log the exception. Have you tried dumping the cfcatch to see what's in there? I find that it often includes much more than the error message you see when not using try/catch... IE, there's a SQLSTATE value in there somewhere and some other stuff. -- Rick Root CFFM - Open Source Coldfusion File Manager http://www.opensourcecf.com/cffm ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325753 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFSTOREDPROC kind of sucks
> A real world scenario is when a user tries to insert a value that must be > unique in the database. If a duplicate > is found, SQL can return a reference code and a message stating that there is > a duplicate found, an exception > is thrown and an id is passed back - RAISERROR & @@ERROR. > > When this is thrown, the message states "[Macromedia][SQLServer JDBC Driver]" > and then the RAISERROR > message following. Nice for debugging but not so nice for passing the message > and error code to a handler. > > I would really like to be able to use the error codes and have a handler > method based on which code it passed > back. Not really possible. Kind of hokey not to throw a SQL exception (and > pass back a value instead) since you > would want SQL Server to log the exception. I don't understand the problem. Just return the exception, catch it with try/catch, and use the CFCATCH data to see the exception data. SQL Server will log the exception, and you can choose how to present it in CF. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325750 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CFSTOREDPROC kind of sucks
First of all, it is my preference to explicitly check for an existing duplicate value prior to insertion instead of allowing SQL server to throw the error. The column constraint is my fall-back. Either way, if you want your stored procedures to be able to pass back useful information about problems or successes it encountered, I would recommend using the return code and an @outputMessage output variable. Your proc can test for the condition and set the output message into an output variable and return the desired code. This way, if the database driver provider or DBMS itself ever changes their error codes in a future version it won't bork your code. CREATE PROCEDURE myProc @outputMessage varchar(255) OUTPUT, @input1 int, @input2 int AS BEGIN IF badCondition is true BEGIN SET @outputMessage = 'Here is a detailed and useful error message' RETURN 75 -- Your special "code" for this error END -- otherwise... SET @outputMessage = 'Execution Successful' RETURN 0 END The cfstoredproc tag give you the return code, and you can then check it for whatever problems may have occurred. You can then use the value of @outputMessage to return to the user if you so choose. ~Brad Original Message ---- Subject: Re: CFSTOREDPROC kind of sucks From: Tony Bentley Date: Wed, August 26, 2009 3:04 pm To: cf-talk Sorry, only two issues really. A real world scenario is when a user tries to insert a value that must be unique in the database. If a duplicate is found, SQL can return a reference code and a message stating that there is a duplicate found, an exception is thrown and an id is passed back - RAISERROR & @@ERROR. When this is thrown, the message states "[Macromedia][SQLServer JDBC Driver]" and then the RAISERROR message following. Nice for debugging but not so nice for passing the message and error code to a handler. I would really like to be able to use the error codes and have a handler method based on which code it passed back. Not really possible. Kind of hokey not to throw a SQL exception (and pass back a value instead) since you would want SQL Server to log the exception. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325749 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFSTOREDPROC kind of sucks
Sorry, only two issues really. A real world scenario is when a user tries to insert a value that must be unique in the database. If a duplicate is found, SQL can return a reference code and a message stating that there is a duplicate found, an exception is thrown and an id is passed back - RAISERROR & @@ERROR. When this is thrown, the message states "[Macromedia][SQLServer JDBC Driver]" and then the RAISERROR message following. Nice for debugging but not so nice for passing the message and error code to a handler. I would really like to be able to use the error codes and have a handler method based on which code it passed back. Not really possible. Kind of hokey not to throw a SQL exception (and pass back a value instead) since you would want SQL Server to log the exception. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325748 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFSTOREDPROC kind of sucks
On Wed, Aug 26, 2009 at 2:14 PM, Tony Bentley wrote: > > dbvarname is completely useless. It would be nice to be able to send values > across out of order or not send a value if it is not needed (NULL). It would > also be nice to have those values in the debugging to reference. You can send a null or -- Rick Root CFFM - Open Source Coldfusion File Manager http://www.opensourcecf.com/cffm ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325747 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFSTOREDPROC kind of sucks
According to LiveDocs, in MX 6 "Changed the dbvarname attribute behavior: it is now ignored for all drivers. ColdFusion uses JDBC 2.2 and does not support named parameters." Thanks, Eric Cobb http://www.cfgears.com Dave Watts wrote: >> Three issues that come to mind: > > You've really only listed two issues. > >> Cannot access transaction errors because a coldfusion exception is thrown so >> any validation exceptions must be >> handled through cftry/cfcatch instead of the CFSTOREDPROC. If en error >> occurs in SQL, it means coldfusion throws >> an error too. > > This is (a) in keeping with how CF deals with database errors > generally, and (b) pretty much ideal. What would you suggest > CFSTOREDPROC do, other than swallow the database error? There's no > place within CFSTOREDPROC for additional conditional processing, > really, so why not use exceptions? > > Of course, you can use the database-specific attributes within CFCATCH > to figure out how to respond. > >> dbvarname is completely useless. It would be nice to be able to send values >> across out of order or not send a value >> if it is not needed (NULL). It would also be nice to have those values in >> the debugging to reference. > > My understanding is that this is a limitation in JDBC, although I > can't really say I've verified that for myself. But yeah, it would be > nice to send name-value pairs. > > Dave Watts, CTO, Fig Leaf Software > http://www.figleaf.com/ > > Fig Leaf Software provides the highest caliber vendor-authorized > instruction at our training centers in Washington DC, Atlanta, > Chicago, Baltimore, Northern Virginia, or on-site at your location. > Visit http://training.figleaf.com/ for more information! > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325746 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFSTOREDPROC kind of sucks
> Three issues that come to mind: You've really only listed two issues. > Cannot access transaction errors because a coldfusion exception is thrown so > any validation exceptions must be > handled through cftry/cfcatch instead of the CFSTOREDPROC. If en error occurs > in SQL, it means coldfusion throws > an error too. This is (a) in keeping with how CF deals with database errors generally, and (b) pretty much ideal. What would you suggest CFSTOREDPROC do, other than swallow the database error? There's no place within CFSTOREDPROC for additional conditional processing, really, so why not use exceptions? Of course, you can use the database-specific attributes within CFCATCH to figure out how to respond. > dbvarname is completely useless. It would be nice to be able to send values > across out of order or not send a value > if it is not needed (NULL). It would also be nice to have those values in the > debugging to reference. My understanding is that this is a limitation in JDBC, although I can't really say I've verified that for myself. But yeah, it would be nice to send name-value pairs. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325744 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
