Hi Guys,
In case anyone else has an issue with text characters, I was having an issue with SQL Scripts related to Currency symbols where they were appearing in VistaDB as question marks for no good and sufficient reason that I could see. The queries worked, but the results were wrong. Jamie from VistaDB support gave me a pointer that helped to find the issue, and I thought that I would write it up in case it is useful to anyone else.
The SQL was written and tested in DataBuilder where it worked perfectly and then copied into a text file in Visual Studio 2008. It also had some of our own additional script code in it, but that can be ignored for the purposes of this discussion.
The functional contents of the file were:
Code:UPDATE Currencies SET CurrencySymbol = '£' WHERE CurrencyCode IN ('CYP','EGP','FKP','GBP','GIP','SHP','SYP');
UPDATE Currencies SET CurrencySymbol = '€' WHERE CurrencyCode = 'EUR';
UPDATE Currencies SET CurrencySymbol = '¥' WHERE CurrencyCode = 'JPY';
This code works perfectly if copied back into DataBuilder and run, but not if it is loaded and run from the file.
The code to load the file was again trivial:
Code:List<string> commandList = new List<string>();
// Load the command lines
using (StreamReader streamReader =
new StreamReader(data.ScriptFileName))
{
while ((currentLine = streamReader.ReadLine()) != null)
{
commandList.Add(currentLine);
}
streamReader.Close();
}
According to MSDN this should result in the file being read with the default encoding, but this was not happening. By simply explicitly setting the Default the problem went away:
Code:// Load the command lines
using (StreamReader streamReader =
new StreamReader(data.ScriptFileName, Encoding.Default))
{
while ((currentLine = streamReader.ReadLine()) != null)
{
commandList.Add(currentLine);
}
streamReader.Close();
}
I still cannot understand why the initial version does not work correctly given that the currency symbols are well within the standard ASCII range i.e. Yen = 165, Pound = 163 Euro = 128, but at least it now works.
I hope that this is useful to someone.