check if oracle database is available on asp pages

I am using classic ASP to query an oracle database. On the weekends, backups take place for oracle database and the oracle system is unavailable. When this happens, asp pages stop working. .htm pages work, but not .asp.

I have the standard error checking code:

If Err.Number <> 0 Then
            s_error_msg =      "Error occured while opening the database." & Err.Description & "."
                  Response.Write(s_error_msg & "<br><br>")
                  Response.End()
            End if

I have tested and this works for my access databases, but not my oracle database.

I have been searching google non-stop. Does anyone have any ideas.

I found this:http://www.experts-exchange.com/Database/Oracle/8.x/Q_24466566.html?sfQueryTermInfo=1+10+30+asp+oracl

but that is sporadic. This is a sure thing every time the oracle database goes down.

I appreciate all the help.
jocelynzilchAsked:
Who is Participating?
 
sammySeltzerConnect With a Mentor Commented:
<%
            'Enter entire Oracle connection string and test to ensure code is working before testing for db being down.
           
            Set o_conn = Server.CreateObject("ADODB.Connection")
            'o_conn.Open application("QUARTZ_INTERNET_PUBLIC_ConnectionString")
            'o_conn.Open application("SarAccess_ConnectionString")
            o_conn.Open application("TeamTrackProd_ConnectionString")
            'response.write(o_conn.State)
            'o_conn.ConnectionTimeout=15
           
           
              'Retrieves the distinct country that is used in s_sql2
             
              s_sql1 = " select * " & _
                           " from custom.cudw_quality_intranet " & _
                           " order by source_system, country "                                                                   
                                                                                 
          'Response.Write "DEBUG: " & s_sql1 & "<HR>"
          'Response.End
         
          Set o_Rs1 = o_conn.Execute(s_sql1)
             
         oops = False ' set to true if we get any error. We are doing this on the assumption that if db is down, you get server error.
         '200 means success server access.

      If o_Rs1.EOF Then  
            %>
            <span class=middletitle style="font-size: small; font-weight: bold;">
                  &nbsp;Sorry database is currently down for maintenance. Check back shortly </span>
            <%
            Else

     
         Do Until o_Rs1.EOF
           process infor from db here


            end if
           If http.Status <> 200 Then
        ' let users know server is down!!!
        Response.Write "ERROR: Status " & http.Status & " received from server."
        ' exit from the program
        oops = True
        Exit Do
    End If
     o_Rs1.MoveNext
  Loop
 
' tell server we are done with with collecting data!
 done = "DONE"
 If oops Then done = "ERROR" 'kick back to error message.
         
           
      %>

 
  sorry, didn't test because I don't have oracle but I modeled after a working code
0
 
sammySeltzerCommented:
hi jocelynzilch,

What exactly is the problem that you want to correct?

Do you want your asp page to return a certain message when the oracle db is down but it isn't doing so?

The server that Oracle runs on is still; just the db is down?

Please clarify
0
 
sammySeltzerCommented:
Please post your current connection string.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
I would actually like to see your code not just the connection string.

Can you post the code without posting any company sensitive information?
0
 
jocelynzilchAuthor Commented:
This is the home page of my intranet site, so when the oracle database is down for maintenance, I would like to have the page read that and then redirect to a safe htm page. Then my users could still access other applications.

@sammyseltzer; The entire system is down for backup at that time.  But there are also times when too much server load slows the system and is unresponsive, when that happens, my calls to the oracle database cannot be accepted. Making the asp page unaccessible. Nothing loads, the browser windows just keeps trying to access the page.

I have been trying with the following sample code

<%
On Error Resume Next
            
Set o_conn = Server.CreateObject("ADODB.Connection")
'o_conn.Open application("QUARTZ_INTERNET_PUBLIC_ConnectionString")  <oracle string>
o_conn.Open application("SarAccess_ConnectionString")                          <access string, no database>
'o_conn.Open application("TeamTrackProd_ConnectionString")                   <sql string, live database>
'response.write(o_conn.State)
'o_conn.ConnectionTimeout=15
            
            
If Err.Number = 0 And objConnection.Errors.Count = 0 Then
 
'OK to redirect
Response.Clear
Response.Redirect("http://www.yahoo.com")
 
End If


If Err.Number <> 0 Then
s_error_msg =      "Error occured while opening the database." & Err.Description & "."
Response.Write(s_error_msg & "<br><br>")
Response.End()
End if
            
'Retrieves the distinct country that is used in s_sql2
            
's_sql1 = " select * " & _
'" from custom.cudw_quality_intranet " & _
'" order by source_system, country "                                                                  
                                                                               
'Response.Write(s_sql1)
'Response.End
            
'Set o_Rs1 = o_conn.Execute(s_sql1)
      
%>


I have been trying different databases and combinations, as you can see above the "<>" identify which connectionstring is which.

0
 
slightwv (䄆 Netminder) Commented:
Can I assume the oracle connections are ODBC or are you using OLEDB or some other connection?

It's been a while since I've done any straight ASP but you might want to try 'select sysdate from dual' to actually see if the database is open instead of checking the return code of the open call itself.


0
 
sammySeltzerCommented:
This is what I use to perform the connection check:

      If o_conn.errors.count <> 0 Then
       response.redirect
      else  'Retrieves the distinct country that is used in s_sql2
                    your query goes here
      end if

You need just one. No need to use two error checks.

Besides, I am really not a big fan of on error resume.

All it does it hide the problem

Try this and let us know.

0
 
jocelynzilchAuthor Commented:
@slightvw  Correct, all the connections are ODBC. Well when the database is up, I do not have an issue. So I am a bit confused on what selecting sysdate from dual will do for me. How could I do that if the connection is not open?

@sammyseltzer  I will try that today, yours is a little different with the If...Else. I cannot test at this exact moment as it is my production system. I will be able to later in the day when there are not so many users on. I will update then.
0
 
slightwv (䄆 Netminder) Commented:
>>How could I do that if the connection is not open?

I gathered from your original post that the open connection was returning OK, not hitting "If Err.Number <> 0 Then", even thought the database was actually down.

Did I misread things?
0
 
sammySeltzerCommented:
Usually, Oracle db acts a little differently from sql server.

Reason is because of the tns.ora file where the connection pooling is set and also the listener files.

So, when the db is down, it acts as though the server is down.

0
 
jocelynzilchAuthor Commented:
@slightwv yes, the connectionstring is ok, when the database is working everything returns. but its like there is no error checking when the database is unavailable.

I think sammySeltzer helped explain it a little better.

I appreciate your help so far!
0
 
slightwv (䄆 Netminder) Commented:
>>but its like there is no error checking when the database is unavailable.

That's why I say you need to try a select to trap the actual database down error.  For whatever reason the o_conn.Open call is always returning OK even if the database is down.  To properly 'trap' the error and redirect, you can't rely on the OPEN call and need to try a select.
0
 
sammySeltzerCommented:
I can see where @slightway is coming from too.

Can you try this:

's_sql1 = " select * " & _
'" from custom.cudw_quality_intranet " & _
'" order by source_system, country "                                                                   
                                                                               
'Response.Write(s_sql1)
'Response.End
           
'Set o_Rs1 = o_conn.Execute(s_sql1)

If o_Rs1.EOF Then  'nothing is coming back from the db
%>
<span  class=middletitle style="font-size: small; font-weight: bold;">
      &nbsp;Maybe database is down
</span>
<%
Else
show your data
end if
%>

see what happens
0
 
slightwv (䄆 Netminder) Commented:
>>If o_Rs1.EOF Then

Close to what I was thinking...

The execute should error out so you'll never get to EOF.  I was thinking try the execute and trap/redirect there.

I used to have some OO4O (Oracle Objects for OLE) VBSCRIPT code that used to go out and 'ping' all of my databases and show 'OK', 'PROBLEM' for analysts.
0
 
jocelynzilchAuthor Commented:
I understand what you mean now @slightwv

I tried this code:

<%
            On Error Resume Next
            
            Set o_conn = Server.CreateObject("ADODB.Connection")
            o_conn.Open application("QUARTZ_INTERNET_PUBLIC_ConnectionString")
            'o_conn.Open application("SarAccess_ConnectionString")
            'o_conn.Open application("TeamTrackProd_ConnectionString")
            'response.write(o_conn.State)
            'o_conn.ConnectionTimeout=15
            
            
            'Retrieves the distinct country that is used in s_sql2
            
            's_sql1 = " select * " & _
            '             " from custom.cudw_quality_intranet " & _
            '             " order by source_system, country "                                                                  
                                                                               
            'Response.Write(s_sql1)
            'Response.End
            
            'Set o_Rs1 = o_conn.Execute(s_sql1)
            
      %>
<%
      If o_Rs1.EOF Then  
%>
<span  class=middletitle style="font-size: small; font-weight: bold;">
      &nbsp;Maybe database is down
</span>
<%
Else
%>
show your data
<%
end if
%>

as mentioned above.

If i used the SarAccess_connectionstring, i saw the 'Maybe database is down' message. Once I tried QUARTZ, the asp page hung again.

Did I apply the code correctly?
0
 
sammySeltzerCommented:
He wants you to trap the error before this code:

 'Set o_Rs1 = o_conn.Execute(s_sql1)

Not sure I agree with that because that's what executes the sql code but try it anyway.

BTW: Did you comment it out intentionally?
0
 
sammySeltzerCommented:
The other thing you could try, I am not sure how it will work in your situation is to try and take advantage of http status codes.

oops = False ' set to true if we get any error

    If http.Status <> 200 Then
        ' let users know server is down!!!
        Response.Write "ERROR: Status " & http.Status & " received from server."
        ' exit from the program
        oops = True
        Exit Do
    End If

Use it right after this:


'Set o_Rs1 = o_conn.Execute(s_sql1)
0
 
jocelynzilchAuthor Commented:
I commented it out as I just copied the code above.

I just tried this::

<%
            'On Error Resume Next
            
            Set o_conn = Server.CreateObject("ADODB.Connection")
            'o_conn.Open application("QUARTZ_INTERNET_PUBLIC_ConnectionString")
            'o_conn.Open application("SarAccess_ConnectionString")
            o_conn.Open application("TeamTrackProd_ConnectionString")
            'response.write(o_conn.State)
            'o_conn.ConnectionTimeout=15
            
      If o_Rs1.EOF Then  
            %>
            <span class=middletitle style="font-size: small; font-weight: bold;">
                  &nbsp;Maybe database is down
            </span>
            <%
            Else
            %>
            show your data
            <%
            end if
            
            
            'Retrieves the distinct country that is used in s_sql2
            
            s_sql1 = " select * " & _
                         " from custom.cudw_quality_intranet " & _
                         " order by source_system, country "                                                                  
                                                                               
            'Response.Write(s_sql1)
            'Response.End
            
            
            
            Set o_Rs1 = o_conn.Execute(s_sql1)
            
      %>

but I receive this error message



Microsoft VBScript runtime error '800a01a8'

Object required: ''

/test/phil/db3.asp, line 303

303 is the If statement.

I will try the status codes tomorrow.
0
 
sammySeltzerCommented:
You got that error because you moved this code down:

 Set o_Rs1 = o_conn.Execute(s_sql1)

In doing, it could no longer find the existence of o_Rs1.

 Set o_Rs1 = o_conn.Execute(s_sql1) needs to be run before the if...statement.

0
 
slightwv (䄆 Netminder) Commented:
>>He wants you to trap the error before this code:

Not my intent.  I wanted to trap immediately AFTER for the reason you posted.

I'll see if I can get some time later today to create a working example.
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
WOW... that brought back a lot of memories (and not all fond memories).

I finally found my old classic ASP OO4O code but it looked a little complicated for what you are after.

I think the main problem here is how classic ASP handled "Err".  You need to make sure you check immediately after the command in question.  A response.write will reset it.

I found an OLD XP Virtual machine I had laying around with and old Oracle 9i ODBC client on it and IIS configured.  Borrowed sammySeltzer's 'oops' variable and concept and here you go...

I tested the following code with that configuration connecting to a 10.2.0.3 database on another dev server.

Leaving the Windows Oracle Service running and just shutting down the database it seems to work.


<%@ Language=VBScript %>

<%
   oops=false

   On Error Resume Next

   Set o_conn = Server.CreateObject("ADODB.Connection")
   o_conn.Open("DSN=ODBC_DSN;Uid=User;Pwd=Pass")
   If Err.Number <> 0 Then oops=true End If


   If oops Then
      s_error_msg = "<br/>Error occured while opening the database: " & Err.Description & "."
      Response.Write(s_error_msg)
   Else
      Response.Write("<br/>OK to redirect.") 
   End If

         
%>

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>> s_error_msg = "<br/>Error occured while opening the database: " & Err.Description & "."
     

If you  have code in the middle, you'll want to capture the error description in the 'If' above as well as setting 'oops'...
0
 
sammySeltzerCommented:
@slightwv,

That looks really good.

Job job!
0
 
slightwv (䄆 Netminder) Commented:
>>That looks really good

*blushes* Thanks! :)

It took a few moments to remember how to go back to VBScript from VB.Net...
0
 
sammySeltzerCommented:
same with me.
good news I have jus completed converting a classic asp app to vb.net and that was a nice classic asp refresher.
0
 
slightwv (䄆 Netminder) Commented:
"convert"?  sounds like it was probably more of a complete rewrite...
0
 
jocelynzilchAuthor Commented:
Wow!!!! That is amazing.

I will test it tonight.

Thank you in advance for your hard work slightwv!

I will update this after I test.
0
 
slightwv (䄆 Netminder) Commented:
No problem and it wasn't all that hard.  You two had most of it already!!!  Assuming it works, I just put the few missing puzzle pieces together...  

I was lucky to find a REALLY old VM that had everything already installed that I needed.  Some times it pays to keep old files around!!!
0
 
jocelynzilchAuthor Commented:
@slightwv It really is amazing what keeping things around will do!

I think we are on to something!

I did get an error message when testing with the database down. Which is awesome. The asp pages are still available.

"Error occured while opening the database: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified."

This was a great test and i will have the real one Sunday.

I really think it was due to the way I had the connectionstring. The syntax you gave above is different and that seems to be working. I put my old string: o_conn.Open application("QUARTZ_INTERNET_PUBLIC_ConnectionString")

in and I had the same error. Looks like I am gonna have some work ahead of me!

I will let you guys know how it goes Sunday morning.
0
 
jocelynzilchAuthor Commented:
sorry for the delay in responding. I had to wait to test.

Well we did and we found something else.....

It seems that IE works ok, but firefox does not. I always use firefox and when my initial issue happens, the server hangs for a second then is ok.

I tested this tonight. The other odd thing is that .aspx pages work fine.

I will close this one up once I know how to split up the points between both of you.

Thanks for your help!
0
 
slightwv (䄆 Netminder) Commented:
Glad everything, except the FF glitch, worked.

>>I will close this one up once I know how to split up the points between both of you.

You can click the 'Accept Multiple Solutions' link and divide up the points as you see fit.
0
 
jocelynzilchAuthor Commented:
This was very helpful. I appreciate both slightwv and sammySeltzer's help and code
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.