SQL command line tools via pc:PowerShell

Using the pc:PowerShell directive, you can execute and commit a SQL script stored in a text file by connecting to an RDBMS-specific SQL command line tool.

The examples below connect to sqlplus for Oracle and to sqlcmd.exe for SQL Server.

Syntax

<pc:PowerShell

user-attr="user"

password-attr="password"

connection-attr="connection"

scriptfile-attr="scriptfile" >

Write-Output echo exit | sqlplus $user/$password@$connection @$scriptfile

</pc:PowerShell>

The echo exit command has been added as an argument of Write-Output to prevent the command from not returning in the event of an incorrect username/password combination.

Example

<pc:PowerShell

user="{$user}" pwd="{$pwd}" conn="{$connection}"

sql="{path:Combine($workingdir,'TRAVEL.120.pre.rdbms.sql')}">
Write-Output echo exit | sqlplus $user/$pwd@$conn @$sql
</pc:PowerShell>

Syntax

<pc:PowerShell

connection-attr="connection"

database-attr="database"

user-attr="user"

password-attr="password"

scriptfile-attr="scriptfile"

logfile-attr="logfile" >

Write-Output sqlcmd.exe -S connection -db database -U user -P password -i scriptfile -o logfile

</pc:PowerShell>

Example

<pc:PowerShell

conn="{$oledbconnection}" db="{$database}" user="{$user}" pwd="{$pwd}"

sql="{path:Combine($workingdir,'TRAVEL.120.pre.rdbms.sql')}"

mss_log="{path:Combine($workingdir,'TRAVEL.120.pre.rdbms.log')}" >
sqlcmd.exe -S $conn -d $db -U $user -P $pwd -i $sql -o $mss_log
</pc:PowerShell>