Calling stored procedure from Powershell using odbc issues - Forum - DataDirect Connect - Progress Community

Calling stored procedure from Powershell using odbc issues

 Forum

Calling stored procedure from Powershell using odbc issues

This question is not answered

Hi,

I hope this is the correct place for this post...

Anyway, I have a stored procedure that takes 3 input parameters. It then builds a temp table and etc.. Then does a select on the temp table.  

Now I have a powershell script that I am trying to get to connect to the stored procedure, pass in the parameters and get the selected results from the query.  I have been trying variuos approach and I am a bit stuck.. Any suggestions or documetation you can recommend on using the ddtek obbc in powershell?

Here is a bit of code (VERY ROUGH DRAFT... )

function Invoke-GetStoredProcedureIQDatabase{
# Get todays date.
# If todays date is less then July 1 then subtract 1 from current year and build PYXXXX
# If todays date is july 1 or greater then use current year and build PYXXXX.
$ErrorActionPreference = "Stop"


$today = Get-Date
$Global:PY = ($today).Year
$july1 = (Get-Date -Day 1 -Month 7)
if( $today -lt $july1) {
$Global:PY = [int]$Global:PY - 1
}

$dataset = New-Object System.Data.DataSet
$DbConn = New-Object System.Data.Odbc.OdbcConnection
$DbConn.ConnectionString = $Global:IqConnectionString

$DbConn.Open()

$DBCmd= $DbConn.CreateCommand()
$DbCmd.CommandTimeout = 3000
# supply the name of the stored procedure
$DBCmd.CommandText = "dbo.OBSWeekly"
$DBCmd.CommandType = [System.Data.CommandType]::StoredProcedure;

#now we have created the command and set it to be a stored procedure
#we now add the parameters to the stored procedures
$DBCmd.Parameters.Add("environment", [System.Data.SqlDbType]::VarChar) | out-null;
$DBCmd.Parameters["environment"].Direction = [System.Data.ParameterDirection]::Input;
$DBCmd.Parameters["environment"].Value = "DEV";

$DBCmd.Parameters.Add("dateFilter", [System.Data.SqlDbType]::VarChar) | out-null;
$DBCmd.Parameters["dateFilter"].Direction = [System.Data.ParameterDirection]::Input;
$DBCmd.Parameters["dateFilter"].Value = $today;

$DBCmd.Parameters.Add("programYear", [System.Data.SqlDbType]::VarChar) | out-null;
$DBCmd.Parameters["programYear"].Direction = [System.Data.ParameterDirection]::Input;
$DBCmd.Parameters["programYear"].Value ="PY$Global:PY";

THIS DOES NOT WORK... 
$adapter = New-Object System.Data.Odbc.OdbcDataAdapter $DBCmd
adapter.Fill($dataSet) | Out-Null


$DBConn.Close()
$DBConn.Dispose()

All Replies
  • Here is another approach...  

    Almost works, but having issues with building the string to execute correctly...

    function Invoke-GetStoredProcedureIQDatabase{

    # Get todays date.

    # If todays date is less then July 1 then subtract 1 from current year and build PYXXXX

    # If todays date is july 1 or greater then use current year and build PYXXXX.

       $ErrorActionPreference = "Stop"

       $today = Get-Date -format d

    $Global:PY = ($today).Year

    $july1 = (Get-Date -Day 1 -Month 7)

    if( $today -lt $july1) {

    $Global:PY = [int]$Global:PY - 1

    }

       $Environment = "DEV";

       $PY ="PY$Global:PY";

       $SqlToRun =  [string]::Format(“EXECUTE dbo.OBSWeekly ""{0}"",""{1}"",""{2}""”,$Environment,$today,$PY)

       WRITE-HOST $SqlToRun;

       $dataset = New-Object System.Data.DataSet

    $DbConn = New-Object System.Data.Odbc.OdbcConnection

    $DbConn.ConnectionString = $Global:IqConnectionString

    $DbConn.Open()

    $DBCmd= $DbConn.CreateCommand()

    $DbCmd.CommandTimeout = 3000

    $DBCmd.CommandText = $SqlToRun

    $adapter = New-Object System.Data.Odbc.OdbcDataAdapter $DBCmd

    $adapter.Fill($dataSet) | Out-Null

    $DBConn.Close()

    $DBConn.Dispose()

    return $dataset

  • Third approach...

           $conn = New-Object System.Data.Odbc.OdbcConnection

           $conn.ConnectionString = $Global:IqConnectionString

    $conn.Open()

    $cmd = $conn.CreateCommand()

    $cmd.CommandText = "dbo.OBSWeekly '$Environment', '$today', '$PY'"

    $adapter = New-Object System.Data.Odbc.OdbcDataAdapter($cmd)

    $dataset = New-Object System.Data.DataSet

    [void]$adapter.Fill($dataset)

    $dataset.tables[0]

    THE ERROR I AM GETTING:

    D:\TFS\PowerShell\RobinTeam\OBSWeekly\OBSWeekly.ps1 : Exception calling "Fill"

    with "1" argument(s): "ERROR [HY000] [Sybase][ODBC Driver][Adaptive Server

    Anywhere]Data conversion failed

    -- (dfe_HDBValue.cxx 351) "

       + CategoryInfo          : NotSpecified: (:) [OBSWeekly.ps1], MethodInvocat

      ionException

       + FullyQualifiedErrorId : OdbcException,OBSWeekly.ps1

    PS U:\>

  • Rose Marie,

    Based on the error message above, it appears that you are using the ODBC driver for Sybase ASE provided by SAP rather than a Sybase driver from Progress. According to our records you are licensed for the the Progress DataDirect 4.2 ADO.NET Provider for Sybase. Could you please modify your code to use the Progress Provider and reply with the results of your testing?

    Thanks,

    Brian

  • Do you have an example?

  • Hmmm, I am using a ODBC connection - Sysbase IQ jr

  • Please open a support case to get further assistance with this issue.

    Thanks,

    Brian