Table of Contents
Functionality
Description
The function of the database connection 
can either through the menu Mass> database connection ... or with the right mouse button New> database connection ... be called.
Via the menu in Object manager a node is inserted in the mass tree that connects to a specific database. For this purpose, a predefined configuration and then either a file or a subset of it is selected. Under this node, all data records of the file or the subset are then created as nodes and with this symbol 
characterized.
The number of hierarchy levels depends on the configuration and structure of the database, but can theoretically contain any number of hierarchy levels. By assigning drawing objects within the nodes, these can be linked to the data records and their attributes (properties) can be written to the data records in the database. The functionality of the interface is also explained using a detailed example (see Application examples).
Basically, a distinction can be made between databases that accommodate several tables in one file (as in this example: MS Access) and databases that consist of several files that then contain one (FoxPro, Paradox) or several tables.
ODBC driver
Access to the databases from the Object manager takes place via an ODBC driver, which must be set up in the control panel in advance. In most cases this entry has to be created manually. In some cases, however, this is done automatically when the database program is installed. The required driver, its version and configuration can best be requested from the manufacturer of the database program.
Create ODBS configuration
An ODBS configuration essentially consists of a configuration setting and several SQL commands that allow access to a data source from the Object manager control via the ODBC driver.
To create or edit a configuration, the corresponding dialog in DATAflor CAD can be called.
Application
- Within DATAflor CAD becomes the command MASS called up and the point in the mass tree marked at which the database connection is to be inserted later. If only the configuration is created in this step, the position in the mass tree is irrelevant.
- Via the menu Mass> New> Database connection ... or the right mouse button opens the dialog Database connectivity open.
- About [New…] a new database configuration is created in the following dialog. With [To edit…] an existing ODBS configuration can be changed, which must be marked in the selection beforehand.
- The program activates or deactivates the input fields so that no incorrect entries can be made and allows confirmation [OK] only when a meaningful configuration has been created. The following fields must be filled in: Title of the configuration, ODBC driver used, Selection SQL and Group SQL. All other fields are optional.
- Configuration title is the name that is entered or displayed when the database configuration is selected. It also serves as the file name for the configuration and as the title of the dialog box for querying the text parameter.
Depending on the data source used, either a Filename or at least one Selection SQL Command to be entered.
Group SQL must contain at least one valid command. - The ODBC driver used a selection of all available ODBC drivers is displayed. Select the associated ODBC driver that you created previously (see Application examples > Set up new ODBC driver).
- After selecting an ODBC driver, checks DATAflor CAD determines whether user name and password are necessary and, if necessary, opens a dialog for input (see Work instructions > Password protection).
Database file has different meanings: Here the text parameter is entered, which in the SQL commands with! #! can be used, or a fixed file can be specified, which, however, can already have been done by the ODBC driver. Search… currently has no meaning and is grayed out.
Selection title is the text that is displayed as the title of the selection dialog when using the ODBS configuration. This text is also used as the title of the box for querying the text parameter. - After confirming the dialogue with [OK] you will be asked whether the configuration should be tested. Will this with [No] confirmed, the configuration is exited and the configuration file is saved. If the query is made with [Yes] confirmed, the selection dialog or the file selection appears first, followed by statistics on the results of the SQL commands or the error messages generated. If the messages are marked, the explanation appears in the lower area.
Entering the SQL commands
With Append ..., Create…, To edit… and Remove… new or existing SQL commands are inserted, changed and deleted.
All SQL commands are via the same dialog Create SQL command created. There these can be composed of table and column names or in the field SQL command entered manually. Any syntax supported by the ODBC driver can be used for manual entry.
As soon as a database table is selected or entered in the selection above, you can use [Column>] the columns of the table can be accessed automatically. When a selection is made, the column is accepted, the table selection is grayed out and the SQL command is created automatically.
For some syntax constructs, DATAflor CAD is not able to clearly recognize the selected columns or the table and in the dialog Create SQL command to interpret correctly. This reduces the effectiveness of this dialogue. However, this does not play a role in the execution of the database connection.
Five different SQL input areas are available for controlling the database:
| Selection SQL | One or more selection SQL commands create a list of possible subsets of records from a database. When coupling a database to a drawing, a selection can then be made from this list for the connection to DATAflor CAD to be taken. Selection-SQL enters the selected data record as the top entry in a mass tree structure. |
| Group SQL | Again, any number of group SQL commands create the tree structure, which is in the Object manager is shown. |
| Graphics SQL | If the connected database contains graphic information, this can be taken into account in the connection. A hatch name, the color and a factor can be read out. In contrast to selection and group SQL, there is only one graphic SQL command for all levels. |
| Object SQL | The object SQL command defines the creation of graphic objects in the drawing when a database is connected. In contrast to selection and group SQL, there is only one object SQL command for all levels. |
| Set SQL | These are two SQL commands with different meanings. QUANTITY SQ1 reads out the unit of measure or defines the object property that is to be entered in the database. QUANTITY SQ2 is necessary for the entry of the quantity in the database. |
In most cases, only selection, group and quantity SQL is required (e.g. for all connections to calculation programs, since no graphic control is provided here). Graphics and object SQL commands require a very precise specification in the database, which is not always implemented by the third-party manufacturer.
Overview of important SQL commands
All common SQL commands can be freely entered within the input fields in the configuration dialog. Knowledge of the SQL command input is a prerequisite for this. Some important statements are briefly listed here:
| SQL keyword | ClassWizard and database classes use it ... |
|---|---|
| SELECT | to identify which tables and columns to use in the data source. |
| WHERE | to apply a filter that limits the selection. |
| ORDER BY | to apply a sort order to the record. |
| INSERT | to add new data to a data record. |
| DELETE | to get data from a dataset delete. |
| UPDATED | to change the fields of a data record. |
More detailed information and the command syntax can be found in the recommended specialist literature (see Database connectivity).