CapeSoft File Manager 3
Documentation

Follow these simple steps below. If you're an FM2 user upgrading to FM3, then check out the Converting your FM2 apps to FM3 section in the docs.

Please note: At this point, please ensure you have completed the installation process by running the Support ABC Template Utility! In Clarion 6 and earlier, you do this via the Application | Template Utility menu in the IDE. In Clarion 7, use the Template Utility button (a cog wheel icon). Then select the SupportABC template utility in the utility templates list.

Step 1. Adding the Global Extension (multi-dll apps do this in the data dll)

• Open your app.
• Click on the Global Button.
• Click on Extensions.
• Click on Insert.
• Select the Activate FM3 Extension from the list (check out the Template Reference: FM3 global extension template for more details).
• Click OK.

Step 2. If you're using a SQL backend - you must add the SQL_Connect window (multi-dll apps do this in the data dll)

• Open the Fm3 Global extension you added in Step 1, and click the "Import SQL Connect Procedure" button on the General tab.
• You will see a new procedure entitled "SQL_Connect" added to the application tree. You'll need to set the Global Owner variable in the SQL_Connect window's "Connect To SQL Backend" Control Template prompts. To do this, double click on the SQL_Connect window and click on the "Connect To SQL Backend" control template to bring up the prompts and enter the Global Owner Variable in the field provided on the General tab.
• (Normally done automatically, but worth checking) In the FM3 Global Extension template (on the AutoUp tab) you must set the SQL Connect procedure to the newly created SQL_Connect procedure. Goto Template Reference: FM3 global extension template for more details.
• You need to add the project define to your application (if you are not using MultiProj's Driver Substitution) - check out the SQL_Connect window Control template for the project defines you need to add to your project depending on which driver your application is supporting.

Step 3. If you're using AutoBUILD/AutoFIX, then add the RuntimeFileManager procedure

• Open the Fm3 Global extension you added in Step 1, and click the "Import RuntimeFileManager Procedure" button on the General tab.
• (Normally done automatically, but worth checking) In the FM3 Global Extension template (on the AutoFIX tab) you must set the RuntimeFileManager to the newly created RuntimeFileManager procedure. Goto Template Reference: FM3 global extension template for more details.
• In your Global properties (on the File Control tab), make sure that you've checked the 'Generate all file declarations' checkbox.

The Alternative - creating a separate application to handle file conversions.

What to do in your Dictionary

• You will need to add a Version item to each file's user options list and set it to 1. You will need to increment the version number every time you make a change to the file properties or file structure.
• If you are using SQL - then each table's owner must be set to use the owner variable that is use in the SQL_Connect window.
• For other file options check out the section on How to make basic file structure changes

That's basically all you need to add to your application. To find out more detail on the different aspects of FM3, read the following documentation sections: AutoUP, AutoBUILD, AutoFIX and AutoNET. Oops - before getting too excited, don't forget to read The Rules.

To find out what you need to do the first time you deploy your FM3 application, check out the UPGFile section.

Here are the RULES! If you break them, then you're on your own! If you obey them, you're well on your way to being a very happy customer with friendly support! <g>

• NEVER EVER decrease the version number!!! Do not do it!
• NEVER EVER delete the upg.tps file! Do not do it! (Check out the UPG.tps in the AutoUp section)
• Once FM3 is applied, DO NOT change the prefix of a file. FM3 uses the File Prefix to identify files. If this is absolutely necessary, see Changing the File Prefix.
• Add FM3 to your application and run it before making any dictionary changes. i.e.: if you already have an Error 47 or D00001 in your app, see the How to handle a Pre-Existing Error 47  or D00001 section.
• Before contacting Support, make sure you have obeyed the above Rules, and have read the Support section in the documentation!
• Do not convert your file using the Clarion dictionary convertor. This will make your UPG.tps file out of sync with the file structure of the data table.
• Do not re-use file-prefixes (if you delete a file and then re-use that file prefix, FM3 will not work). Use a new unique one.
• You MUST backup your users' upg.tps file with their data. Think of it as part of their dataset. Would you not backup their customers.tps file?

Short Description: Automatic Runtime File structure upgrading, and converting.

AutoUP Contents

• Who Should Implement AutoUP?
• Description
• Implementation
• How to make basic File Structure changes
  • How to add / change the version number(NB: Must be done for every file structure change!)
  • How to change a file name
  • How to change a field name
  • How to change the file prefix
  • How to prime new fields
  • How to add / change a file password
  • Other Dictionary User Options Available
• Overrides (also check the Dictionary User Options Quick Reference and Dictionary User Options)
• The UPG File (upg.tps)
• Miscellaneous
• SQL

Who should implement AutoUP?

• Anyone who understands the time and frustration of upgrading client sites with the new file structures for every change.
• SQL Users, if you are using one of the currently supported drivers / databases, and you don't have a DBA.
• Those wanting to convert from 1 supported file driver to another.

Description

AutoUP allows your program to automatically upgrade it's own data files when they need to be upgraded. This means that all you have to do is change the dictionary, re-compile and distribute. AutoUP will do the rest.

When you change your dictionary you can change anything. The file driver, the password, add fields, remove fields, change keys, make arrays bigger or smaller etc. You can also change field types, including memos and strings etc.

AutoUP will also create the file if it does not exist. For this, please ensure the CREATE file attribute is on (if you don't want to do this in an SQL app, then checkout the FAQ section).

Currently UnSupported Structures

• Dimensioned Groups(e.g.: MyDimGrp   group,dim(5)).
• SQL Drivers do not support Memos (see the SQL Tips and Tricks section for workarounds).
• SQL Drivers do not necessarily support the standard Date and Time datatypes (see the SQL Tips and Tricks section for details).
• SQL Drivers do not support Dynamic Indexes.
• SQL Relational Integrity on the backend.
• DAT file (Clarion 2.1) to TPS where the files are OEM collated. The DAT file driver uses a different algorithm to the TPS file driver to interpret OEM characters. So although byte-for-byte what's in the file will be the same, the characters are interpreted differently.

Note: Ordinary Dimensions, and ordinary Groups are supported!

Implementation

Before applying AutoUP, make sure you have assessed your current situation, and read the relevant documentation. AutoUP works differently in different applications, and it is vital you have an understanding of some of these things before attempting an upgrade. For example, AutoUP is applied differently between ABC and Legacy application, Single exe and Multi-DLL, and even between different file drivers. Please follow the instructions for your specific scenario.

If you haven't already done so, run through the Jump Start section of this document.

Adding AutoUP to your Single exe ABC Application

On the Global Extension, AutoUP Tab, check on the file drivers used in your application, including any you have used in the past. Goto Template Reference: FM3 global extension template for more details.

Adding AutoUP to your Multi-DLL ABC Application

• For Multi-DLL ABC, the Global Extension should be added to the DATA DLL only.
• On the Global Extension, AutoUP Tab, check on the file drivers used in your application, including any you have used in the past.
• If you are using a Runtime File Manager and / or Connect Template (SQL), then these procedures should also be added to the DATA DLL. You can use the Template Utility to do this.
  You may Export the Runtime File Manager.

Note: SQL users in Clarion 6 Gold: Due to a bug in Clarion 6, it is recommended you add the FM3 Global Extension to the main exe app as well as the DATA dll. Make sure you check on the necessary driver support, but leave the Connect Procedure Blank! This will allow your program to be properly terminated, instead of the process hanging at 90% CPU.

Adding AutoUP to your Single exe Legacy Application

• On the Global Extension, AutoUP Tab, check on the file drivers used in your application, including any you have used in the past.

Adding AutoUP to your Multi-DLL Legacy Application

• For Multi-DLL Legacy, the Global Extension should be added to ALL DLLs and the exe.
• On the Global Extension, AutoUP Tab, check on the file drivers used in your application, including any you have used in the past.
• For each DLL app, add a source procedure named: <appname>:FM3Init, where <appname> is the name of the app.
• Open the Embed Editor for this procedure, and double click on "Processed Code".
• Select VLDS_DLL - FM3 DLL Init Function from the list:
  
• Now, in the EXE app, Global Extension, Options Tab, click on the Root DLL Init Names button.
• Fill in each of the names created above.
• If you are using a Runtime File Manager and / or Connect Template (SQL), then these procedures should also be added to the DATA DLL. You can use the Template Utility to do this.
  You may Export the Runtime File Manager.

Adding AutoUP to your IP Driven Application

• Add FM3 to the Data Management DLL (That's in the IP Server app.)
• Remove FM3 from your Client app if it exists, whether it's a multi-dll or single exe.
• If you are using a Runtime File Manager, then leave FM3 in your Client app data dll, and add the RuntimeFileManager there.
• If FM3 remains in your client app, ensure that the IPDriver check box is checked on in the Global Extension.

NOTE: It's a good idea to use a variable for the connection string for the IPServer (i.e. in the owner field). Otherwise, if you need to change it, FM3 will think you're making a file change and request a version number increment.
NOTE: You must copy the FM3 dll (c6fm3x.dll for Clarion6 applications located in the Clarion\3rdparty\bin directory) to the ClarionDataServer directory (where the Clarion IPDriver Server resides). If your data server dll resides in a route directory, then the fm3 DLL must be in that directory as well.

Some additional cases:

Case 1:
Note: If you would like FM3 to manage local client-side files, then leave FM3 in your data dll or exe, and check the IPDriver check box on the Global extension.

Case 2:
If you have applications that use the TPS files on the IPserver as local TPS files, then these applications will need to use the upg.tps file that exists in the IPServer database. These applications can be treated as normal TPS applications (where adding FM3 is concerned). If you have Multi-Proj, then you can use the Driver Substitution feature to make 2 exes from the same application - one that is IPDriver enabled and one that is TPS enabled. Otherwise you will need 2 apps - one for IPDriver and one for TPS.

Case 3:
Your data does not exist in the directory in the IPServer where your data Server DLL resides. Insert the following code into your IPServer DLL (in the 'FM3 Global Embeds | Start of Initialization section' global embed point):

ds_SetUPGPath(<subdirname>) !<SubdirName> would be set to 'Data' to exist in the (for example) c:\ClarionDataServer\Data directory).

Case 4:
You have data both locally and at the IP Server. You would like to access the IP Server only if there is a connection available.
At this stage, the table management is not optional at application level. In order to cater for this situation, you need to pull FM3 out of your application and make 2 separate applications for upgrading - one for the IPServer files, and one for the local files. In each app, you need to turn the Generate all Files checkbox off (This is on the Global Properties), and manually force the compiler to generate each IPDriver file (for the IP Driver management app) and then for each TPS file (for the local upgrade app) - also on the Global Properties - Individual File Overrides tab. On the Runtime File Manager of each new application you have just created, you'll find the RuntimeFileManager Control Template. Set the Use Files option to Files Used in this APP.

Known issue: Once adding FM3 to the IP_Server Dll, a Memory Block free'd twice error occurs on unloading of the dll. We are not certain of the cause, but this will only occur when you close the IP_Server Dll. The message will remain for about 20 seconds, and then disappear harmlessly. Not recommended: Further to this, you can set your IPREQ service to not allow interaction with the desktop. This will ensure the error is not visible - although be careful, because if you have any errors, they will not be displayed, so Possible file upgrading without changing the version number errors will cause your DLL to freeze on the IP Server.

AutoUP and the Dynamic File Driver (DFD)

Because the majority of DFD usage is using handcoded data structures, you will need to handcode the FM3 functionality

• You will need to make a call to the ds_AddDriver function - if you have no other files using that file driver in your dictionary (IOW this function should be called once for each driver that your application's files use, and that FM3 is required to upgrade).
• After creating the DFD data structure, make a call to the ds_UsingFile function. You will need to manually increment the version number in this call each time you make a file structure change.

The alternative - Creating a Separate Upgrading Application

Many times it's a good idea to have a separate application to upgrade your datatables. This means that the upgrading of the tables is done once at a scheduled time. Normally you would run this application from your program install, as one of the last thing the installer does. You can do this very easily as follows:

• Create a blank application based on your dictionary.
• From the Application | Template Utility menu, select the CreateFM3ConvertorApplicationxxx utility.
• If you're wanting to include a SQL_Connect procedure in this application, then check the Include a Connect Procedure checkbox, otherwise leave it blank.
• You will see the RuntimeFileManager and SQL_Connect (if selected) procedure(s) added to this application.
• You will need to setup the FM3 global extension template as you have in your application (for settings pertaining to AutoUP).
• You will also need to create the necessary project defines in your project to include the supported drivers.
• You will also need to modify the ConnectToSQLBackendControls control template prompts (on the SQL_Connect window) to match those in your application.

NOTE: If you don't want the create attribute on in your dct for your tables that must be upgraded, then you can remove FM3 from your main application (you will need to build your owner string if you're using SQL manually), and Set the Applications Global Properties | File Control | Create to "Create:None". In your FM3 upgrader application, you can set this to "Create:All"

Where to from here...Assess your scenario:

• You are adding AutoUP to a new Application.
• You are adding AutoUP to an existing Application.
• You are adding AutoUP for a cross-driver conversion (e.g. TPS to SQL).
• You are adding AutoUP to a DET or DEF Application.

You are adding AutoUP to a new Application.
You are ready to run your application, make file changes, etc. Now read about How to make file structure changes.

You are adding AutoUP to an existing Application.
The key here is that you do not already have an Error 47 or D00001 Invalid File Declaration present. This is especially important for TopSpeed and Btrieve files. AutoUP keeps a history of every file structure, and uses this to upgrade from the old structure to the new structure. It cannot do this if the file structure is already wrong. These structures are stored in the upg.tps file. Here are a list of suggestions for getting around this:

• Change your dictionary back to a non Error 47 or D00001 state for the file, and run your program to allow AutoUP to capture the file structure.
• Build your own conversion program for this file.
• Use the Dictionary Conversion tool.

The key is that there should be no Error 47 or D00001s upon initial running of your AutoUP enabled application.

You are ready to run your application, make file changes, etc. Now read about How to make file structure changes.

You are adding AutoUP for a cross-driver conversion (e.g. TPS to SQL).
Please ensure you have first read the "You are adding AutoUP to an existing Application" section.
If converting to a SQL backend, it is recommended you read the SQL Tips and Tricks section, as well as Converting your application to SQL.

You are ready to run your application, make file changes, etc. Now read about How to make file structure changes.

You are adding AutoUP to a DET or DEF Application.
DET Users should follow any Legacy specific code unless otherwise stated.
DEF Users should follow any ABC specific code unless otherwise stated.

You are ready to run your application, make file changes, etc. Now read about How to make file structure changes.

How to make basic file structure changes

As mentioned previously, you can change almost anything in your file structure. You can change datatypes, increase and decrease data lengths, add and delete fields and keys, memo to string and vice versa, etc. Please take note of the Limitations you can make to changing file structures.

Changing a File Structure

• How to add / change the version number(NB: Must be done for every file structure change!)
• How to change a file name
• How to change a field name
• How to change the file prefix
• How to prime new fields
• How to add / change a file password
• Other Dictionary User Options Available

How to add / change the file version number

In some cases, there may be a little more to be done, but for the most part, this is all you need to do:

• Make the necessary change in the dictionary.
• Then, for each file you have changed, go to the file Properties, Options tab. Either add or increment the following:
  • Clarion 5:
    
    
    
  • Clarion 5.5 to Clarion 7:
    Add a string type property, name it Version, and set the value to 1 (or increment if already exists).
    
    
    
    
  • Clarion 8:
    Add a Predefined Option Property and select the File Version Number (FM3) from the CapeSoft | FM3 group of file user options and set the value to 1.
    
    

Important: Make sure you increase the version number every time you change the dictionary. If you forget, a warning messages will be displayed the next time you run the app. It is strongly advised that at that point you end the program, and go back to the dictionary, increase the version number without making any further changes, and re-compile.

Notes:

• Never decrease the version number - ever!
• You do not need to increase the version number of files you have not changed.
• You do not need to change the dictionary version number.
• You do not need to make any changes to your app.
• You do not need to set and change the version number for an alias file. It uses the parent file's version.
• You only need to add the version when you makes changes. FM3 defaults each file to Version 0 for files without a version.
• Do not use the Dictionary Browse and Convert functions!
• If using the Multi-Proj Driver Substitution feature, please read the Hot Topics section.
• FM3, and other 3rd Party tools use other user options. To add multiple user options in the dialog, see below:
  • Clarion 5:
    
  • Clarion 5.5 and above:
    

How to change a file name

As mentioned above, in some cases of file structure changes, a little more info is necessary to enable FM3 to perform it's duty. In cases where you change the name of an object such as a file, you need to tell FM3 what the OldName was, so that it can match it up during upgrade. To do this, after changing the file name, add the following string user option to the file properties options tab:

Property	Value
OldName	MyOldFileName.tps

or for Clarion 8 and up:



Note: No quotes should be used!

You can make additional oldname attributes by adding a sequential (up to 14) number to the end of the attribute. This is useful if you find you're having to rename a file again (which is especially useful when upgrading to SQL):

Property	Value
OldName	MyOldFileName.tps
OldName1	My2ndOldFileName.tps
OldName2	My3rdOldFileName.tps

How to change a field name

As mentioned above, in some cases of file structure changes, a little more info is necessary to enable FM3 to perform it's duty. In cases where you change the name of an object such as a field, you need to tell FM3 what the OldName was, so that it can match it up during upgrade. To do this, after changing the field name, add the following string user option to the field properties options tab:

Property	Value
OldName	MyOldFieldName1
OldName1	MyOldFieldName2
OldName14	MyOldFieldName14

Note: No quotes should be used.

You can have up to 14 old field names listed.

How to change the file prefix

WARNING!! This is NOT recommended for use, but is documented for use when absolutely necessary. Please contact  before attempting this change!

Changing the file prefix was previously not allowed in FM3. FM3 uses the file prefix as a unique identifier for the file in the upg.tps file. We have now added the ability to change the file prefix. However, this is not advised! If you change the prefix, you should not use the old prefix for any future file, as this could cause error 47's. FM3 will still associate this old prefix with the original file. Also, this will only work if your new prefix is alphabetically directly after the the old one! If you must change the file prefix, after changing the file prefix, add the following string user option to the file properties options tab:

Property	Clarion 8 Property Description	Value
OldPrefix	Old prefix for the table	OldPRE

Note: No quotes should be used!
Note: Do not make any other changes to the file while performing this upgrade!

For more information, and syntax differences in Clarion versions, read the Dictionary User Options section.

How to prime new fields

When you add a new field, it is quite common to want that field to be filled with data for all existing records in the file. AutoUP provides an easy way to do this. Go to the Properties dialog of the field you've added, and on the Options tab, add one of the following:

To prime a field with numeric data

Property	Clarion 8 Property Description	Value
SetIfNew	Default value of the field when added to the table	44

To prime a field with character data

Property	Clarion 8 Property Description	Value
SetIfNew	Default value of the field when added to the table	Hello World!

To prime an autonumber field

AutoUP can automatically prime a new autonumber field for you. It will start at 1 and increment by 1 for each record.

Property	Clarion 8 Property Description	Value
SetIfNew	Default value of the field when added to the table	AutoNumber

Note: SetIfNew AutoNumber is not supported in SQL. Check out AutoNumber in the Dictionary User Options for more details.

To prime a field with an existing fields data

In some circumstances, you may want to prime your new field with data from an existing field. A common example of this would be adding a new Postal Address field, and priming it with the existing PhysicalAddress field.

Property	Clarion 8 Property Description	Value
OldName	Default value of the field when added to the table	PRE:FieldName   (For SQL files, use the SQl fieldname as it appears on the backend)

Note: This variable will be set ONCE before each file conversion, so the same value will be populated into the new field for each record changed. If you are wanting to populate each variable with a unique value, then you need to implement a callback procedure. Follow the steps in FAQ34 to change values during the file upgrade.

Tip: To prime a field with an existing field in the same table's data, use the oldname user option of that existing field.

NOTE: For SQL files, using this will move the data from the one SQL field to the other (in accordance with the renaming field feature) - unless the oldfield still exists in the dct. If the old field exists (in the dct) then the data will be copied from the one filed to the new field, otherwise the oldfield will be renamed to the newfield (and not exist on the backend).

To prime a GUID field

AutoUP will automatically prime each empty GUID field it finds during upgrade. The field must be named GUID, or PRE:GUID and must be a STRING(16). NOTE: Not available for SQL files at the time of writing this doc.

To prime a field with a value from a function

AutoUP can automatically prime fields using a function (or variable). Note that you need to prime variables for this function before FM3 initializes:

Property	Clarion 8 Property Description	Value
SetIfNew	Default value of the field when added to the table	' & today() & '

Note: This function will only be called ONCE before each file conversion, so the same value will be populated into the new field for each record changed. If you are wanting to populate each variable with a unique value, then you need to create a process to run through the file and populate the new field with a variable.

How to add / change a file password (Owner)

For non-SQL drivers, the owner attribute serves as a way to password protect your files. For SQL, the Owner attribute of a file serves as a connection string. (For converting an encrypted TPS file to SQL, use the OldOwner user option)

For security reasons, it's better to use a variable for this value, and prime it inside your program. In order for FM3 to be able to manage the file correctly, you'll need to prime this variable before FM3 Initialisation. The best place to place this code would be in the Program Setup Embed with the Priority set to 2000. Please note, this priority must be set less than 3000 thousand to ensure it executes before FM3 Initialisation. Alternatively, you can place it in the FM3:Start Of Initialisation Embed.

NB: If you are adding or changing the password for a file, it is absolutely necessary to change the name of the file too, to allow FM3 to continue managing the file. See Changing the name of a file above for more info.

To set the owner attribute as a variable, place an exclamation mark ("!") in front of the variable name. For example:


For more information on the SQL Connection String, see the SQL Reference.

AutoUpLimitations

See also the SQL Reference for SQL specific limitations.

General:

• You can't have dimensioned groups in an FM3 managed table.
• OLD Limitation: You used to not be able to change a DATE or TIME to a LONG (or visa-versa) - as FM3 moved the data byte for byte without interpreting the bytes.

BTrieve Specific:

• You cannot have the Memo=single attribute on BTrieve files.

Dictionary User Options

Introduction

Dictionary User options are a nifty feature in Clarion that allow you to declare and prime custom variables for use in custom templates. These user options are available at all levels, including dictionary options, file options, field options, key options, and relation options. To add user options to the dictionary, you go the Options tab of the desired object's properties (so either in the file user options for the file property and so on for the others). You can select what datatype the option should be, and then give it a name, and a value. The datatypes available are String, Boolean, Integer, and Ini. All FM3 User Options use the String type. The most common FM3 user option is of a String type. It belongs at File level, and is called Version. Here's a few screen shots demonstrating this:



Right click on your file, and select properties.



Go to the options tab, right click on the list box, and select insert.



Ensure that the String radio is selected (Clarion 7 and below) or Predefined (Clarion 8 and up), and then type the name of your user option (Clarion 7 and below) or select from the list (Clarion 8 and up).

Finally, click in the right hand column of the list box, and set it's value.

The following section is the FM3 User Option Reference. Level refers to the object properties you need to use - ie, file, field, key etc. So, if you need to add a Field Level User option, go to the field properties dialog, and select the option tab there.

Dictionary User Options Quick Reference

Here is a Quick Reference guide to all FM3 User Options.

Property	Type	Value	Level	Example	Notes
Dictionary/FileUser Options:
DisableUniqueKeyNameCheck	String	(0) / (1)	Dct	Disables the unique keyname check assert.
DctMasterFields	String	(0) / (1)	File/Dct
DctMasterKeys	String	(0) / (1)	File/Dct
AddDateTimeChangedTriggers	String	(0) / (1)	Dct
CreateConstraints	String	(0) / (1)	Dct	Create constraints instead of indexes when using DCTMasterKeys.
File User Options:
Version	String	(current value + 1)	File
OldName	String	(OldFileDiskName)	File	OldName MyOldFile.tps
OldOwner	String	('OldOwner')	File	OldOwner 'MyTPSOwner' or GLO:TPSOwner
FM3IGNORE	String		File
IgnoreDriver	String		File
TableSpace	String		File
OldPrefix	String		File
NoAutoNameCheck	String		File	Disable the unique disk filename check
File:AddDateTimeChangedTrigger	string	(0) / (1)	File	Override the dct option if necessary
Field User Options:
OldName	String	(OldFieldName)	Field	OldName PRE:OldFieldName
SetIfNew	String		Field		For SQL builtin functions, prefix the function with SQLFUNC_ - eg: SQLFUNC_getDate()
RealField	String		Field
UnRealField	String		Field
AutoNumber	String	(1) / (1,1)	Field	MSSQL takes seed, increment. Other backends (that don't support seed) can simply use the increment value.	Take a look at Server side Auto-incrementingfor details.
DctMasterField	String	(0) / (1)	Field
ForceSQLDataType	String	(SQLDataType)	Field	ForceSQLDataType Binary
CaseSensitive	String	(0) / (1)	Field	Forces a case sensitive collation in SQL
Key User Options:
IndexSpace	String	(0) / (1)	Key
ZeroNull	String
DctMasterKey	String	(0) / (1)	Key
IgnoreDuplicates	String	(0) / (1)	Key	For SQL: Does not post an error when an insert fails due to a duplicate record insertion.

Dictionary User Options In Detail

Version

This is the most important user option of the lot. In order for FM3 to maintain a valid history of the data structures for a file, it must keep track of the file version. This is a STRING user option added at FILE level. You must increment the value of this user option after every change made to that particular file. When you first declare a file in your dictionary, you do not have to add this user option, as FM3 will assume the file is new, and therefore at version 0. The next time you change that file structure, you must increment the version number by at least 1.

Dictionary > Right Click on file > choose Properties > Select Options tab > Right click on list box > Insert > Select String type > Set the label to Version > Click OK > Click on the right hand side of list box in line with the Version label > set value to 1 (or n + 1).

For more information: See How to make basic file structure changes.

OldName(File Level)
When changing the physical name of a file, the OldName user option should be added to the FILE level options. It should be of STRING type. The label should be OldName, and the value should be the name of the previous file.

If this is a constant, then your OldName user option should like this:



(Note the lack of quotation marks in the value)

If the name of the old file must be a variable, then you can enter this in the value as follows:



(Note: You must prime the variable before FM3 initializes.

For more info, check the How to change a file name section of this doc.

OldName (Field Level)

When changing the name of a field, the OldName user option should be added to the FIELD level options. It should be of STRING type. The label should be OldName, and the value should be the old field name INCLUDING the file prefix- e.g. :if changing the Product Name field from ProdName to ProductName, then the OldName user option value would be PRO:ProdName (where PRO is the Product file prefix). NO QUOTATION MARKS SHOULD BE USED.

For more info, check the How to change a field name section of this doc.

OldOwner
When changing the owner of a file (this is particularly useful when upgrading an encrypted TPS file to SQL), the OldOwner user option should be added to the FILE level options. It should be of STRING type. The label should be OldOwner and the value should be the old encryption password of the TPS file. You'll need to use quotes (for a constant) - or a variable name (the variable must be set before FM3 Initializes). Note: you cannot use this feature to upgrade encrypted clarion dat files to tps.

Note: When using this feature to remove the encryption from a tps file, you must change the name of the file (see OldName feature) simultaneously. You can only change the owner once for each file (i.e. you can't have more than one oldowner user option for each data file).

SetIfNew
This option is used when priming data into a newly added field. Check the How to prime new fields section for more details.

UnRealField (SQL)
This is a SQL Specific User option, and is used specifically when converting from TPS files to SQL files. This option should be added to the String(8) field that is mapped to a SQL datetime. This string should have a group following containing a Clarion Date and Time field. This group should be declared OVER the string. This is a FIELD Level user option, and does NOT require a value. Not being created correctly? Check GQ1 in the FAQ section

RealField (SQL)
This is a SQL Specific User option, and is used specifically when converting from TPS files to SQL files. This option should be added to the Date AND the Time fields that are part of a group. This group should be declared OVER a String(8) that is mapped to a SQL datetime. This is a FIELD Level user option, and does NOT require a value. Not being created correctly? Check GQ1 in the FAQ section

AutoNumber (SQL)
This is a SQL specific user option. It tells FM3 to create the relevant backend autonumber support on the SQL backend. For example, in MSSQL, it creates an Identity field. Add this user option at FIELD level, and of String type. Set the value to 1.

For MSSQL, you can choose to use the alternative syntax in order to prime the seed and increment values. For example a value of 200,3 will result in the autonumber field starting with a value of 203, and incrementing by 3. Most common, and most recommended is to set the increment to 1. e.g.: 1000,1 will start at 1001, then 1002, then 1003 etc. If you just leave the default syntax (1) then the seed and the increment will be 1.

For other backends, only the increment portion is relevant, so you need just enter (1) for an increment value.

Note: You can only create an Identity field. You cannot convert an existing data field into an auto-numbered field.

Not being created correctly? Check GQ1 in the FAQ section

Take a look at Server side Auto-incrementing for details on implementing the full solution.

TableSpace (SQL)
This is an Oracle Specific User option, and it sets the TableSpace for your oracle TABLES. This is a DICTIONARY or FILE Level user option of String type.

For more information on the option: See Oracle TableSpaces in the SQL Section.

IndexSpace (SQL)
This is an Oracle Specific User option, and it sets the TableSpace for your oracle INDEXES. This is a DICTIONARY, FILE, or KEY Level user option of String type.

For more information on the option: See Oracle TableSpaces in the SQL Section.

ZeroNull(SQL)
This option is used when converting TPS to SQL. TPS does not support NULL. SQL does. In SQL NULL is not the same as zero or blank. This option tells FM3 to treat 0 and Blank data from TPS as NULL, and not insert a 0 or blank in the backend. This option can be used a DICTIONARY, FILE, and FIELD Level.

For more information: See the Hot Tips section in the SQL Section.

OldPrefix
WARNING! This action is not recommended unless absolutely necessary. In order for this to work, you must follow the rules. This is explained in detail in the AutoUP Section. For more information: See How to change a file prefix.

IgnoreDriver
This option is used when converting Clarion DAT files to TPS.

For more information: See Ignoring Pre-Existing DAT files in the Overrides Section.

FM3IGNORE
This FILE Level user option surprisingly enough tells FM3 to categorically ignore the file. FM3 will not manage this file at all. If the structure changes, you will get an error 47 or D00001. It's a String type, and takes no value. The existence of this user option means the file is ignored, regardless of the value.

CreateConstraints  (SQL)
This option (if set to 1) will force FM3 to create your unique keys as constraints on the backend if you are using DCTMasterKeys = 1 (which will default to creating unique keys as indexes).

AddDateTimeChangedTriggers (SQL) and File:AddDateTimeChangedTrigger (SQL)
This option (if set to 1) will add a trigger to the file on the SQL backend to stamp the changed date and time of the SQL file into the gMSSQLFile (that matches that table). This is useful if you are caching tables and need to know when to refresh the table. You can store the date, and just check the stamped date when you need to check whether to refresh the table or not. Your prop:sql to check the file would look something like:

MySQLDummyFile{prop:sql} = 'select ds_field1, ds_field5 from dbo.gMSSQLFile where ds_field1 = ''dbo.MyFile'''

ds_Field5 would then contain the datetime (in currenttimestamp format).

DctMasterFields (SQL)
This is a DICTIONARY and FILE Level user option. It takes a value of 1 or 0. Please see the SQL Field Management Section before using this user option.

DctMasterKeys (SQL)
This is a DICTIONARY and FILE Level user option. It takes a value of 1 or 0. Please see the SQL Key Management Section before using this user option.

DctMasterField (SQL)
This is a FIELD Level user option. It takes a value of 1 or 0. Please see the SQL Field Management Section before using this user option.

DctMasterKey (SQL)
This is a KEY Level user option. It takes a value of 1 or 0. Please see the SQL Key Management Section before using this user option.

ForceSQLDataType (SQL)
This is a very handy little user option. It's a FIELD Level User option, and does exactly what it says. It will force a certain SQL Datatype on the backend for the field. This comes in handy when wanting use use the Binary Datatype, or the UniqueIdentifier, or force another type for the field. Simply set the value to the desired SQL datatype.

Please note: This leaves the ball in your court to ensure the forced datatype matches, or is compatible with the dictionary structure. In other words, if you try to force a datetime datatype on a decimal field, you will get an error 47 or D00001.

Not being created correctly? Check GQ1 in the FAQ section

If you are supporting multiple backends then checkout the FAQs section on how to Force a different datatype for each backend you support.

DisableUniqueKeyNameCheck
This is a Dictionary User Option that is used to suppress the assert warning that is generated by the template is a key has the same name (external name) as another key.

Some Examples:

ForceSQLDataType	tinyint
ForceSQLDataType	varchar(50)
ForceSQLDataType	uniqueidentifier NULL ROWGUIDCOL DEFAULT NewID()

CaseSensitive
This is a field user option that is used to force case sensitive collation for that field in MSSQL (at the time of writing (in Fm3 v4.91) only MSSQL is supported). In the Clarion dct, Case Sensitive sorting is associated with a key, whereas in SQL, the case-sensitive collation is on the field level. This option is particularly useful for GUID fields (where it is automatically applied). This is to ensure uniqueness of a key that is case-sensitive.

IgnoreDuplicates
This is a key user option (used when converting TPS to SQL) which, when set to 1, suppresses the warning message when an attempted record insertion is performed and a unique key is violated by the insertion. The record is simply discarded. NOTE: You must use an external name for the key.

Overrides

You may, for some reason wish to override FM3's default behaviour. This section provides some of those options, and when and why you would use them.

Ignoring Pre-existing DAT files
FM3 automatically searches for any pre-existing DAT files that you may be converting to TPS. If your application does not have pre-existing DAT files then you can disable the search that FM3 does for them. This is necessary when you have DAT files of the same name as your new TPS files, and they are in the same directory.

For example: App 1 uses file Customer.dat and App 2 uses file Customer.Tps, but the two "Customer" files are unrelated.

• To set this option for all files:
  
  • Go to the Global Extension Options Tab.
  • Check on the "No pre-existing Clarion files" checkbox.

• To set this option for a specific file:
  
  • Go to the Dictionary.
  • Open the File Properties Dialog.
  • On the Options tab, add the following String User Option:

Property	Value
IgnoreDriver	1

Note: For more information, and syntax differences in Clarion versions, read the Dictionary User Options section.

Ignoring a particular file
If you wish to ignore a particular file, for whatever reason, then you can do this by using the FM3IGNORE switch. If you ignore a file then it is removed from FM3's sight, as if it didn't exist in the dictionary. This includes the ability for C-Scan to scan the file, if it is a TPS or Btrieve file. If all you want to do is make it be ignored by the File Manager 3 template then use the "Exclude files" button on the Runtime File Manager Extension.

• To set this option for a specific file:
  • Go to the Dictionary.
  • Open the File Properties Dialog.
  • On the Options tab, add the following String User Option:

Property	Value
FM3IGNORE	1

Notes:
In this case, the value can be left blank.
For more information, and syntax differences in Clarion versions, read the Dictionary User Options section.

Downgrading files
By default FM3 prevents your Topspeed, Btrieve, and SQL files from being accidentally downgraded.

By default it allows downgrading of Clarion files, this is a side effect of the support for pre-existing Clarion files. To disable the automatic downgrading of DAT files, see the Ignoring Pre-Existing DAT files section above.

If you want to force a downgrade of a file for any reason then you can attempt it by placing /Downgrade on the command line when running the program.
For example: c:\myexedir>myexe /downgrade

Here's an example circumstance when this may be required:

1. Your clients have version 1 of your app (with FM3 enabled).
2. You ship them version 2. They omit to take a backup of the data before running the new version.
3. The new program runs and updates one or more files. However there is a major problem in your program necessitating a move back to version 1 of the EXE.
4. Usually FM3 would *not* downgrade the files from Version 2 status, but if you run the Version 1 exe with the /Downgrade switch then FM3 will downgrade the files back to version 1 level.

The UPG file (Upg.tps)

The UPG file is what allows FM3 to work it's magic! But, how? you ask...trust me, it's magic! <g>
The upg.tps and data strucutures MUST BE IN SYNC before making any additonal changes to the tables are made.

When do I distribute the UPG file?

Case 1:	You've got a new app that you are distributing to new clients. You are using any file driver.
What you do:	You DO NOT distribute the UPG file. It is created, and managed for you, at the client's site, automatically.

Case 2:	You've got an existing app you want to upgrade. The app currently uses Clarion DAT files (maybe it was written in CPD or CDD). You may or may not be converting the data to the Topspeed / Btrieve file formats.
What you do:	You DO NOT distribute the UPG file. It is created, and managed for you, at the client's site, automatically.

Case 3:	You have an existing app using Topspeed or Btrieve files. You are thinking of doing an upgrade, but you haven't changed any file structures yet.
What you do:	Before changing the file structures, add FM3 to your app, re-compile and distribute. You DO NOT distribute the UPG file. It is created, and managed for you, at the client's site, automatically.

Case 4:	You have an existing app using Topspeed or Btrieve files. You have already changed some file structures in the dictionary since you distributed the program to your clients.
What you do:	If you can revert to Step 3 then do so.
Alternative 1:	Add FM3 in the normal way. Upgrade the existing clients to the new version using one of the older methods (i.e. using the CW IDE or Conversion programs). From then on later versions will upgrade automatically. If you were using the DC Data Convertor templates, then this is probably the route you'd take. Make your last data convertor conversion application. You can either include this in the installer - and only run it when the installer does not find the upg.tps file, or handle the clients on a case -by-case basis (i.e. manually). You will then need to ship the upg.tps file in your installer (with version 1 records in it) - and only install it if the upg.tps file does not exist already.
Alternative 2:	Add FM3 in the normal way. Compile a version of your app using the OLD dictionary structure. Compile and run the EXE. Distribute the generate UPG.TPS file to the existing clients with the next upgrade. You do not need to ship it to new clients, or to clients who have already received it once. Make sure that in your install, you set the installer to only install the UPG.tps file if it is non-existent on the clients machine.

Forcing the position of the UPG file?
By default the UPG file is placed in the application directory, in the same place as the calling program. In certain circumstances you may need to override this. If for example you access the data from a number of EXE's, stored in different locations, or if you don't have write access to the application directory, then it makes sense to store the UPG file in the data directory.

In fact, there is a strong case for getting the UPG file to appear in the data directory.

Here are some of the advantages for this:

• When the user backs up the data the UPG file is also backed up.
• If you take a copy of the users data for support reasons then the UPG file is included.
• On a network, the data directory is likely to be shared, but the application directory would probably not be shared.

To set the position of the UPG file, use the ds_SetUPGPath() function. This should be called in the FM3:Start Of Initialisation Global Embed. Note: you must load the path (if you're using a variable).

Note for SQL users: If you are using SQL, then one cannot store the upg.tps file with the data, so it needs to be stored somewhere else. Here are some options:

1. Option 1 is to have a common directory that all the users can access (although this isn't always possible with an SQL backend).
2. Option 2 (if your user's exes don't all have access to a common directory) is to assign an "always install from this machine" that keeps a local copy of the upg.tps file that it references - and then run the conversion application from the installer.
3. Option 3 (if you don't have the ability to restrict which pc the user installs on) - if every installed copy of the app runs every version - so even if each install maintains their own copy of the application, it doesn't matter who installs from where, because they all will have an up-to-date upg.tps file (albeit multiple copies of the upg.tps file).
4. Option 4 (if there's a possibility that they will run the update from a machine that did not install the last update - and has not run the exe since the last update) then you will need to ship a default upg.tps file with all the possible versions in - although this is definitely the worst possible option.

Some more details regarding the UPG.tps file For those that care:
The versions contained at the client site are the ones that have been entered into his UPG file. He doesn't need other structures (other than that match his dataset) - but if his data structures are not in the UPG file, he'll error 47 or D00001.

IOW if your UPG.tps file in your install does not contain version 3 (but does contain 1,2,4,5,6,7) - and his does (even if it only contains version 3) and his file is version 3, then when you overwrite the UPG.tps file at the client site, it won't be able to upgrade because the UPG.tps file will not contain the matching version 3 file structure.

Let FM3 maintain the UPG.tps file at the client's site.

Miscellaneous

Support for Multiple data sets
Non-SQL:FM3 does support multiple datasets as is (for Non-SQL files). However, it may be a good idea to keep a upg file for each dataset, thus ensuring backup and restore integrity. Generally, changing the data path within the application is done by calling SetPath() or FileDialog(). Simply ensure that for each of these function calls within your program, you call ds_SetUPGPath() directly afterwards to ensure a upg file is created, and maintained in the working directory. You'll need to call ds_CloseUPGFile() before setting the path as well.

SQL: FM3 can only manage 1 SQL database at a time. This is determined by the connection information given on the FM3:Connect To SQL Backend Extension Template. Your program can still access files contained within other databases, but FM3 is not able to upgrade them. To upgrade files in a different database, restart the program, connecting to the required database. Alternatively, you can create a separate conversion application specifically for the other database, and run the conversion application form your installation program to upgrade that database's table structures.

Note: FM3 will still upgrade all non-SQL files if necessary while running your program on an SQL backend.

Very Large Dictionary Support
If you have a very large dictionary, and possibly a lot of Aliased files, together with FM3's initialisation code, this could extend the allowed size of a Clarion module. In such cases, you may receive an error such as: Link Error: Too many segdef in file Error(6): cif$fileclose The handle is invalid.

First thing to try is to clear all your obj and obj32 directories, restart your machine, ensure you are compiling in 32bit mode, and try again. If the error persists, read on.

In such cases, you will need to separate FM3's init code into a few blocks. The VLDS:FM3 Init Code Template does just this!

To add the VLDS:FM3 Init Code Template:

• Create a new soure procedure named FM3InitProc. (Don't forget to check the 'Declare Globally' checkbox on the procedure's properties).
• Open the embed editor, and add the code template VLDS:FM3 Init to the Procedure Code (or Process Code) in this source procedure.
  This prompts for 2 values: Start and End. It also contains a check box called Generate File Loop Only? (You can ignore this unless you're an avid handcoder that want's to do the coding yourself).
• Start is the alphabetic character of file names to start generating from (typically "A"). and
• End is the Last alphabetic character of file names to generate init code for.
  It may well be that you'll need to split the init code into more than one procedure (if after making one procedure you're still getting the Too many Segdef error).
  If this is the case, then you would break the init code by specifying smaller alphabetic ranges for each init procedure.
  E.g.: FM3InitProc might be "A" to "M", and FM3InitProc2 would follow on from "N" to "Z".
• There is also a "File Loop Only" check box. This is to specify whether to generate all init code, or just the file loop.
  For the first init code template, you would leave this box unchecked,
  but for any further init code procedures you declare, you'll need to check this box on,
  to ensure you're not calling other FM3 init code more than once.
• When you're done adding your init procedures, you need to go the Global Extension Options Tab, and specify the init procedures to call (in the Very Large Dictionary Support group).
  • On the Global Extension Advanced Tab, Add in each FM3 Init code procedure in order in the Very Large Dictionary Support section.
    Screenshot:
    

How to handle a pre-existing Error 47 or D00001
We STRONGLY recommend adding FM3 to your program while it "still works". i.e., no existing error 47's. File Manager 3 keeps a history of the file structures in it's upg file. This allows it to upgrade the file later on. If your program is already in an error 47 or D00001 state before adding FM3, it will not be able to interpret the structure. This is particularly important with TPS files. It is recommended for all flat file systems though at least. In some cases FM3 should be able to handle the pre-existing error 47 in SQL files.

If you do have pre-existing error 47, study the various options, and follow these steps to solve the problem and allow FM3 to maintain your database from here on out.

Option 1 - Undo the changes you made in the dictionary.

• If you have a backup of the program in non-error 47 or D00001 state, restore that version, and add FM3 to the program there.
• Alternatively, if you are aware of the changes you have made to the dictionary to cause the error 47, and it is easily undone, then do so.
• If you are unsure of the structure of the current TPS file on disk, use TopScan to view the file structure.
  E.g.: TopScan File > File Layout.

Option 2 - Manually create a conversion program.

• Using the before and after file structures, you can manually create a conversion program in Clarion.

SQL (for FM3 users)

SQL enabled apps with FM3 sometimes require a little more help from you the developer to perform it's duties. Unfortunately, there may be some mindset changes to make in terms of database design and best practices, as Flat-file systems in Clarion are quite forgiving. Before attempting a SQL enabled app, please read through our FM3 SQL Documentation.

If you are adding FM3 to a new SQL application, then you can pretty much follow the AutoUP Implementation without a problem.

If you are converting to SQL, make sure you have familiarized yourself with the Example apps shipped with FM3, and read all the necessary documentation. If the example apps don't work, it's pretty safe to assume that your app wont, so get to grips with them first.

Adding the Connect procedure

• Ensure you have added the FM3 Global Extension.
• Run the 'Import the SQL Connect procedure' template utility. You'll need to set the Global Owner variable in the SQL_Connect window's "Connect To SQL Backend" Control Template prompts. To do this, double click on the SQL_Connect window and click on the "Connect To SQL Backend" control template to bring up the prompts and enter the Global Owner variable in the field provided on the General tab.
• In your FM3 global extension template, on the Auto Up tab, set the "SQL Connect procedure" to 'SQL_Connect' (i.e. the new procedure that the template utility just created).