Automate Sybase Integration Tasks from PowerShell

The CData Cmdlets for Sybase 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.

PowerShell Cmdlets or ADO.NET Provider?

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

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

To connect to Sybase, specify the following connection properties:

• Server: Set this to the name or network address of the Sybase database instance.
• Database: Set this to the name of the Sybase database running on the specified Server.

Optionally, you can also secure your connections with TLS/SSL by setting UseSSL to true.

Sybase supports several methods for authentication including Password and Kerberos.

Connect Using Password Authentication

Set the AuthScheme to Password and set the following connection properties to use Sybase authentication.

• User: Set this to the username of the authenticating Sybase user.
• Password: Set this to the username of the authenticating Sybase user.

Connect using LDAP Authentication

To connect with LDAP authentication, configure Sybase server-side to use the LDAP authentication mechanism.

After configuring Sybase for LDAP, you can connect using the same credentials as Password authentication.

Connect Using Kerberos Authentication

To leverage Kerberos authentication, begin by enabling it setting AuthScheme to Kerberos. See the Using Kerberos section in the Help documentation for more information on using Kerberos authentication.

You can find an example connection string below:

Server=MyServer;Port=MyPort;User=SampleUser;Password=SamplePassword;Database=MyDB;Kerberos=true;KerberosKDC=MyKDC;KerberosRealm=MYREALM.COM;KerberosSPN=server-name

PowerShell

1. Install the module:

   Install-Module SybaseCmdlets

2. Connect:

   $sybase = Connect-Sybase -User "$User" -Password "$Password" -Server "$Server" -Database "$Database" -Charset "$Charset"
   

3. Search for and retrieve data:

   $productname = "Konbu"
   $products = Select-Sybase -Connection $sybase -Table "Products" -Where "ProductName = `'$ProductName`'"
   $products
   

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

   $products = Invoke-Sybase -Connection $sybase -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\lib\System.Data.CData.Sybase.dll")
    

2. Connect to Sybase:

    
   $conn= New-Object System.Data.CData.Sybase.SybaseConnection("User=myuser;Password=mypassword;Server=localhost;Database=mydatabase;Charset=iso_1;")
   $conn.Open()
   

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

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

Update Sybase Data

PowerShell

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

ADO.NET

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

Insert Sybase Data

PowerShell

Add-Sybase -Connection $Sybase -Table Products -Columns @("Id", "ProductName") -Values @("MyId", "MyProductName") 

ADO.NET

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

Delete Sybase Data

PowerShell

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

ADO.NET

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

CodeProject