Turn an online webcam into a video log

Opponents of the transport of huge "megaloads" of oil-refining equipment through the wild mountains of Idaho and Montana need a way to monitor the progress (or not!) of the loads.  The Idaho and Montana Departments of Transportation operate webcams around the state that are available on their websites.  Volunteers have been staying up all night watching these webcams to keep tabs on (and potentially capture images of) the megaload convoys.

I wanted to relieve the night watchmen with an automated capture.  The method I used is outlined below.

Note:  This method is Macintosh-specific, though the principles could be applied in Linux or Windows.  If you craft a solution for other platforms, please include it in a comment on this post.

My plan was to capture the image (or the whole web page) into a file four times per hour, since the web-available images are updated every fifteen minutes.  I figured I needed a capture program, and a way to call it at intervals.


Capture the Page

The first step was easy: Googling "mac webcam capture" lead me to Paul Hammond's excellent script, "webkit2png".  The script requires the "python" programming language and the "PyObjC bridge" software, both included in Mac OS X 10.5 and later, but you don't have to know a thing about python or PyObjC to use webkit2png.  When you run from the command line, it accepts a URL, fetches the web page, and converts the entire page to a "png" image file, which is widely supported by browsers and other graphics programs.

To run this in an automated way, each file generated needs a unique file name.  webkit2png offers an option to append the date to the output filename.  Looking at the script, it wasn't hard to find the function that fetches the date.  I googled that function in python and found out how to modify it to include the time as well as the date.  With that one six-character mod, webkit2png was good to go for me.  [Note that this was simplified for me by the fact that webkit32png is a script, not a compiled program.  It is often takes less knowledge to modify a script than a program.]

The command I ended up with is:

python ~/bin/paulhammond-webkit2png-9c4265a/webkit2png -Fd -o Lolo -D ~/Desktop/+Lolo http://rwis.mdt.mt.gov/scanweb/lolo.shtml 

• python is the program which will "interpret" (run) the script
• ~/bin/....webkit2png is the script to run
• -Fd tells webkit2png to append the date-time to the output filenale
• -o Lolo gives the "stem" (first part) of the output filename
• -D /Desktop/+Lolo is the directory into which to put the output file
• http://...lolo.shtml is the URL to capture.

Launch At Intervals

The classic way to launch a command at certain times in Unix-derived systems like Mac OS X is to use the built-in program cron.  [Though Apple now uses and recommends their "launchctl" program, citing extended features, I'm an old Unix hand, and I don't need extended features, so I went with what I know.]  cron "wakes up" once per minute, and reads a control file (called crontab) that tells it what commands to run at specific times.    

crontab has a somewhat arcane syntax, so rather than relearn it, I downloaded Cronnix, which provides a convenient dialog box (in a "simple" or "advanced" mode) to create the entries.  In Cronnix I use:

• min: 8,23,38,53 - one minute after the image is updated by the webcam
• hour: 0-6, 18-23 - only record from midnight to 6am and 6pm to midnight (the megaloads only roll at night)
• mday:  * - any day of the month
• month: * - any month
• Wday: * - any day of the week
• Command: - as above

I clicked Cronnix's "Save" button, and that was it!

Notes

1. The particular webcam I am logging uses a static URL for the actual image, so I could have fed the img "src=" URL to webkit2png instead of the web page URL.  In fact, any URL that return a file type that can be interpreted by webkit could be used.
2. There are many other ways to accomplish the same thing.  This one was pretty simple for me to put up.  YMMV.

Emails with attachments don't appear in document libraries

Emails to a SharePoint 2007 document library have some "gotchas" when it comes to attachments. Filenames and encoding can both cause silent failures; the email appears to be sent correctly, but the doclib is unchanged, and no error appears to the user, either via return email or on the website.
Filenames may contain characters that are valid in your desktop OS, but are not valid for Sharepoint. Microsoft lists the bad guys in this Knowledge Base article:
Information about the characters that you cannot use in sites, folders, and files in SharePoint
I follow this simple rule: don't use punctiation other than underscores in filenames, and don't put an underscore first.
Encoding of attachments may also be an issue. Testing on our SharePoint 2007 site indicates that attachments in the AppleDouble format (often the default for email clients on the Macintosh) cause the email to fail silently. Be sure to "encode for Windows" if sending from a Mac email client. [Note that GMail uses MIME/Base-64 encoding, which SharePoint accepts.]

SharePoint Overview

Posted to sharepointdiscussions@groups.yahoo.com in response to a thread about SharePoint vs. Google Apps for a small church organization:

 I think about SharePoint in the following terms (YMMV):

SharePoint at its core is web-based file sharing. Conventional LAN file shares have some major drawbacks:  you have to be on the LAN to access them, and it's difficult to include and utilize metadata. and.  SharePoint began by addressing these two issues.  [Today's SharePoint also includes document management features like global search and workflow.]

If you ask, "Why do people share files?", the short answer is, "They're collaborating".  So SharePoint includes collaboration features like check-out/check-in, announcements, calendars, and discussions.  This is the basis of the "teamsites".

It is common in today's project-oriented workplace for people to work on several project teams.  Some integrated view of multiple teamsites is needed, so SharePoint includes portal features like MySite.  [Today's SharePoint also includes "social" features like "Colleague Tracker".]

Now that SharePoint is collecting all those eyeballs, it becomes attractive as a business application platform.  Data views, Business Data Connector, Key Performance Indicators, and so on follow.

With so much web-based business function co-located on the SharePoint platform, it is also attractive to integrate content management;  hence the Publishing feature.

So you can consider SharePoint an integrated suite of web-based applications for file sharing, collaboration, business information, and publishing.  If all you need is file-sharing, there are simpler alternatives.

Alex is exactly right:  details are king.  The problem is that the details of the work that people need to do are often hidden from the person who makes the decisions about what platform they will use.

-- Joshua

Controlling access to content for anonymous users?

sharepointdiscussions : Message: RE: [sharepointdiscussions] How do you control access to content for anonymous users?

This post discusses a simple way to deny access for anonymous users to particular pages within a public publishing site.  [Sorry, Yahoo registration required.  I thought it was public :-( ]

Moving A Site - "Export ran out of memory"

Another hitch in the site migration saga (see my earlier post).  Some sites I am migrating reach the "Compressing File(s)" stage, then error out:

FatalError: Export ran out of memory while compressing a very large file. To successfully export, turn compression off by specifying the -nofilecompression parameter.

So the really big .cmp/.cab files I was creating can't be compressed.  The solution suggested in the error message is not attractive, since these are files that have to be transmitted over the network.  My solution:  cut the "-cabsize" parameter on the "stsadm -o export" command back to "60". The export ran to completion,  the SharePoint Designer "Restore website..." operation was successful, and the site actually looks correct.

The one gotcha I saw had to do with the "60" (megabyte) limit on the size of the .cmp/.cab files.  This particular site was exported as 6 files, with sizes (in order of creation) of 89, 86, 82, 64, 68, and 56 megabytes.  Examining the contents of the 89MB .cmp file (by simply changing the file extension to .cab and double-clicking), showed that the last file added to the .cab was 47MB (an .mp3).  Probably the export process doesn't look ahead at file sizes to determine when to switch to building the next .cab file, but waits to detect the occurrence of the overflow.

So admins migrating SharePoint sites with (even moderately) big files, beware!

Moving A Site - "Restore Did Not Complete Successfully"

I have been migrating sites from one SharePoint farm to another by means of making a "backup" or "export" from the source server and doing a "restore" or "import" on the destination server.  This is not as simple as it seems, since Microsoft is determined to make it confusing (which is not far from making it just plain difficult).

Using [Today] or [Me] in SharePoint calculated columns

One would like to be able to use dynamic information like [Today] or [Me] in calculated columns (for example, to compute "Age" from "DateOfBirth"), but SharePoint complains.

Steve Eagleson was kind enough to point out this article from "Novotronix":

Novotronix Blog - Using [Today] or [Me] in SharePoint calculated columns

which contains a workaround which seems to... um, work.

The workaround involves creating a temporary column called "Today", then creating the formula, then deleting the temp column. This suggests that SharePoint actually knows how to use the dynamic variable, but the parser rejects it when the formula is saved. Very odd...

Displaying Attachments to SharePoint Blog Posts

Where I work, we are interested in creating online "learning histories" of our activities.  We conceive of the learning history as a set of brief reflective essays, usually contemporaneous with the events they describe, and sometimes accompanied by (or accompanying) document artifacts. This sort of sounds like a blog with attachments, especially because blogs put chronological organization in the foreground.  

We would like to add entries in the learning history by email as well as via web interface.  So I set up a SharePoint blog, enabled attachments on the "Posts" list, email-enabled the list, and sent an email with attachments.  When viewed via AllItems.aspx, the paper-clip icon was present, but the attachments did not appear when the post was displayed on the Home page or the Post.aspx page.  They did, however, appear on the EditPost.aspx page.  So I knew they were in the system.  [See more information on this in my post "Emailing to SharePoint Blogs".]

I googled and found a post <http://www.pampigt.se/?p=69> that seemed to address the question directly.  The first part of the post deals with changing the site definition of blogs so that "Add Attachment" appears in the "New" and "Edit" page toolbars.  Since I don't have access to the 12 hive for this site, and I know I can get attachments in via email, I skipped to the next step, which reads:

If you want to display the attached files in your post please convert the Web Part in your post.aspx that shows the “post” to an XSLT DVWP.
Fix the Author problem by removing the = character before =@Author and thenfollow the guide below:

Now you have to open the post.aspx of your SharePoint 2007 blog and add the code linked from :

http://blogs.msdn.com/dmp/archive/2009/01/21/how-to-show-attachments-with-dataformwebpart.aspx

This is a little sketchy, so here is the detail as I experienced it:

The conversion referred to in the first sentence is accomplished in SharePoint Designer.  I started this work by opening the Post.aspx page in SPD, then before doing anything else I saved it under another name (Post-test.aspx).  I did all my testing on this page.  This way, I could test in the live site without interfering with production.

To convert the List View Web part to a Data View (XSLT) Web Part, open the page, select the "Posts" web part (which is displaying "There are no posts in this category."), right-click, and choose "Convert to XSLT".

SPD will complain that it can't display the web part. This is the "Author" problem described by pampigt.  Use the "split" icon to display both the "Design" and "Code" modes, find "=@Author", and change it to "@Author" (there's only one occurrence).   Then save the page — the display will be refreshed.

Then I discovered a problem not described by "pampigt".  I tested the page at this stage, and found that in the browser, it displayed the "There are no posts in this category" message.  Examining the XSLT, it appeared to me that this meant that there were no rows returned by the query.  After much munching around, I found the key to this step:  Using the little arrow-head next to the web part, I opened the Common Data View Tasks menu, and selected "Filter:"  I knew I needed to filter on ID, so I selected that as the field name, and I selected "Equals" as the comparison.  Then, under "Value", I knew I needed value of the query string parameter ID, but that didn't appear in the dropdown.  However, "Create a new parameter..." did appear, so I selected it.  I gave the parameter name "ID" and the source as "Query String".  A few "OK"'s later, I saved the page and tested in the browser.  First Victory!

Now to get the attachments listed on the page.  For this, I used the code linked by "pampigt" from Dario Martirani <http://blogs.msdn.com/dmp/archive/2009/01/21/how-to-show-attachments-with-dataformwebpart.aspx>.  His Data View Web Part (he uses the older name "Data Form Web Part") is much simpler than the converted List View Web Part in Posts.aspx, but I took the XSL code for the dvt_1.rowview template and inserted it right above the "Posted at" part of the XSL for my Data View Web part.  I added a test so the code wouldn't be used if there were no attachments, and a <br /> at the end.  Save, test in browser — Second Victory!

Here's the XSL code I ended up with:

   <xsl:if test="@Attachments!='0'">
        Attachments:
           <xsl:element name="SharePoint:AttachmentsField">
          <xsl:attribute name="runat">server</xsl:attribute>
          <xsl:attribute name="ListId">
                 {D2C70ED5-8551-446D-B0CF-AC4B30387CF0}
          </xsl:attribute>
          <xsl:attribute name="FieldName">Attachments</xsl:attribute>
          <xsl:attribute name="ControlMode">Display</xsl:attribute>
          <xsl:attribute name="Visible">true</xsl:attribute>
          <xsl:attribute name="ItemId">
              <xsl:value-of select="@ID"/>
          </xsl:attribute>
          </xsl:element>
          <br />
</xsl:if>

Note that, contrary to Dario's post, this appears to work even though the "ListId" attribute is not set to the correct list.  I suppose that the "AttachmentsField" control is getting its context from somewhere other than its attributes...

As the last step, I opened Post.aspx in SPD and saved it as Post-dist.aspx (the version distributed), then put the test page into production by saving it as Post.aspx, so the new version is the target of all the right links.

Remaining issues:   

1. adding attachment capabilities in the NewPost.aspx and EditPost.aspx pages (without modifying the 12 hive if possible)
2. adding display of attachments to the blog home page
   

Emailing to SharePoint Blogs

There are several places to find information on how to "email-enable" a SharePoint 2007 blog -- that is, to make it possible to post to the blog by sending email to it. At my job, I am helping to set up our "Rain King Chronicle" blog to do exactly that, and learned some interesting things about the process, particularly with regard to attachments.

Mobile App and OS UI's: A Lot To Learn

Jakob Nielsen has (as usual) some interesting and provocative insights about usability, in this case the usability of iPhone and other mobile apps:

iPhone Apps Need Low Starting Hurdles (Jakob Nielsen's Alertbox)

Nielsen says

The "master guideline" remains the same as in 1986: don't port a UI from an old interface paradigm to a new one.

Nielsen also points out that "on mobile devices, applications are easier to use than websites". Maybe we should look for ways to app-ize our websites for mobile users, rather than make "mobile-friendly" sites that depend on mobile browsers.

On a related note, Slate's Farhad Manjoo says Google's Android OS has a usability issue: "constant menu hunting".

What's Wrong With Android

Manjoo traces the difference between the iPhone user experience and the Android UX to a difference in philosophy:

...Apple is clearly trying to make a complete break with desktop operating systems... under Android's design philosophy, menus are a natural consequence of complexity... That's a familiar view of computing, one deeply tied to the interface on the standard PC...

So Nielsen would probably echo Manjoo's plea to the Android team: "join the menu-free bandwagon!"

And maybe we should rethink our web apps' UX with a more "mobile" mindset.

[On a side note: According to a correction notice on the Slate page linked above, the screenshots of the iPhone and Android calendars had their labels swapped initially. Somehow I doubt that anyone actually believed that the picture from the Android phone was something that ever appeared on an Apple product. Android has more problems than just menu-itis.]

The Sean Blog : Uploading Spreadsheets into SharePoint Lists

So you have an Excel 2007 spreadsheet that you want to convert into a SharePoint List (WSS 3.0 or MOSS). So you use the "Create" button on the website and you select "import spreadsheet". So Exel opens on your machine (you are using Windows/IE, right?) and asks you to identify the range of cells. So you do that. So Excel thinks for a while and says "Method 'Post' of object 'IOWSPostData' failed". So then what?

So then you need...

The Sean Blog : Uploading Spreadsheets into SharePoint Lists

You have to fix a bug in the code of the export add-on from Microsoft -- yes, "the greatest company in the world", and you have to fix their code to export from one of their products to another one of their products. Sean shows you how with pretty pictures. [The solution isn't original with him -- see his post -- but I found the screenshots helpful.]

He also mentions an add-on that permits two-way synchronization between Excel spreadsheets and SharePoint lists. I haven't tried that.

The Super-Simple Mindsharp Help Desk Link Feature

This blog post from SharePoint consultants Mindsharp show how to add a help desk link to the top chrome of every page on a farm -- with no "code". The link is wrapped in a control which is wrapped in another control which is wrapped in a feature which is wrapped in a solution... maybe not exactly super-simple, but this recipe lays it out in detail. Other similar changes might be accomplished with minor tweaks to the recipe.

Office 2010: Groove renamed to SharePoint Workspace

Office 2010: Groove renamed to SharePoint Workspace - Ars Technica.
Maybe Microsoft is revealing more than it meant to with the name change -- let's get to work (making money), no "grooving" allowed.

Overview of Creating an Event Receiver

This is a response to a request to sharepointdiscussions@yahoo.com. The poster was looking for a way to send alerts only when changes to a list would affect a particular view of a list. There is no built-in capability, but an Event Receiver could do it...

------ Forwarded Message
From: Paul Stork 
Date: Fri, 5 Sep 2008 08:31:45 -0500
To: "sharepointdiscussions@yahoogroups.com"

Subject: RE: [sharepointdiscussions] Re: Email alerts on a list

Sorry, no.  Here's an overview of how to create an Event Receiver.  You'll
need to write the code that would be used to send the email, but this will
give you the basic shell.

<http://blah.winsmarts.com/2006-7-sharepoint_2007__list_events_practical_example__creating_a_rigged_survey.aspx>

Paul Papanek Stork /
paul.stork@mindsharp.com / 216-272-0573 /
Know More. Do More.
SharePoint Server MVP, MCT, MCSE+I, MCSA, MCSD, MCDBA, MCITP, MCPD
MVP Profile

SharePoint does not support reverse-proxy path modification

Using a load balancer to rewrite an incoming URL can be an attractive option for some situations (for example, where an incorrect URL has been widely advertised). However, it may not be a good idea for SharePoint sites.

From Technet http://technet.microsoft.com/en-us/library/cc288609.aspx:

Some reverse proxy devices can modify the path of a request (the portion of the URL that comes after the hostname and port number) in such a way that a request sent by the user to http://www.contoso.com/sharepoint/default.aspx, for example, is forwarded to the Web server as http://sharepoint.perimeter.example.com/default.aspx.

This is referred to as an asymmetrical path. Microsoft Office SharePoint Server 2007 does not support asymmetrical paths. The path of the URL must be symmetrical between the public URL and the internal URL. In the preceding example, this means that the "/sharepoint/default.aspx" portion of the URL must not be modified by the reverse proxy device.

Consider having the load balancer send a redirect response back to the browser instead of rewriting on the way to the server.

Using formulas in SharePoint

Pointers from sharepointdiscussions@yahoo.com to answers for a question I have often asked myself but never researched: just what can you put into a SharePoint calculated column formula?

------ Forwarded Message 
From: Peter Brunone 
Date: Tue, 29 Jul 2008 10:02:06 -0500 
To: "sharepointdiscussions@yahoogroups.com" 

Subject: Re: [sharepointdiscussions] using formulas in sharepoint 

Hi Liz, 

    The functions look (to me) a lot like XPath, but I'm at a loss to put a 
name on them.  You can find an overview and a complete function list, 
respectively, at the following locations: 

Summary: 

http://office.microsoft.com/en-us/sharepointtechnology/CH011711541033.aspx 

Function List: 

http://office.microsoft.com/en-us/sharepointtechnology/CH011711171033.aspx 

    As you will discover, the "if" condition isn't quite what you'd expect 
as a .NET developer.  There's actually a function that performs a test and 
returns one of two parameters based on the result of the test; you can read 
the explanation of the IF function at the following location: 

http://office.microsoft.com/en-us/sharepointtechnology/HA011610101033.aspx 

    Yell if you need more help. 

Cheers, 

*Peter Brunone * 

*Improving*  – It's what we do. | www.improvingenterprises.com 


On Tue, Jul 29, 2008 at 9:01 AM, Liz.Pumilia wrote: 

> Hello all, 
> 
> 
> 
> I am brand new to using formulas in SharePoint. First question is what 
> language is the formula written in? Next question is how would I write a 
> formula to fill in a field called "Completed date"  that looks at a 
> field [status] and uses the current date if the status is "06-Complete". 
> The following is what I tried, but again it is just a shot in the dark. 
> 
> 
> 
> If [status]="06-Complete" then {Completed date]=current 
> 
> 
> 
> Any guidance would be greatly appreciated. 
> 
> 
> 
> Thanks, 
> 
> 
> 
> Liz Pumilia 
> 
------ End of Forwarded Message

Exit vs. Close - Reduce Login Prompts

A customer support FAQ entry, which skips the the technobabble about why you get the prompts and gets right to the mitigation strategy…

 

One frequently-asked question is, "Why does SharePoint asked repeatedly for my username and password? " Both Microsoft and WSU are working to make the problem go away altogether. In the meantime, if you use Internet Explorer, there is one simple trick you can do yourself to minimize the number of login prompts you get when opening Microsoft Office documents (Word, Excel).

When you open a Word or Excel document from a SharePoint site, Internet Explorer doesn't open the document itself. It hands the request off to Word or Excel, and the application contacts the server directly. The server asks the application for your username and password, and the application asks you. This prompt is unavoidable with today's technology, configuration, and requirements.

However, once Word or Excel has your username and password, it keeps them until the application "exits". If you don't exit the application, the next time the SharePoint server asks for them, the application can pass them along without bothering you about it.

So when you are done viewing (and possibly editing) a document from a SharePoint server, don't use the "X" in the upper-right corner of the window; that exits the application and throws away your login information. Instead, if you are using Excel 2003, Excel 2007, or Word 2003, use the "close document" icon that's just below the "Exit" "X".

If you are using Word 2007, there is no "Close" icon; but you can (1) click on the "Office button", and (2) click on the "Close" item at the bottom of the drop-down menu.

 

Either way, you only need to log in once for each application, not once for each document.

Arithmetic on Times in SharePoint

A note about how to calculate a field as the sum of two times. The catches
are the format of the "Length" field (probably better titled "Duration"), and the use of the "TEXT" function.

------ Forwarded Message
From: Tom <tomsrig@hotmail.com>
Reply-To: "sharepointdiscussions@yahoogroups.com"
<sharepointdiscussions@yahoogroups.com>
Date: Wed, 11 Mar 2009 00:50:43 -0000
To: "sharepointdiscussions@yahoogroups.com"
<sharepointdiscussions@yahoogroups.com>
Subject: [sharepointdiscussions] Re: Useing Time in SharePoint

Hi, Peter,

You gave me some additional information I did not know, but here is what I
came up with to make this time thing work:

=TEXT([Air Date & Time]+[Show Length],"h:mm:ss")

The [Air Date & Time] column could be set to either text or a date & time
format. [Show Length] must be in this format; hh,mm,sec. For example
01:10:30. The calculated value with then be the air date and time + show
length. Works good.

Your added tip .....""m/d/yyyy hh:MM:ss") gives me the way to combine the
date and time.

Thanks much, Peter

Tom

--- In sharepointdiscussions@yahoogroups.com, Peter Brunone
<peter.brunone@...> wrote:
>
> Hi Tom,
>
> Does the calculated field save if you use this formula? According to the
> reference at
> http://office.microsoft.com/en-us/sharepointtechnology/HA011610841033.aspx ,
> TIME(0, 0, 30) would yield a decimal value of something like 0.000347. To
> add this to a DateTime field, you'd probably have to convert the date/time
> stamp using TIMEVALUE to get a similar decimal value, e.g.
>
> =TIMEVALUE([Date & Time]) + TIME(0,0,30)
>
> Another option would be to use VALUE([Date & Time]) + VALUE("0:0:30") and
> see if that would work.
>
> Of course to re-convert that into a meaningful time figure, you'll have
> to use the TEXT function. I'm not sure of its ability to display seconds,
> but you should have that available (note that I'm extrapolating the time
> syntax below based on my knowledge of past and current Microsoft date
> nomenclature):
>
> =TEXT(TIMEVALUE([Date & Time]) + TIME(0,0,30), "m/d/yyyy hh:MM:ss")
>
> Something like this *should* work, if anything does. Let me know how it
> goes.
>
> Cheers,
>
> Peter
>
> On Mon, Mar 9, 2009 at 3:37 PM, Tom <tomsrig@...> wrote:
>
> > Hi, Peter,
> >
> > I use the TIME formula, like below.
> >
> > =[Date & Time]+TIME(0,0,[Offset Number])
> >
> > [Date & Time] in this example would be 3/5/2009 1:00 PM - but notice I
> > can't display seconds in a list.
> >
> > The [offset number] in this example would be 30 (meaning 30 secoonds)- This
> > number is set as a NUMBER. SharePoint has no way of setting seconds that I
> > know of, and that may be my dilema.
> >
> > Yet, if you look at the formulas in the Help file, it does show a way to do
> > hours, minutes and seconds.
> >
> > I'm stumped.
> >
> > --- In sharepointdiscussions@yahoogroups.com, Peter Brunone
> > <peter.brunone@> wrote:
> > >
> > > What is your formula, and how does it display?
> > >
> > > Is there a chance that the seconds are somehow rendered but not visible
> > in
> > > the browser?
> > >
> > > Peter
> > >
> > > On Mon, Mar 9, 2009 at 2:42 PM, Tom <tomsrig@> wrote:
> > >
> > > > Hi, all,
> > > >
> > > > I have a case where I need to take a Time like: 10:10:10 (ten hours,
> > ten
> > > > minues, and 10 seconds, and subract (or add) a number like 1:10 (one
> > minute
> > > > and 20 seconds. The result should be 10:10:09, (ten hours, nine
> > minutes.
> > > >
> > > > So far, I can't get seconds to appear using the calculated column, even
> > > > though the formulas do support seconds.
> > > >
> > > > Any ideas?
> > > >
> > > > Tom
> >
>
>
> [Non-text portions of this message have been removed]
>




------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/sharepointdiscussions/

<*> Your email settings:
Individual Email Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/sharepointdiscussions/join
(Yahoo! ID required)

<*> To change settings via email:
mailto:sharepointdiscussions-digest@yahoogroups.com
mailto:sharepointdiscussions-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
sharepointdiscussions-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/


------ End of Forwarded Message

ASP and ADO: When the Table Name is a Parameter

I am particularly proud (probably unreasonably) of this little piece of work; after a long afternoon and evening of googling, I was unable to find a useful solution on the web – so I worked one out myself! The solution itself has the merit of simplicity, and (as far as I can tell) originality. It may also be obvious to anyone but a dolt…

I have been working on hardening some of our ASP/SQL Server applications against SQL injection attacks. Typically, this involves identifying SQL commands that are built by concatenation with user-supplied variables and replacing them with SQL commands that are "parameterized", and setting the parameters to the user-supplied variables via ADO. Parameters can never be interpreted as database commands, so there is no danger of command injection.

Note that the term "user-supplied" here means _any_ information that is included in a request from the browser, regardless of its initial source. For example, information sent from the server and returned by the browser in a "cookie" must be considered "user-supplied", because malicious client software (or, absent SSL, a "man-in-the-middle") could modify that information before it re-arrives at the server.

To use parameters in ADO, you must utilize the Command object, rather than just the Connection object's "Execute" method or the recordset's Open method. Here's some sample code for a simple case (the code assumes that the connection objConn is open):

set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = objDBConn
objCmd.CommandType = adCmdText
objCmd.NamedParameters = True
objCmd.CommandText = "select field1, field2 from tblSurveyList where strSurveyNumber= ? "
objCmd.Parameters.Append objCmd.CreateParameter("", adChar, adParamInput, Len(strSurveyNumber), strSurveyNumber)
on Error Resume Next
set rsSurveyIndex = objCmd.Execute
On Error GoTo 0
if objDBConn.Errors.count <> 0 then
call handleError (objDBConn.Errors, SQLQuery, "survey list query")
end if

The parameter value given in "CreateParameter" call effectively replaces the "?" in the command text when the SQL is executed; but the technique is _not_ concatenation. Instead, ADO creates an SQL stored procedure and passes the value as a parameter to that procedure.

But what happens when the parameter you need to pass is actually a table name? For example, in our Silhouette survey system, each survey has its own table to store responses. When we record responses, we need to use the survey number as a variable to update the appropriate table. I tried something like this:


set objCmd1 = Server.CreateObject("ADODB.Command")
objCmd1.ActiveConnection = objDBConn
objCmd1.CommandType = adCmdText
' passing table name as parameter (fails)
objCmd1.CommandText = "select * from ? where strRespID = ?"
objCmd1.Parameters.Append objCmd.CreateParameter("", adChar, adParamInput, Len(strRespTable), strRespTable)
objCmd1.Parameters.Append objCmd.CreateParameter("", adChar, adParamInput, Len(strRespID), strRespID)
...

What I got for my pains was an error message: "The tablename variable @P1 must be declared." Google searches didn't answer the question directly, but I did find numerous posts about problems with passing table names into stored procedures. The solutions all used "dynamic SQL" -- EXEC statements plus concatenation, our old enemy. What to do?

I already had an "existence check" in the code to make sure that the response table exists before calling it for update. This check uses the "INFORMATION_SCHEME.tables" table maintained by SQL Server, in which the table name is an ordinary variable with a value that can be passed as a parameter:


'test if the response table exists
objCmd.CommandType = adCmdText
objCmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.tables where table_name = ?"
objCmd.Parameters.Append objCmd.CreateParameter("", adChar, adParamInput, Len(strRespTable), strRespTable)

on Error Resume Next
set rsTableExists = objCmd.Execute
On Error GoTo 0
if objDBConn.Errors.count <> 0 then
call handleError (objDBConn.Errors, SQLQuery, "response table does not exist")
end if


If this check is successful, I reasoned, the value of the response table name must be valid, and can be included in the command text via concatenation without concern about injection.

The final query looks like:


objCmd1.ActiveConnection = objDBConn
objCmd1.CommandType = adCmdText
' cannot pass table name as parameter (apparently) -- but strSurveyResponse is a table that exists
' (per success of Response Table Existence query above) so concatenation is safe
objCmd1.CommandText = "select * from " & strRespTable & " where strRespID = ?"
objCmd1.Parameters.Append objCmd.CreateParameter("", adChar, adParamInput, Len(strRespTable), strRespTable)
objCmd1.Parameters.Append objCmd.CreateParameter("", adChar, adParamInput, Len(strRespID), strRespID)

What makes a bad virus bad?

Harder to detect, harder to remove... how do modern viruses fight back against the white hats? A reasonably technical, reasonably legible answer from Patrick Fitzgerald of Symantecx: W32.Virut.CF—Collateral Damage.