Solved

check if oracle database is available on asp pages

Posted on 2010-09-21
32
661 Views
Last Modified: 2013-12-19
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.
0
Comment
Question by:jocelynzilch
  • 12
  • 11
  • 9
32 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33731113
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
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33731177
Please post your current connection string.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33734739
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
 

Author Comment

by:jocelynzilch
ID: 33735052
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33735179
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
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33735356
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
 

Author Comment

by:jocelynzilch
ID: 33735566
@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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33735804
>>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
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33735927
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
 

Author Comment

by:jocelynzilch
ID: 33737877
@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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33738111
>>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
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33738503
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33738533
>>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
 

Author Comment

by:jocelynzilch
ID: 33740510
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
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33740611
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
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33740649
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:jocelynzilch
ID: 33740782
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
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33740873
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33743754
>>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
 
LVL 28

Accepted Solution

by:
sammySeltzer earned 25 total points
ID: 33745386
<%
            '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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 25 total points
ID: 33746446
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33746459
>> 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
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33747038
@slightwv,

That looks really good.

Job job!
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33747091
>>That looks really good

*blushes* Thanks! :)

It took a few moments to remember how to go back to VBScript from VB.Net...
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33747217
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33747242
"convert"?  sounds like it was probably more of a complete rewrite...
0
 

Author Comment

by:jocelynzilch
ID: 33748577
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33749192
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
 

Author Comment

by:jocelynzilch
ID: 33750727
@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
 

Author Comment

by:jocelynzilch
ID: 33785230
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33788229
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
 

Author Closing Comment

by:jocelynzilch
ID: 33822713
This was very helpful. I appreciate both slightwv and sammySeltzer's help and code
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now