test db connectivity

Tell us more. Why, in that last failure, did the query fail? SQL wrong? Something about the connection string wrong?
Wrap your query in a try catch and get more specifics on what is wrong.

more info
wish to see if DB is up or not
doing DB.SetConnection(name) returns nothing... no way to tell if DB is up or not
so... when do the first thing of
DB.Query(... kapam.. get crash msg ( I'm working with Db2 )
so I need to do try / catch, but there is nothing to try, because if do, still get the Kapan... crash msg
I need to have some low level DB connectivity test to ensure DB's are up and working
using ASP stuff is all high level calls, no way to trap any error before get crash msg
Thanks

Mark

There is no such as "DB is up". When you use DB.SetConnection(name) it simply means that the next database operation will be executed against that connection.
Instead of "kabam... crash" you need to post the actual error message here.

Sergey
Here is the code / error
any suggestions most appreciated
Thanks

Mark
// NOTE THIS ALL WORKS FINE IF SQL SERVER IS ON LINE... BUT IF NOT... GET BELOW err msg...

// which is what I'm trying to prevent...
// switch over to SQL SErver ...

DB.SetConnection("ODBC:lims_test");
// I'VE TRIED DOING errmsg = DB.LastError here but always comes up empty... nada .. nothing...

// I've TRIED DOING try / catch and nada.. always says it is fine...
sqlcmd = "select count(*) as hits from (select sampleid,method from dbo.nye_result where processed = 0 group by sampleid,method ) a ;" ;
rs2 = DB.Query(sqlcmd);

datars2 = rs2.fetchAssoc();

if(datars2 ) {

hits = datars2["hits"];

XSession.Session["numassays"]=hits ;

}
Here is the error msg
Server Error in '/nyegeo' Application.

Cannot perform runtime binding on a null reference

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: Cannot perform runtime binding on a null reference
Source Error:
Line 617:

Line 618:rs2 = DB.Query(sqlcmd);

Line 619:datars2 = rs2.fetchAssoc();

Line 620:if(datars2 ) {

Line 621: hits = datars2["hits"];
Source File: c:\inetpub\wwwroot\nyegeo\include\CommonEvents.cs Line: 619
Stack Trace:
[RuntimeBinderException: Cannot perform runtime binding on a null reference]

CallSite.Target(Closure , CallSite , Object ) +148

System.Dynamic.UpdateDelegates.UpdateAndExecute1(CallSite site, T0 arg0) +664

runnerDotNet.CommonEvents.BeforeProcessMenu(Object pageObject) in c:\inetpub\wwwroot\nyegeo\include\CommonEvents.cs:619

System.Dynamic.UpdateDelegates.UpdateAndExecute2(CallSite site, T0 arg0, T1 arg1) +698

runnerDotNet.class_GlobalEvents.BeforeProcessMenu(Object pageObject) in c:\inetpub\wwwroot\nyegeo\include\EventsAggregatorGlobal.cs:365

System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid2(CallSite site, T0 arg0, T1 arg1) +679

runnerDotNet.GlobalController.menu() in c:\inetpub\wwwroot\nyegeo\menu.cs:172

lambda_method(Closure , ControllerBase , Object[] ) +87

System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) +280
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters) +35

System.Web.Mvc.<>cDisplayClass15.<InvokeActionMethodWithFilters>b12() +80

System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +466

System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +659

System.Web.Mvc.Controller.ExecuteCore() +146

runnerDotNet.BaseController.ExecuteThread(Object arg) in c:\inetpub\wwwroot\nyegeo\Controllers\BaseController.cs:45
[AggregateException: One or more errors occurred.]

runnerDotNet.BaseController.ExecuteCore() in c:\inetpub\wwwroot\nyegeo\Controllers\BaseController.cs:32

System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +91

System.Web.Mvc.<>cDisplayClassb.<BeginProcessRequest>b5() +60

System.Web.Mvc.Async.<>cDisplayClass1.<MakeVoidDelegate>b0() +25

System.Web.Mvc.<>cDisplayClasse.<EndProcessRequest>bd() +36

System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +651

System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +220

System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +134
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.8.4075.0

Sergy
Here's another typical DB error will get when network is down ( which happens here )
Here's the code
// BELOW WORKS FINE IF NETWORK IS UP, get err msg if Network DOWN...

// ALSO THIS IS THE VERY VERY FIRST DB call in the program...

// DB = DB2 ver 9.7 FP 11
102 sqlcmd = " select PROJECT,TITLE,HOST,DB_REGION,IISWRKDIR,IISPATH,DNS,USEDNS,VERSION,BUILD,cast(conversion_date as varchar(10)) as conv_date ," ;

103 sqlcmd += " MSXMLHTTP_VER , JSPRSRVR_URL_PORT, VULCAN_URL_PORT from NYEGEONET.projectinfo where host='" + host+ "' and db_region='" + region + "' ;" ;

104

105

106 rs=tDAL.CustomQuery(sqlcmd) ;

107 datars = CommonFunctions.db_fetch_array(rs);

108 if ( datars) {

109 XSession.Session["title"] = String.Format("{0}",datars["TITLE"]).ToUpper();

110 XSession.Session["version"] = String.Format("{0}",datars["VERSION"]).ToUpper();

111 XSession.Session["build"] = String.Format("{0}",datars["BUILD"]).ToUpper();

112 XSession.Session["d
and here's the error trying to catch and prevent
Server Error in '/nyegeo' Application.

ERROR [08001] [IBM][CLI Driver] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "10.90.10.174". Communication function detecting the error: "selectForConnectTimeout". Protocol specific error code(s): "0", "", "". SQLSTATE=08001

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.Odbc.OdbcException: ERROR [08001] [IBM][CLI Driver] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "10.90.10.174". Communication function detecting the error: "selectForConnectTimeout". Protocol specific error code(s): "0", "", "". SQLSTATE=08001
Source Error:
Line 241: throw e;

Line 242: }

Line 243: return null;

Line 244: }

Line 245: }
Source File: c:\inetpub\wwwroot\nyegeo\connections\Connection.cs Line: 243
Stack Trace:
[OdbcException (0x80131937): ERROR [08001] [IBM][CLI Driver] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "10.90.10.174". Communication function detecting the error: "selectForConnectTimeout". Protocol specific error code(s): "0", "", "". SQLSTATE=08001

]

runnerDotNet.Connection.query(XVar sql) in c:\inetpub\wwwroot\nyegeo\connections\Connection.cs:243

System.Dynamic.UpdateDelegates.UpdateAndExecute2(CallSite site, T0 arg0, T1 arg1) +698

runnerDotNet.tDAL.CustomQuery(XVar dalSQL) in c:\inetpub\wwwroot\nyegeo\Classes\tDal.cs:17

runnerDotNet.CommonEvents.AfterAppInit() in c:\inetpub\wwwroot\nyegeo\include\CommonEvents.cs:107

CallSite.Target(Closure , CallSite , Object ) +132

System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid1(CallSite site, T0 arg0) +645

runnerDotNet.appsettings.Apply() in c:\inetpub\wwwroot\nyegeo\include\appsettings.cs:923

runnerDotNet.BaseController.OnActionExecuting(ActionExecutingContext filterContext) in c:\inetpub\wwwroot\nyegeo\Controllers\BaseController.cs:65

System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +53

System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +659

System.Web.Mvc.Controller.ExecuteCore() +146

runnerDotNet.BaseController.ExecuteThread(Object arg) in c:\inetpub\wwwroot\nyegeo\Controllers\BaseController.cs:45
[AggregateException: One or more errors occurred.]

runnerDotNet.BaseController.ExecuteCore() in c:\inetpub\wwwroot\nyegeo\Controllers\BaseController.cs:32

System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +91

System.Web.Mvc.<>cDisplayClassb.<BeginProcessRequest>b5() +60

System.Web.Mvc.Async.<>cDisplayClass1.<MakeVoidDelegate>b0() +25

System.Web.Mvc.<>cDisplayClasse.<EndProcessRequest>bd() +36

System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +651

System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +220

System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +134
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.8.4075.0

I can see two totally different issues here.

1. You are not properly switching to another connection
   
2. The network is being down
   If you are concerned about the second scenario - wrap your code into try ... catch block to capture the error so you can show a meaningful error message to the end user.

For info here is what found to work... bear in mind I'm switching from Db2 ( primary DB ) over to Sql Server ( secondary read only )
sqlcmd = "select count(*) as hits from (select sampleid,method from dbo.nye_result where processed = 0 group by sampleid,method ) a ;" ;
// switch over to Sql Server

DB.SetConnection("ODBC:lims_test");
try{
// gotta put all the variable declarations withing the try for db stuff

// if not, won't work !!!! go figure...

dynamic rs2 = DB.Query(sqlcmd);

dynamic record;

// -------------------------------------------
record = rs2.fetchAssoc();
if(record ) {

hits = record["hits"];

XSession.Session["numassays"]=hits ;

}

}

// gotta catch something, if not won't work... gasp... but examples all show can do it without catching anything...

catch(Exception e) {
nosqlsrvr="yes" ;

XSession.Session["nosqlsrvr"] = nosqlsrvr;

nosqlsrvr_count = XSession.Session["nosqlsrvr_count"] ;

XSession.Session["nosqlsrvr_count"] = nosqlsrvr_count ;

}
// switch back to primary connection aka DB2 ...

DB.SetConnection("");
this was done in the after successful login event
so that when hit the menu before display event check the XSession.Session["nosqlsrvr"] = "yes" and if so display pop up for such, etc...
Best

Mark