Saving a cached copy of a page [Archive]

Click to See Complete Forum and Search --> : Saving a cached copy of a page

lmf232s

12-02-2004, 06:15 PM

I have this report that is pretty massive.
It would nice if everytime the user tried
to view the page he could pull up a cached copy
of the page so it would not have to hit the DB everytime.

Any ideas on this.

XML?

russell

12-02-2004, 08:30 PM

write a program to generate the web page and save it. schedule it to run weekly, nightly (or however often the data is updated). XML would be fine, but a compiled app running periodically can generate the static html page and you'll have far less overhead.

now, if you need sorting and such, then you'll need to use either xml and xpath or pull the data into a summary table used by the report.

if you can write ASP, you won't have much trouble writing a VB app that will do the trick for you.

post back or pm me if you need further details/help.

rb

lmf232s

12-03-2004, 11:08 AM

Russell,
Good idea, would not of thought of creating a vb app to create
the report and save it. I dont need to sort or anything its a basic
straight forward report. I do the ordering and sorting in the query.

So what would you do.
Create a vb app to create the report and save it to the server.
Then schedule a windows task to run it say every night or week.

I like the sounds of that.
Yes i know vb, but i do not know how to create a web page from code.
I bet i could find some examples on the web.
Ill check it out.

If you want to post back a little code snippet of how to achieve this
that would be great also but ill also check the web.

Thanks for the idea.

russell

12-03-2004, 12:57 PM

Remember that an html file is just text file, so there is really nothing much new to learn. In the sample below, you'll want to add error handling, and of course the choice of database connection, command etc is yours. You'll need to add a reference in your vb project to scrrun (microsoft scripting runtime) or use the regular vb print # functions instead of the text stream used below, and add a reference to ado.

I've executed the query and put the results into an array via the ado GetRows method. Then i walk through the array writing to the text (html) file.

Private Sub MakeWebPage()
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fso As Scripting.FileSystemObject
Dim tst As Scripting.TextStream
Dim sql As String
Dim i As Long
Dim j As Long
Dim ar

Set db = New ADODB.Connection
Set fso = New Scripting.FileSystemObject

db.Open MyConnectionString

sql = "SELECT fields FROM MyTable WHERE someCondition = something " _
& "ORDER BY whatever"

Set rs = db.Execute(sql)
ar = rs.GetRows

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

Set tst = fso.OpenTextFile("c:\myDir\webPage.htm", ForWriting, True)

tst.WriteLine "<html>" & vbCrLf
tst.WriteLine "<body>" & vbCrLf
tst.WriteLine "<table>" & vbCrLf

For i = 0 To UBound(ar, 2)
tst.WriteLine "<tr>" & vbCrLf
For j = 0 To UBound(ar, 1)
tst.WriteLine "<td>" & ar(j, i) & "</td>" & vbCrLf
Next
tst.WriteLine "</tr>" & vbCrLf
Next

tst.WriteLine "</table>" & vbCrLf
tst.WriteLine "</body>" & vbCrLf
tst.WriteLine "</html>"

tst.Close
Set tst = Nothing
Set fso = Nothing

End Sub

lmf232s

12-03-2004, 01:24 PM

thanks russel,
Ill play with this over the weekend.