Friendly error messages from SQL/XSLT Functions, when database connection fails

Topics: Community, Troubleshooting
Jan 26, 2013 at 11:39 AM
Edited Jan 26, 2013 at 11:41 AM

Is there a way to get friendly error messages, of my own writing, back from SQL/XSLT functions, instead of:

[ Error ]

.... or the worse:

<div class="c1errordetails" style="border: 2px solid red; padding: 2px 6px 2px 6px; background-color: InfoBackground; color: InfoText; -moz-border-radius: 4px; -moz-box-shadow: 1px 1px 3px 0 rgba(0,0,0,0.75);">
	<strong>Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)</strong>
	<div>C1 Function: BSU.Display.UndergraduateSubjectStudyOptions</div>
	<div style="font-size: 0.7em">Error details:
		<div style="padding-left: 10px;" title="xx">One or more errors occurred.
			<div style="padding-left: 10px;" title="xx">Failed to execute function with local name 'SQLUndergraduateSubjectStudyOptions'
				<div style="padding-left: 10px;" title="xx">Failed to get value for function 'BSU.Courses.SQLUndergraduateSubjectStudyOptions'
					<div style="padding-left: 10px;" title="xx">A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)</div>
				</div>
			</div>
		</div>
	</div>
</div>

?

I'd like to have something nice in place, in case of a firewall change within our network or a connection string failure.

Jan 26, 2013 at 11:44 AM

Also, is there a way to detect the failure, and run another function, one that doesn't rely on a SQL lookup instead (a fall-back)?

Coordinator
Mar 14, 2013 at 9:30 AM
Hi David

We have a similar feature request registered http://compositec1.codeplex.com/workitem/1341

As always there're a few ways you can work around it:

1) Create a "wrapper" function that would be calling underlying SQL function, and in the case it fails, it may do a f.e. a redirect to "Website temporarily unavailable" page.

To do that you would have to write an implementation of Composite.Functions.Plugins.FunctionProvider.IFunctionProvider
and add it to the appropriate section in web.config.

It will return an IFunction instance that will do the necessary work. It would have a method like:
public override object Execute(ParameterList parameters, FunctionContextContainer context)
    {
        try {
            return parameters.GetParameter<XElement>("InnerFunction");
        }
        catch (SqlException){
            HttpContext.Current.Response.Redirect("/......", true);
        }
    }
That approach could be a bit tricky if you aren't writendly with the function api yet, also you would may have to change many code lines to have this approach

2) You can create a response filter, that would check if there's <span class="c1error">[ Error ]</span> in the response, and if there is, it would do a redirect
This one is much easier, check out implementation of class Composite.Core.WebClient.HttpModules.AdministrativeResponseFilterHttpModule
that is a working example of how to add a filter, you just have to write your own stream, something like this:
        internal class ErrorRedirectStream : Utf8StringTransformationStream
        {
            public ErrorRedirectStream(Stream innerStream) : base(innerStream) { }

            public override string Process(string str)
            {
                if (str.Contains("class=\"c1error\""))
                {
                    HttpContext.Current.Response.Redirect("/MyNoSqlConnecitonPage");
                }

                return str;
            }
        }