User Tools

Site Tools


Differences

This shows you the differences between two versions of the page.

Link to this comparison view

en:produkte:cad_2018:funktionsweise [2018/02/27 13:31] (current)
Line 1: Line 1:
 +====== Method ======
 +===== Description =====
 +Start the function {{:​produkte:​cad:​planung:​icon0025.gif?​nolink|}} with Menu **Masses > New > Database connection** or with the mouse by right-click.
  
 +Via the menu in the **Object-Manager** a node is inserted in the object tree that connects to a certain database. For this, first a specific configuration is selected and then, either a file or a part of it. Under this node all data of the file or partial file is created as a node with this symbol {{:​produkte:​cad:​planung:​icon0026.gif?​nolink|}}.
 +
 +The number of hierarchy levels depends on the configuration and structure of the database, but can theoretically contain any number of hierarchy levels. By allocating drawing objects within a node they can be linked to the data and their attributes (properties) can be written into the data of the database. The method of the interface is also explained in an example (see [[Anwendungsbeispiele]]).
 +
 +Basically, you can distinguish between databases that contain several tables in a file (like the example: MS Access) and databases that contain multiple files which then contain one (FoxPro, Paradox) or more tables.
 +
 +----
 +
 +===== ODBC-driver =====
 +The access to the database from the **Object-Manager** is via an ODBC-Driver which needs to be setup in the Windows Control Panel beforehand. In most cases this entry has to be manually. In some cases, however, this is automatically carried out in the installation of the database program. The required driver, its version and configuration,​ can be best inquired from the manufacturer of the database program.
 +
 +----
 +
 +===== Create ODBS-configuration =====
 +A ODBS-configuration consists basically of a configuration setting and multiple SQL-commands that control the access to the data source from the **Object-Manager** via the ODBC driver.
 +
 +To create or edit a configuration,​ the according dialogue in DATAflor CAD has to be opened
 +
 +=== Procedure ===
 +  - Within DATAflor CAD the command **MASSEN** is entered and the location in the object tree marked where the database is to be inserted later. If only the configuration is setup in this work step the location in the object tree does not matter.
 +  - With Menu **Masses > New > Database connection** or respectively with clicking the right mouse button the dialogue **Database Connection** opens.\\ {{:​produkte:​cad:​planung:​odbs0018.gif?​nolink|}}\\ \\ 
 +  - With **[New...]** a new database configuration is created in the following dialogue. With **[Edit...]** an existing ODBS-configuration can be amended, but has to be selected beforehand.\\ {{:​produkte:​cad:​planung:​odbs0017.gif?​nolink|}}\\ \\ 
 +  - The program activates or deactivated the entry fields so that no wrong entries can be made and also only allows a confirmation with **[OK]** when a useful configuration is setup. For this, the following fields have to be filled in: Title of Configuration,​ Used ODBC-Driver,​ Selection-SQL and Group-SQL. All other fields are optional.\\ {{:​produkte:​cad:​planung:​odbs0016.gif?​nolink|}}\\ \\ 
 +  - **Title of Configuration** is the name that is entered or displayed for the selection of the database configuration. It also serves as file name for the configuration and it is used as title of the dialogue box for inquiries about text parameters.\\ Depending on the used data source either a **file name** or at least a **Selection-SQL** command has to be entered.\\ **Group-SQL** must at least have one valid command. ​
 +  - In **Used ODBC-Driver** a selection of all available ODBC-Drivers is listed. Select the ODBC-Driver that you have previously created (see [[Anwendungsbeispiele]] > Configure new ODBC-driver).\\ {{:​produkte:​cad:​planung:​odbs0015.gif?​nolink|}}\\ \\ 
 +  - After the selection of a ODBC-Driver DATAflor CAD checks whether user name and password are required and opens, if necessary, a dialogue for input (see [[Arbeitshinweise]] > Password protection).\\ **Database file** has different meanings: Here, the text parameter that can be used in SQL-commands with !#! is entered, or a file is specified which also could have occured by the ODBC-driver. **Search...** has at this point no meaning and is inactive.\\ **Selection title** is the text displayed as title of the selection dialogue when appyling the ODBS-configuration.
 +  - After confirming the dialogue with **[OK]** you are asked whether you want to have the configuration tested. If this is answered with **[No]** the configuration is closed and the configuration file saved. If the inquiry is confirmed with **[Yes]** a selection dialogue, or the file selection and then a statistic with the results of the SQL-commands or created error messages, will appear. When marking the messages there is an explanation at the bottom.\\ {{:​produkte:​cad:​planung:​odbs0014.gif?​nolink|}}
 +
 +----
 +
 +===== Entry of SQL-commands =====
 +With** Attach ...**, **Create ...**, **Edit ...** and **Delete ...** new or existing SQL-commands are inserted, modified, or deleted.
 +
 +All SQL-commands are created with the same dialogue **Create SQL-command**. There, they can be assembled from table- and column-names,​ or entered manually into the field **SQL-command**. At the manueal entry any syntax supported by the ODBC-driver can be used.
 +
 +{{:​produkte:​cad:​planung:​odbs0013.gif?​nolink|}}
 +
 +Once a database table is selected or entered into the upper selection field the table can be accessed with 
 +**[Column > ]** automatically. At a selection the column is applied, the table selection grayed, and the SQL-command created automatically.
 +
 +<WRAP round tip 90%>
 +At some syntax constructs is DATAflor CAD not able to clearly recognize the selected columns or tables and to interpret them correctly in the dialogue **Create SQL-command**. This reduces the efficiency of the dialogue. However, for the application of the database connection this does not matter.
 +</​WRAP>​
 +
 +There are five different SQL-entry fields available to access the database:
 +|**Selection-SQL**|One or more selection-SQL commands create a list of possible subsets of records from a database. When linking a database to a drawing a selection for the link to DATAflor CAD can be made. Selection-SQL transfers the selected data in a object tree structure as top entry.|
 +|**Group-SQL**|Then again, any number of Group-SQL commands generate the tree structure displayed in the **Object-Manager**.|
 +|**Graphic-SQL**|Provided that the linked database contains graphic information this can be taken into account of the connection. A hatch name, colour, and factor can be read. 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 generation of graphic objects in the drawing of the connection. In contrast to Selection- and Group-SQL there is only one Object-SQL command for all levels.|
 +|**Mass-SQL**|These are two SQL-commands with different impacts. MENGENSQL1 reads the mass unit, or defines the object properties which is to be entered into the database. MENGENSQL2 is required for the entry of mass into the database.|
 +
 +<WRAP round tip 90%>
 +In most cases only Selection-, Group-, and Mass-SQL are required (eg. for all connections of spreadsheet programs, since there is no graphic control provided).\\ Graphic- and Object-SQL commands require a very precise target in the database which is not always realized by a foreign manufacturer.
 +</​WRAP>​
 +
 +----
 +
 +===== Overview of important SQL-commands =====
 +Within the entry fields in the configuration dialogue all common SQL-commands can be entered manually. Precondition for this is the knowledge of the SQL-command entry. Some important statements are briefly listed below:
 +
 +^SQL keyword^ClassWizard and database classes use it ...^
 +|SELECT|To identify which tables and columns in the data source are to be used.|
 +|WHERE|To apply a filter that narrows the selection.|
 +|ORDER BY|To apply a sort order to the recordset.|
 +|INSERT|To add new records to a recordset.|
 +|DELETE|To delete records from a recordset.|
 +|UPDATE|To modify the fields of a record.|
 +
 +Specific information as well as the command syntax can be found in the specialist literature recommended (see [[Datenbankanbindung]]). ​