I had to do a MySQL update on records that were retrieved from a two-table join today. I shouldn’t have been surprised to find that I needed a different syntax than I know from MS SQL, where I would write,

I always thought this was a goofy syntax, but I’ve gotten used to it. If it isn’t clear, I’m finding all of the records in fTime that belong to a particular record in parent table fProject with the conditions that the WorkDate is before a particular date and the ExportStatus is 0. Those are just columns in fTime. They aren’t particularly important to this discussion.

To do the same thing in MySql, I have to write,

Now that I look at it, the MySql syntax is a lot more intuitive to me. Too bad I’ll never remember it, since I use MySql so much less than I use MS SQL.

Of course, with MySql, I have to be really specific with my date format too. But that’s another topic for another day.

Since I added a Mac to my repertoire of workstations, I’m getting better and better about finding quick and easy ways to get around. Sooner or later, I’ll be just as efficient on my Mac as I am on my PCs.

One thing that I do to save a lot of time on the PC is to copy paths from the Explorer address bar, and paste them in on other places. I didn’t have a quick way to do anything similar on my Mac.

Today, I created a Service Workflow to help me out. (I’m not 100% sure about what makes something a workflow service yet, but this worked.)

From Automater, select New, and then select Service. Then, just make your settings like this:

You’ll probably need to click on the image to see it full-sized.

Then, chose Save, and name it something like “Copy Path to Clipboard.”

After that, you can right-click any file or folder, move the arrow down to the Services menu, and “Copy Path to Clipboard” is right there for you.

To do the reverse — open a box where you can type a known path — just use Command + Shift + G. I’m not nuts about having to memorize keyboard shortcuts, but I’m starting to get them, little by little.

Command + Shift + G even works in an “Open File” dialog in any app that you happen to be using. You don’t have to actually be in Finder itself.

From time to time, we’ve all had a database query to run that was stymied by foreign key constraints.

In my case, it was a request to restore a table’s values from one copy of the system to another. We knew that after the information was refreshed, there wouldn’t be any key violations. But SQL server was coded not to trust us, so I couldn’t even run the truncate statement to get rid of the original data.

Of course you want the foreign keys to exist. It’s the best way to prevent buggy code from creating situations where rows that should point to a parent point to nothing at all. If you have an Orders table, and that table has a column with an ID number that points to a Customers table, you can’t let your software delete the Customers record, because then you have Orders for nobody.

But once in a while, the king of the database needs to override the rules in order to get something done.

Unfortunately, there’s no “super delete” function that lets you bypass the constraint.

I decided to write a stored procedure that would save the relevant information about my constraints, drop them, and restore them when I was ready to have them back. I leaned heavily on the work posted by Sushant Saha on DatabaseJournal.com.

I ended up with a stored procedure with usage like this:

The first parameter accepts any number of table names. I’ll warn you now that the way I wrote this means that it won’t work for table names that has spaces in them. (No one does that though, do they?)

The second parameter is ‘D’ to drop constraints or ‘R’ to restore them. That way, I can run the procedure to drop them, do my other work, then flip the D to an R, and run it again to restore.

I can make it this easy because the procedure also leaves behind a table named dbo.FKeys, which displays an easily readable representation of the foreign key contstratints in the database. Columns show the name of the constraint, names of primary and foreign tables, and names of primary and foreign columns.

The final parameter can be flipped to a 1 to display the ALTER TABLE statements that will be applied rather than actually applying them. You would be wise to run it like this before letting it fly from scratch.

Running this with a 1 in the last parameter will also update FKeys, so if you’ve applied an update that may have added foreign key constraints, you can refresh the user-friendly readout of them without chaning anything in your database structure by running it with a 1 at the end.

I took this exercise out to four columns, becase I was sure that none of my primary keys need more than that. It’s easily adapted to more, though, if you have such needs.

The code for the procedure is below.

One would be well advised to be cautious playing with this stuff from scripts copied from other people’s websites, but I hope its either educational or helpful to someone, somewhere down the road.

A SQL Server tidbit that I never knew before today. This was a test on SQL 2000. I’d be interest to know if you get the same result on something more current.

[Update: still just like this on SQL 2008]

What do you suppose this returns?

A – NULL
B – asfdfs
C – an error message
D – OrderName

Correct Answer: B.

I would have expected A. It turns out that I had a variable assignment in a loop, and from time to time, it wasn’t getting assigned a new value. The problem was on rows where the select statement returns no rows. In that case, my varible still had the value from its previous iteration.

Anyone who has had to troubleshoot or support systems built on a SQL Server database knows and loves the Profiler Tool, which allows you to see all of the queries submitted to a database.

Web page database interaction tends to be simpler, and until recently, I never had to troubleshoot a situation that was so complex that I needed to trace its database interactions. This week, though, I was looking at a function to save some records. I had a print_r at the top to prove to myself that the records were there, and I saw other records in the list in the database after the run, but my last ones were missing.

It had to be that the records were being deleted behind the insert. I needed a trace to figure out what was coming behind me to delete perfectly good records.

It turns out to be pretty easy. First, I edited the configuration file for mysql, which I found in /etc/mysql/my.cnf. (All of my work was on an Ubuntu server running both Apache and MySql. Needless to say, there are other environments where paths and file names are different.)

I found this section of the config file:

I changed the general_log value from 0 to 1, and restarted mysql.

I then ran my page, and opened the log file named in the configuration: /var/log/mysql/mysql.log.

Suppose you are charged with system QA for a complicated system that expects dozens or hundreds of simultaneous users. The system has a user front end through either a browswer of through deployed software, and it sends transactions back and forth with a Microsoft SQL Server database.

What do you do, if you’ve confirmed the functionality, but you have doubts about the system’s ability to handle all of those simultaneious connections and database transactions?

One way to do a quick and dirty test is to trace the software’s database statements, and then run them many times in a batch. You’ll probably have to tweak the script, but a trace is a good place to start. Then, you can kick off your script from dozens of connections at about the same time.

(This might not be the best way to verify a system, but it’s 99% better than most companies do, which is to not test this at all.)

Thinking through such a scenario, I found a useful SQL trick. In order to simulate a real user interacting with a system, you can use the WAITFOR statement to delay statements being sent. With the “time” option, you can even make sure your connections start at the same second.

Some examples are in order.
Continue reading →

For the past couple of months, most of my work has been in that last, most overlooked arc of the development life cycle: maintenance.

Specifically, I’ve spent a lot of time diagnosing problems, tracking down their origins in the code, and making fixes. The client has a strong focus on documentation and process, so the step after a fix is always to write up exactly what changed in the code, and why that change should fix the problem.

If you had spelled out that part of the job to me right at first, it would have seemed irriatating. Certainly not in the top 10 irritants I’ve smiled my way through (#1 = Buzzwords), but one of those things that I’d rather not do, given my druthers.

I’ve turned a cornor, and I’m now actaully considering starting my documentation before changing code.

Twice in the past month, the process of putting a fix into a plain-language description has caused me to realize problems that I didn’t see when just looking at the code.

When it happened today, it was one of those where the fix looked right, but I had that nagging feeling that I was overlooking something. As soon as I started writing, my oversight was as clear as it could be.

This approach should be obvious. I would never start implementing a new feature without putting it into documentation first. I’m first to gleefully predict failure for those who just listen to a problem and start writing code to fix it.

Since my previous adventure with Applescript, I made a few tweaks, and then I got hung up on a bigger problem. I did a lot of googling, and while I found cases of my problem, I never found an answer that worked, so I thought I would post more information for someone to stumble on some time down the road.

First, a little background information: When I pull images off of digital cameras, they always display with the correct orientation. Like almost all digital cameras now, mine has a sensor that can tell which way the camera was being held when the picture was taken. So there’s a little bit of information in the picture’s metadata (called the picture’s EXIF information) that tells how it’s oriented.
Continue reading →

Before a week ago, the most time I’d spent on a mac was just playing around in the Apple store, while waiting for a Genius Bar kid to confirm that my salt-water soaked phone was indeed ready for the electronics graveyard.

But then, my laptop’s hard drive crashed, and I spent a night or two playing around on my wife’s Macbook Air. A coincidental promotion hit my email box, and fast forward to today, where I’m posting from my new Macbook.

Tonight, I wrote my first applescript. Applescript is a confusing but useful language that lets you automate a lot of things. There are operating system tasks, and functions that you can pass along to programs to do your bidding.

What I wanted to do was to import photos from my camera’s memory card. There’s software on the mac that will do it. And there’s more software from the camera company that will do it slightly differently. But I wanted one particular configuration available from the camera manufacturer’s software, but I didn’t want that software’s bloat.

What I specifically wanted to do was to is this:

Images are stored on my camera card with file names like IMG_xxxx.JPG, where XXXX is a sequential number. I want those files to live on with names like IMG_yyyymmdd_xxxsx.JPG where that middle part is the date that the picture was taken.

The applescript below does just that. I drag the files from the memory card to an app (icon) that has this script associated with it, and my desires are fulfilled.

As an added bonus, it moves RAW files to a subfolder named Raw. For movies taken by the camera, it changes IMG at the beginning to MOV, then moves it to a subfolder named Movies.

Perhaps this will help you do something similar in the future.

If you’re interested in how to configure the scripts to run automatically, let me know, and I can post more about that configuration.

One of the features of the more current versions of CSS is the :nth-child pseudo-class. Suppose you have an element with a designated class that repeats on your web page. And suppose you want to specify specific instances of them uniquely. nth-child is heaven-sent for this sort of application.

For the purposes of this discussion, I’m going to pretend that I’m formatting a page with a list of accounts, generated in an unordered list, with each list item having a class of “account” assigned.

Our example requirements are these:

• The first item in the list should be styled on bold.
• The third item in the list should be presented with a green font. (I can’t imagine why a company would want this particular example, but it will demonstrate the functionality.)
• Every other item should have its background shaded a light gray, to help distinguish list items.
• Every fourth item should be bordered by a dark blue box.

We will start with this list:

View Source

In order to format the first item in the list in bold font, we add this instruction to our style:

“.account” shows the class we’re modifying. “:nth-child” is the pseudo-0class that specifies the style instruction for only a subset of elements with that class. And simply, “(1)” means to style the first element.

This gives us the list updated as,

View Source

Now let’s use that same kind of thing to turn the third list item green. Add this to the style instructions.

And that give us the document as shown here.

View Source

We can also use the words “even” and “odd” for formatting on every other element. To add our gray backgrounds, we add,

Which gives us the document formatted as,

View Source

Finally, we can use formulas with the variable “n” to specify even more complicated patterns. To but a blue box around every fourth entry, we write,

View Source

Here, we use a formula, and just apply some simple algebra. Start counting with n = 1, and every time the page hits a value that applies here, it will apply that value. The only tricky thing is that the page starts with zero for the first element, one for the second, etc.

This is where you want to get creative. For example, if you want to format the first three items on a list, you could do something like,

In this case, after n gets to 3, the value goes to zero (and then to negative numbers), so the formatting no longer applies.

Browser Compatibility, Especially IE

The main reason to not use this over the past several years has been browser compatibility. Most importantly Internet Explorer only has full support for the pseudo-class in IE9. And even then, there’s a confusing catch for developers.

IE9 defaults to compatibility mode for looking at local files. Compatibility mode equals ‘render stuff the old way,’ and I can’t imagine why that would be the default. No doubt, countless developers have beat their heads into their desks wondering why they couldn’t get their page formatting to work in IE, even though IE9 is supposed to handle this just fine.

To resolve,click on Tools -> ‘Compatibility View settings’ on the IE menu and then unclick the check box that says ‘Display internet sites in Compatibility View’. Then, you can quit blaming your code for a silly browser setting that says to generate some content one way and other content in a completely different way.

Fortunately, the default settings make it work like you want it to for your users — who are getting the content from a web site instead of from a local instance.

Still, it might be worth waiting on this, until the amount of traffic from Internet Explorer versions earlier than 9 goes down. Keep an eye on your user agent stats, and judge for yourself when it’s safe to implement.