1. Tech

Your suggestion is on its way!

An email with a link to:

http://delphi.about.com/od/database/l/aa062601a.htm

was emailed to:

Thanks for sharing About.com with others!

From Paradox to Access with ADO
Page 1: Focusing on the TADOCommand components and using the SQL DDL language.
 More of this Feature
• Page 2: Porting a table
 Join the Discussion
"Post your views and comments to this chapter of the free Delphi database Programming Course"
Discuss!
 Related Resources
• free DB Course.TOC
• Queries with ADO
• SQL with Delphi
• more Database articles

Chapter 5 of this course (Free Delphi Database Course for Beginners - focus on ADO techniques) featured the ADOQuery component designed to enable Delphi developers to use the SQL language with ADO. The SQL statements can either be DDL (Data Definition Language) statements such as CREATE TABLE, ALTER INDEX, and so forth, or they can be DML (Data Manipulation Language) statements, such as SELECT, UPDATE, and DELETE.

In this chapter, I'll focus on the TADOCommand components and using the SQL DDL language to help port your BDE/Paradox data to ADO/Access.

   Data definition language
Creating a database programmatically isn't something most developers do every day - we all use some kind of visual tool, like MS Access for maintaining a MDB file. Unfortunately, sometimes you'll need to create and destroy databases and database objects from code. The most basic technique in use today is the Structured Query Language Data Definition Language (SQL DDL). Data definition language (DDL) statements are SQL statements that support the definition or declaration of database objects (for example, CREATE TABLE, DROP TABLE, CREATE INDEX and similar statements).
My intention here is not to teach you the DDL language, if you are familiar with the SQL DML then DDL should be no barrier for you. Note that working with DDL can be quite tricky, every database vendor generally provides its own extensions to SQL.

Let's quickly take a look at a simple CREATE TABLE statement:

CREATE TABLE PhoneBook(
  Name TEXT(50)
  Tel TEXT(50)
);

This DDL statemnt (for MS Access), when executed, will create a new table named PhoneBook. The PhoneBook table will have two fields, Name and Tel. Both fields are of the string (TEXT) type and the size of the fields is 50 characters.

TFieldDef.DataType
Obviously, the data type that represents a string in Access is TEXT. In Paradox it's STRING. In order to port Paradox tables to Access we'll have to know what data types are available and what are their names. When working with the BDE and Paradox tables, the TFieldDef.DataType determines the type of a physical field in a (dataset) table. To successfully migrate Paradox tables to Access you need to have a function that "transforms" a Paradox field type to an Access type.
The next function checks the type of the field (fd) and returns the corresponding Access type along with a field size when needed for a CREATE TABLE DDL statement.

function AccessType(fd:TFieldDef):string;
begin
 case fd.DataType of
  ftString: Result:='TEXT('+IntToStr(fd.Size)+')';
  ftSmallint: Result:='SMALLINT';
  ftInteger: Result:='INTEGER';
  ftWord: Result:='WORD';
  ftBoolean: Result:='YESNO';
  ftFloat : Result:='FLOAT';
  ftCurrency: Result := 'CURRENCY';
  ftDate, ftTime, ftDateTime: Result := 'DATETIME';
  ftAutoInc: Result := 'COUNTER';
  ftBlob, ftGraphic: Result := 'LONGBINARY';
  ftMemo, ftFmtMemo: Result := 'MEMO';
 else
  Result:='MEMO';
 end;
end;

   ADOX
ADO Extensions for Data Definition Language and Security (ADOX) is an extension to the ADO objects and programming model. ADOX gives developers a rich set of tools for gaining access to the structure, security model, and procedures stored in a database.

To use ADOX in Delphi, you should establish a reference to the ADOX type library.
1. Select Project | Import Type Library
3. Choose "Microsoft ADO Ext 2.x for DDL and Security (Version 2.x)"
4. Change "TTable" to "TADOXTable"
5. Change "TColumn" to "TADOXColumn"
6 .Change "TIndex" to "TADOXIndex"
7. Press Install button (rebuilding packages)
8. Press OK once and Yes twice
9. File | Close All | Yes

The top-level object in the ADOX object model is the Catalog object. It provides access to the Tables, Views, and Procedures collections, which are used to work with the structure of the database, and also provides the Users and Groups collections, which are used to work with security. Each Catalog object is associated with only one Connection to an underlying data source.

We'll leave ADOX (at least for now) and stick to ADOExpress.

   TADOCommand
In ADOExpress, the TADOCommand component is the VCL representation of the ADO Command object. The Command object represents a command (a query or statement) that can be processed by the data source. Commands can then be executed using the ADOCommand's Execute method. TADOCommand is most often used for executing data definition language (DDL) SQL commands. The CommandText property specifies the command to execute. The CommandType property determines how the CommandText property is interpreted. The cmdText type is used to specify the DDL statement. Although it makes no sense to use the ADOCommand component to retrieve a dataset from a table, query, or stored procedure, you can do so.

It's time for some real code...

Next page > Project to port your BDE/Paradox data to ADO/Access. > Page 1, 2

DB Course Next Chapter >>
>> Master detail relationships

©2014 About.com. All rights reserved.