Monday, August 30, 2010

Basic sharepoint web part in .NET

1     Created a new Windows Class Library project in VS2008
2     Added a reference to System.Web
3     Added the following ‘using’ statements:
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
4     Changed the default class from public class Class1 to public class Hello: WebPart
5     Wrote an override for the “RenderContents” function (I got fancy and stripped off the leading domain portion of the login, and later wrote more code to change the font and add the little % bar)
protected override void RenderContents(HtmlTextWriter writer)
{
 string name = this.Context.User.Identity.Name;
 writer.Write(" You are logged in as " + name + "! ");
}
6     Built the dll in release mode and put it in a shared dir on my machine.
7     Logged onto the mossdev server and copied the dll to the sharepoint server into the web server’s ‘bin’ directory (\Inetpub\wwwroot\wss\VirtualDirectories\80\bin)
8     Added a line to web.config to make the server trust the dll (\Inetpub\wwwroot\wss\VirtualDirectories\80\web.config). In the section of web.config, I added a entity that looked like this:
9     Went onto sharepoint, went to site settings (for the whole site), and chose ‘Web Parts’ under Galleries. Clicked New, found my ‘Hello’ part, and clicked ‘Populate Gallery’ which added it for use on the site.

1    Went to a site and added the part to my front page. Opah!

Tuesday, August 24, 2010

automating silent MSI installs

I had to dig a bit to figure this out, and I know I've done this before so I'm making a note here so I can find this information again the next time I forget about it.

To automate a standard MSI install, run it once and log all the properties to a file:
msiexec /i "My Program Installer.msi" /Lp options.log

Make a note of anything you change during this first run. Then take a look at your options.log file. It should have a lot of items like this:

Property(C): ProgramFilesFolder = C:\Program Files\
Property(C): SourceDir = E:\
Property(C): VersionNT = 501
Property(C): ALLUSERS = 1
Property(C): INSTALLLOCATION = C:\Program Files\Whoever\Whatever
Property(C): Manufacturer = Whoever


You can run a silent install by setting these properties on the command line. The ones in all caps are "public" so they can be set by msiexec:

msiexec /qn /i "My Program Installer.msi" INSTALLLOCATION="C:\MyFolder\Whatever"

That's all there is to it! Find the properties you want, pass them on the command line. I have no idea why I can't seem to remember the steps to do that.

Thursday, May 27, 2010

Going Meta

Lately I've been using some of the same tricks over and over again in different contexts. Essentially I've just been doing a lot of complex queries, where I select a set of records first, and then select other data out of the first set:

select my_set.X, my_set.Y, my_set.Z
from (
select * from whatever_it_is
where my_item = 'something'
) my_set


Why would I need to do this? Well, for one thing it breaks a problem down into smaller bits, so it is easier to work on. I can write and validate a query that gets all the records I might want to work with, and then write two or three queries that use it to produce friendlier output. I can also use it to do things like counts:

select my_set.X Item,
count(case when my_set.Y = 'Chicago' then 1 else null end) Num_Chicago,
count(case when my_set.Y = 'Phoenix' then 1 else null end) Num_Phoenix,
count(my_set.Y) Total
from (
select * from whatever_it_is
where my_item = 'something'
) my_set
group by my_set.X

This will produce something that looks like this:



ItemNum_ChicagoNum_PhoenixTotal
Item 1 4 1 5
Item 2 2 2 4

You can do grand totals using a UNION ALL with another query:

select my_set.X Item,
count(case when my_set.Y = 'Chicago' then 1 else null end) Num_Chicago,
count(case when my_set.Y = 'Phoenix' then 1 else null end) Num_Phoenix
from (
select * from whatever_it_is
where my_item = 'something'
) my_set
group by my_set.X
UNION ALL
select 'Totals',
count(case when my_set.Y = 'Chicago' then 1 else null end) Num_Chicago,
count(case when my_set.Y = 'Phoenix' then 1 else null end) Num_Phoenix,
count(my_set.Y) Total
from (
select * from whatever_it_is
where my_item = 'something'
) my_set

The key to doing a UNION is you need your columns to match up. So note that I have the same number of columns (3) and I used the same names (Item, Num_Chicago, Num_Phoenix, Total) to describe them. By not including my_set.X in the second query I end up with total counts of all the rows, not simply of one item. The output from this should look like:




ItemNum_ChicagoNum_PhoenixTotal
Item 1 4 1 5
Item 2 2 2 4
Totals 6 3 9

I've used the same sort of complex queries to pull the earliest or latest item by category from a set of records; it is a fairly simple concept but can be very powerful.

SQL*Plus formatting and automation

Here is some advice on how to run your SQL and generate a file with the delimiter of your choice.


  1. Login to sqlplus
  2. Set your page size (how often you get headers – so set this to be arbitrarily large), line size (try to make this long enough to hold a complete row of data), column separator, and suppress the “X rows selected” feedback.
    set pagesize 5000 linesize 500 colsep ',' feedback off
  3. Setup an output file
    spool output.txt
  4. Run your sql command
    select ... from ... where ... ;
  5. Turn off the output
    spool off
    At this point you should have a file called output.txt with your data. Don’t worry if you have some command data in there, just read on.
  6. You might also read up on how to format columns – in particular you can have nicer column headings:
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch6.htm

Once you have tweaked these settings to your liking, you can automate the process pretty easily. Put your commands into a file. To suppress output on screen you can set termout off (this only works with a command file):

set pagesize 5000 linesize 500 colsep ',' feedback off termout off
spool output.txt
select whatever;
spool off
quit

Then run sqlplus with that file as an argument (the -S will run the commands silently):

sqlplus -S login/pwd@server @commands.txt

An added bonus of doing it this way is you won’t see the sql commands in your output file anymore.

This is particularly handy if you need to do some scripting in an environment where you don't have control over what libraries are installed; I recently used something similar on a unix machine that had Perl installed but not DBD::Oracle. I shelled out and ran my query using sqlplus, and parsed the output in the script. It won't perform as well as a built-in, but for occasional use it is very low rent.

Sunday, April 25, 2010

iPad and DHCP

I started wondering about the iPad and DHCP after noticing in my router's log files that the iPad was pretty chatty; re-associating with the AP really often even when sleeping. Here is about 40 minutes' worth of router logs, and notice the iPad associates with the AP every minute or so (I replaced my MAC address with "iPad" here):

[INFO] Sun Apr 25 14:39:52 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:39:52 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:38:52 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:38:52 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:37:51 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:37:51 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:36:51 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:36:51 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:35:51 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:35:50 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:34:50 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:34:50 2010 Wireless system with MAC address "iPad" associated
... more of the same ...
[INFO] Sun Apr 25 14:03:10 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:03:10 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:03:01 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:03:01 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:02:01 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:02:01 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:01:53 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:01:53 2010 Wireless system with MAC address "iPad" associated
[INFO] Sun Apr 25 14:00:53 2010 Lease 192.168.0.109 renewed by client "iPad"
[INFO] Sun Apr 25 14:00:53 2010 Wireless system with MAC address "iPad" associated

The really scary thing about this is the iPad was asleep, sitting on the charger for all but the last 10 minutes or so of this log. So it appears to be chatting up the network even when you're not using it.

I ran a network sniffer from another machine to capture broadcast traffic on the wireless LAN which backed up what I saw in the router log; I have a DHCP request and acknowledgment about every minute, as well as a bunch of ARP traffic. This is the sequence that was repeated every minute:


Time Source Destination Protocol Info
35.434182 0.0.0.0 255.255.255.255 DHCP DHCP Request - Transaction ID "iPad"
35.440707 192.168.0.1 255.255.255.255 DHCP DHCP ACK - Transaction ID "iPad"
35.441587 "iPad" Broadcast ARP Who has 169.254.255.255? Tell 192.168.0.109
35.442794 "iPad" Broadcast ARP Gratuitous ARP for 192.168.0.109 (Request)
35.533334 "iPad" Broadcast ARP Gratuitous ARP for 0.0.0.0 (Request)
35.793304 "iPad" Broadcast ARP Who has 169.254.255.255? Tell 192.168.0.109
36.194540 "iPad" Broadcast ARP Who has 169.254.255.255? Tell 192.168.0.109
36.444386 "iPad" Broadcast ARP Who has 192.168.0.1? Tell 192.168.0.109
36.595654 "iPad" Broadcast ARP Who has 169.254.255.255? Tell 192.168.0.109
41.468674 "iPad" Broadcast ARP Who has 192.168.0.2? Tell 192.168.0.109


Now a normal DHCP request has 4 packets: discover, offer, request, and acknowledgment (or 'ack'). A DHCP renewal just uses the last two: the request and the ack. There's something fishy about this, though:

  1. For a DHCP renewal it is common to send my request packet to the DHCP server I've been using; that is, the first packet should be to 192.168.0.1, not to the broadcast address 255.255.255.255.
  2. One interesting item in the ARP requests is that the iPad looks for address 169.254.255.255, which is reserved as an "autoconfiguration" address for devices that cannot be configured using DHCP. Why would it be looking for that address if it had processed the DHCP acknowledgment?
  3. I think the iPad must have seen the DHCP acknowledgment because it looks for the DNS servers, 192.168.0.1 and 192.168.0.2, listed in the DHCP ack. How else would it be interested in those specific IP addresses?
  4. This could be a WiFi association problem instead of strictly DHCP-related; if it were aware that it was still on my network it wouldn't need to send a DHCP renewal for 12 hours, since my lease times are set up for 24. Why is it re-associating every minute and sending a fresh DHCP request? Is it because it doesn't know it has a lease, or because it doesn't know what network it is attached to? I think it could be either.
  5. If the machine associates to a new wireless access point (or doesn't realize it is on the same network), there's a good reason for it to send out a DHCP request packet (though honestly I think it should be starting over at 'discover' if it doesn't know where it is) - it could be that sending the request packet to the broadcast address was an attempt to workaround some problem with renewals on the same network versus requests on a new network.

This is all a mystery to me, but I will say I think there's something odd going on with the network interface on the iPad. The next step is to setup a wireless access point with a sniffer onboard to see if I can see not only the broadcast traffic, but any direct communication between the router and the iPad as well. I welcome comments from people who may know more than I do about DHCP, the iPad, or wireless networking.