unit DbUtils;

interface

uses
  FIBQuery,
  pFIBDatabase,
  FIBDatabase,
  pFIBQuery,
  Classes,
  sysutils,
  variants;

  function do_sql(sql: string): boolean;
  function field_exist(tbl, fld: string): boolean;
  function table_exist(tbl: string): boolean; overload;
  function table_exist(db: TpFIBDatabase; tbl: string): boolean; overload;
  function GenerateId(genname: string; Db: TFIBDatabase): integer; overload;
  function GenerateId(genname: string; qry: TFIBQuery): integer; overload;

  function locateRecord(table, field, value: string; Db: TFIBDatabase): integer;

var
  qry: TpFIBQuery;



implementation




function do_sql(sql: string): boolean;
begin
  result := true;
  try
    qry.Transaction.StartTransaction;
    qry.SQL.Text := sql;
    qry.ExecQuery;
    qry.Transaction.Commit;
  except
    qry.Transaction.Active := false;
    result := false;
  end;
end;


function field_exist(tbl, fld: string): boolean;
var
  l: TStringList;
  i: integer;
begin
  result := false;
  l := TStringList.Create;
  try
    with TpFIBDatabase(qry.Database) do begin
      GetFieldNames(tbl, l);
      l.Sort;
      if l.Find(fld, i) then
        result := true;
    end;
  except
  end;
  l.Free;
end;


function table_exist(tbl: string): boolean;
var
  l: TStringList;
  i: integer;
begin
  result := false;
  l := TStringList.Create;
  try
    TpFIBDatabase(qry.Database).GetTableNames(l, false);
    l.Sort;
    if l.Find(tbl, i) then
      result := true;
  except
  end;
  l.Free;
end;

function table_exist(db: TpFIBDatabase; tbl: string): boolean;
var
  l: TStringList;
  i: integer;
begin
  result := false;
  l := TStringList.Create;
  try
    db.GetTableNames(l, false);
    l.Sort;
    if l.Find(tbl, i) then
      result := true;
  except
  end;
  l.Free;
end;


function GenerateId(genname: string; Db: TFIBDatabase): integer;
var
  v: variant;
begin
  v := Db.QueryValue('SELECT GEN_ID(' + genname + ',1) AS ID FROM RDB$DATABASE', 0);
  if VarType(v) <> varBoolean then
    begin
      result := v;
    end
  else
    begin
      raise Exception.Create('GenerateId');
      result := 0;
    end;
end;


function GenerateId(genname: string; qry: TFIBQuery): integer;
begin
  qry.SQL.Text := Format('select gen_id(%s, 1) from rdb$database', [genname]);
  qry.ExecQuery;
  Result := qry.Fields[0].AsInteger;
  qry.Close;
end;



function locateRecord(table, field, value: string; Db: TFIBDatabase): integer;
var
  v: variant;
begin
  result := 0;
  v := Db.QueryValue( Format('SELECT id from %s where %s = ''%s''', [table, field, value]), 0);
  if not VarIsNull(v) then
    result := v;
end;


end.
