This example is similar to Selecting Records from a Relational Database except it uses the Query operation so you can use joins and other sql features.  The Query operation is more powerful but it does require extra programming to prevent mischevious input.  Please see this article on sql injection before deploying this script in production.

This example is written for Microsoft Access. You can read more about the Access connector, or you can easily adapt this script to use other database connectors.

To run this example, please download and install RSSBus Desktop, plus this zip file containing the script and db.  Note there are several other database scripts in this zip which are described in other database tutorials.  The example below only uses query.rsb and customersWithType.mdb.  Place these two files in your RSSBus root directory (www/) and call with:

    http://localhost:1110/query.rsb


You can learn more about RSSBus by reading the RSSBus Server Quick Start Guide.

This example uses RSSBus to publish a list of files with links for downloading.  It uses the FileOps connector to generate the list of files in RSS format.

If you want to run the demo, download and install a trial copy of RSSBus Desktop from the RSSBus Downloads page, then download this script and sample zip file and place the files under your RSSBus www/ directory:

 ...

   www/

     fsconfig.rsb  -- configuration file for fslist and fsget

     fslist.rsb    -- lists files in fsfiles/, with fsget URLs

     fsget.rst     -- returns contents of a file

     fsfiles/      -- directory that will be served
       text.txt    -- sample data

       word.doc    -- sample data

To use the file server, call fslist with this URL:

    http://localhost:1110/fslist.rsb

If you don't get a listing of fsfiles/, check the path in fsconfig against your actual fsfiles/ path.  You may want to move the served directory out of www/ if you deploy this service.

The following is a discussion of the design process for the file server.  You should refer to the full script listings in the download. 

fslist.rsb pushes the following RSS item for each file in the directory:

 <item>
    <title>text.txt</title>
    <link>http://127.0.0.1:1110/fsget.rst?file=text.txt</link>
    <description>
       <a href="http://127.0.0.1:1110/fsget.rst?file=text.txt">View</a>
       <em>(text/plan - 29 bytes - Updated Tue, 01 Jul 2008 14:18:44 GMT)</em>
    </description>
    <enclosure length="29" type="text/plain"
        url="http://127.0.0.1:1110/fsget.rst?file=text.txt" />
    <guid isPermaLink="false">text.txt-Tue, 01 Jul 2008 14:18:44 GMT</guid>
 </item>

The <title> attribute is easy to construct from the file:name, which is returned by the fileListDir operation.  fileListDir also returns the file modification time and size which we use to write the <description> attribute:

<a href="[file:geturl]">View</a>

<em>([file:mimetype] -- [file:size] bytes -- Updated [file:mtime])</em>

The <link> attribute is what users will click to retrieve the file content.  Note that it calls fsget with the file name.  We decided not to pass the full directory path in the fsget URL for two reasons:  1) We don’t want the end user to see where the file really lives on disk, and 2) we don’t want to encourage the user to hack the directory path in the URL to explore our file system.  Instead we’ll just send the file name, and arrange for fsget to know the full path to the file directory.  This means that both fslist and fsget have to know where the served directory is, so we moved that bit of configuration to the separate configuration file that they both include:

<!—- fsconfig.rsb file for directory server -->

<!-- Configure the directory you want to serve -->

<rsb:set attr="path"

         value="c:\\Program Files\\RSSBus\\Desktop\\www\\fsfiles\\" />

    ...

Below is the shell of fsget.rst.  Note how we use getfilename() on the file input parameter to make sure the user can’t sneak path information in with the file name, and later we concatenate the configured directory path.  Note also that fsget is a template script (.rst extension) which does not generate a feed like .rsb files do. Templates are used to return formatted html or streamed file content:

<rsb:info title="fsget.rst" desc="Works with fslist.rsb">

  <input name="file" desc="File must be in configured path." />

</rsb:info>

 

<rsb:include file="fsconfig.rsb" />

 

<rsb:set attr="file" value="[file | getfilename()]" />

. . .

<rsb:set attr="_response.writefile" value="[path][file]" />

Returning to fslist.rsb, the <guid> is traditionally made up of the object name or id plus a modification time, and is used by client feed readers to decide if this item is new or updated since the last feed.  The file:guid attribute supplied by fileListDir contains the full file path, which we really don’t want the user to see, so we’ll make up our own guid from the short file name and modification time:

<rsb:set attr="file:guid" value="[file:name]-[file:mtime]" /> 

The <enclosure> tag is used by feed readers when deciding whether to “pre-fetch” content prior to the user clicking on the item, this is most commonly seen in "podcast" feeds where the enclosed files are audio.  Pre-fetch behavior is usually configurable in the reader.  This tag requires the file mime type, which is not provided by the fileListDir operation.  We’ll figure it out using the file extension and a table of mime types, which we’ll keep in fsconfig:

<rsb:set item="mime"  attr="txt"  value="text/plan" />

<rsb:set item="mime"  attr="htm"  value="text/html" />

<rsb:set item="mime"  attr="doc"  value="application/msword" />

<rsb:set item="mime"  attr="default" value="application/octet-stream" />

In fslist we’ll use the file extension (without leading dot) as the key to the table:

<rsb:set attr="ext" value="[file:extension | replace ('.', '', False)]" />

<rsb:set attr="file:mimeType" value="[mime.[ext] | def('[mime.default]')]" />

Note that you could omit both the guid and enclosure attributes and still have a usable feed, but we’ve included them for completeness.  So the basic shell of fslist.rsb is:

<rsb:include file="fsconfig.rsb" /> 

<rsb:call op="fileListDir" output="out">
  <!-- The body of this call executes once for each file in path.
       The fileListDir operation sets many file: attributes in the
       "out" item.  Next we'll skip sub-dirs for simplicity --> 
  <rsb:equals attr="file:isdir" value="false"> 

    <!-- Make file:geturl and file:guid that hide path from user --> 

    <!-- Find file:mimetype in "mime" item (see fsconfig) -->

    <!-- Make a new item called "rss" for output.  Decided

         against just pushing current item "out" because it has

         many other file: attributes we don't want to push  -->

    <rsb:set item="rss" attr="rss:enclosure@url"  value="[file:geturl]" />

    <rsb:set item="rss" attr="rss:enclosure@length" value="[file:size]" />

    <rsb:set item="rss" attr="rss:enclosure@type" value="[file:mimetype]" />

    <rsb:set item="rss" attr="rss:guid"  value="[file:guid]" />

    <rsb:set item="rss" attr="rss:link"  value="[file:geturl]" />

 

    <!-- Push sends the "rss" item on its way -->

    <rsb:push item="rss" title="[file:name]">

       <!-— Write description attribute here -->

    </rsb:push>

  </rsb:equals>

</rsb:call> 

Please visit the online documentation for more information on RSSBus connectors, or read the RSSBus Server Quick Start Guide to learn more about RSSbus. 

Downloads: FileServer.zip

 

This example uses Microsoft Access so you can try it without configuring a database server. You can readily adapt the script to work with Oracle, SQL Server, and other popular databases by changing the connector used. Please visit the RSSBus Connectors page on the web site to see the full range of database technologies and read/write operations supported by RSSBus.

This entry follows a preceeding blog entry called Inserting Records into a Relational Database.

If you want to run the demo yourself, first download and install a trial copy of RSSBus Desktop from the RSSBus Downloads page, and then download this database file and scripts.

Here is the Customers table from customers.mdb:

CustomerID	CompanyName	FirstName	LastName	PhoneNumber
1	Selecto Magic	Bob	Smith	800-123-4567
2	Woonsocket Widgets	Bob	Jones	800-321-4321
3	Spacely Sprokets	Bob	Rogers	727-888-1234

 Here is the loaddata.xls Excel spreadsheet that we are going to load:

CompanyName	FirstName	LastName	PhoneNumber
Disney Studios	Roger	Rabbit	666-234-1234
Sam Adams Brewery	Sam	Adams	800-345-2345

Here is the RSBScript (loaddata.rsb) that loads records from the spreadsheet into the database.  Note that it calls insert.rsb, presented in the prior blog entry:

To use this script, place the customers.mdb, loaddata.xml, loaddata.rsb, and insert.rsb files in the www/ directory of your RSSBus installation, and call it with this URL:

    http://localhost:1110/loaddata.rsb

Here is the RSS feed that is returned, which confirms the insert: