MacLochlainns Weblog

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

1
2
3
4
5
6
7
8
9
10

# 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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# 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:

7
8
9
10
11
12
13
14
15
16

# 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

# 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.

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135

# 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.

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:

54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

# 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.

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.

Transaction Management

Learning Outcomes

• Learn how to use Multiversion Concurrency Control (MVCC).
• Learn how to manage ACID-compliant transactions.
• Learn how to use:
  • SAVEPOINT Statement
  • COMMIT Statement
  • ROLLBACK Statement

Lesson Material

Transaction Management involves two key components. One is Multiversion Concurrency Control (MVCC) so one user doesn’t interfere with another user. The other is data transactions. Data transactions packag SQL statements in the scope of an imperative language that uses Transaction Control Language (TCL) to extend ACID-compliance from single SQL statements to groups of SQL statements.

Multiversion Concurrency Control (MVCC)

Multiversion Concurrency Control (MVCC) uses database snapshots to provide transactions with memory-persistent copies of the database. This means that users, via their SQL statements, interact with the in-memory copies of data rather than directly with physical data. MVCC systems isolate user transactions from each other and guarantee transaction integrity by preventing dirty transactions, writes to the data that shouldn’t happen and that make the data inconsistent. Oracle Database 12c prevents dirty writes by its MVCC and transaction model.

Transaction models depend on transactions, which are ACID-compliant blocks of code. Oracle Database 12c provides an MVCC architecture that guarantees that all changes to data are ACID-compliant, which ensures the integrity of concurrent operations on data—transactions.

ACID-compliant transactions meet four conditions:

Atomic

They complete or fail while undoing any partial changes.

Consistent

They change from one state to another the same way regardless of whether
the change is made through parallel actions or serial actions.

Isolated

Partial changes are never seen by other users or processes in the concurrent system.

Durable

They are written to disk and made permanent when completed.

Oracle Database 12c manages ACID-compliant transactions by writing them to disk first, as redo log files only or as both redo log files and archive log files. Then it writes them to the database. This multiple-step process with logs ensures that Oracle database’s buffer cache (part of the instance memory) isn’t lost from any completed transaction. Log writes occur before the acknowledgement-of-transactions process occurs.

The smallest transaction in a database is a single SQL statement that inserts, updates, or deletes rows. SQL statements can also change values in one or more columns of a row in a table. Each SQL statement is by itself an ACID-compliant and MVCC-enabled transaction when managed by a transaction-capable database engine. The Oracle database is always a transaction-capable system. Transactions are typically a collection of SQL statements that work in close cooperation to accomplish a business objective. They’re often grouped into stored programs, which are functions, procedures, or triggers. Triggers are specialized programs that audit or protect data. They enforce business rules that prevent unauthorized changes to the data.

SQL statements and stored programs are foundational elements for development of business applications. They contain the interaction points between customers and the data and are collectively called the application programming interface (API) to the database. User forms (typically web forms today) access the API to interact with the data. In well-architected business application software, the API is the only interface that the form developer interacts with.

Database developers, such as you and I, create these code components to enforce business rules while providing options to form developers. In doing so, database developers must guard a few things at all cost. For example, some critical business logic and controls must prevent changes to the data in specific tables, even changes in API programs. That type of critical control is often written in database triggers. SQL statements are events that add, modify, or delete data. Triggers guarantee that API code cannot make certain additions, modifications, or deletions to critical resources, such as tables. Triggers can run before or after SQL statements. Their actions, like the SQL statements themselves, are temporary until the calling scope sends an instruction to commit the work performed.

A database trigger can intercept values before they’re placed in a column, and it can ensure that only certain values can be inserted into or updated in a column. A trigger overrides an INSERT or UPDATE statement value that violates a business rule and then it either raises an error and aborts the transaction or changes the value before it can be inserted or updated into the table. Chapter 12 offers examples of both types of triggers in Oracle Database 12c.
MVCC determines how to manage transactions. MVCC guarantees how multiple users’ SQL statements interact in an ACID compliant manner. The next two sections qualify how data transactions work and how MVCC locks and isolates partial results from data transactions.

Data Transaction

Data Manipulation Language (DML) commands are the SQL statements that transact against the data. They are principally the INSERT, UPDATE, and DELETE statements. The INSERT statement adds new rows in a table, the UPDATE statement modifies columns in existing rows, and the DELETE statement removes a row from a table.

The Oracle MERGE statement transacts against data by providing a conditional insert or update feature. The MERGE statement lets you add new rows when they don’t exist or change column values in rows that do exist.

Inserting data seldom encounters a conflict with other SQL statements because the values become a new row or rows in a table. Updates and deletes, on the other hand, can and do encounter conflicts with other UPDATE and DELETE statements. INSERT statements that encounter conflicts occur when columns in a new row match a preexisting row’s uniquely constrained columns. The insertion is disallowed because only one row can contain the unique column set.

These individual transactions have two phases in transactional databases such as Oracle. The first phase involves making a change that is visible only to the user in the current session. The user then has the option of committing the change, which makes it permanent, or rolling back the change, which undoes the transaction. Developers use Transaction Control Language (TCL) commands to confirm or cancel transactions. The COMMIT statement confirms or makes permanent any change, and the ROLLBACK statement cancels or undoes any change.

A generic transaction lifecycle for a two-table insert process implements a business rule that specifies that neither INSERT statement works unless they both work. Moreover, if the first INSERT statement fails, the second INSERT statement never runs; and if the second INSERT statement fails, the first INSERT statement is undone by a ROLLBACK statement to a SAVEPOINT.

After a failed transaction is unwritten, good development practice requires that you write the failed event(s) to an error log table. The write succeeds because it occurs after the ROLLBACK statement but before the COMMIT statement.

A SQL statement followed by a COMMIT statement is called a transaction process, or a two-phase commit (2PC) protocol. ACID-compliant transactions use a 2PC protocol to manage one SQL statement or collections of SQL statements. In a 2PC protocol model, the INSERT, UPDATE, MERGE, or DELETE DML statement starts the process and submits changes. These DML statements can also act as events that fire database triggers assigned to the table being changed.

Transactions become more complex when they include database triggers because triggers can inject an entire layer of logic within the transaction scope of a DML statement. For example, database triggers can do the following:

• Run code that verifies, changes, or repudiates submitted changes
• Record additional information after validation in other tables (they can’t write to the table being changed—or, in database lexicon, “mutated”
• Throw exceptions to terminate a transaction when the values don’t meet business rules

As a general rule, triggers can’t contain a COMMIT or ROLLBACK statement because they run inside the transaction scope of a DML statement. Oracle databases give developers an alternative to this general rule because they support autonomous transactions. Autonomous transactions run outside the transaction scope of the triggering DML statement. They can contain a COMMIT statement and act independently of the calling scope statement. This means an autonomous trigger can commit a transaction when the calling transaction fails.

As independent statements or collections of statements add, modify, and remove rows, one statement transacts against data only by locking rows: the SELECT statement. A SELECT statement typically doesn’t lock rows when it acts as a cursor in the scope of a stored program. A cursor is a data structure that contains rows of one-to-many columns in a stored program. This is also known as a list of record structures.

Cursors act like ordinary SQL queries, except they’re managed by procedure programs row by row. There are many examples of procedural programming languages. PL/SQL and SQL/PSM programming languages are procedural languages designed to run inside the database. C, C++, C#, Java, Perl, and PHP are procedural languages that interface with the database through well-defined interfaces, such as Java Database Connectivity (JDBC) and Open Database Connectivity (ODBC).

Cursors can query data two ways. One way locks the rows so that they can’t be changed until the cursor is closed; closing the cursor releases the lock. The other way doesn’t lock the rows, which allows them to be changed while the program is working with the data set from the cursor. The safest practice is to lock the rows when you open the cursor, and that should always be the case when you’re inserting, updating, or deleting rows that depend on the values in the cursor not changing until the transaction lifecycle of the program unit completes.

Loops use cursors to process data sets. That means the cursors are generally opened at or near the beginning of program units. Inside the loop the values from the cursor support one to many SQL statements for one to many tables.

Stored and external programs create their operational scope inside a database connection when they’re called by another program. External programs connect to a database and enjoy their own operational scope, known as a session scope. The session defines the programs’ operational scope. The operational scope of a stored program or external program defines the transaction scope. Inside the transaction scope, the programs interact with data in tables by inserting, updating, or deleting data until the operations complete successfully or encounter a critical failure. These stored program units commit changes when everything completes successfully, or they roll back changes when any critical instruction fails. Sometimes, the programs are written to roll back changes when any instruction fails.

In the Oracle Database, the most common clause to lock rows is the FOR UPDATE clause, which is appended to a SELECT statement. An Oracle database also supports a WAIT n seconds or NOWAIT option. The WAIT option is a blessing when you want to reply to an end user form’s request and can’t make the change quickly. Without this option, a change could hang around for a long time, which means virtually indefinitely to a user trying to run your application. The default value in an Oracle database is NOWAIT, WAIT without a timeout, or wait indefinitely.

You should avoid this default behavior when developing program units that interact with customers. The Oracle Database also supports a full table lock with the SQL LOCK TABLE command, but you would need to embed the command inside a stored or external program’s instruction set.

A unit test script may contain SQL or PL/SQL statements or it may call another script file that contains SQL or PL/SQL statements. Moreover, a script file is a way to bundle several activities into a single file because most unit test programs typically run two or more instructions as unit tests.

Unconditional Script File

You can write a simple unit test like the example program provided in the Lab 1 Help Section, which includes conditional logic. However, you can write a simpler script that is unconditional and raises exceptions when preconditions do not exist.

The following script file creates a one table and one_s sequence. The DROP TABLE and DROP SEQUENCE statements have the same precondition, which is that the table or sequence must previously exist.

-- Drop table one.
DROP TABLE one;
 
-- Crete table one.
CREATE TABLE one
( one_id    NUMBER
, one_text  VARCHAR2(10));
 
-- Drop sequence one_s.
DROP SEQUENCE one_s;
 
-- Create sequence one_s.
CREATE SEQUENCE one_s;

After writing the script file, you can save it in the lab2 subdirectory as the unconditional.sql file. After you login to the SQL*Plus environment from the lab2 subdirectory. You call the unconditional.sql script file from inside the SQL*Plus environment with the following syntax:

@unconditional.sql

It will display the following output, which raises an exception when the one table or one_s sequence does not already exist in the schema or database:

DROP TABLE one
           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
Table created.
 
DROP SEQUENCE one_s
              *
ERROR at line 1:
ORA-02289: sequence does not exist
 
Sequence created.

An unconditional script raises exceptions when a precondition of the statement does not exist. The precondition is not limited to objects, like the table or sequence; and the precondition may be specific data in one or several rows of one or several tables. You can avoid raising conditional errors by writing conditional scripts.

Conditional Script File

A conditional script file contains statements that check for a precondition before running a statement, which effectively promotes their embedded statements to a lambda function. The following logic recreates the logic of the unconditional.sql script file as a conditional script file:

-- Conditionally drop a table and sequence.
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name IN ('ONE','ONE_S')
            ORDER BY object_type ) LOOP
    IF i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS';
    ELSE
      EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name;
    END IF;
  END LOOP;
END;
/
 
-- Crete table one.
CREATE TABLE one
( one_id    NUMBER
, one_text  VARCHAR2(10));
 
-- Create sequence one_s.
CREATE SEQUENCE one_s;

You can save this script in the lab2 subdirectory as conditional.sql and then unit test it in SQL*Plus. You must manually drop the one table and one_s sequence before running the conditional.sql script to test the preconditions.

You will see that the conditional.sql script does not raise an exception because the one table or one_s sequence is missing. It should generate output to the console, like this:

PL/SQL procedure successfully completed.
 
Table created.
 
Sequence created.

As a rule, you should always write conditional script files. Unconditional script files throw meaningless errors, which may cause your good code to fail a deployment test that requires error free code.

Selective Aggregation

Learning Outcomes

• Learn how to combine CASE operators and aggregation functions.
• Learn how to selective aggregate values.
• Learn how to use SQL to format report output.

Selective aggregation is the combination of the CASE operator and aggregation functions. Any aggregation function adds, sums, or averages the numbers that it finds; and when you embed the results of a CASE operator inside an aggregation function you get a selective result. The selectivity is determined by the WHEN clause of a CASE operator, which is more or less like an IF statement in an imperative programming language.

The prototype for selective aggregation is illustrated with a SUM function below:

SELECT   SUM(CASE
               WHEN left_operand = right_operand THEN result
               WHEN left_operand > right_operand THEN result
               WHEN left_operand IN (SET OF comma-delimited VALUES) THEN result
               WHEN left_operand IN (query OF results) THEN result
               ELSE alt_result
             END) AS selective_aggregate
FROM     some_table;

A small example let’s you see how selective aggregation works. You create a PAYMENT table and PAYMENT_S sequence for this example, as follows:

-- Create a PAYMENT table.
CREATE TABLE payment
( payment_id     NUMBER
, payment_date   DATE	      CONSTRAINT nn_payment_1 NOT NULL
, payment_amount NUMBER(20,2) CONSTRAINT nn_payment_2 NOT NULL
, CONSTRAINT pk_payment PRIMARY KEY (payment_id));
 
-- Create a PAYMENT_S sequence.
CREATE SEQUENCE payment_s;

After you create the table and sequence, you should insert some data. You can match the values below or choose your own values. You should just insert values for a bunch of rows.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

DECLARE
  -- Create local collection data types.
  TYPE pmtval IS TABLE OF NUMBER(20,2);
  TYPE smonth IS TABLE OF VARCHAR2(3);
 
  -- Create variable to hold the list of payments.
  payments PMTVAL := pmtval();
 
  -- Declare month arrays.
  short_month SMONTH := smonth('JAN','FEB','MAR','APR','MAY','JUN'
                              ,'JUL','AUG','SEP','OCT','NOV','DEC');
 
  -- Declare variable values.
  month      VARCHAR2(3);
  year       NUMBER := '2019';
  pmt_date   DATE; 
  tpmt_date  VARCHAR2(11);
 
  -- Declare default number of random payments.
  payment_number  NUMBER := 10000;
BEGIN
  -- Populate payment list. 
  FOR i IN 1..payment_number LOOP
    payments.EXTEND;
    SELECT   ROUND(dbms_random.value() * 1000,0)
    ||       '.'
    ||       ROUND(dbms_random.value() * 100,0)
    INTO     payments(payments.COUNT)
    FROM     dual;
  END LOOP;
 
  -- Create and populate payment date and amount.
  FOR i IN 1..payment_number LOOP
    -- Assign random month value.
    month := short_month(dbms_random.value(1,short_month.COUNT));
 
    -- Assign random day of the month value and assemble random date.
    IF month IN ('JAN','MAR','MAY','JUL','AUG','OCT','DEC') THEN
      pmt_date := ROUND(dbms_random.value(1,31),0) || '-' || month || '-' || year;
    ELSIF month IN ('APR','JUN','SEP','NOV') THEN
      pmt_date := ROUND(dbms_random.value(1,30),0) || '-' || month || '-' || year;
    ELSE
      pmt_date := ROUND(dbms_random.value(1,28),0) || '-' || month || '-' || year;
    END IF;
 
    -- Insert values into the PAYMENT table.
    INSERT INTO payment
    ( payment_id, payment_date, payment_amount )
    VALUES
    ( payment_s.NEXTVAL, pmt_date, payments(i));
  END LOOP;
 
  -- Commit the writes.
  COMMIT;
END;
/

After inserting 10,000 rows, you can get an unformatted total with the following query:

-- Query total amount.
SELECT   SUM(payment_amount) AS payment_total
FROM     payment;

It outputs the following:

PAYMENT_TOTAL
-------------
   5011091.75

You can nest the result inside the TO_CHAR function to format the output, like

-- Query total formatted amount.
SELECT   TO_CHAR(SUM(payment_amount),'999,999,999.00') AS payment_total
FROM     payment;

It outputs the following:

PAYMENT_TOTAL
---------------
   5,011,091.75

Somebody may suggest that you use a PIVOT function to rotate the data into a summary by month but the PIVOT function has limits. The pivoting key must be numeric and the column values will use only those numeric values.

-- Pivoted summaries by numeric monthly value.
SELECT   *
FROM    (SELECT EXTRACT(MONTH FROM payment_date) payment_month
         ,      payment_amount
         FROM   payment)
         PIVOT (SUM(payment_amount) FOR payment_month IN
                 (1,2,3,4,5,6,7,8,9,10,11,12));

It outputs the following:

	 1	    2	       3	  4	     5		6	   7	      8 	 9	   10	      11	 12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
 245896.55  430552.36  443742.63  457860.27  470467.18	466370.71  415158.28  439898.72  458998.09  461378.56  474499.22  246269.18

You can use selective aggregation to get the results by a character label, like

SELECT   SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) = 1
             AND  EXTRACT(YEAR FROM payment_date) = 2019  THEN payment_amount
           END) AS "JAN"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) = 2
             AND  EXTRACT(YEAR FROM payment_date) = 2019  THEN payment_amount
           END) AS "FEB"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) = 3
             AND  EXTRACT(YEAR FROM payment_date) = 2019  THEN payment_amount
           END) AS "MAR"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) IN (1,2,3)
             AND  EXTRACT(YEAR FROM payment_date) = 2019 THEN payment_amount
           END) AS "1FQ"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) = 4
             AND  EXTRACT(YEAR FROM payment_date) = 2019  THEN payment_amount
           END) AS "APR"
FROM     payment;

It outputs the following:

       JAN	  FEB	     MAR	1FQ	   APR
---------- ---------- ---------- ---------- ----------
 245896.55  430552.36  443742.63 1120191.54  457860.27

You can format the output with a combination of the TO_CHAR and LPAD functions. The TO_CHAR allows you to add a formatting mask, complete with commas and two mandatory digits to the right of the decimal point. The reformatted query looks like

COL JAN FORMAT A13 HEADING "Jan"
COL FEB FORMAT A13 HEADING "Feb"
COL MAR FORMAT A13 HEADING "Mar"
COL 1FQ FORMAT A13 HEADING "1FQ"
COL APR FORMAT A13 HEADING "Apr"
SELECT   LPAD(TO_CHAR(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) = 1
             AND  EXTRACT(YEAR FROM payment_date) = 2019  THEN payment_amount
           END),'9,999,999.00'),13,' ') AS "JAN"
,        LPAD(TO_CHAR(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) = 2
             AND  EXTRACT(YEAR FROM payment_date) = 2019  THEN payment_amount
           END),'9,999,999.00'),13,' ') AS "FEB"
,        LPAD(TO_CHAR(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) = 3
             AND  EXTRACT(YEAR FROM payment_date) = 2019  THEN payment_amount
           END),'9,999,999.00'),13,' ') AS "MAR"
,        LPAD(TO_CHAR(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) IN (1,2,3)
             AND  EXTRACT(YEAR FROM payment_date) = 2019 THEN payment_amount
           END),'9,999,999.00'),13,' ') AS "1FQ"
,        LPAD(TO_CHAR(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM payment_date) = 4
             AND  EXTRACT(YEAR FROM payment_date) = 2019  THEN payment_amount
           END),'9,999,999.00'),13,' ') AS "APR"
FROM     payment;

It displays the formatted output:

Jan	      Feb	    Mar 	  1FQ		Apr
------------- ------------- ------------- ------------- -------------
   245,896.55	 430,552.36    443,742.63  1,120,191.54    457,860.27

INSERT Statement

Learning Outcomes

• Learn how to use positional- and named-notation in INSERT statements.
• Learn how to use the VALUES clause in INSERT statements.
• Learn how to use subqueries in INSERT statements.

The INSERT statement lets you enter data into tables and views in two ways: via an INSERT statement with a VALUES clause and via an INSERT statement with a query. The VALUES clause takes a list of literal values (strings, numbers, and dates represented as strings), expression values (return values from functions), or variable values.

Query values are results from SELECT statements that are subqueries (covered earlier in this appendix). INSERT statements work with scalar, single-row, and multiple-row subqueries. The list of columns in the VALUES clause or SELECT clause of a query (a SELECT list) must map to the positional list of columns that defines the table. That list is found in the data dictionary or catalog. Alternatively to the list of columns from the data catalog, you can provide a named list of those columns. The named list overrides the positional (or default) order from the data catalog and must provide at least all mandatory columns in the table definition. Mandatory columns are those that are not null constrained.

Oracle databases differ from other databases in how they implement the INSERT statement. Oracle doesn’t support multiple-row inserts with a VALUES clause. Oracle does support default and override signatures as qualified in the ANSI SQL standards. Oracle also provides a multiple- table INSERT statement. This section covers how you enter data with an INSERT statement that is based on a VALUES clause or a subquery result statement. It also covers multiple-table INSERT statements.

The INSERT statement has one significant limitation: its default signature. The default signature is the list of columns that defines the table in the data catalog. The list is defined by the position and data type of columns. The CREATE statement defines the initial default signature, and the ALTER statement can change the number, data types, or ordering of columns in the default signature.

The default prototype for an INSERT statement allows for an optional column list that overrides the default list of columns. When you provide the column list you choose to implement named-notation, which is the right way to do it. Relying on the insertion order of the columns is a bad idea. An INSERT statement without a list of column names is a position-notation statement. Position-notation is bad because somebody can alter that order and previously written INSERT statements will break or put data in the wrong columns.

Like methods in OOPLs, an INSERT statement without the optional column list constructs an instance (or row) of the table using the default constructor. The override constructor for a row is defined by any INSERT statement when you provide an optional column list. That’s because it overrides the default constructor.

The generic prototype for an INSERT statement is confusing when it tries to capture both the VALUES clause and the result set from a query. Therefore, I’ve opted to provide two generic prototypes.

Insert by value

The first uses the VALUES clause:

INSERT
INTO table_name
[( column1, column2, column3, ...)] VALUES
( value1, value2, value3, ...);

Notice that the prototype for an INSERT statement with the result set from a query doesn’t use the VALUES clause at all. A parsing error occurs when the VALUES clause and query both occur in an INSERT statement.

The second prototype uses a query and excludes the VALUES clause. The subquery may return one to many rows of data. The operative rule is that all columns in the query return the same number of rows of data, because query results should be rectangles—rectangles made up of one to many rows of columns.

Insert by subquery

Here’s the prototype for an INSERT statement that uses a subquery:

INSERT
INTO table_name
[( column1, column2, column3, ...)]
( SELECT value1, value2, value3, ... FROM table_name WHERE ...);

A query, or SELECT statement, returns a SELECT list. The SELECT list is the list of columns, and it’s evaluated by position and data type. The SELECT list must match the definition of the table or the override signature provided.

Default signatures present a risk of data corruption through insertion anomalies, which occur when you enter bad data in tables. Mistakes transposing or misplacing values can occur more frequently with a default signature, because the underlying table structure can change. As a best practice, always use named notation by providing the optional list of values; this should help you avoid putting the right data in the wrong place.

The following subsections provide examples that use the default and override syntax for INSERT statements in Oracle databases. The subsections also cover multiple-table INSERT statements and a RETURNING INTO clause, which is an extension of the ANSI SQL standard. Oracle uses the RETURNING INTO clause to manage large objects, to return autogenerated identity column values, and to support some of the features of Oracle’s dynamic SQL. Note that Oracle also supports a bulk INSERT statement, which requires knowledge of PL/SQL.

DESCRIBE table_name

Name                                 Null?    Type
------------------------------------ -------- --------
RENTAL_ID                            NOT NULL NUMBER
CUSTOMER_ID                          NOT NULL NUMBER
CHECK_OUT_DATE                       NOT NULL DATE
RETURN_DATE                                   DATE
CREATED_BY                           NOT NULL NUMBER
CREATION_DATE                        NOT NULL DATE
LAST_UPDATED_BY                      NOT NULL NUMBER
LAST_UPDATE_DATE                     NOT NULL DATE

Name                                      Null?    Type
------------------------------------ -------- --------
RENTAL_ID                            NOT NULL NUMBER
CUSTOMER_ID                          NOT NULL NUMBER
CHECK_OUT_DATE                       NOT NULL DATE
RETURN_DATE                                   DATE
CREATED_BY                           NOT NULL NUMBER
CREATION_DATE                        NOT NULL DATE
LAST_UPDATED_BY                      NOT NULL NUMBER
LAST_UPDATE_DATE                     NOT NULL DATE

SQL> INSERT INTO rental
  2  VALUES
  3  ( 1086
  4  , 1009
  5  , SYSDATE
  6  , TRUNC(SYSDATE + 5) 7 ,1
  8  , SYSDATE
  9  , 1
 10  , SYSDATE);

  5  , TO_DATE('15-APR-2011 12:53:01','DD-MON-YYYY HH24:MI:SS')

  5  , CAST('15-APR-2011 12:53:02' AS DATE)

  5  ,  CAST('15-APR-2011 12:53:02' AS DATE) FROM dual
        *
ERROR AT line 1:
ORA-01830: DATE format picture ends before converting entire input string

  5  ,  CAST(CAST('15-APR-2011 12:53:02' AS TIMESTAMP) AS DATE)

SQL> SELECT   rental_s1.NEXTVAL FROM dual;

  3  ( rental_s1.NEXTVAL

SQL> SELECT   contact_id
  2  FROM     contact
  3  WHERE    last_name = 'Potter'
  4  AND      first_name = 'Harry';

SQL> INSERT INTO rental
  2  ( rental_id
  3  , customer_id
  4  , check_out_date
  5  , return_date
  6  , created_by
  7  , creation_date
  8  , last_updated_by
  9  , last_update_date )
10 VALUES
 11  ( rental_s1.NEXTVAL
 12  ,(SELECT   contact_id
 13    FROM     contact
 14    WHERE    last_name = 'Potter'
 15    AND      first_name = 'Harry')
 16  , SYSDATE
 17  , TRUNC(SYSDATE + 5)
 18  , 1
 19  , SYSDATE
 20  , 3
 21  , SYSDATE);

,(SELECT   contact_id
  *
ERROR AT line 3:
ORA-01427: single-ROW subquery returns more than one ROW

Name                             Null?    Type
 ------------------------------- -------- -----------------------
ITEM_DESC                        NOT NULL CLOB
ITEM_ICON                        NOT NULL BLOB
ITEM_PHOTO                                BINARY FILE LOB

EMPTY_BLOB()
EMPTY_CLOB()

 10  , BFILENAME('VIRTUAL_DIRECTORY_NAME', 'file_name.png')

INSERT INTO some_table
[( column1, column2, column3, ...)]
VALUES
( value1, value2, value3, ...)
RETURNING column1 INTO local_variable;

Name                                 Null?    Type
------------------------------------ -------- --------------
PARENT_ID                            NOT NULL NUMBER
PARENT_NAME                                   VARCHAR2(10)

Name                                 Null?    Type
------------------------------------ -------- --------------
CHILD_ID                             NOT NULL NUMBER
PARENT_ID                                     NUMBER
PARENT_NAME                                   VARCHAR2(10)

SQL> INSERT INTO parent
  2  VALUES
  3  ( parent_s1.NEXTVAL 4 ,'One Parent');
 
SQL> INSERT INTO child
  2  VALUES
  3  ( child_s1.NEXTVAL 4 , parent_s1.CURRVAL 5 ,'One Child');

SQL> INSERT INTO child
  2  VALUES
  3  ( child_s1.NEXTVAL -- CHILD_ID
  4  , parent_s1.CURRVAL -- PARENT_ID
  5  ,'One Child') -- CHILD_NAME
  6  /

INSERT
INTO table_name
[( column1, column2, column3, ...)]
( SELECT value1, value2, value3, ... FROM table_name WHERE ...);

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

# 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.

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

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

2. 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.

3. 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.

4. 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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

# 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.