Gebruikershulpmiddelen

Site-hulpmiddelen


Verschillen

Dit geeft de verschillen weer tussen de geselecteerde revisie en de huidige revisie van de pagina.

Link naar deze vergelijking

nl:produkte:cad_2017:eingabe_der_sql-befehle [2016/03/23 14:08] (huidige)
Regel 1: Regel 1:
 +====== Entry of SQL-commands ======
 +Enter the SQL-commands with **[Attach ...]** or **[Create ...]** ​
 +
 +===== Selection SQL =====
 +In a configuration any number of SQL-commands (in the following called AUSWAHLSQL1,​ AUSWAHLSQL2 etc. where AUSWAHL stands for Selection) can control the selection of subsets of records from a file. In the example all projects and their related specification are to be displayed for the selection of the specification. For selection the following steps are to be executed:
 +
 +|**Step 1**|AUSWAHLSQL1:​|SELECT ProjectID, Caption, Client FROM PROJECTS|
 +
 +This command generates the list of all projects and adds them in a node at the top level in a tree structure. The first selected column serves a clear allocation of the node to the recordset. Here, always the primary key of the table has to be selected.
 +
 +<WRAP round tip 90%>
 +It is very important that a column is selected which marks the record set unambiguously.
 +</​WRAP>​
 +
 +The caption of the nodes can be specified for each level in a formatting text. For the example the following formatting text for the first level is used: **[Caption],​ [Client]**
 +
 +For each node found in Step 1 the AUSWAHLSQL2 is executed. The AUSWAHLSQL2 is:
 +
 +|**Step 2**|AUSWAHLSQL2:​|SELECT LSpecID, Name FROM Specs WHERE ProjectID=[PROJECTS:​ProjectID]| ​
 + 
 +Each time a term appears in **[ ]** in the SQL commands, this term is loaded from the node of the superordinate levels. In this case the content of the field **ProjectID** from the table PROJECTS that was loaded in the AUSWAHLSQL1.
 +
 +The found recordsets are sorted in the respective node from Step 1 and sub-node. Then again, the first selected column serves the clear allocation Node -> Recordset. For the second level only the caption in the formatting text is used: **[Caption]**
 +
 +This leads to the following structure in the example:\\ {{:​produkte:​cad:​planung:​odbs0009.gif?​nolink|}}\\ \\ In a flat structure AUSWAHLSQL2 can be omitted. If the last existing level does not produce a result then the respective results from the superordinate levels are not displayed, either. Hence, the project with the ID 15344 "Water Park" is not displayed in the example.
 +
 +<WRAP round tip 90%>
 +The depth of the nesting is unlimited.
 +</​WRAP>​
 +
 +If the database structure to be connected consists of multiple files (eg. a file for a specification) this can be solved with the ODBS as well. In this case the entry has to occur in the field **Selection Title** in the main dialogue and there must not be any AUSWAHLSQL commands defined. If the configuration is used a dialogue opens for the file selection where the respective database file can be selected.\\ {{:​produkte:​cad:​planung:​odbs0010.gif?​nolink|}}\\ ​
 +
 +The setup of the Windows ODBC driver is conform with the already described procedure. The selection of the file has no impact, but is only used for the specification of the directory.
 +
 +----
 +
 +===== Group SQL =====
 +Any number of GroupSQL-commands generate the tree structure for the **Object-Manager**. Because there are maximum three item-levels in our example also three entries have to be generated for the GROUPSQL:
 +
 +|**Step 1**|GRUPPENSQL1:​|SELECT ItemID, Item Number, Short Text, GraphicID FROM ITEMS WHERE SpecID=[Specs:​SpecID] AND ParentID IS zero|
 +
 +The results from GROUPSQL1 are now added as node to the first level to the object tree structure. The first selected column serves a clear allocation of the node to the recordset. Here, always the primary key of the table has to be selected.
 +
 +<WRAP round tip 90%>
 +It is very important that a column is selected which marks the record set unambiguously.
 +</​WRAP>​
 +
 +The caption of the nodes can be specified for each level in a formatting text. For the example the following formatting text for the first level is used: **[ItemNumber] : [ShortText]**
 +
 +After that, for each of these nodes GRUPPENSQL2 is executed and looks as follows:
 +
 +|**Step 2**|GRUPPENSQL2:​|SELECT ItemID, Item Number, Short Text, GraphicID FROM ITEMS WHERE LVID=[LVS:​LVID] AND ParentID=[POSITIONEN:​PositionID]|
 +
 +and at last:
 +
 +|**Step 3**|GRUPPENSQL3:​|SELECT PositionID, Positionsnummer,​ Kurztext, GrafikID FROM POSITIONEN WHERE SpecID=[Specs:​SpecID] AND ParentID=[ITEMS:​ItemID]|
 +
 +GRUPPENSQL3 is conform with GROUPSQL2 and thus prodcues different results, because the conditions "​ParentID=ITEMS:​ItemID]"​ refer to the results of GROUPSQL2 (therefore only the items selected under the second level).
 +
 +Of the three SQL-commands a tree structure to the AUSWAHLSQL commands is generated which is then inserted into the Object-Manager as object tree. In the example the object tree within the **Object-Manager** looks like this:\\ {{:​produkte:​cad:​planung:​odbs0008.gif?​nolink|}}
 +
 +The top node is the selected specification (in this case the specification with the ID 17243: "Round cut box trees"​). In this the recordset generated from the GROUPSQL commands is created. In contrast to the AUSWAHLSQL commands the results of the GRUPPENSQL commands are displayed on higher levels even if there are no results in lower levels. In the example there are the items 1.2, 2., and 2.1.
 +
 +The hierarchy has to be specified in the database, otherwise only one group level can be displayed. In the example this would be the case if the column **ParentID** is missing and there would be only one GROUPSQL command. In this case all items in a level are displayed.\\ {{:​produkte:​cad:​planung:​odbs0007.gif?​nolink|}}
 +
 +----
 +===== Graphic SQL =====
 +Provided, the linked database contains graphic infomation these can be taken into account for the connection. Hatch name, colour, and factor can be read. In this version, only a hatch is possible.
 +
 +<WRAP round tip 90%>
 +There is only one GRAPHICSQL command for all levels.
 +</​WRAP>​
 +
 +For the analysis of the graphic information for each found recordset from all GROUPSQL commands the GRAPHICSQL command is executed. The result can consist of the following columns:
 +
 +|**Type**|Only the last character of the field is analyzed. For 0, "​H",​ or "​S"​ a hatch is created. For 1 or "​B"​ a block is inserted. For 2, "​I",​ "​P",​ or "​G"​ a graphic is inserted. For 3 or "​L"​ a line type is displayed. Upper and lower case letters are not distinguished. Default is 0.|
 +|**Name**|The name of the hatch, block, graphic file, or line type. For hatches the default is "​SOLID",​ for line types "​CONTINUOUS"​. For blocks or graphics the field must be available to specify the name of a file (for graphics with extension!). A hatch must be contained in the hatch file, or must be found in the searchpath. The line type must have been included in the drawing or in the file **//​.LIN//​**. Blocks and graphic files need to be found in the Searchpath. Upper and lower case letters are not distinguished.|
 +|**Colour**|The Colour index applies to the colour of the graphic. Default is 7 for white. The names of the primary colours or the letters in brackets "​red"​ (r), "​green"​ (g), "​yellow"​ (y), "​cyan"​ (c), "​blue"​ (b), "​magenta"​ (m), and "​white"​ (w); "​white"​ or "​black"​ are also recognized. Upper and lower case letters are not distinguished. The colour "​white"/"​black"​ or 7 is always displayed as complementary colour of the background in DATAflor CAD.|
 +|**Factor**|A real or interger number as factor for the graphic. Default is 1.0 where blocks are scaled uniformly in X, Y, and Z. For line types the value is read as width.|
 +|**Layer**|The name of the layer on which the graphic is created. It is omitted for line types.|
 +
 +Since the columns are named differently in the example table, the SQL commands have to be renamed:
 +
 +|**Step 1**|GRAFIKSQL:​|SELECT Caption AS Name, Colourindex AS Colour, Factor FROM GRAPHIC WHERE GraphicID=[ITEMS:​GraphicID]|
 +
 +The GRAPHICSQL command is to deliver exactly one recordset as result. All further recordsets are ignored.
 +
 +----
 +
 +===== Object SQL =====
 +The OBJECTSQL command defines the creation of graphic objects in the drawing when connecting to a database. It is executed for each node created by the GROUPSQL commands. In this example the OBJECTSQL command has no meaning, because no graphic objects are created from the database. The following description only clarifies the command syntax. ​
 +
 +<WRAP round tip 90%>
 +There is only one OBJEKTSQL command for all levels.
 +</​WRAP>​
 +
 +The result can consist of the following columns:
 +|**Type**|Only the first character of the field is analyzed. 0 or "​B"​ add a block into the drawing. 1, "​C",​ or "​P"​ create a polygon from all recordsets found. For "​C"​ always a closed polygon is created. Default is 0 for block.|
 +|**ID**|The ID with which the object is linked to this recordset. This is later important for the synchronisation. For Type 1 ("​Poly"​) the ID of the dataset first found is used. For Type 0 each object receives the ID of the respective recordset|
 +|**Name**|The name of the block is "​$$DBOBJ.DWG"​. This parameter is omitted for polygons.|
 +|**Colour**|The Colour index applies to the colour of the block or polygon. Default is 7 for white. The names of the primary colours or the letters in brackets "​red"​ (r), "​green"​ (g), "​yellow"​ (y), "​cyan"​ (c), "​blue"​ (b), "​magenta"​ (m), and "​white"​ (w); "​white"​ or "​black"​ are also recognized. Upper and lower case letters are not distinguished. The colour "​white"/"​black"​ or 7 is always displayed as complementary colour of the background in DATAflor CAD.|
 +|**Factor**|A real or interger number as factor for the graphic. For blocks this is the factor for the scale. Default is 1.0 and for polygons this denotes the line weight. Default is 0.0.|
 +|**Layer**|The name of the layer on which the block or the polygon is inserted.|
 +|**X,​Y,​Z**|The coordinates for the import of the block or for the pick point of the polygon. Default is 0.0 for each.|
 +
 +In the case that the columns in the database are named differently they have to be renamed in the SQL command:
 +
 +|**Step 1**|OBJEKTSQL:​|SELECT ID, Art AS Typ, Name, Colourindex AS Colour, Size AS Factor, Right AS X, High AS Y, Height AS Z FROM COORDINATES WHERE ItemID=[ITEMS:​ItemID]|
 +
 +Delivers the OBJECTQL command for Type "​Block"​ (0) several results, then multiple blocks are inserted.
 +
 +Delivers the command for Type "​Poly"​ (1) several resuts, then the order of the recordsets has to be specified by a respective "ORDER BY" or the recordsets have to be delivered in a certain order by the database driver.
 +
 +A closed polygon is created when the first coordinate is repeated at the end or the Type "​C"​ is specified.
 +
 +To keep the requirements low for the database structure this SQL command can be cetended with overwrites for the defaults. This is done by attaching the overwrites in curly brackets at the end of the SQL command. If the type is not delivered, but 1 is to stand for "​Poly"​ then  the attachment is: **{Type=1}**
 +
 +If the name is not delivered, but the block **//​3DTREE.DWG//​** supposed to be inserted, then the attachment would be: **{Name=3DTREE.DWG}**
 +
 +The name of the created object can be specified in a formatting text like in the AUSWAHLSQL and GRUPPENSQL commands. If the formatting text left blank, then the numbering in done in the number circle of the Object-Manager.
 +
 +The default **//​DBOBJ.DWG//​** in the name field is a block delivered with DATAflor CAD. It contains a circle with the diameter 1 and a cross.
 +
 +----
 +
 +===== Mass SQL =====
 +These are two SQL commands with different impacts. MASSSQL1 reads the mass units or defines the object properties which are added to the database. MASSSQL2 writes the mass into the database.
 +
 +For MASSSQL1 there are three different options available:​\\ {{:​produkte:​cad:​planung:​odbs0006.gif?​nolink|}}\\ ​
 +|**SQL**|At Selection-SQL a SQL command can be defined to read the mass units in the database. This is explained in the application-example.|
 +|**Formula {}**|With the formula for the selection an object property or a calculation operation is defined which is written into the database table independantly from mass units setup in the database.|
 +|**Attribute {}**|With the attribute for the selection the allocated attribute can be created in the object tree which is written into the database table.|
 +
 +After the selection of an option it is entered into the dialogue and must not be deleted. The MASSSQL1 command with the option SQL should deliver exactly one recordset as result. All other recordsets found are ignored.
 +
 +|**Step 1**|MENGENSQL1:​|SQL:​ SELECT Mass Unit FROM ITEMS WHERE ItemID=[ITEMS:​ItemID]|
 + 
 +The mass unit is important, because DATAflor CAD needs to know, which object attribute is to be written into the recordset. For m² this is would be an area, for m a distance, etc. For unknown mass units the program will try to achieve a known mass unit with the conversions from the file **//​UNITS.TBL//​**. Should the table not contain any mass units the MASSSQL1 stays empty and the mass with the most dimensions is entered (for volume objects m³, for area objects m², etc.).
 +
 +<WRAP round tip 90%>
 +Objects with an allocated attribute called **DBMass** are excepted (see [[GAEB Schnittstelle]] > Work method).\\ Because of this, conversions can be made and the result can then be entered into the database, for instance. For these objects MASSSQL1 will not be evaluated.
 +</​WRAP>​
 +
 +MASSSQL2 is required for the entry of the mass into the table which was defined with MASSSQL1.
 +
 +|**Step 2**|MENGENSQL2:​|UPDATE ITEMS SET Mass=[OBJECT:​ATTRIBUTE] WHERE ItemID=[ITEMS:​ItemID]|
 +
 +MENGENSQL2 is always opened when a change is made within the tree of the database connection, eg. when an object is added or deleted, or when a drawing object is amended. MASSSQL2 does not necessarily have to select the recordset which is displays in the tree and corresponds with the node for the entry of the mass.\\ ​
 +Should MASSSQL2 deliver multiple recordsets as result the mass entry will be made in all recordsets.
 +
 +Both MASSSQL commands must only select one database column.
 +
 +At the completion of the ODBS-configuration the configuration file is saved with **[OK]** and the configuration appears subsequently in the selection of the database configuration for the database connection.