synergiq
asked on
Creating a partial backup of a sybase database using classic asp
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?
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?
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 :
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
%>
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.
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.
ASKER
I'm using Sybase iAnywhere version 12.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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","att
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","att
%>
<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.