Dom DXecutioner
07-25-2007, 09:08 AM
Greetings,
Most of you may know what RSS is and what it is used for. RSS is based on XML technology. RSS readers are widely available and it even comes with the new IE 7 and Microsoft Outlook 2007.
While this is kind of nice, I'd like to have the ability to include such feature into my own applications. I will attempt at giving you a starting point for developing this feature into your own applications.
Please note that, although the required and perhaps essential code is included, some code has been purposely removed so that you can get your feet wet and continue where I leave off. I do, however, provide a glimpse at what needs to be done.
If you're not familiar with XML/RSS specifications, you can visit the following links for more details.
- http://blogs.law.harvard.edu/tech/rss
- http://www.rss-specifications.com/
In an attempt to recreate MS Outlook's organization of feeds, I will be using the Folder, Channel, and Feed hierarchy. This means that I need to create data tables for the purposes of archiving my feeds... So, the data tables required are as follows:
t_RssFolders: the table where the folders categorizing the RSS feeds will be stored
- RSSFolderID : AutoNumber
- RSSFolder : Text
t_RssChannels: the table where the RSS channels related to any given folder will be stored
- RSSChannelID : AutoNumber
- RSSFolderID : Number
- Title : Text
- Link : Memo
- Description : Text
t_RssItems: the table where the parsed feeds related to any given channel will be stored
- RSSItemID : AutoNumber
- RSSChannelID : Number
- title : Text
- link : Memo
- description : Memo
When building the relationships, be sure to check Enforce Referential Integrity and Cascade Delete Related Records.
Building the user interface, which includes the design and forms required for a complete application, is out of the scope of this exercise. But I will be discussing that at a later time (when time allows it).
I have, however, created the basic interface required to process the basics of processing a feed into our data tables. It's a class module that should be 'easy' to use in your own application. Keep in mind that this is a basic interface and it's NOT a complete solution, but just about.
Another thing to note is the references required for this to work properly.
- Microsoft DAO 3.6 Object Library
- Microsoft XML, version 2.0
CLASS MODULE: cRSSManager
Option Compare Database
Option Explicit
’// module level variables
Private oXML As MSXML.DOMDocument
Private oNodes As MSXML.IXMLDOMNodeList
Private oNode As MSXML.IXMLDOMNode
’// data primary keys/ids variables
Private m_lngFolderID As Long
Private m_lngChannelID As Long
’// data tables variables
Private m_strFeedDataTable As String
Private m_strFolderDataTable As String
Private m_strChannelDataTable As String
Private Sub Class_Initialize()
’// instantiate xml/DOM object
Set oXML = New DOMDocument
End Sub
Private Sub Class_Terminate()
’// clean up
Set oNode = Nothing
Set oNodes = Nothing
Set oXML = Nothing
End Sub
Public Sub getFeed(lngChannelID As Long, strURL As String)
’// general variables
Dim i As Integer ’// item parent node loop variable
Dim ii As Integer ’// item child nodes loop variable
Dim intNodes As Integer ’// number of child nodes
Dim rs As DAO.Recordset
Dim strField As String
Dim strValue As String
’// enable error handler
On Error GoTo Err_Handler
’// load xml, but not in a asynchronous mode,
’// mainly because you may get a "No Child" error
oXML.async = False
’// TODO: test for internet connection
’// test for successful load
If oXML.Load(strURL) Then
’// get list of item nodes
Set oNodes = oXML.selectNodes("rss/channel/item")
’// get count of items
intNodes = oNodes.length
’// open appendable recordset
Set rs = DAODataSet(Me.feedDataTable, dbOpenDynaset)
’// loop thru nodes
For i = 0 To intNodes - 1
’// open reference to recordset (performance)
With rs
’// begin to add new record
.AddNew
’// provide channel id (required)
.Fields("RssChannelID").Value = lngChannelID
’// loop thru each child node of the "ITEM NODE" to get all
’// of the information for that 'ITEM"
‘// note: this should be useful in other projects where XML format is used
For Each oNode In oNodes(i).childNodes
’// get the node's name, which may be one of the fields in our table
strField = oNode.nodeName
’// get the value of the node
strValue = oNode.Text
’// TODO: test item does not already exists
’// an error is expected depending on the fields we're filling in,
’// ie, the RSS 2.0 specs include a GUID field (amongst others) that allows us to identified
’// that unique item, this allows us to verify whether or not we already have this
’// feed in our data table. However, assuming that you're sticking to RSS 1.0, and
’// you don't include this in your data table, it will fail because the field does not exist
’// hope this makes sense...
On Error Resume Next
’// apply the values to the field provided by the XML feed
.Fields(strField).Value = strValue
Next
’// commit to changes
.Update
End With
Next i
Else
’// attempt provide some meaningful reason of failure
MsgBox oXML.parseError.errorCode & " : " & oXML.parseError.reason
End If
Err_Exit:
’// clean up
Set rs = Nothing
’// exit sub
Exit Sub
Err_Handler:
’// error constant
Const Err_ItemNotInCollection As Integer = 3265
’// test if it's the expected error of a fied not being in the
’// recordset's collection... if so, ignore and resume to the item
If Err.Number = Err_ItemNotInCollection Then
Resume Next
Else
MsgBox Err.Number & " : " & Err.description
Resume Err_Exit
End If
End Sub
Private Function DAODataSet(strDataSource As String, DataSetType As DataTypeEnum) As DAO.Recordset
’// general data objects
Dim db As DAO.Database
Dim rs As DAO.Recordset
’// load object pointer
Set db = CurrentDb
’// load DAO recordset
Set rs = db.OpenRecordset(strDataSource, DataSetType)
’// return recordset/dataset
Set DAODataSet = rs
’// clean up
Set rs = Nothing
End Function
Public Property Let folderDataTable(strFolderDataTable As String)
m_strFolderDataTable = strFolderDataTable
End Property
Public Property Get folderDataTable() As String
folderDataTable = m_strFolderDataTable
End Property
Public Property Let feedDataTable(strFeedDataTable As String)
m_strFeedDataTable = strFeedDataTable
End Property
Public Property Get feedDataTable() As String
feedDataTable = m_strFeedDataTable
End Property
Public Property Let channelDataTable(strChannelDataTable As String)
m_strChannelDataTable = strChannelDataTable
End Property
Public Property Get channelDataTable() As String
channelDataTable = m_strChannelDataTable
End Property
Public Function createFolder(strTitle) As Long
’// TODO: create code to add folder and return the record's id/pk
End Function
Public Function createChannel(lngFolderID As Long, strTitle As String, strURL As String, Optional strDescription) As Long
’// TODO: create code to create channel and return the record's id/pk
End Function
Now that we have the class code, below is the test code in a standard module... It is assumed that you have already created a folder 'CNN' with the id of 1, and a Channel 'Top Stories' with the id of 1...
STANDARD MODULE: mod_RSSManager
Option Compare Database
Option Explicit
Private RSSManager As cRssManager
Public Sub testRSSManager()
’// general variables
Dim strRssURL As String
Dim lngChannelID As Long
’// initialize variables
strRssURL = "http://rss.cnn.com/rss/cnn_topstories.rss"
lngChannelID = 1
’// instantiate RSSManager class
Set RSSManager = New cRssManager
’// open class reference
With RSSManager
’// provide data table info
.folderDataTable = "t_RssFolders"
.channelDataTable = "t_RssChannels"
.feedDataTable = "t_RssItems"
’// download and store rss feed into data table
.getFeed lngChannelID, strRssURL
End With
’// clean up/release memory
Set RSSManager = Nothing
End Sub
So, there you have it... you should be on your way to adding a new functionality to your applications, although I'd encourage some error trapping.
If you come across any problems, bugs, etc, please post in the forums with the exact same title, and I'll try to respond at my earliest convenience. But, be very specific to your problem, or you may not get a response in a timely manner.
Enjoy. :)
Most of you may know what RSS is and what it is used for. RSS is based on XML technology. RSS readers are widely available and it even comes with the new IE 7 and Microsoft Outlook 2007.
While this is kind of nice, I'd like to have the ability to include such feature into my own applications. I will attempt at giving you a starting point for developing this feature into your own applications.
Please note that, although the required and perhaps essential code is included, some code has been purposely removed so that you can get your feet wet and continue where I leave off. I do, however, provide a glimpse at what needs to be done.
If you're not familiar with XML/RSS specifications, you can visit the following links for more details.
- http://blogs.law.harvard.edu/tech/rss
- http://www.rss-specifications.com/
In an attempt to recreate MS Outlook's organization of feeds, I will be using the Folder, Channel, and Feed hierarchy. This means that I need to create data tables for the purposes of archiving my feeds... So, the data tables required are as follows:
t_RssFolders: the table where the folders categorizing the RSS feeds will be stored
- RSSFolderID : AutoNumber
- RSSFolder : Text
t_RssChannels: the table where the RSS channels related to any given folder will be stored
- RSSChannelID : AutoNumber
- RSSFolderID : Number
- Title : Text
- Link : Memo
- Description : Text
t_RssItems: the table where the parsed feeds related to any given channel will be stored
- RSSItemID : AutoNumber
- RSSChannelID : Number
- title : Text
- link : Memo
- description : Memo
When building the relationships, be sure to check Enforce Referential Integrity and Cascade Delete Related Records.
Building the user interface, which includes the design and forms required for a complete application, is out of the scope of this exercise. But I will be discussing that at a later time (when time allows it).
I have, however, created the basic interface required to process the basics of processing a feed into our data tables. It's a class module that should be 'easy' to use in your own application. Keep in mind that this is a basic interface and it's NOT a complete solution, but just about.
Another thing to note is the references required for this to work properly.
- Microsoft DAO 3.6 Object Library
- Microsoft XML, version 2.0
CLASS MODULE: cRSSManager
Option Compare Database
Option Explicit
’// module level variables
Private oXML As MSXML.DOMDocument
Private oNodes As MSXML.IXMLDOMNodeList
Private oNode As MSXML.IXMLDOMNode
’// data primary keys/ids variables
Private m_lngFolderID As Long
Private m_lngChannelID As Long
’// data tables variables
Private m_strFeedDataTable As String
Private m_strFolderDataTable As String
Private m_strChannelDataTable As String
Private Sub Class_Initialize()
’// instantiate xml/DOM object
Set oXML = New DOMDocument
End Sub
Private Sub Class_Terminate()
’// clean up
Set oNode = Nothing
Set oNodes = Nothing
Set oXML = Nothing
End Sub
Public Sub getFeed(lngChannelID As Long, strURL As String)
’// general variables
Dim i As Integer ’// item parent node loop variable
Dim ii As Integer ’// item child nodes loop variable
Dim intNodes As Integer ’// number of child nodes
Dim rs As DAO.Recordset
Dim strField As String
Dim strValue As String
’// enable error handler
On Error GoTo Err_Handler
’// load xml, but not in a asynchronous mode,
’// mainly because you may get a "No Child" error
oXML.async = False
’// TODO: test for internet connection
’// test for successful load
If oXML.Load(strURL) Then
’// get list of item nodes
Set oNodes = oXML.selectNodes("rss/channel/item")
’// get count of items
intNodes = oNodes.length
’// open appendable recordset
Set rs = DAODataSet(Me.feedDataTable, dbOpenDynaset)
’// loop thru nodes
For i = 0 To intNodes - 1
’// open reference to recordset (performance)
With rs
’// begin to add new record
.AddNew
’// provide channel id (required)
.Fields("RssChannelID").Value = lngChannelID
’// loop thru each child node of the "ITEM NODE" to get all
’// of the information for that 'ITEM"
‘// note: this should be useful in other projects where XML format is used
For Each oNode In oNodes(i).childNodes
’// get the node's name, which may be one of the fields in our table
strField = oNode.nodeName
’// get the value of the node
strValue = oNode.Text
’// TODO: test item does not already exists
’// an error is expected depending on the fields we're filling in,
’// ie, the RSS 2.0 specs include a GUID field (amongst others) that allows us to identified
’// that unique item, this allows us to verify whether or not we already have this
’// feed in our data table. However, assuming that you're sticking to RSS 1.0, and
’// you don't include this in your data table, it will fail because the field does not exist
’// hope this makes sense...
On Error Resume Next
’// apply the values to the field provided by the XML feed
.Fields(strField).Value = strValue
Next
’// commit to changes
.Update
End With
Next i
Else
’// attempt provide some meaningful reason of failure
MsgBox oXML.parseError.errorCode & " : " & oXML.parseError.reason
End If
Err_Exit:
’// clean up
Set rs = Nothing
’// exit sub
Exit Sub
Err_Handler:
’// error constant
Const Err_ItemNotInCollection As Integer = 3265
’// test if it's the expected error of a fied not being in the
’// recordset's collection... if so, ignore and resume to the item
If Err.Number = Err_ItemNotInCollection Then
Resume Next
Else
MsgBox Err.Number & " : " & Err.description
Resume Err_Exit
End If
End Sub
Private Function DAODataSet(strDataSource As String, DataSetType As DataTypeEnum) As DAO.Recordset
’// general data objects
Dim db As DAO.Database
Dim rs As DAO.Recordset
’// load object pointer
Set db = CurrentDb
’// load DAO recordset
Set rs = db.OpenRecordset(strDataSource, DataSetType)
’// return recordset/dataset
Set DAODataSet = rs
’// clean up
Set rs = Nothing
End Function
Public Property Let folderDataTable(strFolderDataTable As String)
m_strFolderDataTable = strFolderDataTable
End Property
Public Property Get folderDataTable() As String
folderDataTable = m_strFolderDataTable
End Property
Public Property Let feedDataTable(strFeedDataTable As String)
m_strFeedDataTable = strFeedDataTable
End Property
Public Property Get feedDataTable() As String
feedDataTable = m_strFeedDataTable
End Property
Public Property Let channelDataTable(strChannelDataTable As String)
m_strChannelDataTable = strChannelDataTable
End Property
Public Property Get channelDataTable() As String
channelDataTable = m_strChannelDataTable
End Property
Public Function createFolder(strTitle) As Long
’// TODO: create code to add folder and return the record's id/pk
End Function
Public Function createChannel(lngFolderID As Long, strTitle As String, strURL As String, Optional strDescription) As Long
’// TODO: create code to create channel and return the record's id/pk
End Function
Now that we have the class code, below is the test code in a standard module... It is assumed that you have already created a folder 'CNN' with the id of 1, and a Channel 'Top Stories' with the id of 1...
STANDARD MODULE: mod_RSSManager
Option Compare Database
Option Explicit
Private RSSManager As cRssManager
Public Sub testRSSManager()
’// general variables
Dim strRssURL As String
Dim lngChannelID As Long
’// initialize variables
strRssURL = "http://rss.cnn.com/rss/cnn_topstories.rss"
lngChannelID = 1
’// instantiate RSSManager class
Set RSSManager = New cRssManager
’// open class reference
With RSSManager
’// provide data table info
.folderDataTable = "t_RssFolders"
.channelDataTable = "t_RssChannels"
.feedDataTable = "t_RssItems"
’// download and store rss feed into data table
.getFeed lngChannelID, strRssURL
End With
’// clean up/release memory
Set RSSManager = Nothing
End Sub
So, there you have it... you should be on your way to adding a new functionality to your applications, although I'd encourage some error trapping.
If you come across any problems, bugs, etc, please post in the forums with the exact same title, and I'll try to respond at my earliest convenience. But, be very specific to your problem, or you may not get a response in a timely manner.
Enjoy. :)