| Visual SQL-Builder V3.0
Screenshot |
Choosing the DB-type | If there is any need for DB-specific information for generating the statement, i.e. how to quote words, then you should matter about this choice.
Choosing query-types | There are different query types that can be generated. Choose the one you need. The different statement-types: Select: A normal select-statement will be generated. Select (grouped by): You may also use some aggregating functions Update: An update-statement will be generated Insert by values: You may specify some fix values for an insert-statement. The statement will look something like "INSERT INTO ... VALUES ..." Insert by select: You may specify a select-statement with the visual-builder. The generated statement will be something like "INSERT INTO ... SELECT ... FROM ... WHERE ..." Delete: A delete-statement will be generated Count results: You'll get a select-count-statement with your specified criteria.
Choosing tables | When building your query visually all tables from the specified database are shown in a dropdownlist. You can choose tables by pressing the button beside. The table and its attributes will be displayed in the visual builder. |
Looking at the SQL-statement | To get a preview of the statement which will be built just press the left mouse button in the building-area, not standing on any other table or relation. When completely finished with query-building press . The generated query will be shown in the statementarea of the main window of sqlFree as well as in the tabbed pane of the sqlBuilder.
Clearing the view | To clear and initialize the visual builder press the clear-button . All tables and relations will be removed from the building area as well as all chosen attributes from the lower table.
Specifying joins between attributes | To specify equiJoins (=values of attributes of two tables have to match) just drag and drop from the attribute of one table to the attribute of another table. Your result should look something like this: To modify the intention of a relation you have to get the contextmenu of the relation by rightclicking in one of the two little boxes shown on the relationline or on the relationline itself. A menu will appear from which you can make the supported choices. Example: In case you only want the results from the left table and just the matching from the right (rest is filled up with SQL-null) choose the appropriate menuentry. As shown in the illustration below a little plus sign is shown at the table which will be filled up with SQL-nulls. So your relation-line should appear as follows:
Removing elements from the view | Tables: If you want to get rid of a table simply click on the cross in the titlebar of the table. Relations: As shown in the context-menu of the relation just choose "Remove relation from view" Rows from the criteria-table: Right-click on the row you want to eliminate and remove it.
Setting criteria on your attributes | In most cases you'll want to have a look at specific attributes and their values in your database. To select the attributes you are interested in doubleclick them. For every doubleclicked attribute you'll get an additional row in the lower part of the building screen. Table: Tablename the attribute belongs to. This value can not be modified. Attribute: Attributename. You can modify this entry by the statement 'as ...'. Example: 'PROJ_NAME as projectname'. Criteriafields: Feel free to enter values or boolean expressions in a field. Examples: 1 or 2 3 or >6 like 'ab%' like 'abc%' and not 'abcd' not (3 or 4) Placeholdervalues: You can also use 'placeholders'. When sending your statement to the database with sqlFree (by pressing the 'Send SQL-Statement'-Button) you will be asked for an actual placeholdervalue. This value will be placed instead. Example: ##enter projectname## not ##not name a## and not ##not name b## Order: You can specify an order on your result-set Show: By default every chosen attribute will be shown in your resultset. Sometimes you simply want to use the attribute to enter a criteria but not see it in your result-set. Depending on your query-type there may be additional fields: Functions: When making aggregations (grouping) you have the choice between the functions listed in the dropdown-list. New value: When updating/inserting values you can specify here what the new value should be. Insert into: If choosing "Select by insert"-statementtype each value which is marked as to be shown in the table, has to be mapped to the attribute in the inserted table.
How are the criteriafields interpreted? | Think about the following input: The logic how the built SQL-statement would look like is as follows: (1 and 2 and 3) or (4 and 5 and 6) or (7 and 8 and 9)
What are the tabbed panes for? | Statement: Generated statements will be shown in there. Criteria: This issue was already described above. Description: Available information about attributes and tables are shown in this pane.

Up to date versions at http://www.independencesoft.com