Title: Universal solution to formatting values for SQL statements (issued from Delphi code)
When working on database applications using dbGO (ADO) over SQL Server or MS Access, you might be having troubles formatting the SQL string expression used in various INSERT, UPDATE or SELECT statements.
Here are some issues:
1. Depending on locale international settings date values, for example, when transformed to string values might not be formatted properly. Delphi RTL's function DateTimeToStr might return a date value as 'DD.MM.YYYY' while a valid SQL expression requires, for example (depending on the Windows international settings), 'MM/DD/YYYY'.
2. When you need to "construct" a string value to a SQL statement string, you almost always need to use the QuotedStr function (to "fix" the apostrophe "'" character issue).
3. Last (but not the least), SQL Server expects "." (dot) for the decimal separator when formatting decimal values. Some countries use "," (comma) for decimal separator. A simple FloatToStr will not do the trick.
With the above in mind, when you have many SQL expressions to create, a simple SQL like the following can turn into a nightmare (to create a valid INSERT statement):
'INSERT INTO TableName (IntField, StringField) VALUES (' + IntToStr(2005) +','+ QuotedStr('delphi.about.com')+')';
This tends to be even worse when date and money fields and involved.
The TSQLF (SQL Format) "helper" class
With the help of the TSQLF class (exposing only class methods), the above SQL expression can be turned into:
'INSERT INTO TableName (IntField, StringField) VALUES (' + TSQLF.Int(2005) + ','+ TSQLF.Text('delphi.about.com') + ')';
And if it turns that you have to correct the way, for example, QuotedStr function operates you only need to change the code in one place (implementation of the TSQLF.Text method).
Here's the full source of the TSQLF class (unit), taking local settings into account:
~~~~~~~~~~~~~~~~~~~~~~~~~
unit SQLFormat;
interface
uses Windows, SysUtils, StrUtils;
type
TSQLF = class
public
class function Date(const Date : TDateTime) : string;
class function Money(const Money : Currency) : string;
class function Int(const Int : integer) : string;
class function Text(const str : string) : string;
end;
implementation
var fs : TFormatSettings;
class function TSQLF.Date(const Date: TDateTime): string;
var
Year, Month, Day : Word;
begin
DecodeDate(Date, Year, Month, Day) ;
Result := QuotedStr(IntToStr(Year) + '-' + IntToStr(Month) + '-' + IntToStr(Day)) ;
end;
class function TSQLF.Int(const Int: integer): string;
begin
Result := IntToStr(Int) ;
end;
class function TSQLF.Money(const Money: Currency): string;
var
decSep : char;
begin
decSep := fs.DecimalSeparator;
fs.DecimalSeparator := '.';
Result := CurrToStr(Money,fs) ;
fs.DecimalSeparator := decSep;
end;
class function TSQLF.Text(const str: string): string;
begin
result := QuotedStr(str) ;
end;
initialization
GetLocaleFormatSettings(LOCALE_SYSTEM_DEFAULT, fs) ;
finalization
//nothing special in the finalization
end.
~~~~~~~~~~~~~~~~~~~~~~~~~