Clarion Community Help

| **Navigation:**  [[welcome to my product .htm|User's Guide and Tutorials]] > The Dictionary > Dictionary Editor >====== Lesson 7 - Dictionary Synchronization ====== | [[lesson 6 trigger properties.htm|{{btn_prev_n.gif|Previous page}}]][[welcome to my product .htm|{{btn_home_n.gif|Return to chapter overview}}]][[lesson 8 advanced features of the dictionary editor.htm|{{btn_next_n.gif|Next page}}]] |
|  ||

This lesson introduces you to the Clarion Dictionary Synchronizer. It takes you through all the steps needed to synchronize two databases'both the data and the data definitions (.dct, .ddf, or SQL script).

This synchronization process saves time in several development contexts: developing new applications to serve legacy databases, updating existing applications to conform to a modified database, and sharing application development among several developers with incremental modifications to the database definition.

**Lesson Objectives**

This lesson introduces you to the Clarion Dictionary Synchronizer. When you have finished the lesson you should be able to

·Consolidate two Clarion data dictionaries

·Run the Synchronizer Wizard

·Convert existing data to a new format

·Create and review a Dictionary Synchronizer log

·Create a Clarion Data Dictionary from a Btrieve or SQL database

{{notebox.jpg|NoteBox.jpg}}

To complete the lesson you will need a copy of the files from the Clarion '//\Lessons\SyncLesson\Start //folder.

Please copy the files to the// '\Lessons\SyncLesson// folder.

In Windows XP, the Lessons folder is found in:

**C:\Documents and Settings\All Users\Documents\SoftVelocity\Clarion7\Lessons**

In Vista and Windows 7:

**C:\Users\Public\Documents\SoftVelocity\Clarion7\Lessons**

**Consolidate Two Clarion Data Dictionaries**

In a team development environment, the Dictionary Synchronizer automates the synchronization of the "master" dictionary or database with other project dictionaries and databases.

This section of the lesson shows how to consolidate two similar but different Clarion data dictionaries into a single dictionary. In this example, the //master.dct// represents the "master" dictionary and the //Project.dct// represents another dictionary used by the development team. The two dictionaries must be consolidated into a single dictionary that all team members can use to continue to develop their application components.

By using the Dictionary Synchronizer to update the //Project.dct// (as opposed to simply replacing it with a copy of the //master.dct//), you are able to see exactly which dictionary items changed and you can intelligently manage those changes; if both dictionaries have changed, you can incorporate the changes from both dictionaries by synchronizing each dictionary with the other; and finally, the Dictionary Synchronizer can generate a Clarion program to convert your existing data to the new format.

{{notebox.jpg|NoteBox.jpg}}

**Whenever you consolidate two Clarion dictionaries into a single dictionary, you risk damaging the relationship between the replaced dictionary and its corresponding applications, unless you first export those applications to text (.TXA) format.**

**Make a Master TXA File**

Make a //Master.TXA// file that you can use to create a master.app that will work with the consolidated dictionary.

**Starting Point:**

**The C:\Clarion7\Lessons\SyncLesson\Master.app file should be opened in the Application Editor.**

1.Choose **Application** {{blttria.jpg|BLTTRIA.jpg}} **Export Application To Text**, then press the **Save** button to create //Master.txa//.

2.Press the {{saveclose.jpg|SaveClose.jpg}} button to save and close the //\SyncLesson\Master.app//.

3.Select **File** {{blttria.jpg|BLTTRIA.jpg}} **Close** {{blttria.jpg|BLTTRIA.jpg}} **Solution** (CTRL+SHIFT+F4) to close the application solution.

**Run the Synchronizer Wizard**

**The Synchronizer Wizard leads you step-by-step through the synchronization process. The wizard allows you to select the Synchronizer Server, the data dictionaries, the dictionary to synchronize (identify source and target), and set rules for matching items (files, fields, keys, etc.) between the dictionaries.**

1.Open //Project.DCT//.

Every synchronization requires an open Clarion data dictionary (although the dictionary may be a newly created one that is completely empty).

{{tipbox.jpg|TipBox.jpg}}

When synchronizing two Clarion data dictionaries, it doesn't matter which dictionary you open first.

2.From the DCT Explorer, press the **Synchronize** button to start the //Synchronizer Wizard// (save the changes).

{{ugl7a.jpg|UGL7A.jpg}}

The **Synchronizer Wizard** is displayed.

{{syncwiz1.jpg|SyncWiz1.jpg}}

3.Verify that the **Prompt Detail** slider to the **More** position.

**Select the Synchronizer Mode**

1.CLICK on the **Next >** button to start a new synchronization.

{{tipbox.jpg|TipBox.jpg}}

This page lets you rerun a prior synchronization. This page also lets you generate a data conversion program without modifying a data dictionary.

**Select the Synchronizer Server**

The //Clarion Dictionary// Server is the default (**Synchronize With**) server. A Synchronizer Server is a .dll that lets the Dictionary Synchronizer access a Clarion data dictionary or a database/file system such as ORACLE, MSSQL, Btrieve, etc. The Clarion Dictionary server handles all Clarion data dictionaries (regardless of the file driver).

**Select the Other Clarion Dictionary**

1.Press the ellipsis (...) button to //Select the "other" database or dictionary// with the Windows file dialog.

{{notebox.jpg|NoteBox.jpg}}

If you choose an SQL, ADO, ODBC, or Btrieve Synchronizer Server, the Synchronizer Wizard prompts you for the logon information needed to connect to the SQL database or for the folder containing the Btrieve .DDF files.

{{syncwiz2.jpg|SyncWiz2.jpg}}

2.Select the //Master.dct//, then press the **Open** button.

**Set the Synchronizer Log File**

The Synchronizer saves details about each synchronization so you can automatically repeat a synchronization process as often as you need to. You can use the **Log Synchronization To** field to name the log file that stores the synchronization information. Press the ellipsis button and select the current working directory (//C:\Clarion7\Lessons\SyncLesson\SynLog.TXT//).

**3.****CLICK on the ****Next >**** button to continue.**

**Set the Dictionary to Synchronize (source and target)**

4.CLICK on the **From ..\Master.dct To Project.DCT** radio button to make Project.DCT the target dictionary.

The Dictionary Synchronizer only modifies the target dictionary. Specify which dictionary is the source and which is the target by clicking the appropriate radio button.

The warning is simply a reminder that for any applications that use a dictionary that will be replaced (copied over), you should export those applications to text (.txa) format before you replace (copy over) the dictionary. A .txa is not necessary if you are simply synchronizing two dictionaries, both of which will survive the synchronization.

5.CLICK on the **Next >** button to continue.

**Select the Source Files to Synchronize**

Select the files, views, and tables within the Clarion data dictionary to include in the synchronization process. The Synchronizer Wizard provides two file lists for the dictionary: on the left is a list of files not synchronized and on the right is a list of files to synchronize. Depending on the database server, the list may show filenames, table names, owner names, etc.

6.CLICK on the **>** button to select the Customer file and related files.

7.CLICK on the **Next >** button.

8.CLICK on the **>>** button to move all the files from the left to the right.

This synchronizes all files from Master.DCT to Project.DCT.

9.CLICK on the **Next** **>** button to continue.

**Set the Synchronizer Options**

10.If it's not already cleared, clear the **Generate Data Conversion Program** box.

The Dictionary Synchronizer options control how the Synchronizer //begins// the synchronization. You may change these settings later on in the process (in the //Synchronize Dictionaries// dialog). See the Dictionary Synchronizer Options for more information on these settings.

{{tipbox.jpg|TipBox.jpg}}

This page lets you request a data conversion program to convert existing data to the new format. Also, the **Matchings** button sets rules for matching dictionary items. The default rules match primarily on file and field names.

11.CLICK on the **Finish** button to accept the defaults and continue.

**Synchronize Dictionaries Dialog**

When you "finish" the Synchronizer Wizard, it loads, analyzes, and compares the two dictionaries, then opens the Synchronize Dictionaries dialog where you complete the synchronization process.

{{syncwiz3.jpg|SyncWiz3.jpg}}

The Synchronize Dictionaries dialog compares the two data dictionaries, item by item and lets you resolve any differences between them.

1.CLICK the Dictionary item (first item in the list) to select it.

Actions you apply to the selected item cascade to all its children.

The //Synchronize Dictionaries// dialog uses a "file centric" hierarchical list to compare the dictionaries. Beneath each item in the list, the dialog nests the item's properties and components (files, fields, keys, relationships, and aliases).

The //Synchronize Dictionaries// dialog uses color to provide information about the synchronization process:

**Blue**

indicates no action is applied, and the item is either different than its matching item or it has no matching item.

**Black**

**i**ndicates an action is applied and the resulting target item is valid, or no action is applied but none is needed because the items already match and the target item is valid.

**Red**

indicates an action is applied and the resulting target item is not valid'the target does not support the applied property. Red may also indicate an invalid condition in the source dictionary'you can clean up the source dictionary and resynchronize, or you can ignore the invalid item and complete the current synchronization.

**Gray**

The item is not supported by the target dictionary.

{{tipbox.jpg|TipBox.jpg}}

Use the navigation buttons and the corresponding navigation menu items to quickly locate new, changed, or invalid dictionary components.

The //Synchronize Dictionaries// dialog uses action indicators to show the action applied to each item. Action indicators are at the left side of the Destination (target) column. The question mark ( **?** ) indicates no decision as yet.

{{tipbox.jpg|TipBox.jpg}}

A gray action indicator indicates a cascaded (or inherited) action'the action was originally applied to a parent (full color indicator) and the child has inherited the action (gray indicator).

2.Right-click on the Dictionary item (first item in the list), and choose **Copy to MoreData** from the popup menu to apply the action to the selected items.

The action cascades to all children of the selected item'that is, to all items in the dictionary.

When you select **Copy to Project.DCT**, the Action Indicator changes to a right arrow ( {{syncarrow.jpg|syncarrow.jpg}} ) for matched items, and to an exclamation point ( **!** ) for unmatched items.

The right arrow ({{picarrw2.jpg|PICARRW2.jpg}} ) indicates the synchronizer will copy the item's properties from the source to the target.

The exclamation point ( **!** ) indicates the synchronizer will ignore the suggested action for unmatched items. This is because the Copy to action only changes existing items in the target dictionary'it does not add new items.

3.Press CTRL + Z to undo the //Copy to// action.

The Action Indicator reverts to the question mark ( **?** ) ' indicating no decision.

{{tipbox.jpg|TipBox.jpg}}

Undo may be applied multiple times. It undoes actions in LIFO (Last In First Out) order.

4.RIGHT-CLICK the Dictionary item (first item in the list), then choose **Merge Into** from the popup menu.

When you select **Merge Into**, the Action Indicator changes to a right arrow ({{syncarrow.jpg|syncarrow.jpg}} ) for matched items, and to a plus sign ( **+** ) for unmatched items. A multi-item indicator on a parent item ( {{parentitem.jpg|parentitem.jpg}} ) indicates different actions for different children. For example, a file may contain both matching fields (right arrow) and new fields (plus sign).

The right arrow ({{picarrw2.jpg|PICARRW2.jpg}} ) indicates the synchronizer will copy the item's properties from the source to the target.

The plus sign ( **+** ) indicates the synchronizer will add the marked items to the target dictionary. This is because the **Merge Into** action not only changes existing items in the target dictionary, it also adds items.

{{tipbox.jpg|TipBox.jpg}}

A gray action indicator indicates a cascaded (or inherited) action'the action was originally applied to a parent (full color indicator) and the child has inherited the action (gray indicator).

5.Press the **OK** button to complete the synchronization and update the //Project.DCT//.

The //Dictionary Editor// dialog shows the updated //Project.DCT//.

The //Project.DCT// now matches the //Master.dct//.

6.Press the **Save and Close** button {{saveclose.jpg|SaveClose.jpg}} to close the //Project.DCT//.

This dictionary now contains all the same information (properties, files, fields, and relationships) as the Master.dct; however, these two .dct files are not identical. The Project.DCT will work with the MoreData.app, and the Master.dct will work with the Master.app, but the converse is not true'the dictionaries are not interchangeable because their internal file, field, and key numbers may be different.

**Remake the Master Application**

**Starting Point:**

**The Clarion environment should be open.**

1.Choose **File** {{blttria.jpg|BLTTRIA.jpg}} **New** {{blttria.jpg|BLTTRIA.jpg}} **Solution, Project or Application **(CTRL + SHIFT + N).

The //New Project// window is displayed:

{{ugl7b.jpg|UGL7B.jpg}}

2.Select the //Clarion for Window// Category, and then select the //Application from Txa// Quick Start

3.Enter //Master// in the Application QuickStart **Name**, change the location to //C:\Clarion7\Lessons\SyncLesson//, and uncheck the **Auto create project subdir** box as shown above.

4.**Press the ****Create**** button.**

This opens the //Select TXAFile //dialog.

5.Select //Lesson.txa// from the list, then press the **Open** button.

6.Press the **Replace All** button when prompted for //Procedure name clash//.

{{notebox.jpg|NoteBox.jpg}}

An Error Editor dialog warns you that the .TXA specifies a different dictionary than the application. Since we just synchronized the two DCT files, this warning can be safely ignored.

7.Press the **Close** button to ignore the warning.

**You can now make and run the Lesson application using the synchronized Qwktutor.dct, and you can make and run the Qwktutor application with the same Qwktutor.dct!**

**Summary**

When you replace a data dictionary, you must take the following steps to preserve the internal relationships between the application and the data dictionary: first, create a text application file (.TXA), then update the data dictionary, finally create a new application (.APP) from the text application file (.TXA).

By using the Dictionary Synchronizer to update the Qwktutor.dct (as opposed to simply replacing it with a copy of the Tutorial.dct), you are able to see exactly which items changed and you can intelligently manage those changes; if both dictionaries have changed, you can incorporate the changes from both dictionaries by synchronizing each dictionary with the other; finally, the Dictionary Synchronizer can generate a Clarion program to convert your existing data to the new data format.

{{blk2blue.jpg|blk2blue.jpg}}

**Review the Dictionary Synchronizer Log**

The Dictionary Synchronizer creates a log to help with the debugging process and to allow batch synchronizations (see the //Batch Synchronization// help topic for more information).

For every synchronization, the Dictionary Synchronizer creates a log file with the pathname you specify. This log serves two purposes: it can be used to rerun a synchronization in batch mode, and it should be submitted with any bug reports along with the synchronized data dictionaries to aid in the debugging process.

The synchronization from the first part of this lesson created a log file ..\SyncTutr\SYNLOG.TXT. If you browse this file you will see the following:

**CMD(DCT(Clarion Dictionary-C:\Clarion7\Lessons\SyncLesson\Project.DCT))**

**CMD(DCT(Clarion Dictionary-C:\Clarion7\Lessons\SyncLesson\master.dct))**

**CMD(MATCH_Files(MatchByName))**

**CMD(MATCH_Fields(MatchByName))**

**CMD(MATCH_Keys(MatchByComponent))**

**CMD(MATCH_Alias(MatchByName))**

**CMD(SortFiles(FALSE))**

**CMD(SortFields(FALSE))**

**CMD(SortKeys(FALSE))**

**CMD(Hide(FALSE))**

**CMD(Convert(FALSE))**

**CMD(AutoCorrect(FALSE))**

**CMD(GenDBAScript(FALSE))**

**CMD(CWSource(FALSE))**

**CMD(StopAfterMatch(FALSE))**

**CMD(StopBeforeLastCmd(TRUE))**

**FILTER(CLARION)**

**Add,FullCluster,e247e4e1-7e83-4e51-941c-5a42029072ac**

**END_FILTER**

**FILTER(OTHER)**

**AddAll,FullCluster,**

**END_FILTER**

**! SET_ERROR(Field,Customer:CustNumber,DERIVEDFROM,FldDerivedFreeze)**

**! SET_ERROR(Field,Customer:CustNumber,FREEZE,FldDerivedFreeze)**

**CMD(OP_Copy(FALSE,Dictionary,master,Object))**

**! RESET_ERROR(Field,Customer:CustNumber,DERIVEDFROM,FldDerivedFreeze)**

**CMD(EVENT(Undo))**

**! SET_ERROR(Field,Customer:CustNumber,DERIVEDFROM,FldDerivedFreeze)**

**! SET_ERROR(Field,Customer:CustNumber,FREEZE,FldDerivedFreeze)**

**CMD(OP_Merge(FALSE,Dictionary,master,Object))**

**! RESET_ERROR(Field,Customer:CustNumber,DERIVEDFROM,FldDerivedFreeze)**

**CMD(EVENT(OK))**

{{notebox.jpg|NoteBox.jpg}}

If a synchronization fails, the Clarion environment tries to create a log called //Clalog.txt// in the current directory. Please submit this log, the synlog.txt file, and the synchronized data dictionaries and with your bug reports.

{{tipbox.jpg|TipBox.jpg}}

Each Synchronizer Server (Oracle, MSSQL, Pervasive, etc.) calls the corresponding database driver to collect information from the database. Therefore, you can also enable database driver logging to trace the Synchronizer Server calls. For more information see the //Debugging your SQL Application// topic.

**Convert Existing Data to New Format**

The Dictionary Synchronizer optionally generates a Clarion program to convert existing data to the new format.

**Starting Point:**

**Open the \SyncLesson\Master.dct with the Clarion environment.**

**Add a Field to the Master Dictionary**

1.In the DCT Explorer, select the //Customer// file.

2.**In the ****//Fields Quick View//****, select the FirstName field, then press the ****Add **{{addtable.jpg|AddTable.jpg}}** button.**

3.In the **Column Name** field, type //MiddleName//, then press TAB to the next field, and then press the **OK** button.

4.Press the **Cancel** button to stop inserting fields.

5.**Press the **{{savebutton.jpg|savebutton.jpg}}** button in the IDE toolbar to save the Master.dct.**

**Synchronize the Project Dictionary**

{{syncbut.jpg|SyncBut.jpg}}

1.Press the **Synchronize** button in the DCT Explorer to start the Synchronizer Wizard.

The Synchronizer Wizard is opened.

2.Press the **Next >** button to start a new synchronization.

3.Press the ellipsis (...) button to select the //Project.dct//, then press the **Open** button.

4.**CLICK on the ****Next >**** button to continue.**

5.CLICK on the **Next >** button to make //Project.dct// the target dictionary.

{{syncwiz4.jpg|SyncWiz4.jpg}}

**Set Options to Generate Conversion Program**

1.CLICK on the **>>** button to select all Master.dct files.

2.**CLICK on the ****Next**** > button to continue.**

3.CLICK on the **>>** button to select all Project.dct files.

4.CLICK on the **Next** > button to continue.

5.Make sure that the **Generate Data Conversion Program **check box is checked.

6.CLICK on the **Finish** button to open the //Synchronize Dictionaries// dialog.

**Finish the Synchronization and Generate the Program**

1.In the //Synchronize Dictionaries// dialog, right-click the //Dictionary// item (first item in the list), then choose **Add** **to Project** from the popup menu.

This adds all new items (MiddleName field) to the Project.dct.

2.Press the **OK** button to complete the synchronization, update the Project.dct, //and generate the data conversion program//. You may be prompted to save the CONVERT.PRJ project file, and make sure that it is saved in the //..\SyncLesson //folder.

The Synchronizer generates the conversion program components into the working directory (c:\SyncTutr). The conversion program consists of the following components:

| Clacvt_1.clw | file declarations, procedure definitions, field assignments |
| Convert.clw | program map, class declarations, initialization |
| Convert.prj | project file to compile and link the program |

3.Press the **Close** button to close the Master.dct.

**Make and Run the Conversion Program**

1.Open the //Convert.PRJ// file in the //..\SyncLesson //folder (choose **File** {{blttria.jpg|BLTTRIA.jpg}} **Open **{{blttria.jpg|BLTTRIA.jpg}} **Solution, Project or Application**.

2.**Press the **{{buildrun.jpg|BuildRun.jpg}}** button to make and run the conversion program.**

The conversion program lets you select the specific data files to convert to the new format. The conversion program optionally backs up the files you select to the backup folder you specify.

The conversion program handles new files as well as changed files. This gives you the framework to create and prime new files or to split one file into two new related files. You can edit the Clacvt_1.clw source code to prime records and make custom field assignments as needed.

3.Press the **Proceed** button to convert the Customer file, or press the **Cancel** button to abort the conversion.

{{blk2blue.jpg|blk2blue.jpg}}

**Create an SQL Database from a Clarion Dictionary**

The Dictionary Synchronizer can create an entire Data Dictionary, including relationships, from an existing database in a single pass. Conversely, it can create a database from an existing Clarion Data Dictionary. This part of the lesson shows you how to create a database from a Clarion Data Dictionary.

In the SQL case, the Synchronizer generates a SQL script you can run to create the database; in the Btrieve case, the Synchronizer actually creates DDF files that define the database.

{{notebox.jpg|NoteBox.jpg}}

Apply this part of the lesson to your preferred SQL database (MSSQL, Oracle, Pervasive, SQLAnywhere, or AS/400)

**Create an SQL Database Definition**

**Starting Point:**

**The \SyncLesson\Master.dct is open and you have a valid SQL based target in mind.**

1.In the DCT Explorer toolbar, press the **Synchronize** button to start the Synchronizer Wizard.

2.CLICK on the **Next >** button to start a new synchronization.

3.Choose your SQL platform from the **Synchronize With** drop list.

**Set the SQL Database Definition Destination Folder**

1.Press the Select other dictionary ellipsis button (...) to select the relative location or definition of the SQL data definition file.

This opens the //Select Dictionary// dialog (a Windows file dialog).

With SQL databases, specify the connection information in the **Connection**, **User ID**, and **Password** fields, then press the **OK** button.

{{notebox.jpg|NoteBox.jpg}}

The Clarion SQL drivers attach to the proper interface needed for the selected format.

This connection screen will vary from driver to driver.

ADO: **Connection**, **User ID**, and **Password**

Pervasive (Btrieve): Specify a **DDF file** to generate.

Microsoft SQL: **Server**, **Database**, **Login ID**, **Password**, and **Trusted connection** options

Oracle: **HostName**, **User Name**, **Password**

ODBC: **Datasource**, **User ID**, **Password**

Pervasive (SQL) ' Choose from **Database** or **DDF source**, **Owners**, **Username**, **Password**

SQL Anywhere ' **Database**, **User ID**, **Password**

Make sure to consult your SQL documentation for the proper connection settings.

The default database filter excludes system tables from the synchronization process. Generally your applications do not need access to system tables.

2.CLICK on the **Next** **>** button to continue.

** **

**Set the Synchronizer Source and Target**

1.CLICK on the **From ..\Lesson.dct To \SYNCTUTR\****//your database//** radio button to make \SyncTutr the target folder for the conversion script.

If your database can only be opened READ-ONLY, you will not be able to proceed. Consult your system administrator, or check your security rights before continuing.

2.CLICK on the **Next** **>** button to continue.

**Set the Files to Synchronize**

3.CLICK on the **>>** button to include all the Lesson.dct files for synchronization.

4.Highlight the //FieldPool// column, and press the **<;** button to move this back to the //Do Not Synchronize// list.

5.CLICK on the **Next** **> **button to continue.

6.CLICK on the **Next** **> **button to bypass the SQL target tables and continue.

**Set the Synchronizer Options**

7.CLICK on the **Finish** button to accept the default synchronizer options and open the //Synchronize Dictionaries// dialog.

**Add the Dictionary Information to the SQL Database**

8.In the //Synchronize Dictionaries// dialog, RIGHT-CLICK the //Dictionary// item (first item in the list), then choose **Add To (****//SQL target database//****)** from the popup menu.

The Action Indicator changes to a plus sign ( + ) for all items. The plus sign indicates the synchronizer will "add" the marked items to the target database.

9.Press the **OK** button to complete the synchronization and create the SQL database definition.

In the Btrieve case, the Synchronizer writes .ddf files to the path you specified.

In the SQL case, the Synchronizer writes an SQL script (an .sql file instead of a .dct or .ddf file). The Synchronizer prompts you for the pathname of the script file. Type Lesson.sql in the Filename field of the Windows save file dialog.

The script (//..\SyncLesson\Lesson.sql//) looks something like the following:

**SET QUOTED_IDENTIFIER ON**

**CREATE TABLE Customer(**

**   CustNumber INT,**

**   Company CHAR(20),**

**   FirstName CHAR(20),**

**   MiddleName CHAR(20),**

**   LastName CHAR(20),**

**   Address CHAR(20),**

**   City CHAR(20),**

**   State CHAR(2),**

**   CHECK(State IN ('AL', 'MS', 'FL', 'GA', 'LA', 'SC')),**

**   ZipCode INT,**

**   CONSTRAINT KeyCustNumber PRIMARY KEY (CustNumber))**

**CREATE INDEX KeyCompany ON Customer(Company)**

**CREATE INDEX KeyZipCode ON Customer(ZipCode)**

**CREATE TABLE Orders(**

**   CustNumber INT,**

**   OrderNumber SMALLINT,**

**   InvoiceAmount DECIMAL(7,2),**

**   OrderDate INT DEFAULT (getdate()),**

**   OrderNote CHAR(80),**

**   CONSTRAINT KeyOrderNumber PRIMARY KEY (OrderNumber))**

**CREATE INDEX KeyCustNumber ON Orders(CustNumber)**

**CREATE TABLE Detail(**

**   OrderNumber SMALLINT,**

**   ProdNumber SMALLINT,**

**   Quantity SMALLINT,**

**   ProdAmount DECIMAL(5,2),**

**   TaxRate DECIMAL(3,2))**

**CREATE INDEX KeyProdNumber ON Detail(ProdNumber)**

**CREATE INDEX KeyOrderNumber ON Detail(OrderNumber)**

**CREATE TABLE Products(**

**   ProdNumber SMALLINT,**

**   ProdDesc CHAR(25),**

**   ProdAmount DECIMAL(5,2),**

**   TaxRate DECIMAL(3,2),**

**   CONSTRAINT KeyProdNumber PRIMARY KEY (ProdNumber))**

**CREATE INDEX KeyProdDesc ON Products(ProdDesc)**

**CREATE TABLE Phones(**

**   PhoneID SMALLINT,**

**   CustNum INT,**

**   Area INT,**

**   Phone INT,**

**   Description CHAR(20),**

**   CONSTRAINT KeyPhoneID PRIMARY KEY (PhoneID))**

**CREATE INDEX KeyCustNumber ON Phones(CustNum)**

**ALTER TABLE Phones ADD**

**   CONSTRAINT KeyCustNumber1 FOREIGN KEY (CustNum)**

**   REFERENCES Customer(CustNumber)**

**ALTER TABLE Orders ADD**

**   CONSTRAINT KeyCustNumber2 FOREIGN KEY (CustNumber)**

**   REFERENCES Customer(CustNumber)**

**ALTER TABLE Detail ADD**

**   CONSTRAINT KeyOrderNumber1 FOREIGN KEY (OrderNumber)**

**   REFERENCES Orders(OrderNumber)**

**ALTER TABLE Detail ADD**

**   CONSTRAINT KeyProdNumber1 FOREIGN KEY (ProdNumber)**

**   REFERENCES Products(ProdNumber)**

{{blk2blue.jpg|blk2blue.jpg}}

**Create a Clarion Data Dictionary from an SQL Database**

The Dictionary Synchronizer can create an entire Data Dictionary from an existing database in a single pass. Conversely, it can create a database from a Clarion Data Dictionary. This part of the lesson shows you how to create a Clarion Data Dictionary from an existing SQL based database.

{{tipbox.jpg|TipBox.jpg}}

**To synchronize an SQL database, you must have read privileges for the database system tables'except for security or access tables (user ids, passwords, privileges, etc.)**

**Create an Empty Dictionary:**

**Starting Point:**

**The Clarion Environment is open, your SQL database driver is registered, and the database is started (if applicable).**

1.From the IDE Start Page, select the **Dictionaries** category link, and press the **New Dictionary** button.

**This opens the Windows ****Save As**** file dialog.**

2.In the **File name** field, type //SQLDemo//, then press the **Save** button.

This opens the Dictionary dialog to the empty data dictionary.

3.In the DCT Explorer toolbar, press the **Synchronize** button {{syncbut1.jpg|syncbut1.jpg}} to start the Synchronizer Wizard.

4.CLICK on the **Next >** button to start a new synchronization.

5.Choose your SQL database type from the **Synchronize With** drop list.

**Select the SQL Database**

1.Press the ellipsis button to select the SQL database.

2.Specify the appropriate connection information, then press the **OK** button.

If you are using the Oracle Demo database supplied with Personal Oracle. The host is local (2:), the Username is DEMO, and the Password is DEMO.

The host for later versions of Personal Oracle (7.3 and 8) is blank (not 2:).

The default database filter excludes system tables from the synchronization process. Generally your applications do not need access to system tables.

3.CLICK on the **Next >** button to continue.

**Set the Synchronizer Source and Target**

1.CLICK on the **From ..(your SQL target) To ..\SQLDemo.dct** radio button to make //SQLDemo.dct// the target dictionary.

2.CLICK on the **Next >** button to continue.

**Select the Tables to Synchronize**

1.CLICK on the **Next >** button to select files and continue.

2.CLICK on a desired table name, then click on the **>** button to select the table and all related tables (if applicable).

3.CLICK on the **Next >** button to accept the synchronizer options and continue.

4.CLICK on the **Finish** button to continue.

**The synchronizer collects information from the SQL database.**

3.In the //Synchronize Dictionaries// dialog, RIGHT-CLICK the //Dictionary// item (first item in the list), then choose **Merge Into** from the popup menu.

The Action Indicator changes to a plus sign ( **+** ) for all items. The plus sign indicates the synchronizer will add the marked items to the target data dictionary.

4.Press the **OK** button to complete the synchronization and complete the //SQLDemo.dct// dictionary.

The Dictionary dialog shows the completed SQLDemo.dct dictionary, including file relationships.

5.Press the **Save and Close** button to close and save the //SQLDemo.dct// file.

6.Run the Application Wizard against the //SQLDemo.dct// file to create a complete application to access the target SQL database.

**Next:** [[lesson 8 advanced features of the dictionary editor.htm|Lesson 8 - Advanced Features of the Dictionary Editor]]