Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
Solved
check if oracle database is available on asp pages
Posted on 2010-09-21
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
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.
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
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
12-
11
-
9
32 Comments
Active today
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
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
Active today
I would actually like to see your code not just the connection string.
Can you post the code without posting any company sensitive information?
Can you post the code without posting any company sensitive information?
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
'o_conn.Open application("QUARTZ_INTERN
o_conn.Open application("SarAccess_Con
'o_conn.Open application("TeamTrackProd
'response.write(o_conn.Sta
'o_conn.ConnectionTimeout=
If Err.Number = 0 And objConnection.Errors.Count
'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
Response.End()
End if
'Retrieves the distinct country that is used in s_sql2
's_sql1 = " select * " & _
'" from custom.cudw_quality_intran
'" 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.
@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
'o_conn.Open application("QUARTZ_INTERN
o_conn.Open application("SarAccess_Con
'o_conn.Open application("TeamTrackProd
'response.write(o_conn.Sta
'o_conn.ConnectionTimeout=
If Err.Number = 0 And objConnection.Errors.Count
'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
Response.End()
End if
'Retrieves the distinct country that is used in s_sql2
's_sql1 = " select * " & _
'" from custom.cudw_quality_intran
'" 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.
Active today
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.
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.
Active today
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.
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.
@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.
@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.
Active today
>>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?
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?
Active today
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.
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.
@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!
I think sammySeltzer helped explain it a little better.
I appreciate your help so far!
Active today
>>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.
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.
Active today
I can see where @slightway is coming from too.
Can you try this:
's_sql1 = " select * " & _
'" from custom.cudw_quality_intran
'" 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;">
Maybe database is down
</span>
<%
Else
show your data
end if
%>
see what happens
Can you try this:
's_sql1 = " select * " & _
'" from custom.cudw_quality_intran
'" 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;">
Maybe database is down
</span>
<%
Else
show your data
end if
%>
see what happens
Active today
>>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.
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.
I understand what you mean now @slightwv
I tried this code:
<%
On Error Resume Next
Set o_conn = Server.CreateObject("ADODB
o_conn.Open application("QUARTZ_INTERN
'o_conn.Open application("SarAccess_Con
'o_conn.Open application("TeamTrackProd
'response.write(o_conn.Sta
'o_conn.ConnectionTimeout=
'Retrieves the distinct country that is used in s_sql2
's_sql1 = " select * " & _
' " from custom.cudw_quality_intran
' " 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;">
Maybe database is down
</span>
<%
Else
%>
show your data
<%
end if
%>
as mentioned above.
If i used the SarAccess_connectionstring
Did I apply the code correctly?
I tried this code:
<%
On Error Resume Next
Set o_conn = Server.CreateObject("ADODB
o_conn.Open application("QUARTZ_INTERN
'o_conn.Open application("SarAccess_Con
'o_conn.Open application("TeamTrackProd
'response.write(o_conn.Sta
'o_conn.ConnectionTimeout=
'Retrieves the distinct country that is used in s_sql2
's_sql1 = " select * " & _
' " from custom.cudw_quality_intran
' " 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;">
Maybe database is down
</span>
<%
Else
%>
show your data
<%
end if
%>
as mentioned above.
If i used the SarAccess_connectionstring
Did I apply the code correctly?
Active today
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?
'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?
Active today
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)
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)
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
'o_conn.Open application("QUARTZ_INTERN
'o_conn.Open application("SarAccess_Con
o_conn.Open application("TeamTrackProd
'response.write(o_conn.Sta
'o_conn.ConnectionTimeout=
If o_Rs1.EOF Then
%>
<span class=middletitle style="font-size: small; font-weight: bold;">
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_intran
" 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.
I just tried this::
<%
'On Error Resume Next
Set o_conn = Server.CreateObject("ADODB
'o_conn.Open application("QUARTZ_INTERN
'o_conn.Open application("SarAccess_Con
o_conn.Open application("TeamTrackProd
'response.write(o_conn.Sta
'o_conn.ConnectionTimeout=
If o_Rs1.EOF Then
%>
<span class=middletitle style="font-size: small; font-weight: bold;">
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_intran
" 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.
Active today
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.
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.
Active today
>>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.
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.
Active today
<%
'Enter entire Oracle connection string and test to ensure code is working before testing for db being down.
Set o_conn = Server.CreateObject("ADODB
'o_conn.Open application("QUARTZ_INTERN
'o_conn.Open application("SarAccess_Con
o_conn.Open application("TeamTrackProd
'response.write(o_conn.Sta
'o_conn.ConnectionTimeout=
'Retrieves the distinct country that is used in s_sql2
s_sql1 = " select * " & _
" from custom.cudw_quality_intran
" 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;">
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
'Enter entire Oracle connection string and test to ensure code is working before testing for db being down.
Set o_conn = Server.CreateObject("ADODB
'o_conn.Open application("QUARTZ_INTERN
'o_conn.Open application("SarAccess_Con
o_conn.Open application("TeamTrackProd
'response.write(o_conn.Sta
'o_conn.ConnectionTimeout=
'Retrieves the distinct country that is used in s_sql2
s_sql1 = " select * " & _
" from custom.cudw_quality_intran
" 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;">
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
Active today
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.
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
%>
Active today
>> 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'...
If you have code in the middle, you'll want to capture the error description in the 'If' above as well as setting 'oops'...
Active today
>>That looks really good
*blushes* Thanks! :)
It took a few moments to remember how to go back to VBScript from VB.Net...
*blushes* Thanks! :)
It took a few moments to remember how to go back to VBScript from VB.Net...
Active today
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.
good news I have jus completed converting a classic asp app to vb.net and that was a nice classic asp refresher.
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.
I will test it tonight.
Thank you in advance for your hard work slightwv!
I will update this after I test.
Active today
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!!!
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!!!
@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_INTERN
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.
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_INTERN
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.
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!
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!
Featured Post
The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Configuring and using Oracle Database Gateway for ODBC
Introduction
First, a brief summary of what a Database Gateway is. A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
- Acronis
- Databases
- Oracle Database
- MongoDB
- Oracle
- *Backup Software
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility. The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Course of the Month11 days, 4 hours left to enroll
719 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.