Macros containing single quotes

[mseminatore]

I am trying to create a failure step which logs the text of a failed step to a database via SQL. The text of the failed step is as follows:

Error message from the server: 'an item with that name already exists'

The SQL step is essentially:

INSERT INTO errors ("ErrorText") VALUES('%FAILSTEP_OUTPUT%');

The issue is that the single quotes within the text cause an error in expansion of the text so that the last single quote is not passed to SQL Server.

What gets passed to ISQL is:

'Error message from the server: 'an item with that name already exists'

[mseminatore]

I even tried a script step to filter out the quotes:

Set failText = Application.Macros(vbldProject).Item("FAILURE_TEXT")

Dim failOutput
failOutput = "%FAILSTEP_OUTPUT%"
Escape(failOutput)

If failText Is Nothing Then
' if the macro doesn't exist yet, initialize to one
Application.Macros(vbldProject).Add "FAILURE_TEXT", Application.ExpandMacros(failOutput)
Else
failText.Value = Application.ExpandMacros(failOutput)
End If

Then I get this error:

Error at Line 4, Column 85 (Unterminated string constant)
Code: failOutput = "Error message from the server: 'an item with that name already exists'

[kinook]

It sounds like the FAILSTEP_OUTPUT contains newlines (that would cause the 'unterminated string constant' error in the 2nd post). You need to process the string in a variable without expanding it in the script code that gets executed (which happens when using %FAILSTEP_OUTPUT%):

INSERT INTO errors ("ErrorText") VALUES('[Escape(vbld_AllMacros().Item("FAILSTEP_OUTPUT").Value)]');

[mseminatore]

Ok, I tried that. My exact SQL step looks like this

SELECT build_seterrortext('%BUILD_KEY%', %CURRENT_STEP%, '[Escape(vbld_AllMacros().Item("FAILSTEP_OUTPUT").Value)]');

and now I get the following error message:

Building failure step 'Log error to DB'...
Error expanding macros in property InputStr: <Error parsing macros: Unrecoverable Parse Error at position 221 - expecting percent_sign>

[mseminatore]

I should add that there are definitlely newlines in the FAILSTEP_OUTPUT. The text is:

"Error message from the server: 'an item with that name already exists'

Process completed with exit code 1"

I had thought the issue was with the single quotes but perhaps not.

[kinook]

Regarding the "Error parsing macros: Unrecoverable Parse Error at position 221 - expecting percent_sign" error, if the macro value could contain VBP special chars (% [ ]), those need to be escaped via the vbld_EscapeString system script function:

SELECT build_seterrortext('%BUILD_KEY%', %CURRENT_STEP%, '[vbld_EscapeString(Escape(vbld_AllMacros().Item("FAILSTEP_OUTPUT").Value))]');

Regarding the 'an item with that name already exists' error, that sounds like a error message from the database.

[mseminatore]

It is an error from a build step before the database. What I am trying to do is, in an error step, log that error text into a database.

So the database doesn't really play into this issue except that it is being given a badly formed text string from VBScript, or VBScript fails itself in trying to format the text.

I tried the vbld_escapestring around Escape and it works but the text is all full of html escapes. Is there an easy way to just remove the newlines?

thanks

[kinook]

Ah, so Escape is a built-in VBScript function that escapes a string into valid URL format. I thought it was a function you wrote to convert newlines to something else. To do just that instead, something like this would work (and you don't actually need to use vbld_EscapeString on FAILSTEP_OUTPUT, since special characters will already be escaped -- the Escape method is what was adding non-VBP-escaped chars):

SELECT build_seterrortext('%BUILD_KEY%', %CURRENT_STEP%, '[Replace(vbld_AllMacros().Item("FAILSTEP_OUTPUT").Value, vbCrLf, "")]');

[mseminatore]

Excellent! That did it. For reference here is what I wound up with:

SELECT build_seterrortext('%BUILD_KEY%', %CURRENT_STEP%, '[Replace(Replace(vbld_AllMacros().Item("FAILSTEP_OUTPUT").Value, vbCrLf, ""), "'","")]');

I had to replace (remove) both single quotes and newlines.