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! | <a href="http://zeus.developershed.com/shonuff.php?blackbird=3853&zoneid=442&source=&dest=http%3A%2F%2Fwww.ibm.com%2Fdeveloperworks%2Fspaces%2Fjazz%3FS_TACT%3D105AGY31%26S_CMP%3DDEVSHED&ismap="><img src="http://images.devshed.com/corp/img/news/jazz01.gif" alt="developerWorks Jazz space" align="left"></a>You've heard the buzz about Jazz... want to know more about it from a developer's perspective? Check out the Jazz space on developerWorks. This space is an up-to-date resource for developers, including technical information about Jazz and products built on Jazz, like Rational Team Concert Express. The Jazz space includes content from a wide variety of sources, including links, feeds, and comments from experts. FREE! Go There Now!
| | | | Hear how IBM Rational Project and Portfolio Management integrated solutions help teams put the right tools and processes in place to maximize the effectiveness and efficiency of project teams and ensure that the business vision is being executed correctly. Learn how to automate and integrate requirements prioritization, top-down project planning, communications and controls, and methodology deployment to keep your scope, costs, and schedules under control. Tackle with an end-to-end approach the management of scope and scope changes, usage of methodology to control and empower project teams, and optimization of resources to align activity costs with the overall project plan. 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!
| | | | Download the Rational Application Developer (RAD) v7.5 open beta code and start developing applications for the JEE5 standard which features EJB3.0, JPA, JSF 1.2, JSP 2.1 and Servlet 2.5 standards. When you use this beta you will see how you can increase developer productivity for already existing applications with improved support for refactoring, as well as adding new features to existing applications. In addition, the beta provides tooling for JD Edwards, Oracle, SAP, Siebel and PeopleSoft to improve the developer productivity with these enterprise systems. FREE! Go There Now!
| | | | In this tutorial, you can learn how to install and configure the IBM Rational Asset Manager Eclipse client, explore the different views in the Asset Management perspective, learn various search techniques, work with existing assets, and submit a new asset. FREE! Go There Now!
| | | | Learn how Rational Build Forge can extend a simple compile and package build process by adding customization and deployment capability. Go from a manual method to automating: checking for code changes; getting the latest source; compiling and packaging; customizing; copying to and restarting a deployment server; and sending e-mail notification that a new version is available. FREE! Go There Now!
| | | | XML has become a common way of storing business data as flat files and many data server vendors including IBM have provided ways to store this data within relational database systems. Increasingly collections of XML files are accessed like databases using an xQuery and other XML standard mechanisms. Businesses find the need to combine the traditional tabular structured data with XML formatted data. In this webcast, you’ll learn about IBM’s WebSphere Federation Server technology, which provides users with the ability to integrate these two data formats. FREE! Go There Now!
| | | | Portfolio Management is about effectively managing portfolio value by aligning portfolio investments with business goals. This complimentary e-kit provides a collection of materials that can help you understand how IBM Rational enables and automates best practices for improved governance and clear visibility into portfolio and project performance across the entire IT project lifecycle. FREE! Go There Now!
| | | | Visit IBM developerWorks to try the IBM SOA Sandbox for people. The SOA Sandbox for people provides a trial environment with the necessary tooling and components required to enable consistent human and process interaction and collaboration, showing how you can improve user experience and business productivity. FREE! Go There Now!
| | | | With IBM Rational Systems Development Solution, you can deliver products faster with higher quality. Within this kit, Read the “Model Driven Systems Development” white paper to see how to improve product quality and communication. Then check out the rest of the e-Kit to learn more about important topics that can affect the success of any software project through customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems. From start to finish, at every stage in your projects, Rational Systems Development Solution can help your company reach its full potential. FREE! Go There Now!
| | | | All FREE IBM® developerWorks Tools! | |