Archive for the ‘Microsoft Windows 11’ Category
MySQL @SQL_MODE
Installing MySQL Workbench 8 on Windows, we discovered that the default configuration no longer sets ONLY_FULL_GROUP_BY as part of the default SQL_MODE parameter value. While I’ve written a stored function to set the SQL_MODE parameter value for a session, some students didn’t understand that such a call is only valid in the scope of a connection to the database server. They felt the function didn’t work because they didn’t understand the difference between connecting to the MySQL CLI and clicking the lightening bolt in MySQL Workbench.
So, here are the instructions to reset the default SQL_MODE parameter value for Windows. You need to edit the setting in the my.ini file, which is in the C:\ProgramData\MySQL\MySQL Server 8.0 directory. The default installation will have the following:
# Set the SQL mode to strict sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION" |
You need to change it to the following in an editor with Administrative privileges:
# Set the SQL mode to strict sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY" |
Then, you need to connect to the services by launching services.msc from the command prompt. In the list of services find MYSQL80 service and restart it. You can verify it by connecting to the MySQL 8.0.* server and running the following SQL query:
SELECT @@SQL_MODE: |
That’s how you convert Windows to use only traditional group by behaviors in SQL. As always, I hope this helps those looking for a solution.
PostgreSQL Table Function
A quick tutorial on how to write a PL/pgSQL Table function. The functions is simple. It returns the list of conquistadors that were originally German. It does that by filtering on the lang column in the table. For example, you use ‘de‘ for German.
I’ll stage this with the same conquistador table used in the last post. Don’t forget to use the chcp command to the Active Console Code Page to 4-byte Unicode before you run the script file, like:
chcp 65001 |
Then, connect to the psql shell and run the following script file:
/* Conditionally drop the conquistador table. */ DROP TABLE IF EXISTS conquistador; /* Create the conquistador table. */ CREATE TABLE conquistador ( conquistador_id SERIAL , conquistador VARCHAR(30) , actual_name VARCHAR(30) , nationality VARCHAR(30) , lang VARCHAR(2)); /* Insert some conquistadors into the table. */ INSERT INTO conquistador ( conquistador , actual_name , nationality , lang ) VALUES ('Juan de Fuca','Ioánnis Fokás','Greek','el') ,('Nicolás de Federmán','Nikolaus Federmann','German','de') ,('Sebastián Caboto','Sebastiano Caboto','Venetian','it') ,('Jorge de la Espira','Georg von Speyer','German','de') ,('Eusebio Francisco Kino','Eusebius Franz Kühn','Italian','it') ,('Wenceslao Linck','Wenceslaus Linck','Bohemian','cs') ,('Fernando Consag','Ferdinand Konšcak','Croatian','sr') ,('Américo Vespucio','Amerigo Vespucci','Italian','it') ,('Alejo García','Aleixo Garcia','Portuguese','pt'); |
Now, you can build another script file to create the getConquistador function, like:
/* Drop the funciton conditionally. */ DROP FUNCTION IF EXISTS getConquistador; |
Create the getConquistador function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE FUNCTION getConquistador (IN lang_in VARCHAR(2)) RETURNS TABLE ( conquistador VARCHAR(30) , actual_name VARCHAR(30) , nationality VARCHAR(30)) AS $$ BEGIN RETURN QUERY SELECT c.conquistador , c.actual_name , c.nationality FROM conquistador c WHERE c.lang = lang_in; END; $$ LANGUAGE plpgsql; |
Then, you can test it like:
SELECT * FROM getConquistador('de'); |
It will return the following:
conquistador | actual_name | nationality -----------------------+--------------------+------------- Nicolás de Federmán | Nikolaus Federmann | German Jorge de la Espira | Georg von Speyer | German (2 rows) |
As always, I hope this helps with a technique that’s useful.
PostgreSQL Unicode
It seems unavoidable to use Windows. Each time I’m compelled to run tests on the platform I find new errors. For example, they don’t use 4-byte unicode and as a result when you want to use Unicode in PostgreSQL there’s a mismatch.
For example, change the Active Console Code Page with the chcp (change code page) to match the one PostgreSQL uses, like:
chip 1252 |
It lets you avoid this warning message:
Password for user postgres:
psql (14.1)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# |
However, it won’t avoid display issues with real Unicode values. For example, let’s use a small international table like the following:
/* Conditionally drop the conquistador table. */ DROP TABLE IF EXISTS conquistador; /* Create the conquistador table. */ CREATE TABLE conquistador ( conquistador_id SERIAL , conquistador VARCHAR(30) , actual_name VARCHAR(30) , nationality VARCHAR(30) , lang VARCHAR(2)); /* Insert some conquistadors into the table. */ INSERT INTO conquistador ( conquistador , actual_name , nationality , lang ) VALUES ('Juan de Fuca','Ioánnis Fokás','Greek','el') ,('Nicolás de Federmán','Nikolaus Federmann','German','de') ,('Sebastián Caboto','Sebastiano Caboto','Venetian','it') ,('Jorge de la Espira','Georg von Speyer','German','de') ,('Eusebio Francisco Kino','Eusebius Franz Kühn','Italian','it') ,('Wenceslao Linck','Wenceslaus Linck','Bohemian','cs') ,('Fernando Consag','Ferdinand Konšcak','Croatian','sr') ,('Américo Vespucio','Amerigo Vespucci','Italian','it') ,('Alejo García','Aleixo Garcia','Portuguese','pt'); /* Query the values from the conquistador table. */ SELECT * FROM conquistador; |
When you call the script to load it, like:
\i testScript.sql |
It’ll display the following, which you can check against the strings in the VALUES clause above. There are encoding issues on lines 1, 2, 3, 5, 7, and 8 below.
conquistador_id | conquistador | actual_name | nationality | lang
-----------------+------------------------+----------------------+-------------+------
1 | Juan de Fuca | Ioánnis Fokás | Greek | el
2 | Nicolás de Federmán | Nikolaus Federmann | German | de
3 | Sebastián Caboto | Sebastiano Caboto | Venetian | it
4 | Jorge de la Espira | Georg von Speyer | German | de
5 | Eusebio Francisco Kino | Eusebius Franz Kühn | Italian | it
6 | Wenceslao Linck | Wenceslaus Linck | Bohemian | cs
7 | Fernando Consag | Ferdinand Konšcak | Croatian | sr
8 | Américo Vespucio | Amerigo Vespucci | Italian | it
9 | Alejo GarcÃa | Aleixo Garcia | Portuguese | pt
(9 rows) |
If you’re like me, it was annoying. The problem is that the native 2-byte Unicode of Microsoft sends values into PostgreSQL that are invalid. Those codes are read back with unintended values from other character encoding sets.
While you can’t set Windows generic encoding to 65001 without causing the system problems, you can set Active Console Code Page value in the scope of a Command-Line session before running the script.
The chcp command lets you set it to 4-byte Unicode, like:
chcp 65001 |
Now, rerun the script and PostgreSQL will display the correct character encoding set with some spacing irregularities. However, that’s not what’s important when you call table from another programming language through the ODBC-layer. The data will be returned in a 4-byte Unicode encoding stream.
conquistador_id | conquistador | actual_name | nationality | lang
-----------------+------------------------+----------------------+-------------+------
1 | Juan de Fuca | Ioánnis Fokás | Greek | el
2 | Nicolás de Federmán | Nikolaus Federmann | German | de
3 | Sebastián Caboto | Sebastiano Caboto | Venetian | it
4 | Jorge de la Espira | Georg von Speyer | German | de
5 | Eusebio Francisco Kino | Eusebius Franz Kühn | Italian | it
6 | Wenceslao Linck | Wenceslaus Linck | Bohemian | cs
7 | Fernando Consag | Ferdinand Konšcak | Croatian | sr
8 | Américo Vespucio | Amerigo Vespucci | Italian | it
9 | Alejo García | Aleixo Garcia | Portuguese | pt
(9 rows) |
A similar error to what I encountered testing MySQL Workbench’s ability to export SQL Server databases 10 years ago. I thought giving a solution to get coerce correct 4-byte Unicode data insertion may help those who also may be surprised by the behavior.
Oracle DSN Security
Oracle disallows entry of a password value when configuring the ODBC’s Windows Data Source Name (DSN) configurations. As you can see from the dialog’s options:
So, I check the Oracle ODBC’s property list with the following PowerShell command:
Get-Item -Path Registry::HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Oracle | Select-Object |
It returned:
Oracle Driver : C:\app\mclaughlinm\product\18.0.0\dbhomeXE\BIN\SQORA32.DLL
DisableRULEHint : T
Attributes : W
SQLTranslateErrors : F
LobPrefetchSize : 8192
AggregateSQLType : FLOAT
MaxTokenSize : 8192
FetchBufferSize : 64000
NumericSetting : NLS
ForceWCHAR : F
FailoverDelay : 10
FailoverRetryCount : 10
MetadataIdDefault : F
BindAsFLOAT : F
BindAsDATE : F
CloseCursor : F
EXECSchemaOpt :
EXECSyntax : F
Application Attributes : T
QueryTimeout : T
CacheBufferSize : 20
StatementCache : F
ResultSets : T
MaxLargeData : 0
UseOCIDescribeAny : F
Failover : T
Lobs : T
DisableMTS : T
DisableDPM : F
BatchAutocommitMode : IfAllSuccessful
Description : Oracle ODBC
ServerName : xe
Password :
UserID : c##student
DSN : Oracle |
Then, I used this PowerShell command to set the Password property:
Set-ItemProperty -Path Registry::HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Oracle -Name "Password" -Value 'student' |
After setting the Password property’s value, I queried it with the following PowerShell command:
Get-ItemProperty -Path Registry::HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Oracle | Select-Object -Property "Password" |
It returns:
Password : student |
After manually setting the Oracle ODBC DSN’s password value you can now connect without providing a password at runtime. It also means anybody who hacks the Windows environment can access the password through trivial PowerShell command.
I hope this alerts readers to a potential security risk when you use Oracle DSNs.
MySQL Windows DSN
Almost a Ripley’s Believe It or Not. An prior data science student told me that his new IT department setup a Windows component that let him connect his Excel Spreadsheets to their production MySQL database without a password. Intrigued, I asked if it was a MySQL Connector/ODBC Data Source Configuration, or DSN (Data Source Name)?
He wasn’t sure, so I asked him to connect to PowerShell and run the following command:
Get-Item -Path Registry::HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\MySQL |
It returned something like this (substituting output from one of my test systems):
Hive: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
Name Property
---- --------
MySQL Driver : C:\Program Files\MySQL\Connector ODBC 8.0\myodbc8w.dll
DESCRIPTION : MySQL ODBC Connector
SERVER : localhost
UID : student
PWD : student
DATABASE : studentdb
PORT : 3306 |
The student was stunned and concerned he was compromising his employer’s system security. I suggested he share the information with his IT department so they could provide a different approach for his access to the production database. His IT department immediately agreed. Unfortunately, he’s bummed he can’t simply access the data through Excel.
I told him they were welcome to use the MySQL Connect Dialog PowerShell solution that I wrote. It creates a minimal MySQL DSN and requires a manual password entry through the PowerShell Dialog box. I also suggested that they look into the PowerShell Excel Module.
I also suggested they develop a query only copy of the production database, or shift access to a data warehouse. Needless to say, it wasn’t a large corporation.
As always, I hope this helps others.
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.
Multidimension Arrays
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.
PL/pgSQL Array Listing
Somebody asked me how to navigate a collection in PostgreSQL’s PL/pgSQL and whether they supported table and varray data types, like Oracle’s PL/SQL. The most important thing to correct was that PostgreSQL supports only array types.
The only example that I found with a google search used a FOREACH-loop, like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DO $$ DECLARE /* An array of integers. */ list int[] = array[1,2,3,4,5]; /* Define a local variable for array members. */ i int; BEGIN /* Loop through the integers. */ FOREACH i IN ARRAY list LOOP RAISE NOTICE '[%]', i; END LOOP; END; $$; |
It prints:
NOTICE: [1] NOTICE: [2] NOTICE: [3] NOTICE: [4] NOTICE: [5] |
As I suspected the student didn’t want to use a FOREACH-loop. The student wanted to use a for-loop, which was much closer to the Oracle PL/SQL syntax with which they were most familiar. That example is:
1 2 3 4 5 6 7 8 9 10 11 12 | DO $$ DECLARE /* An array of integers. */ list int[] = array[1,2,3,4,5]; BEGIN /* Loop through the integers. */ FOR i IN 1..5 LOOP RAISE NOTICE '[%]', list[i]; END LOOP; END; $$; |
However, it’s bad form to use a literal for the upper number in a range for-loop, and you should use the CARDINALITY function in PostgreSQL because there is no collection API, like Oracle’s COUNT method. There is an ARRAY_LENGTH function but it’s really only necessary when you use a multidimensional array.
The modified code is:
1 2 3 4 5 6 7 8 9 10 11 12 | DO $$ DECLARE -- An array of integers. list int[] = array[1,2,3,4,5]; BEGIN /* Loop through the integers. */ FOR i IN 1..CARDINALITY(list) LOOP RAISE NOTICE '[%]', list[i]; END LOOP; END; $$; |
If you use the ARRAY_LENGTH function, line #8 would look like:
7 8 | /* Loop through the integers, and determines the length of the first dimension. */ FOR i IN 1..ARRAY_LENGTH(list,1) LOOP |
As always, I hope this helps those looking for a clear solution to basic activities.
Parameter Validation
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.
Oracle ODBC DSN
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.
Subscribe via RSS