This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
integration:rdbms:odbc:tables [2011/04/06 19:17] doc |
integration:rdbms:odbc:tables [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 23: | Line 23: | ||
| === The CREATE TABLE Statement === | === The CREATE TABLE Statement === | ||
| - | The [[dev:sql:statements:create_table:home|CREATE TABLE]] statement is used to declare a table or view within an Omnidex Environment File to be accessed using ODBC. The Omnidex Environment File will contain a declaration for each table or view to be accessed, and will correlate all of the schema information between Omnidex and ODBC, including object names and datatypes. This statement can either be issued directly, or it can be extracted from the database through ODBC using the EXTRACT statement as discussed in the previous section. | + | The [[dev:sql:statements:create_table:home|CREATE TABLE]] statement is used to declare a table or view within an Omnidex Environment File to be accessed through ODBC. The Omnidex Environment File will contain a declaration for each table or view to be accessed, and will correlate all of the schema information between Omnidex and ODBC, including object names and datatypes. This statement can either be issued directly, or it can be extracted from the database through ODBC using the EXTRACT statement as discussed in the previous section. |
| The following example compares the ODBC declarations with the Omnidex declarations. | The following example compares the ODBC declarations with the Omnidex declarations. | ||
| Line 55: | Line 55: | ||
| </code> | </code> | ||
| - | In the Omnidex declaration, note that the PHYSICAL clause for table points to the actual table name within the database. Also note that the columns use the PHYSICAL clause to point to the underlying column name, and use Omnidex datatypes rather than ODBC datatypes. | + | === Using the Table and Column PHYSICAL Clauses === |
| + | |||
| + | In the Omnidex declaration, note that the optional PHYSICAL clause for the table allows Omnidex to have a different table name than the underlying database. This is most commonly used when administrators wish a different name in the Omnidex environment or need to shorten a table name to meet the 32-character limit in Omnidex. If the PHYSICAL clause is not present, Omnidex assumes that the table names are the same between Omnidex and the underlying database. | ||
| + | |||
| + | Column declarations can similarly use a PHYSICAL clause to allow Omnidex to have a different column name than the underlying database. | ||
| === Modifying Omnidex's View of the ODBC Data Objects === | === Modifying Omnidex's View of the ODBC Data Objects === | ||
| - | When Omnidex accesses a table, it only knows about the data objects that are declared in the Omnidex Environment File. It does not have an independent understanding of the ODBC environment. This allows administrators to shape the Omnidex Environment the way they want. Some of the opportunities this provides are: | + | When Omnidex accesses a table, it only knows about the data objects that are declared in the Omnidex Environment File. It does not have an independent understanding of the underlying database. This allows administrators to shape the Omnidex Environment the way they want. Some of the opportunities this provides are: |
| - | * Omnidex can have a controlled view of the underlying database, limited to only the tables and columns that the application requires. | + | * Omnidex can have a controlled view of the underlying database, limited to only the tables that the application requires. Similarly, administrators can choose to include only the columns for each table that the application requires. |
| * Omnidex tables can point to views in the underlying database, allowing an easy approach to reshaping the application's view of the data. | * Omnidex tables can point to views in the underlying database, allowing an easy approach to reshaping the application's view of the data. | ||
| * Omnidex can use different names for tables and columns by using the PHYSICAL clause in the TABLE or COLUMN sections of the CREATE TABLE statement to map to the underlying object names. | * Omnidex can use different names for tables and columns by using the PHYSICAL clause in the TABLE or COLUMN sections of the CREATE TABLE statement to map to the underlying object names. | ||
| Line 70: | Line 75: | ||
| Omnidex table declarations can reference views in the underlying database. Omnidex will retrieve from the view just as though it is a table. Views can be used to provide different views of the data, appropriate for the application. | Omnidex table declarations can reference views in the underlying database. Omnidex will retrieve from the view just as though it is a table. Views can be used to provide different views of the data, appropriate for the application. | ||
| - | When referencing a view, it is necessary to declare a UNIQUE constraint. This requirement for the UNIQUE constraint is independent of any PRIMARY or FOREIGN constraints, discussed in the next page. The UNIQUE constraint tells Omnidex how to uniquely identify a row. When Omnidex retrieves individual rows, it will use this unique value; therefore, it is important that access to this column (or columns) be properly indexed in the underlying table. | + | When referencing a view, it is necessary to declare a UNIQUE constraint. This requirement for the UNIQUE constraint is independent of any PRIMARY or FOREIGN constraints, discussed in the next page. The UNIQUE constraint tells Omnidex how to uniquely identify a row. When Omnidex retrieves individual rows, it will use this unique value. Therefore, it is important that access to this column (or columns) be properly indexed in the underlying table. |
| **ODBC CREATE VIEW Statement** | **ODBC CREATE VIEW Statement** | ||