Apply Sanitize String to Queries, too.

[hecon5]

Apply sanitize to queries as well.

I'll also toss in the regex and see if performance is hit; doesn't look like it is.

[hecon5]

FYI: I've not forgotten about this: I've been utterly bludgeoned by another project, so I haven't had even a moment to look at this.

[hecon5]

@joyfullservice, since you now export the extended properties for tables, do you happen to know if they can be done to queries, too?

If so, can we do this to queries?

The only difference I'm seeing is the designer view settings, but if they can be exported as a "property" then, that may ease this process.

Thoughts?

[joyfullservice]

@joyfullservice, since you now export the extended properties for tables, do you happen to know if they can be done to queries, too?

As far as I am aware, there is not any similar feature for queries. (You can export many (but not all) query types to XML, but you don't get the extended properties like you do with tables.)

Personally, I I think the SaveAsText approach, followed by the sanitization parsing is going to give us the most robust approach. As to the query compilation issues, I see that as a bit more outside the scope of this add-in. We have hooks that allow you to run custom code before exporting or after importing, so that is a great place to tie in special functionality to deal with edge cases in certain databases.

[hecon5]

Darn; that was my fear. I played around with it briefly and didn't see anything obvious so that's disappointing. I have some light at the end of the tunnel, so I may be able to pick this up in the mid-term.

[hecon5]

I think I may have made some progress on this, but want to lay out some findings for others to check before I get too far here.

This may also help with a validator for merging, as well, but first things first.

I'm focusing on the file information for Queries and tables here, not the form details, however, some of this also applies to the forms.

1. From what I can gather, Forms, Reports, use a similar structure,

2. Queries opened in "Design View" query editor combined with a Query/Table Format.

3. Queries and Tables are built with what appears to be a pseudo JSON.

4. Pass through queries cannot be opened in "Design View", and therefore use the pseudo JSON type. This makes detection of the pass through query a lot easier, maybe.

5. Forms and Reports (and some queries) Indent for object sections and if the "Data" portion of the entry is too long.

6. Queries/Tables indent one tab (4 space characters) for the following line if the line is too long.

7. Objects (Forms, Controls, Sections, etc.) have a Begin and an End Row, IE: Begin Form End Form which groups the properties and Objects within the parent object.

8. Object definition elements have three parts: Indent, element name, and the value (value may span multiple rows)

9. Query/Table elements consist of a ElementType, ElementName and Data portion (Value) separated by spaces.
   Example:

dbMemo "SQL" ="SELECT MSysObjects.Name\015\012FROM MSysObjects\015\012WHERE (((MSysObjects.type"
   ") In (4)));\015\012"
ElementType "ElementName" ="SomeData"

11. ElementName and Data are enclosed by quotes.
12. Data is prepended also with an equals sign.
13. Data is a maximum of 80 characters of data; or 82 Characters long including the " at the beginning and end of each line, followed by CRLF.
14. For Data entires longer than 80 characters long the following lines, four spaces, a quote mark, and then up to 80 more characters of data and then a quote mark for a total of up to 86 more characters followed by CRLF.
15. I cannot determine if there's a maximum total width, it appears to be around 100, but I can only anecdotally verify this, and it really doesn't have much to do with parsing, so to be honest, I've not spent much time on it.
16. ElementType appears to be a data type, but it isn't always.
17. Connect is always a dbMemo type.
    Now, the hard part.
18. Strings appear to be escaped somewhat.
19. Data split over multiple rows just continues s
    Example: "The long frog went out to eat grapes" becomes
    "The long fro"
    "g went out "
    "to eat grap"
    "es"
20. Special Character Escaping appears to be done by a \ followed by a 3 character indicator (Octal format) of what that is.
    NOTE: The indicators are OCTAL ASCII. Why they didn't use Hex or Decimal is far beyond me. Perhaps this resulted in fewer false positives during their testing or maybe the internal engine math was easier. Don't know. But they're octal.
21. Regular Character escaping appears to be done by adding a \ before the character.
22. Escaped Charachters (as best I can tell, so far):

Charachter	Escape Charachter	Example Replacement Text	Notes
\	\	\\
"	\	\"	This can mess up your external IDE's language parsing (such as VSCode)
(Object Definitions ONLY) ;		OMITTED	When used in a object property (like RowSource), semi-colons are omitted from the result if they are the last charachter. They are not excluded mid-string for items. I suspect this is because it's a delimiter.

[hecon5]

Given the reworked sanitize functions, and the lack of distinct things which should be sanitized out of queries, I'm not sure if this should be cleared out or not. I'm guessing not; we can always reopen this in the future, but given some specific santization items are taken care of with queries now, I'm not sure if we need to do anything particular here.