Automate Sybase IQ Integration Tasks from PowerShell

The CData Cmdlets for Sybase IQ are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Sybase IQ.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to Sybase IQ, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete Sybase IQ data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for Sybase IQ. To access Sybase IQ data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Sybase IQ.

Once you have acquired the necessary connection properties, accessing Sybase IQ data in PowerShell can be enabled in three steps.

To connect to SybaseIQ, set User, Password, Server and Database properties. To secure connections with TLS/SSL, set UseSSL to TRUE.

PowerShell

1. Install the module:

   Install-Module SybaseIQCmdlets

2. Connect:

   $sybaseiq = Connect-SybaseIQ -User "$User" -Password "$Password" -Server "$Server" -Database "$Database"
   

3. Search for and retrieve data:

   $productname = "Konbu"
   $products = Select-SybaseIQ -Connection $sybaseiq -Table "Products" -Where "ProductName = `'$ProductName`'"
   $products
   

   You can also use the Invoke-SybaseIQ cmdlet to execute SQL commands:

   $products = Invoke-SybaseIQ -Connection $sybaseiq -Query 'SELECT * FROM Products WHERE ProductName = @ProductName' -Params @{'@ProductName'='Konbu'}
   

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Sybase IQ\lib\System.Data.CData.SybaseIQ.dll")
    

2. Connect to Sybase IQ:

    
   $conn= New-Object System.Data.CData.SybaseIQ.SybaseIQConnection("User=myuser;Password=mypassword;Server=localhost;Database=Northwind")
   $conn.Open()
   

3. Instantiate the SybaseIQDataAdapter, execute an SQL query, and output the results:

   $sql="SELECT ProductName, Price from Products"
   
   $da= New-Object System.Data.CData.SybaseIQ.SybaseIQDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.productname $_.price
   }
    

Update Sybase IQ Data

PowerShell

Update-SybaseIQ -Connection $SybaseIQ -Columns @('ProductName','Price') -Values @('MyProductName', 'MyPrice') -Table Products -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.SybaseIQ.SybaseIQCommand("UPDATE Products SET ProductName='Konbu' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.SybaseIQ.SybaseIQParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Sybase IQ Data

PowerShell

Add-SybaseIQ -Connection $SybaseIQ -Table Products -Columns @("ProductName", "Price") -Values @("MyProductName", "MyPrice") 

ADO.NET

$cmd = New-Object System.Data.CData.SybaseIQ.SybaseIQCommand("INSERT INTO Products (ProductName) VALUES (@myProductName)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.SybaseIQ.SybaseIQParameter("@myProductName","Konbu")))
$cmd.ExecuteNonQuery()

Delete Sybase IQ Data

PowerShell

Remove-SybaseIQ -Connection $SybaseIQ -Table "Products" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.SybaseIQ.SybaseIQCommand("DELETE FROM Products WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.SybaseIQ.SybaseIQParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()