Follow me on Twitter...

So, I finally bit the bullet and started using Twitter a bit more - I have had an account for some time, but now that I use Digsby, I actually follow people.

http://twitter.com/AdamNofsinger

Anyone else I know - and who reads this blog I guess ;) - use Twitter at all? I hardly ever update compared to most people, but I felt like I was still web 1.0 not using it. Maybe it's just a Silicon Valley fad.

Using BCP Format Files and SQL BULK IMPORT

You have a bunch of data - let's say locations that you are putting in a store locator database and application - which was given to you by a customer as an Excel spreadsheet. Or maybe even a CSV or Tab-delimited file.

You want to import that data into an SQL database table you coded up with a very similar schema to the data file. Here's what your database table looks like - pretty standard and minimal for this domain:

CREATE TABLE [dbo].[Locations](
[LocationID] [int] IDENTITY(1,1) NOT NULL,
[LocationName] [varchar](500) NOT NULL,
[PhoneNumber] [varchar](50) NOT NULL,
[Address1] [varchar](100) NOT NULL,
[Address2] [varchar](100) NOT NULL,
[City] [varchar](100) NOT NULL,
[StateProvince] [varchar](20) NOT NULL,
[ZipPostalCode] [varchar](20) NOT NULL,
[Latitude] [decimal](18, 9) NULL,
[Longitude] [decimal](18, 9) NULL,
CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED ([LocationID] ASC)
)

Here is an example of what your data file might look like, if it is Comma Delimited for instance:

Bob's Meat Mart,565-555-1236,Bob Wallace,423 Highland Ave.,Juneau,WI,54701,44.778885,-91.478939
Alvin Dairy Ranch,565-555-4875,Greg Alvin,1032 Caledonia St.,Plain,WI,54603,43.838412,-91.246566

So you can see in the data file, the order of the columns is like this:

LocationName
PhoneNumber
OwnerName
Address
City
State
ZIP
Latitude
Longitude

It doesn't exactly match up with the order, and number of columns, in our database. If you try to import this data file using a minimal BULK INSERT command such as the following, you will get some error messages like I show below the command and the process will fail completely:

BULK INSERT Locations
FROM 'C:\Work\StoreLocator\Docs\TestData.csv' WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)


Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (LocationID).
Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (LocationID).

Format File to the Rescue

What we need is a BCP Format File to tell the BULK INSERT command how to manage the difference in number of columns and order of columns. I'll brush over the steps I used here to get one of those - there maybe a different or better way, but this works for me.

You can use the bcp.exe tool that comes with SQL Server 2005 (earlier versions too I think) to generate a "first draft" of a format file. BCP can connect to your database, look at the schema of a table you specify, and then generate a format file which, in its first draft, would describe a 1:1 mapping between a data file and that table.

Note: if you are having trouble getting bcp.exe to connect to your database, make sure that you turn on remote connections (I read somewhere that that is how bcp.exe connects), and that the user you are running the command as has proper access on the database/table. Another trick I figured out was to specify the server instance name using the -S ServerName parameter on the command - I have a couple of database instances, and for some reason these tools tend to default to the wrong one.

Another Note: After struggling for hours trying to get this to work with the XML format file, I tried it with the non-XML format file and got it working in minutes. I won't waste your time trying to explain that whole frustrating process, but I suggest you stick with the non-XML format file.

So, here is the bcp.exe command I came up with to create my non-XML format file, and what it generates:

> bcp StoreLocator.dbo.Locations format nul -T -n -f LocationsFormatFile.fmt -S localhost\SQLEXPRESS


9.0
10
1 SQLINT 0 4 "" 1 LocationID ""
2 SQLCHAR 2 500 "" 2 LocationName SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 2 50 "" 3 PhoneNumber SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 2 100 "" 4 Address1 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 2 100 "" 5 Address2 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 2 100 "" 6 City SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 2 20 "" 7 StateProvince SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 2 20 "" 8 ZipPostalCode SQL_Latin1_General_CP1_CI_AS
9 SQLDECIMAL 1 19 "" 9 Latitude ""
10 SQLDECIMAL 1 19 "" 10 Longitude ""

Now we need to edit this file so that it actually represents what is in a row on the input data file and things get mapped to the correct column in the table. Also, we'll need to make BULK IMPORT skip the extra "OwnerName" field we have in the data, but not in the table. Here are the articles I read over to figure out how to do that, and the format file I ended up with:

• Understanding non-XML Format Files


• Using a format file to skip a Table Column


• Using a format file to skip a Data Field

9.0
9
1 SQLCHAR 0 500 "," 2 LocationName SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "," 3 PhoneNumber SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 500 "," 0 EXTRA SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "," 4 Address1 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 100 "," 6 City SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 20 "," 7 StateProvince SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 20 "," 8 ZipPostalCode SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 19 "," 9 Latitude ""
9 SQLCHAR 0 19 "\r\n" 10 Longitude ""

So, now everything was looking good, except I got a complaint about the Address2 field not being NULL, so I had to modify the script a little bit to support this. Nothing you can do in the Format file.

-- Temporarily set Locations.Address2 to NULLable
ALTER TABLE Locations
ALTER COLUMN [Address2] [varchar](100) NULL

BULK INSERT Locations
FROM 'C:\Work\StoreLocator\Docs\TestData_TSV.txt' WITH (
FORMATFILE='C:\Work\StoreLocator\Database\LocationsFormat.fmt'
)

-- Blank all of the NULL Address2 fields, and rest to NOT NULLable
UPDATE Locations
SET Address2 = ''
WHERE Address2 IS NULL

ALTER TABLE Locations
ALTER COLUMN [Address2] [varchar](100) NOT NULL

Success!

StackOverflow

Not sure why, but StackOverflow.com seems pretty neat to me. It's like a free Experts-Exchange (which I have only used a tiny bit, mostly to steal other peoples questions/answers) only it is better in many respects.

Anyhow, here is me on stackoverflow.com :

http://stackoverflow.com/users/18524/noffie

Beware of Ad-Blocking!

So, I just spent a good 6 hours trying to diagnose the strangest little quirk I was having. In one of our products, we use a Flash map selection product called TrueVector. This is implemented as a flash file (tvfm.swf) which itself grabs various configuration files (tv_cfg.xml) and xml data files from your web server in order to load up with a pretty little map you can select states and zip codes on. I have blogged about this troublesome control before (most of the trouble seems to be in getting all the files in the file structure and in the config file listings just right so that the flash successfully finds them).

The map solution was working fine, until I moved the page (and thus, out of necessity, some of the supporting files such as tv_cfg.xml) to a subdirectory in the web, specifically /Ads/campaign_edit.aspx. Now, the flash file would load up, but it would immediately display an error Failed to load main configuration data from: tv_cfg.xml. Now, the tv_cfg.xml file was in the same relative spot it was before in the root of the web page, and I actually had it in several other places just for testing. Firebug was not even showing a request for this file in the Net tab. To further confuse me, I tested this page in Internet Explorer, then in Firefox 2, then in Firefox 3 (where I was having the issue) on a virtual machine - they all loaded the map fine. I finally started getting close when I thought to try loading my development Firefox 3 in safe-mode, with all of the add-ons disabled. Now, it worked! Through the process of elimination, and to make a long story short, I finally figured out that AdBlock was causing the problem. I pulled up the Blockable Items (Ctrl-Shift-V) page and smacked myself on the forehead:

I could have just added an exception to AdBlock, but I decided instead to go through the work of renaming this directory in the website. That way, I can be more sure that if any of our customers use AdBlock or similar software, that they will not have issues with the site.

Firefox 3 and Self-Signed SSL Certificates at http://localhost

I'm putting this here because it was too hard to find on google -- I guess there are variations of this problem with the new Firefox 3 SSL error handling functionality.

Problem: When you browse to a secure site on your local computer using 'localhost' -- https://localhost/website -- you get a modal dialog stating

...
The certificate is not trusted because it is self signed
(Error code: sec_error_ca_cert_invalid)
[Ok]

You can go no further.  If you try to manually add an exception using the advanced properties of Firefox, you will hit the same wall again when you tell Firefox to fetch the certificate you want to add an exception for. 

Cause: It's a bug in FF3, specifically with IPv6 stuff I guess. https://bugzilla.mozilla.org/show_bug.cgi?id=435558

Solution: There are two workarounds.  The first is to use 127.0.0.1 instead of localhost.  The second is to disable IPv6 tunneling (which you probably don't use now anyhow) by going to 'about:config' and setting network.dns.disableIPv6 to true.

Issues adding CUPS printers to Samba with cupsaddsmb

So, once again I find myself trying to get printers from CUPS to Samba on a debian linux print/file server. I didn't have any luck with cupsaddsmb command last time, but I thought "What the heck, I know a bit more about linux now" and decided to try using it again. (The alternative is to manually add the printers to Samba, and then manually install the drivers in Samba using rpcclient or something).

I was following this somewhat updated guide. I'll just record here the error messages I got when trying to run

cupsaddsmb -v -U root -a

and the corresponding fixes.

First error messages (they typically just keep repeating if you have '-v' on, otherwise it silently fails):

Unable to copy Windows 2000 printer driver files (1)!
Running command: smbclient //localhost/print$ -N -A /tmp/4868f5046ce91 -c 'mkdir W32X86;put /tmp/4868f4ff23c03 W32X86/PUB_Conference_HPLJ5n.ppd;put /usr/share/cups/drivers/ps5ui.dll W32X86/ps5ui.dll;put /usr/share/cups/drivers/pscript.hlp W32X86/pscript.hlp;put /usr/share/cups/drivers/pscript.ntf W32X86/pscript.ntf;put /usr/share/cups/drivers/pscript5.dll W32X86/pscript5.dll'
Domain=[WORKGROUP] OS=[Unix] Server=[Samba 3.0.30]
Server not using user level security and no password supplied.
Server requested LANMAN password (share-level security) but 'client use lanman auth' is disabled
tree connect failed: SUCCESS - 0

I guess this is a sort of bug in something, where the solution (workaround) is to add

client lanman auth = yes

to your smb.conf and restart samba

/etc/init.d/samba restart

One error message down, another one crops up. :-)

Unable to copy Windows 2000 printer driver files (1)!
Running command: smbclient //localhost/print$ -N -A /tmp/4868f64feb286 -c 'mkdir W32X86;put /tmp/4868f64a04e87 W32X86/PUB_Conference_HPLJ5n.ppd;put /usr/share/cups/drivers/ps5ui.dll W32X86/ps5ui.dll;put /usr/share/cups/drivers/pscript.hlp W32X86/pscript.hlp;put /usr/share/cups/drivers/pscript.ntf W32X86/pscript.ntf;put /usr/share/cups/drivers/pscript5.dll W32X86/pscript5.dll'
Domain=[WORKGROUP] OS=[Unix] Server=[Samba 3.0.30]
Server not using user level security and no password supplied.
tree connect failed: NT_STATUS_WRONG_PASSWORD

I had a feeling the line about "Server not using user level security and no password supplied" had something to do with it. On a whim, I opened up my /etc/samba/smb.conf again and changed

security = SHARE

to

security = USER

Hooray! That seemed to do the trick. Now the cupsaddsmb command runs to completion for each printer, and states that the drivers are installed for it. Of course, I'll change my security back to SHARE after I'm done installing the browsers. Guess I'll have to switch it to USER and restart samba every time I want to add another printer. Not much of a sacrifice compared to doing it the manual way -- I might switch to user based security if our company expands anyhow.

Flash to Javascript calls in Internet Explorer

I was having trouble getting a Flash that uses "FSCommand()" to send messages to javascript on a page working in Internet Explorer 7 (this applies to other versions too I'm assumming).  Worked fine in Firefox just having the

function flashEmbedID_DoFSCommand(command, args) {}

javascript function defined somewhere in the page.  But the same setup in Internet Explorer seemed to not be getting any calls from the Flash.

Internet Explorer runs Flash as an ActiveX component instead of a plug-in like Firefox does.  I had already read somewhere that you need to use VBScript to get a call from Flash in IE, and then make a subsequent call to javascript.

http://moock.org/webdesign/flash/fscommand/index.html

I tried this right away, but still no go. After days of sandbox testing and googling one thing after another, I somehow came up with just the right mix of words to feed Google and came back with a possible fix (NOTE: I'm using the latest version of SWFObject 2 for javascript-based standards compliant Flash embedding, which is similar to UFO and actually replaces it (along with SWFObject 1.5, which was not so similar to UFO)). Follow the rabbit trail:

http://www.accessifyforum.com/viewtopic.php?t=9261

http://www.bobbyvandersluis.com/ufo/index.html

From bobbyvandersluis.com:

Q Why doesn't fscommand work in Internet Explorer?
A In order to make fscommand work in Internet Explorer Adobe recommends to add a block of VBScript to capture and forward the FSCommand calls to JavaScript. However VBScript doesn't work anymore when a Flash movie is inserted using innerHTML, like UFO does. Fortunately you can also use JavaScript instead of VBScript to catch the FSCommand calls. A small downside is that it uses proprietary attributes, however wrapped in conditional comments you will keep your code valid. A sample page can be found here.

I'm not sure his explanation exactly fits my problem, since I tried not using SWFObject, just using a straight <object> embed, and it still did not work in IE.  I'm guessing ASP.NET or something else broke the VBScript FSCommand subroutine calls working for me, so using the javascript with the proprietary "event" and "for" attributes was still a great solution.

Netflix Site Design Upgrades

So, Netflix has done a complete revamping of their site design - bringing some of the changes they made to the "New Releases" page to the rest of the site.

Having dabbled primarily in web application development now for awhile, the first thing that I noticed was that they changed the width of the overall layout.  Previously, the site had a fixed layout fit perfectly well into any 800px wide display - catering to people still stuck using 800x600 as their screen resolution.  This is something you used to see pushed a lot on the net.  Making sure their your pages still look ok on older computers - or for yesteryears abundance of novices who hadn't even seen their Display control panel since they had installed Windows 98 years prior.

The new Netflix site design is still a fixed layout, but now looks best on a 1024px wide or wider display.  Anything less, and you will get horizontal scroll bars.  "So What? They have an extra 224 pixels of width to take up now -- big deal!"  Well, I can tell you from experience, having worked on two large projects recently where one was designed to support 800x600 displays and the other only 1024x768 and above, that it is much easier to obtain a 'Web 2.0-ish' look when you have those extra pixels to play with.  I am glad to see that high visibility sites like this are committing to a new, higher standard.

The Table Cell Box Model

Another Firefox vs. Internet Explorer behavior quirk, this time relating to how heights, padding, etc are interpreted on table cell (<td>) elements - The table cell box model, if you will.

Bruno Fassino (great CSS IE bugs/fixes page) has a test page up that demonstrates the behavior, and offers an explanation as to why I can't get cell height to be exactly the same in Firefox and IE7 on a table cell that has some padding all around it:

Results: height on cell (td) is interpreted as:

1. content-box height in: IE/Win/standards, Saf3/standards, iCab4/standards
2. border-box height in: IE/Win/quirks, Gecko 1.8+, Op9, Saf3/quirks, iCab4/quirks

I looked around, and there seems to be some confusion as to exactly what the proper behavior is.

The Solution

For now, it seems like the only fix is to have alternate CSS for IE7 (I recommend conditional comments, and a separate iehax.css file)

CSS Performance Enhancing Drugs

Several times I have gone looking for an article like this on Google, and several times I have failed.  Tonight I hit just the right combination of keywords, and up popped this article straight from the horses mouth:

http://developer.mozilla.org/en/docs/Writing_Efficient_CSS

Whenever I'm writing rules for CSS, I always find myself asking questions like "Is it faster for the client rendering engine if I use #id as a selector, or if I use div#id"?  This article answers that and many other questions - and exposes several things I've been doing all wrong, thinking I was actually improving the render speed. 

BTW, from the article:

BAD - tag#id
GOOD - #id"

Windows Media Player Embed

Here is an object / embed I used to successfully embed a .wmv movie in a website.  Users will see the Media Player controls at the bottom, and the movie will "buffer" automatically without the need for some special server software.

<!-- embed:  UCNvideo04.wmv -->
<object id="MediaPlayer1" 
    CLASSID="CLSID:22d6f312-b0f6-11d0-94ab-0080c74c7e95" 
    codebase="http://activex.microsoft.com/activex/controls/mplayer/en/nsm p2inf.cab#Version=5,1,52,701"
    standby="Loading Microsoft Windows® Media Player components..." 
    type="application/x-oleobject" 
    width="640" height="480">
    
    <param name="fileName" value="UCNvideo04.wmv" />
    <param name="animationatStart" value="true" />
    <param name="transparentatStart" value="true" />
    <param name="autoStart" value="true" />
    <param name="showControls" value="true" />
    <param name="Volume" value="100" >
    
    <embed type="application/x-mplayer2" 
        pluginspage="http://www.microsoft.com/Windows/MediaPlayer/" 
        src="UCNvideo04.wmv" 
        name="MediaPlayer1" 
        width="640" height="480" 
        autostart="1" showcontrols="1" volume="100" />
</object>

We stopped using this though, and switched to FLV files loaded into FlowPlayer (flowplayer.org).