DEV/OP: Execute Stored Procedure against database using Powershell and ODBC

I had a need to execute a stored procedure stored in a ASE IQ database version 12. (VERY OLD).

I could only use a ODBC command…

Here is an example powershell example

param(
 [parameter(Mandatory=$true)][string]$Environment= "DEV"
)

$SybaseLogin = ""
$SybasePassword = ""
$IQLogin = ""
$IQPassword = ""
$EmailRecipients = "email"

# Determine script location for PowerShell
$PowerShellDir = Split-Path $script:MyInvocation.MyCommand.Path
Write-Host "Current Powershell script directory is $PowerShellDir"

$DllFullPath = "C:\Program Files\DataDirect\Connect for ADO.NET 3.3\DDTek.Sybase.dll"#$PowerShellDir + "\DDTek.Sybase.dll"
$Global:IqConnectionString = ""
$Global:SybaseConnectionString = ""
$Global:PY = ""
$Global:ObsDate = ""
$Global:SybaseData = @()

#============================================================================
# Initialize-SqlModule
#
# This method will set up all db connections
#============================================================================
function Initialize-SqlModule {

 param(
 [Parameter(Mandatory=$true)][string] $DbEnv,
 [Parameter(Mandatory=$true)][string] $DbSybaseLogin,
 [Parameter(Mandatory=$true)][string] $DbSybasePassword,
 [Parameter(Mandatory=$true)][string] $DbIQLogin,
 [Parameter(Mandatory=$true)][string] $DbIQPassword,
 [Parameter(Mandatory=$true)][string] $PowerShellDir
 )

 try {
 [reflection.assembly]::loadfile($DllFullPath)
 }
 catch [Exception] {
 Write-Host "Error loading sybase dll. Check location on next line."
 Write-Host $DllFullPath
 Write-Host $_.Exception.Message
 return "Error"
 }

 switch ( $DbEnv)
 {
 "DEV"
 {
 $Global:IqConnectionString = ""
 $Global:SybaseConnectionString = ""
 }
 "TAC"
 {
 $Global:IqConnectionString = " "
 $Global:SybaseConnectionString = " "
 }
 "PROD"
 {
 $Global:IqConnectionString = " "
 $Global:SybaseConnectionString = " "
 }
 default
 {
 Write-Host "Environment $Environment is not a valid parameter"
 exit -1
 }
 }
 return "OK"
}


#============================================================================
# Invoke-GetIqDataTables
#============================================================================
function Invoke-GetIqDataTables {
 param(
 [Parameter(Mandatory=$true)][string] $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
}


#============================================================================
# Invoke-GetIqDataTables
#============================================================================
function Invoke-GetSybaseDataTables {
 param(
 [Parameter(Mandatory=$true)][string] $SqlToRun
 )

 $dataset = New-Object System.Data.DataSet

 $DbConn = New-Object DDTek.Sybase.SybaseConnection
 $DbConn.ConnectionString = $Global:SybaseConnectionString
 $DbConn.Open()
 $DBCmd= $DbConn.CreateCommand()
 $DbCmd.CommandTimeout = 3000
 $DBCmd.CommandText = $SqlToRun
 $adapter = New-Object DDTek.Sybase.SybaseDataAdapter $DBCmd
 $adapter.SelectCommand.Connection = $DbConn
 $adapter.Fill($dataSet) | Out-Null
 $DBConn.Close()
 $DBConn.Dispose()

 return $dataset
}


#============================================================================
# New-IqWeeklyOBS
#============================================================================
function New-IqWeeklyOBS {

 # 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.
 $today =(Get-date).AddDays(-1).AddYears(-1).ToString("dd/mm/yyyy")
 $Global:PY = (Get-date).Year
 $july1 = (Get-Date -Day 1 -Month 7)
 if( $today -lt $july1) {
 $Global:PY = [int]$Global:PY - 1
 }

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


 #$SqlToRun = " declare @obs_dt varchar(10) "
 #$SqlToRun += " select @obs_dt = '$Global:ObsDate' "

 #$SqlToRun += " select c.ctr_shname, 0 as avg_act_obs ,convert(numeric(10,2), (ISNULL(cdo.planned_male_res,0) + ISNULL(cdo.planned_male_non_res,0) + ISNULL(cdo.planned_female_res,0) + ISNULL(cdo.planned_female_non_res,0))) as plan_obs "
 #$SqlToRun += " from ctr_daily_obs cdo, center c "
 #$SqlToRun += " where c.ctr_id = cdo.ctr_id and cdo.obs_dt = Convert(DATE,'01/01/2016',101) "
 #$SqlToRun += " group by c.ctr_shname,cdo.planned_female_res, cdo.planned_female_non_res, cdo.planned_male_non_res, cdo.planned_male_res "
 #$SqlToRun += " order by c.ctr_shname "

 Write-Host $SqlToRun;

 $FoundTables = Invoke-GetIqDataTables $SqlToRun
 $IqData = @()

 ForEach ( $oneRow in $FoundTables[0].Tables.Rows)
 {
 $ObjectRow = New-Object System.Object
 $ObjectRow | Add-Member -type NoteProperty -Name ctr_shname -Value $oneRow.ctr_shname
 $ObjectRow | Add-Member -type NoteProperty -Name avg_act_obs -Value $oneRow.avg_Act_obs
 $ObjectRow | Add-Member -type NoteProperty -Name plan_obs -Value $oneRow.plan_obs
 $IqData += $ObjectRow
 }

 return $IqData
}


#============================================================================
#
# Execution Starts
#
#============================================================================
$Result = Initialize-SqlModule $Environment $SybaseLogin $SybasePassword $IQLogin $IQPassword $PowerShellDir
if ($Result -eq "OK" )
{
 Write-Host "Enter the word: DEV"
 # get data
 New-IqWeeklyOBS

 
}