Mysql – Storing SQL field names and general SQL usage with Delphi

databasedelphifieldmysqlnames

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 🙂

Best Solution

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.