MacLochlainns Weblog

-- Conditionally drop the procedure.
DROP PROCEDURE IF EXISTS dropIndexIfExists;
 
-- Change the default semicolon delimiter to write a PSM
-- (Persistent Stored Module) or stored procedure.
DELIMITER $$
 
-- Create the procedure.
CREATE PROCEDURE dropIndexIfExists
( pv_table_name  VARCHAR(64)
, pv_index_name  VARCHAR(64))
BEGIN
 
  /* Declare a local variable for the SQL statement. */
  DECLARE stmt VARCHAR(1024);
 
  /* Set a session variable with two parameter markers. */
  SET @SQL := CONCAT('ALTER TABLE ',pv_table_name,'DROP INDEX ',pv_index_name);
 
  /* Check if the constraint exists. */
  IF EXISTS (SELECT NULL
             FROM   information_schema.statistics s
             WHERE  s.index_schema = database()
             AND    s.table_name = pv_table_name
             AND    s.index_name = pv_index_name)
  THEN
 
    /* Dynamically allocated and run statement. */
    PREPARE stmt FROM @SQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
 
END;
$$
 
-- Reset the default semicolon delimiter.
DELIMITER ;

CALL dropIndexIfExists('payment','idx_payment01');

// Declare a string and substring.
var myStr = 'Get me from the string.'
var mySubstr = 'me'
 
// Assign the substring to variable by rescuing it from the larger string.
var rescued = myStr.substring(myStr.indexOf(mySubstr),myStr.indexOf(mySubstr) + mySubstr.length)
 
// Print the result.
print(rescued)

mongo --nodb --norc < parsing.js

MongoDB shell version v4.0.20
me
bye

-- Stable testing scenario with table values requires a test table.
DROP TABLE IF EXISTS sample;
CREATE TABLE sample
( text  VARCHAR(100) );
 
-- Insert the literal string into the testing table.
INSERT INTO sample
( text )
VALUES
('Get me from the string.');
 
-- Test using a WITH clause to place a variable in context for use
-- in the query, relying on the fact that a Cartesian set of one
-- column and row becomes a new column in all rows of the other
-- table's set.
WITH struct AS
(SELECT 'me' AS result)
 SELECT SUBSTR(text,INSTR(text,struct.result),LENGTH(struct.result)) AS rescued
 FROM   sample CROSS JOIN struct;

+---------+
| rescued |
+---------+
| me      |
+---------+
1 row in set (0.00 sec)

CREATE TABLE exercise_4_2 AS
SELECT payment_id
,      customer_id
,      amount
,      payment_date
FROM   payment
WHERE  payment_id BETWEEN 101 AND 120;

CREATE VIEW exercise_4_2 AS
SELECT payment_id
,      customer_id
,      amount
,      payment_date
FROM   payment
WHERE  payment_id BETWEEN 101 AND 120;

SELECT *
FROM   exercise_4_2;

+------------+-------------+--------+---------------------+
| payment_id | customer_id | amount | payment_date        |
+------------+-------------+--------+---------------------+
|        101 |           4 |   8.99 | 2005-08-18 05:14:44 |
|        102 |           4 |   1.99 | 2005-08-19 02:19:13 |
|        103 |           4 |   2.99 | 2005-08-20 09:32:04 |
|        104 |           4 |   6.99 | 2005-08-20 12:55:40 |
|        105 |           4 |   4.99 | 2005-08-21 04:53:37 |
|        106 |           4 |   2.99 | 2005-08-22 13:58:23 |
|        107 |           4 |   1.99 | 2005-08-23 07:43:00 |
|        108 |           5 |   0.99 | 2005-05-29 07:25:16 |
|        109 |           5 |   6.99 | 2005-05-31 11:15:43 |
|        110 |           5 |   1.99 | 2005-05-31 19:46:38 |
|        111 |           5 |   3.99 | 2005-06-15 22:03:14 |
|        112 |           5 |   2.99 | 2005-06-16 08:01:02 |
|        113 |           5 |   4.99 | 2005-06-17 15:56:53 |
|        114 |           5 |   2.99 | 2005-06-19 04:20:13 |
|        115 |           5 |   4.99 | 2005-06-20 18:38:22 |
|        116 |           5 |   4.99 | 2005-07-06 09:11:58 |
|        117 |           5 |   2.99 | 2005-07-08 20:04:43 |
|        118 |           5 |   4.99 | 2005-07-09 01:57:57 |
|        119 |           5 |   5.99 | 2005-07-09 07:13:52 |
|        120 |           5 |   1.99 | 2005-07-09 08:51:42 |
+------------+-------------+--------+---------------------+
20 rows in set (0.26 sec)

/*
||  Function Name: update_membership
|| ----------------------------------------------------------------------
||  No return, treated as a procedure, or method returning a void.
|| ----------------------------------------------------------------------
*/
function update_membership(
    $h_account_number
  , $h_member_type
  , $h_credit_card_number
  , $h_credit_card_type
  , $account_number
  , $member_type
  , $credit_card_number
  , $credit_card_type
  , $h_first_name
  , $h_middle_name
  , $h_last_name
  , $h_contact_type
  , $first_name
  , $middle_name
  , $last_name
  , $contact_type) {
 
  // Include the credentials file if omitted.
  include_once("MySQLCredentials.inc");
 
  // Assign credentials to connection.
  $mysqli = new mysqli(HOSTNAME, USERNAME, PASSWORD, DATABASE);
 
  // Check for connection error and print message.
  if ($mysqli->connect_errno) {
    print $mysqli->connect_error."<br />";
    print "Connection not established ...<br />";
  }
  else {
    // Initial statement.
    $stmt = $mysqli->stmt_init();
 
    // Disable auto commit when you want two plus statements run.
    $mysqli->autocommit(FALSE);
 
    // Set the transaction guarantee.
    $mysqli->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);
 
    // Declare a static query.
    $sql = "UPDATE   member\n"
         . "SET      member_type = ?\n"
         . ",        credit_card_number = ?\n"
         . ",        credit_card_type = ?\n"
         . "WHERE    account_number = ?\n"
         . "AND      member_type = ?\n"
         . "AND      credit_card_number = ?\n"
         . "AND      credit_card_type = ?\n";
 
    // Prepare statement.
    if ($stmt->prepare($sql)) {
      $stmt->bind_param(
          "sssssss"
         , $member_type
         , $credit_card_number
         , $credit_card_type
         , $account_number
         , $h_member_type
         , $h_credit_card_number
         , $h_credit_card_type); } 
 
    // Attempt query and exit with failure before processing.
    if (!$stmt->execute()) {
 
      // Rollback or undo the transaction.
      $mysqli->rollback();
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />\n";
      print "Failed to resolve query ...<br />\n";
    }
 
    // Declare a static query.
    $sql = "UPDATE   contact\n"
         . "SET      first_name = ?\n"
         . ",        middle_name = ?\n"
         . ",        last_name = ?\n"
         . ",        contact_type = ?\n"
         . "WHERE    first_name = ?\n"
         . "AND      middle_name = ?\n"
         . "AND      last_name = ?\n"
         . "AND      contact_type = ?\n";
 
    // Prepare statement.
    if ($stmt->prepare($sql)) {
      $stmt->bind_param(
          "ssssssss"
        , $first_name
        , $middle_name
        , $last_name
        , $contact_type
        , $h_first_name
        , $h_middle_name
        , $h_last_name
        , $h_contact_type); } 
 
    // Attempt query and exit with failure before processing.
    if (!$stmt->execute()) {
 
      // Rollback or undo the transaction.
      $mysqli->rollback();
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />";
      print "Failed to resolve query ...<br />";
    }
    else {   
      /*  Manually commiting writes when you have disabled the
      ||  default auto commit setting, explained above.
      || ------------------------------------------------------------
      ||  You would add the following command to commit the 
      ||  transaction.
      ||  ------------------------------
      ||   $mysqli->commit();
      || ------------------------------------------------------------
      */
      $mysqli->commit();
 
      // Close the transaction.
      $mysqli->close();
    }
  }
}

DROP TABLE IF EXISTS transaction;
CREATE TABLE transaction
( transaction_id      int unsigned primary key auto_increment
, transaction_date    date
, transaction_amount  double );
 
INSERT INTO transaction
( transaction_date, transaction_amount )
VALUES
 ('2021-01-10', 56)
,('2021-02-14',23.02)
,('2021-03-31',31.06)
,('2021-01-01',.25)
,('2020-01-02', 52)
,('2020-02-08',22.02)
,('2020-03-26',32.06)
,('2020-01-12',.75);;

SELECT   EXTRACT(YEAR FROM transaction_date) AS "Year"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN transaction_amount
            END) AS "Jan"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 2 THEN transaction_amount
           END) AS "Feb"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 3 THEN transaction_amount
           END) AS "Mar"
,        SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) IN (1,2,3) THEN transaction_amount
           END) AS "1FQ"
FROM     transaction
WHERE    transaction_date BETWEEN '2020-01-15' AND '2021-03-15'
GROUP BY EXTRACT(YEAR FROM transaction_date)
ORDER BY 1;

+------+-------+-------+-------+-------+
| Year | Jan   | Feb   | Mar   | 1FQ   |
+------+-------+-------+-------+-------+
| 2020 |  NULL | 22.02 | 32.06 | 54.08 |
| 2021 | 56.25 | 23.02 |  NULL | 79.27 |
+------+-------+-------+-------+-------+
2 rows in set (0.02 sec)

SELECT   EXTRACT(YEAR FROM transaction_date) AS "Year"
,        CONCAT('$',LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN transaction_amount
             ELSE 0
            END),2),8,' ')) AS "Jan"
,        LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 2 THEN transaction_amount
             ELSE 0
           END),2),8,' ') AS "Feb"
,        LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) = 3 THEN transaction_amount
             ELSE 0
           END),2),8,' ') AS "Mar"
,        LPAD(FORMAT(SUM(
           CASE
             WHEN EXTRACT(MONTH FROM transaction_date) IN (1,2,3) THEN transaction_amount
             ELSE 0
           END),2),8,' ') AS "1FQ"
FROM     transaction
WHERE    transaction_date BETWEEN '2020-01-15' AND '2021-03-15'
GROUP BY EXTRACT(YEAR FROM transaction_date)
ORDER BY 1;

+------+-----------+----------+----------+----------+
| Year | Jan       | Feb      | Mar      | 1FQ      |
+------+-----------+----------+----------+----------+
| 2020 | $    0.00 |    22.02 |    32.06 |    54.08 |
| 2021 | $   56.25 |    23.02 |     0.00 |    79.27 |
+------+-----------+----------+----------+----------+
2 rows in set (0.01 sec)

-- -----------------------------------------------------------------
-- Drop the demonstration tables.
-- -----------------------------------------------------------------
DROP TABLE IF EXISTS left_table, right_table;
 
-- -----------------------------------------------------------------
-- Create left_table.
-- -----------------------------------------------------------------
CREATE TABLE left_table
( left_id        int unsigned primary key auto_increment
, leftstring     varchar(10));
 
-- -----------------------------------------------------------------
-- Create left_table.
-- -----------------------------------------------------------------
CREATE TABLE right_table
( right_id       int unsigned primary key auto_increment
, left_id        int unsigned
, rightstring         varchar(10));
 
-- -----------------------------------------------------------------
-- Insert five rows to the left table, which holds a 
-- left_id primary key column.
-- -----------------------------------------------------------------
INSERT INTO left_table (leftstring) values ('One');
INSERT INTO left_table (leftstring) values ('Two');
INSERT INTO left_table (leftstring) values ('Three');
INSERT INTO left_table (leftstring) values ('Four');
INSERT INTO left_table (leftstring) values ('Five');
 
-- -----------------------------------------------------------------
-- Delete row four to create a gap.
-- -----------------------------------------------------------------
DELETE FROM left_table where left_id = 4;
 
-- -----------------------------------------------------------------
--  Insert four rows, skipping a foreign key value for the 
--  left_id primary key value of 2.
-- -----------------------------------------------------------------
 
INSERT INTO right_table (rightstring,left_id) values ('One',1);
INSERT INTO right_table (rightstring,left_id) values ('Three',3);
INSERT INTO right_table (rightstring,left_id) values ('Four',4);
INSERT INTO right_table (rightstring,left_id) values ('Five',5);

SELECT l.left_id
,      l.leftstring
,      r.left_id
,      r.right_id
,      r.rightstring
FROM   left_table l INNER JOIN right_table r
ON     l.left_id = r.left_id;

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|       1 | One        |       1 |        1 | One         |
|       3 | Three      |       3 |        2 | Three       |
|       5 | Five       |       5 |        4 | Five        |
+---------+------------+---------+----------+-------------+
3 rows in set (0.00 sec)

SELECT l.left_id
,      l.leftstring
,      r.left_id
,      r.right_id
,      r.rightstring
FROM   left_table l LEFT JOIN right_table r
ON     l.left_id = r.left_id;

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|       1 | One        |       1 |        1 | One         |
|       2 | Two        |    NULL |     NULL | NULL        |
|       3 | Three      |       3 |        2 | Three       |
|       5 | Five       |       5 |        4 | Five        |
+---------+------------+---------+----------+-------------+
4 rows in set (0.00 sec)

FROM   left_table l LEFT JOIN right_table r
ON     l.left_id = r.left_id
WHERE  r.left_id IS NULL;

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|       2 | Two        |    NULL |     NULL | NULL        |
+---------+------------+---------+----------+-------------+
1 row in set (0.00 sec)

SELECT l.left_id
,      l.leftstring
,      r.left_id
,      r.right_id
,      r.rightstring
FROM   left_table l RIGHT JOIN right_table r
ON     l.left_id = r.left_id;

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|       1 | One        |       1 |        1 | One         |
|       3 | Three      |       3 |        2 | Three       |
|    NULL | NULL       |       4 |        3 | Four        |
|       5 | Five       |       5 |        4 | Five        |
+---------+------------+---------+----------+-------------+
4 rows in set (0.00 sec)

FROM   left_table l LEFT JOIN right_table r
ON     l.left_id = r.left_id
WHERE  l.left_id IS NULL;

+---------+------------+---------+----------+-------------+
| left_id | leftstring | left_id | right_id | rightstring |
+---------+------------+---------+----------+-------------+
|    NULL | NULL       |       4 |        3 | Four        |
+---------+------------+---------+----------+-------------+
1 row in set (0.00 sec)

WITH cte AS
(SELECT l.left_id AS primary_left_id
,      l.leftstring
,      r.left_id  AS foreign_left_id
,      r.right_id
,      r.rightstring
FROM   left_table l LEFT JOIN right_table r
ON     l.left_id = r.left_id
UNION
SELECT l.left_id  AS primary_left_id
,      l.leftstring
,      r.left_id  AS foreign_left_id
,      r.right_id
,      r.rightstring
FROM   left_table l RIGHT JOIN right_table r
ON     l.left_id = r.left_id)
SELECT * FROM cte
ORDER BY
CASE
  WHEN 'One'   IN (leftstring, rightstring) THEN 1
  WHEN 'Two'   IN (leftstring, rightstring)  THEN 2
  WHEN 'Three' IN (leftstring, rightstring)  THEN 3
  WHEN 'Four'  IN (leftstring, rightstring)  THEN 4
  WHEN 'Five'  IN (leftstring, rightstring)  THEN 5
END;

+-----------------+------------+-----------------+----------+-------------+
| primary_left_id | leftstring | foreign_left_id | right_id | rightstring |
+-----------------+------------+-----------------+----------+-------------+
|               1 | One        |               1 |        1 | One         |
|               2 | Two        |            NULL |     NULL | NULL        |
|               3 | Three      |               3 |        2 | Three       |
|            NULL | NULL       |               4 |        3 | Four        |
|               5 | Five       |               5 |        4 | Five        |
+-----------------+------------+-----------------+----------+-------------+
5 rows in set (0.00 sec)

SELECT * FROM cte
WHERE  primary_left_id IS NULL
OR     foreign_left_id IS NULL

+-----------------+------------+-----------------+----------+-------------+
| primary_left_id | leftstring | foreign_left_id | right_id | rightstring |
+-----------------+------------+-----------------+----------+-------------+
|               2 | Two        |            NULL |     NULL | NULL        |
|            NULL | NULL       |               4 |        3 | Four        |
+-----------------+------------+-----------------+----------+-------------+
2 rows in set (0.00 sec)

  WHEN leftstring = 'One'   OR rightstring = 'One'   THEN 1

  WHEN 'One'   IN (leftstring, rightstring) THEN 1

CREATE TABLE actor
( actor_id    int unsigned primary key auto_increment
, first_name  varchar(30) not null
, last_name   varchar(30) not null
, CONSTRAINT  actor_uq UNIQUE (first_name, last_name));

INSERT INTO actor
( first_name
, last_name )
VALUES
 ('Harrison','Ford')
,('Carrie','Fisher')
,('Mark','Hamill')
,('Alec','Guinness');

INSERT INTO actor
(first_name, last_name)
VALUES
('Harrison','Ford');

ERROR 1062 (23000): Duplicate entry 'Harrison-Ford' for key 'actor.actor_uq'

INSERT INTO actor
SET first_name = 'Tom'
,   last_name  = 'Hanks';

SELECT   *
FROM     actor
ORDER BY actor_id;

+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
|        1 | Harrison   | Ford      |
|        2 | Carrie     | Fisher    |
|        3 | Mark       | Hamill    |
|        4 | Alec       | Guinness  |
|        6 | Tom        | Hanks     |
+----------+------------+-----------+
5 rows in set (0.01 sec)

C:\Data\cit225\mysql\ps\mod

function Get-Credentials {
 
  # Add libraries for form components.
  Add-Type -AssemblyName System.Windows.Forms
  Add-Type -AssemblyName System.Drawing
 
  # Define a user credential form.
  $form = New-Object System.Windows.Forms.Form
  $form.Text = 'User Credential Form'
  $form.Size = New-Object System.Drawing.Size(300,240)
  $form.StartPosition = 'CenterScreen'
 
  # Define a button and assign it and its controls to a form.
  $loginButton = New-Object System.Windows.Forms.Button
  $loginButton.Location = New-Object System.Drawing.Point(60,160)
  $loginButton.Size = New-Object System.Drawing.Size(75,23)
  $loginButton.Text = 'Login'
  $loginButton.DialogResult = [System.Windows.Forms.DialogResult]::OK
  $form.AcceptButton = $loginButton
  $form.Controls.Add($loginButton)
 
  # Define a button and assign it and its controls to a form.
  $cancelButton = New-Object System.Windows.Forms.Button
  $cancelButton.Location = New-Object System.Drawing.Point(155,160)
  $cancelButton.Size = New-Object System.Drawing.Size(75,23)
  $cancelButton.Text = 'Cancel'
  $cancelButton.DialogResult = [System.Windows.Forms.DialogResult]::Cancel
  $form.CancelButton = $cancelButton
  $form.Controls.Add($cancelButton)
 
  # Define a label and assign it and its controls to a form.
  $userLabel = New-Object System.Windows.Forms.Label
  $userLabel.Location = New-Object System.Drawing.Point(30,15)
  $userLabel.Size = New-Object System.Drawing.Size(100,20)
  $userLabel.Text = 'Enter User Name:'
  $form.Controls.Add($userLabel)
 
  # Define a TextBox and assign it and its controls to a form.
  $userTextBox = New-Object System.Windows.Forms.TextBox
  $userTextBox.Location = New-Object System.Drawing.Point(140,15)
  $userTextBox.Size = New-Object System.Drawing.Size(100,20)
  $form.Controls.Add($userTextBox)
 
  # Define a label and assign it and its controls to a form.
  $pwdLabel = New-Object System.Windows.Forms.Label
  $pwdLabel.Location = New-Object System.Drawing.Point(30,40)
  $pwdLabel.Size = New-Object System.Drawing.Size(100,20)
  $pwdLabel.Text = 'Enter Password:'
  $form.Controls.Add($pwdLabel)
 
  # Define a TextBox and assign it and its controls to a form.
  $pwdTextBox = New-Object System.Windows.Forms.TextBox
  $pwdTextBox.Location = New-Object System.Drawing.Point(140,40)
  $pwdTextBox.Size = New-Object System.Drawing.Size(100,20)
  $pwdTextBox.PasswordChar = "*"
  $form.Controls.Add($pwdTextBox)
 
  # Define a label and assign it and its controls to a form.
  $hostLabel = New-Object System.Windows.Forms.Label
  $hostLabel.Location = New-Object System.Drawing.Point(30,65)
  $hostLabel.Size = New-Object System.Drawing.Size(100,20)
  $hostLabel.Text = 'Enter Hostname:'
  $form.Controls.Add($hostLabel)
 
  # Define a TextBox and assign it and its controls to a form.
  $hostTextBox = New-Object System.Windows.Forms.TextBox
  $hostTextBox.Location = New-Object System.Drawing.Point(140,65)
  $hostTextBox.Size = New-Object System.Drawing.Size(100,20)
  $form.Controls.Add($hostTextBox)
 
  # Define a label and assign it and its controls to a form.
  $portLabel = New-Object System.Windows.Forms.Label
  $portLabel.Location = New-Object System.Drawing.Point(30,90)
  $portLabel.Size = New-Object System.Drawing.Size(100,20)
  $portLabel.Text = 'Enter Port #:'
  $form.Controls.Add($portLabel)
 
  # Define a TextBox and assign it and its controls to a form.
  $portTextBox = New-Object System.Windows.Forms.TextBox
  $portTextBox.Location = New-Object System.Drawing.Point(140,90)
  $portTextBox.Size = New-Object System.Drawing.Size(100,20)
  $form.Controls.Add($portTextBox)
 
  # Define a label and assign it and its controls to a form.
  $dbLabel = New-Object System.Windows.Forms.Label
  $dbLabel.Location = New-Object System.Drawing.Point(30,115)
  $dbLabel.Size = New-Object System.Drawing.Size(100,20)
  $dbLabel.Text = 'Enter DB Name:'
  $form.Controls.Add($dbLabel)
 
  # Define a TextBox and assign it and its controls to a form.
  $dbTextBox = New-Object System.Windows.Forms.TextBox
  $dbTextBox.Location = New-Object System.Drawing.Point(140,115)
  $dbTextBox.Size = New-Object System.Drawing.Size(100,20)
  $form.Controls.Add($dbTextBox)
 
  $form.Topmost = $true
 
  $form.Add_Shown({$userTextBox.Select()})
  $result = $form.ShowDialog()
 
  if ($result -eq [System.Windows.Forms.DialogResult]::OK) {
 
    # Assign inputs to connection variables.
    $uid = $userTextBox.Text
    $pwd = $pwdTextBox.Text
    $server = $hostTextBox.Text
    $port= $portTextBox.Text
    $dbName = $dbTextBox.Text
 
    # Declare connection string.
    $credentials = 'server=' + $server +
                   ';port=' + $port +
                   ';uid=' + $uid +
                   ';pwd=' + $pwd +
                   ';database=' + $dbName
  }
  else {
    $credentials = $null
  }
 
  return $credentials
}

# Import your custom module.
Import-Module Get-Credentials
 
# Test the Get-Credentials function.
if (($credentials = Get-Credentials) -ne $undefinedVariable) {
  Write-Host($credentials)
}

powershell .\test.ps1

server=localhost;port=3306;uid=student;pwd=student;database=studentdb

-- Create the avenger table.
CREATE TABLE db_connect
( db_connect_id  INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, version        VARCHAR(10)
, user           VARCHAR(24)
, db_name        VARCHAR(10));
 
-- Seed the avenger table with data.
INSERT INTO avenger
( first_name, last_name, avenger )
VALUES
 ('Anthony', 'Stark', 'Iron Man')
,('Thor', 'Odinson', 'God of Thunder')
,('Steven', 'Rogers', 'Captain America')
,('Bruce', 'Banner', 'Hulk')
,('Clinton', 'Barton', 'Hawkeye')
,('Natasha', 'Romanoff', 'Black Widow')
,('Peter', 'Parker', 'Spiderman')
,('Steven', 'Strange', 'Dr. Strange')
,('Scott', 'Lange', 'Ant-man');

# Import your custom module.
Import-Module Get-Credentials
 
# Test the Get-Credentials function.
if (($credentials = Get-Credentials) -ne $undefinedVariable) {
 
  # Connect to the libaray MySQL.Data.dll
  Add-Type -Path 'C:\Program Files (x86)\MySQL\Connector NET 8.0\Assemblies\v4.5.2\MySql.Data.dll'
 
  # Create a MySQL Database connection variable that qualifies:
  # [Driver]@ConnectionString
  # ============================================================
  #  You can assign the connection string before using it or
  #  while using it, which is what we do below by assigning
  #  literal values for the following names:
  #   - server=<ip_address> or 127.0.0.1 for localhost
  #   - uid=<user_name>
  #   - pwd=<password>
  #   - port=<port#> or 3306 for default port
  #   - database=<database_name>
  # ============================================================
  $Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString=$credentials}
  $Connection.Open()
 
  # Define a MySQL Command Object for a non-query.
  $sqlCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
  $sqlDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter
  $sqlDataSet = New-Object System.Data.DataSet
 
  # Assign the connection and command text to the MySQL command object.
  $sqlCommand.Connection = $Connection
  $sqlCommand.CommandText = 'SELECT CONCAT(first_name," ",last_name) AS full_name ' +
                            ',      avenger ' +
                            'FROM   avenger'
 
  # Assign the connection and command text to the query method of
  # the data adapter object.
  $sqlDataAdapter.SelectCommand=$sqlCommand
 
  # Assign the tuples of data to a data set and return the number of rows fetched.
  $rowsFetched=$sqlDataAdapter.Fill($sqlDataSet, "data")
 
  # Print to console the data returned from the query.
  foreach($row in $sqlDataSet.tables[0]) {
    write-host "Avenger:" $row.avenger "is" $row.full_name }
 
  # Close the MySQL connection.
  $Connection.Close()
}

Avenger: Iron Man is Anthony Stark
Avenger: God of Thunder is Thor Odinson
Avenger: Captain America is Steven Rogers
Avenger: Hulk is Bruce Banner
Avenger: Hawkeye is Clinton Barton
Avenger: Black Widow is Natasha Romanoff
Avenger: Spiderman is Peter Parker
Avenger: Dr. Strange is Steven Strange
Avenger: Ant-man is Scott Lange

# Add libraries for form components.
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
 
# Define a user credential form.
$form = New-Object System.Windows.Forms.Form
$form.Text = 'User Credential Form'
$form.Size = New-Object System.Drawing.Size(300,240)
$form.StartPosition = 'CenterScreen'
 
# Define a button and assign it and its controls to a form.
$loginButton = New-Object System.Windows.Forms.Button
$loginButton.Location = New-Object System.Drawing.Point(60,160)
$loginButton.Size = New-Object System.Drawing.Size(75,23)
$loginButton.Text = 'Login'
$loginButton.DialogResult = [System.Windows.Forms.DialogResult]::OK
$form.AcceptButton = $loginButton
$form.Controls.Add($loginButton)
 
# Define a button and assign it and its controls to a form.
$cancelButton = New-Object System.Windows.Forms.Button
$cancelButton.Location = New-Object System.Drawing.Point(155,160)
$cancelButton.Size = New-Object System.Drawing.Size(75,23)
$cancelButton.Text = 'Cancel'
$cancelButton.DialogResult = [System.Windows.Forms.DialogResult]::Cancel
$form.CancelButton = $cancelButton
$form.Controls.Add($cancelButton)
 
# Define a label and assign it and its controls to a form.
$userLabel = New-Object System.Windows.Forms.Label
$userLabel.Location = New-Object System.Drawing.Point(30,15)
$userLabel.Size = New-Object System.Drawing.Size(100,20)
$userLabel.Text = 'Enter User Name:'
$form.Controls.Add($userLabel)
 
# Define a TextBox and assign it and its controls to a form.
$userTextBox = New-Object System.Windows.Forms.TextBox
$userTextBox.Location = New-Object System.Drawing.Point(140,15)
$userTextBox.Size = New-Object System.Drawing.Size(100,20)
$form.Controls.Add($userTextBox)
 
# Define a label and assign it and its controls to a form.
$pwdLabel = New-Object System.Windows.Forms.Label
$pwdLabel.Location = New-Object System.Drawing.Point(30,40)
$pwdLabel.Size = New-Object System.Drawing.Size(100,20)
$pwdLabel.Text = 'Enter Password:'
$form.Controls.Add($pwdLabel)
 
# Define a TextBox and assign it and its controls to a form.
$pwdTextBox = New-Object System.Windows.Forms.TextBox
$pwdTextBox.Location = New-Object System.Drawing.Point(140,40)
$pwdTextBox.Size = New-Object System.Drawing.Size(100,20)
$pwdTextBox.PasswordChar = "*"
$form.Controls.Add($pwdTextBox)
 
# Define a label and assign it and its controls to a form.
$hostLabel = New-Object System.Windows.Forms.Label
$hostLabel.Location = New-Object System.Drawing.Point(30,65)
$hostLabel.Size = New-Object System.Drawing.Size(100,20)
$hostLabel.Text = 'Enter Hostname:'
$form.Controls.Add($hostLabel)
 
# Define a TextBox and assign it and its controls to a form.
$hostTextBox = New-Object System.Windows.Forms.TextBox
$hostTextBox.Location = New-Object System.Drawing.Point(140,65)
$hostTextBox.Size = New-Object System.Drawing.Size(100,20)
$form.Controls.Add($hostTextBox)
 
# Define a label and assign it and its controls to a form.
$portLabel = New-Object System.Windows.Forms.Label
$portLabel.Location = New-Object System.Drawing.Point(30,90)
$portLabel.Size = New-Object System.Drawing.Size(100,20)
$portLabel.Text = 'Enter Port #:'
$form.Controls.Add($portLabel)
 
# Define a TextBox and assign it and its controls to a form.
$portTextBox = New-Object System.Windows.Forms.TextBox
$portTextBox.Location = New-Object System.Drawing.Point(140,90)
$portTextBox.Size = New-Object System.Drawing.Size(100,20)
$form.Controls.Add($portTextBox)
 
# Define a label and assign it and its controls to a form.
$dbLabel = New-Object System.Windows.Forms.Label
$dbLabel.Location = New-Object System.Drawing.Point(30,115)
$dbLabel.Size = New-Object System.Drawing.Size(100,20)
$dbLabel.Text = 'Enter DB Name:'
$form.Controls.Add($dbLabel)
 
# Define a TextBox and assign it and its controls to a form.
$dbTextBox = New-Object System.Windows.Forms.TextBox
$dbTextBox.Location = New-Object System.Drawing.Point(140,115)
$dbTextBox.Size = New-Object System.Drawing.Size(100,20)
$form.Controls.Add($dbTextBox)
 
$form.Topmost = $true
 
$form.Add_Shown({$userTextBox.Select()})
$result = $form.ShowDialog()
 
if ($result -eq [System.Windows.Forms.DialogResult]::OK) {
 
  # Assign inputs to connection variables.
  $uid = $userTextBox.Text
  $pwd = $pwdTextBox.Text
  $server = $hostTextBox.Text
  $port= $portTextBox.Text
  $dbName = $dbTextBox.Text
 
  # Declare connection string.
  $credentials = 'server=' + $server +
                 ';port=' + $port +
		 ';uid=' + $uid + 
		 ';pwd=' + $pwd + 
		 ';database=' + $dbName
 
  # Connect to the libaray MySQL.Data.dll
  Add-Type -Path 'C:\Program Files (x86)\MySQL\Connector NET 8.0\Assemblies\v4.5.2\MySql.Data.dll'
 
  # Create a MySQL Database connection variable that qualifies:
  # [Driver]@ConnectionString
  # ============================================================
  #  You can assign the connection string before using it or
  #  while using it, which is what we do below by assigning
  #  literal values for the following names:
  #   - server=<ip_address> or 127.0.0.1 for localhost
  #   - port=<port #>
  #   - uid=<user_name>
  #   - pwd=<password>
  #   - database=<database_name>
  # ============================================================
  $Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString=$credentials}
  $Connection.Open()
 
  # Define a MySQL Command Object for a non-query.
  $sqlCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
  $sqlDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter
  $sqlDataSet = New-Object System.Data.DataSet
 
  # Assign the connection and command text to the MySQL command object.
  $sqlCommand.Connection = $Connection
  $sqlCommand.CommandText = 'SELECT CONCAT(first_name," ",last_name) AS full_name ' +
                            ',      avenger ' +
                            'FROM   avenger'
 
  # Assign the connection and command text to the query method of
  # the data adapter object.
  $sqlDataAdapter.SelectCommand=$sqlCommand
 
  # Assign the tuples of data to a data set and return the number of rows fetched.
  $rowsFetched=$sqlDataAdapter.Fill($sqlDataSet, "data")
 
  # Print to console the data returned from the query.
  foreach($row in $sqlDataSet.tables[0]) {
    write-host "Avenger:" $row.avenger "is" $row.full_name }
 
  # Close the MySQL connection.
  $Connection.Close()
}

-- Create the avenger table.
CREATE TABLE db_connect
( db_connect_id  INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, version        VARCHAR(10)
, user           VARCHAR(24)
, db_name        VARCHAR(10));
 
-- Seed the avenger table with data.
INSERT INTO avenger
( first_name, last_name, avenger )
VALUES
 ('Anthony', 'Stark', 'Iron Man')
,('Thor', 'Odinson', 'God of Thunder')
,('Steven', 'Rogers', 'Captain America')
,('Bruce', 'Banner', 'Hulk')
,('Clinton', 'Barton', 'Hawkeye')
,('Natasha', 'Romanoff', 'Black Widow');

powershell .\staticQuery.ps1

Avenger: Iron Man is Anthony Stark
Avenger: God of Thunder is Thor Odinson
Avenger: Captain America is Steven Rogers
Avenger: Hulk is Bruce Banner
Avenger: Hawkeye is Clinton Barton
Avenger: Black Widow is Natasha Romanoff