Inserting a batch of random numbers

Let’s say you want to insert (or, for our example, update) a whole set of random numbers into a table.

You may try this:

UPDATE table SET rnum = RAND()

…only you find, to your amazement, that SQL Server has put the same number into each row.

Hmm.

In SQL Server, when rand() is called multiple times in the same query (e.g. for multiple rows in an update statement), it usually returns the same number.

Two problems:

* Firstly, the rand() function returns a number between 0 and 1.
* Secondly, when rand() is called multiple times in the same query (e.g. for multiple rows in an update statement), it usually returns the same number (which I suspect your algorithm above is trying to solve, by splitting it into multiple calls)

My favourite way around this problem is to use a function that’s guaranteed to return a unique value each time, like NEWID(), convert it to binary, and use it as the seed.

UPDATE table SET rnum = RAND(convert(binary(16),NEWID()))

This works because NEWID() is guaranteed to return a new GUID (a globally unique 16-byte number) each time it’s invoked. We must convert this to binary before using it, as RAND() won’t accept GUIDs as its seed.

So, although RAND() ordinarily gives the same random value for each row in an update, we get over the problem with RAND by giving it a different seed for each row using a function that gives a different result for each row.

MySQL: Mass email change

It’s not unheard of for a company to change e-mail domain in mid-thrust; maybe it’s been bought out, or rebranded, or the parent company has spun it off to its own brand.

Only you’ve got hundreds of employees, each one with their own email address, and your MySQL database is in dire need of updating to reflect this.

To get around this, you’ll need to replace the relevant part of each email string within an update statement, grabbing the hostname substring (after the ‘@’) with a REPLACE, and replacing it.

UPDATE table SET email=REPLACE(email,'OLDHOST.com', 'newhost.com');

Note: REPLACE() is case-sensitive, so if needs be, you can use LOWER(email) inside the REPLACE function if you need to catch all case possibilities, as below:

UPDATE table SET email=REPLACE(LOWER(email),'oldhost.com', 'newhost.com');

This will also convert all your email addresses to lowercase, so be aware of that.

Accessibility: HTML Keyboard Shortcuts with "accesskey"

Accessibility is a hot topic on the web, and there are many emerging standards to help move in an accessible direction. We have CSS media types, alternative web pages with simpler navigation and high-contrast styling, alt and title tags, and the general move away from mixing style with substance.

Along with all of this, we have the relatively old standard of using the accesskey attribute on invokeable elements such as hyperlinks and form inputs. This allows us to attach a keyboard shortcut to elements in our webpage.

Unfortunately, this isn’t a widely used, or easily implemented standard. Although the accesskey attribute is widely supported, only one commonly-used browser (Opera) at the time of writing provides an easy way for users to see what accesskeys are enabled on a given site, and there is no widely-accepted standard for choosing which accesskeys perform which function.

However, with a little jiggery-pokery we can implement a simple way to show accesskeys on demand:


This link
will bring you to this post’s permalink, and can be actuated with the accesskey “9”; in Firefox, you hold alt-shift and press 9.

The code for the above is pretty simple. First, the button:


The hyperlink itself (abbreviated):

<a href="https://jeremysmyth.com/...." title="Link to this post">This link</a>
will bring you...

Finally, the javascript:

function showKeys() {
	if (document.styleSheets) {
		var sheet = document.styleSheets[0];
		var len = sheet.cssRules.length;
                // reformatted to fit
		sheet.insertRule("[accesskey]:after {" +
                        "font-weight: 700; " +
                        "border-bottom: 1px blue dotted; " +
                        "content: '[' attr(accesskey) ']';}" , len);
	}
}

Clicking on the button calls the “showKeys()” function in the Javascript script block, which adds a style to the current stylesheet. The style automatically styles elements with the “accesskey” attribute, adding the value of that attribute after the element itself.

Put simply, it adds a styled [9] after the hyperlink, because (1) it has the “accesskey” attribute, and secondly, the “9” is the value of that attribute, as calculated by the attr() function.

Note: The above Javascript won’t currently work in Internet Explorer; for that, you’d need Microsoft’s addRule function rather than the standards-compliant insertRule I’ve used.

Feature requests: discuss, implement, or reject/deny?

I work in another publicly accessible community, where bug reports and feature requests are happily solicited from the userbase.

We have thousands of open idea requests (the codebase is nearing 20 years old), and close only a sizeable fraction of those opened regularly.

From our perspective, the idea requests are welcome, but not all of them are actionable; some are brilliant, and are implemented immediately because they work well with our vision; some are entirely incompatible and are closed/denied.

The majority fit in between; they’re ideas that would work with a bit of tweaking, or a bit of thought, but aren’t necessarily on the primary development roadmap, so don’t get our attention immediately. Nor do they warrant closing, because they are relevant, merely not timely or important.

Because our developers have their own ideas, their own neverending todo lists, we treat the open idea pile more as inspiration than as a roadmap. There’s very much a feeling of “we’ll get to it when we’ve run out of other things to do”, but this never happens in practice.

I know it’s a cop-out not to choose one or the other, but I think it’s a normal thing to have to choose between two equally bad things in a public forum like this: either responding to most feature requests with a “denied”, and so risk upsetting the folk who love the community enough to contribute with their own ideas; or leave some of them dangling because they’re not immediately and obviously wrong, but to do something worthwhile with them takes more time and effort than the idea deserves right now.

HTML: Title Tooltips and Alt text

Sadly, another one for the Internet Explorer Vs. Firefox debate.

It’s pretty well known that most browsers will display a tooltip of sorts when you hover over an image. The alt attribute of the img tag gives rise to that, in pretty much all places.

Lesser known is the title attribute, which is supposed to give the tooltip; the alt attribute might do that as a side-effect if title isn’t there, but it’s just that: a side-effect. The alt attribute is really there to give browsers that aren’t displaying images (or screenreaders that can’t see them anyway) some idea of what the image is.

This separation of concerns is somewhat of a problem when it comes to image maps: in image maps, the alt text for the area elements is there for similar reasons, to show what options are there when the image isn’t there. The title attribute is there for the tooltip, as ever.

However, if both alt and title are there, Internet Explorer shows the alt text as a tooltip, where Firefox will show the title text. Although they serve very different functions, a conscientious web developer is forced to keep them identical, or risk causing problems for the non-standards-compliant behaviour of Internet Explorer.

Of course, title will still work in other places, for example on images or even links:

<a href="mypage.html" title="My lovely page!">My Page</a>

Delving into the XHTML 1.1 DTD

So, you’re looking at the top of a web page’s source code, and you see something like this:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

What’s the relationship between that and the actual code in the web page?

Well, a DOCTYPE tag declares what document type this webpage is, by formally specifying a Document Type Descriptor (that’s what the “dtd” in the filename and in the declaration means). This is the formal specification, written in its own computer language, used to define legal dialects of languages descended from SGML. Most predominantly, this includes languages like HTML 4.01 and XHTML. Hence this walkthrough.

In our specific case, it references a specification for XHTML, which is a modular XML-expressed version of HTML. Let’s look inside.

Firstly, if we look in the declaration, we see the link ““http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd” which, if you download it, shows the DTD itself. For XHTML, this is a relatively short document; the specification largely consists of modules, referenced from this document. Let’s have a look.

Within the DTD, you’ll see this section (around line 121):

<!-- Text Module (Required)  ..................................... -->

&lt;![%xhtml-text.module;[

%xhtml-text.mod;]]&gt;

This defines a module to be included, which itself is a technically part of the DTD as it is INCLUDEd.

If you navigate to the included module, “http://www.w3.org/TR/xhtml-modularization/DTD/xhtml-text-1.mod”, you’ll see a further set of INCLUDEd items, for example:


&lt;![%xhtml-inlstruct.module;[

%xhtml-inlstruct.mod;]]&gt;

This entry includes the inline structural elements br and span, and further down the document we have more included modules containing inline phrasal elements (em, strong etc.), block structural (p and div), and block phrasal (h1, h2 etc.).

Try them:

In each, you’ll see the definitions for tags such as p, div, code, strong, em and so on.

For comparison, have a look at the HTML 4.01 DTD, which you’ll be able to follow using the DOCTYPE:


…and linked to from here: http://www.w3.org/TR/html4/strict.dtd. As you’ll see, it’s not quite modular, but still contains code defining the elements (and their contents, attributes and so on) that are legal within the dialect concerned.

Regular Expressions and Numeric Comparisons

So let’s say you’re parsing through order numbers (as strings):

OD1004A
OD1004B
OD1108A
OE1108B
OE1108C
OE1109A
OE1148A
OE1149A
OE1151A

…and so on. And you want to find orders where the numeric portion is between 1100 and 1150, regardless of what the rest is.

Tempting as it is, this isn’t actually a regular expression problem, but requires a bit of numeric processing too.

We would need to parse the digits and then perform numeric comparison, e.g.:

$input = whatever(); # gets something like "OE1105A"

…then match with the following pattern:

preg_match("/w+(d+)w/", $input, $match);

…to store the digits in memory, and then:

if($match[1] >= 1100 and $match[1] <= 1150){...

to check to see if the number is in range.

Cascading Parameter Values in SSRS

In SQL Server Reporting Services (SSRS), you might want your choice in the first parameter to limit the choices available in the second (and subsequent) parameters.

This works automagically if you order your parameters and datasets correctly

  • First, set up a primary (report) dataset, then a dataset for each parameter dropdown. Code the WHERE clause in the datasets to make the dependencies correct across parameter variables
  • Secondly, order your parameters in the Report | Parameters menu so that the first variable/parameter you want the user to fill in is at the top, and make the second dataset depend on that parameter. Follow this ordering through the parameters; the final parameter(s) should be the one(s) the actual report dataset depends on.
  • Repeat for subsequent parameters

This will work if your WHERE clause in the second and subsequent datasets have variables that SSRS knows are populated from earlier parameters.

As an example, I have three datasets from the venerable pubs database.

pubslist is used to populate the @p parameter, and looks like this:

 select pub_id, pub_name from publishers 

titleslist populates the @t parameter, and looks like this:

  select title_id, title from titles where pub_id = @p

Finally, reportdataset looks like this:

  select title, price, ytd_sales from titles where title_id = @t

The order of the parameters in the Report | Report Parameters menu is crucial; because the datasets must be executed in the order shown above, and the @t parameter is in a dataset that relies on the @p parameter being set first, we move @p to the top of the list.

Now, SSRS evaluates the dataset needed to fill the dropdown for the first parameter with labels. It relies on a dataset that doesn’t need a parameter, so can be produced immediately.

Then, having got that parameter value, it can populate the second parameter’s dropdown. That in turn results in the report being produced.

Tables, tables, tables

So, what’s a temporary table? or an in-memory table? or a pivot table?

An in-memory table is a table in some platforms that’s stored entirely in memory. These don’t really exist in MS SQL, although you could say that it is a table that’s been entirely cached, and so doesn’t result in any physical (hard disk) reads when queried. In earlier versions, the DBCC PINTABLE command allowed the “pinning” of tables in memory, but this was deprecated in SQL Server 2005.

Often, a table-valued variable, @tablename, might be stored in memory (although this is not guaranteed), and declared in a batch or function, with no persistence.

A temporary table is a table that will be automatically dropped when it’s no longer needed, usually when the creating session is terminated. In MS SQL, they begin with a # (or two hashes if they’re global temporary tables, shared between multiple sessions), and are often created with a SELECT INTO #TEMPTABLE … style query.

A pivot table is a special form of query where the values in several rows are summarised, “pivoted” on an axis, and become columns, where the summary data then becomes the rows. Frequently this happens where you’ve rows sorted on dates; these may then be “pivoted” so you end up with a column for January, one for February, one for March, etc.

PHP: require and include

Another simple feature that’s good to pick up on!

When you want to run the same functions from several pages, but don’t want to copy/paste (because that’s BAD), you do this.

Usually, you’d use require or include to include the code from one PHP script in another.

If you hadn’t done this before, or designed it in, you’ll probably need to extract the functions from your script(s), place them in a central file, and include that in all scripts that need it (including, of course, the one they were originally in!). Remember that includeing a file in another causes its embedded HTML to be included too, so you’ll typically include PHP files that have functions only, to achieve the desired result.

Then you just call the function, as if it were written in the script in the first place 🙂