Databases - Shorten SQL Names

When using the Interbase DataPump (a freeware program that reads and converts any ADO/BDE/ODBC source to an Interbase / Firebird database) to convert MS Access databases to Firebird, many of the generated "names" were longer than 31 characters (the maximum allowed in Firebird).

Some datapumps automatically shorten long names - but the results are not always satisfactory.

Shorten_SQL_Names.exe works with the Interbase DataPump to semiautomate the process - it

Creates a list of long names (as an ini file)
Requires the user (you) to manually enter the 31 character replacement string
Modifies the SQL file
Provides documentation of what was changed

Note, however, that Shorten_SQL_Names.exe is NOT a fully automatic program - each step requires manual input.

Several parameters can be entered via the command line
Strings that need shortening are specified via an ini file
The program can create a list of long names

There are several different ways to use the application.

Generate a list of long names

The first time you work with a new sql file produced with the Interbase DataPump, there is a high probability that some of the names will be too long (longer than 31 characters).

In this case, Shorten_SQL_Names.exe will generate a new ini file that contains a list of all the long names.

Use File / Open to select an *.sql file
Use Parse SQL! to find the long names
Use copy and paste (Ctrl-C and Ctrl-V) to copy text from the Long Names tab to the ini File tab
Edit the ini file to create a translate table
Copy the guide line with the numbers as often as you like - it is ignored
Use Configure / SaveAs... to save an ini file containing the long names and their replacements
Use a name like Shorten_databaseName.ini
Select Run! (this converts the SQL file)
Use File / Save (or SaveAs...) to save the converted SQL file

Find new long names

If you have continued to develop the MS Access database, then follow this procedure to locate new long variable names. Long names already in the selected ini file are ignored - just the new one are located.

Use File / Open to select an *.sql file
Import the existing ini file via Configure / Open
Use Parse SQL! to find the long names
Use copy and paste (Ctrl-C and Ctrl-V) to copy text from the Long Names tab to the ini File tab
Edit the ini file to create a translate table

This is why there are 2 tabs

One for the long names
One for the ini file

This lets you see what has changed.

Convert an SQL file without checking for long names

This method converts an sql file using an existing ini file. There are no checks to see if any names are too long.

Use File / Open to select an *.sql file
Import the ini file via Configure / Open
Select Run! (this converts the SQL file)
Use File / Save (or SaveAs...) to save the converted SQL file

To run this program in batch mode, (use this when there should not be any problems)

Specify the input and ini files on the command line
Set the Quite flag - /q

For example

Shorten_SQL_Names.exe /q /i "Testcases\Samples_Test.sql" /ini "Testcases\Samples_Test.ini"

Other INI file parameters

In addition to the translation table, the ini file has 2 additional parameters

[General]
max_len = 31
spacing = 42

These are used to set the maximum parameter length and to line up the equals signs. (You probably won't want to modify these.)

As of 01-13-05, these NOW work :)

Names

Throughout this page, I have used the term names.

Firebird requires all "names" to be 31 characters or less - these names include

Table names
Field (column) names
Generator names
Stored procedure names
And the like

In MS Access, names only have to be unique within the local "name space". For instance, every table can have an index named PrimaryKey because each table is associated with its own name space.

However, in Firebird, those index names are all in the same name space. Therefore, datapumps modify the names by concatenating the table name with the field name. If a generator is associated with some field, or trigger is associated with a table, the datapump also creates unique names for these. As a result, some of the generated names end up significantly longer than the specified maximum (31 characters). For example, the following names are related to the Instrument_Metrology_Log_tbl - the example shows both the automatically generated long names and the manually shortened versions. (The numbers are used to help get the lengths right.)

123456789 123456789 123456789 ^23456789      123456789 123456789 123456789 ^234

INSTRUMENT_METROLOGY_LOG_TBL_PRIMARYKEY    = Instrument_Metrology_Log_tbl_PK
INSTRUMENT_METROLOGY_LOG_TBL_INSTRUMENT_ID = Instr_Metrology_Log_tbl_InstrID
INSTRUMENT_METROLOGY_LOG_TBL_USER_ID       = Instr_Metrology_Log_tbl_User_ID
GEN_INSTRUMENT_METROLOGY_LOG_TBL_ID        = GEN_Instr_Metrology_Log_tbl_ID
TRIG_INSTRUMENT_METROLOGY_LOG_TBL_BI       = TRIG_Instr_Metrology_Log_tbl_BI

In addition, there are numerous reserved words that are legal field names in MS Access, but can not be used in Firebird Dialect 1, such as

Type, Value, Password, unique, Date, Current
Database, Table, Action, Date, Active, Order

The "correct" solution is to rename these in MS Access. Another solution is to quote the field names and use Dialect 3.

Note: This discussion indicates that you can design an application that moves almost seamlessly from MS Access to Firebird.

Typical long names

In my design, the most common problem names end with PrimaryKey. Depending on the available space, these are shortened to one of these

PrimKey    PKey    PK

Author: Robert Clemenzi - clemenzi@cpcug.org