Windows 10 | MacLochlainns Weblog

Archive for the ‘Windows 10’ Category

Record Type Arrays

Another question that I was asked today: “Can you create an array of a record type in PL/pgSQL?” The answer is yes.

You first have to create a type, which is what you do when you want to create a table with an embedded table. This is a simple full_name record type:

CREATE TYPE full_name AS
( first_name   VARCHAR(20)
, middle_name  VARCHAR(20)
, last_name    VARCHAR(20));

The following DO block shows you how to create a record type array and then print it’s contents in a FOR-LOOP:

DO
$$
DECLARE
  -- An array of full_name records.
  list  full_name[] = 
          array[('Harry','James','Potter')
               ,('Ginevra','Molly','Potter')
               ,('James','Sirius','Potter')
               ,('Albus','Severus','Potter')
               ,('Lily','Luna','Potter')];
BEGIN
  -- Loop through the integers.
  FOR i IN 1..CARDINALITY(list) LOOP
    RAISE NOTICE '%, % %', list[i].last_name, list[i].first_name, list[i].middle_name;
  END LOOP;
END;
$$;

Since you typically only have a single dimension array with record-type structure, using CARDINALITY is clearer than ARRAY_LENGTH(list,1). If you don’t agree use the latter.

It prints the following:

NOTICE:  Potter, Harry James
NOTICE:  Potter, Ginevra Molly
NOTICE:  Potter, James Sirius
NOTICE:  Potter, Albus Severus
NOTICE:  Potter, Lily Luna
DO

As always, I hope this helps those looking for a solution to this type of problem.

Written by maclochlainn

April 27th, 2022 at 10:27 pm

Posted in Fedora,Linux,Microsoft Windows 10,Microsoft Windows 11,PL/pgSQL,Postgres,PostgreSQL,PostgreSQL 14,Unix,Windows 10,Windows 11

Tagged with PostgreSQL DBA, PostgreSQL Developer

Picking up where I left off on yesterday’s post on PostgreSQL arrays, you can also write multidimensional arrays provided all the nested arrays are equal in size. You can’t use the CARDINALITY function to determine the length of nested arrays, you must use the ARRAY_LENGTH to determine the length of subordinate arrays.

Here’s an example file with a multidimensional array of integers:

DO
$$
DECLARE
  /* Declare an array of integers with a subordinate array of integers. */
  list  int[][] = array[array[1,2,3,4]
                       ,array[1,2,3,4]
                       ,array[1,2,3,4]
                       ,array[1,2,3,4]
                       ,array[1,2,3,4]];
  row   varchar(20) = '';
BEGIN
  /* Loop through the first dimension of integers. */
  <<Outer>>
  FOR i IN 1..ARRAY_LENGTH(list,1) LOOP
    row = '';
    /* Loop through the second dimension of integers. */
    <<Inner>>
    FOR j IN 1..ARRAY_LENGTH(list,2) LOOP
      IF LENGTH(row) = 0 THEN
        row = row || list[i][j];
      ELSE
        row = row || ',' || list[i][j];
      END IF;
    END LOOP;
    /* Exit outer loop. */
    RAISE NOTICE 'Row [%][%]', i, row;
  END LOOP;
END;
$$;

It prints:

NOTICE:  Row [1][1,2,3,4]
NOTICE:  Row [2][1,2,3,4]
NOTICE:  Row [3][1,2,3,4]
NOTICE:  Row [4][1,2,3,4]
NOTICE:  Row [5][1,2,3,4]
DO

Multidimensional arrays are unique to PostgreSQL but you can have nested lists of tables or varrays inside an Oracle database. Oracle also supports nested lists that are asynchronous.

As always, I hope this helps those trying sort out the syntax.

Written by maclochlainn

April 27th, 2022 at 10:01 pm

Posted in Linux,Mac OS X,Mac Pro,Microsoft,Microsoft Windows 10,Microsoft Windows 11,PostgreSQL,PostgreSQL 11,PostgreSQL 14,psql,Windows 10,Windows 11,Windows OS

Tagged with PostgreSQL DBA, PostgreSQL Developer

Parameter Validation

without comments

I was trying to explain how to validate PowerShell script parameters to my students when I found the number and quality of enum data type examples was woefully inadequate. Here’s a series of examples that show you how to validate input parameters against an enum list of values.

The basic concept requires you to validate an element in an enum type. It uses a literal value

# Create a enum type.
Add-Type -TypeDefinition @"
  public enum msgTypes
    { moe, larry, curly }
"@
 
# Check whether the value is found in the enum type.
if ([enum]::isDefined(([msgTypes]), [msgTypes]::moe)) {
  Write-Host "Success"
}

You test this testEnum1.ps1 script file with this syntax:

powershell testEnum1.ps1

It prints:

Success

The next sample embeds the validation in a local function:

# Create a enum type.
Add-Type -TypeDefinition @"
  public enum msgTypes
    { moe, larry, curly }
"@
 
# A local function for verbose reporting.
function Print-Message ($msg) {
  # Check whether the value is found in the enum type.
  if ([enum]::isDefined(([msgTypes]), [msgTypes]::$msg)) {
    Write-Host "Success"
  }
}
 
# Call the function with a literal value.
Print-Message "moe"

You test this testEnum2.ps1 script file with this syntax:

powershell testEnum2.ps1

It also prints:

Success

The next sample testEnum3.ps1 accepts a parameter and passes it to the validation function:

# Create a enum type.
Add-Type -TypeDefinition @"
  public enum msgTypes
    { moe, larry, curly }
"@
 
# A local function for verbose reporting.
function Print-Message ($msg) {
  # Check whether the value is found in the enum type.
  if ([enum]::isDefined(([msgTypes]), [msgTypes]::$msg)) {
    Write-Host "Success"
  }
}
 
# Wrap the Parameter call to avoid a type casting warning.
try {
  param (
    [Parameter(Mandatory)][hashtable]$args
  )
}
catch {}
 
# Call the function with a literal value.
Print-Message "moe"

You test this testEnum.ps1 script file with this syntax:

powershell testEnum3.ps1 moe

It also prints:

Success

However, if you don’t pass a parameter to the testEnum3.ps1, like this

powershell testEnum3.ps1

It raises the following error:

Exception calling "IsDefined" with "2" argument(s): "Value cannot be null.
Parameter name: value"
At C:\Data\cit225\mysql\test\testEnum3.ps1:9 char:7
+   if ([enum]::isDefined(([msgTypes]), [msgTypes]::$msg)) {
+       ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentNullException

Rewriting the Print-Message function in the script may appear to fix the problem. At least, a missing parameter won’t cause an error when you wrap the call to the isDefined method inside an if-statement.

Here’s how that wrapping attempt would look:

# A local function for verbose reporting.
function Print-Message ($msg) {
  if (!($msg -eq $null)) {
    if ([enum]::isDefined(([msgTypes]), [msgTypes]::$msg)) {
      Write-Host "Success"
    }
  }
  else {
  Write-Host "Failure" }
}

While the prior change to the Print-Message function manages the lack of a parameter, it doesn’t prevent a failure when you pass an incorrect parameter. A new variation of the old error occurs when you pass a parameter that is not a member of the enum type, like

powershell testEnum4.ps1 Shem

It now prints:

Failure

So, you need to complete another step. Our woeful tale of parameter validation against a set of possible enum values isn’t quite complete. That’s because any incorrect parameter value raises a null value when isDefined method can’t find a valid value in the enum type. This standard behavior means that the isDefined method returns this error message:

Exception calling "IsDefined" with "2" argument(s): "Value cannot be null.
Parameter name: value"
At C:\Data\cit225\mysql\test\testEnum4.ps1:10 char:9
+     if ([enum]::isDefined(([msgTypes]), [msgTypes]::$msg)) {
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentNullException

After all the effort to sort through how PowerShell handles the isDefined method and navigating Microsoft’s limited PowerShell examples, we’ve travelled down a rabbit hole. The problem is that the isDefined method isn’t terribly useful.

You need to use another getValues method, which returns the set of member values from the enum type. This requires you to write a new function. Find-EnumMatch seems an appropriate Pascal-like name for that function because that’s what it will do. Writing the new function also simplifies the Print-Message function.

# Create a enum type for statements, for future use.
Add-Type -TypeDefinition @"
  public enum msgTypes
    { moe, larry, curly }
"@
 
# A local function to find a valid enum member.
function Find-EnumMatch ($typeof, $member) {
  # Set default return value.
  $evaluated = $false
 
  # Check for a not null match to an enum member.
  if (!($msg -eq $null)) {  
    foreach ($msgValue in $msgTypes = [enum]::getValues(([enum]::'[' + $typeof + ']'))) {
      if ($msgValue -eq $member) {
 	    $evaluated = $true
 	    break }
    }
  }
  # Return whether true or false.
  return $evaluated
}   
 
# A local function for verbose reporting.
function Print-Message ($msg) {
  # Check for a not null match to an enum member.
  if (find-enumMatch $msg) { Write-Host "Success" }
  else { Write-Host "Failure" }
}
 
# Wrap the Parameter call to avoid a type casting warning.
try {
  param (
    [Parameter(Mandatory)][hashtable]$args
  )
}
catch {}

Now, if we test the program with a valid, invalid, or null value parameter it works as expected. It prints “Success” when the parameter is found as a member of the enum type, and prints “Failure” when the parameter is null or not found as a member of the enum type. It also never raises an unhandled exception.

There’s an important explicit casting trick required on line #14 to avoid the following error:

Cannot convert argument "enumType", with value: "[msgTypes]", for "GetValues" to type "System.Type": "Cannot convert the "[msgTypes]" value of
type "System.String" to type "System.Type"."
At C:\Data\cit225\mysql\test\testEnum7.ps1:14 char:27
+ ... ($msgValue in $msgTypes = [enum]::getValues(('[' + $typeof + ']'))) {
+                   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument

While many developers might think that you can write the line without explicitly casting the enum name enclosed by square brackets, you actually have to explicitly cast it.

As always, I hope this helps those looking for an arcane bit of knowledge. While handling parameters is routine, it sure appears the method for doing so in PowerShell with an enum type isn’t quite well documented.

Written by maclochlainn

April 24th, 2022 at 1:17 am

Posted in Microsoft,Microsoft Windows 10,Microsoft Windows 11,PowerShell,Windows 10,Windows 11,Windows OS

Tagged with PowerShell Developer, Windows Administrator

Oracle ODBC DSN

without comments

As I move forward with trying to build an easy to use framework for data analysts who use multiple database backends and work on Windows OS, here’s a complete script that lets you run any query stored in a file to return a CSV file. It makes the assumption that you opted to put the user ID and password in the Windows ODBC DSN, and only provides the ODBC DSN name to make the connection to the ODBC library and database.

# A local function for verbose reporting.
function Get-Message ($param, $value = $null) {
  if (!($value)) {
    Write-Host "Evaluate swtich    [" $param "]" } 	  
  else {
    Write-Host "Evaluate parameter [" $param "] and [" $value "]" } 
}
 
# Read SQLStatement file and minimally parse it.
function Get-SQLStatement ($sqlStatement) {
  # Set localvariable for return string value.
  $statement = ""
 
  # Read a file line-by-line.
  foreach ($line in Get-Content $sqlStatement) {
    # Use regular expression to replace multiple whitespace.
    $line = $line -replace '\s+', ' '
 
    # Add a whitespace to avoid joining keywords from different lines;
    # and remove trailing semicolons which are unneeded.
    if (!($line.endswith(";"))) {
      $statement += $line + " " }
    else {
      $statement += $line.trimend(";") }
  }
  # Returned minimally parsed statement.
  return $statement
}
 
# Set default type of SQL statement value to a query.
$stmt = "select"
 
# Set a variable to hold a SQL statement from a file.
$query = ""
 
# Set default values for SQL input and output files.
$outFile = "output.csv"
$sqlFile = "query.sql"
 
# Set default path to: %USERPROFILE%\AppData\Local\Temp folder, but ir 
# the tilde (~) in lieu of the %USERPROFILE% environment variable value.
$path = "~\AppData\Local\Temp"
 
# Set a verbose switch.
$verbose = $false
 
# Wrap the Parameter call to avoid a type casting warning.
try {
  param (
    [Parameter(Mandatory)][hashtable]$args
  )
}
catch {}
 
# Check for switches and parameters with arguments.
for ($i = 0; $i -lt $args.count; $i += 1) {
  if (($args[$i].startswith("-")) -and ($args[$i + 1].startswith("-"))) {
    if ($args[$i] = "-v") {
      $verbose = $true }
      # Print to verbose console.
    if ($verbose) { Get-Message $args[$i] }}
  elseif ($args[$i].startswith("-")) {
    # Print to verbose console.
    if ($verbose) { Get-Message $args[$i] $args[$i + 1] }
 
    # Evaluate and take action on parameters and values.
    if ($args[$i] -eq "-o") {
      $outfile = $args[$i + 1] }
    elseif ($args[$i] -eq "-q") {
      $sqlFile = $args[$i + 1] }
    elseif ($args[$i] -eq "-p") {
      $path = $args[$i + 1] }
  }
}
 
# Set a PowerShell Virtual Drive.
New-PSDrive -Name folder -PSProvider FileSystem -Description 'Forder Location' `
            -Root $path | Out-Null
 
# Remove the file only when it exists.
if (Test-Path folder:$outFile) {
  Remove-Item -Path folder:$outFile }
 
# Read SQL file into minimally parsed string.
if (Test-Path folder:$sqlFile) {
  $query = Get-SQLStatement $sqlFile }
 
# Set a ODBC DSN connection string.
$ConnectionString = 'DSN=OracleGeneric'
 
# Set an Oracle Command Object for a query.
$Connection = New-Object System.Data.Odbc.OdbcConnection;
$Connection.ConnectionString = $ConnectionString
 
# Attempt connection.
try {
  $Connection.Open()
 
  # Create a SQL command.
  $Command = $Connection.CreateCommand();
  $Command.CommandText = $query;
 
  # Attempt to read SQL command.
  try {
    $row = $Command.ExecuteReader();
 
    # Read while records are found.
    while ($row.Read()) {
      # Initialize output for each row.
      $output = ""
 
      # Navigate across all columns (only two in this example).
      for ($column = 0; $column -lt $row.FieldCount; $column += 1) {
        # Mechanic for comma-delimit between last and first name.  
        if ($output.length -eq 0) { 
          $output += $row[$column] }
        else {
          $output += ", " + $row[$column] }
      }
      # Write the output from the database to a file.
      Add-Content -Value $output -Path folder:$outFile
    }
  } catch {
    Write-Error "Message: $($_.Exception.Message)"
    Write-Error "StackTrace: $($_.Exception.StackTrace)"
    Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
  } finally {
    # Close the reader.
    $row.Close() }
} catch {
  Write-Error "Message: $($_.Exception.Message)"
  Write-Error "StackTrace: $($_.Exception.StackTrace)"
  Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
} finally {
  $Connection.Close() }

# A local function for verbose reporting. function Get-Message ($param, $value = $null) { if (!($value)) { Write-Host "Evaluate swtich [" $param "]" } else { Write-Host "Evaluate parameter [" $param "] and [" $value "]" } } # Read SQLStatement file and minimally parse it. function Get-SQLStatement ($sqlStatement) { # Set localvariable for return string value. $statement = "" # Read a file line-by-line. foreach ($line in Get-Content $sqlStatement) { # Use regular expression to replace multiple whitespace. $line = $line -replace '\s+', ' ' # Add a whitespace to avoid joining keywords from different lines; # and remove trailing semicolons which are unneeded. if (!($line.endswith(";"))) { $statement += $line + " " } else { $statement += $line.trimend(";") } } # Returned minimally parsed statement. return $statement } # Set default type of SQL statement value to a query. $stmt = "select" # Set a variable to hold a SQL statement from a file. $query = "" # Set default values for SQL input and output files. $outFile = "output.csv" $sqlFile = "query.sql" # Set default path to: %USERPROFILE%\AppData\Local\Temp folder, but ir # the tilde (~) in lieu of the %USERPROFILE% environment variable value. $path = "~\AppData\Local\Temp" # Set a verbose switch. $verbose = $false # Wrap the Parameter call to avoid a type casting warning. try { param ( [Parameter(Mandatory)][hashtable]$args ) } catch {} # Check for switches and parameters with arguments. for ($i = 0; $i -lt $args.count; $i += 1) { if (($args[$i].startswith("-")) -and ($args[$i + 1].startswith("-"))) { if ($args[$i] = "-v") { $verbose = $true } # Print to verbose console. if ($verbose) { Get-Message $args[$i] }} elseif ($args[$i].startswith("-")) { # Print to verbose console. if ($verbose) { Get-Message $args[$i] $args[$i + 1] } # Evaluate and take action on parameters and values. if ($args[$i] -eq "-o") { $outfile = $args[$i + 1] } elseif ($args[$i] -eq "-q") { $sqlFile = $args[$i + 1] } elseif ($args[$i] -eq "-p") { $path = $args[$i + 1] } } } # Set a PowerShell Virtual Drive. New-PSDrive -Name folder -PSProvider FileSystem -Description 'Forder Location' ` -Root $path | Out-Null # Remove the file only when it exists. if (Test-Path folder:$outFile) { Remove-Item -Path folder:$outFile } # Read SQL file into minimally parsed string. if (Test-Path folder:$sqlFile) { $query = Get-SQLStatement $sqlFile } # Set a ODBC DSN connection string. $ConnectionString = 'DSN=OracleGeneric' # Set an Oracle Command Object for a query. $Connection = New-Object System.Data.Odbc.OdbcConnection; $Connection.ConnectionString = $ConnectionString # Attempt connection. try { $Connection.Open() # Create a SQL command. $Command = $Connection.CreateCommand(); $Command.CommandText = $query; # Attempt to read SQL command. try { $row = $Command.ExecuteReader(); # Read while records are found. while ($row.Read()) { # Initialize output for each row. $output = "" # Navigate across all columns (only two in this example). for ($column = 0; $column -lt $row.FieldCount; $column += 1) { # Mechanic for comma-delimit between last and first name. if ($output.length -eq 0) { $output += $row[$column] } else { $output += ", " + $row[$column] } } # Write the output from the database to a file. Add-Content -Value $output -Path folder:$outFile } } catch { Write-Error "Message: $($_.Exception.Message)" Write-Error "StackTrace: $($_.Exception.StackTrace)" Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)" } finally { # Close the reader. $row.Close() } } catch { Write-Error "Message: $($_.Exception.Message)" Write-Error "StackTrace: $($_.Exception.StackTrace)" Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)" } finally { $Connection.Close() }

You can use a command-line call like this:

powershell ./OracleContact.ps1 -v -o output.csv -q script.sql -p .

It produces the following verbose output to the console:

Evaluate swtich    [ -v ]
Evaluate parameter [ -o ] and [ output.csv ]
Evaluate parameter [ -q ] and [ script.sql ]
Evaluate parameter [ -p ] and [ . ]

You can suppress printing to the console by eliminating the -v switch from the parameter list.

As always, I hope this helps those looking for a solution to less tedious interactions with the Oracle database.

Written by maclochlainn

April 21st, 2022 at 5:21 pm

Posted in Database,Database Design,DBA,DevOps,Microsoft,Microsoft Office,Microsoft Windows 10,Microsoft Windows 11,Oracle,Oracle 12c,Oracle 18c,Oracle 21c,Oracle Developer,sql,Windows 10,Windows OS

Tagged with Oracle DBA, Oracle Developer

Switch or Parameter

without comments

I told my students that processing parameters in pairs, like a prior post demonstrated for another student, was a bad idea. That’s true because any list of parameters may contain switches and parameter/argument pairs.

A switch is a signal to turn on or off some behavior, like -v typically makes a utility produce a verbose (or wordy) display to console. Parameter and argument pairs are like name and value pairs in dictionaries. For example, you may have the following:

-o output.csv -s query.sql

The dash (–) identifies the parameter and the lack of one identifies an argument or value. So, here’s simply the PowerShell block re-written to demonstrate how to handled an argument list that may contain switches and parameter/argument pairs:

# Wrap the Parameter call to avoid a type casting warning.
try {
  param (
    [Parameter(Mandatory)][hashtable]$args
  )
}
catch {}
 
# Check for switches and parameters with arguments.
for ($i = 0; $i -lt $args.count; $i += 1) {
  if (($args[$i].startswith("-")) -and ($args[$i + 1].startswith("-"))) {
    $verbose = $true
	# Print to verbose console.
    if ($verbose) { Get-Message $args[$i] }}
  elseif ($args[$i].startswith("-")) {
    # Print to verbose console.
    if ($verbose) { Get-Message $args[$i] $args[$i + 1] }
 
    # Evaluate and take action on parameters and values.
    if ($args[$i] -eq "-o") {
      $outfile = $args[$i + 1] }
    elseif ($args[$i] -eq "-q") {
      $sqlFile = $args[$i + 1] }
    elseif ($args[$i] -eq "-s") {
      # You must evaluate the argument before using it to access an enum
      # value; and the program assumes an incorrect SQL statement value
      #	means you should assume the SQL statemente is a query.
      if ([SQLStatements]::($args[$i + 1])) {
        $stmt = [SQLStatements]::($args[$i + 1]) }}	
    elseif ($args[$i] -eq "-p") {
      $path = $args[$i + 1] }
  }
}

I hope this helps those looking for a solution to processing a parameter list in a PowerShell script.

Written by maclochlainn

April 21st, 2022 at 5:15 pm

Posted in Microsoft Windows 10,Microsoft Windows 11,PowerShell,Windows 10,Windows OS

Tagged with Robotic Process Automation, Windows Administrator, Windows Developer

PowerShell Parameter List

without comments

Playing around with passing parameters for a PowerShell project and ran into some interesting quirks. I found that you can pass in a set of values by declaring the parameter list as an array or string[] but the Microsoft documentation suggests using the array data type, which is implicitly cast as a string.

So, this would work provided you want to enter commas between the parameters:

param (
  [Parameter(Mandatory)][array]$args
)

However, you can’t use a -o for example because it’ll raise this error message:

C:\PowerShell\test\Params.ps1 : Parameter cannot be processed because the parameter name 'o' is ambiguous. Possible matches
include: -OutVariable -OutBuffer.
At line:1 char:21
+ ./Params.ps1 -o output.csv
+                     ~~
    + CategoryInfo          : InvalidArgument: (:) [Params.ps1], ParameterBindingException
    + FullyQualifiedErrorId : AmbiguousParameter,Params.ps1

The error doesn’t occur because the script but because the -OutVariable and -OutBuffer are parameters of the Powershell executable. You can get rid of the error and use an array without error by using two hyphens (—) back-to-back before your script’s parameters. The two hyphens together are the end-of-parameters operator, which means everything following it will be treated as arguments.

However, the better solution is to use a hashtable, like:

param (
  [Parameter(Mandatory)][hashtable]$args
)

However, as written above it raises the following error:

Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.Collections.Hashtable".
At C:\PowerShell\test\Params.ps1:35 char:1
+ param (
+ ~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [], RuntimeException
    + FullyQualifiedErrorId : ConvertToFinalInvalidCastException

There’s a great deal of humor if you recognize that the parameters are actually cast as strings in the hashtable and the error is more of a warning rather than a real error. All you need to do is wrap the param() call in a try-catch block, like:

try {
  param (
    [Parameter(Mandatory)][hashtable]$args
  )
}
catch {}

Then, you can call the Powershell script like this from the CLI:

powershell params -o outfile.csv -q query.sql

and, process it in a block like this, naturally qualifying the keys before assigning to local variables:

if ($args.count % 2) {
  Write-Host "Uneven number of parameters." 
  Exit }
else {
  for ($i = 0; $i -lt $args.count; $i += 2) {
    if ($args[$i] -eq "-o") {
      $outFile = $args[$i + 1] }
    elseif ($args[$i] -eq "-q") {
      $queryFile = $args[$i + 1] }
  }
}

Since this is really simply a script file, I prefer the if-not logic or a verbose (wordy) list of options when the parameter list is incorrect. For example, you could rewrite the block above to exclude an error message as follows:

if (!($args.count % 2)) {
  for ($i = 0; $i -lt $args.count; $i += 2) {
    if ($args[$i] -eq "-o") {
      $outFile = $args[$i + 1] }
    elseif ($args[$i] -eq "-q") {
      $queryFile = $args[$i + 1] }
  }}
else {
  exit }

While I answered the question about pairs, it’s a bad approach. You can find the right way to handle options (or, switches) and parameters to your PowerShell scripts in this later blog post.

As always, I hope blogging tidbits helps others looking for a solution.

Written by maclochlainn

April 14th, 2022 at 1:59 am

Posted in Microsoft,Microsoft Windows 10,Microsoft Windows 11,PowerShell,Windows 10,Windows OS

Tagged with PowerShell Developer, Programming

MySQL CSV Output

without comments

Saturday, I posted how to use Microsoft ODBC DSN to connect to MySQL. Somebody didn’t like the fact that the PowerShell program failed to write a *.csv file to disk because the program used the Write-Host command to write to the content of the query to the console.

I thought that approach was a better as an example. However, it appears that it wasn’t because not everybody knows simple redirection. The original program can transfer the console output to a file, like:

powershell .\MySQLODBC.ps1 > output.csv

So, the first thing you need to do is add a parameter list, like:

2
3
4

param (
  [Parameter(Mandatory)][string]$fileName
)

Anyway, it’s trivial to demonstrate how to modify the PowerShell program to write to a disk. You should also create a virtual PowerShell drive before writing the file. That’s because you can change the physical directory anytime you want with minimal changes to rest of your code’s file references.

You can create a PowerShell virtual drive with the following command:

7 8	New-PSDrive -Name test -PSProvider FileSystem -Description 'Test area' ` -Root C:\Data\cit225\mysql\test

but, it will write the following to console:

Name           Used (GB)     Free (GB) Provider      Root                                                                                 CurrentLocation
----           ---------     --------- --------      ----                                                                                 ---------------
test                0.00         28.74 FileSystem    C:\Data\cit225\mysql\test

You can suppress the console output with Microsoft’s version of redirection to the void (> /dev/null), which pipes (|) the standard out (stdout) to Out-Null, like:

7 8	New-PSDrive -Name test -PSProvider FileSystem -Description 'Test area' ` -Root C:\Data\cit225\mysql\test \| Out-Null

Since the program may run before an output file has been created, or after its been created and removed, you need to check whether the file exists before attempting to remove it. PowerShell provides the Test-Path command to check for the existence of a file and the Remove-Item command to remove a file, like:

11
12

if (Test-Path test:$fileName) {
  Remove-Item -Path test:$fileName }

Then, you simply replace the Write-Host call in the other program with the Add-Content command:

Add-Content -Value $output -Path test:$fileName

Now, the PowerShell script file writes the MySQL query’s output to an output.csv file. You can call the MySQLContact.ps1 script file with the following syntax:

powershell MySQLContact.ps1 output.csv

In case these changes don’t make sense outside the scope of the full script, here is the rewritten script:

# Define parameter list for mandatory file name.
param (
  [Parameter(Mandatory)][string]$fileName
)
 
# Define a PowerShell Virtual Drive.
New-PSDrive -Name test -PSProvider FileSystem -Description 'Test area' `
            -Root C:\Data\cit225\mysql\test | Out-Null
 
# Remove the file only when it exists.
if (Test-Path test:$fileName) {
  Remove-Item -Path test:$fileName }
 
# Define a ODBC DSN connection string.
$ConnectionString = 'DSN=MySQLODBC2'
 
# Define a MySQL Command Object for a non-query.
$Connection = New-Object System.Data.Odbc.OdbcConnection;
$Connection.ConnectionString = $ConnectionString
 
# Attempt connection.
try {
  $Connection.Open()
 
  # Create a SQL command.
  $Command = $Connection.CreateCommand();
  $Command.CommandText = "SELECT last_name " + 
                         ",      first_name " +
                         "FROM   contact " +
                         "ORDER BY 1, 2";
 
  # Attempt to read SQL command.
  try {
    $row = $Command.ExecuteReader();
 
    # Read while records are found.
    while ($row.Read()) {
      # Initialize output for each row.
      $output = ""
 
      # Navigate across all columns (only two in this example).
      for ($column = 0; $column -lt $row.FieldCount; $column += 1) {
        # Mechanic for comma-delimit between last and first name.  
        if ($output.length -eq 0) { 
          $output += $row[$column] }
        else {
          $output += ", " + $row[$column] }
      }
        # Write the output from the database to a file.
        Add-Content -Value $output -Path test:$fileName
    }
 
  } catch {
    Write-Error "Message: $($_.Exception.Message)"
    Write-Error "StackTrace: $($_.Exception.StackTrace)"
    Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
  } finally {
    # Close the reader.
    $row.Close() }
 
} catch {
  Write-Error "Message: $($_.Exception.Message)"
  Write-Error "StackTrace: $($_.Exception.StackTrace)"
  Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
} finally {
  $Connection.Close() }

# Define parameter list for mandatory file name. param ( [Parameter(Mandatory)][string]$fileName ) # Define a PowerShell Virtual Drive. New-PSDrive -Name test -PSProvider FileSystem -Description 'Test area' ` -Root C:\Data\cit225\mysql\test | Out-Null # Remove the file only when it exists. if (Test-Path test:$fileName) { Remove-Item -Path test:$fileName } # Define a ODBC DSN connection string. $ConnectionString = 'DSN=MySQLODBC2' # Define a MySQL Command Object for a non-query. $Connection = New-Object System.Data.Odbc.OdbcConnection; $Connection.ConnectionString = $ConnectionString # Attempt connection. try { $Connection.Open() # Create a SQL command. $Command = $Connection.CreateCommand(); $Command.CommandText = "SELECT last_name " + ", first_name " + "FROM contact " + "ORDER BY 1, 2"; # Attempt to read SQL command. try { $row = $Command.ExecuteReader(); # Read while records are found. while ($row.Read()) { # Initialize output for each row. $output = "" # Navigate across all columns (only two in this example). for ($column = 0; $column -lt $row.FieldCount; $column += 1) { # Mechanic for comma-delimit between last and first name. if ($output.length -eq 0) { $output += $row[$column] } else { $output += ", " + $row[$column] } } # Write the output from the database to a file. Add-Content -Value $output -Path test:$fileName } } catch { Write-Error "Message: $($_.Exception.Message)" Write-Error "StackTrace: $($_.Exception.StackTrace)" Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)" } finally { # Close the reader. $row.Close() } } catch { Write-Error "Message: $($_.Exception.Message)" Write-Error "StackTrace: $($_.Exception.StackTrace)" Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)" } finally { $Connection.Close() }

While I understand you might want to go to this level of effort if you where building a formal cmdlet, I’m not convinced its worth the effort in an ordinary PowerShell script. However, I don’t like to leave a question unanswered.

Written by maclochlainn

April 4th, 2022 at 12:45 am

Posted in Database,Database Design,DevOps,Microsoft Windows 10,MySQL,MySQL 8,Windows 10,Windows OS

Tagged with MySQL Developer, MySQL Techniques

MySQL ODBC DSN

with one comment

This post explains and demonstrates how to install, configure, and use the MySQL’s ODBC libraries and a DSN (Data Source Name) to connect your Microsoft PowerShell programs to a locally or remotely installed MySQL database. After you’ve installed the MySQL ODBC library, use Windows search field to find the ODBC Data Sources dialog and run it as administrator.

There are four steps to setup, test, and save your ODBC Data Source Name (DSN) for MySQL. You can click on the images on the right to launch them in a more readable format or simply read the instructions.

MySQL ODBC Setup Steps

Click the SystemDSN tab to see he view which is exactly like the User DSN tab. Click the Add button to start the workflow.

The Create New Data Source dialog requires you select the MySQL ODBC Driver(UNICODE) option from the list and click the Finish button to proceed.

The MySQL Unicode ODBC Driver Setup dialog should complete the prompts as follows below. If you opt for localhost as the server value and you have a DCHP IP address, make sure you’ve configured your hosts file in the C:\Windows\System32\drivers\etc directory. You should enter the following two lines in the hosts file:
127.0.0.1 localhost ::1 localhost
127.0.0.1 localhost ::1 localhost
These are the string values you should enter in the MySQL Unicode ODBC Driver Setup dialog:
Data Source: MySQLODBC Database: studentdb Server: localhost User Name: student Description: MySQL ODBC Connector Port: 3306 Password: student
Data Source: MySQLODBC Database: studentdb Server: localhost User Name: student Description: MySQL ODBC Connector Port: 3306 Password: student
After you complete the entry, click the Test button.

The Connection Test dialog should return a “Connection successful” message. Click the OK button to continue, and then click the OK button in the next two screens.

After you have created the System MySQL ODBC Setup, it’s time to build a PowerShell Cmdlet (or, Commandlet). Some documentation and blog notes incorrectly suggest you need to write a connection string with a UID and password, like:

$ConnectionString = 'DSN=MySQLODBC;Uid=student;Pwd=student'

You can do that if you leave the UID and password fields empty in the MySQL ODBC Setup but it’s recommended to enter them there to avoid putting them in your PowerShell script file.

The UID and password are unnecessary in the connection string when they’re in MySQL ODBC DSN. You can use a connection string like the following when the UID and password are in the DSN:

$ConnectionString = 'DSN=MySQLODBC'

You can create a MySQLCursor.ps1 Cmdlet like the following:

# Define a ODBC DSN connection string.
$ConnectionString = 'DSN=MySQLODBC'
 
# Define a MySQL Command Object for a non-query.
$Connection = New-Object System.Data.Odbc.OdbcConnection;
$Connection.ConnectionString = $ConnectionString
 
# Attempt connection.
try {
  $Connection.Open()
 
  # Create a SQL command.
  $Command = $Connection.CreateCommand();
  $Command.CommandText = "SELECT database();";
 
  # Attempt to read SQL command.
  try {
    $Reader = $Command.ExecuteReader();
 
    # Read while records are found.
    while ($Reader.Read()) {
      Write-Host "Current Database [" $Reader[0] "]"}
 
  } catch {
    Write-Host "Message: $($_.Exception.Message)"
    Write-Host "StackTrace: $($_.Exception.StackTrace)"
    Write-Host "LoaderExceptions: $($_.Exception.LoaderExceptions)"
  } finally {
    # Close the reader.
    $Reader.Close() }
 
} catch {
  Write-Host "Message: $($_.Exception.Message)"
  Write-Host "StackTrace: $($_.Exception.StackTrace)"
  Write-Host "LoaderExceptions: $($_.Exception.LoaderExceptions)"
} finally {
  $Connection.Close() }

Line 14 assigns a SQL query that returns a single row with one column as the CommandText of a Command object. Line 22 reads the zero position of a row or record set with only one column.

You call the MySQLCursor.ps1 Cmdlet with the following syntax:

powershell .\MySQLCursor.ps1

It returns:

Current Database [ studentdb ]

A more realistic way to write a query would return multiple rows with a set of two or more columns. The following program queries a table with multiple rows of two columns, but the program logic can manage any number of columns.

# Define a ODBC DSN connection string.
$ConnectionString = 'DSN=MySQLODBC'
 
# Define a MySQL Command Object for a non-query.
$Connection = New-Object System.Data.Odbc.OdbcConnection;
$Connection.ConnectionString = $ConnectionString
 
# Attempt connection.
try {
  $Connection.Open()
 
  # Create a SQL command.
  $Command = $Connection.CreateCommand();
  $Command.CommandText = "SELECT last_name, first_name FROM contact ORDER BY 1, 2";
 
  # Attempt to read SQL command.
  try {
    $row = $Command.ExecuteReader();
 
    # Read while records are found.
    while ($row.Read()) {
      # Initialize output for each row.
      $output = ""
 
      # Navigate across all columns (only two in this example).
      for ($column = 0; $column -lt $row.FieldCount; $column += 1) {
        # Mechanic for comma-delimit between last and first name.  
        if ($output.length -eq 0) { 
          $output += $row[$column] }
        else {
          $output += ", " + $row[$column] }
        }
        # Write the output from the database.
        Write-Host $output
      }
 
  } catch {
    Write-Host "Message: $($_.Exception.Message)"
    Write-Host "StackTrace: $($_.Exception.StackTrace)"
    Write-Host "LoaderExceptions: $($_.Exception.LoaderExceptions)"
  } finally {
    # Close the reader.
    $row.Close() }
 
} catch {
  Write-Host "Message: $($_.Exception.Message)"
  Write-Host "StackTrace: $($_.Exception.StackTrace)"
  Write-Host "LoaderExceptions: $($_.Exception.LoaderExceptions)"
} finally {
  $Connection.Close() }

You call the MySQLContact.ps1 Cmdlet with the following syntax:

powershell .\MySQLContact.ps1

It returns an ordered set of comma-separated values, like

Clinton, Goeffrey
Gretelz, Simon
Moss, Wendy
Potter, Ginny
Potter, Harry
Potter, Lily
Royal, Elizabeth
Smith, Brian
Sweeney, Ian
Sweeney, Matthew
Sweeney, Meaghan
Vizquel, Doreen
Vizquel, Oscar
Winn, Brian
Winn, Randi

As always, I hope this helps those looking for a complete concrete example of how to make Microsoft Powershell connect and query results from a MySQL database.

Written by maclochlainn

April 2nd, 2022 at 7:56 pm

Posted in Database,DevOps,Microsoft Windows 10,MySQL,MySQL 8,Windows 10,Windows OS

Tagged with MySQL DBA, MySQL Developer, MySQL Techniques

MySQL 5-Table Procedure

with one comment

A student wanted a better example of writing a MySQL Persistent Stored Module (PSM) that maintains transactional scope across a couple tables. Here’s the one I wrote about ten years ago to build the MySQL Video Store model. It looks I neglected to put it out there before, so here it is for reference.

-- Conditionally drop procedure if it exists.
DROP PROCEDURE IF EXISTS contact_insert;
 
-- Reset the delimiter so that a semicolon can be used as a statement and block terminator.
DELIMITER $$
 
SELECT 'CREATE PROCEDURE contact_insert' AS "Statement";
CREATE PROCEDURE contact_insert
( pv_member_type         CHAR(12)
, pv_account_number      CHAR(19)
, pv_credit_card_number  CHAR(19)
, pv_credit_card_type    CHAR(12)
, pv_first_name          CHAR(20)
, pv_middle_name         CHAR(20)
, pv_last_name           CHAR(20)
, pv_contact_type        CHAR(12)
, pv_address_type        CHAR(12)
, pv_city                CHAR(30)
, pv_state_province      CHAR(30)
, pv_postal_code         CHAR(20)
, pv_street_address      CHAR(30)
, pv_telephone_type      CHAR(12)
, pv_country_code        CHAR(3)
, pv_area_code           CHAR(6)
, pv_telephone_number    CHAR(10)) MODIFIES SQL DATA
 
BEGIN
 
  /* Declare variables to manipulate auto generated sequence values. */
  DECLARE member_id            int unsigned;
  DECLARE contact_id           int unsigned;
  DECLARE address_id           int unsigned;
  DECLARE street_address_id    int unsigned;
  DECLARE telephone_id         int unsigned;
 
  /* Declare local constants for who-audit columns. */
  DECLARE lv_created_by        int unsigned DEFAULT 1001;
  DECLARE lv_creation_date     DATE         DEFAULT UTC_DATE();
  DECLARE lv_last_updated_by   int unsigned DEFAULT 1001;
  DECLARE lv_last_update_date  DATE         DEFAULT UTC_DATE();
 
  /* Declare a locally scoped variable. */
  DECLARE duplicate_key INT DEFAULT 0;
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
 
  /* Start the transaction context. */
  START TRANSACTION;
 
  /* Create a SAVEPOINT as a recovery point. */
  SAVEPOINT all_or_none;
 
  /* Insert into the first table in sequence based on inheritance of primary keys by foreign keys. */
  INSERT INTO member
  ( member_type
  , account_number
  , credit_card_number
  , credit_card_type
  , created_by
  , creation_date
  , last_updated_by
  , last_update_date )
  VALUES
  ((SELECT   common_lookup_id
    FROM     common_lookup
    WHERE    common_lookup_context = 'MEMBER'
    AND      common_lookup_type = pv_member_type)
  , pv_account_number
  , pv_credit_card_number
  ,(SELECT   common_lookup_id
    FROM     common_lookup
    WHERE    common_lookup_context = 'MEMBER'
    AND      common_lookup_type = pv_credit_card_type)
  , lv_created_by
  , lv_creation_date
  , lv_last_updated_by
  , lv_last_update_date );
 
  /* Preserve the sequence by a table related variable name. */
  SET member_id = last_insert_id();
 
  /* Insert into the first table in sequence based on inheritance of primary keys by foreign keys. */
  INSERT INTO contact
  VALUES
  ( null
  , member_id
  ,(SELECT   common_lookup_id
    FROM     common_lookup
    WHERE    common_lookup_context = 'CONTACT'
    AND      common_lookup_type = pv_contact_type)
  , pv_first_name
  , pv_middle_name
  , pv_last_name
  , lv_created_by
  , lv_creation_date
  , lv_last_updated_by
  , lv_last_update_date );  
 
  /* Preserve the sequence by a table related variable name. */
  SET contact_id = last_insert_id();
 
  /* Insert into the first table in sequence based on inheritance of primary keys by foreign keys. */
  INSERT INTO address
  VALUES
  ( null
  , last_insert_id()
  ,(SELECT   common_lookup_id
    FROM     common_lookup
    WHERE    common_lookup_context = 'MULTIPLE'
    AND      common_lookup_type = pv_address_type)
  , pv_city
  , pv_state_province
  , pv_postal_code
  , lv_created_by
  , lv_creation_date
  , lv_last_updated_by
  , lv_last_update_date );  
 
  /* Preserve the sequence by a table related variable name. */
  SET address_id = last_insert_id();
 
  /* Insert into the first table in sequence based on inheritance of primary keys by foreign keys. */
  INSERT INTO street_address
  VALUES
  ( null
  , last_insert_id()
  , pv_street_address
  , lv_created_by
  , lv_creation_date
  , lv_last_updated_by
  , lv_last_update_date );  
 
  /* Insert into the first table in sequence based on inheritance of primary keys by foreign keys. */
  INSERT INTO telephone
  VALUES
  ( null
  , contact_id
  , address_id
  ,(SELECT   common_lookup_id
    FROM     common_lookup
    WHERE    common_lookup_context = 'MULTIPLE'
    AND      common_lookup_type = pv_telephone_type)
  , pv_country_code
  , pv_area_code
  , pv_telephone_number
  , lv_created_by
  , lv_creation_date
  , lv_last_updated_by
  , lv_last_update_date);
 
  /* This acts as an exception handling block. */  
  IF duplicate_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT all_or_none;
 
  END IF;
 
  /* This commits the write when successful and is harmless otherwise. */
  COMMIT;
 
END;
$$
 
-- Reset the standard delimiter to let the semicolon work as an execution command.
DELIMITER ;

-- Conditionally drop procedure if it exists. DROP PROCEDURE IF EXISTS contact_insert; -- Reset the delimiter so that a semicolon can be used as a statement and block terminator. DELIMITER $$ SELECT 'CREATE PROCEDURE contact_insert' AS "Statement"; CREATE PROCEDURE contact_insert ( pv_member_type CHAR(12) , pv_account_number CHAR(19) , pv_credit_card_number CHAR(19) , pv_credit_card_type CHAR(12) , pv_first_name CHAR(20) , pv_middle_name CHAR(20) , pv_last_name CHAR(20) , pv_contact_type CHAR(12) , pv_address_type CHAR(12) , pv_city CHAR(30) , pv_state_province CHAR(30) , pv_postal_code CHAR(20) , pv_street_address CHAR(30) , pv_telephone_type CHAR(12) , pv_country_code CHAR(3) , pv_area_code CHAR(6) , pv_telephone_number CHAR(10)) MODIFIES SQL DATA BEGIN /* Declare variables to manipulate auto generated sequence values. */ DECLARE member_id int unsigned; DECLARE contact_id int unsigned; DECLARE address_id int unsigned; DECLARE street_address_id int unsigned; DECLARE telephone_id int unsigned; /* Declare local constants for who-audit columns. */ DECLARE lv_created_by int unsigned DEFAULT 1001; DECLARE lv_creation_date DATE DEFAULT UTC_DATE(); DECLARE lv_last_updated_by int unsigned DEFAULT 1001; DECLARE lv_last_update_date DATE DEFAULT UTC_DATE(); /* Declare a locally scoped variable. */ DECLARE duplicate_key INT DEFAULT 0; /* Declare a duplicate key handler */ DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1; /* Start the transaction context. */ START TRANSACTION; /* Create a SAVEPOINT as a recovery point. */ SAVEPOINT all_or_none; /* Insert into the first table in sequence based on inheritance of primary keys by foreign keys. */ INSERT INTO member ( member_type , account_number , credit_card_number , credit_card_type , created_by , creation_date , last_updated_by , last_update_date ) VALUES ((SELECT common_lookup_id FROM common_lookup WHERE common_lookup_context = 'MEMBER' AND common_lookup_type = pv_member_type) , pv_account_number , pv_credit_card_number ,(SELECT common_lookup_id FROM common_lookup WHERE common_lookup_context = 'MEMBER' AND common_lookup_type = pv_credit_card_type) , lv_created_by , lv_creation_date , lv_last_updated_by , lv_last_update_date ); /* Preserve the sequence by a table related variable name. */ SET member_id = last_insert_id(); /* Insert into the first table in sequence based on inheritance of primary keys by foreign keys. */ INSERT INTO contact VALUES ( null , member_id ,(SELECT common_lookup_id FROM common_lookup WHERE common_lookup_context = 'CONTACT' AND common_lookup_type = pv_contact_type) , pv_first_name , pv_middle_name , pv_last_name , lv_created_by , lv_creation_date , lv_last_updated_by , lv_last_update_date ); /* Preserve the sequence by a table related variable name. */ SET contact_id = last_insert_id(); /* Insert into the first table in sequence based on inheritance of primary keys by foreign keys. */ INSERT INTO address VALUES ( null , last_insert_id() ,(SELECT common_lookup_id FROM common_lookup WHERE common_lookup_context = 'MULTIPLE' AND common_lookup_type = pv_address_type) , pv_city , pv_state_province , pv_postal_code , lv_created_by , lv_creation_date , lv_last_updated_by , lv_last_update_date ); /* Preserve the sequence by a table related variable name. */ SET address_id = last_insert_id(); /* Insert into the first table in sequence based on inheritance of primary keys by foreign keys. */ INSERT INTO street_address VALUES ( null , last_insert_id() , pv_street_address , lv_created_by , lv_creation_date , lv_last_updated_by , lv_last_update_date ); /* Insert into the first table in sequence based on inheritance of primary keys by foreign keys. */ INSERT INTO telephone VALUES ( null , contact_id , address_id ,(SELECT common_lookup_id FROM common_lookup WHERE common_lookup_context = 'MULTIPLE' AND common_lookup_type = pv_telephone_type) , pv_country_code , pv_area_code , pv_telephone_number , lv_created_by , lv_creation_date , lv_last_updated_by , lv_last_update_date); /* This acts as an exception handling block. */ IF duplicate_key = 1 THEN /* This undoes all DML statements to this point in the procedure. */ ROLLBACK TO SAVEPOINT all_or_none; END IF; /* This commits the write when successful and is harmless otherwise. */ COMMIT; END; $$ -- Reset the standard delimiter to let the semicolon work as an execution command. DELIMITER ;

You can then call the procedure, like:

SELECT 'CALL contact_insert() PROCEDURE 5 times' AS "Statement";
CALL contact_insert('INDIVIDUAL','R11-514-34','1111-1111-1111-1111','VISA_CARD','Goeffrey','Ward','Clinton','CUSTOMER','HOME','Provo','Utah','84606','118 South 9th East','HOME','011','801','423\-1234');
CALL contact_insert('INDIVIDUAL','R11-514-35','1111-2222-1111-1111','VISA_CARD','Wendy',null,'Moss','CUSTOMER','HOME','Provo','Utah','84606','1218 South 10th East','HOME','011','801','423-1234');
CALL contact_insert('INDIVIDUAL','R11-514-36','1111-1111-2222-1111','VISA_CARD','Simon','Jonah','Gretelz','CUSTOMER','HOME','Provo','Utah','84606','2118 South 7th East','HOME','011','801','423-1234');
CALL contact_insert('INDIVIDUAL','R11-514-37','1111-1111-1111-2222','MASTER_CARD','Elizabeth','Jane','Royal','CUSTOMER','HOME','Provo','Utah','84606','2228 South 14th East','HOME','011','801','423-1234');
CALL contact_insert('INDIVIDUAL','R11-514-38','1111-1111-3333-1111','VISA_CARD','Brian','Nathan','Smith','CUSTOMER','HOME','Spanish Fork','Utah','84606','333 North 2nd East','HOME','011','801','423-1234');

I hope this code complete approach helps those looking to learn how to write MySQL PSMs.

Written by maclochlainn

March 31st, 2022 at 1:40 am

Posted in Database,Linux,MySQL,MySQL 8,PSM,Unix,Windows 10,Windows OS

Tagged with MySQL DBA, MySQL Developer, MySQL Techniques

PostgreSQL+PowerShell

with one comment

This post explains and demonstrates how to install, configure, and use the psqlODBC (32-bit) and psqlODBC (64-bit) libraries to connect your Microsoft PowerShell programs to a locally installed PostgreSQL 14 database. It relies on you previously installing and configuring a PostgreSQL 14 database. This post is a step-by-step guide to installing PostgreSQL 14 on Windows 10, and this post shows you how to configure the PostgreSQL 14 database.

If you didn’t follow the instructions to get the psqlODBC libraries in the installation blog post, you will need to get those libraries, as qualified by Microsoft with the PostgreSQL Stack Builder.

You can launch PostgreSQL Stack Builder after the install by clicking on Start -> PostgreSQL -> Stack Builder. Choose to enable Stack Builder to change your system and install the psqlODBC libraries. After you’ve installed the psqlODBC library, use Windows search field to find the ODBC Data Sources dialog and run it as administrator.

There are six steps to setup, test, and save your ODBC Data Source Name (DSN). You can click on the images on the right to launch them in a more readable format or simply read the instructions.

PostgreSQL ODBC Setup Steps

The Microsoft DSN (Data Source Name) dialog automatically elects the User DSN tab. Click on the System DSN tab.

The view under the System DSN is exactly like the User DSN tab. Click the Add button to start the workflow.

The Create New Data Source dialog requires you select the PostgreSQL ODBC Driver(UNICODE) option from the list and click the Finish button to proceed.

The PostgreSQL Unicode ODBC Driver Setup dialog should complete the prompts as follows below and consistent with the PostgreSQL 14 Configuration blog. If you opt for localhost as the server value because you have a DCHP IP address, make sure you’ve configured your hosts file in the C:\Windows\System32\drivers\etc directory. You should enter the following two lines in the hosts file:
127.0.0.1 localhost ::1 localhost
127.0.0.1 localhost ::1 localhost
These are the string values you should enter in the PostgreSQL Unicode ODBC Driver Setup dialog:
Data Source: PostgreSQL35W Database: videodb Server: localhost User Name: student Description: PostgreSQL SSL Mode: disable Port: 5432 Password: student
Data Source: PostgreSQL35W Database: videodb Server: localhost User Name: student Description: PostgreSQL SSL Mode: disable Port: 5432 Password: student
After you complete the entry, click the Test button.

The Connection Test dialog should return a “Connection successful” message. Click the OK button to continue.

The ODBC Data Source Administrator dialog should show the PostgreSQL35W System Data Source. Click the OK button to continue.

After you have created the System PostgreSQL ODBC Setup, it’s time to build a PowerShell Cmdlet (or, Commandlet). Some documentation and blog notes incorrectly suggest you need to write a connection string with a UID and password, like:

$ConnectionString = 'DSN=PostgreSQL35W;Uid=student;Pwd=student'

The UID and password is unnecessary in the connection string. As a rule, the UID and password are only necessary in the ODBC DSN, like:

$ConnectionString = 'DSN=PostgreSQL35W'

You can create a readcursor.ps1 Cmdlet like the following:

# Define a ODBC DSN connection string.
$ConnectionString = 'DSN=PostgreSQL35W'
 
# Define a MySQL Command Object for a non-query.
$Connection = New-Object System.Data.Odbc.OdbcConnection;
$Connection.ConnectionString = $ConnectionString
 
# Attempt connection.
try {
  $Connection.Open()
 
  # Create a SQL command.
  $Command = $Connection.CreateCommand();
  $Command.CommandText = "SELECT current_database();";
 
  # Attempt to read SQL command.
  try {
    $Reader = $Command.ExecuteReader();
 
    # Read while records are found.
    while ($Reader.Read()) {
      Write-Host "Current Database [" $Reader[0] "]"}
 
  } catch {
    Write-Error "Message: $($_.Exception.Message)"
    Write-Error "StackTrace: $($_.Exception.StackTrace)"
    Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
  } finally {
    # Close the reader.
    $Reader.Close() }
 
} catch {
  Write-Error "Message: $($_.Exception.Message)"
  Write-Error "StackTrace: $($_.Exception.StackTrace)"
  Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
} finally {
  $Connection.Close() }

Line 14 assigns a SQL query that returns a single row with one column as the CommandText of a Command object. Line 22 reads the zero position of a row or record set with only one column.

You call the readcursor.ps1 Cmdlet with the following syntax:

powershell .\readcursor.ps1

It returns:

Current Database [ videodb ]

# Define a ODBC DSN connection string.
$ConnectionString = 'DSN=PostgreSQL35W'
 
# Define a MySQL Command Object for a non-query.
$Connection = New-Object System.Data.Odbc.OdbcConnection;
$Connection.ConnectionString = $ConnectionString
 
# Attempt connection.
try {
  $Connection.Open()
 
  # Create a SQL command.
  $Command = $Connection.CreateCommand();
  $Command.CommandText = "SELECT last_name, first_name FROM contact ORDER BY 1, 2";
 
  # Attempt to read SQL command.
  try {
    $row = $Command.ExecuteReader();
 
    # Read while records are found.
    while ($row.Read()) {
      # Initialize output for each row.
      $output = ""
 
      # Navigate across all columns (only two in this example).
      for ($column = 0; $column -lt $row.FieldCount; $column += 1) {
        # Mechanic for comma-delimit between last and first name.  
        if ($output.length -eq 0) { 
          $output += $row[$column] }
        else {
          $output += ", " + $row[$column] }
        }
        # Write the output from the database.
        Write-Host $output
      }
 
  } catch {
    Write-Error "Message: $($_.Exception.Message)"
    Write-Error "StackTrace: $($_.Exception.StackTrace)"
    Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
  } finally {
    # Close the reader.
    $row.Close() }
 
} catch {
  Write-Error "Message: $($_.Exception.Message)"
  Write-Error "StackTrace: $($_.Exception.StackTrace)"
  Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
} finally {
  $Connection.Close() }

You call the readcontact.ps1 Cmdlet with the following syntax:

powershell .\readcontact.ps1

It returns an ordered set of comma-separated values, like

Clinton, Goeffrey
Gretelz, Simon
Moss, Wendy
Royal, Elizabeth
Smith, Brian
Sweeney, Ian
Sweeney, Matthew
Sweeney, Meaghan
Vizquel, Doreen
Vizquel, Oscar
Winn, Brian
Winn, Randi

As always, I hope this helps those looking for a complete concrete example of how to make Microsoft Powershell connect and query results from a PostgreSQL database.

Written by maclochlainn

March 31st, 2022 at 1:13 am

Posted in Database,Database Design,DBA,DevOps,Microsoft,Microsoft Windows 10,PostgreSQL,PostgreSQL 14,PowerShell,Windows 10,Windows OS

Tagged with PostgreSQL Developer, PowerShell Developer

MacLochlainns Weblog

Archive for the ‘Windows 10’ Category

Record Type Arrays

Multidimension Arrays

Parameter Validation

Oracle ODBC DSN

Switch or Parameter

PowerShell Parameter List

MySQL CSV Output

MySQL ODBC DSN

MySQL 5-Table Procedure

PostgreSQL+PowerShell

Recent Posts

Things Written About

Pages

Blogroll

Archives