I am a “TC Winner”


TC Winner

I won this badge for solving the Running Total Problem at Beyond Relational.

Posted in Uncategorized | Leave a comment

MySQL NET Connector 6.3.6 bugs with Visual Studio 2010

Just wasted a day trying to fix things after installing mysql connector 6.3.6 which has managed code for vs 2010 and net 4. The problem is that this version reconfigures VS 2010. After which, VS stops working properly:

1. Add Connection wizard just closes as soon as you enter any server connection details – so you can not add a connection to any MySQL database by any method.

2. Similar problems happen when trying to add new object to an existing Dataset which connects to MySQL already. Various errors which get progressively worse until the wizard just fails to load at all.

The only fix I’ve found is to roll back to MySQL connector version 6.3.2. ;¬(

Posted in Uncategorized | Leave a comment

Logging: how to get Class and Method names

When logging errors is helps to have the Class and method names where the exception was thrown without using string constants that are a pain to maintain. The code to get the names is

Current assembly and class name = Me.GetType.FullName
Current class name = Me.GetType.Name
Current method (function or sub) = System.Reflection.MethodBase.GetCurrentMethod().Name

Posted in VB.NET | Leave a comment

The Benefits of Stored Procedures

The ‘debate’ over Stored Procedures comes up a lot, mostly in the PHP forums I frequent and I have become tired of repeating myself. So this post summarises the benefits and reasons for using Stored Procedures and I can just point people to it in future.

Benefits of Stored Procedures

  • Precompiled execution. The Database Server compiles each stored procedure once and then reutilizes the execution plan. Query strings on the other hand have to be syntax checked, then the execution plan is calculated by the query parser and only then can the query be executed. This results in tremendous performance boosts when stored procedures are called repeatedly.
  • Reduced network traffic. Calling a stored procedure with an array of parameters takes less characters than the full query string. This is especially true when the target query is long and complex.
  • Efficient reuse of code. Stored Procedures can be called again and again from different code modules, scripts and applications instead of each one having to build its own query string.
  • Code seperation and abstraction. Using stored procedures means that the sql is all in one place – on the database where it should be. This centralisation makes it easier to develop and maintain the sql itself since it is not scattered around in other code and languages. All the client code will contain are the CALLs or EXECs.
  • Enhanced security controls. You can grant users and clients access only through the stored procedures and deny access to the tables themselves. This makes it easier to control what users can and cannot modify, or even see.

Posted in Uncategorized | Leave a comment

How to run Xcopy with elevated privileges

Here is another usefull class that will run as an administrator for xcopy operations on protected filesystems. In my case this is a large folder structure for our clients’ files that we do not want the ordinary user to be able to move or delete: users being what they are they’ll drag and drop anything given half the chance ;p. I use it to clone a standard folder template with different ACLs on different folders and sub-folders.


Option Strict Off
Option Explicit On

Imports System
Imports System.ComponentModel
Imports System.Diagnostics
Imports System.Security

#Region “XcopyClass Class”

”’
”’ COM class to run Xcopy commands as DocRootAdmin
”’
”’
”’ source: http://www.dreamincode.net/code/snippet1231.htm
”’ Modified to run as Domain Admin
”’ and to conform to CKA coding standards
”’

_
Public Class XcopyClass

#Region “COM GUIDs”
These GUIDs provide the COM identity for this class
‘ and its COM interfaces. If you change them, existing
‘ clients will no longer be able to access the class.

Public Const ClassId As String = “321f8234-8343-415f-9c77-3628ff655502″
Public Const InterfaceId As String = “a4d2eb4c-935b-45e2-bc3a-f8e34fea2bf5″
Public Const EventsId As String = “1ef73250-4246-459c-9808-1ee2fdd9a045″

#End Region

#Region “Constructor”

‘ A creatable COM class must have a Public Sub New()
‘ with no parameters, otherwise, the class will not be
‘ registered in the COM registry and cannot be created
‘ via CreateObject.

Public Sub New()
MyBase.New()
End Sub

#End Region

#Region “Declarations”

Private _Status As Status

#End Region

#Region “Properties”

”’
”’ Exposes object status to code that uses this COM object
”’
”’
”’
”’
”’ Status is set by class methods to indicate success
”’ or exceptions/errors and their causes
”’

Public ReadOnly Property Status() As Status
Get
Return _Status
End Get
End Property

#End Region

#Region “Public Methods”

”’
”’ Run Xcopy as admin user
”’
”’
the xcopy arguments: switches, source and target ”’ True or False
”’
”’ This function runs xcopy as the DocRootAdmin user
”’

Public Function ProcessXCopy(ByVal arg As String) As Boolean
Dim flgReturn As Boolean = False
Dim XCopyArguments As String = arg
Dim XCopyProcess As New Process()
Dim XCopyStartInfo As New ProcessStartInfo()
Dim ExitCode As Integer
Dim securePwd As New SecureString()

Try

XCopyStartInfo.FileName = “CMD.exe “

‘set the domain and user
XCopyStartInfo.Domain = AppData.GetAppData(“DocRootDomain”)
XCopyStartInfo.UserName = AppData.GetAppData(“DocRootAdmin”)

‘convert password to a secure string and set it
For Each character As Char In AppData.GetAppData(“DocRootPassword”)
securePwd.AppendChar(character)
Next
securePwd.MakeReadOnly()
XCopyStartInfo.Password = securePwd

‘do not write error output to standard stream
XCopyStartInfo.RedirectStandardError = False
‘do not write output to Process.StandardOutput Stream
XCopyStartInfo.RedirectStandardOutput = False
‘do not read input from Process.StandardInput (i/e; the keyboard)
XCopyStartInfo.RedirectStandardInput = False

XCopyStartInfo.UseShellExecute = False
‘Dont show a command window
XCopyStartInfo.CreateNoWindow = True

XCopyStartInfo.Arguments = “/D /c XCOPY ” & XCopyArguments

XCopyProcess.EnableRaisingEvents = True
XCopyProcess.StartInfo = XCopyStartInfo

‘start cmd.exe & the XCOPY process
XCopyProcess.Start()

‘set the wait period for exiting the process
XCopyProcess.WaitForExit(5000)

‘get the return code
ExitCode = XCopyProcess.ExitCode

‘Now we need to see if the process was successful
If ExitCode > 0 And Not XCopyProcess.HasExited Then
XCopyProcess.Kill()
flgReturn = False
Else
flgReturn = True
End If

Catch ex As Exception
flgReturn = False
SetStatus(True, ErrorCode.CodeException, “XcopyClass->ProcessXCopy”, “Exception” & vbCrLf & vbCrLf & ex.Source & ” : ” & ex.Message)
Finally
XCopyProcess.Dispose()
XCopyStartInfo = Nothing
ProcessXCopy = flgReturn
End Try

End Function

#End Region

#Region “Private Methods”

”’
”’ Set Status object with current error status for reporting
”’
”’

Error Status: True or False ”’
”’
”’
”’
”’ When a method fails for any reason,
”’ it calls this method to populate the Status object

”’ with error details for client code to interogate.
”’

Private Sub SetStatus(ByVal StatusSet As Boolean, ByVal StatusCode As ErrorCode, ByVal StatusSource As String, ByVal StatusMessage As String)

If IsNothing(_Status) Then _Status = New Status

_Status.ErrorState = StatusSet
_Status.ErrorCode = StatusCode
_Status.ErrorSource = StatusSource
_Status.ErrorMessage = StatusMessage

End Sub

#End Region

End Class

#End Region

Posted in VB.NET | Tagged | Leave a comment

How to run code as another user

One of the things I sometimes need is to run code with elevated privileges. This class allows your code to impersonate another user with the necessary privileges. It is written as a COM class so that I can reference it in VBA.


Option Strict Off
Option Explicit On

#Region “AliasAccount Class”

”’
”’ COM class to enable impersonation of another user account
”’
”’
”’ Code found on the NET
”’ http://weblogs.asp.net/ralfw/archive/2003/11/24/39479.aspx
”’ Modified slightly to be a COM class
”’

_
Public Class AliasAccount

#Region “COM GUIDs”
‘ These GUIDs provide the COM identity for this class
‘ and its COM interfaces. If you change them, existing
‘ clients will no longer be able to access the class.

Public Const ClassId As String = “39b4e646-a308-47eb-8f39-bd8dc16e07a6″
Public Const InterfaceId As String = “4a0d67f3-8935-4840-93ad-fb28ecd4bf0f”
Public Const EventsId As String = “fff5562b-6ab1-4d01-b678-022c39c00510″

#End Region

#Region “Constructor”

‘ A creatable COM class must have a Public Sub New()
‘ with no parameters, otherwise, the class will not be
‘ registered in the COM registry and cannot be created
‘ via CreateObject.

Public Sub New()
MyBase.New()
End Sub

#End Region

#Region “Declarations”

Private _DomainName As String
Private _UserName As String
Private _Password As String

Private _tokenHandle As New IntPtr(0)
Private _dupeTokenHandle As New IntPtr(0)
Private _impersonatedUser As System.Security.Principal.WindowsImpersonationContext

#End Region

#Region “Properties”

Public Property DomainName() As String
Get
Return _DomainName
End Get
Set(ByVal value As String)
_DomainName = value
End Set
End Property

Public Property UserName() As String
Get
Return _UserName
End Get
Set(ByVal value As String)
_UserName = value
End Set
End Property

Public Property Password() As String
Get
Return _Password
End Get
Set(ByVal value As String)
_Password = value
End Set
End Property

#End Region

#Region “Public Methods”

”’
”’ runs code under the user account details set
”’
”’

Public Sub BeginImpersonation()
Const LOGON32_PROVIDER_DEFAULT As Integer = 0
Const LOGON32_LOGON_INTERACTIVE As Integer = 2
Const SecurityImpersonation As Integer = 2

Dim win32ErrorNumber As Integer

_tokenHandle = IntPtr.Zero
_dupeTokenHandle = IntPtr.Zero

If Not LogonUser(_UserName, _DomainName, _Password, LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT, _tokenHandle) Then
win32ErrorNumber = System.Runtime.InteropServices.Marshal.GetLastWin32Error()
Throw New ImpersonationException(win32ErrorNumber, GetErrorMessage(win32ErrorNumber), _UserName, _DomainName)
End If

If Not DuplicateToken(_tokenHandle, SecurityImpersonation, _dupeTokenHandle) Then
win32ErrorNumber = System.Runtime.InteropServices.Marshal.GetLastWin32Error()

CloseHandle(_tokenHandle)
Throw New ImpersonationException(win32ErrorNumber, “Unable to duplicate token!”, _UserName, _DomainName)
End If

Dim newId As New System.Security.Principal.WindowsIdentity(_dupeTokenHandle)
_impersonatedUser = newId.Impersonate()
End Sub

Public Sub EndImpersonation()
If Not _impersonatedUser Is Nothing Then
_impersonatedUser.Undo()
_impersonatedUser = Nothing

If Not System.IntPtr.op_Equality(_tokenHandle, IntPtr.Zero) Then
CloseHandle(_tokenHandle)
End If
If Not System.IntPtr.op_Equality(_dupeTokenHandle, IntPtr.Zero) Then
CloseHandle(_dupeTokenHandle)
End If
End If
End Sub

#End Region

#Region “Exception Class”

Public Class ImpersonationException
Inherits System.Exception

Public ReadOnly win32ErrorNumber As Integer

Public Sub New(ByVal win32ErrorNumber As Integer, ByVal msg As String, ByVal username As String, ByVal domainname As String)
MyBase.New(String.Format(“Impersonation of {1}\{0} failed! [{2}] {3}”, username, domainname, win32ErrorNumber, msg))
Me.win32ErrorNumber = win32ErrorNumber
End Sub
End Class

#End Region

#Region “External Declarations and Helpers”

Private Declare Auto Function LogonUser Lib “advapi32.dll” (ByVal lpszUsername As [String], _
ByVal lpszDomain As [String], ByVal lpszPassword As [String], _
ByVal dwLogonType As Integer, ByVal dwLogonProvider As Integer, _
ByRef phToken As IntPtr) As Boolean

Private Declare Auto Function DuplicateToken Lib “advapi32.dll” (ByVal ExistingTokenHandle As IntPtr, _
ByVal SECURITY_IMPERSONATION_LEVEL As Integer, _
ByRef DuplicateTokenHandle As IntPtr) As Boolean

Private Declare Auto Function CloseHandle Lib “kernel32.dll” (ByVal handle As IntPtr) As Boolean

_
Private Shared Function FormatMessage(ByVal dwFlags As Integer, ByRef lpSource As IntPtr, _
ByVal dwMessageId As Integer, ByVal dwLanguageId As Integer, ByRef lpBuffer As [String], _
ByVal nSize As Integer, ByRef Arguments As IntPtr) As Integer
End Function

Private Function GetErrorMessage(ByVal errorCode As Integer) As String
Dim FORMAT_MESSAGE_ALLOCATE_BUFFER As Integer = &H100
Dim FORMAT_MESSAGE_IGNORE_INSERTS As Integer = &H200
Dim FORMAT_MESSAGE_FROM_SYSTEM As Integer = &H1000

Dim messageSize As Integer = 255
Dim lpMsgBuf As String
Dim dwFlags As Integer = FORMAT_MESSAGE_ALLOCATE_BUFFER Or FORMAT_MESSAGE_FROM_SYSTEM Or FORMAT_MESSAGE_IGNORE_INSERTS

Dim ptrlpSource As IntPtr = IntPtr.Zero
Dim prtArguments As IntPtr = IntPtr.Zero

Dim retVal As Integer = FormatMessage(dwFlags, ptrlpSource, errorCode, 0, lpMsgBuf, messageSize, prtArguments)
If 0 = retVal Then
Throw New System.Exception(“Failed to format message for error code ” + errorCode.ToString() + “. “)
End If

Return lpMsgBuf
End Function

#End Region

End Class

#End Region

To use it one simply does this: (in my case I am using it to overwrite a protected file but it could be anything)


Dim oAA As New AliasAccount()

‘impersonate user DocRootAdmin
oAA.DomainName = My.Settings.DocRootDomain
oAA.UserName = My.Settings.DocRootAdmin
oAA.Password = My.Settings.DocRootPassword
oAA.BeginImpersonation()

‘move the file
My.Computer.FileSystem.MoveFile(oldFile, newFile, overwrite)

‘end impersonation
oAA.EndImpersonation()

Posted in VB.NET | Tagged | Leave a comment

Open a file in VB.NET

To open a file in it’s default application all you need is one line of code:

System.Diagnostics.Process.Start("www.mysite.com")

or

System.Diagnostics.Process.Start("N:\myfile.pdf")

Posted in VB.NET | Tagged | Leave a comment

John Cleese on Proportional Representation

Given the kind of crap we are bound to see and read in the next few weeks, here is an explanation of the real issue:

Posted in Uncategorized | Tagged , , , | Leave a comment

Capitalise all Words in VBA

As part of an Access data cleanse, I needed to capitalise company’s and people’s names and addresse. So I googled for a regex that would do it. Of course I found plenty that would work very nicely in other languages and on other platforms, but none that worked in VBA. I found that I was not the only person who had faced this problem, but no working solutions. So I have developed my own and present it for you here.

The first problem of course is to break the string up into words. Regex 5.5 will do this very nicely for us. What it will not do is to capitalise the words when it finds them. For this we need to process the found words with vbProperCase.

Here is the complete function for you

Public Function CapitaliseNames(ByVal strIn As String) As String
‘~~~~~
‘Name: CapitaliseNames
‘Version: 1.00
‘Purpose: capitalise the input name – handles hypens and apostrophes

‘Arguments:
‘ strIn = string to capitalise
‘Return:
‘ capitalised string

‘Author: David Soussan
‘Updated: 14/01/2010

‘Comments:
‘ requires reference to VBScript Regular Expression 5.5
‘~~~~~

On Error GoTo ErrorHandler
Dim strReturn As String
Dim oRegEx As RegExp
Dim Matches, Match
Const strPattern As String = “\b(\w+)\b”

‘~~~~~
‘use regex to find all words
‘~~~~~

10:
Set oRegEx = New RegExp
oRegEx.Pattern = strPattern
oRegEx.Global = True
Set Matches = oRegEx.Execute(strIn)

‘~~~~~
‘capitalise each word found
‘~~~~~

20:
For Each Match In Matches

strIn = Replace(strIn, Match.Value, StrConv(Match.Value, vbProperCase))

Next

‘~~~~~
‘lowercase common terms
‘~~~~~

30:
strIn = Replace(strIn, ” And “, ” and “)
strIn = Replace(strIn, ” Of “, ” of “)
strReturn = strIn

ExitProcedure:
CapitaliseNames = strReturn
Exit Function
ErrorHandler:
strReturn = “”
Select Case iLogError(Err.Description, CODE_MODNAME, “CapitaliseNames()”, Err.Number, Erl)
Case ERR_IGNORE
Resume Next
Case Else
Resume ExitProcedure
End Select
End Function

It does of course require my error handler – so substitute your own there (and sorry about the formatting – can’t seem to get copy/paste working in wordpress :( ) And do make sure that you keep the blank before and after the ‘ and ‘ n  ‘ or ‘ unless you want to spoil names like Anderson and Oregon :)

Posted in Uncategorized | Tagged , , | 4 Comments

The Most Truth That I Know

If we could conquer greed then we would conquer need.
This is the most truth that I know.
I only wish it could be so.

Posted in Uncategorized | Tagged | 4 Comments

Some cool Flash widgets

Just came across some cool Flash widgets on abowman.com, check them out.

Pengiuns: they follow your mouse around.

Fish: click on the pond to feed the fish

[edit]turns out it was the FollowSite plugin that was slowing up my blog – lousy plugin that does not work[/edit]

Posted in Uncategorized | Leave a comment

Play a game and donate water

Try it out: Play a game online and do some good while you’re at it.

Posted in Uncategorized | Leave a comment

Act on Global Warming

Get active about global warming and the failure of the Copenhagen conference, click the logo and send an email to our ‘leaders’.

Act Now - Change the Future


;
Actions you can take now


Posted in Uncategorized | Tagged | Leave a comment

Calculate the Running Balance

Another fine use of my sequencing method is to calculate the running balance on a ledger:

SELECT t1.CompanyID, t1.CompanyName, t1.PostingDate, t1.Total, Sum(t2.Total) AS balance
FROM tmpDirLoanData AS t1 INNER JOIN tmpDirLoanData AS t2 ON t1.CompanyID = t2.CompanyID
GROUP BY t1.CompanyID, t1.CompanyName, t1.PostingDate, t1.Total, [t1].[postingdate]>=[t2].[postingdate]
HAVING ([t1].[postingdate]>=[t2].[postingdate])=True;

Here I am using the same method to calculate a Sum instead of a Count – that is the only difference.

PS this is JET sql in case anyone is wondering.

Posted in SQL Tips n Tricks | Tagged , , , , | Leave a comment

Find user’s most recent post in each thread

One common problem that my sequence method can help to solve is how to display the most recent entry that a user has made in each thread that the user has contributed to:


SELECT * FROM posts WHERE usedid = $userid AND postid IN
(SELECT t1.postid, COUNT(t1.postid) AS sequence, t1.postid <= t2.postid AS flg
FROM posts t1 INNER JOIN posts t2 ON t1.userid = t2.userid AND t1.threadid = t2.threadid
GROUP BY t1.userid, t1.threadid, flg
HAVING flg = TRUE AND sequence = 1)

Notice that I use the reverse sequence here (t1.postid <= t2.postid) so that the posts are numbered in descending order with the most recent = 1.

Posted in SQL Tips n Tricks | Tagged , , , , | 1 Comment

How to Sequence each Sub-set of Records

I first developed this method in answer to a very specific problem posted on PHP Builder:keep last 5 logs and delete rest. It is an ideal solution to this knotty problem where there are an unknown number of records in each subset.
The problem is compounded because records will have been inserted in a more or less random order across the sets. To solve it we have to find the sequence within each sub-set.

Now some DBs such as MS SQL and Oracle have that functionality built in, but others do not. For these we need another approach, and the method is to use a self-join with grouping to generate the sequence number.

SELECT t1.userid, COUNT(t1.tableid) AS sequence, t1.tableid, t1.tableid >= t2.tableid AS flg
FROM table t1 INNER JOIN table t2 ON t1.userid = t2.userid
GROUP BY t1.userid, t1.tableid, flg
HAVING flg = TRUE


What this does is to join each row to itself and all those within the same sub-set, in this case ‘userid’, that were inserted before it and then count them. So the first entry for each user is only joined to itself = 1, the second is joined to itself and the first = 2, third to itself and the first two = 3, etc.

To reverse the sequence simply reverse the selection
t1.tableid <= t2.tableid AS flg

So to solve the original problem we just need the reverse sequence which we can then use to delete all records with a sequence greater than 5, neat huh.

DELETE FROM table INNER JOIN
(SELECT t1.userid, COUNT(t1.tableid) AS sequence, t1.tableid, t1.tableid <= t2.tableid AS flg
FROM table t1 INNER JOIN table t2 USING(userid)
GROUP BY t1.userid, t1.tableid, flg
HAVING flg = TRUE) AS q1 USING(tableid)
WHERE sequence > 5

PS this is of course MySQL syntax and many other RDBMS do not support the USING operator for joins

Posted in SQL Tips n Tricks | Tagged , , , , | 2 Comments