November 19, 2021 at 4:03 pm
Hi Guys,
It's Friday afternoon and my brain hurts....
TLDR:
Given the string:
strStmt = "SELECT * FROM " & strView & " WHERE MainLKUPID = #REM " & strAccounts(lngCount) & "#REM " #REM This is a comment
How would you replace the #REM with single quotes so that you end up with a string
strStmt = "SELECT * FROM " & strView & " WHERE MainLKUPID = '" & strAccounts(lngCount) & "'" #REM This is a comment
Which would eventually resolve to an executable string
SELECT * FROM myView WHERE MainLKUPID = 'myStringValue'
Long Version and context.
I have an application which holds a whole bunch on VBScript functions in a TEXT field in single record in the database. I need to deconstruct this so that I can report on the individual functions.
VBScript uses a single single-quote to identify a line as being a comment. As I have to substring the TEXT field into separate lines I have to replace the single-quote with #REM_ (for Remark, the _ is actually a space character). Big shout out to Microsoft for making the comments syntax consistent across their application base - NOT!
Some functions will make a connection to the database and execute a prepared statement as in the above example. These prepared statements are concatenations and where the concatenation is going to include a CHAR value that will be coming from a variable the string segments will contain a single-quote but it will be bounded by double-quotes. Unfortunately I have substituted the single quotes for #REM so now I need to set them back to single quotes (back-ticks will do as it is only for presentation) but I only want to do the substitution when they are bounded. In the original example there are two #REM that ARE bounded and one that isn't (at the end...)
Is there a more elegant solution than doing some sort of string split, doing a running count of the number of double quotes using SUM() OVER() and only doing the replacement if the count of double-quotes is odd, then joining the string back together.
November 19, 2021 at 7:46 pm
I can think of a few approaches, but my recommendation is to scrap that process and instead use stored procedures and have your code call the stored procedure instead. This is more of a "best practice" approach and will be a lot nicer going forward.
Now if you absolutely NEED to do it in VB, I would start by posting this on a VB forum and not a SQL Server forum. But I would look for "consistencies" in your #REM statements to determine when to replace it. Similar to how you'd handle a find and replace search. Your #REM that you want to replace are followed by a space and a ". So your replace (note I don't remember VBScript REPLACE function exactly) would be something like:
strStmt = strStmt.Replace("#REM \"", "'\"")
I don't remember 100% if \" is the escape character for a quote; I know SQL Server uses "", but I think VBScript uses \" to escape the quote character.
Now, if you are trying to do this 100% in SQL Server, that last #REM is going to give you a syntax error. strStmt will too unless you toss an @ in front and declare it as an VARCHAR or NVARCHAR prior to that. But I would HIGHLY recommend you use stored procedures for that and not ad-hoc SQL. You generally will get better overall performance as stored procedures are compiled and the plan can be reused. If you use ad-hoc SQL, the query plan will be recreated each time if there is any change to the query which includes the value in the WHERE clause (and whitespace and capitalization).
EDIT - I just wanted to make a comment about your comment "Big shout out to Microsoft for making the comments syntax consistent across their application base - NOT!". In this case, I would argue it is NOT Microsoft's fault. VBScript is based off of Visual BASIC which is based off of BASIC, which was NOT created by Microsoft. John G. Kemeny wrote the first version of BASIC, and it was patterned around FORTRAN. Microsoft did have an implementation of BASIC, but they didn't invent BASIC, so they inherited some of the syntax. As BASIC grew into other flavors (QBASIC, Quick BASIC, Visual BASIC, and others), the syntax remained the same for important things like comments.
Now, SQL Server using 2 -'s for a comment (ie --), again is not Microsoft's decision - it is part of the ANSI SQL standard.
So BOTH of those decisions for comments were outside Microsoft's control UNLESS they wanted to deviate away from what people were already using which would have made their adoptions of the language a lot more complex for new users of their flavor of tool to use and result in MORE complaints about what they did for comments, and not less.
Microsoft followed the standards set in place prior to them making their version of BASIC for comments and followed the ANSI standards for SQL, which in my opinion is the correct choices. If I were to build my own C compiler for example, I would be hated in the coding community if I decided to change what characters are used for comments and my compiler, even if it generated 10x more efficient code, would not be used. Same thing if I designed my own SQL engine - if I didn't follow ANSI SQL standards for things, it would confuse new people to the language and I'd have slow, if any, adoption.
Now, if you had some complaint about PowerShell and MS-DOS/Windows Command Prompt differences, you may have an argument there, but even that is a hard sell as PowerShell accepts most (if not all) DOS commands and then extends on them with powershell specific features.
Wikipedia has a nice writeup on the BASIC programming language and if you google "SQL Comments", you will see that it is NOT just Microsoft SQL Server that handles comments with 2 dashes (--).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 20, 2021 at 12:01 am
Thanks Brian, I was being faceicous about the comments, I know it's not Microsofts fault 🙂
I didn't state the problem clearly enough. I am not trying to develop the code in VBScript, the text field IS VBScript. I am trying to parse it in SQL. The larger project is that these script functions are used in a workflow environment that conceptually is like Microsoft Flow but a proprietary format and I need to be able to identify which functions are used in which workflows and what those functions do.
In the end I have taken each script line and used Jeff Modens fantastic DelimitedSplit8K, splitting on the double-quotes (actually using REPLACE(textline, '"',''"£) and splitting on the '£' to preserve the double-quotes), and then if the ItemNumber is even (using ItemNumber % 2 = 0) REPLACE(Item, '#REF ', '`') and then the magic of STUFF..FOR XML to stitch them back together.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply