1

I need to read data from mysql database with pwsh 7. On powershell 5 it is working fine but with pwsh 7 not with , and receive an error message when querying a table with 'timestamp' data :

"format-default: Unable to cast object of type 'MySql.Data.Types.MySqlDateTime' to type 'System.IFormattable'."

 [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")
        $connStr = "server=" + $MySQLHost + ";port=" + $MySQLPort + ";uid=" + $user + ";pwd=" + $pass + ";database=" + $MYSQLDatabase + ";Pooling=FALSE;Allow Zero Datetime=true;Allow User Variables=True;Connect Timeout=60"
        $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
        $conn.Open()
        New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn)
        
    $query = "select Name, InputDate from ENVIRONMENT"
    $cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $conn)    
    $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd)
    $dataSet = New-Object System.Data.DataSet
    $dataAdapter.Fill($dataSet, "data")
    $cmd.Dispose() 
    
    $dataSet.Tables["data"]

So I can see that after "$dataAdapter.Fill($dataSet, "data")" data are collected from mysql because it return the number of row. But after "$dataSet.Tables["data"]" it return the error.

In powershell 5 it is working fine.

Any help :-) ?

Thanks

kent2004
  • 59
  • 6
  • 1
    It is the same, pwsh cannot cast the object DatTime ConvertTo-Json: Unable to cast object of type 'MySql.Data.Types.MySqlDateTime' to type 'System.IFormattable'. – kent2004 Jul 31 '22 at 13:35
  • I thing I understood what you explain. I did that but it convert everything into string unfortunatelly, I don't know how to do better $members = $($dataSet.Tables["data"] | Get-Member -MemberType Property).Name $d = @() foreach($data in $dataSet.Tables["data"]){ $psObj = New-Object -TypeName PSObject foreach($memberName in $members ){ $psObj | Add-Member -MemberType NoteProperty -Name $memberName -Value $data.$memberName.tostring() } $d += $psObj } return $d – kent2004 Aug 01 '22 at 08:42
  • I have measure the time it takes to do what I proposed in my previous comment, and it is very slow compare to the regular 'return $dataSet.Tables["data"] '. Do you know another way to do such dataset that can convert MysqlDateTime ? I'm a bit confuse because in pwsh 5 it can convert, but not with pwsh 7 – kent2004 Aug 03 '22 at 08:24

1 Answers1

0

If your code works in Windows PowerShell, but not in PowerShell (Core) 7+, I encourage you to report an issue in the PowerShell GitHub repo.

However, you should first make sure that you're loading the .NET (Core)-appropriate version of the assembly (MySql.Data) from the MySql.Data NuGet package, given that [System.Reflection.Assembly]::LoadWithPartialName("MySql.Data") may be loading an older, .NET Framework-only version from the GAC (Global Assembly Cache), as would the equivalent - and preferable - form Add-Type -AssemblyName MySql.Data.

Unfortunately, using NuGet packages isn't well supported in PowerShell as of version 7.2.x:

Some thoughts about the error and a potential - slow - workaround:

MySql.Data.Types.MySqlDateTime indeed does not implement the System.IFormattable interface. It's unclear to me why this cast is even attempted or - in case the attempt is by design - why the exception isn't caught.

When PowerShell stringifies objects, it checks for whether they implement the IFormattable, so that a culture-invariant string representation can be requested. Conceivably, the bug has to do with mistakenly believing MySqlDateTime to implement this interface.

MySqlDateTime does have an explicit conversion operator to .NET's [datetime] (System.DateTime) type, which you should be able to use from PowerShell with a [datetime] cast.

Thus, the following approach may work as a workaround:

  • For a given table's rows, replace the MySqlDateTime-typed properties (columns) with properties that explicitly convert the values to [datetime], with the help of Select-Object and calculated properties.

  • Note that such an approach will be slow, because a new object must be constructed for each row, which also means that the type identity of the rows will be lost.

Note: The following is a simplified, self-contained example that simulates your MySQL data types; for simplicity, type [string] is used in lieu of [MySql.Data.Types.MySqlDateTime] to modify the columns of interest with an explicit [datetime] cast - adapt accordingly.

# These sample rows simulate the following:
#   $rows = $dataSet.Tables["data"]
$rows = @(
  [pscustomobject] @{ 
    foo = 1; 
    bar = '1970/01/01'
  },
  [pscustomobject] @{ 
    foo = 2; 
    bar = '1970/01/02'
  }
)

# Get the first row, so the data types of its properties (columns)
# can be analyzed.
# (I assume there's a way to do this directly via a table object,
#  and its column definitions, but I'm not familiar with the MySQL .NET API.)
$firstRow = $rows | Select-Object -First 1

# Construct an array of property names / calculated properties to pass
# to Select-Object below.
$propArray = foreach ($prop in $firstRow.psobject.Properties) {
  if ($prop.Value -is [string]) { # Substitute [MySql.Data.Types.MySqlDateTime] here.
    @{
      Name = $prop.Name
      Expression = [scriptblock]::Create("[datetime] `$_.$($prop.Name)")
    }
  }
  else {
    $prop.Name
  }
}

# Now output all rows with the [MySqlDateTime] columns (properties)
# converted to [datetime].
$rows | Select-Object $propArray
mklement0
  • 382,024
  • 64
  • 607
  • 775