AndyJarrett

Socket IP and Port test to see if a DB is there

I've recently been having problems with a MSSQL server going down. The SQL box is geographically in a different place to the CF server so the datasrouces are connecting remotely. All has been ok until recently. The MSSQL server has been playing up (due to a driver problem we think) and rebooting. When this happens CF is hanging. Probably because of open connections? I know i can play around with the Datasource settings and set up connection limits etc. Kola on the CF-Talk list even suggested removing the datasource via the admin API - though this would work was a little too radical for what i wanted to do. All i wanted to do was check the SQL server was there before the page loaded without using the datasource. Thats when i thought about sockets, and after a little Googling I came across Aaron Johnson's post about using Sockets in CFMX. After a little tweaking this is what i've came up with:
<cfset serverStruct = structNew()/>

<!--- The server IP you want to check --->
<cfset serverStruct.serverIp = "127.0.0.1"/>
<!--- The port you want to check. In our case MSSQL --->
<cfset serverStruct.serverPort = "1433"/>
<!--- The response --->
<cfset serverStruct.portConnected = ""/>


<!--- Create the socket object --->
<cfset objSocket = CreateObject("java", "java.net.Socket") />


<!--- Now we try to connect to our port --->
<cftry>
   <!--- call the init constructor and connect to the IP and Port --->
   <cfset objSocket.init(serverStruct.serverIp, serverStruct.serverPort)/>
   <!--- Did we connect? --->
   <cfset serverStruct.portConnected = objSocket.isConnected()/>
   <!--- Close connection --->
   <cfset objSocket.close()/>   
<cfcatch type="any">
   <!--- The connection wasnt made --->
   <cfset serverStruct.portConnected = "No"/>
</cfcatch>
</cftry>

<cfoutput>Is the server and port there : #serverStruct.portConnected#</cfoutput>

<cfdump var="#serverStruct#"/>
The only weird thing here, and as i've only be been up an hour i havent looked into more (wheres my coffe), is that if the socket cannot connect CFMX bombs out. Hence the try and catch block. I was more expecting the isConnected() constructro to come back with a negative? Odd but i'll look into later.