Friday, 7 June 2013

HTA : HTML-defined GUIs for your PowerShell Scripts

In my last posting I showed an HTA containing VBScript, to be called from a batch file or Python script.

This time it's PowerShell's turn, and I've converted to JavaScript - because I've been looking at Node.js and how it has made excellent use of JS's functions being first class objects, closures, etc. Before I carry on studying all that fun, I thought I'd finish up this HTA malarkey first.

Time to run a new script .....
C:\>powershell  c:\testps.ps1

The PowerShell Script : testps.ps1 :

This uses Invoke-Expression twice - first to launch the HTA and wait, and secondly to run a piece of PS code that the HTA will have written to the temp folder (using a cmd line echo!) to set the variables.

A line of JavaScript commands is passed into the HTA call, with | vertical bars taking the place of " quotes. This allows you to run whatever code you like within the HTA.

$htacall = 'cmd /c start /wait mshta.exe "C:\your-path\testps.hta" "^ num1=3.14 ; str1=|String Param| ; alert(|Wow!|)"'
Invoke-Expression -command $htacall

$htacall = 'cmd /c type %TEMP%\'
$outp = Invoke-Expression -command $htacall
Invoke-Expression $outp

"Result: " + $ZZ_NUM1 + " " + $ZZ_STR1


The HTA with JavaScript : testps.hta :

The script in here is explained in the previous blog posting - well, the VBScript version anyway :)

This time (compared to the VBScript version) the variables are initialised in case the HTA is called directly. The BASIC version didn't complain about undeclared variables, but JS will. And I've removed the batch_path stuff, as it's not used here. As always, any comments are welcomed.

<head> <title>My HTA Test</title> <HTA:APPLICATION ID="oHTA" APPLICATIONNAME="HTA Test" SCROLL="no" SINGLEINSTANCE="yes" WINDOWSTATE="maximize" > </head> <SCRIPT LANGUAGE="javascript"> function NoContextMenu(){ alert("Sorry, but right-click is not allowed."); }; function Window_onLoad(){ // Use the following line to disable right-click menu and prevent View Source document.oncontextmenu = new Function ( "NoContextMenu(); return false" ); // Resize to quarter of screen area, centered window.resizeTo(screen.availWidth/2,screen.availHeight/2); window.moveTo(screen.availWidth/4,screen.availHeight/4); // Initialise vars in case the HTA is called without the expected code num1=0; str1=''; // Passed-in code - will execute to set variables var arrParams = oHTA.commandLine.split('^'); for (i = 1; i < arrParams.length; ++i) { // next 2 lines needed for PowerShell method of calling the HTA arrParams[i]=arrParams[i].replace(/"/g,''); // delete any " arrParams[i]=arrParams[i].replace(/\|/g,'"'); // | become " // alert(arrParams[i]); eval(arrParams[i]); } // SCRIPT-specific code : set controls from executed code txt_num1.value=num1; txt_str1.value=str1; }; function Set_Env_Vars(){ // Write PowerShell code (for exec later) to in %TEMP% folder var oWSS = new ActiveXObject(""); strBat='Echo $ZZ_NUM1=' + txt_num1.value + '; $ZZ_STR1="' + txt_str1.value + '" > "%TEMP%"\\';'cmd /C ' + strBat ,0,true); oWSS = null; window.close(); }; window.onload=Window_onLoad; </SCRIPT> <BODY bgcolor="#ccccFF" > <!-- onmousedown = "DisplayMessage" --> <h1>Hello!</h1> Just a little test.... <P> Number &nbsp; <input type="text" value="" name="txt_num1""> <p> String &nbsp; <input type="text" value="" name="txt_str1""> <p> <input type="button" value="Return these to the batch" name="run_button" onClick="Set_Env_Vars()"><p> </BODY>

Wednesday, 29 May 2013

HTA - HTML-defined GUIs for your scripts

Back when I was assessing whether I needed Python in my life, I was trying to find a straightforward way to build a GUI. All the tools listed at seemed to be total overkill for basic needs or not licensed in a way that I found acceptable for the commercial use I had in mind.

Some time later, I was passing the time on StackOverflow seeing if I could rack up some more points for being helpful with some problem solving, and up came a question about GUIs for batch files. HTA was a solution - quite a good one in fact. But it's funny that it seems that MS haven't been too hot about spreading the word about HTA - I have to say that even with all the geek sites that I surf daily it's not something I recall ever seeing getting much of a mention.

An HTA is simply HTML, displayed by the guts of Window's IE engine via mshta.exe - allowing you to display anything a browser can normally show, collect input, and run code that can't normally be run within HTML scripts. For example, write to local files. This means an HTA can be a script with a GUI, or it can be used to create a GUI for a separate script - command batch or Python, etc ...

Good ol' Wikipedia will explain more about HTA (HTML Applications), and MS gives you the basics - so all I can do here is to present a full little example showing how simple it would be to add some spice to a script (Command .bat Batch, Python or other) that needs some user input. Here we can see how simple it is to display the HTML GUI from the script, passing in any required parameters if required, and retrieve the user's input afterwards.

Example HTA

The HTML below is all you need to produce a basic example GUI like this :

  <title>My HTA Test</title>


    Sub NoContextMenu()
      window.event.CancelBubble= True
      window.event.ReturnValue= False
      ' Alert("Right-click is not allowed, sorry!")
    End Sub

    Sub Window_onLoad
        ' use the following line to disable right-click menu and prevent View Source
        Set document.onContextMenu=GetRef("NoContextMenu") 

        ' resize to quarter of screen area, centered
        window.resizeTo screen.availWidth/2,screen.availHeight/2
        window.moveTo screen.availWidth/4,screen.availHeight/4
        'Msgbox oHTA.commandLine

        ' Passed-in code - will execute to set variables
        arrParams = Split(oHTA.commandLine, "`")
        For i = 1 to (Ubound(arrParams))
          ' Msgbox arrParams(i)

        ' SCRIPT-specific code : set controls from executed code
    End Sub 

    Sub Set_Env_Vars
      ' msgbox "writing " & txt_str1.value
      Set oWSS = CreateObject("WScript.Shell") 
          ' FYI: msgbox oWSS.ExpandEnvironmentStrings("%WinDir%")
      strBat="Echo @SET ""ZZ_NUM1=" & txt_num1.value _
          & """ ^& @SET ""ZZ_STR1=" & txt_str1.value & """ > ""%TEMP%""\testhta-set.bat"
      strBat=strBat & " & Echo @SET ZZ_NUM1=^& @SET ZZ_STR1=^&> ""%TEMP%""\testhta-clr.bat" "cmd /C " & strBat ,0,true
      Set oWSS = Nothing 
    End Sub 

<BODY bgcolor="#ccccFF">
  Just a little test....
  Batch Path &nbsp; <input type="text" value="" name="txt_bp"">
  Number &nbsp; <input type="text" value="" name="txt_num1"">
  String &nbsp; <input type="text" value="" name="txt_str1"">
  <input type="button" value="Return these to the batch"
   name="run_button"  onClick="Set_Env_Vars"> 

The <Head> section contains the HTA:APPLICATION tag to specify a number of options about the HTA.

In the <Script> section we have :

Sub NoContextMenu()
(along with the first line of Window_onLoad)
Prevents the user from seeing the source code by disabling any right-click pop-up menu which would have had a View Source option. The user won't have any clues to reveal that it's an HTA.

* Sub Window_onLoad
Sets the window size and location, runs the code passed in from the batch, and then uses those newly set variables to set the control values.

* Sub Set_Env_Vars
This is called as the click action for the button, and uses a WScript.Shell object to run a single line of Command Line code (two or more commands can be executed in one line with a & between them) in order to create a temporary batch file in the %TEMP% folder. This - containing environment variable assignments - will be called from the main batch file after the HTA has closed, providing a mechanism to get the values available to the batch file.
Sure, you could write the file the hard way in the VBScript, but why not call a simple piece of familiar command line code instead - with all the %TEMP% decoding for free?

In the <Body> section, we have normal HTML with <Input> controls, and whatever ever else you would need to make things look beautiful.

If you'd rather use JavaScript, yes, that's just as capable. Here's the part that resizes and centers the HTA, and sets the function to run automatically when loading/refreshing :

<SCRIPT LANGUAGE="javascript">
    function Window_onLoad(){  // resize to quarter of screen area, centered

Example Batch File

You could, of course, call the HTA directly and rely upon it doing whatever it needs to do itself, or arrange for it to call hidden batch files to do any heavy lifting. But this blog post is about calling an HTA from an already-running batch file for user interaction.

This testhta.bat calls the above HTA, passing in some code that gets executed within the HTA - setting some variables. Everything after the backtick ` is interpreted as code by the HTA. Several lines of code can be compressed into one line by using a : between each command.

In this example the %~dp0 trick is passed in (shown in the GUI but not passed back to the batch) to show how you could pass in the batch's path in case you decide to try building in functions within the batch file (chunks of code that get bypassed in normal use but can be used by calling the batch with suitable parameter to enable a goto), to be called from within the HTA - another can of worms of complexity! (with the advantage of containing all your code in just two files, the .bat and .hta)

When control returns to the batch, it calls the temporary batch file that the HTA created, and in this way the cmd environment variables are set and will then be available to use within the batch. Here's an example of the "%TEMP%\testhta-set.bat" contents:
@SET "ZZ_NUM1=5.678" & @SET "ZZ_STR1=Oh, I see." 

@echo off
start /wait mshta.exe "C:\somepath\test.hta" ` batch_path="%~dp0" : num1=3.14 : str1="String Param" : msgbox("Wow!")
if exist "%TEMP%\testhta-set.bat" call "%TEMP%\testhta-set.bat"
echo Result: %ZZ_STR1%

 rem Tidy up and remove Env Vars & temp batches
 if exist "%TEMP%\testhta-clr.bat" call "%TEMP%\testhta-clr.bat"
 if exist "%TEMP%\testhta-clr.bat"  del "%TEMP%\testhta-clr.bat"
 if exist "%TEMP%\testhta-set.bat"  del "%TEMP%\testhta-set.bat"

Yes, I realise that including runable code - in the call to the HTA file - is something that would raise an eyebrow or two amongst purists, but this is just one simple way to pass in data to be used by the HTA - there are other ways. This is for personal use, or where security issues aren't a problem. Just showing the easiest way to get you started!

Python version

Some subtle differences here in :

#Python v3.3
print("Hello, World!")

# First, call the HTA - using | in place of any "  - and wait for it to close
from subprocess import Popen    # now we can reference Popen
from subprocess import PIPE
new_process_obj = Popen(['mshta.exe', r'C:\your-path\testpy.hta', r'` batch_path=|no %~dp0 available| : num1=3.14 : str1=|String Param| : msgbox(|Wow!|)'])

# retrieve a line of code to exec, from py file in temp folder
process = Popen(['cmd.exe','/c type %TEMP%\\ & echo '], shell=False, stdin=PIPE,stdout=PIPE,stderr=None)  
output = process.stdout.readline()  
estr= output.decode("utf-8")[:-3]  # delete some trailing "\r\n garbage from the & echo

# now we have the variables set

# pause in case run from Win Explorer double-click
input('Press <ENTER> to continue')

When the HTA is launched, the VBScript code is passed in with the | character used in place of ", for Pythonesque reasons. Also, when the string is retrieved within the HTA it will have an unwanted trailing " - which calls for two replaces :

        For i = 1 to (Ubound(arrParams))
            ' next 2 lines needed for Python method of calling the HTA
          arrParams(i)=Replace(arrParams(i),Chr(34),"") ' delete any "
          arrParams(i)=Replace(arrParams(i),"|",Chr(34)) ' any | becomes "

One final difference is the method used to pass the values back to the Python script. This time I've used a file in %TEMP% to be exec-uted. Here's an example, one line with two commands separated by a semi-colon  :
ZZ_NUM1=3.14; ZZ_STR1="String Param"

This is created by this modified part of the orginal HTA (in Sub Set_Env_Vars)  :

      strBat="Echo ZZ_NUM1=" & txt_num1.value _
          & "; ZZ_STR1=" & chr(34) & txt_str1.value & chr(34) & " > ""%TEMP%""\"

Of course, as both the HTA and Python have access to the Clipboard, that could be used for passing data back or forth instead, as one of many alternatives.


I don't want to duplicate someone else's fine work, so have a good read here :
In this case the author is starting from the HTA and launching the PowerShell from there, instead of the other way around. Whatever seems logical to you!

I have rewritten the HTA in JavaScript and I provide a PS script in my follow-up posting

Monday, 27 May 2013

21st Century Pascal

I worked with Pascal for a number of years, so I'll always have an affection for the language of Begins and Ends. It was created in the late 1960s as a very readable and structured general purpose language, and was obviously so suitable for the task that it caught on widely for teaching purposes. As the years passed, MS VB managed to capture the mindset of the masses, then we saw the competing platforms of Java and .NET. Pascal languished on the sidelines, with most of its fans using Delphi, but along the way the procedural language gained full OOP abilities that brought it up to date and able to hold its own.

Pascal finds itself still incorrectly maligned as an 'obsolete training language' (very far from the case!) while the world assumes that C-like languages of curly braces { } are the One True Way - as if a little change in syntax flavouring really makes any difference! I do prefer more verbose languages given the choice, begins and ends just stand out more than tiny { and }s. You'll find that 'muscle memory' makes typing begin as quick as entering a password, and a modern Pascal code editor will autocomplete the matching end for you.

Here's some reading material comparing Pascal and C :
and assorted opinions are offered here :

In the end, we can easily conclude that by the time you 'outgrow' Pascal (if ever?) then you'll be experienced enough to switch to the more terse C (or descendants) without any great trouble - so it's still a great way to start programming.

Free Pascal and Lazarus

And here's a great reason to get going with Pascal. It's Free! The Free Pascal project has brought us an Open Source compiler, and the Lazarus IDE is also an impressive piece of work for development. In one easily installed all-in-one IDE application, it's a snap to produce GUI programs with standard controls and much much more. It talks to all the major databases and makes Pascal a credible alternative to other languages.

Your Lazarus/FPC program will compile to a standard .exe that requires no runtime framework, is as fast as other compiled languages (leaving interpreted scripting languages in its dust), and all this for no cost and no vendor lock-in. Free! What are the alternatives? Java is still at the mercy of Oracle, and .NET has Mono, but .... isn't Totally Free quite appealing? Scripting languages like Python and Ruby have their fans, but after studying them to my satisfaction I found I simply didn't need them (yet?) and creating GUIs seems to be a nightmare compared to the Lazarus drag-and-drop simplicity.


Hundreds of users are downloading every day, from Source Forge. I downloaded the 110MB file lazarus-1.0.8-fpc-2.6.2-win32.exe, and it was an easy install with no tricky questions to confuse anyone :

 When you fire it up, you'll find that the various parts of the IDE user interface are separate windows :

but this is nothing to worry about, even if you have previous experience with Visual Studio it's no biggie - you get used to it (so easily that they've never been motivated to change this).

If you don't like the rather nasty default font, it can be changed easily via Tools.. Options.. Editor..Display so you can make it look as smart and contemporary as you like :

... and I was also able to change the Default Text Background colour to some something slightly darker than bright white glaring into my eyeballs.

Almost too easy!

In moments you can add a button to a form, and display a mesage with showmessage :

So why not give it a try? There's a helpful Wiki and the forums are friendly. It must be straightforward because most of the forum questions are of a quite advanced technical nature - nobody seems to need to ask about the basics and run of the mill queries.

I believe Lazarus is unmatched in its niche, so have a go!

Thursday, 23 May 2013

AutoIt or AutoHotkey?

It can be very useful indeed to be able to run simple scripts to set up macro hotkeys to fire off a whole load of commands, even controlling other running apps. Even better if you can use it for more interactive tasks, as a general purpose programming tool, allowing neat and functional GUIs with ease.

For example, for my radio use I developed an AHK script that can be drag-n-dropped onto a standalone interpreter .exe (portable - no install required) that fires up several small copies of WinAmp audio player, each configured to a different soundcard and routed to its own channel on the mixing desk. It gives me the ability to select a random track at the press of a button, from the library of hundreds of tracks - ensuring variety by removing any tendency to stick to my same old favourites.

My script can stop/start a WinAmp instance by referencing its name (WinAm1/WinAm2/WinAm3 etc), mute the audio or bring it up to normal again, seek to 30s into the track (lots of tracks take time to get going, so it's quicker to audition them some way in, when they're well under way), pause, restart, etc. Another window shows me a list of jingles, and double-clicking one will automatically load it into the usual 'cart' ready to play, and tick a box so that I can see that I've already used it in the show. The small main GUI window has an Exit button that closes everything it opened, tidying away everything in one fell swoop.

The script is over 400 lines long, and it helps that Notepad++ colours the syntax for me (Language.. A.. Autoit) but I'm glad I commented as I went (back in 2011) because a couple of years down the line there are parts that are now bafflingly odd without my comments to explain it!

So... AutoIt or AutoHotkey?

In the beginning there was AutoIt and it was good. But lacking. Lacking enough for Chris Mallett to break away and develop AutoHotkey as an alternative, because his proposal to integrate hotkey support into AutoIt v2 failed to generate response from the AutoIt community.

Chris Mallett : "The AutoHotkey project began about two years ago because I loved AutoIt v2 but wanted faster, better, and built-in hotkeys. Although AutoHotkey does not use any AutoIt v2 source (I didn't even know its source had been released), it does use AutoIt v3 source for about 34 commands, such as Process and FileCopy. In addition, AutoHotkey includes ahk2exe, which is a modified version of Jon's script compiler. In addition, it includes AutoScriptWriter (the macro recorder) and Window Spy, both of which were written by Larry Keys. So thanks to Jon, Larry, and the other AutoIt developers for all their innovation, hard work, and generosity."

So that explains why AHK exists. Me, I had already gotten used to AHK's odd syntax and was well underway to achieving what I needed before I heard about AutoIt, so I wasn't sufficiently impressed to switch even if I had reason to. I found the AHK forums and documentation were friendly and helpful, so there wasn't any point in changing just for slightly clearer more BASIC-like syntax in places. I can adapt to anything, verbose BEGIN/END, C/Java/etc's {} or Python's Significant Whitespace - it makes no real difference to me. Sometimes AHK drives me nuts and it seems like Every. Single. Line. Of. Code. needs to be researched as I go, but it's worth the pain in the end. There is no simpler way to intercept hot keys and control other programs already running. How much code would it take in a conventional programming language to do the following?

 ; simple example
msgbox You pressed F12

That's an AHK script in its entirety - and one of these four lines is a comment! Even though - as a software dev - I find it painful that the message isn't in quotes (to be fair, you can do that if you want - msgbox % "You pressed F12" - but that doesn't make the scripts all over the www more readable), it's still packing a remarkable amount of functionality into those few lines, tapping into the very heart of Windows to intercept the key handling routines.

A quick bit of more history, to help you to decide between the two competing camps :
As AHK progressed, tension arose between AHK and AutoIt - "AutoIT is no longer open source. Before AutoHotKey existed, AutoIT v3 was open source. According to the AHK help file, AutoHotKey adapted source code from AutoIT v3 such as Window Spy and the script compiler. When Autohotkey launched, disagreement happened between the creators of AutoIt and Autohotkey because Autohotkey placed advertisements on Google that directly targeted AutoIt v3 searches and displayed Autohotkey as a "sponsored link" ad for Autohotkey app. This included posts on the AutoIt forum that the Autohotkey creator posted with link to his software. Shortly afterwards, a new version of AutoIt was released as closed source." -

.. so the upshot is : no matter what the capabilities of either program, if you prefer to select your software based upon how Open it is, then only AutoHotkey fits the bill; AutoIt may be Freeware but it is Closed Source. With Closed Source I always wonder why - what are they hiding and for what good reason? With Open Source it feels like the project will live for as long as I could possibly want and I won't be wasting my time learning it.

Want to decide based upon more technical merits? is a good start. Or Google for : autoit vs autohotkey

Wednesday, 22 May 2013

Turbo Pascal 3 revisited

For some reason thoughts popped into my head of an old program I wrote to produce a Lissajous figure on the screen, in Turbo Pascal 3 back in the 1980s. This is done by plotting X and Y coordinates from two independently running sine waves.

At the time it ran very smoothly and impressively fast (I thought!) on my old DOS box - a gloriously primitive twin-floppy box with a mono CRT and IIRC it didn't even have a full 640k of RAM - and sat there rotating gracefully like a trace on an oscilloscope.

Looking at the file date it looks like I played with it again in 1995, and I vaguely remember it had sped up a bit on a more advanced Win 95 box, hehe.

Here's the code, FWIW - note the repeat.. until 1=2 will require Ctrl-C to break out :)

program liss;
VAR px, py : Real;  { phase in rad X,y-axis }
VAR ox, oy : Real;  { phase in deg X,y-axis }
    stepy : real;
CONST  ScXs = 38;  { Scr X Size }
       ScYs = 10;  { Scr X Size }
       StepX = 0.1;
    If ox>6.2831 THEN ox:=ox-6.2831;
    If oy>6.2831 THEN oy:=oy-6.2831;
    GotoXy(Trunc(41-(SIN(oy)*ScXs)), Trunc(13-(SIN(ox)*ScYs)));
    Write( ' ');
    If px>6.2831 THEN px:=px-6.2831;
    If py>6.2831 THEN py:=py-6.2831;
    GotoXy( Trunc(41-(SIN(py)*Scxs)), Trunc(13-(SIN(Px)*Scys)));
    Write( 'รพ');  { use block character obtained by
                    holding ALT and typing 2 5 4 on the numeric keypad }
  until 1=2;
Needless to say, on modern kit it runs like lubed lightning, comparatively - even without XP's ALT+ENTER full screen textmode. I even tried it in DOSBox for a laugh - rather more slow there.
To install and play with TP v3, it can be downloaded (ZIP) free of charge from the copyright holders (seeing as it's an antique!) from here :

I found a PDF manual at

I used 7zip (FOSS) to unzip the download into C:\DATA\TP\ and had a blast from the past. I can still remember Crtl-KB and Ctrl-KK to mark text, Ctrl-KC to copy, Ctrl-KD to exit :)

In fact, I still used as a text editor for years, even when we moved on up to Metaware's Pro Pascal. It was handy for general text file use, until the 64k file size limit started to get in the way, and then I moved on to Win32Pad as it was easier than NotePad to retain my familiar DOS-like white-on-black colour scheme. From there I migrated to Notepad++ for coloured syntax.

TURBO runs in B&W mode at first, and you will need to set the TINST.MSG and TURBO.COM files to ReadWrite (if the unzip process sets them ReadOnly) before you'll have any luck setting the colour mode to 80x25 colour using the installer TINTST.COM

In this day and age I suspect that Pascal newcomers tend to go straight to FreePascal and the Lazarus IDE, but this old clunker would certainly be one way to start from the BEGINning :)

Workflow Automation

Another little program of mine was a working environment like this :

This was back in days of 8.3 filenames, and even with Win95 I still worked at the command line. So I rigged up this code to present pages of directory contents in alpha order, with the arrow keys moving the tab-card shaped surround around the files. I could tab between two different folders and selected files. At the push of a button I could do whatever I needed with the selected file - edit, launch all the usual batch files to compile, link, check in/out from version control, etc.

It was launched with two quick almost-adjacent keys because I found I could call a batch file '.bat - single quotes are valid in a DOS filename. The batch called my turbo pascal .com program, and that in turn rewrote the same batch file that had called it.. while it was still running! Odd, perhaps, but it worked well so long as the folder and filenames within the batch were padded with trailing exclamation mark characters so that the batch was always the same size with everything in the same place - otherwise poor old DOS got a bit confused by the mid-run rewrite :)

My turbo .com program launched further processes by adding the command to the end of the '.bat and then exiting - one batch file chained into another (no 'call' command with two batches in memory at once) so as much memory was available for compiling as if I'd typed the command at the prompt directly. This was vital when memory was at a premium and sometimes I had to be really careful to optimise my code to get things to compile! It all worked like a charm and made life a lot easier. I do enjoy harnessing the power of lazyness to make life easier.

Those were the days! Not sure I'd want to go back though, to be honest :)

Tuesday, 21 May 2013

SQL-like queries for your variables - .net LINQ

Got a list, collection, DataGridView full of data from your database, and you'd like to query that in-memory data without going back to the database itself? I recommend taking a look a LINQ Query Expressions if you haven't already.

With a simple code block, you can treat an IEnumerable<T> or IQueryable<T> collection as a data source and get results fast!

Here we iterate through a DGV (regardless of Option Strict), showing how to use an initial Select to convert the long-winded cell references into more descriptive names of your choice, and then Select again (with grouping and ordering in this case) as required  :

Dim test = From row In DataGridView1.Rows.Cast(Of DataGridViewRow)()
    Where Not row.IsNewRow
    Select num1 = CDbl(row.Cells(0).Value), num2 = CDbl(row.Cells(1).Value)
    Group By num1 Into f1_Distinct = Min(num1), f2_Distinct = Max(num2)
    Select num1 = f1_Distinct, num2 = f2_Distinct
    Order By num1
If test.Count > 0 then MsgBox(test(0).num1)

That's a lot less code that doing it the hard way! Does this whet your appetite to discover more? :)

Useful SQL - CTE Common Table Expressions

I was delighted when I discovered the power of CTEs, some years ago, as they have made many of my queries so much clearer since I started making use of this powerful syntax when required.

Basically, in one single SQL statement you can create one or more named temporary tables in a sequence, each able to reference the prior SELECTs. This works anywhere a single SQL statement can be issued, even in a report definition.

How about an example? You will usually query database tables of course, but here we hard-code some data for a stand-alone example requiring no other data :

With TestData as (
        SELECT 1 as id, 'Number 1' as txt
  UNION SELECT 2,'no 2'
  UNION SELECT 3,'3rd'
  UNION SELECT 4,'a 4th'
Largest as (
  SELECT Max(id) as Max_id FROM TestData
SELECT TestData.*
FROM TestData, Largest
WHERE id=Largest.Max_id

You can see this in action and play with it (MS SQL) with an example I've created at (CTEs are also available in Oracle and PostgreSQL - but not in MySQL).

The temporary tables can be used as any other table, joins and all. If this is new to you, and you can appreciate from this the power and possibilities that this gives you, I'm sure you'll be thinking "Wow!". The CTE technique has saved my day numerous times when a simple SQL query simply couldn't do what I needed.

And then I found that you can make them recursive! Ever needed a sequential list of numbers? Here's a list from 1 to 10 :

With Numbers AS
 SELECT n = 1
 SELECT n + 1
 FROM Numbers
 WHERE n+1 <= 10
(!6/55030/12 )

That could be useful with an Outer Join if you want to visually find gaps in a sequence of row IDs, for example. It works like a For Loop in C, by specifying the start, the increment, and the final condition.

Here's one more, where we take the 2nd and 3rd rows from the hardcoded record set :

With TestData as (
        SELECT 1 as id, 'Number 1' as txt
  UNION SELECT 2,'no 2'
  UNION SELECT 3,'3rd'
  UNION SELECT 4,'a 4th'


Numbers as (
 SELECT n = 2
 SELECT n + 1
 FROM Numbers
 WHERE n+1 <= 3

SELECT * FROM TestData,Numbers
WHERE id=Numbers.n
(!6/55030/10 )

Many complex problems on StackOverflow can be solved cleanly using the power of CTE.

CGI-bin - and batch files!

I had a little play with Windows Batch Files and CGI after spotting a question on StackOverflow and trying to help.

Having installed WampServer for experiments with PHP and MVC frameworks like Yii and Laravel, it was easy to add a test batch file in the www folder and try some things out. Building upon something I found at .... I wrapped it up into HTML and sorted out the Parameters Count (ArgC)  :

@echo off
  rem  This works in Wampserver's Apache cgi-bin...
  rem     http://localhost/cgi-bin/testbat.bat?param1+param2
echo Content-Type: text/html
echo ^<html^>^<head^>^</head^>^<body^>
echo ^<H1^>Hello world!!!^</H1^>

echo ^<PRE^>
echo argV = %0

set argC=0
for %%x in (%*) do Set /A argC+=1
echo argC = %argC%

echo %%1 = %1
echo %%2 = %2
echo %%3 = %3

echo ^</PRE^>

echo ^</body^>^</html^>
Given that batch files are a lot more capable than they used to be back in the 80s, it's intriguing to ponder just how much you could achieve this way!

Back to the StackOverflow question, it seems that some commands that work individually at the command prompt don't actually seem to work in a CGI batch file. I found that it may be possible to get around this with a 'FOR' construct to execute a command somewhat indirectly  :
FOR /F "usebackq delims==" %%x IN (`cmd /c echo NOT hidden from browser`) do echo %%x

All good fun. Please let me know if you find this useful :)

Fixing the audio on Dell Inspiron 1300

I was recently surprised - and somewhat less than delighted at the nature of the issue - to be asked to fix a problem where the owner couldn't record a quick snippet of something that they were listening to, on an old Dell Inspiron 1300 lappy (complete with about 1 minute's worth of battery capacity!). The only options available were the line-in and mic. My heart sank as I wondered if it was going to be as tricky to 'record what you hear' as it was on my netbook with Linux Mint.

A quick search online revealed large numbers of complaints about Dell removing the feature to appease the recording industry. Oh dear. It looked like I was facing a challenge to get the SigmaTel audio working.

I thought forum threads like this one were going to be the answer.
I found a number of suggestions to add/change a registry key
I added a binary value “0000 01” and rebooted, but that didn't help.
The oft-given links for new R171789 drivers led to the "Sorry, not available" page on Dell's support site.

Finally, I found this link for the new drivers. The 1300 is listed in the compatibility list, so I downloaded and installed. The 1300 didn't appear in the installer list, but I pressed onwards, trusting the initial webpage more.

Success! We now have "SigmaTel Audio: Stereo Mix" in the drop down list in Audacity, and the owner can record what they need to, at last. Thank-you Dell, eventually :)

BTW you'll need to right-click the Systray volume icon, select "adjust audio properties", then select the audio tab, press the volume button on the Sound Recording section, and finally you'll arrive at the mixer panel where you can select the Stereo Mix as the recording channel. A bit of a longwinded route to get there, but at least you can!

Thursday, 2 May 2013

How to delete index.dat browsing history

Index.dat files, as maintained by MS-IE (see grow ever larger, slowing things down, and are a privacy threat. If you're leaving a job and have been browsing privately in your lunchtimes (with permission of course) perhaps you'd like to remove all the data before you leave - presuming you're trusted enough to tidy up your things and weren't working in the kind of place that shows you straight to the door after giving you the bad news (with your belongings boxed up and sent on afterwards!). Thankfully I've always been allowed the chance to clean up after myself, so far.

Having just Googled for delete index.dat I'm surprised so many sites came up making a meal of it.

If you were on a 'roaming profile' setup, it's tricky and I ran out of time to experiment. Sorry. But if not, the main difficulty is that the files are in use if you try to delete them.

It's not too hard to find the files - open a command window, head up to root \  and try :
C:\>dir index.dat /s /ahs /b

If you want full control over which to delete, create a batch as follows :
C:\>dir index.dat /s /ahs /b >idat.txt

Open idat.txt in a text editor, and add " quotes around each path+filename by speedy use of the back-tick ` key, and [down] and [End] etc.... then Search-and-Replace the ` with "
... then seach and replace
del c:\

Ctrl-a to mark the whole lot, and ctrl-c to copy to clipboard.
Search and replace
del c:\
attrib -s -h -a -r c:\

Go to a new line at the end, and paste in the saved delete lines.

You now have a batch file to change all the index.dat attributes and delete them, with two blocks of safely quoted filenames (in case of spaces in the paths) preceeded by attrib and del commands.

Save and rename idat.txt to idat.bat

Releasing the grip on the files - kill Explorer

Keep the command window open while killing explorer - this will remove the "file in use" problem you would have had when trying to delete the dat files.

To do this (Windows 7), click the Windows Start button, then [Left Shift]+[Ctrl]+[Right Click] the area around the Shut Down button. A context menu should appear with "Exit Explorer" as an option. Use it, and the Windows desktop will vanish execpt for the wallpaper and the one command window you still have open.

On earlier versions of Windows, you may have to select the Shut Down dialogue, then Click the Cancel button with [Left Shift]+[Ctrl]+[Alt] all held. It will happen immediately with no chance to back out - no "Exit Explorer" option this way.

With Explorer out of the way, the files you want to remove can now be disposed of. Run the batch file of attrib and del commands, or if you want to do it more quickly, this command will take care of the Hidden, System & Readonly attributes of all index.dat files :

C:\>for /r %x in (index.dat*) do @attrib  -s -h -a -r "%x"

You then just up arrow and edit the command to delete instead of changing the attribs :
C:\>for /r %x in (index.dat*) do @del "%x"

Now simply Ctrl-Alt-Delete to log off and get back to normal. When you load up MS-IE again, the index files with be recreated, hopefully smaller than they were, default sizes, containing no private data any more.

It's almost as easy as booting from a Linux USB stick, mounting the NTFS drive and deleting files from there.
Now, resolve to use Firefox instead, in future!

Tuesday, 30 April 2013

Easy hard-disk fill to thwart undelete

A Windows Command Batch can fill the hard drive surprisingly quickly, if you've deleted some files and wish to ensure that they can't be recovered by a simple undelete utility.

First, create a file of rubbish to fill the disk with, if you can't find a suitable file of garbage to use, a simple
dir >>fill.dat
repeated a few times (up arrow and enter at the command prompt) will do the trick. Keep going until it's a couple of hundred k in size.

Then create a fill.bat as follows

@echo off
type fill.dat>>delete.txt
goto start

Run the batch fill and stand back and marvel at how quickly delete.txt grows.
From what I've read on Slashdot discussions about disk recovery, it really isn't going to be possible for anyone to get at your old files (notes, embarrassing photos and music, etc!) once the space they occupied is overwritten! You'll probably want to leave a note about deleting the massive text file :)

Thursday, 18 April 2013

Flash updates - note to future self

Sometimes Flash updates OK, sometimes it breaks with an error "Actionlist not found".

That's probably because I'm tucked away behind a web proxy, or maye Sophos sticks its oar in.

If the 990k updater (like install_flashplayer11x32_gdrd_aih.exe)  fails, uninstall completely, then get the whole 17MB installer install_flash_player.exe from
( )
That solves it. You're welcome, Future Self.