I'm trying to take some data through InterSystems ODBC driver with Powershell, but there is an error from the query execution (01004 "String data, right-truncated"). I want to ignore that (or handle this error in PS) and keep the script moving on. Here is the part of my code:
$c = new-object system.data.odbc.odbcconnection
$c.connectionstring = "..."
$c.open()
$cmd = New-object System.Data.Odbc.OdbcCommand( $sql , $c )
$dst = New-Object System.Data.DataSet
$oda = New-Object System.Data.Odbc.OdbcDataAdapter( $cmd )
$oda.Fill( $dst )
This works, i.e., doesn't crash and take the data, when I'm running the script with -NoExit parameter, i.e.:
Powershell -NoExit Y:\test.ps1
Otherways, the Powershell crashes, and the console closes. I need to run this script from another script and eventually receive the result. In the end, I want to close the console window (now I invoke it by exit).
I tried the try-catch block, throws, Invoke-Expression in a few ways, functions with return, etc., as far. I searched for a solution to handle such a type of error, but no result.
I have found the solution (or maybe the workaround). It is better to use ADODB objects instead of System.Data.Odbc. The most straightforward code looks as follows:
$connection = New-Object -ComObject ADODB.Connection
$command = New-Object -ComObject ADODB.Command
$recordset = New-Object -ComObject ADODB.Recordset
$connection.CursorLocation = 3
$connection.ConnectionString = $c_s
$connection.Open()
$command.ActiveConnection = $connection
$command.CommandText = $sql
$command.CommandType = 1
$recordset.ActiveConnection = $connection
$recordset.Open( $command )