CodeCharge Studio
The Database Explorer can be used to view the database catalog and select the tables and fields you want to define in the data dictionary. The catalog is displayed as a familiar hierarchical folder tree.
If you right click on any field name, the context menu allows you add/delete the field as a primary key column, or reset any changes you made in the data dictionary for that field. 'Reset' is useful if you want to revert your changes back to the original state of that field in the data dictionary (e.g. to revert back to the original database setting). It does not affect any other fields that you may have configured in the data dictionary.
To open the Data Dictionary Property Editor part of the workspace, double click on a table, or column from the Database Explorer.
If you are running one of the builders that updates the database, it's necessary for CodeCharge Studio to uniquely identify records in the database either with a primary key or a unique column. A primary key is normally one field in the table, but you can have a primary key defined by multiple columns. When the primary key columns can be detected automatically using the underlying database connector and datasource, CodeCharge Studio will use the primary key by default. But, when the primary key cannot be determined, it is necessary to define a primary key, or one or more columns that can be used in lieu of the primary key. Rather than define this each time you run one of the builders, you can use the Database Explorer to define this for each table, however you must be careful to select columns that uniquely identify the record, especially if you choose to override the primary key columns that are already defined in the database.
To change the primary key columns for a table, right-click on the column you want to add, and select click on the action "Is Primary Key". The added column should now have a 'key' icon next to the column name. To remove a column from the primary key set, right-click on an existing key column and click on "Is Primary Key".
When the data in one table relates to data in another table, the tables have a relationship expressed by the data. e.g. each employee in the 'employees' table belongs to a department. Information about the department, such as the department name, is in another table. In order to relate the employee to the department information, each employee record in the 'employees' table contains a 'department_id' which is a unique value that can be used to look up information about the department in the 'departments' table.
Relationships between tables is important to the way in which CodeCharge Studio builds queries. When a relationship exists between tables, CodeCharge Studio uses the SQL JOIN operator to relate the tables. The JOIN operator is used to look up and find information from one table based on information from another table. When you run one of the builders (e.g. Grid builder) and select 'build query', this runs the Query Builder. If a relationship exists between tables, the relationship is shown visually as a set of connected arrows between the tables, and also in the actual SQL query created by the Query Builder.
SELECT employees.*, group_name, department_name
FROM (employees INNER JOIN groups ON employees.group_id = groups.group_id)
INNER JOIN departments ON employees.department_id = departments.department_id
This query displays the employee information, plus the name of the group and department they belong to.
In the Visual Query Builder, if you delete one or more relationships, the JOIN operator is dropped from the SQL query.
SELECT employees.*, group_name, department_name
FROM employees, departments, groups
Without the join, this query cross tabulates all the records in the 'employees' table with all the records in both the 'departments' and 'groups' tables. This query does not have much usefulness since every employee is listed along with all the deparments and groups that exist, not just those that the employee is assigned to.
The relationships between tables is not static. You can use the Visual Query Builder to delete or add relationships. You should define relationships that make sense relative to the data in each field, but you are not constrained to do so. You can relate any fields between any tables but the subsequent query generated by CodeCharge Studio will probably not be correct so be careful when selecting the fields that should be referenced.
Relationships between tables are normally defined as foreign key constraints in the database, and most ODBC and OLEDB providers support interfaces that expose how these relationships are defined. However, when the relationships are not defined, or the connection is using a connector that doesn't have support for foreign keys, CodeCharge Studio creates artificial relationships based on the field names in the tables. Specifically, when two fields of a table have the same name, CodeCharge Studio assumes that a relationship exists between the tables. The Visual Query Builder always relates tables by field names when CodeCharge Studio cannot discover the relationships from the database, but the Data Dictionary will only do this if you request the relationships to be added with the Suggest icon on the ribbon bar.
Relationships can also be added or deleted in the Database Explorer. The Database Explorer and the Visual Query Builder are very similar, except the relationships you define in the Data Dictionary are permanent and reuseable. When you change or define a relationship between tables in the Visual Query Builder, the relationship only exists for that query. e.g. if you define a relationship between the 'employees' table and the 'departments' table on the 'department_id' field, where it didn't exist before, the relationship only exists for that query. When you define the relationship in the data dictionary, the relation can be resused every time you run one of the builders.
To view the relationships between tables using the Database Explorer, select the 'Relationships' button on the ribbon bar under the 'Data Dictionary' tab, then add tables to the document pane either by selecting them from the drop lists at the bottom, or dragging and dropping tables from the folder tree to the document pane. If a relationship already exists between two tables, it will be represented by a connecting line between the field in each table.
If you right click any area within the relationship window, the context menu will appear. Using this menu, you can 'add' a table, or 'add' all tables.
Like the Visual Query Builder, you can delete or redefine any relationship shown. This could be either an actual constraint defined in the database, a previously defined relationship using the Database Explorer, or an artificial relationship created by CodeCharge Studio based on the field names. Also, you can relate any fields between tables, but you should be careful about picking fields since the relationships you define affects how CodeCharge Studio generates SQL queries.
Using the Database Explorer, you can define a query that can be used as the datasource for the forms you create. This can be useful if you need to reuse a query, or use one query as a template for another.
To create a query, right-click on the 'Queries' folder in the folder tree and select 'Create Query'. This will start the Visual Query Builder and from this point, you can define a custom query. See Form Data Source for more information about using the Visual Query Builder.
To rename a query, right-click on the query in the folder tree and select 'Rename'.
To delete a query, right-click on the query in the folder tree and select 'Delete'.
When you create a query, the query becomes part of the list of available datasources when you define a form. For example, if you create a query called 'query1' and then run the Grid builder, the query will appear under the list of datasources to select from. If you select the query as the datasource, the datasource selected, at the top of the drop down box, changes to the list of tables you have defined in the query. For example, suppose 'query1' is a query against the 'employees' table, when you select 'query1' as the datasource, the name of the selected datasource changes to 'employees' since the query is based on this table. The preview of your form is based on the query you defined in the Data Dictionary and similarly, if you select 'build query', the query you see will be the query you defined in the Data Dictionary and selected from the datasource list.
Note, your query is also linked to the connection and database. If you modify your connection, the query may no longer be valid.
Wherever you can select a datasource, you can use a query you created in the Data Dictionary.
Note, deleted and renamed queries are not immediately removed from the list of datasources. To remove them from the list of available datasources, click on the 'Refresh' button on the ribbon bar under the 'Data Dictionary' tab.
Using the ribbon bar, you can also change the query type, in the same way in which you would change the query type in the Visual Query Builder. There are 3 types of queries you can define:
Table queries - These are queries you design using the Query by Example (QBE) interface in the Visual Query Builder. With these types of queries, you do not have to know SQL since CodeCharge Studio generates the specific SQL syntax based on the tables and fields you have selected for the query. If you wish to see the generated SQL statement, select the SQL tab at the bottom of the window. You cannot edit a generated SQL query.
SQL queries - This is a query you enter directly as text into the query builder. You can either change a generated query to a custom query and modify the query, or enter a new query directly into the editor. By default, the query type is 'table'. When you convert a 'table' type query, the query builder will prompt you to 'rebuild' the query. This is the same as requesting the query builder to replace any custom definition of the query with a new generated query based on the table and field definitions. Once you have created a custom query, you cannot change the type back to 'table'.
Stored procedure - This is a query that is invoked thru a stored procedure.
See Form Data Source, for more details about creating queries and the visual query builder.