StarQuest Technical Documents

Using PowerShell with StarSQL

Last Update: 18 February 2021
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ067

Abstract

This document describes how to execute a Db2 query and call stored procedures from a PowerShell using the StarSQL software.

Solution

See below for samples of executing a simple SQL query and calling a simple stored procedure with literal parameters and no output.

You can either use an ODBC data source (DSN) or a DSN-less connection string. See Using a DSN-less Connection with StarSQL for details.

Use PowerShell v5.1 or later, which is standard on Windows Server 2016/2019/Windows 10. Enter $PSTableVersion to display the version of PowerShell. See the Microsoft documentation Install and Configure WMF 5.1 for instructions on updating PowerShell on earlier versions of Windows; a reboot will be required.

64-bit Considerations

• If you are using a 64-bit operating system, the default invocation of PowerShell is a 64-bit application, and uses the 64-bit version of StarSQL and 64-bit data sources.
• If you prefer to use the 32-bit version of StarSQL, create 32-bit data sources with \windows\syswow64\odbcad32.exe and invoke the 32-bit PowerShell interpreter from \windows\syswow64.

Linux considerations

• See Installing PowerShell on Linux for instructions on installing PowerShell (available from the Microsoft YUM repository).
• Invoke PowerShell with the command pwsh.
• When using a connection string, the name of the driver is StarSQL64 rather than StarSQL (64-bit)..

Example 1: Simple SQL query

$conn = new-object System.Data.Odbc.OdbcConnection
$username='MYUSER'
$password='MYPASSWORD'
$sqlCommand= "select * from MYTAB"

$conn.connectionstring = "DSN=MYDSN;UID=$username;PWD=$password"
# or
# $conn.connectionstring = "Driver={StarSQL (64-bit)};Server=MYRDB;HostName=myhost;Port=446;PkgColID=STARSQL;UID=$username;PWD=$password"
$conn.Open()
$cmd = New-object System.Data.Odbc.OdbcCommand($sqlCommand,$conn)
$rdr = $cmd.ExecuteReader()
$dt =[System.Data.DataTable]::New()
$dt.Load($rdr)
$conn.Close()
$dt

Example 2: Simple Stored Procedure

This example shows the use of a simple stored procedure call to execute a program on an IBM i (AS/400) host using the program QCMDEXC. See the StarQuest technical note SQV00SQ040: Calling an AS/400 Program with QCMDEXC for details.

$conn = new-object System.Data.Odbc.OdbcConnection
$username='MYUSER'
$password='MYPASSWORD'
$sqlCommand= "select * from MYTAB"
$conn.connectionstring = "DSN=MYDSN;UID=$username;PWD=$password"
$sqlCommand= "CALL QSYS.QCMDEXC('CRTLIB TEMP',0000000011.00000)"

$conn.Open()
$cmd = New-object System.Data.Odbc.OdbcCommand($sqlCommand,$conn)
$rdr = $cmd.ExecuteReader()
$conn.Close()