August 1998

DB->dilemmas

Sample files illustrating this article's examples are available at www.cobb.com/cpb. Click the Source Code link, then download aug98.zip. Unzip the archive, and you'll find these files in db.zip.

Text tables 2

Fred C. Mitchell
Princeton, NJ

Thanks, Fred. I received several responses like yours. Importing text files is a definitely a trouble spot, primarily because the documentation is so weak. I've recently worked with the TBatchMove object and found it to be a superb tool. In this column, I'll show you how to build a utility to import text files using the TBatchMove component. Along the way, I'll discuss an easier way to write schema files.

My motivation

On my first attempt to port the data, I opened the text file as a table and used a TQuery object with a parameterized INSERT query to populate the Paradox table. Much to my surprise, the process took hours--and there were only 10,000 records.

To get the data into a Paradox database, I decided to use the TBatchMove object, hoping it would offer reasonable performance. I was astonished at the results. Initially, I thought the command had failed because the entire text table was copied in eight or nine seconds!

Scheming to save time and effort

struct fld {
  char FieldName[25];
  int FieldLen;
  char type;  //Field type
  };

Listing A: The declaration to set up a field structure

#define CHAR_TYPE      'c' //code value
#define DATE_TYPE      'd'
#define SHORT_TYPE     'i' //16 bit integer
#define CURRENCY_TYPE  'm'
#define PAD_TYPE       'p'
#define LAST_INSTRUX   'z'

fld f[]=
{ //fName       len     Type
  {"p1",         3,     PAD_TYPE},
  {"InvoiceNo",  6,     CHAR_TYPE},
  {"p2",         1,     PAD_TYPE},
  {"BranchID",   2,     SHORT_TYPE},
  {"p3",         1,     PAD_TYPE},
  {"Description",15,    CHAR_TYPE},
  {"SoldBy",     2,     SHORT_TYPE},
  {"InvoiceCode",1,     CHAR_TYPE},
  {"InvoiceDate",12,    DATE_TYPE},
  {"Reference",  7,     CHAR_TYPE},
  {"InvoiceAmt", 8,     CURRENCY_TYPE},
  {"eof",        0,     LAST_INSTRUX}
};
String TextTableName("Aging");
String DBTableName(TextTableName);

void BuildSchema(fld f[], 
  String &TextTableName, 
  const char SCHEMA_TYPE, bool Debug);

void BuildTable(fld f[], String &TableName, 
  bool Debug);

void CopyTextToTable(String &TextTableName, 
  String &DBTableName);

void ViewTable(String NewFile);

Table A: Field-type specification cross-references

Flg	Macro name	Schema	SQL
'c'	CHAR_TYPE	CHAR	CHAR
'd'	DATE_TYPE	DBIDATE	DATE
't'	TIME_TYPE	TIME	TIME
'D'	DATETIME_TYPE	TIMESTAMP	TIMESTAMP
'm'	CURRENCY_TYPE	FLOAT	MONEY
'f'	FLOAT_TYPE	FLOAT	FLOAT
'i'	SHORT_TYPE	NUMBER	SMALLINT
'l'	LONG_TYPE	LONGINT	INTEGER
'b'	BOOL_TYPE	BOOL	BOOLEAN

I tested the SQL types using Paradox and Access 97. All the types are supported in both environments except BOOLEAN. The MS Access Boolean specifier is LOGICAL and doesn't accept BOOLEAN as a field type in a CREATE TABLE statement.

Using a table similar to the one shown in Table A adds considerable flexibility to your application. Modifications to the program's macros accommodate requirements across multiple databases. Simply changing BOOLEAN to LOGICAL in the source code allows you to create an Access table instead of Paradox.

Double mapping also provides flexibility. For example, the schema specification doesn't support a currency field type. I wanted to create a database table using currency fields. So, I simply mapped the schema's FLOAT type twice--once as FLOAT and once as MONEY. When creating the schema file, the program uses FLOAT for both. However, the SQL command uses either FLOAT or MONEY, as specified.

TBatchMove

Listing B: Code required for porting data from a text table to a database table

  String dbTable(DBTableName);
  dbTable +=  DB_FILE_EXT;
  tblPdox->TableName = dbTable;
  tblTxt->TableName = TextTableName;
  dbTxt->Connected = true;
  dbPdox->Connected = true;
  tblTxt->Active = true;
  tblPdox->Active = true;
  BatchMove1->Execute();
  tblPdox->Active = false;
  tblTxt->Active = false;
  dbTxt->Connected = false;
  dbPdox->Connected = false;