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
}
Like this:
Like Loading...