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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


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