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! | You'll get answers to many questions and more from David Barnes, Lead Evangelist for IBM Emerging Internet Technologies. David will discuss aspects of Web 2.0 that bring value to corporations, academia, and government. He'll also discuss IBM's vision around Web 2.0, including the importance of remixability and consumability. The discussion will culminate with examples of various IBM Software Group solutions you can use to get ahead of the Web 2.0 adoption curve. FREE! Go There Now!
| | | | This whitepaper presents the benefits of successfully introducing static analysis into your organization using IBM Rational Software Analyzer. Additionally, it identifies some common pitfalls that can hinder the effective use of static analysis tooling as well as presents 10 simple strategies designed to help you quickly realize the value of static analysis using Rational Software Analyzer. FREE! Go There Now!
| | | | Learn how you can extend modern application lifecycle management to IBM System z through the IBM Rational Software Delivery Platform (SDP). The Did you say mainframe? e-kit includes podcasts, webcasts, tutorials, white and red papers, demos, and articles designed to help ease the challenges of modernizing your enterprise. This complimentary kit for mainframe developers is a practical, how-to guide for making the most of an existing development environment, including the skills and infrastructure already in place at an established enterprise. FREE! Go There Now!
| | | | Discover how IBM Rational AppScan Standard Edition can help you detext vulnerabilities in your web applications in the Web Application Security eKit. IBM Rational AppScan is a leading suite of automated web application security solutions that scan and test for common Web application vulnerabilities. The new Web Application Security eKit provides you with valuable resources, including white papers, demos, and additional information on the benefits of testing your Web applications. FREE! Go There Now!
| | | | Download a free trial version of IBM Rational Software Analyzer Developer Edition V7.0 to identify bug defects earlier in the software development cycle. Rational Software Analyzer is an extensible software development solution that reduces the expense of bug-fixes by enabling static analysis code reviews and bug identification very early in the development cycle. FREE! Go There Now!
| | | | Join us for this web seminar to learn how you can defend your web applications from attack. Learn about the 3 most common web application attacks, including how they occur and what can be done to prevent them. We’ll also discuss manual versus automated approaches for scanning and identifying web application vulnerabilities and how IBM Rational AppScan, an automated vulnerability scanner, can help you automate more of what you are doing manually today. FREE! Go There Now!
| | | | As systems increase in complexity, communication between systems and software teams becomes more and more difficult. Now, there’s a way to improve product quality and communication.<br />Read the “Model Driven Systems Development” white paper to see how. Also included in this kit are more educational white papers, customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems.<br /> FREE! Go There Now!
| | | | Regression testing -- in which code is thoroughly tested to ensure that changes have not produced unexpected results -- is an important part of any development process. But many testing environments neglect the terminal-based applications that still form the backbone of many industries. In this tutorial, you'll learn how the Rational Functional Tester Extension for Terminal-Based Applications works with other Rational Functional Tester to help test terminal-based applications quickly and easily. FREE! Go There Now!
| | | | Whether you are creating new applications or modifying existing ones, managing integration of new components with traditional z/OS elements is a critical part of building and deploying modern applications. Listen to this webcast to see how IBM can help you optimize your development process using an IDE like Rational Developer for System z that integrates with management tools, such as ClearCase to manage your application development on mainframes. FREE! Go There Now!
| | | | The Eclipse community is constantly working to extend Eclipse's functionality. In this webcast, learn about some of the most important and feature-rich projects under development. From multi-language support to plug-in development, tune in to see what Eclipse is capable of now. FREE! Go There Now!
| | | | All FREE IBM® developerWorks Tools! | |