DB Tables Backup using ASP, OO4O and Oracle's Export Utility
by Jenny Corpus
This is a simple ASP program that allows the user to create
a backup copy of a DB user's tables. It uses Oracle Objects for OLE (OO4O)
for database access to Oracle and for the backup function, Oracle's
Export Utility program is used.
This code assumes that the web server has the following installations:
1.Oracle Net8
2.Oracle Object for OLE
Here is the code:
'************** Start**********************'
File: Export.asp
<%@ Language=VBScript %>
<%Option Explicit%>
<%
dim OraDatabase 'Oracle Database object
dim OraDynaset 'Oracle Dynaset object
dim sExpPath 'physical path of Export program in the web server
dim sExpFileNm 'physical path and file name of the Export file to be created
'physical path must be existing in the web server
sExpPath = "d:\oracle\ora817\bin\exp.exe" 'set the location of the export program
sExpFileNm = "d:\Test\EXPDAT.DMP" 'set the name of the Export file to be created
'Main Procedure
Sub Main()
if Request.Form("hidSubmit")="1" then 'Check if the form is submitted
Call DoExport() 'Execute Export program
end if
Call Header() 'Write the Header
Call Body() 'Write the Body
Call Footer() 'Write the Footer
End Sub
'Export Procedure
Sub DoExport()
Dim nCount 'number of rows
Dim nCtr 'counter for the loop
Dim sTabList 'list of selected Tables to be exported
Dim sTabNm 'selected Table Name
nCount = int(Request.Form("hidCount")) 'Get the total number of rows
'Check each checkbox of it is selected
For nCtr=1 to nCount
if Request.Form("chkExport" & nCtr) = "on" then 'Check if checkbox is checked
sTabNm = Request.Form("hidTabNm" & nCtr) 'Get the selected Table Name
sTabList = sTabList & "," & sTabNm 'Place selected name in the list of tables to be exported
end if
next
sTabNm = Right(sTabList, Len(sTabList) - 1) 'Remove the comma at the start of the list
Call ExportTables(sTabList) 'Export the tables
End Sub
'********************************
'Execute the Export program
Sub ExportTables(sTabNm)
dim wsh 'WScript object
dim sCommand 'Export command
dim sConnection 'DB connection string
'Make the DB Connection string
sConnection = AppDic("UserName") & "/" & AppDic("Password") & "@" & AppDic("DBName")
'Create the Export command
sCommand = sExpPath & " " & sConnection & " tables=" & sTabNm & " File=" & sExpFileNm
'Create the WScript Object
set wsh = server.createobject("WScript.Shell")
'Run the Export command
wsh.run sCommand
End Sub
'*********************************
'Writes the HTML Header
Sub Header()
%>
<HTML>
<HEAD>
<meta HTTP-EQUIV="Content-Type" content="text/html; charset=iso-8859-1">
</HEAD>
<SCRIPT language=javascript>
//Checks or Unchecks all the checkboxes
function CheckAll()
{
var nCount;
var sChkName;
var nCheck
nCount = document.forms[0].hidCount.value;
if (document.forms[0].btnCheck.value=="Check")
{
nCheck=1;
document.forms[0].btnCheck.value="UnCheck";
}
else
{
nCheck=2;
document.forms[0].btnCheck.value="Check";
};
for (var i=1; i <= nCount; i++)
{
sChkName="chkExport" + i;
if (nCheck==1)
{
document.forms[0].item(sChkName).checked=true;
}
else
{
document.forms[0].item(sChkName).checked=false;
};
};
};
//submit form
function Export()
{
document.forms[0].hidSubmit.value=1;
document.forms[0].method='POST';
document.forms[0].submit();
};
</SCRIPT>
<BODY>
<%
End Sub
'Retrieve tables of user from DB
Function GetResults
Dim sqlStatement
' Get the reference to the OraDatabase object from the pool
set OraDatabase = OraSession.getDatabaseFromPool(10)
if Oradatabase is nothing then 'Check if connection is successful
GetResults=-1
Exit Function
else
'Make the select statement
sqlStatement = "SELECT table_name, tablespace_name FROM user_tables "
'Execute the select statement, store it in the Dynaset
set OraDynaset = OraDatabase.CreateDynaset(sqlStatement, cint(0))
if OraDynaset.RecordCount < 1 then 'Check if there are records retrieved
GetResults=-2
Exit Function
else
GetResults=1
End if
end if
End Function
'Displays error message on the HTML
Sub DisplayError(nError)
Dim strWrite
Select Case nError
Case -1
strWrite="No Connection."
Case -2
strWrite="No records retrieved"
end select
Response.Write strWrite
End Sub
'Displays the retrieved rows in an HTML table
Sub DisplayTable()
dim strWrite 'string to write
dim nCount 'Number of rows retrieved
%>
<FORM Method=POST >
<TABLE border=1>
<TR>
<TD><INPUT type='button' name="btnCheck" value="Check" onclick="Javascript:CheckAll()"></TD>
<TD nowrap> Table Name </TD>
<TD nowrap> Tablespace Name </TD>
</TR>
<%
nCount = 0
Do Until OraDynaset.EOF
nCount = nCount + 1
strwrite = "<TR>"
strwrite = strwrite & "<TD nowrap><INPUT type='hidden' name='hidTabNm" & nCount &"' value=" & OraDynaset.Fields("table_name") & ">"
strwrite = strwrite & "<INPUT type='checkbox' name='chkExport" & nCount & "' ></TD>"
strwrite = strwrite & "<TD nowrap>" & OraDynaset.Fields("table_name") & "</TD>"
strwrite = strwrite & "<TD nowrap>" & OraDynaset.Fields("tablespace_name") & "</TD>"
strwrite = strwrite & "</TR>" & chr(13) & chr(10)
Response.Write strWrite
OraDynaset.MoveNext
Loop
%>
</TABLE>
<BR>
<INPUT type='button' name='btnExport' value="Export" onclick="JavaScript:Export()">
<INPUT type='hidden' name='hidSubmit' value=0>
<INPUT type='hidden' name='hidCount' value=<%=nCount%> >
</FORM>
<%
End Sub
'Displays the Body of the HTML
Sub Body()
Dim nRet 'Return value from GetResults(); 1=OK, -1 or -2=ERROR
'Get the rows from DB
nRet=GetResults()
If nRet <> 1 then 'Check if there is an error
Call DisplayError(nRet)
else
Call DisplayTable
end if
End Sub
'Writes the Footer of the HTML
Sub Footer()
'Set the Oracle objects to nothing
Set OraDatabase=Nothing
Set OraDynaset=Nothing
%>
</BODY>
</HTML>
<%
End sub
'****START of server-side procedures****
Call Main()
%>
'************** End********************'
'************** Start**********************'
'File: Global.asa
<OBJECT RUNAT="Server" SCOPE="Application" ID="AppDic" PROGID="Scripting.Dictionary"></OBJECT>
<OBJECT RUNAT=Server SCOPE=Application ID=OraSession PROGID="OracleInProcServer.XOraSession"></OBJECT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
AppDic.Add "DBName","orcl" 'Registered service name in the SQL Net
AppDic.Add "UserName","scott" 'User name
AppDic.Add "Password","tiger" 'Password
OraSession.CreateDatabasePool 20, 100, 600, AppDic("DBName"), AppDic("UserName") & "/" & AppDic("Password"), 0
End Sub
Sub Application_OnEnd
'Destroys global pool of connections.
OraSession.DestroyDatabasePool
End Sub
</SCRIPT>
|
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More ASP Code Articles
More By aspfree  developerWorks - FREE Tools!
|
Poor Requirements Management capabilities in an Enterprise have been linked to excessive project failures, escalating IT costs, and failure to deliver competitive advantage into the marketplace. Join Brianna M Smith from IBM Rational and learn about how successful organizations align IT and Business stakeholders through collaborative processes and tools for effective requirements management, and how an integrated approach across the IT lifecycle can provide unparalleled visibility and traceability to ensure that project teams are delivering on the business vision by "doing the right things" and "doing things right."
FREE! Go There Now!
| | | |
Join us for this on demand webcast to learn about developing complex systems more quickly and efficiently. We'll cover market drivers for developing, governing and reusing systems software assets and how you can develop system software assets with Rational Asset Manager.
FREE! Go There Now!
| | | |
Download a free trial version of IBM DB2 9.5 for Linux, UNIX, and Windows. DB2 9 is the result of a five-year development project that transformed traditional (static) database technology into an interactive data server that merges the high performance and ease of use of DB2 with the self-describing benefits of XML.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download the latest trial version of IBM Data Studio V1.1 at no cost. IBM Data Studio is a comprehensive data management solution that helps you effectively design, develop, deploy and manage your data, databases, and database applications throughout the data management life cycle utilizing a consistent and integrated user interface. Unlike other client-side data management solutions that focus on only one aspect of the application lifecycle or database administration, Data Studio complements the Rational Software Delivery platform, providing unparalleled flexibility for a heterogeneous data server environment across platforms.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download a free trial version of IBM Rational Business Developer V7.1. Rational Business Developer offers rapid and simplified development of business applications and services through Enterprise Generation Language (EGL) tools, generating Java or mainframe solutions while shielding developers from technical complexities.
FREE! Go There Now!
| | | |
IBM Enterprise Modernization solutions help organizations evolve core IT systems towards modern architectures and technologies—reducing the burden of maintenance and freeing up resources to develop new business requirements and capabilities. With the IBM Enterprise Modernization Sandbox for System z you can evaluate IBM Enterprise Modernization solutions focused on five key areas: Assets, Architectures, Skills, Processes and Infrastructures, and Investment. Each solution is based upon real customer experiences and offers a proven path to get you started with your modernization projects.
FREE! Go There Now!
| | | |
Rational Build Forge Express Edition is an automation framework that packages the latest enterprise-grade technologies into a reliable, flexible and robust configuration designed and priced specifically for small to midsize businesses. The new Rational Build Forge Express eKit provides you with valuable resources – including a case study, podcast, demo, and articles – to help you increase staff productivity, compress development cycles and deliver better software, fast.
FREE! Go There Now!
| | | |
This whitepaper provides areas to consider when evaluating any software configuration management solution. It addresses how the IBM solutions (Rational ClearCase and Rational ClearQuest) meet the needs and requirements of both project leaders and developers to provide successful Software Change and Configuration Management.
FREE! Go There Now!
| | | |
As organizations have grown increasingly dependent on online software, the risk of malicious attacks has also become far more serious. Fortunately, well-governed organizations can protect their Web applications by injecting vulnerability assessments and ethical hacks into their software development and delivery processes. This paper describes 12 of the most common hacker attacks and provides basic rules that you can follow to help create more hack-resistant Web applications.
FREE! Go There Now!
| | | |
Join this Rational Talks to You teleconference, to hear how Enterprise Generation Language (EGL) eliminates the need for tedious and error-prone low level coding, so developers can focus on business requirements. EGL extends the Rational software development platform with a simplified programming language that enables developers who have little or no experience with Java, Web technologies or Service Oriented Architecture, to create enterprise-class applications and services quickly and easily. It also allows developers who may have little or no mainframe programming experience to quickly create traditional mainframe components.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
/ 2 
