Microsoft Dynamics GP and Illegal Characters Part 1
Over the years that I have worked with Microsoft Dynamics GP, there are been a few occasions where we have had issues with so called "illegal" characters.
While the use of these characters should work everywhere, there are times when the characters are not handled correctly by the system and so cause problems. The characters in question are the quote or apostrophe characters; the single quote ('), ASCII 39; the double quote ("), ASCII 34; and the back quote (`) ASCII 96.
Boring Theory Stuff
Those of you who have a development background would know that when the character used to delimit a string field needs to be included in a string field, you would usually need to use some sort of special handling to prevent the string field being terminated prematurely.
Here are some examples to demonstrate what I mean:
Dexterity uses a double quote (") as a string terminator. So to display a string including a double quote, I need to either use two double quotes, use the QUOTE constant, or use the char() function with the ASCII value of 34.
warning "This is a "" character";
warning "This is a " + QUOTE + " character";
warning "This is a " + char(34) + " character";
Visual Basic for Applications (VBA) also uses a double quote (") as a string terminator. So to display a string including a double quote, I need to either use two double quotes or use the chr() function with the ASCII value of 34.
msgbox "This is a "" character"
msgbox "This is a " + Chr(34) + " character"
Transact SQL uses a single quote (') as a string terminator. So to display a string including a single quote, I need to use two single quotes.
print 'This is a '' character'
This special handling for the string terminator character is usually handled during the development process and so usually does not cause any issues.
Also, the development environments usually will understand the special requirements for the string terminator and handle it appropriately.
For example: when Dexterity sends data to SQL Server it will automatically handle the conversion of a single quote in a string field to two single quotes to avoid early termination of the field.
The Real World
So when can these characters cause problems....
The problem occurs when code is built on the fly and the string terminator is not handled correctly. In Dexterity, we have the ability to create pass through sanScript Dexterity code that we can run with the execute() function. We also have the ability to pass code to SQL Server as part of a where clause or as pass through Transact-SQL code.
The following Knowledge Base (KB) article explains how to use the SQL_FormatStrings() function to ensure that any single quote characters in the data are handled correctly:
If a Dexterity developer writes code where the data from a string field is passed to SQL Server without using the SQL_FormatStrings() function, then the inclusion of a single quote into the data field can cause the code to fail. If this situation happens, it should be logged as a support case with the developer so they can find and fix the offending code.
Another situation that can cause issues is when a stored procedure in SQL Server uses dynamically built code to pass through SQL code using the exec command. This code also needs to handle the single quote correctly. A SQL User Defined Function (UDF) could be written to perform the same function as the SQL_FormatStrings() Dexterity function.
More information to come in the next post..... Microsoft Dynamics GP and Illegal Characters Part 2.
Also have a look at this related KB article for handling of dates and times.
How to pass dates and times to SQL Server from Dexterity in Microsoft Dynamics GP (KB 929786)
Hope this information is useful.
David