How to call a .net method with an output parameter?

Question

I just want to call the GenerateScript method of Microsoft.Data.Schema.ScriptDom.Sql.Sql100ScriptGenerator from PowerShell.

#C

public void GenerateScript(
    IScriptFragment scriptFragment,
    out string script
)

I found this, but I do not get it to work

$sg = new-object  Microsoft.Data.Schema.ScriptDom.Sql.Sql100ScriptGenerator

$sql = 'select * from PowerShell'

$out = ''
$sg.GenerateScript($sql, [ref] $out)

$out

this gives

Cannot find an overload for "GenerateScript" and the argument count: "2".
At line:6 char:19
+ $sg.GenerateScript <<<< ($sql, [ref] $out)
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

Edit:

Current version is

$sql = 'select * from PowerShell'

$sr = new-Object System.IO.StringReader($sql)

$sg =     new-object Microsoft.Data.Schema.ScriptDom.Sql.Sql100ScriptGenerator
$parser = new-object Microsoft.Data.Schema.ScriptDom.Sql.TSQL100parser($true)

$errors = ''
$fragment = $parser.Parse($sr,([ref]$errors))

$out = ''
$sg.GenerateScript($fragment,([ref][string]$out))

$out

But I get an error in line

$fragment = $parser.Parse($sr,([ref]$errors))



Cannot find an overload for "Parse" and the argument count: "2".
At line:11 char:26
+ $fragment = $parser.Parse <<<< ($sr,([ref]$errors))
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

I'm trying to convert

    IList<ParseError> errors;

    using (StringReader sr = new StringReader(inputScript))
    {
        fragment = parser.Parse(sr, out errors);
    }

Edit:

OK this works:

$sql = @'
select * from PowerShell -- a comment
where psRefnr = 1
'@
$options = new-object Microsoft.Data.Schema.ScriptDom.Sql.SqlScriptGeneratorOptions

$sr = new-Object System.IO.StringReader($sql)

$sg =     new-object Microsoft.Data.Schema.ScriptDom.Sql.Sql100ScriptGenerator($options)
$parser = new-object Microsoft.Data.Schema.ScriptDom.Sql.TSQL100parser($true)

$errors = $null
$fragment = $parser.Parse($sr,([ref]$errors))

$out = $null
$sg.GenerateScript($fragment,([ref]$out))

$out

and generates ( it removes the comment as intended )

SELECT *
FROM   PowerShell
WHERE  psRefnr = 1;

Eleanoreleanora

"but I do not get it to work": Please expand: are you getting an error/warning? Is the behaviour you're getting not what you expect: what do you expect, and what are you getting? – Vinery

possible duplicate of How to call method with output parameters in Powershell? – Sudiesudnor

The question there is WMI related and the answer doesn't apply. – Eleanoreleanora

You are again passing the wrong type of parameter. You are passing a string for $errors to Parse, when it expects a IList<ParseError>. – Gisellegish

Forgot to mention, try setting $errors to $null. By setting it to '' you are making it a string. – Gisellegish

Please can someone add a tag sql-parser? – Eleanoreleanora

Answer 1

I believe your issue is with your first parameter, which should be a IScriptFragment. You are passing a string.

You would need to pass something that derives from TSqlFragment. Using something like the TSql100Parser.ParseStatementList method, you will get a list of fragments.

EDIT: This blog post has a similar issue to your second error.

Gisellegish

Answer 2

Not entirely sure how this works with Powershell, but in normal C# you need to call an out parameter with the keyword "out" instead of the "ref" you have. Sorry if this is off base, but figured it might help.

Judgment

in powershell, [ref] covers both c#'s out and ref. – Seabrook