Storing SQL field names and general SQL usage with Delphi

Question

I'm starting to write my first Delphi application that connects to an SQL database (MySQL) using the ADO database components. I wondered whether there was any best way of storing the names of the fields in the database for easy reference when creating SQL queries later on.

First of all I made them a simple constant e.g. c_UserTable_Username, c_UserTable_Password, but then decided that was not a particularly good way of doing things so I am now storing them in a constant record e.g.:

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

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

this allows me to create a statement like:

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

and not have to worry about hard coding the field names etc.

I've now run into the problem however where if I want to cycle through the table fields (for example if there are 20 or so fields), I can't. I have to manually type the record reference for every field.

I guess what I'd like to know is whether there is a way to iterate though all field names at once, or singularly; or am I going about this the wrong way? Perhaps I shouldn't be storing them like this at all?

Also, I've made a “Database” class which basically holds methods for many different SQL statements, for example GetAllUsers, GetAllProducts, etc. Does that sound correct? I've taken a look at a lot of Delphi/SQL tutorials, but they don't seem to go much past showing you how to run queries.

I suppose I'm just a little lost and any help is very welcome. Thanks :)

Answer 1

Well, you are hard coding field names; you just hardcode them in the const instead of in the query itself. I'm not sure that actually improves anything. As far as iterating through the fields goes, try this:

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

Rather than making a "Database" class, I would probably use a TDataModule. This does almost the same thing as your class, except that it allows you to interactively design queries at design time. You can put any methods you need on the DataModule.

This also makes it really easy to instantiate persistent TFields (see help on that topic), which you may find the solution more to your liking than using consts to store field names.

Answer 2

You could also store your queries as RESOURCESTRING which would allow editing of them after the fact using a resource editor (if necessary).

RESOURCESTRING
  rsSelectFromUsers = 'SELECT USERNAME FROM USERS ';

Your approach of a database class works very well. I have done just that in several of my projects, returning an interface to an object which contains the dataset...the advantage of this is when the returned interface variable goes out of scope, the dataset would be closed and cleared.

Answer 3

If you're really going to use a database class as illustrated, consider the ability of records to contain functions in D2007 and later.

For instance, your example would become:

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;

which allows:

query.SQL.add(userTable.sqlGetUserName);

or

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

I don't really recommend using SQL directly as you've illustrated. In my opinion, you should never have direct SQL calls to the tables. That's introducing a lot of coupling between the UI and the database (which shouldn't exist) and prevents you from placing a high level of security on direct table modification.

I would wrap everything into stored procs and have a DB interface class that encapsulates all of the database code into a data module. You can still use the direct links into data-aware components from a data module, you just have to preface the links with the DM name.

For instance, if you built a class like:

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;

You now have the ability to do some form of unit testing on your data interface, you've removed the SQL from the UI and things are looking up. You still have a lot of ugly SQL in your interface code though so move that over to stored procs and you get:

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;

You could now move some of these functions into an ADO Thread and the UI would have no idea that adding or deleting users occur in a separate process. Mind, these are very simple operations so if you want to do handy things like notifying the parent when process are done (to refresh a user list for instance after add/delete/update occurs), you'd need to code that into the threading model.

BTW, the stored proc for the add code looks like:

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

Also, a little disclaimer: this post is pretty long and, while I tried to check most of it, I may have missed something, somewhere.

Answer 4

Take a loot at Analysing DataSets (in Delphi and Kylix)

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

Answer 5

Maybe a bit off topic but you could use Data Abstract from RemObjects.