"Strip Comments From Sql Script"

[tnomore]

Hi. I find your product most useful.

I could use an explanation of the "Strip Comments" option.

Here is my situation: I am using AI to install a series of updated stored procedures (sp) into a db. Each sp has its own "Sql Script" in AI, and all are configured exactly the same - the "Strip Comments" option is NOT CHECKED.

The source file for a sp looks like this, note the presence of comments and carriage returns provided for readability:

Code: Select all

-- get the status of the user account
SELECT @AccountStatusCode = AccountStatusCode
FROM Usr
WHERE UsrCode = @UsrCode
 print @UsrCode

IF @AccountStatusCode = 0
	RETURN 3 -- account disabled

IF @AccountStatusCode = 2
	RETURN 7 -- account revoked

IF @AccountStatusCode IS NULL
	RETURN 1 -- invalid user

-- check if the good password has been specified for the user
EXEC @CheckResult = usp_CheckUsrPwd @UsrCode, @UsrPwd

print @CheckResult

However, after the AI SqlScript runs, the sp in the database looks like this:

Code: Select all

SELECT @AccountStatusCode = AccountStatusCode
FROM Usr
WHERE UsrCode = @UsrCode
 print @UsrCode
IF @AccountStatusCode = 0
	RETURN 3 IF @AccountStatusCode = 2
	RETURN 7 IF @AccountStatusCode IS NULL
	RETURN 1 EXEC @CheckResult = usp_CheckUsrPwd @UsrCode, @UsrPwd
print @CheckResult

Note that the comments have been stripped (even thought I did not check the "Strip Comments" box) and the carriage returns have been removed.

The functionality of the sp is unchanged - it just looks different to the human eye. This is causing some confusion, as the sp in the db appears - without close inspection - to be different than its source.

An explanation of these two behaviors:
-comment stripping when "strip comments" is not selected
-"readability" carriage returns being removed

would be greatly appreciated. Thanks much.

[Cosmin]

Hi,

Please note that if you set a separator for your script in Advanced Installer then the script will automatically be stripped from comments.

This is done to avoid the situation in which the separator is included in a comment. In this case the comment would be included in the final statements, creating an invalid script.

Regards,
Cosmin

[tnomore]

thanks cosmin.

i see.

does the comment stripping also remove the carriage returns provided for readability?

[Cosmin]

Hi,

does the comment stripping also remove the carriage returns provided for readability?

This happens because you placed a line comment marker (--) on the same line with a SQL instruction. After this marker is encountered the current AI algorithm ignores the rest of the line content, including the final CR/LF pair.

In the future we will improve this parsing mechanism, in the meantime you can use C-style comments in your script (do not use line comments on the same line with an instruction).

Regards,
Cosmin

[tnomore]

cosmin:

thank you for a rapid, concise, and lucid explanation - a rare occurrence in the world of software support.