Search code examples
powershellodbcintersystems

InterSystems ODBC error crashes Powershell

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.

Solution

  • 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 )