Monday, August 15, 2016

What Broke, for Heavens' Sake?!

[Warning: SQL Tech !]
Well, so you're working (like me) with Microsoft's SQL Server and you've got all this code, and every now and then it breaks. Usually because some crazy user did something dumb like put a tick ( ' ) in the middle of a text field, like they've been instructed not to do, or managed to add something like a hidden character, or whatever.
Anyhow, the code breaks, and the carefully crafted error trapping works perfectly, so you know the line of the procedure that it died on, and a fairly generic and opaque description of the problem itself.
However, there are two things that you don't know:
1. the actual piece of data that caused the failure
2. the history of the processing of that data up to the point of failure
Knowing the actual data that caused the failure and be a great help; knowing what happened to it on its way to the point of failure can be even a greater eye-opener, especially if you're working with code that you inherited from someone else now long gone.

Logging
The obvious way to handle this problem is to add some logging code to your program. Personally, I maintain programs where the data is usually in the form of messages, even when they arrive batched up in a text file, which move through the systems, undergoing one of a number of sequences of operations, depending on where they've come from, what their content is, and where they're going. Each of these messages is assigned a unique ID to identify it on its journey from receipt to emission.
So, I have a log table with these fields:


NameContent
IDRecord id in the table
CreatedOnDate record was added
SubjectWhat is happening in general
TextDetailed Info about the situation
TrackingIDID of the message being handled
OriginName of the procedure writing the record
SequenceSequence number of call points in procedure

I also have a stored procedure that accepts the fields it needs (the last 5) and inserts a record into the table.
All well and good so far, until you get to the small matter of transactions. Very wonderful things, transactions, and very helpful. Very nice to use to prevent things that have to be done together getting only part-done. Rolling back a transaction allows you to re-try the whole thing again when you know what's going wrong and have fixed the problem. However, the rollback undoes everything the code has written ... including all the logging, so you never get to find out what it was that went wrong - just that it went wrong !

The Light!
This, of course, is not a whole heap of help, but one day I was reading a book on SQL Server and suddenly part of the description of committing (successfully completing a transaction turned on a light in my head!

COMMIT TRANSACTION makes all data modifications performed since the start of the transaction a permanent part of the database

So if you roll back the transaction, instead of committing it, everything written is rolled back. That's why the records in the log table vanished with a rollback - that much I already understood.
However, what I didn't cotton on to immediately was the implication of the corollary - that things not actually written out are not deleted.
Actually, it was a few lines into a piece of code before something hit me! Variables don't get rolled back - because they're in memory and not written to disc. And what then hit me was that a Table Variable in SQL Server is a Variable - even though it's very often stored in TempDB (on disc!).

The Implementation
So, to keep my logs that will tell me what happened to cause the rollback, I wrote some code to copy the ones for this message out into a table variable of the same structure as my log table, did the rollback, and then stuffed all those saved records back into the log table.

Et Voilà! On trouve tous les donneés intacte!!

The code is like this:

declare @taLogTable ( [Field List] )
insert into @taLogTable
select * from dbo.LogTable where [specify which records I want]
-- ---------------------
rollback transaction
-- ---------------------
set identity_insert dbo.LogTable on
insert into dbo.LogTable ( [Field List] )
select * from @taLogTable where ID not in (select ID from dbo.LogTable)
set identity_insert dbo.LogTable off

But Wait - There's More
That's fine, but there's a small hiccough coming here.
Part one is that the system runs in two separate databases, and we use synonyms to refer to objects in one database from another. That's perfectly fine: if I need to save logging data that's been logged on another machine but is still part of this transaction, I simply use the appropriate synonyms for the tables involved:

declare @taLogTable ([Field List])
insert into @taLogTable
select * from syn_OtherDB_dbo_LogTable where [specify which records I want]
-- ---------------------
rollback transaction
-- ---------------------
set identity_insert syn_OtherDB_dbo_LogTable on
insert into syn_OtherDB_dbo_LogTable ( [Field List])
select * from @taLogTable where ID not in (select ID from syn_OtherDB_dbo_LogTable)
set identity_insert syn_OtherDB_dbo_LogTable off

So I'm using the synonyms obediently, so if I compy the code to a test database, or somewhere similar, where the name of the other database is OtherDB_Test instead of OtherDB 
then all will still be ok.
Yeah, well, almost. Close, but no cigar! Synonyms are great in SQL Server except that the set identity_insert command (which allows me to push values into a field which otherwise generates its own automatically) doesn't accept synonyms. So, everything has to be explicit, and therefore there's more more worry and work at the time of deployment in a release when you also have to check that all the set identity_insert statements are pointing to the correct databases!

Victory In The End
There is, of course, a way to fix things to avoid this problem (which, if you're bitten by it, probably happens several months after the release and when you've totally forgotten all about it, becauseit only ever happens with errors, and they really don't happen all that often.

The way to do it is to use a stored procedure instead of the code after the rollback in order to achieve the same result. You pass in to the procedure the name of the database into whose LogTable the data has to return (I called it @strDatabase here), and also the table variable (@taLogTable). Then the code is like this:

declare @strSQL nvarchar(2000)
set @strSQL = 'SET IDENTITY_INSERT ' + @strDatabase + '.dbo.LogTable ON; '
set @strSQL += 'insert into ' + @strDatabase + '.dbo.LogTable'
set @strSQL += '( [Field List] )'
set @strSQL += 'select * from @taLogTable '
set @strSQL += 'where id not in '
set @strSQL += '(select id from ' + @strDatabase + '.dbo.LogTable); '
set @strSQL += 'SET IDENTITY_INSERT ' + @strDatabase + '.dbo.LogTable OFF; '

There is just one little extra piece of code you'll have to incorporate. 
SQL Server needs a type for each object coming in to a stored procedure, so you have to declare a user type for your table variable. This shouldn't be too much of a hardship, especially if you're using the same structure for all your log tables (a very good idea!). The parameters for that procedure turn out to be like this:
@strDatabase varchar(32),
@taLogTable taLogTable readonly

so not such a problem after all, and helps the programmers coming after you!

Have fun - keep cool!   Food next time!
TTFN

Saturday, April 16, 2016

Do I have to have Windows 8 / 8.1 / 10 / whatever?



So you're looking for a new computer 'cos your old vintage 2003 laptop just ins't keeping up any more. Not to mention the fact that the case is cracked and split, the screen has a crack, some of the keys don't work too well since you spilled Mountain Dew on it, and your friends mock you for still using Windows XP.

So you want something cool. Something that you can make look the way you want it to look. Something shiny - something you can attach to the TV, even! But what do you really need? A friend just bought a new machine and found that she can't upload pictures to Facebook for some reason. She was also convinced to buy an antivirus program, only to find that the OS came with one! (PC World did a good article on the subject way back in July 2012 that's worth reading).

Check off what you do on this list:

Listen to MusicWatch DVDsCapture video for DVD
Word ProcessingUse a SpreadsheetCreate pictures, charts, etc
Create PresentationsDatabase WorkEnail
Anything with a browserWindows-specific programsMac-Specific Programs


Windows 8
If you're looking at Windows 8 (right) with horror, it seems like you had a lot of company. Don't buy this with the expectation that you can sit back and do nothing. Windows 8 had a large enough upgrade (to 8.1) that it is now officially no longer supported by Microsoft, just like Windows XP!







Windows 8.1
The replacement for Windows 8. Basically, Windows 8 with some GUI tweaks to appease the vociferous detractors. The appeasement didn't work!








Windows 10
The great white hope for Microsoft! "Everybody is going to upgrade their operating systems to Windows 10 because many features were added by the developers. The big thing is that the Start Menu button was reverted back that was previously removed/changed in Windows 8 and Windows 8.1" (here). Well, somewhat tongue-in-cheek there, and no comment at that time on the compulsory updates that seem to be causing a lot of people a lot of problems, but Win 10 does appear to be a genuine improvement - over 8 and 8.1, at least.

Here are some possibilities - maybe not all obvious:
Windows 7  --  this is probably the easiest thing to install on your PC. Just stick the disk in, blow away Windows 8, and install it. Easy, right? Well, there is a small step that you absolutely have to do first, but essentially yes.
Apple Mac OS/X - a very nice system indeed, if you can give up your Windows-specific programs. I doubt it'll run on your PC after you blow Windows 8 away!
Linux - there are lots of versions to choose from; you can "try before you buy" by using what's called a "Live Image" disc, where you boot off a CD and get the system - a little slow - that you would be using and you can see what you'll get before getting it.

For both Linux and Windows 7 there's a small procedure you'll need to go through before you can install them on your new machine. All Windows 8 machines are supposed to run with a replacement to the old and much-hacked BIOS, called UEFI. The requirement for this system, imposed by Microsoft, is supposedly to protect you and I (the un-knowing and helpless consumers) from malicious software ("malware") that would write changes into a special part of your hard disk - the part that contains the very first code that the computer runs every time you start it up. Once there it's very hard to get rid of.

This is a laudable move ..... except ....... very few instances of code doing that are around any more, as it's difficult to do and there are lots of easier ways to get your info. You may remember an uproar a few years ago about Sony trying to stop you copying their CDs (here)? Well if not, the skinny was that they decided to stop people copying their CDs in a rather proactive way, and took over their computers to do it! Not once (2005) but again in 2007! They received a rather large fine for being naughty!

So the idea with the new version of the BIOS - with UEFI - is that simple programs can't turn it off - it has to be a real live human. So you should be able to press Del or F2 or whatever the system wants you to press while it starts up, wander your way through the menus, and eventually find a switch to turn it off. Just how you'll identify the switch isn't, of course, defined!

As I said, this kind of attack, while it does exist, really isn't that common. What you are far more likely to encounter is something called "Phishing", or "Social Engineering", where someone sends you an email with an attachment that you open but which is, in fact, a program designed to do something bad. Similar examples include the perennial repeats of emails from the infamous "George Layba" promising you vast amounts of money in exchange for your bank account(which will be swiftly emptied if you do what they ask!). A larger and more sophisticated attack has more recently been coming to us all courtesy of the Chinese company that took over IBM's PC operations, Lenovo. Take a look here and here for info about Lenovo selling you computers with spyware already installed!! The second guy seems fairly blase about the whole thing, but could be right, I suppose. One should be more careful after getting caught three times!

Anyhow, what does this strange thing called Linux look like, and can I use it?
Well, the answer to the first question is somewhat frightening, because it is what you've asked for with no hope of getting - "whatever you like" ! Well, almost. There are around a hundred different distributions! Here's a list with links to many. Here's a site called DistroWatch, that tries to keep an accounting of the number of installs of each distribution and ranks them accordingly. Right now there are about 900!
If you take time to investigate you'll find that all these versions tend to form family trees. For example, Debian is the name of a basic distribution, and then there are a lot of distributions that take Debian and change certain things - such as trying to make it easier to use (Ubuntu), or specialise it for, for example, musical applications (KXStudio), and Distrowatch has a nice feature that allows you to select what you want from your machine and see what there is to choose from.
When I started out I chose Ubuntu, which was fine. However, the makers introduced a new interface called Unity, which I didn't really like (curiously enough it's rather like Windows 10 !) so I moved. To a descendant of Ubuntu called Mint.


As you can see, it actually looks rather like a version of Windows - I have it set up to have the menu drop from the top left, but the bar could be at the bottom. There are two columns in the menu drop-down - categories and programs. This means that you move the mouse over the category you want and, if you were using my system, you'd see this:
So the products that I have installed include (with their Windows equivalents)

  • GIMP (Photoshop)
  • ImageViewer (Acrobat)
  • Inkscape (FrameMaker)
  • MyPaint (Freehand drawings)
  • SimpleScan (scans from my Brother MFP)
  • Xara Xtreme (vector graphics, like Corel Draw!)


So yes, there are many programs out there that will do what you do on your Windows or Mac machine, but do it on Linux. Are they as polished as those commercial programs? Oftentimes not quite, but don't forget that they're usually free! That lets you try out various programs and make a good choice for your needs - or else use a combination of several - without going bankrupt in the process!

So no - you don't have to upgrade to the next version of Windows. I work with MS SQL Server using Windows every day, but use Linux for preference at home. In fact, Microsoft is porting SQL Server to Linux in 2016!
TTFN

Windows 10? Or Not?

If you're buying a new machine (that isn't an Apple) then it'll almost certainly be coming to you with Windows 10 already installed, although some retailers are still selling hardware with Windows 8.1 or even Windows 7 pre-installed.

On the other hand, you may own an older PC already and be thinking of upgrading from Windows 7 or Vista, or XP .....

Well, firstly take a look at what you'll need in the way of hardware (see ZDNet's take - I would have added Microsoft's recommendations but their links are broken!). For those about to take the upgrade plunge please read this article first, as the rules changed at the end of 2015! You still only have until around July 2016, though, for the freebie!

  1. Processor: 1 gigahertz (GHz) or faster.
  2. RAM: 1 gigabyte (GB) (32-bit) or 2 GB (64-bit)
  3. Free hard disk space: 16 GB.
  4. Graphics card: Microsoft DirectX 9 graphics device with WDDM driver.
  5. A Microsoft account and Internet access.



Now at this point I should 'fess up: I'm running a 2.8 GHz machine with 32 GB or RAM and 2 TB of disc. However, I'm not running Windows on that - I'm only running Windows in a virtual machine from Oracle, called VirtualBox. Here's what Windows 10 thinks about it (right and below).
As you can see, it's not a particularly new cpu (2011), but Windows 10 is getting a pair of threads, 8 GB of RAM, and, in this case, some 45 GB of "disc". Roughly the equivalent of many Windows 7 machines that will be getting upgraded to Windows 10.

Well, the purpose of this post is to walk you through my upgrade experience, so you can have some idea of how yours might go.

1. How I started. 
In this case I started with a totally clean machine and installed a copy of 64-bit Windows 7 Pro onto it. I started with 45 GB of virgin disc and filled a fair amount of it with Windows 7. I installed just one program - the Magic Jelly Bean Finder that you can use to discover the product key of your OS - always a good thing to have if it's worn off the bottom of your laptop!
2. The first problem.
After installing Windows 7 I immediately started the upgrade process and equally quickly got told "You can't upgrade from a copy of Windows that isn't activated"!  Oh well!  So I sat down and wasted a little more time activating my copy of Windows 7 that I was never going to use.
3. Upgrades and Updates
You'd think that a whole new operating system would come with all the latest patches, etc. Well, maybe, but in this case it quietly refused to install until I'd loaded all the Important updates for Windows 7. Just what it needs all those Windows 7 patches for is completely beyond me, but still, it's obviously nice to have them.
4. Starting Over
So, after putting the Windows 7 machine on to take all the necessary patches and updates, I went to bed. There was over two foot of snow outside, and it was still chuckin' it down, so I really wan't up to waiting!
5. Next Morning ... Installing!
Next morning, after digging out a friend, and her neighbour, and my neighour, and my car, and my wife's car, and the car of a random stranger who was making heavy weather of digging hers out, I took the afternoon off and drank hot chocolate (thank you Wegman's for stocking Cadbury's!). I alsos topped by the computer, woke up the Windows 7 VM that I'd made the day before, found it happily updated, and started the upgrade to Windows 10 again. 
There are two ways to get Windows 10 - as an ISO disc image that you burn to a DVD and then use to install from, or you can download the web installer from the same page that you can find the ISO image download in, and choose to upgrade your PC. That's what I did.
For the next five minutes or so I got practice in clicking the Next button, screen after screen, and adding the odd bit of information, like my name and the time zone. Then there was the install, so I waited about an hour for that - if you're doing it on a standard machine it may be quicker - don't forget I was using a virtual machine! It also reboots a number of times, but appears to "know what it's doing, because at the end you can log into the machine you just installed - using your Microsoft account and password!
It is possible to install the upgrade and keep the original machine user name and password - which is what I did.  However, there are some nice features that you don't get unless you're logged in to Windows 10 with a Microsoft  identity.

So that was it - very simple. So you can go and get a legal copy of Windows 7 and put it on your (older) machine and, if it performs satisfactorily, I'd bet that you can just sail into Windows 10.

Fun Surprises
Sometimes an operating system can surprise you! For example, a few days after installing, it was time for Windows 10 to load some patches. I started the machine and it obviously looked out to somewhere in Microsoftland and found things it needed to do. I was left with a blank black screen during the boot process for a couple of minutes, and then this!
before the normal login appeared and I could log in again.
Once it you are normally greeted with a blank screen with whatever icons you may have placed there, and a menu bar. I've mooved mine to the right, as you'll see. Left-clicking on the Windows 10 icon that takes the place of the old Windows Start button gets you this layout, rather similar to Windows 8,
and left-clicking it again will clear off all those flat panes again.  However, right-clicking it gives you the left-hand screengrab - the complete menu with most of the links into the control panel. 

It also offers you "Search", a way of searching for programs, files, etc. I tried it out after installing SQL Server 2016 and the screengrab on the right was what I got - not bad! In fact, a lot more useful that what you get with a Windows 7 search from the File Explorer.
You can still, of course, return to the command prompt ... there isn't an icon or menu choice for that, but you can create yourself a shortcut to cmd.exe which works very well!
Here's the output from running the command prompt, and searching for the substring "SQL" anywhere - first the command and then the result:
cd \       
      dir *sql*.* /P /S

Windows 10 echoes the command you just ran to the header bar of the window. As you can see, it's finding every file with "sql" in the name. So, many things haven't changed - if you learned to use the command prompt while the engineers at Xerox PARQ were creating the Xerox Alto machine with its graphicl interface that Microsoft and Apple stole and turned into their interfaces, then a little bit of that life survives!
TTFN

Food From Long Ago

Well, not the food itself, but ...
I recently discovered,  during one of out many moves, a book from probably my great grandmother. It sings the praises of a piece of wonderful new kitchen equipment - the gas stove!!! This dates it to around the 1890s. As my mother was born in 1921 and my grandmother in the 1890s, my great-granny takes the blame! It was written by Miss Amy Atkinson and Miss Grace Holroyd.



I also discovered, in the same trove, a copy of The Olio Cookery Book, 14th Edition, from 1922. This is a rather interesting find, although much younger. It offers a lot of recipes, it is true, but also many tips interspersed amongst the recipes. For an example, on page 144 one may find recipes for Sherry Whey, Raw Beef Tea, Quickly-Made Lemon Jelly, Special Suet Pudding, and Invalid Pudding. After that come two Cures For Neuralgia and a Boer Embrocation For Sprains. Page 145 has a variety of remedies - for afflictions as varied as Boils, Rheumatism, Corns, and Chilblains!
This book was written by a Miss L. Sykes.
Both books contain fascinating little insights into the past, and quick simple little recipes very suitable for today.

With a little bit of research you can find both of these books in a database online from the University of California at Davis. Believe it or not, you can obtain copies of both books from Amazon UK !

Anyway, on to the food!!

Sausage and Tomato Pie
Ingredients

1 lb sausages, chopped as shown 2-3 medium onions, sliced
3 plum tomatoes, sliced Salt and pepper
Half a cup of stock
2 cups Idahoan dried Mashed Potatoes 3 cups of water
1 stick of butter 1 cup of milk


The Chopped Onions. You'll see that at least one had sprouted. This isn't a problem, excepting that most of the bulb was useless - the green part was still good!

The sausage I used was Amylu's fully cooked Chicken Andouille. I got a pack of 8 from CostCo and used four here. For info on the product see here.


Procedure
You will need a container to cook this in: I used a glassware loaf dish, about 26x15x8 cm.
  1. Start the oven heating to 350F.
  2. Slice up the onions, tomatoes, and sausage. 
  3. Fry the onions until well-browned - the picture below is about 2 or 3 minutes from the end.     
  4. If you're using raw sausages then prick the skins well and cook them gently in boiling water for 5-10 minutes until pretty well cooked through.
  5. Fry the sausage gently until the pieces are reasonably browned. You don't need to totally cook these sausages - they already are cooked! (See the next picture!)
  6. Put a pan on to heat with the water, milk, and butter. Let it come to a boil.
  7. Add the dried mash to the water mix and whisk to mix it. The dry flakes will rapidly turn back into mashed potatoes!
  8. Put the sausage pieces in the bottom of the dish and cover with the onions. 
  9. Add the stock and the tomato slices 
  10. Slather on the mash. Smooth it down to neatness with a fork.
  11. Place in the oven on a baking tray and cook for about 30 minutes, until the tips of the potato turn brown.
  12. Serve!
Onions frying!


As you can see, I've been fighting Blogger a bit to get this to post. I hope you enjoy it.
TTFN

Thursday, January 28, 2016

More recipes to keep warm with

Cream of Thing Soup
Sounds very much like something from an episode of The Munsters from the late 1950s, but it tastes different from your run-of-the-mill "Cream of ..." soup, which is a major plus when you've been feeding people soup and stews for what probably seems like forever!

Thing
Can be Chicken, Mushroom, or Celery. Substitute whichever you like!

Ingredients
Half a medium onion, chopped2 heaped teaspoons of chopped garlic
Heaped tsp Summer SavouryHeaped tsp Nutmeg
1 Tsp Pepper2 litres of Milk
Half stick of butter2 cans of condensed Thing soup (e.g. Campbells)
Two or three slices of brown bread
You can use Herbes de Provence instead of Savoury.
You can use soy milk instead of cow's milk.

Method
  1. Melt the butter in a pot that will comfortably hold all the milk.
  2. Add the onions and garlic and gently fry until the onions are beginning to become soft and translucent.
  3. Add the pepper and herbs, mix, and let cook in for maybe five minutes.
  4. Add the soup and the milk and raise the heat to medium.
  5. Use a whisk to stir up the mixture so that the soup completely dissolves in the milk.
  6. Break up the bread into smallish pieces and add it to the soup.
  7. Keep whisking gently to aerate the soup as it warms. 
  8. When the soup is hot enough you'll see tiny bubbles around the edge of the pot - they're the hottest places and are beginning to boil.

This should provide a good portion of soup for four. Enjoy!!

TTFN