Saving SQL field names and general SQL usage with Delphi

Question

I am starting to write my first Delphi application that connects to an SQL database (MySQL) using ADO database components. I wondered if there is a better way to store field names in a database to simplify references when creating SQL queries later.

First of all, I made them a simple constant, for example. c_UserTable_Username, c_UserTable_Password, but then decided that this is not a good way to do what I now store them in a permanent record, for example:

type
   TUserTable = record
     TableName : String;
     Username : String;
     Password : String;
 end;

const
UserTable : TUserTable =
    (
      TableName : 'users';
      Username : 'Username';
      Password : 'Password';
    );

this allows me to create an operator like:

query.SQL.Add('SELECT ' + UserTable.Username + ' FROM ' + UserTable.TableName);

and no need to worry about hard coding field names, etc.

, , (, 20 ), . .

, , , ; ? , , ?

, " ", SQL, GetAllUsers, GetAllProducts .. ? Delphi/SQL, , , , .

, , . :)

Answer 1

RESOURCESTRING, ( ).

RESOURCESTRING
  rsSelectFromUsers = 'SELECT USERNAME FROM USERS ';

. , , ... , , .

Answer 2

, ; const, . , - . , :

var
  Field: TField;
begin
  for Field in query.Fields do begin
     // do stuff with Field
  end;
end;

, " ", , , TDataModule. , , , . DataModule .

TFields (. ), , .

Answer 3

, , D2007 .

, :

type
   TUserTable = record
     TableName : String;
     Username : String;
     Password : String;
     function sqlGetUserName(where:string=''):string;
   end;

const
UserTable : TUserTable =
    (
      TableName : 'users';
      Username : 'Username';
      Password : 'Password';
    );

function TUserTable.sqlGetUserName(where:string=''): string;
begin
if where='' then result := Format('SELECT %s from %s', [userName, tableName])
else             result := Format('SELECT %s from %s where %s', [userName, tableName, where]);
end;

:

query.SQL.add(userTable.sqlGetUserName);

query.SQL.add(userTable.sqlGetUserName(Format('%s=%s', [userTable.userName,'BOB']));

SQL , . , SQL . ( ) .

procs DB, . , , , DM.

, :

type
   TDBInterface = class
      private
         function q(s:string):string; //just returns a SQL quoted string
      public
         procedure addUser(userName:string; password:string);
         procedure getUser(userName:string);
         procedure delUser(userName:string);

         function testUser:boolean;

         procedure testAllDataSets;
      end;

function TDBInterface.q(s:string):string;
begin
result:=''''+s+'''';
end;

procedure TDBInterface.addUser(userName:string; password:string);
begin
cmd.CommandText:=Format( 'if (select count(userName) from users where userName=%s)=0 '+
                         'insert into users (userName, password) values (%s,%s) '+
                         'else '+
                         'update users set userName=%s, password=%s where userName=%s',
                         [q(userName), q(userName), q(password), q(userName), q(password), q(userName)]);
cmd.Execute;
end;

procedure TDBInterface.getUser(userName:string);
begin
qry.SQL.Add(Format('select * from users where userName=%s', [q(userName)]));
qry.Active:=true;
end;

procedure TDBInterface.delUser(userName:string);
begin
cmd.CommandText:=Format('delete from users where userName=%s',[userName]);
cmd.Execute;
end;

procedure TDBInterface.testAllDataSets;
begin
assert(testUser);
end;

function TDBInterface.testUser: boolean;
begin
result:=false;

   addUser('99TEST99','just a test');
   getUser('99TEST99');
   if qry.IsEmpty then exit;
   if qry.FieldByName('userName').value<>'99TEST99' then
      exit;
   delUser('99TEST99');
   if qry.IsEmpty then
      result:=true;
end;

, SQL , . SQL , , :

type
   TDBInterface = class
      public
         procedure addUser(userName:string; password:string);
         procedure getUser(userName:string);
         procedure delUser(userName:string);

         function testUser:boolean;

         procedure testAllDataSets;
      end;

procedure TDBInterface.addUser(userName:string; password:string);
begin
cmd.CommandText:='usp_addUser;1';
cmd.Parameters.Refresh;
cmd.Parameters.ParamByName('@userName').Value:=userName;
cmd.Parameters.ParamByName('@password').Value:=password;
cmd.Execute;
cmd.Execute;
end;

procedure TDBInterface.getUser(userName:string);
begin
sproc.Parameters.ParamByName('@userName').Value:=userName;
sproc.Active:=true;
end;

procedure TDBInterface.delUser(userName:string);
begin
cmd.CommandText:='usp_delUser;1';
cmd.Parameters.Refresh;
cmd.Parameters.ParamByName('@userName').Value:=userName;
cmd.Execute;
end;

ADO, , . , , , , ( , , //), .

BTW, :

create procedure [dbo].[usp_addUser](@userName varchar(20), @password varchar(20)) as
if (select count(userName) from users where userName=@userName)=0
   insert into users (userName, password) values (@userName,@password)
else 
   update users set userName=@userName, password=@password where userName=@userName

, : , , , , , - .

Answer 4

Maybe a little off topic, but you can use Data Abstract from RemObjects.

Answer 5

Take the loot in Dataset Analysis (in Delphi and Kylix)

Code is a good example of manipulating table metadata. You can get the field names, and then write a code generator that can create the base unit or part of the interface.