Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 25, 2004

Storing scanned web-pages and files to SQL Server - Page 2

By Muthusamy Anantha Kumar aka The MAK



Method 3

This method explains how to read multiple web pages listed in a SQL Server table and store those web pages to a SQL Table.

Step1:

Create a table in SQL server database to store the web pages.

Use WebPageScanner
Go
Create table WebPageStorage (ID int identity(1,1), 
  Webpage varchar(500), Content Varchar(7000))
Go

Step 2 :

Copy and paste the code below to a text file and save as C:\ScanWebPage\ScanWebPagefromandToSQL.vbs

'Author: MAK
'Contact: mak_999@yahoo.com
'Objective: To scan many websites from SQL Table 
'and store it in a SQL Table

Set AdCn = CreateObject("ADODB.Connection")
AdCn.commandtimeout =36000
Set AdRec = CreateObject("ADODB.Recordset")
Set AdRec1 = CreateObject("ADODB.Recordset")
AdCn.Open = "Provider=SQLOLEDB.1;Data Source=
  SQL2k\instance1;Initial Catalog=WebPageScanner;
  user id = 'WebPageuser';password='Web' "
SQL1 = "Select WebPage from WebPages order by [ID]"
AdRec1.Open SQL1, AdCn,1,1
while not Adrec1.EOF 
WebPage= Adrec1("WebPage")

Set objXMLhttp = CreateObject("MSXML2.XMLhttp") 
With objXMLhttp 
  .open "GET", webpage, False 
'	On Error Resume Next 
  .send 
  If Err.Number <> 0 Then 
    Msgbox "XMLhttp error " & Hex(Err.Number) & 
	" " & Err.Description 
  ElseIf .status <> 200 Then 
    MsgBox "http error " & CStr(.status) & " " & .statusText 
  Else 
	Query=""
	Query = "insert into WebPageStorage (WebPage,Content) Values ('"+ WebPage + "',
	  '"+ replace(.responseText,"'","")+"')"
	AdRec.Open Query, AdCn,1,1
  End If 
end with
Set objXMLhttp =nothing
Adrec1.movenext
Wend

MsgBox "Completed writing all Web Pages!"

Step 3:

Above VB script stores the web page in a TEXT format in a SQL Server table as shown below.

Note: Replace SQL2k\Instance1 with your SQL Server box name.

Upon completion, you will be presented with the following message box:

Method 4:

This method will scan the webpage http://forums.databasejournal.com and find the string "Members" and send email to mak_999@yahoo.com

Step 1:

Copy and paste the following code to a text file and save as C:\ScanWebPage\ScanWebPage.vbs

'Author: MAK
'Contact: mak_999@yahoo.com
'Objective: To scan http://forums.databasejournal.com and find # of members and 
'send email to mak_999@yahoo.com with latest information.

'Option Explicit 
Const ForReading = 1 
Const ForWriting = 2 
Dim objXMLhttp, objFSO, objTS 
Set fso = CreateObject("Scripting.FileSystemObject") 
Set objXMLhttp = CreateObject("MSXML2.XMLhttp") 
With objXMLhttp 
  .open "GET", "http://forums.databasejournal.com", False 
  On Error Resume Next 
  .send 
  If Err.Number <> 0 Then 
    Msgbox "XMLhttp error " & Hex(Err.Number) & " " & Err.Description 
  ElseIf .status <> 200 Then 
    MsgBox "http error " & CStr(.status) & " " & .statusText 
  Else 
    Set objFSO = CreateObject("Scripting.FileSystemObject") 
    Set objTS = objFSO.CreateTextFile("findtext.txt", True) 
    objTS.Write Replace(.responseText, vbLf, vbNewLine) 
    objTS.Close 
    Set objTS = Nothing 
    Set objFSO = Nothing 
'    MsgBox "Completed writing file!" 
  End If 
End With 

Set ReadFile = fso.OpenTextFile("findtext.txt", ForReading, TristateFalse) 
thisTXT = ReadFile.ReadAll 

Chaine = "Members: " 
Chaine2 = "<br>" 
ValueSearch = InStr(1,thisTXT,Chaine,1) 
ValueSearch2 = InStr(valuesearch,thisTXT,Chaine2,1) 
'msgbox ValueSearch 
'msgbox ValueSearch2 
If ValueSearch>=1 Then 
y=  mid(thisTXT,ValueSearch,(ValueSearch2-ValueSearch)) 
'msgbox y 
Else 
y="Wrong Data" 
End If 
Set objXMLhttp = Nothing 
x=Weekdayname(weekday(now())) +", "+monthname(month(now()))+" "+right("00"+cstr(month(now())),2)+", "+cstr(year(now())) 
'if left(mid(thisTXT,ValueSearch,(ValueSearch2-ValueSearch)),26)=x then 
'msgbox "True" 
'end if 
'msgbox x 

'Sending a text email using a remote server  

Set objMessage = CreateObject("CDO.Message") 
objMessage.Subject = "Database Journal # of Members" 
objMessage.Sender = "mak_999@yahoo.com" 
objMessage.To = "mak_999@yahoo.com" 
objMessage.TextBody = y 
'msgbox y
'The line below shows how to send a webpage from a remote site 
objMessage.Configuration.Fields.Item _ 
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
'Name or IP of Remote SMTP Server 
objMessage.Configuration.Fields.Item _ 
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "121.111.222.11" 
'Server port (typically 25) 
objMessage.Configuration.Fields.Item _ 
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 
objMessage.Configuration.Fields.Update 

objMessage.Send

Step 2:

Executing the above VB Script will find the number of members, threads and posts as shown below, and send an email.

Note: Replace 121.111.222.11 with your SMTP server IP address. Replace mak_999@yahoo.com with latest # of members, threads and posts.

Conclusion:

By scheduling the VB scripts listed in method 1, method 2 and method 3 you can store the listed web pages in an HTML file or as an SQL Server table. By scheduling the VB script in method 4, you will get e-mail with latest information from a web site. You can also update the script in such a way that it can compare the previous values and send email only when the value was changed.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date