unit u_db_utilities;
interface
uses
Windows,
SysUtils,
Classes,
Controls,
ADODB,
DB;
const ADOConnStrTemplate = 'Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=%s;Data Source=%s;User ID=%s;Password=%s';
MySQL5StrTemplate = 'Provider=MSDASQL.1;Persist Security Info=True;User ID=%0:s;Password=%1:s;Extended Properties="DRIVER={MySQL ODBC 5.1 Driver};SERVER=%2:s;PORT=3306;DATABASE=%3:s;USER=%0:s;PASSWORD=%1:s;OPTION=1048579"';
MySQL3StrTemplate = 'Provider=MSDASQL.1;Persist Security Info=True;User ID=%0:s;Password=%1:s;Extended Properties="DRIVER={MySQL ODBC 3.51 Driver};SERVER=%2:s;PORT=3306;DATABASE=%3:s;USER=%0:s;PASSWORD=%1:s;OPTION=1048579"';
type
TDBtype = (dbMsSQL, dbMySQL3, dbMySQL5);
const
TDBTypeStrings : array[dbMsSQL..dbMySQL5] of string[10] = ('MsSQL', 'MySQL3', 'MySQL5');
type
TDateTimeType = (dtDate, dtTime, dtDateTime);
IIADOQuery = interface
['{344B711B-FBB2-473B-A864-F5D0ABB7A8F8}']
procedure ParseQuery(SQL : String);
procedure SelectParsedQuery;
procedure UpdateParsedQuery;
procedure SelectQuery(SQL : String);
procedure UpdateQuery(SQL : string);
procedure SelectQueryEx(SQL : String; var Flag : Boolean);
procedure UpdateQueryEx(SQL : string; var Flag : Boolean);
procedure UpdateParsedQueryEx(var Flag : Boolean);
procedure SelectParsedQueryEx(var Flag : Boolean);
procedure Parameter(ParameterName : String; Value : String); overload;
procedure Parameter(ParameterName : String; Value : Integer); overload;
procedure Parameter(ParameterName : String; Value : Boolean); overload;
procedure Parameter(ParameterName : String; Value : Real); overload;
procedure Parameter(ParameterName : String; Value : String; CurrencySymbol: String); overload;
procedure Parameter(ParameterName : String; Value : String; DateTimeType : TDateTimeType); overload;
function AsString(FieldName : String) : String;
function AsInteger(FieldName : String) : Integer;
function AsFloat(FieldName : String) : Double;
function AsDateTime(Fieldname : String) : TDateTime;
function EOF : Boolean;
function RecordCount : Integer;
procedure Next;
procedure Previous;
procedure Edit;
procedure Post;
function Qry : TADOQuery;
end;
TIADOQuery = class(TInterfacedObject, IIADOQuery)
private
public
ADOQuery : TADOQuery;
procedure ParseQuery(SQL : String);
procedure SelectParsedQuery;
procedure UpdateParsedQuery;
procedure SelectQuery(SQL : String);
procedure UpdateQuery(SQL : string);
procedure SelectQueryEx(SQL : String; var Flag : Boolean);
procedure UpdateQueryEx(SQL : string; var Flag : Boolean);
procedure UpdateParsedQueryEx(var Flag : Boolean);
procedure SelectParsedQueryEx(var Flag : Boolean);
procedure Parameter(ParameterName : String; Value : String); overload;
procedure Parameter(ParameterName : String; Value : Integer); overload;
procedure Parameter(ParameterName : String; Value : Boolean); overload;
procedure Parameter(ParameterName : String; Value : Real); overload;
procedure Parameter(ParameterName : String; Value : String; CurrencySymbol: String); overload;
procedure Parameter(ParameterName : String; Value : String; DateTimeType : TDateTimeType); overload;
procedure Parameter(ParameterName: String; Value: TDate); overload;
function AsString(FieldName : String) : String;
function AsInteger(FieldName : String) : Integer;
function AsFloat(FieldName : String) : Double;
function AsDateTime(Fieldname : String) : TDateTime;
function EOF : Boolean;
function RecordCount : Integer;
procedure Next;
procedure Previous;
procedure Edit;
procedure Post;
function Qry : TADOQuery;
constructor Create(ADOConnection : TADOConnection); overload;
constructor Create(ADOConnection : TADOConnection; ctDynamic : Boolean); overload;
destructor Destroy; override;
end;
function CreateADOConnection(DBType : TDBType; SQLServer, SQLUsername, SQLPassword, Catalog : String) : TADOConnection;
function CreateConnectionString(DBType : TDBType; SQLServer, SQLUsername, SQLPassword, Catalog : String) : String;
function StringToDBType(db : string) : TDBtype;
implementation
// general purpose routines
function StringToDBType(db : string) : TDBtype;
var dbtype : TDBtype;
begin
for dbtype := Low(TDBTypeStrings) to High(TDBTypeStrings) do
begin
if AnsiSameText(TDBTypeStrings[dbtype], db) then
begin
Result := dbtype;
Exit;
end;
end;
raise Exception.CreateFmt('Not database type found for "%s"', [db]);
end;
function CreateConnectionString(DBType : TDBType; SQLServer, SQLUsername, SQLPassword, Catalog : String) : String;
begin
case DBType of
dbMsSQL: Result := Format(ADOConnStrTemplate,[Catalog, SQLServer, SQLUsername, SQLPassword]);
dbMySQL3: Result := Format(MySQL3StrTemplate,[SQLUsername, SQLPassword, SQLServer, Catalog]);
dbMySQL5: Result := Format(MySQL5StrTemplate,[SQLUsername, SQLPassword, SQLServer, Catalog]);
end;
end;
function CreateADOConnection(DBType : TDBType; SQLServer, SQLUsername, SQLPassword, Catalog : String) : TADOConnection;
begin
Result := TADOConnection.Create(nil);
Result.ConnectionString := CreateConnectionString(DBType, SQLServer, SQLUsername, SQLPassword, Catalog);
end;
{ TInterfacedADO }
constructor TIADOQuery.Create(ADOConnection : TADOConnection);
begin
if not Assigned(ADOQuery) then
ADOQuery := TADOQuery.Create(nil);
ADOQuery.Connection := ADOConnection;
// ADOQuery.CursorLocation := clUseServer;
// ADOQuery.CursorType := ctDynamic;
ADOQuery.ParamCheck := False;
end;
constructor TIADOQuery.Create(ADOConnection: TADOConnection; ctDynamic: Boolean);
begin
Create(ADOConnection);
end;
destructor TIADOQuery.Destroy;
begin
FreeAndNil(ADOQuery);
inherited;
end;
procedure TIADOQuery.ParseQuery(SQL: String);
begin
ADOQuery.ParamCheck := True;
ADOQuery.SQL.Text := SQL;
end;
function TIADOQuery.Qry: TADOQuery;
begin
Result := ADOQuery;
end;
function TIADOQuery.RecordCount: Integer;
begin
Result := ADOQuery.RecordCount;
end;
procedure TIADOQuery.SelectParsedQuery;
begin
ADOQuery.Active := False;
ADOQuery.Active := True;
end;
procedure TIADOQuery.SelectQuery(SQL: String);
begin
ADOQuery.Active := False;
ADOQuery.ParamCheck := False;
ADOQuery.SQL.Text := SQL;
ADOQuery.Active := True;
end;
procedure TIADOQuery.SelectQueryEx(SQL: String; var Flag: Boolean);
begin
try
SelectQuery(SQL);
except
Flag := False;
raise;
end;
end;
procedure TIADOQuery.UpdateParsedQuery;
begin
ADOQuery.Active := False;
ADOQuery.ExecSQL;
end;
procedure TIADOQuery.SelectParsedQueryEx(var Flag: Boolean);
begin
try
SelectParsedQuery;
except
Flag := False;
raise;
end;
end;
procedure TIADOQuery.UpdateParsedQueryEx(var Flag : Boolean);
begin
try
UpdateParsedQuery;
except
Flag := False;
raise;
end;
end;
procedure TIADOQuery.UpdateQuery(SQL: string);
begin
ADOQuery.Active := False;
ADOQuery.ParamCheck := False;
ADOQuery.SQL.Text := SQL;
ADOQuery.ExecSQL;
end;
procedure TIADOQuery.UpdateQueryEx(SQL: string; var Flag: Boolean);
begin
try
UpdateQuery(SQL);
except
Flag := False;
raise;
end;
end;
procedure TIADOQuery.Parameter(ParameterName, Value: String);
begin
ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftString;
end;
procedure TIADOQuery.Parameter(ParameterName: String; Value: Integer);
begin
ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftInteger;
end;
procedure TIADOQuery.Parameter(ParameterName: String; Value: Boolean);
begin
ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftBoolean;
end;
procedure TIADOQuery.Parameter(ParameterName: String; Value: Real);
begin
ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftFloat;
end;
procedure TIADOQuery.Parameter(ParameterName: String; Value: TDate);
begin
ADOQuery.Parameters.ParamByName(ParameterName).Value := Value;
ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftDate;
end;
procedure TIADOQuery.Parameter(ParameterName, Value, CurrencySymbol: String);
var ConvertedValue : Real;
Ps : Integer;
begin
Ps := Pos(CurrencySymbol, Value);
if Ps > 0 then
Value := Copy(Value, 1, Ps - 1);
ConvertedValue := StrToFloat(Value);
ADOQuery.Parameters.ParamByName(ParameterName).Value := ConvertedValue;
ADOQuery.Parameters.ParamByName(ParameterName).DataType := ftFloat;
end;
procedure TIADOQuery.Parameter(ParameterName, Value: String; DateTimeType: TDateTimeType);
begin
case DateTimeType of
dtDate :
end;
end;
function TIADOQuery.AsDateTime(Fieldname: String): TDateTime;
begin
Result := ADOQuery.FieldByName(FieldName).AsDateTime;
end;
function TIADOQuery.AsFloat(FieldName: String): Double;
begin
Result := ADOQuery.FieldByName(FieldName).AsFloat;
end;
function TIADOQuery.AsInteger(FieldName: String): Integer;
begin
Result := ADOQuery.FieldByName(FieldName).AsInteger;
end;
function TIADOQuery.AsString(FieldName: String): String;
begin
Result := ADOQuery.FieldByName(FieldName).AsString;
end;
procedure TIADOQuery.Edit;
begin
Qry.Edit;
end;
function TIADOQuery.EOF: Boolean;
begin
Result := Qry.Eof;
end;
procedure TIADOQuery.Next;
begin
Qry.Next;
end;
procedure TIADOQuery.Post;
begin
Qry.Post;
end;
procedure TIADOQuery.Previous;
begin
Qry.Prior;
end;
end.