Marcus's Musings

Ok, so I lied!

Been back a week, no posts. I've been busy, ok?

I've just stumbled across "disc golf"! Apparently it's big in the States, but I'm totally flabbergasted - I honestly have never heard of it!! It sounds good fun though, and I'll try to give it a shot when I visit Florida at the end of the month. There's even a course here in London, so if I like it I might take it up! :-)

Code snippets - random integers and URL decoding

It's about time I posted some code (I keep promising people on forums, but somehow I never get round to it) so here's a few bits & bobs to start off with. I've got a huge code library to sift through, so I'll try to pick some things which will be helpful to a lot of people, as well as a few more interesting things here & there...

Firstly, generating random integer numbers within a specified range seems to crop up from time to time, so here's what I wrote a number of years ago. Notice the way I use the Select Case statement, which I think it far more elegant than the equivalent If...ElseIf...Else statements would be...

'Returns a random integer in the range specified (inclusive)
Function RndInt(LowerBound, UpperBound)
Randomize()

Select Case True
'rather pointless, but since it's possible...
Case UpperBound = LowerBound
RndInt = UpperBound

'swap bounds if they're the wrong way round
Case UpperBound < LowerBound
RndInt = Int((LowerBound - UpperBound + 1) * Rnd + UpperBound)

'just generate it as normal
Case Else
RndInt = Int((UpperBound - LowerBound + 1) * Rnd + LowerBound)
End Select
End Function

Quite some time ago I noticed on MSDN that VBScript does actually have Escape() and UnEscape() functions, which rather oddly are not documented in the downloadable Windows Scripting 5.6 reference help (if you don't have this downloaded, installed, and in regular use when coding then shame on you!!). I then used the UnEscape() function to whip up a simple to decode URLs encoded with Server.URLEncode (Microsoft didn't see fit to provide one themselves):
'Decodes a string encoded with Server.URLEncode()
Function URLDecode(ByVal str)
str = UnEscape(str)
str = Replace(str,"+"," ")
str = Replace(str,"%2A","*")
str = Replace(str,"%40","@")
str = Replace(str,"%2D","-")
str = Replace(str,"%5F","_")
str = Replace(str,"%2B","+")
str = Replace(str,"%2E",".")
str = Replace(str,"%2F","/")
URLDecode = str
End Function
More to follow later... must do some work!

A few words on enumerated constants

A particularly common error which comes up again and again (although happily on the decline) is people using enumerated constants (e.g. adOpenStatic) - something which is definitely to be encouraged (because of increased readability/maintainability, futureproofing if the value of the enumerated constant changes, etc.) - but failing to actually import the constants themselves into their scripts, which leads to all sorts of errors. This is entirely understandable, since this essential step is shamefully overlooked in the scripting documentation and most other relevant Microsoft reference material. When it *is* mentioned, usually only the antiquated method of including the ADOVBS.INC file is offered. However, there is a better way, which is to import the binary type library (a file containing information about the contents of a DLL, including enumerated constants) directly.

In your scripts (preferably in an include file that every script in your site will share, or in the GLOBAL.ASA) simply add the following lines *outside* of the script tags:

<!-- METADATA TYPE="TypeLib" NAME="Microsoft ActiveX Data Objects 2.5 Library"
UUID="{00000205-0000-0010-8000-00AA006D2EA4}" -->
<!-- METADATA TYPE="TypeLib" NAME="Microsoft ADO Ext. 2.5 for DDL and Security"
UUID="{00000600-0000-0010-8000-00AA006D2EA4}" -->
The above will import all the enumerated constants you need for using the ADO and ADOX objects (from ADO version 2.5), which include ADODB.Connection, ADODB.Recordset, ADODB.Stream, ADOX.Catalog, etc. The import of these values is much quicker (i.e. more efficient) than the traditional ADOVBS.INC method because the values are retrieved directly from the compiled binary typelib (rather than evaluating an ASP script) and also helps to avoid versioning problems. You will also find that only ADO has a readily available .INC file (which is out of date), so you would have to create your own for other libraries if you wanted to use that old approach.

These typelib imports work by looking up the UUID number in the section of the Windows registry where all the typelibs are stored (HKCR\TypeLib) to find where the typelib file is located on disk. For more on finding these yourself (which you may well need to do), read this excellent tutorial.

Incidentally, if you use the FSO, you may find it helpful to import is the Microsoft Scripting Runtime ({420B2830-E718-11CF-893D-00A0C9054228}), so that you don't have to do this.

Blog bug

There seems to be a problem (at least in IE 6, haven't had a chance to test in anything else) with the paragraph immediately following a <pre> element (which I've been using to post the code below)... I'll sort it out when I get a chance...

EDIT 1: it looks like there's problems *inside* <pre> elements too... hmm...
EDIT 2: In fact, I'm really not happy with the design of the site at all... it's virtually untouched from the default BlogWorks XML install, and for some unfathomable reason, all the pages currently have an embedded CSS stylesheet rather than linking to a shared external one.

<MentalNote>Really must give the site a redesign soon...</MentalNote>

Code snippet - output CSV from Recordset

Recently I desperately needed to examine some tables from an MSSQL database on a remote server (in Italy) that I couldn't access directly, and which didn't have Enterprise Manager (or any other useful tools) installed (so I couldn't get someone to extract them for me).

I came up with a VBScript Windows shell script to do the job, and thought that others might find my CSV file dumping function handy (you can use it as-is in an ASP script or shell script). Apologies for the lack of documentation (done in a hurry and I don't have time to comment it now), but I think it's fairly self explanatory anyway...

Function RenderValue(ByVal Value)
If IsNull(Value) Then
RenderValue = ""
Else
RenderValue = Value
End If
End Function


'Outputs a recordset to a CSV file
Sub SaveRecordSetAsCSV(ByRef objRS, ByVal CSVFilePath)
Set objCSVFile = CreateObject("ADODB.Stream")
Call objCSVFile.Open

If Not objRS.EOF Then
DataArray = objRS.GetRows

XMax = objRS.Fields.Count - 1
YMax = UBound(DataArray, 2)
Else
XMax = objRS.Fields.Count - 1
YMax = 0
End If

For X = 0 To XMax - 1
Call objCSVFile.WriteText(objRS.Fields(X).Name & ",")
Next
Call objCSVFile.WriteText(objRS.Fields(XMax).Name, 1)

If IsArray(DataArray) Then
For Y = 0 To YMax
For X = 0 To XMax - 1
Call objCSVFile.WriteText(RenderValue(DataArray(X, Y)) & ",")
Next

Call objCSVFile.WriteText(HandleNull(RenderValue(XMax, Y)), 1)
Next
End If

Call objCSVFile.SaveToFile(CSVFilePath, 2)

Set objCSVFile = Nothing
End Sub

You'll probably want to add your own error handling code, and you probably should modify the RenderValue function to handle strings containing commas (I didn't need to at the time)!

UPDATE: See post above for a better way.

Code snippet - create a disconnected Recordset

Disconnected Recordsets are very handy for manipulating dynamically-populated tables of information (e.g. views of folders, with names, sizes, dates, times, etc) and so I have a handy little function for summoning one when needed:

Function CreateDisconnectedRecordset()
Dim RS
Set RS = Server.CreateObject("ADODB.Recordset")
RS.CursorLocation = adUseClient
Set RS.ActiveConnection = Nothing
RS.CursorType = adOpenStatic
RS.LockType = adLockBatchOptimistic
Set CreateDisconnectedRecordset = RS
End Function

More BlogWorks XML needed features

Blog post categories with view filtering and corresponding separate RSS feeds (in addition to the existing one). I rather like YoungPup's approach but would probably use small (16x16 pixel) icons (with the category description on a tooltip via the TITLE attribute) instead of the *nix-style list.

Code snippet - output CSV from Recordset... a better way

Amusingly, I've just found that I've coded a CSV generating function before, and I did a better job the first time round!! lol

Here's how I *should* have done it the other day...!

Sub RecordsetToCSV(ByRef RS, ByVal CSVFilePath, ByVal IncludeFieldNames)
Set objCSVFile = CreateObject("ADODB.Stream")
Call objCSVFile.Open

If IncludeFieldNames Then
'string concatenation issues aren't a problem for a small string of field names
For Each Field In RS.Fields
If FieldNames = "" Then
FieldNames = Field.Name
Else
FieldNames = FieldNames & "," & Field.Name
End If
Next

FieldNames = FieldNames & vbCRLF
Call objCSVFile.WriteText(FieldNames, 1)
End If

Call objCSVFile.WriteText(FieldNames & RS.GetString(adClipString, , ",", vbCRLF, ""))
Call objCSVFile.SaveToFile(CSVFilePath, 2)
Set objCSVFile = Nothing
End Sub

Much better! :-)

Code snippet - dispose of an object / array

It's always wise to manually dispose of objects and arrays that you use, since although ASP's garbage collection is much improved in ASP 3.0 (IIS 5), it is known to overlook some things, and hence lead to memory leaks.

Rather than typing the same old "Set objSomething = Nothing" every time, it's much easier to use a function/sub to do the job for you, and unsuprisingly I have one up my sleeve. It will automatically call .Close() on ADO objects, call .RemoveAll() on Dictionaries, and Erase arrays as appropriate - how many of you remember to do those every time?! Finally, it sets the variable to Empty, as if it was never used - not essential, but I like it!

Sub Kill(ByRef Obj)
Select Case True
Case IsObject(Obj)
Select Case LCase(TypeName(Obj))
Case "recordset", "command", "stream", "connection"
'closeable ADO objects
If Obj.State <> 0 then
Obj.Close
End If

case "dictionary"
'remove all the pairs
Obj.RemoveAll

Case else
'something else so don't
'do anything special

End Select

Set Obj = Nothing

Case IsArray(Obj)
'clear the array
Erase Obj

Case Else
'do nothing at all

End Select

'Now revert it to an unitialized state
Obj = Empty
End Sub
NOTE: It's also good to standardize your Sub calling syntax... I use the Call SubName(Param1, Param2, ...) syntax so that whenever I pass parameters to something I use brackets.

Radical new forum architecture & functionality?

Last week I started a thread at Sitepoint Forums about some ideas I had for a new forum architecture, making extensive use of metadata to avoid some problems which are common to large forums (like which forum a thread "belongs" in), to allow users to create dynamic views of the forum tailored to their requirements, and to greatly enhance the sophistication of forum searches when trying to locate a solution to a problem (or find an old post).

There has been a little discussion, but not as much as I'd hoped, so perhaps you might have a few comments to throw into the melting pot!? Has it been done already?

I'm off!

Just thought I'd mention that I'm off to Tampa Bay, Florida for a 10 day holiday (well-deserved, I think, because although I've had some fun on my business trips, I haven't had a proper holiday for about 2 years), so I won't be around on the forums much (if at all), and I probably won't be posting anything here...

Pleased to see that the weather's looking pretty good... :D

Florida, here I come!

A quick update

I've been enjoying a nice relaxing holiday here in Florida, with the highlights being a day at Universal Islands Of Adventure and two day stay at the historic town of St Augustine, where the what we now know as the United States of America was actually founded (*not* Jacksonville, as is often claimed).

The plan had been to visit Universal Studios today, but unfortunately car problems have put paid to that. Nevermind, I'm going to soak up some rays in the garden instead...

Notes to self

Upon return to England:
1) Shower
2) Sleep off jetlag
3) Publish ASP HTTP class
4) Publish ASP debugging class
5) Republish ASP synamic download functions (originally posted here)

;-)

Back in town

I arrived back home on Monday and started back at work today. I'll post some snaps and work through my to-do list (see previous post) as soon as I have a moment, so if you're waiting for more code, watch this space!