Solved

Creating a partial backup of a sybase database using classic asp

Posted on 2007-11-22
5
341 Views
Last Modified: 2012-06-21
Hi,

I have a database that contains information on lots of different customers, each customer has a customer ID in the databse. I want a script/query that will backup all records for a certain customer into csv/access/excel format.

How would I go about doing this?
0
Comment
Question by:synergiq
[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
5 Comments
 
LVL 12

Expert Comment

by:R_Harrison
ID: 20335391
I can see a couple of options, you could create a new Access database on your website, then copy records into a table and provide a hyperlink to download the Access table - after download you could then delete the records or even drop the table so it is ready for the next backup.

The other option is to output the records into en Excel spreadsheet, this is easy however not sure how this fits into with your Backup routine.   An easy way to output records into excel is to simply add:

Response.AddHeader "Content-Disposition","attachment;filename=myexcelreport.xls"

to the top of your asp page, then you use a table to hold the data from your database and excel will treat each cell in your table as a cell on an excel spreadsheet. example below.

<%
Response.AddHeader "Content-Disposition","attachment;filename=myexcelreport.xls"
%>
<table>
<tr>
<td>name from record 1 in db</td>
<td>address from record 1 in db</td>
<td>other data from record 1 in db</td>
</tr>
<td>name from record 2 in  db</td>
<td>address from record 2 in db</td>
<td>other data from record 2 in db</td>
</tr>
</table>

Obvisouly you will need to get the database to write the html code using response.write, but it is fairly straightforward.
0
 
LVL 10

Expert Comment

by:effx
ID: 20335575
You could use a script that will do the output to all the formats you want just using QueryString variables for example:

http://www.yoursite.com/convertor.asp?type=access
http://www.yoursite.com/convertor.asp?type=excel
http://www.yoursite.com/convertor.asp?type=csv

the code would be something like :
<%
Output_Dump = Request.QueryString("type")
Select Case Output_Dump
	Case "access"
		Start_Tbl = "<table>"
		Start_Row = "<tr>"
		Start_Data = "<td>"
		End_Data = "</td>"
		End_Row = "</tr>"
		End_Tbl = "</table>"
	Case "excel"
		Start_Tbl = "<table>"
		Start_Row = "<tr>"
		Start_Data = "<td>"
		End_Data = "</td>"
		End_Row = "</tr>"
		End_Tbl = "</table>"
	Case "csv"
		Start_Tbl = ""
		Start_Row = ""
		Start_Data = ""
		End_Data = ","
		End_Row = VbCrLf
		End_Tbl = ""
End Select
'The code below will not work. but is just a representation.
Output_String = Start_Tbl
For Each Row In Your Table
	Output_String = Output_String & Start_Row
	For Each Item In Your Row
		Output_String = Output_Sting & Start_Data & Item.Value & End_Data
	Next
	Output_String = Output_String & End_Row
Next
Output_String = Output_String & End_Tbl
 
Set FSO = FSO
	FSO.FileName = "YourFileName"
	FSO.Write Output_String
Set FSO = Nothing
%>

Open in new window

0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 20337158
You don't say which Sybase database product (they have many) or which version you're using.

There are many ways of copying data from a table to a file. If you want to do this purely in ASP then it looks like you've been given some good answers to that already. Depending on your Sybase product and version you could do this with Sybase native tools which would get the data into a file for you with considerably less code. let us know if you'd like to go down that path.
0
 
LVL 2

Author Comment

by:synergiq
ID: 20337983
I'm using Sybase iAnywhere version 12.
0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 500 total points
ID: 20338244
Then it's even easier, you can use the OUTPUT TO clause following a regular SELECT.

OUTPUT TO takes the result set of a SELECT and send it to a file instead. Note that it is *not* returned to the client, and that the file is a file on the server where ASA is running, not on the client.

FORMAT ASCII gives you CSV with one row per line, all strings in single quotes.
FORMAT EXCEL writes an Excel spreadsheet directly. (No quotes.)

You can also control whether only strings are quoted (default behaviour) or every column regardless of datatype.
SELECT [...]
FROM   [...]
WHERE  CustomerID = [...]
OUTPUT TO [path_and_file] FORMAT ASCII

Open in new window

0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

623 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