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:

Item	Num_Chicago	Num_Phoenix	Total
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:

Item	Num_Chicago	Num_Phoenix	Total
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.

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.

Reinstalling Windows on a netbook

A friend of mine and I both bought HP mini netbooks when they came out last year. As it turns out, we were both ready to reload them and pass them along to other people this year. I spent some time figuring out how to do this, and wrote up instructions for my friend. They worked for him, so maybe they can also help you. Use these instructions at your own risk.

Some things to know before you get started:
1. The first thing you'll need to do is prepare an SD card with the Windows install and the contents of your HP driver disc. I did that using a lot of help and tools I found here: http://www.liliputing.com/2008/04/install-windows-xp-on-mini-note-usb.html - I used a 4GB flash card so I'd have room to copy the HP disc onto the same card, which ended up working well. You don't need a special process with the HP disc, just copy the files into a folder on the card.

2. You need the CD-Key that is on the sticker on the bottom of your laptop (check to see you haven't worn it off somehow, nice placement HP) and you'll need to activate windows once it is installed (it went smoothly for me, and I did it twice).

Here is the procedure as I explained it to my friend:

1. You might want to make sure the little tab on the SD card is set to 'Lock' - slide it down away from the business end of the card. I set it that way and I think it is safest, that should make it read-only.
2. Insert SD card. It is probably best to remove all the other flash drives, peripherals, etc. from the machine to simplify things.
3. Boot, hit F9 to choose boot device, choose the flash card (should say USB I think)
4. You'll get a standard XP boot prompt - choose the text only install (should be labeled '1.') to start (you'll switch to GUI after reboot)
5. Follow windows setup prompts:

a. Even if you already have a C:\ drive, go ahead and delete the partition on your internal disk and start again. Format it NTFS Quick.

b. If for some reason the SD card comes up as drive C:\, try to change it so your internal disk gets to be C:\ - I don't think this will happen to you since you already have windows on the machine but it happened to me when I switched from Linux back to Windows.

6. Keep watch while windows formats the disk and copies the appropriate files (approx. 10 minutes or so), so you can be ready when the system reboots.
7. On reboot, hit F9 to choose the boot device again!
8. You'll get a standard XP boot prompt - choose the GUI install option this time.
9. This should be the standard XP setup you're used to. Sadly the USB isn't really any faster than a CD, but you don't need to babysit it.

a. Type in your CD Key from the sticker on the bottom of your machine, crap, no mouse! use a combination of tab, enter, 'N' key to move on.

b. It will wait for you to type in the CD key, and your first reboot after the install will probably fail, so its ok to wander off while this runs.

c. For some reason mine took FOREVER at the "Removing any temporary files used... 1 minute remaining" part. It was a LOT more than 1 minute.

10. Once windows has installed, the system will try to reboot but you will get an error:

"Windows could not start because the following file is missing or corrupt: \system32\hal.dll. Please re-install a copy of the above file."

Hit enter and the system should try to reboot. Hit F9 and boot from the SD card again. Choose the GUI install option again. This should actually boot you into windows. I have no idea why this works, haha.
11. You should be presented a login screen with two users: Owner and UserXP. I've been using Owner and it seems to work. Click Owner to login.
12. Windows will gripe a bit about the display being low-res. Don't worry about it; the HP driver install will fix all of that.
13. Start->My Computer->C:\->show contents. You should have a boot.ini file in C:\ - you might need to go to Tools->Folder options and change some of the settings to see it, but probably not. double-click boot.ini to edit the file. You shouldn't need to change much. I made mine look like this:

    [Boot Loader]
    timeout=1
    Default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
    [Operating Systems]
    multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Microsoft Windows XP Home Edition" /noexecute=optin /fastdetect

14. Remove the UserXP user account from the control panel. Start, Control Panel, User accounts, click on UserXP, click 'Delete this account', don't bother saving files or anything.

15. Go to the SD card (mine was D:\ - the label is USB_XP_710), open the HP folder, open SWSETUP, open APPINSTL, double-click SETUP.EXE

a. Open the tree and uncheck boxes for things you don't want to install - for instance, I don't have bluetooth or a WWAN card so I unchecked these underneath "Hardware Enabling Drivers," and I didn't want instant messenger so I unchecked it under "Recommended Software Applications"

b. In particular, don't bother installing Java. It doesn't seem to work consistently. Just uncheck it and install it from the web later if you need it.

c. Click 'Install' and walk away for an hour or so. It will pop up some boxes that look like you need to do something, but you don't. Just wait the process out. You'll be prompted to reboot at the end.

16. Reboot. Don't hit F9 this time and see if the system can boot on its own. If it can, you're ready to run windows update, activate windows, etc. If it can't try booting from the SD card again and see if you can figure out what has happened. The boot ini needs to point to the internal hard drive (usually disk 0, partition 1 but there might be a reason it is something else).


I hope this helps you. Use these instructions at your own risk.

Database Tracing in Oracle

You can trace statements in Oracle using a package called "dbms_monitor" - it allows you to see statements that are executed along with some indication of how efficient they are (sort of like using an explain plan, but after the fact). Why would you want to do this? Well, if you have an application that is misbehaving or if you want to know more about how your statements are running live (as opposed to using the explain plan) this can help. Also, if you have a closed package and want to know more about what it is doing in the database this might help. You'll need to be a DBA or be friendly with your DBA to do this, as it requires some privileges and logs are written to the database servers' filesystem.

1. Get access to the dbms_monitor package. Your DBA can grant you rights. I do not know the details on this step, I'm afraid.
   
   
2. Do a select to find out what sessions are active:
   

   
     SELECT sid, serial#, client_identifier, service_name, action, module, username
     FROM V$SESSION 
   

   
   I'd actually use a "WHERE username='my_username'" clause of some kind here to limit what I get, but YMMV. You'll end up with rows that look something like this:
   

   
   SID SERIAL# CLIENT_IDENTIFIER SERVICE_NAME ACTION      MODULE     USERNAME
   266 24923                     My_Instance  SQL Window  SQL Devel  my_username
   

   
   
3. Enable logging:
   
   
   
   1. To log by user session, you need to make a note of the SID and SERIAL# columns, and run this to begin tracing:
      

      
        begin
        dbms_monitor.SESSION_TRACE_ENABLE(266, 24923, TRUE, TRUE);
        end;
      

      
      
   2. To log by service and module, you can use those columns:
      

      
        begin
        dbms_monitor.serv_mod_act_trace_enable('My_Instance', 'SQL Devel', 
          DBMS_MONITOR.ALL_ACTIONS, TRUE, TRUE, NULL);
        end;
      

      
      An upside to doing tracing by service is you can verify it is running:
      

      
      select * from DBA_ENABLED_TRACES
      
      TRACE_TYPE      PRIMARY_ID  QUALIFIER_ID1 QUALIFIER_ID2 WAITS BINDS INSTANCE_NAME
      SERVICE_MODULE  My_Instance SQL Devel                   TRUE  TRUE 
      

      
      
   
   
4. At some point you will have had enough logging, and you'll want to turn it off.
   
   
   
   1. If you are tracing by user/session:
      

      
        begin
        dbms_monitor.SESSION_TRACE_DISABLE(266, 24923);
        end;
      

      
      
   2. If you are tracing by service/module:
      

      
        begin
        dbms_monitor.serv_mod_act_trace_disable ('My_Instance', 'SQL Devel');
        end;
      

      
      note: with service level tracing you can select * from DBA_ENABLED_TRACES to verify tracing has been turned off, just as you did to determine it was on.
      
   
   
5. So you've traced some information. Where is it? In a file. Where is the file?
   

   
   select value from v$parameter where name = 'user_dump_dest'
   
   VALUE
   /usr/local/oracle/10204/admin/My_Instance/udump
   

   
   
6. Once you have the file (ask your DBA for the contents of that folder, or better yet the files from today, or this hour or something) you might wonder what is inside. Well, it is only semi-readable at this point. You need to use a utility called TKPROF to reformat the trace into something you can really read. I found I already had a copy of TKPROF in my oracle bin directory:
   

   
   "C:\oracle\product\10.2.0\client_1\BIN\tkprof.exe" c:\my_instance_ora_23486.trc c:\my_oracle.log
   

   
   At this point, c:\my_oracle.log should contain a list of SQL statements and performance data for each statement for as long as you had tracing on.
   
   
7. For more information, check out Oracle's site:
   http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm
   

tracing in ASP.NET

Just a handy code snippet to add to your code if you've enabled tracing in your web.config:

If tracing is enabled, you may view trace information using <a href=\"http://" + this.Context.Request.Url.Authority + this.Context.Request.ApplicationPath + "/Trace.axd\">Trace.axd</a>

Encrypting connection strings in .NET

There is a lot of documentation on the web about this (my favorites are at the bottom of this post) but I really wanted a summary of how to encrypt strings using my own key that could be moved from server to server, and how to access those strings from a web app / code-behind. So this is what I came up with. I'm posting it so I can return to it later as I learn more; take it with a grain of salt.

Generating an exportable / movable RSA key and encrypting using your custom key

---

1. Generate an RSA Key container
   
aspnet_regiis -pc "SampleKeys" –exp

   
   
2. Tie the RSA key container to your web.config (<configProtectedData> is a top-level key, like connectionStrings or appSettings) - note that this should be identical to the config for RsaProtectedConfigurationProvider in your machine.config, with a different value for the defaultProvider, name and keyContainerName attributes.
   

   
   <configProtectedData defaultProvider="SampleProvider">
      <providers>
        <add name="SampleProvider" type="System.Configuration.RsaProtectedConfigurationProvider,System.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" description="Uses RsaCryptoServiceProvider to encrypt and decrypt" keyContainerName="SampleKeys" cspProviderName="" useMachineContainer="true" useOAEP="false"/>
      </providers>
   </configProtectedData>
   

   
   
   
3. Grant access to the key container to the user your process runs as
   
aspnet_regiis -pa "SampleKeys" "ASPNET"

   
   
   
   1. This sets up permissions for the "default" RsaProtectedConfigurationProvider. I had to set this up for the ASPNET user along with permissions for my own container above.
      
aspnet_regiis -pa "NetFrameworkConfigurationKey" "ASPNET"

      
   2. If the user your process is running as doesn't have permission to use the provider, you'll get an error "Failed to decrypt using provider 'RsaProtectedConfigurationProvider'."
      
      
   3. I recently ran into a server that didn't have the "NetFrameworkConfigurationKey" - I think this is part of the normal .NET Framework setup so I don't know why it might have been missing. Generating the key fixed the problem:
      
aspnet_regiis -pc "NetFrameworkConfigurationKey"

      
   
   
   


4. Encrypting strings - if you're not using the default site you'll need the site identifier (shown in IIS manager window under "Web Sites"?)
   
aspnet_regiis -pe "connectionStrings" -site 1 -app "/SampleApplication" -prov "SampleProvider"

   
   
5. Other Useful Commands
   
   
   
   1. Decrypting strings (in case you need to change something)
      
aspnet_regiis -pd "connectionStrings" -app "/SampleApplication"

      
      
   2. Export the public and private keys for import on another machine
      
aspnet_regiis -px "SampleKeys" keys.xml -pri

      
      
   3. Importing the keys
      
aspnet_regiis -pi "SampleKeys" keys.xml

      
      
   4. Removing access to a no longer needed key
      
aspnet_regiis -pr "SampleKeys" "ASPNET"

      
      
   5. Deleting a no longer needed key container
      
aspnet_regiis -pz "SampleKeys"

      
   
   

Using encrypted strings in code

---

Most of the canned demo code on the internet shows you opening, testing for the strings to be encrypted, decrypting them, then saving the config file back. I don't think I want to save the unencrypted strings, as this would seem to defeat the purpose of encrypting them in the first place. I may be misunderstanding the code samples, but this is what I came up with and it makes more sense to me.

       static string GetProtectedConnectionString(string name)
       {
           // Open the Web.config file.
           Configuration config = WebConfigurationManager.
               OpenWebConfiguration("~");

           // Get the connectionStrings section.
           ConnectionStringsSection section =
               config.GetSection("connectionStrings")
               as ConnectionStringsSection;

           // Toggle encryption.
           if (section.SectionInformation.IsProtected)
           {
               section.SectionInformation.UnprotectSection();
               ConnectionStringSettings s = section.ConnectionStrings[name];
               return s.ConnectionString;
           }
           else
           {
               throw new ConfigurationException("Warning: connection strings are not encrypted");
           }           
       }

This code probably needs a bit more cleanup. The point is, you don't save the unprotected section, but you decrypt the section long enough to get the string from the config file that you were looking for. Note that you could easily modify this to just return the connection string whether or not it was encrypted but I decided I'd rather be notified if the config ends up unprotected for some reason. Which brings up a valuable point: when you re-publish your site, you will need to re-encrypt your strings! Add this as a post-build step!


Useful links

---

Securing Connection Strings
http://msdn.microsoft.com/en-us/library/89211k9b(VS.80).aspx

Encrypting Configuration Information Using Protected Configuration
http://msdn.microsoft.com/en-us/library/53tyfkaw(VS.80).aspx

Importing and Exporting Protected Configuration RSA Key Containers
http://msdn.microsoft.com/en-us/library/yxw286t2(VS.80).aspx

Walkthrough of encrypting connection strings and accessing them from ASPx
http://msdn.microsoft.com/en-us/library/dtkwfdky(VS.80).aspx

How To: Encrypt with ASP.NET 2.0 using RSA
http://msdn.microsoft.com/en-us/library/ms998283.aspx