Playing with Regular Expressions, part 1 – Find the last word in a sentence

As a developer I quite often run into situations where I need to find an occurrence of a word or phrase in a text or some kind of number or pattern in a string. Regular expressions makes these tasks relatively simple and usually you will find loads of examples on how to match your specific pattern on the internet. This blog series will cover how to think when working with regular expressions. 

In all the examples in this blog series I have used the same sample text (you will see it in the samples). I have used an excellent tool named Expresso to evaluate all expressions in this blog entry. Of course it is possible to tweak the expressions in my examples below so that it searches other patterns as well.

Problem

I want to retrieve the last word in a sentence.

Solution

Use the \b anchor together with the pattern that ends the sentence to instruct regex that you only want the word that appears right before the dot-character, carriage return (and/or) line feed or the dollar-character that represent the end of the string.

The pattern below will match all the last words in each sentence.

(?<myNamedGroup>\b\w+)(?:\.|\r\n)

 

You should se the following result when executing the expression against the sample text.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut eu sem nisl.
Nulla elementum consectetur leo nec consequat. Vestibulum quis libero sit amet arcu euismod bibendum a.

Nulla elementum:    1389-89-1443

Praesent a nibh sed augue mollis vehicula.
Vestibulum nisl elit, eleifend a tristique nec, faucibus a sem.

Explanation

  • The first parenthesis tries to match the first word and will put the word found in a named group named myMatchedWord when a word is successfully matched.
  • \b\w+ will match every word in the text that has at least one character. The \b anchor matches either the beginning or the end of a word.
  • (?:\.|\r\n) will match either a dot or the combination of carriage return and line feed. The (?: part of the expression tells regex to skip the pattern in the matched content.
  • The whole expression together will return all words where the first part of the expression is combined with one of the expressions found in the second part.
Advertisements

Working with comma separated strings in t-SQL

Problem

When working with t-SQL you might run into a scenario where you want to return all the records that corresponds to the IDs found in a comma separated list, e.g. ‘123,456,789’. There are several ways to solve this problem and while browsing around I found an interesting solution.

Solution

In the example below I take advantage of the CHARINDEX and PATINDEX functions in t-SQL to search for specific patterns in the comma-separated string by concatenating the customer ID with commas and then match the concatenated string with the id string. The functions returns a number  greater than 0 if the sought string exists in the ID string which can be useful in my scenario where I got a comma separated list with customer IDs.

CHARINDEX works similar to the Substring or InStr functions that you can find in other programming languages. The function takes three parameters and the first is the string to search for, the second is the string to search and the third (which is optional) is the starting point where the search will begin in the string to search.

PATINDEX works similar to CHARINDEX, but allows the use of wildcards when performing the search. The function takes two parameters and the first is the string or pattern to search for and the second is the column or variable that will be searched. The method returns an integer bigger.

The example below also show that LIKE can be used to search the comma-separated string for specific IDs using a concatenated value along with wildcards.

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE DATABASE [WeirdScience]
GO

USE [WeirdScience]
GO

CREATE TABLE [dbo].[Customers](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Customer] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO

INSERT Customers (Customer) VALUES (N'Dan Jansson')
INSERT Customers (Customer) VALUES (N'Lars Svensson')
INSERT Customers (Customer) VALUES (N'Lisa Karlsson')
INSERT Customers (Customer) VALUES (N'Kalle Johansson')
INSERT Customers (Customer) VALUES (N'Gullan Bengtsson')
GO

DECLARE @customerstring varchar(max);
SET @customerstring = '1,3,4,6'

-------------------------------
-- This example is based on the following article:
-- http://www.projectdmx.com/tsql/sqlarrays.aspx
-------------------------------

SELECT *
  FROM Customers
 WHERE CHARINDEX(
	',' + CAST(ID AS NVARCHAR(50)) + ',', 
	',' + @customerstring + ','
	) > 0
 
SELECT *
  FROM Customers 
 WHERE PATINDEX(
	'%,' + CAST(ID AS NVARCHAR(50)) + ',%', 
	',' + @customerstring + ','
	) > 0

SELECT *
  FROM Customers
 WHERE ',' + @customerstring + ',' 
  LIKE '%,' + CAST(ID AS NVARCHAR(50)) + ',%'

USE master
GO

DROP DATABASE WeirdScience
GO

For more ideas on how to work with arrays in SQL Server you should take a look at the excellent article found here: http://www.projectdmx.com/tsql/sqlarrays.aspx

Shrinking the Transaction Log in SQL Server

Problem

Well, this is a classical problem: The transaction log has been growing since who knows when and is now taking up almost all space on the server. You need to shrink the transaction log in order to free space on the server.

Solution

There are several solutions to this problem and it all depends on your database. This time I used the following script to get rid of all data in the transaction log and worth mentioning is that I didn’t care about the data in the transaction log; my goal was only to get rid of the unnecessary data and to reduce the size of the transaction log.

USE [MyDatabase]  
GO
ALTER DATABASE [MyDatabase]
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE ([MyDatabase_Log], 1);
GO
ALTER DATABASE [MyDatabase]
SET RECOVERY FULL;
GO

The script above worked fine for me and you will find similar solutions on the internet. If you analyze the script you will see that it changes the recovery model from FULL to SIMPLE which means that the database will not longer log transactions that occur in the database. After the change we execute the DBCC SHRINKFILE command that will try to shrink the file stated by the logical filename (MyDatabase_Log) to the desired size in megabytes (the second parameter to the DBCC command). When the file has been shrunk we restore the recovery model to FULL that was the original setting for the database; of course this all depends on your scenario, but my guess if you read this is that you are using the FULL recovery model. 

While looking around I found another older solution that were used earlier to accomplish sort of the same thing, however it’s recommended not to use the approach mentioned below.

USE [MyDatabase]  
GO
DBCC SHRINKFILE([MyDatabase_Log], 1)
BACKUP LOG [MyDatabase] WITH TRUNCATE_ONLY
DBCC SHRINKFILE([MyDatabase_Log], 1)
GO 

Using PowerShell to create rules in Outlook

I recently started using Microsoft PowerShell to carry out administrative tasks and it didn’t take long for me to fall in love with it. Being a script fanatic using DOS instead of Windows, VBScript and JScript, Console-apps in favor to Windows apps, regex, etc. it’s easy to see the tremendous power in PowerShell; you can say that it’s the command-prompt on steroids :-) I’m lovin’ it!

The task – Creating an Outlook rule using PowerShell

Ok, do this might seem to be a trivial task, but as always when you think something seems to be easy you run into problems, especially when working with COM-objects/Automation. So, in order to solve my problem I had to do a lot of looking around at the forums on the web.

The script below creates a new rule in Outlook (I’m currently running Outlook 2010) that checks the sender of the message and looks for certain words in the subject field. If the sender is e.g. foo@bar.com (FROMEMAIL) and the subject contain the words ‘Hello’ and ‘World’ (SUBJECTWORDARRAY), the message should be forwarded to fii@bar.com (FORWARDEMAIL) and moved to the MyCustomFolder (MOVETOFOLDER) folder which is a subfolder to the Inbox folder.

##########################################
# Based on PowerShell 2.0
##########################################
function add_outlook_rule
{
    param([string]$RuleName,
          [string]$FromEmail,
          [string]$ForwardEmail,
          [string]$RedirectFolder,
          [string[]]$SubjectWords)
              
    Add-Type $class -ReferencedAssemblies Microsoft.Office.Interop.Outlook
    
    Add-Type -AssemblyName microsoft.office.interop.outlook 
    $olFolders = "Microsoft.Office.Interop.Outlook.OlDefaultFolders" -as [type]
    $olRuleType = "Microsoft.Office.Interop.Outlook.OlRuleType" -as [type]
    $outlook = New-Object -ComObject outlook.application
    $namespace  = $Outlook.GetNameSpace("MAPI")
    $inbox = $namespace.getDefaultFolder($olFolders::olFolderInbox)
    
    $rules = $outlook.session.DefaultStore.GetRules()
    $rule = $rules.Create($RuleName,$olRuleType::OlRuleReceive)

    $SubjectCondition = $rule.Conditions.Subject
    $SubjectCondition.Enabled = $true
    $SubjectCondition.Text = $SubjectWords
     
    $d = [System.__ComObject].InvokeMember(
        "EntryID",
        [System.Reflection.BindingFlags]::GetProperty,
        $null,
        $inbox.Folders.Item($RedirectFolder),
        $null)
    $MoveTarget = $namespace.getFolderFromID($d)
    
    # Uncomment the row below if you want to use the DeletedItems
    # folder as MoveToFolder.
    #$MoveTarget = $namespace.getDefaultFolder(
        $olFolders::olFolderDeletedItems)

    $MoveRuleAction = $rule.Actions.MoveToFolder
    [Microsoft.Office.Interop.Outlook._MoveOrCopyRuleAction].InvokeMember(
        "Folder",
        [System.Reflection.BindingFlags]::SetProperty,
        $null,
        $MoveRuleAction,
        $MoveTarget)
    $MoveRuleAction.Enabled = $true

    $FromCondition = $rule.Conditions.From
    $FromCondition.Enabled = $true
    $FromCondition.Recipients.Add($FromEmail)
    $fromCondition.Recipients.ResolveAll()

    $ForwardRuleAction = $rule.Actions.Forward
    $ForwardRuleAction.Recipients.Add($ForwardEmail)
    $ForwardRuleAction.Recipients.ResolveAll()
    $ForwardRuleAction.Enabled = $true

    $rules.Save()
}

##########################################
# SYNTAX: 
##########################################
# add_outlook_rule RULENAME 
#                  FROMEMAIL
#                  FORWARDEMAIL
#                  MOVETOFOLDER
#                  SUBJECTWORDARRAY
##########################################

add_outlook_rule "This is my custom rule" "fii.from@bar.com" "foo.forward@bar.com" "MyCustomFolder" @("foo","bar")

 

And now for the problems I ran into…

Problem number 1

The first problem I ran into was with the criterion that looked for certain words in the Subject field. I found out that the subject field expected an array of type string, but by some strange reason I didn’t get it to work, even tough I Reflected the PIA (Primary Interop Assembly) for Microsoft Outlook and checked exactly what types the class expected and tried to cast the variable in all possible ways available.  After some additional investigation I found out that the string array needed to contain more than one item which means that if you have an array with two or more items the script started to work.

$SubjectCondition = $rule.Conditions.Subject 
$SubjectCondition.Enabled = $true 

# Will not work...
# $a = $(“foo”)

# Works like a charm
$a = $(“foo”,”fii”)

$SubjectCondition.Text = $a

 

Problem number 2

When I tried to set the Folder property on the MoveToFolder object I got an error stating that I was trying to set a property that expected an object that implemented the interface MAPIFolder to a ComObject which apparently doesn’t do that.

This is the error message I received:

Exception setting "Folder": "Cannot convert the "System.__ComObject" value of type "System.__ComObject#{00063006-0000-0000-c000-000000000046}" to type "Microsoft.Office.Interop.Outlook.MAPIFolder"."

No matter how I tried to cast the ComObject to a MAPIFolder the script constantly failed. I tried to get a handle on the desired folder in many different and this part was actually quite easy, but the problem still remained: It was not possible to cast the ComObject to a MAPIFolder. So what now? Give up? Never! :-)

I came up with the idea that maybe the solution would be not to cast the ComObject but instead try to interact with it somehow, and for this we have our .NET friend – Reflection! By using reflection on objects in .NET it is possible to get and set properties on ComObjects and this turned out to be the solution to my problem.

In the first code block below I use the InvokeMember method that is part of reflection for types in .NET to first get a property from a ComObject. There are other ways to get a handle on a folder in Exchange/Outlook, but I wanted to show an alternate way of doing this in this sample. The second code block I set the Folder property on the MoveToFolder action object. This solved my second problem.

$d = [System.__ComObject].InvokeMember(
	"EntryID",
	[System.Reflection.BindingFlags]::GetProperty,
	$null,
	$inbox.Folders.Item($RedirectFolder),
	$null)
$MoveTarget = $namespace.getFolderFromID($d)
	
$MoveRuleAction = $rule.Actions.MoveToFolder
[Microsoft.Office.Interop.Outlook._MoveOrCopyRuleAction].InvokeMember(
	"Folder",
	[System.Reflection.BindingFlags]::SetProperty,
	$null,
	$MoveRuleAction,
	$MoveTarget)
$MoveRuleAction.Enabled = $true

 

Problem number 3

When everything started to work (scripts are like that – it’s all or nothing) everything seemed fine until I used the script in a batch where I created many rules on a specific mailbox store. The problem here turned out to be a limit in the definition storage on the Exchange server that only allowed the size of the rule definitions for an account to be 32 kb. In my case I exceeded the limitation and got an error when executing my scripts.

More information

Architecture mismatch when using ODBC

Today I ran into a problem when I was trying to connect to a data source using ODBC.  Currently I’m working on a web application that uses ASP.NET running on .NET Framework 2.0 and that has one connection to a SQL Server database and a number of other connections to databases based on Pervasive SQL. The web application uses ODBC to connect to the Pervasive SQL databases.

Ok, and now for the problem…

When I was trying to start the web application I got an error message saying “ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application”.

After a little investigation I found out that my Visual Studio project was compiled targeted towards the x64 architecture (64 bit) using ODBC drivers created for the x86 architecture (32 bit) which led to the error message. Apparently you need to have ODBC-drivers that is compiled for the same architecture as the application that uses them.

This is how I solved the problem and got my application up and running…

The first thing I did was to make sure that I had a valid ODBC connection, and since I’m running Windows 7 x64 (64 bit) I needed to open the old ODBC Data Source Administrator using the following path:

%WINDIR%\SYSWOW64\ODBCAD32.exe

image

The WOW part in SYSWOW64 basically means Windows 32 bit on Windows 64 bit and is sort of a copy of the system32 folder created on the x86/32 bit architecture

I checked my ODBC connection and made sure that it was properly created.

Next thing – Visual Studio …

Ok, the ODBC connection seems to work and the next thing I had to do was to change the platform for the projects in my Visual Studio solution. First a little background: the solution contains five C# projects that are class libraries and two projects that are ASP.NET web site projects. The web sites has references to the class libraries and everything is created with default settings in Visual Studio, i.e. the platform is set to “Any CPU”. The web sites are configured to run using IIS and not Cassini, the ASP.NET development server used by Visual Studio.

Changing the target platform for the class libraries was easy and I simply just opened the properties for each project and changed the Platform target found under the Build tab in the Properties dialog.

image

When I tried to do the same thing with the The ASP.NET web site projects I discovered that that type of Visual Studio projects doesn’t have any setting for this. In order to make the web sites run on the x86 architecture I found out that this has to be done in Internet Information Services Manager (IIS).

Next and final thing – IIS

So, almost there! I started the IIS Manager (INETMGR) and expanded the node for Application Pools. This is where we get the possibility to tell the application what platform to run on. I selected the application pool used by my web site and clicked Advanced Settings in the right panel. The next thing I did was to alter the Enable 32-Bit Applications setting to true which basically means that my web site application should run as an application using the x86 (64 bit) architecture.

image

 

Problem solved!

I solved the problem following the steps above and my web site application is up and running.

Problem with Integration Features in Windows 7

Lately I have had a lot of problems with my virtual pc environments which is running Windows 7. My hosting OS is Windows 7 and the big reason for the trouble is because of the Windows 7 Service Pack 1. Since it was installed on my hosting machine it totally messed up the Virtual PC Integration Features for my virtual machines.

The mouse stopped working in the virtual machine plus everything else except from the keyboard, so I was quite stuck with using shortcuts to navigate. Every time I tried to install the Integration Components I got an error, think it was error code 1602 or something, which resulted in a rollback for the installation process.

How to solve the problem

After a lot of browsing around on the net I came across some information that explained that it is possible to prohibit the installer from doing a rollback of the installation of the Integration Feature Components if anything should fail (which it always does on my machine). By prohibiting a rollback the installer is unable to create certain files used for the rollback and the result is that the Integration Features are installed anyway even though something might be wrong. This solved my problem!

image

 

  1. Open MMC using the textbox in the start menu.
  2. Add the Group Policy Object Editor snap-in to the console.
  3. Select the Computer Configuration node and expand it.
  4. Select the Administrative Templates node and expand it.
  5. Select Windows Components and locate the Windows Installer item.
  6. In the Windows Installer; locate the Prohibit rollback setting.
  7. Set the value for Prohibit rollback to Enabled.
  8. Save the value and close the Console application.
  9. Restart the system.
  10. Start the installer for the Integration Features.
  11. If you get questions regarding processes that needs to be shut down you can terminate them using the task manager. I needed to terminate two processes related to the Virtual PC and I simply wrote down the PID numbers and terminated them in Task Manager.
  12. The installer should complete and inform you that the installation succeeded.
  13. Restart the system.
  14. Now at least the mouse should be working.
  15. I don’t really know how to fix the rest of the integration features, but I will update this blog post if I get some ideas. I think that the cause of the problem is something with the hardware together with service pack 1 for Windows 7.

Changing language for PowerPoint presentations

When you want to change the dictionary that is being used for a PowerPoint presentation, you might think that it is just to select all slides and change dictionary or enter some magic dialog where you find some setting for the current presentation, but this is where the problem occur and you realizes that it is not as easy as you might think.

Everything that appear in a PowerPoint slide are considered to be an object. This means that the title of the slide is an object as well as the text area where you have a bulleted list, the additional text areas, etc.. And if you think a little bit further you will soon realize that you need to select each object in each slide in order to change to the correct dictionary. Pheew!

Luckily there is always a way of scripting this, so I created a macro in VBA for PowerPoint that iterates through every slide in the presentation and changes dictionary for all objects found on the slide. The script is quite self-explanatory and you’ll find it below.

Sub SetLanguageIDEnglishUS()
    Dim slideCount, shapeCount, j, k
    slideCount = ActivePresentation.Slides.Count
    For j = 1 To slideCount
        
        ' Change dictionary for all shape objects
        shapeCount = ActivePresentation.Slides(j).Shapes.Count
        For k = 1 To shapeCount
            If ActivePresentation.Slides(j).Shapes(k).HasTextFrame Then
                ActivePresentation.Slides(j).Shapes(k).TextFrame _
                    .TextRange.LanguageID = msoLanguageIDEnglishUS ' msoLanguageIDSwedish
            End If
        Next k
        
        ' Change dictionary for all notes
        shapeCount = ActivePresentation.Slides(j).NotesPage.Shapes.Count
        For k = 1 To shapeCount
            If ActivePresentation.Slides(j).NotesPage.Shapes(k).HasTextFrame Then
                ActivePresentation.Slides(j).NotesPage.Shapes(k).TextFrame _
                    .TextRange.LanguageID = msoLanguageIDEnglishUS ' msoLanguageIDSwedish
            End If
        Next k
    Next j
End Sub