November 29, 2005
Build 3
This utility exports a Pragma PFM file to SQL. Please note that when we talk about a Pragma PFM file we also include the Pragma Btrieve files.
There are basically two ways that you can export a Pragma PFM
file to PostgreSQL. Also keep in mind that "what you see is
what you export". The export program reads the data from the list control
and no longer from the file itself.
Follow this procedure to export all the records of a PFM file.
It is a "quick and dirty" way to export your data. The generated PostgreSQL table will be a mirror image of your PFM file, with its advantages (compatibility, ease and speed) and disadvantages (potentially sacrifice a bit of the PostgreSQL power).
Validate the PFM file and make sure that you have a current backup.
|
PFM File |
Select the PFM file you want to export from the PFM Files combo. |
|
To and From |
Select, if necessary, the boundaries of the display by entering them in the "To" and "From" edit fields. |
|
Index |
Choose the Index (Key Noun) that you want to sort the files with. |
|
IntExt |
Choose the appropriate filemanager
for the file (PFM or Btrieve). If in doubt, check in 'Tools', 'Options', 'Filemanager') The internal filemanager is 0, the external 1. |
|
MaxListNumber |
Instead of the boundaries you can limit the number of files to export by inserting the maximum number of records to display in the Max List edit field. This can be useful if you first want to make some tests and do not want to deal every time with a huge amount of data. Then, if and when you want all the records enter an absurdly big number. |
|
View File |
After having entered
the parameters hit the View
File button. The selected Pragma file will be displayed
in the big window. The actual Records displayed are indicated in the Records window. |
|
Show Tables |
The Show
Tables button will display all the available SQL
tables in the combo.
If you are not yet connected to PostgreSQL the Connect to PostgreSQL dialog will let you connect to the desired database. Select the PostgreSQL table into which you want to export the Pragma file. You can select an existing
table or create a new table, called with the same name as the
PFM file. Spaces in the name will be converted to underlines. |
|
Get Table |
The Get
Table button will display the headers of the selected or of the
newly created table.
This helps you show where the Pragma file data will be inserted into the PostgreSQL table. |
|
Export |
Finally the Export
button will export the Pragma file to the PostgreSQL table, record by
record.
If a record contains a mistake, a message window will give you the choice to continue without that record or to abort the whole procedure. |
Follow this procedure to partially or completely export a PFM file to PostgreSQL. With a Report Textfile you have control over the fields to export and in what order. Also you can skip to insert data into a table column if you so wish to do.
It is a "slow and clean" way to export your data. The generated PostgreSQL table can be maximized to your needs, with its advantages (latest, greatest SQL table) and disadvantages (partially incompatible with your existing application).
|
Report Textfile |
Create a Report Textfile with a
text editor.
Follow the guidelines of the Pragma 6 Reports paper. All the parameters needed for exporting are clearly marked. |
|
...
|
To select a Report Textfile hit the ... button to the right hand side of the Report Textfile window. A file dialog will pop up and let you choose the appropriate file. |
|
View File |
After having
selected the Report Textfile hit the View
File button. The selected Pragma file will be displayed
in the big window. The actual Records displayed are indicated in the Records window. |
|
Show Tables |
The Show
Tables button will display all the available SQL
tables.
If you are not yet connected to PostgreSQL the Connect to PostgreSQL dialog will let you connect to the desired database. Select the PostgreSQL table into which you want to export the Pragma file. You can select an existing
table or create a new table, called with the same name as the
PFM file. Spaces in the name will be converted to underlines. |
|
Get Table |
The Get
Table button will display the headers of the selected or of the
newly created table.
This helps you show where the Pragma file data will be inserted into the PostgreSQL table. |
|
Export |
Finally the Export
button will export the Pragma file to the PostgreSQL table, record by
record.
If a record contains a mistake, a message
window will give you the choice to continue without that record or to
abort the whole procedure. |
Report Textfile
The Report Textfile is the same textfile that you use to display a report, and the necessary parameters are indicated in Pragma 6 Reports.
Using a Report Textfile lets you export the fields in your PFM records in any order you want, even eliminating some fields if necessary. Say for instance you have in your PFM file the nouns AD NAME, AD COMPANY, AD HOME STREET and AD COMPANY STREET and you are no longer interested in AD HOME STREET and want to put the company column in your SQL table in front of the name column you would add the following lines to the Report Textfile:
Column = 1, 2, AD COMPANY, 100, L
Column = 2, 1, AD NAME , 80, L
Column = 3, 4, AD COMPANY STREET ,120, L
etc.
The example uses on purpose the names of the file nouns so that
you can relate to them directly, even though they will not be exported, unless
you want to create a new table, in which case the names of the file nouns or
whatever other name you choose will be used for the table.
Also the lengths (100, 80 , 120) will not be exported, but if you put different
lengths to the various columns this will facilitate you to quickly see a clear relationship
between the PFM file and the SQL table, since the SQL table will use the same
widths, to optically facilitate your task.
But what if you want to export to a table that for example in the first column has a serial primary key? Add a -1 column to the Report Textfile:
Column = 1, -1, sql_number, 50, L
so that your Report Textfile would then look like this:
Column = 1, -1, sql_number, 50, L
Column = 2, 2, AD COMPANY, 100, L
Column = 3, 1, AD NAME , 80, L
Column = 4, 4, AD COMPANY STREET ,120, L
etc.
You are only allowed to put one -1 column at the beginning of the table. Make sure that you add all the parameters to a -1 column. The are expected to be there.
If you are creating a new table and you have a -1 column as the first column, called for instance sql_number like the example above, the resulting column will be a text column, like all the others. It is then up to you to modify the column. On the whole, if you want a table with special constraints and datatypes you are better off to create the table beforehand with the PostgreSQL administrator.
Follow this procedure to automatically export more than one PFM file at a time, with a Rerport Script file that will create the SQL tables whose column properties you define and then fill them with data.
Let's face it, both procedures are pretty labour intensive, especially if you have to do it repeatedly say to 50 files. With this procedure and the Report Script file that you create the creation of the tables and to fill them will be not quite a pleasure but at least a bearable experience.
It is a "highly automated" way to export your data. The generated PostgreSQL tables can be maximized to your needs.
|
Report ScriptTextfile |
Create a Report Script Textfile with a
text editor.
Report Script Textfile is like a regular Report Textfile only it uses additional parameters that control the operation. The Script Report Textfile will list all the Pragma files to convert, and if necessary point to a Report Textfile of that vill further control the creation ot tables ant the export of data. Follow the guidelines of the Pragma 6 Reports paper. All the parameters needed for making a Report ScriptTextfile are clearly marked. |
|
...
|
To select a Report Script Textfile hit the ... button to the right hand side of the Report Textfile window. A file dialog will pop up and let you choose the appropriate file. |
|
Run Script |
If the selected
Report Script Textfile has enough valid parameters the Export button will
be enabled with the text saying Run Script.
Depending on the parameters specified in
the Report Script TextfileI if there is a problem somewhere the program
will stop and ask for your instructions. For instance if the program
tries to create a table that already exists you can automatically delete
the table and create a new one or you must agree before a table will be
deleted.. |
Report Script Textfile
The Report Textfile is the same textfile that you use to display a report, and the necessary parameters are indicated in Pragma 6 Reports.
Using a Report Script Textfile you indicate the Pragma PFM files that you want to convert. You must list all the Pragma files to export in the textfile.
The parameter ReportScript = true will tell the program that this is actually a Report Script Textfile.
The next parameter needed is what to export, so you must add all the Pragma filenames using the parameter ScriptPragmaFilename. Optionally you can add near the Pragma file the name of a Report Textfile for that Pragma file. In that case the Report Textfile will have precedence over the default values.
A very important parameter is InformWhenDuplicateTablename. By default, when the program creates a new table it checks whether that table already exists and if it does it will give you the possibility to abort and save your precious data. If you are exporting with the script file dozens of Pragma files and maybe have to do it more than once (Practice makes perfect) this parameter comes in handy, since it will brutally overwrite all the existing tables with the new ones without annoying you.
The parameter DebugShowQueries is more for your curiosity since it shows the actual queries that are sent to SQL.. By default this parameter is false.
The export is done Pragma record by Pragma record with the PostgreSQL command INSERT:
The function 'sqlverbs.Set (sql_query)' is the actual function that communicates with PostgreSQL and sends everything to the SQL land.
All the records are exported as text. If you insert text into a columns that say accepts only integers, PostgreSQL will try to convert to a number. In other words, if you have a sequential text number at the beginning of every record, you can have the first column of the table defined to accept only integers and PostgreSQL will do the conversion automatically.
All the records are exported without padding spaces at the beginning or end of each field, like you had to do for Pragma's mock flag 0 key fields. This operation is done w=before inserting the field into the list control. If you want, we can add an option to leave those spaces during the conversion, even if I think that they will no longer be of any use.
The above shown code does not stop if an error is encountered when an INSERT fails, simply that record is not inserted into the table. If an error occurs the export could be canceled and no data inserted into the table. In other words, the insert could not be committed. Errors are displayed in a message box and the export will stop until you acknowledge the problem.
When a Pragma field contains an apostrophe ( ' ), the program adds before the apostrophe a backlash ( \ ) with the function replace, since the apostrophe is used by INSERT to delimit the various field expressions. It works, but I do not know at this moment if there will be negative consequences down the road for you.
Please let me know your suggestions, bugs or whatever you think can be done to this utility and or explanation to make it more useful.
Please write your comments to the Pragma listserver at the following address: pragma@houseware.nl
If you arte not yet on the Pragma listserver list you can subscribe by sending an e-mail to the listserver with "Subscribe" in the subject.