0

I've built a powershell script that allows me to run and email the result of sql queries that are being read from a sql file:

function global:readscript($path)
{
    #using UTF7 encoding to allow select with accented/french/russian/chinese/... etc chars
    $inenc = [System.Text.Encoding]::UTF7
    $reader = new-object System.IO.StreamReader($path, $inenc)
    $finalquery = ""

    while ($line = $reader.ReadLine())
    {
        $finalquery += $line
    }
    $reader.close()

    return $finalquery
}

function global:get-result($query)
{
    $oracleconnection = new-object Oracle.ManagedDataAccess.Client.OracleConnection
    $oracleconnection.connectionstring = $connectionstring
    $oracleconnection.Open()
    $oraclecommand = $oracleconnection.CreateCommand()
    $oraclecommand.CommandText = $query
    $reader = $oraclecommand.ExecuteReader()

    #...etc
}

$scriptquery = readscript "d:\mysqlquery.sql"
get-result($scriptquery)

Everything is working fine so far, except this one sql script that contains the "+" sign for purpose of calculation.

Lets say file mysqlquery.sql contains a line such as:

(SELECT COUNT(a.ID)) + (SELECT COUNT(b.ID))

I can see in the console it's being translated to 

(SELECT COUNT(a.ID)) (SELECT COUNT(b.ID))

and of course throws this annoying exception "missing right parenthesis"

How do I escape this plus sign when reading it from a txt file ?

Marc
  • 352
  • 2
  • 6
  • 19
  • Found a (very) dirty way: in my sql file I replace "+" by "£plus£" and then in my powershell script proceed to $query = $query.Replace('£plus£','+') ... I'm not proud of it.. – Marc May 29 '18 at 13:55
  • 1
    I think [this question](https://stackoverflow.com/questions/45446274/file-readalltext-with-utf-7-ignoring-characters) is related. My guess is that the file isn't really UTF7, [maybe MacRoman](https://www.experts-exchange.com/questions/23633953/UTF-7-encoding-skips-the-character-when-reading-text-file-from-MacOSX-in-StreamReader.html) (which .Net doesn't have a decoder for), so your dirty workaround may be the best way. – BenH May 29 '18 at 15:45
  • This makes perfectly sense ! I would have never thought the encoding was the reason for the plus sign to be ignored. Thanks – Marc May 30 '18 at 09:26

0 Answers0