How to Call Functions in Powershell – The Right Way

My secret is to use the $PSScriptRoot built-in variable.  If you don’t fully qualify the path, then I’ve seen issues when you run Powershell in different directories, it sometimes cannot find the function.  On the other hand, it’s bad to fully qualify the function filename, because then you cannot easily transfer the scripts to another computer that uses a different disk structure.

So I always fully qualify the function, but with a variable that uses the directory of the main program.

First here is the program that calls the function:

### this is the include statement for the file that contains your functions
. "$($PSScriptRoot)\DemoSharedFunctions.ps1"       

cls
$myFileName = "C:\users\JohnDoe\My Documents\Abcde.txt"
$pathOnly = GetDirectoryFromPath($myFileName)
$fileOnly = GetFilenameFromPath($myFileName)
Write-Host "`$myFileName=$myFileName"
Write-Host "`$pathOnly=$pathOnly"
Write-Host "`$fileOnly=$fileOnly"

Write-Host "Path call in Write-Host = $(GetDirectoryFromPath($myFileName))"

Here is the function (filename=\DemoSharedFunctions.ps1″.  Sometimes I have trouble remembering the exact syntax of the split-path command; so I made two easy to remember function names.

Function GetDirectoryFromPath($Path)
{
$testpath = split-path $Path
### this next line is here just so you can play with it and see the value of the internal variable
#Write-Host "`$PSScriptRoot=$PSScriptRoot"   
return $testpath
}

Function GetFilenameFromPath($Path)
{
$filename = split-path $Path -leaf
return $filename
}

Runtime results:

$myFileName=C:\users\JohnDoe\My Documents\Abcde.txt
$pathOnly=C:\users\JohnDoe\My Documents
$fileOnly=Abcde.txt
Path call in Write-Host = C:\users\JohnDoe\My Documents

 

I included this line (from the above calling script) to explain one more thing:

Write-Host "Path call in Write-Host = $(GetDirectoryFromPath($myFileName))"

This demonstrates how you can call a function directly inside of a Write-Host statement, just by wrapping it like this $(xxx)

How Call a SQL Stored Procedure from Powershell

This blog discusses how to call a stored procedure that takes several input parms, but doesn’t return any data. For instance, you are calling a stored proc to insert data into one or more database tables.

When you are not expecting data back, you don’t need the DataAdapter nor the DataTables classes. Use the “ExecuteNonQuery” method of the command object. (Note: It can return one value such as an integer, count, or success flag, but it doesn’t return any rows of data, i.e. no result set.)

function GetConnectionString()
{
  return "Server=.;Database=VideoGenerator;Integrated Security=False;User ID=myusername;Password=mypassword"
}

function LogVideoTrackingToSQL($license, $keyword, $computerName, $videoFilename, $templateName, $createdDateTimeUTC, $uploadedDateTimeUTC) 
{
    Write-Host "license=$license"
    Write-Host "keyword=$keyword"
    Write-Host "computerName=$computerName"
    Write-Host "videoFilename=$videoFilename"
    Write-Host "templateName=$templateName"
    Write-Host "createdDateTimeUTC=$createdDateTimeUTC"
    Write-Host "uploadedDateTimeUTC=$uploadedDateTimeUTC"

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = GetConnectionString

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = "dbo.LogVideo"  ## this is the stored proc name 
    $SqlCmd.Connection = $SqlConnection  
    $SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure  ## enum that specifies we are calling a SPROC 

      #set each of the 7 parameters 

      $param1=$SqlCmd.Parameters.Add("@License" , [System.Data.SqlDbType]::VarChar)
      $param1.Value = $license 

      $param2=$SqlCmd.Parameters.Add("@Keyword" , [System.Data.SqlDbType]::VarChar)
      $param2.Value = $keyword 

      $param3=$SqlCmd.Parameters.Add("@ComputerName" , [System.Data.SqlDbType]::VarChar)
      $param3.Value = $computerName 

      $param4=$SqlCmd.Parameters.Add("@VideoFilename" , [System.Data.SqlDbType]::VarChar)
      $param4.Value = $videoFilename 
      
      $param5=$SqlCmd.Parameters.Add("@TemplateName" , [System.Data.SqlDbType]::VarChar)
      $param5.Value = $templateName 

      $param6=$SqlCmd.Parameters.Add("@CreatedDateTimeUTC" , [System.Data.SqlDbType]::DateTime2)
      $param6.Value = $createdDateTimeUTC 

      $param7=$SqlCmd.Parameters.Add("@UploadedDateTimeUTC" , [System.Data.SqlDbType]::DateTime2)
      $param7.Value = $uploadedDateTimeUTC 

    $SqlConnection.Open()
    $result = $SqlCmd.ExecuteNonQuery() 
    Write "result=$result" 
    $SqlConnection.Close()
}


CLS 
$license = "4F926ADB-8193-4824-9881-DF147B721889"
$keyword = "test"
$computerName = "PC45"
$videoFilename = "c:\videos\abcdefg.mp4" 
$templateName = "Customer_Appliance4.prj"
$getUTCdate = $(get-date).ToUniversalTime()
LogVideoTrackingToSQL $license $keyword $computerName $videoFilename $templateName $getUTCdate $getUTCdate

To pass a Null (DBNULL) in Powershell, check out this post (use the [System.DBNull]::Value).

So you have the entire end–to-end picture, here is the stored proc being called:

ALTER PROCEDURE [dbo].[LogVideo] 
    @License varchar(36),
	@Keyword varchar(120), 
	@ComputerName varchar(50), 
	@TemplateName varchar(120), 
	@VideoFileName varchar(255), 
	@CreatedDateTimeUTC dateTime2(7), 
	@UploadedDateTimeUTC dateTime2(7)
AS
BEGIN

   /*  Unit Test 

      Declare @utcDate datetime2 = getUTCDate() 
      Exec LogVideo '4F926ADB-8193-4824-9881-DF147B721889', 'Sql-Test', 'Machine-A', 'Customer-abc.prj', 
                   'c:\Videos\SQL-Test.mp4', @utcDate, @utcDate

   */ 


	declare @CustomerID int 
	declare @ErrMessage varchar(500) 

	select @CustomerID = ID from Customer C where C.LicenseGuid = @License 

	if @CustomerID is null 
		BEGIN	
			SET @ErrMessage = 'No customer license found for key=' + @License 
			RAISERROR (@ErrMessage, 11,1)
		END

    insert into VideoTracking 
	   (CustomerID, CreatedDateTimeUTC, UploadedDateTimeUTC, Keyword, ComputerName, TemplateName, VideoFilename) 
	values 
	   (@CustomerID, @CreatedDateTimeUTC, @UploadedDateTimeUTC, @Keyword, @ComputerName, @TemplateName, @VideoFilename) 

/* History 
01/12/2015 - Neal Walters - Initial Version 
*/ 

END

Below show the results of running a query against the stored data:

HowToCallSPROCFromPowershell_Example_Table_Rows

And here are the two underlying tables:
HowToCallSPROCFromPowershell_SQL_Tables

 

See Part 2 here – “How to catch SQL Stored Proc Errors in PowerShell“.

Validate User Data Values in an XML Config File with Powershell

This blog discusses how I decided to validate user data in an XML Config file.

I’ve been creating a rather involved Powershell program that automates the creation of videos and posting them to our team’s video site, in order to create points that could potentially generate a nice part-time income.

As I wrote the program, I externalized any variables that I thought the user might want to customize to an XML config file. I provide documentation and videos to show the user what to change in the config file; but as of yet, I have not had time to make a GUI configuration utility. Thus, the user could make mistakes, typos etc…, and I need to validate that 1) the file is still really XML, and 2) the user has entered valid data values for certain variables.

<?xml version="1.0"?>
<configuration>
  <appSettings>
    <add key="EmailRecipient" value="somebody@somebody.com" />
    <add key="GmailUser" value="somebody-somebody.com" />
    <add key="GmailPassword" value="abcdabcdabcd" />
    <add key="WukarUser" value="somebody@somebody.com" />
    <add key="WukarPassword" value="abcdabcdabcd" />
    <add key="WukarSiteLoginUrl" value="http://www.teamwukar.com/login/" />
    <add key="WukarSiteCustomerVideosUrl" value="http://www.teamwukar.com/members/area/jobs_customervideos.php" />
    <add key="WukarSiteBAVideosUrl" value="http://www.teamwukar.com/members/area/jobs_bavideos.php" />
    <add key="WukarSiteCustomerUploadUrl" value="http://www.teamwukar.com/members/area/jobs_customervideos_nokeyword.php" />
    <add key="WukarSiteBAUploadUrl" value="http://www.teamwukar.com/members/area/jobs_bavideos_nokeyword.php" />
    <add key="YNPlayTuneOnChangeVideo" value="N" />
    <add key="MaxScanDetailRows" value="500" />
    <add key="SlideWaitSeconds" value="1" />
    <add key="KeywordSourceFile" value="c:\Amerisoft\trunk\WukarVideoTools\Data\KeywordFilePastCityStateZip.txt" />
    <!-- Logging level. 0 – Off, 1 – Error, 2 – Warning, 3 – Info, 4 – Verbose  -->
    <add key="LoggingLevel" value="2" />
    <add key="MaxReuseOfSameVideoStyle" value="12" />
    <add key="MonitorStartX" value="0" />
    <add key="WatinDotNet" value="Net40" />
    <add key="VideoDirectory" value="C:\Videos\GeneratedCustomerVideos\" />
    <add key="VideoDirectoryShort" value="C:\Videos\GeneratedCustomerVideos\" />
    <add key="VideoDirectoryLaptop" value="c:\Videos\DubliBiz\GeneratedCustomerVideos\" />
    <add key="VideoDirectoryRDP" value="C:\Users\Administrator\Documents\VideoMakerFX\Exported Videos\" />
    <add key="TemplateConfigDirectory" value="C:\Amerisoft\trunk\WukarVideoTools\Data\" />
    <add key="TemplateDirectory" value="C:\Amerisoft\trunk\WukarVideoTools\Data\" />
    <add key="CSVHistoryFilename" value="C:\Amerisoft\trunk\WukarVideoTools\Data2\SummaryResults.csv" />
    <add key="DesiredHotelTemplate" value="Neal_Hotel.prj" />
    <add key="DesiredAppliancesTemplate" value="CustomerTemplate - Appliances 3.prj" />
    <add key="DesiredBATemplate" value="Neal_BA_1.prj" />
    <add key="DesiredKeywordType" value="Customer" />
    <add key="VideoProductionWaitSeconds" value="60" />
    <add key="PostInitialWaitSeconds" value="30" />
    <add key="CustomKeywordPostWaitSeconds" value="30" />
    <add key="PostMaxSeconds" value="150" />
    <add key="MaxWaitSecondsNewKeyword" value="45" />
    <add key="IsPostOnOff" value="Off" />
    <add key="UploadVideoDirectory" value="c:\Videos\DubliBiz\GeneratedCustomerVideos\Upload\" />
    <add key="UploadVideoMoveToDirectory" value="c:\Videos\DubliBiz\GeneratedCustomerVideos\Uploaded\" />
    <add key="UploadKeywordType" value="Customer" />
  </appSettings>
</configuration>

Retrieving single values from the config is done using these two functions:

function LoadConfigString ($configFile, $appSettingsKey) 
{
    $configXml = [xml](get-content $configFile)
    [string]$lookupValue = $($configXml.configuration.appSettings.add | where { $_.key -eq $appSettingsKey }).value 
    return $lookupValue 
}

function LoadConfigInt ($configFile, $appSettingsKey) 
{
    $configXml = [xml](get-content $configFile)
    [int]$lookupValue = $($configXml.configuration.appSettings.add | where { $_.key -eq $appSettingsKey }).value 
    return $lookupValue 
}
### Example calls to functions above ###
$slideWaitSeconds = LoadConfigInt $configFilename "SlideWaitSeconds"
$videoDirectory      = LoadConfigString $configFilename "VideoDirectory"

Below are my data validation routines.


Function Test-XMLFile ($xmlFilePath) 
{
    # Returns $true if file exists and is valid XML 

    # Check the file exists
    if (!(Test-Path -Path $xmlFilePath))
      {
         throw "$xmlFilePath is not valid. Please provide a valid path to the .xml file"
      }
	   
    # Check for Load or Parse errors when loading the XML file
    $xml = New-Object System.Xml.XmlDocument
    try 
      {
       $xml.Load((Get-ChildItem -Path $xmlFilePath).FullName)
       return $true
      }
    catch [System.Xml.XmlException] 
      {
	   #this message may help user debug or at least see the detailed issue of the file. 
       Write-Trace "$xmlFilePath : $($_.toString())" Y 
	   #TODO - why did line above have Write-Verbose, what does that do? 
       return $false
      }
}

function Is-Numeric-Int ($Value) 
{
    #use Regular Expression to verify if a string is a number 
    return $Value -match "^[\d]+$"
}

function Is-Email ($testEmail) 
{
    #use Regular Expression to verify if a string is an email 
    $EmailRegex = '^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$';
    if ($testEmail -match $EmailRegex) {
        return $true 
    }
    else {
        return $false 
    }
}

function IsXMLConfigFileDataValid
{

    param($configFilename = $(throw "You must specify a config file"))

    $intVarNames = @("MaxScanDetailRows","SlideWaitSeconds","LoggingLevel","MonitorStartX", 
                     "PostInitialWaitSeconds", "CustomKeywordPostWaitSeconds",
                     "PostMaxSeconds", "MaxWaitSecondsNewKeyword", "MaxReuseOfSameVideoStyle"
                    )
    $YNVarNames = @("YNPlayTuneOnChangeVideo")
    $OnOffVarNames = @("IsPostOnOff")
    $PathVarNames = @("VideoDirectory", "VideoDirectoryShort", "TemplateConfigDirectory", 
                      "TemplateDirectory")
    $PathVarNamesBlankOK = @("KeywordSourceFile", "UploadVideoDirectory", "UploadVideoMoveToDirectory") 
    $PathVarNamesWithoutFile = @("CSVHistoryFilename") 
    $templateNames = @("DesiredHotelTemplate","DesiredAppliancesTemplate","DesiredBATemplate")
    $emailVarNames = @("EmailRecipient","GmailUser")
    
    
    $global:appSettings = @{}
    $config = [xml](get-content $configFilename)
    foreach ($addNode in $config.configuration.appsettings.add) {
     if ($addNode.Value.Contains(',')) 
      {
      # Array case
      $value = $addNode.Value.Split(',')

      for ($i = 0; $i -lt $value.length; $i++) 
          {
            $value[$i] = $value[$i].Trim()
          }
     }
     else 
     {
      # Scalar case
      $value = $addNode.Value
     }
     $global:appSettings[$addNode.Key] = $value
    }
    #$global:appSettings
    #enumerate the hash table in code - maybe we want to write to trace? 
    $enum = "N" 
    if ($enum -eq "Y") 
        {
            foreach($key in $($global:appSettings.keys)){
                Write-Host "$key=$($global:appSettings[$key])"
        }
    }

    $IsValid = $true   #start by assuming valid, unless reset to $false below in any one of the tests 
    Write-Host "*** Starting Validation ***" 
    #enumerate the hash table in code and validate integers 
    foreach($key in $($global:appSettings.keys)){

        if ($intVarNames -contains $key) 
        {
           if (!(Is-Numeric-Int($global:appSettings[$key])))
               {
                   Write-Host "Variable must be an Numeric Integer: $key=$($global:appSettings[$key])"
                   $IsValid = $false 
               }
        }

        if ($YNVarNames -contains $key) 
        {
           if ($global:appSettings[$key] -ne "Y" -and $global:appSettings[$key] -ne "N")
               {
                   Write-Host "Variable must be have value of "Y" or "N": $key=$($global:appSettings[$key]) "
                   $IsValid = $false 
               }
        }

        if ($PathVarNames -contains $key) 
        {
           if (!(Test-Path $global:appSettings[$key]))  
               {
                   Write-Host "Path does not exist on your disk: $key=$($global:appSettings[$key]) "
                   $IsValid = $false 
               }
        }

        if ($PathVarNamesBlankOK -contains $key) 
        {
           if ($global:appSettings[$key] -ne "")
           {
               if (!(Test-Path $global:appSettings[$key]))  
                   {
                       Write-Host "Non-Blank value, and Path does not exist on your disk: $key=$($global:appSettings[$key]) "
                       $IsValid = $false 
                   }
           }
        }

        if ($PathVarNamesWithoutFile -contains $key) 
        {
           $testJustThePath = split-path $global:appSettings[$key]  #get just the pathname part 
           #$filename = split-path "C:\Docs\*.xls" -leaf    #this returns just the filename part 
           if (!(Test-Path $testJustThePath))  
               {
                   Write-Host "Path does not exist on your disk: $key=$($global:appSettings[$key]) (file does not have to exist, just the path: $testPath)"
                   $IsValid = $false 

               }
        }

        if ($OnOffVarNames -contains $key) 
        {
           if ($global:appSettings[$key] -ne "on" -and $global:appSettings[$key] -ne "off")
               {
                   Write-Host "Expected value of 'on' or 'off': $key=$($global:appSettings[$key]) "
                   $IsValid = $false 

               }
        }
        
        if ($emailVarNames -contains $key) 
        {
           if (!(Is-Email($global:appSettings[$key])))
               {
                   Write-Host "Not a valid email address: $key=$($global:appSettings[$key]) "
                   $IsValid = $false 

               }
        }

        if ($templateNames -contains $key) 
        {
           $templateDirectoryName = $global:appSettings["TemplateDirectory"]
           $testTemplateFilename = $templateDirectoryName + $global:appSettings[$key]   #get just the pathname part 
           $templateExtension = [System.IO.Path]::GetExtension($testTemplateFilename)
           #write-host "testTemplateFilename=$testTemplateFilename"
           #$filename = split-path "C:\Docs\*.xls" -leaf    #this returns just the filename part 
           if (!(Test-Path $testTemplateFilename))  
               {
                   Write-Host "Template filename does not exist on your disk: $key=$($global:appSettings[$key]) (in your TemplateDirectory: $templateDirectoryName)"
                   $IsValid = $false 
               }
           if ($templateExtension -ne ".prj") 
               {
                   Write-Host "File extension for template must be .prj: $key=$($global:appSettings[$key])"
                   $IsValid = $false 
               }
        }

    }

    ### special hard-coded validations ###
    if (!("Net40Net35Net20".Contains($global:appSettings["WatinDotNet"])))
       {
                   Write-Host "WatinDotNet must have value of: Net40, Net35, Net20 (depending of the version of .NET on your computer) $key=$($global:appSettings[$key])"
                   $IsValid = $false 
       }

    if ($global:appSettings["UploadKeywordType"] -ne "Customer" -and $global:appSettings["UploadKeywordType"] -ne "BA")
       {
                   Write-Host "UploadKeywordType must have value of 'Customer' or 'BA'  $key=$($global:appSettings[$key])"
                   $IsValid = $false 
       }

    if ($global:appSettings["DesiredKeywordType"] -ne "Customer" -and $global:appSettings["DesiredKeywordType"] -ne "BA")
       {
                   Write-Host "DesiredKeywordType must have value of 'Customer' or 'BA'  $key=$($global:appSettings[$key])"
                   $IsValid = $false 
       }


    ##### final logic #####
    if (!($IsValid))
       {
           Write-Host "***********************************************************************************" 
           Write-Host "**** Error VAL-01: Config file has errors, see above." 
           Write-Host "**** configFileName=$configFilename"
           Write-Host "***********************************************************************************" 
       }

    return $IsValid
} 

cls
$configFilename = "$($PSScriptRoot)\Data\Wukar_Config.xml"
$result1 = Test-XMLFile $configFilename 
Write-Host "Validation Result1 = $result1" 
if ($result1) 
   {
      $result2 = isXMLConfigFileDataValid $configFilename
      Write-Host "Validation Result2 = $result2" 
   }

The general idea is that in the documentation, I will advise the user to run the validation program before starting the application. The application itself will probably also run the validation and fail to start if any issues have. The idea is that it is better to fail immediately, then to run for 5 or 10 minutes, then fail later, only when some variable is used for the first time. My application is made to actually run 24 hours a day, creating hundreds of spin-off VideoMakerFX videos from a video template.

The downside of building this application in Powershell vs C# is that it becomes basically “Open Source”, the users can see all my code, and even share it with others. I will be adding a license key to the config file, but with the source code, that could be easily skipped over. Thus, I’m counting on honest customers who don’t know how to code. I may call a few C# routines here and there just to make hide a few features.

Here were some of my ideas on the above validation routine.

1. I categorized the config parms into categories: integers, paths, YN Variables, Emails, Templates, etc…
I then loop through all the Key= values in the app config file, looking to see if each config variable-name matches an array an array of one of the validation. If it is a match, that validation is done against the value of that key.
2. Some of the tricky parts were with the disk paths. Some of them I will allow to be blank, i.e. they are not always used.
Some require folder names only, and some require fully qualified file names.
3. I used two function Is-Numeric and Is-Email that do Regular Expression (Reg Ex) matches on the data.

Here is the video that describes what my application does:

http://Videos.WorkWithNeal.com

Intro to Oracle for SQLServer Developers and DBAs – Part 3

After finally getting the GUI “SQL Developer” tool installed, see earlier blog Intro to Oracle SQL for SQL Server Developers and DBAs, I one day noticed an Oracle Icon on my desktop, clicked it, and found the web interface for managing Oracle. I’ve seen in the trainings from Pluralsight that 12c has a much nicer web interface; but at least now I now there is for the 11g Express Edition (XE).

The URL on my machine is: http://127.0.0.1:8080/apex/f?p=4950:7:3175421414017644::NO. I haven’t yet figured out what all the codes are. If I just put http://127.0.0.1:8080 the browser prompts for userid/password (not on a web page) and then gets unauthorized. If just try http://127.0.0.1:8080/apex then I get a valid web page, that wants me to logon to a workspace.

Major Screens in the Oracle Web Interface

Oracle 11g Web Interface – Home Screen

Oracle_11g_XE_Web_Interface_1

Oracle Web Interface – Storage

Oracle_11g_XE_Web_Interface_2

Oracle 11g XE Web Management – Sessions

Oracle_11g_XE_Web_Interface_3

 

 Oracle 11g XE Web Interface – Parameters

Oracle_11g_XE_Web_Interface_4

 

This is what happened when I tried just the URL: http://127.0.0.1:8080/apex

Oracle_11g_XE_Web_Interface_6_Workspace

 

 

 

 

 

 

 

 

 

 

Regular expressions go all the way back to 1956. I think I first saw them in the PERL language; but today in 2015, they are very useful in Powershell, C# and most every language.

Regular Expressions have three main purposes:
1. Validate if text conforms to a pattern
2. Capture (extract) a string (or series of strings) from another string
3. Replace a pattern with a new text.

In this blog, I’ll be discussing #2 on the above list. Here is the function I created.

Function GetCityStateFromKeyword([String] $keyword)
{
#Write-Host “GetCityStateFromKeyword”
$pattern = “in (.*?)($|.?\d{5}?.?)”
#the first parenthese is for capturing the cityState into the $Matches array
#the second parentheses are needed above to look for $ (which is end of line)
#or zip code following the city/state
$isMatch = $keyword -match $pattern
$returnCityState = “ERROR”
#Write-Host “GetCityStateFromKeyword RegEx `$Matches.Count=$($Matches.Count)”
if ($Matches.Count -gt 0)
{
$returnCityState = $Matches[1]
}

return $returnCityState
}

First, let’s look at some of my sample data:

best deals on appliances in Irving TX 75039 
best deals on appliances in Irving TX 75039 bestbuy 
best deals on appliances in Irving TX
best deals on appliances in Irving TX 75039-1234 
$myKeyword = "best deals on appliances in Irving TX 75039"
$cityState = GetCityStateFromKeyword($myKeyword)
Write-Host "City/State=$cityState" 

My goal was to extract (or capture) the city, state from each of the above lines, or return the string “ERROR” if not found. The result I want in any of the above is simply:

Irving TX 

Now, let’s look in detail at my pattern.

   $pattern = "in (.*?)($|.?\d{5}?.?)"

Parentheses have two purposes in Reg Ex:
1. Specify what to capture
2. Wrap around a list of alternatives, which are delimited by the pipe symbol.

.* means 0 or more characters, and adding the question mark to make it .*? makes it “non-greedy”. If you don’t specify the non-greedy operator (the question mark), then the .* might “suck up” too much text. So, (.*?) will capture my city state.

What does ($|.?\d{5}?.?) mean? Well first, \d stands for digit, and \d{5} means exactly 5 digits, i.e. the zip code. The ? mark unfortunately has two meanings, one as the non-greedy operator, and when seen like this .?, it means 0 or more characters (as compared to .* which means 1 or more characters). So the zip-code is optional, the space before and after the zip-code s optional.

NOTE: My requirement was only for US postal zip codes. I would have to change the logic to handle Canadian zip codes, which I believe are 6 characters and contain alphas.

Now let’s look at this line of code:

 $isMatch = $keyword -match $pattern 

$isMatch will be a boolean. $keyword is my parameter to which the RegEx pattern will be applied. -Match is the Powershell keyword, and we’ve already discussed the value of the $pattern variable above. -Match will return an array in the variable $Match. Each capture from the () provided in the pattern will be stored in an element of the $Matches array. Thus, my city/state is stored in $Matches[1] (array in Powershell are 1-based, not 0-based).

Intro to Oracle for SQLServer Developers and DBAs

1. Oracle has something called V$ tables, which are essential similar to the Dynamic Management View (DMV) of SQLServer, Here are two examples:

select * from v$database;
select * from v$thread;

Result – this shows that my 11g Express Edition has both an instance name of “xe” and a database name of “XE”. See “Intro to Oracle SQL for SQLServer Developers and DBAs – Part 1” for an explanation of instances vs databases.

DBID NAME      CREATED   RESETLOGS_CHANGE# RESETLOGS_TIME
---------- --------- --------- ----------------- --------------
2749400775 XE        06-JAN-15            370965 06-JAN-15

THREAD# STATUS ENABLED      GROUPS INSTANCE
---------- ------ -------- ---------- --------------------------------------------------------------------------------
1 OPEN   PUBLIC            2 xe

2. Oracle has an odd “fake” table called “dual”.

Oracle always requires a table name, so this is the dummy “catch-all” table”  The links below will you the full scoop and history of why it was called “dual”.

select 5+5 from dual;
select sysdate into myDate from dual;

Additional explanation of the “Dual” table;

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388

http://stackoverflow.com/questions/73751/what-is-the-dual-table-in-oracle

3. The following example will illustrate many things I put together, from dozens of examples found in many different sources.

Sample Code
set serveroutput on
select 5+5 from dual;
select sysdate from dual;
declare
   myDate date;
   myNumber number;
   myString varchar2(50) := 'Initial Value';
begin
   select sysdate into myDate from dual;
   myNumber := 10;
   myNumber := myNumber + 8;
   myString := 'New Value';
   -- use this function/method to print
   DBMS_OUTPUT.put_line ('The date is ' || myDate);
   DBMS_OUTPUT.put_line ('myNumber is ' || myNumber);
   DBMS_OUTPUT.put_line ('myString is ' || myString);
end;
SQL Output
5+5
----------
10

SYSDATE
---------
09-JAN-15

anonymous block completed
The date is 09-JAN-15
myNumber is 18
myString is New Value
Dbms Output Window:
The date is 09-JAN-15
myNumber is 18
myString is New Value

1. “set serveroutput on” will bring the needed to use the DBMS_OUPUT_put_line into the SQL result window.  Otherwise, it will only show in the “Dbms Output” window.  “:DBMS_Output.put_line()” is basically the SQLServer Print command. Use || to concatenate, and to actually see the results of these ‘print’ statement, you may have to manually “view” and then “enable” the DBMS-Output window in the SQL “GUI” tool that you are using.

2. “from dual” see prior point above.

3.  declare/begin/end – is the structure – variables have local scope to the block in which they are declared, and these blocks can be nested.

4.  Semicolons to end the lines are very important!  This small sample gives you an idea of where they go, and where they don’t go.

5. “Number” is a data type  You could use “PLS_INTEGER” is you want an integer.  VarChar2(nn) is the standard string type.

6. := is the assignment operator, to set the value of something equal to another value

7.  Comments work the same between PL/SQL and Transact-SQL (T-SQL)

 

 

 

 

Intro to Oracle SQL for SQL Server Developers and DBAs

This is an Intro to Oracle SQL for SQLServer Developers and DBAs, and probably Part 1 of several blogs that I will write as I learn more, and make  more newbie mistakes.  You probably already know that Microsoft implementation (or extension) of the “standard” SQL is Tranact-SQL (T-SQL for short) and Oracle’s implementation is called PL/SQL.

1. The Server and the Clients

At the place I’m working we use PL/SQL Developer, a third party tool developed by “AllRoundAutomations”.  It wasn’t until a few weeks later that I learned that Oracle does have a client, which you download separately from the server.  Because I wanted to play with all the features of Oracle on my own safe environment (i.e. I probably don’t have privileges I need on clients main server, and I definitely don’t want to put any junk there), I installed Oracle 11g Express (it’s apparently the latest free version) on my own PC.   Once I had installed that, I was totally confused, because if this had been Microsoft SQL Server, the first thing I would do would be to create a database, add a table, stores some data, and run some queries.  Well, so far, that has taken me about three days to get there.

Install the server only gives you a command line SQL tool called “SQL Plus”, which is roughly the equivalent of Microsoft’s osql command line utility. I also had trouble getting it to connect.  More on that maybe in the future.

Oracle Client Issues

For some reason, I still cannot get “PL/SQL Developer” to connect to the XE database.  It wasn’t until several days later that I discovered the SQL client utilities and installed them.  The Oracle SQL client tool is called “SQL Developer”.  They had their own issues.  1) The icon in Win 7 startup/menu pointed to a non-existent .bat file, 2) I had to install the Java JDK for 32-bit windows to get it to run, 3) When I started it, it always was prompting for the Java.exe location.  First I learned that not any .Java.exe works, only the JDK/SDK version for 32-bit windows.  Then, it tries to store this path in the registery, so it bombs with access denied unless you do a “run as Administrator”.  Apparently this only needs to be done the first time.  4) After that I was able to create a table, but it was in the SYSTEM namespace, which was probably not a good idea.  (See below for how to solve that.)   I have to click this to run the tool: c:\Oracle11gClientApp\neal.walters\product\11.2.0\client_1\sqldeveloper\sqldeveloper\bin\sqldeveloper.exe  (since I specified c:\Oracle11gClientApp\neal.walters as the install path during the install).  I don’t understand why this would be specific to one user.

Choose your client

These are some of the popular clients.  Again, Oracle’s client is called “SQL Developer”.  There is TOAD, PL/SQL Developer, SQL Navigator, formerly by Quest, now owned by Dell, and an open source one called SQuirreL SQL.

Using Oracle’s “SQL Developer” is very similar to using Microsoft’s SSMS (SQL Server Management Studio).  Each is a client tool that you install.  The Oracle one is of course dependent on Java.  Similarly, Microsoft allows you to call “CLR” (Common Language RunTime from SQL), Oracle allows you to call Java from their SQL.

2. What is a Database & Instance?

So far, my learning has told me that a Oracle SQL Instance and an Oracle database are identical.  The default database and instance installed with Oracle 11g express is called “XE”.  Hopefully, I will soon learn more about this.  As Microsoft SQL users, we often used to having several databases on the same SQL Server Instance.  (By the way, Oracle 12c allows for that concept, with their “new” concept called pluggable databases.  You can plug additional databases into the initial instance/database.)

So how do large shops manage this?  My guess is that Oracle is more reliant on the Schema to separate types of data.  This feature exists in Microsoft SQL Server, and used by different extents in the companies that I’ve worked at.  Some SQL Users throw everything into one big schema, others try to group data.

As mentioned above, the first table I added went to the System Namespace. When I first opened “SQL Developer” I logged on with username=SYS and my password that I setup during the install.  After all, that’s the only user I had at this time.  Anxious as I was to create a table, I found “tables” and did “right-click” then “New table” much like would be done in MS SQL Studio Management Studio.

Oracle_SQLDeveloper_CreateTable_1

In the screen shot above, notice all the System tables on the left.  There are over 1680 of them.  You can do “Select * from dba_tables” if you want to get a count. Then notice in the red box, the first table I created, I went ahead and stored in this SYS schema, just to make sure any simple create would work, and it did.

There were other schema to choose from the list.   I have yet to learn why so many default schemas (including one called “HR” which already had some tables in it).

Oracle_SQLDeveloper_CreateTable_2

So then later, I forged out to try to figure out how to create another schema.  Well, it turns out that schemas are apparently users.  So I created an HR user, so I could create an test “Employee” table in the HR schema/namespace. I then created a user called “Neal”, closed “SQL Developer” (as I didn’t know how to switch users yet inside of it), then logged on as user=Neal.

 

In future blogs, I probably be able to explain more about multiple schema, users, profiles and granting security.  I logged on to Neal, created my table, and from now on when I logon as “Neal” – I only see “my stuff” in “SQL Developer”:

Oracle_SQLDeveloper_CreateTable_3

 

Oracle_SQLDeveloper_CreateTable_4Interestingly enough, when you expand the users, you can see the tables tied to each user (see picture to the left).

I did a select of the Employees in the HR schema/user, and this is what I saw (below):

Oracle_SQLDeveloper_HR_User

The only plausible conclusion is that this is the “Demo” database, something like the “Pubs” database or “Northwind” database in Microsoft-land.  Only it’s not a database, it’s a user/schema within the bigger XE database.

I later found out that my version of “SQL Developer” is 1.5.5 and heard that was about 8 years old.  Not sure why downloading the latest client gave me such an old version.  Here’s the latest and greatest (4.0.3 or 4.1 for early adopters), apparently you can download SQL Developer without the entire client: http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html. The surprise in this download, is that it did not include an installer program, it was just a zip file of the raw files; so I guess you just pick whatever directory your prefer and unzip it there.  It seemed to work great. The newer version has a place on the “Tool/Preferences/Advanced” options to set the location (directory path) containing the desired TNSNames.ora file.

 

 

3. The magical and all important file: TNSNAMES.ORA

I will save most everything else for future blogs, but there is one more critical thing I learned.  One way to connect to a database (especially on another machine), is by means of the “tsnames.ora” file.  This file is found in your $ORACLE_HOME directory under network/Admin, such as c:\oracle\ora81\network\ADMIN\TNSNAMES.ORA  (the first one) or c:\Oracle11gClientApp\neal.walters\product\11.2.0\client_1\network\admin\sample\tnsnames.oRA (the second one, created with install of the Oracle Client).

<pre>
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = MyPCName.myDomain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
</pre>

Note that 1521 is the default port for Oracle database.  There is a listener utility that runs, and could be changed to a different port number.

Oracle_PLSQLDeveloper_LogonYes, you can apparently have multiples of these (TNSNAMES.ORA) on your machine, and that’s one of the things I’m trying to sort out.  Apparently on my PC, “PL/SQL Developer” is using the first one.  I can prove this by copying the above text, and adding XEDUMMY1 to it.  My client also has all their databases in this tsnames.ora file.  I then see XE and XEDummy on the drop-down list of Databases.  I’m not sure databases are discoverable on their own, or if you must define the tnsnames.ora file.

In theory, the ORACLE_HOME is supposed to point to the one being used. There does not appear to be any environment variable called ORACLE_HOME.

RegEdit_OracleHome

 

 

 

 

 

 

 

 

 

 

 

 

This would imply that I have a third file: c:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora  on my disk, and sure enough, it does exist as well.

4. Shocking surprises.

1. Until Oracle 12c, it didn’t support AUTO_INCREMENT identity columns.

StackOverflow -How to create id with AUTO_INCREMENT on Oracle?   – and  New Oracle 12c Identity Columns

2. The varchar2 string data type in Oracle cannot distinguish between a NULL and an empty string (i.e. empty strings are treated as null)

StackOverflow – Oracle not distinguishing between nulls and empty strings?

 

 

 

5. Oracle Release Names

Believe it or not, those little letters after the release are release numbers, they are mnemonics (or abbreviations) for major focus of that release.
And here is what they stand for:

8i = Internet – from 1999
9i = internet – from 2001
10g = grid      – from 2003
11g = grid      – from 2007
12c = cloud   – from 2013

 

The following keywords may also help you and others find this article be found:
Microsoft SQL vs Oracle SQL, T-SQL VS PL/SQL, MSSQL vs Oracle SQL.

This blog shows you how to How to Add and Delete MSMQ Queues from Powershell, programmatically, via a simple re-usable function.

I used the code from this blog as a base: http://blogs.msdn.com/b/sajay/archive/2010/03/18/powershell-script-to-create-an-msmq.aspx. His code is fine, but it’s from an administrators perspective, i.e. run this Powershell, pass it some parms. I wanted the same code to be callable as a function that could be called from other Powershell code.  Another source of similar information is here: http://blogs.msdn.com/b/biztalknotes/archive/2013/08/19/powershell-scripts-to-manage-msmq.aspx

In earlier blogs, I demonstrated How to Write to MSMQ from Powershell and How to Read MSMQ from Powershell. Those dealt with read and writing individual queue messages from an existing queue in MSMQ. In this blog, the topic is creating and removing the entire queue in MSMQ.

function CreateMSMQQueue($queuename, $YNPrivate, $YNTransactional, $user, $permission) 
{

    if ($permission -ne "all" -and $permission -ne "restricted") 
        {
           Write-Host "Error: fifth parameter "permission" must have value 'all' or 'restricted'" 
           exit 
        }

    [Reflection.Assembly]::LoadWithPartialName("System.Messaging")


    if ($YNPrivate -ieq "Y")
        { 
          $queuename = ".\private$\" + $queuename 
        }

    if ($YNTransactional -ieq "Y")
        {
            $transactional = 1
            Write-Host "Creating a transactional queue."
        }
    else
        {
            $transactional = 0
            Write-Host "Creating a non-trasactional queue"
        }
    
    $msgQueue = [System.Messaging.MessageQueue]::Create($queuename, $transactional) 
    
    if($msgQueue -eq $null)
        {
            Write-Host "Error: got a 'null' back from the Create MSMQ function" 
            exit
        }
    
    $msgQueue.label = $queuename
           
    if ($permission -ieq "all")
        {
            Write-Host "Granting all permissions to "  $User
            $msgQueue.SetPermissions($User, [System.Messaging.MessageQueueAccessRights]::FullControl, [System.Messaging.AccessControlEntryType]::Allow) 
        }
    else
        {
            Write-Host "Restricted Control for user: "  $User
            Write-Host ""
            $msgQueue.SetPermissions($User, [System.Messaging.MessageQueueAccessRights]::DeleteMessage, [System.Messaging.AccessControlEntryType]::Set) 
            $msgQueue.SetPermissions($User, [System.Messaging.MessageQueueAccessRights]::GenericWrite, [System.Messaging.AccessControlEntryType]::Allow) 
            $msgQueue.SetPermissions($User, [System.Messaging.MessageQueueAccessRights]::PeekMessage, [System.Messaging.AccessControlEntryType]::Allow) 
            $msgQueue.SetPermissions($User, [System.Messaging.MessageQueueAccessRights]::ReceiveJournalMessage, [System.Messaging.AccessControlEntryType]::Allow)
        }
}


function DeleteMSMQQueue($queuename, $YNPrivate) 
{
    Write-Host "DeleteMSMQQUEUE QueueName: " + $queuename
    if ($YNPrivate -ieq "Y")
    { 
      $queuename = ".\private$\" + $queuename 
    }
    Write-Host "Delete Queue: " + $queuename
    [System.Messaging.MessageQueue]::Delete($queuename)
}


cls
$myQueueName = "NealTestPSMSMQ1" 
$ynIsTransactional = "Y" 
$ynIsPrivate = "Y" 
$user ="Administrator" 
$permission = "all" 
CreateMSMQQueue $myQueueName $ynIsPrivate $ynIsTransactional $user $permission 

#use the following instead of Create to Delete the Queue
#DeleteMSMQQueue $myQueueName $ynIsPrivate 

Results

To view the results, open “Server Manager”, expand “Features” and “Message Queueing”, and look for the queue just created. In my example above, the queue name was ‘nealtestpsmsmq1’.
ServerManager_Features_MessageQueueing_Results

 

To read or write individual messages to that queue, see previous blogs (links at top of this article).

One potential enhancement to the above code might be to pass an array or collection of userids and desired privileges to the function, or to create a third function to manage the security privileges for the queue.

In summary, this article has shown how to create and remove a MSMQ queue using Powershell.

 

How to Get the Last Line of a File

Have you ever needed to read just the last line of a file (i.e. “the last row”) in Powershell? It’s easier than I thought. If you ask for the last several rows (as shown in the commented-out code in the example below, you will get back an array of rows. If you get just the last row, you will get it in a string variable.

$myFileName = "c:\Users\neal.walters\Documents\test.csv" 

#Example of how to get the last 3 rows 
#$LastThreeRowsArray = (Get-Content $myFileName)[-1 .. -3]

# Get last one row of file into variable 
$lastDataRow = (Get-Content $myFileName)[-1]

#parse the CSV data back into separate variables, one for each column 
$dataArray = $lastDataRow.Split(",") 
$lastRowDate = [DateTime] $dataArray[0]  #convert back to DateTime datatype 
$lastRowStatus = $dataArray[1] 
$lastRowAction = $dataArray[2] 
$lastRowKeyword = $dataArray[3] 

Write-Host "`$lastDataRow=$lastDataRow" 
Write-Host "Date=$lastRowDate" 
Write-Host "Status=$lastRowStatus" 
Write-Host "Action=$lastRowAction"
Write-Host "Keyword=$lastRowKeyword"

$currentDateTime = get-date

$dateTimeDiff = $currentDateTime  - $lastRowDate 
$minutesDiff = $dateTimeDiff.Minutes 
$totalMinutesDiff = $dateTimeDiff.TotalMinutes 
Write-Host "`$currentDateTime =$currentDateTime"
Write-Host "`$dateTimeDiff=$dateTimeDiff"
Write-Host "`$minutesDiff=$minutesDiff "
Write-Host "`$totalMinutesDiff=$totalMinutesDiff "

$maxAcceptableLapseMinutes = 15 
if ($totalMinutesDiff -gt $maxAcceptableLapseMinutes) 
   {
       Write-Host "No video created for more than $maxAcceptableLapseMinutes minutes!" 
       # do so logic here to handle that issue 
   }

Test Data File:

1/5/2015 8:51,Success,CreatedVideo,best appliances in Abilene TX
1/5/2015 8:51,Success,CreatedVideo,best appliances in Irving TX
1/5/2015 8:51,Failure,CreatedVideo,best appliances in Farmers Branch TX
1/5/2015 8:52,Success,CreatedVideo,best appliances in Abilene TX
1/5/2015 8:52,Success,CreatedVideo,best appliances in Boston MA
1/5/2015 8:52,Success,CreatedVideo,best appliances in West Newton MA 
1/5/2015 8:52,Success,CreatedVideo,best appliances in Boca Raton FL 

Results

$lastDataRow=1/5/2015 8:52,Success,CreatedVideo,best appliances in Boca Raton FL 
Date=01/05/2015 08:52:00
Status=Success
Action=CreatedVideo
Keyword=best appliances in Boca Raton FL 
$currentDateTime =01/05/2015 15:17:51
$dateTimeDiff=06:25:51.5232317
$minutesDiff=25 
$totalMinutesDiff=385.858720528333 
No video created for more than 15 minutes

Why would anyone want to get the last row of a file? I have a process that creates an mp4 video about every 1 to 1.5 minutes. When you do “Add-Content”, the data is written to the end of the file. (I show how I write data to this file in a previous blog: Logging Data Variables to a CSV File in Powershell) I want to write a separate process to monitor the progress by looking at the last row of the file. If no video is created for example, in 10 minutes, I know something has frozen up, and might need to kill and restart the process that creates the videos.

So the above code also shows how to compute the elapsed time, of the time the program runs to the last date in the CSV file. I ran the program at 3:17 pm, and the last date time in the CSV file was 8:52 this morning. Thus 6 hours, 25 minutes, and some-odd seconds has elapsed.

The above code also illustrates the why you might want to use the .TotalMinutes property instead of the .Minutes property. I want to know if more than 15 minutes have elapsed. Let’s suppose the elapse time was 1 hour and 7 minutes. I cannot just compare the 7 minutes to 15, I need to compare the 60+7 (or 67 minutes) to 15.

I found the base code for this here: http://vstepic.blogspot.com/2013/02/how-to-convert-string-to-base64-and.html. If you want to know more about Base64, check out this
Wikipedia Entry

But I’m not a command-line guy, I’m a developer, and I like to have my code in a file where I can edit it, modify it, re-use it, and run it.

Function Base64Encode($textIn) 
{
    $b  = [System.Text.Encoding]::UTF8.GetBytes("Hello World")
    $encoded = [System.Convert]::ToBase64String($b)
    return $encoded    
}

Function Base64Decode($textBase64In) 
{
    $b  = [System.Convert]::FromBase64String($textBase64In)
    $decoded = [System.Text.Encoding]::UTF8.GetString($b)
    return $decoded
}

#Test Logic 
$base64Encoded = Base64Encode("Hello World") 
Write-Host "`$base64Encoded=$base64Encoded"

$base64Decoded = Base64Decode($base64Encoded) 
Write-Host "`$base64Decoded=$base64Decoded"

Here is the results of running the above code:

What is your password?: Hello World
$base64Encoded=SGVsbG8gV29ybGQ=
$base64Decoded=Hello World

Now, how can we use this? Let’s take the original code, comment out everything below the “#Test Logic” comment, and save it as “Base64Functions.ps1”.

I wanted to obfuscate a simple password in a config file. Encoding is definitely NOT encryption. But if someone is standing over your shoulder, or even opens the Config File – it will not be obvious that the password is Base64 and they certainly couldn’t convert it in their head or even memorize it.

I found samples for how to update a standard XML Config File at this link: http://blogs.msdn.com/b/sonam_rastogi_blogs/archive/2014/08/18/update-configuration-files-using-powershell.aspx

XML Config File Before:

 

XML Config File After:

. \Base64Functions.ps1  #reference the function created earlier 
cls

#prompt the user to interactively enter his/her password 
$password = Read-Host 'What is your password?'   

#encode it
$base64EncodedPassword = Base64Encode($password)
Write-Host "`$base64EncodedPassword=$base64EncodedPassword"

#store it in our config file in the proper place 
$configFilename = "c:\Users\neal.walters\Documents\GmailConfig.xml"
$xmlConfig = [xml](get-content $configFilename)

$obj = $xmlConfig.configuration.appSettings.add| where {$_.Key -eq 'GmailPassword'}
$obj.Value = $base64EncodedPassword

#we change the xml in memory, so now write it back to the disk file. 
$xmlConfig.Save($configFilename)

Here is the results of running the above code (I typed in “Hello World” in response to the prompt.)

What is your password?: Hello World
$base64Encoded=SGVsbG8gV29ybGQ=
$base64Decoded=Hello World

XML Config File After:


In a future blog, I can show you how I use this to send GMail (Google emails) from Powershell.