Can't return the last known ID after an INSERT statement

I am trying to return the last inserted ID after running a MySQL INSERT statement with PowerShell. My INSERT works fine and the data is inserted into the database as expected. The issue I am having is grabbing the ID of that row that was just inserted.

My Code is below:

./MySQL.ps1 -Query "insert into table (field1,field2,field3,field4) values ('$val1','$val2','$val3','$val4')"
$last_id = ./MySQL.ps1 -Query "SELECT LAST_INSERT_ID()"
Write-Host "Last ID: $last_id"

returns:

Last ID: System.Data.DataRow

MySQL.ps1:

Param(
  [Parameter(
    Mandatory = $true,
    ParameterSetName = '',
    ValueFromPipeline = $true)]
  [string]$Query
)

$MySQLAdminUserName = 'root'
$MySQLAdminPassword = 'password'
$MySQLDatabase = 'storage_manager'
$MySQLHost = 'localhost'
$ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" +
                    $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword +
                    ";database=" + $MySQLDatabase

try {
  [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
  $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
  $Connection.ConnectionString = $ConnectionString
  $Connection.Open()

  $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
  $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
  $DataSet = New-Object System.Data.DataSet
  $RecordCount = $dataAdapter.Fill($dataSet, "data")
  $DataSet.Tables[0]
} catch {
  Write-Host "ERROR : Unable to run query : $query `n$Error[0]"
} finally {
  $Connection.Close()
}

I have also tried with the following and get the same info.

SELECT max(id) FROM mytab WHERE category = 1

Solution

Your function returns a DataTable object, not just a value. To get the value you need to select it via the field name (in your case the name of the SQL function):

$result  = ./MySQL.ps1 -Query "SELECT LAST_INSERT_ID()"
$last_id = $result.'LAST_INSERT_ID()'
Write-Host "Last ID: $last_id"

The quotes around LAST_INSERT_CD() in the second line are required, because the name of the property includes parentheses, and without quotes PowerShell would interpret LAST_INSERT_ID() as a method instead of a property. You can avoid the quotes by specifying an alias without parentheses (or other special characters):

$result  = ./MySQL.ps1 -Query "SELECT LAST_INSERT_ID() AS foo"
$last_id = $result.foo
Write-Host "Last ID: $last_id"