This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:features:attach:tables [2012/02/29 21:47] doc |
admin:features:attach:tables [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 21: | Line 21: | ||
| Relational tables can be attached to a connection as long as it matches the database type declared in the Omnidex environment. For example, if an Omnidex Environment references a Microsoft SQL Server database, then additional SQL Server tables can be attached; however, Oracle tables cannot since they are not the same database type. If multiple database types are required, then each database can be declared in a separate Omnidex environment and [[admin:features:attach:databases|attached]] as needed. Alternatively, a single Omnidex environment can permanently declare multiple database, each of a different type. | Relational tables can be attached to a connection as long as it matches the database type declared in the Omnidex environment. For example, if an Omnidex Environment references a Microsoft SQL Server database, then additional SQL Server tables can be attached; however, Oracle tables cannot since they are not the same database type. If multiple database types are required, then each database can be declared in a separate Omnidex environment and [[admin:features:attach:databases|attached]] as needed. Alternatively, a single Omnidex environment can permanently declare multiple database, each of a different type. | ||
| - | |||
| - | Attaching a table is simple, and uses syntax that is nearly identical to CREATE TABLE, as shown below: | ||
| - | <code> | ||
| - | attach table "GEO"."AREACODES" | ||
| - | physical "dat/arc.dat" | ||
| - | ( | ||
| - | "AREACODE" character(3), | ||
| - | "TYPE" string(7), | ||
| - | "TYPE_DESC" string(31), | ||
| - | "ASSIGNABLE" character(1), | ||
| - | "EXPLANATION" string(31), | ||
| - | "RESERVED" character(1), | ||
| - | "ASSIGNED" character(1), | ||
| - | "ASSIGN_DATE" ascii date, | ||
| - | "GEOGRAPHIC" character(1), | ||
| - | "SERVICE" string(39), | ||
| - | "STATES" character(10), | ||
| - | "LOCATION" string(31), | ||
| - | "COUNTRY" character(2), | ||
| - | "AREA_SERVED" string(511), | ||
| - | "IN_SERVICE" character(1), | ||
| - | "IN_SERVICE_DATE" ascii date, | ||
| - | "OVERLAY" character(1), | ||
| - | "OVERLAY_COMPLEX" character(20), | ||
| - | "PARENT" character(32), | ||
| - | "TIME_ZONE1" character(1), | ||
| - | "TIME_ZONE2" character(1), | ||
| - | constraint AREACODES_AREACODE_PK primary ("AREACODE") | ||
| - | ); | ||
| - | </code> | ||
| The following restrictions apply to attached tables: | The following restrictions apply to attached tables: | ||
| Line 58: | Line 28: | ||
| * Attached tables cannot be partitioned as part of an Omnidex Grid. | * Attached tables cannot be partitioned as part of an Omnidex Grid. | ||
| * Attached tables can only be attached into the current connection. | * Attached tables can only be attached into the current connection. | ||
| + | |||
| + | |||
| + | Attaching a table is simple, and uses the [[dev:sql:statements:attach_table:home|ATTACH TABLE]] statement. The ATTACH TABLE syntax is nearly identical to CREATE TABLE, as shown below: | ||
| + | |||
| + | <code> | ||
| + | > connect to simple | ||
| + | Connected to D:\class\labs\attach\simple.xml | ||
| + | |||
| + | > attach table "AREACODES" | ||
| + | >> physical "dat/arc.dat" | ||
| + | >> ( | ||
| + | >> "AREACODE" character(3), | ||
| + | >> "TYPE" string(7), | ||
| + | >> "TYPE_DESC" string(31), | ||
| + | >> "ASSIGNABLE" character(1), | ||
| + | >> "EXPLANATION" string(31), | ||
| + | >> "RESERVED" character(1), | ||
| + | >> "ASSIGNED" character(1), | ||
| + | >> "ASSIGN_DATE" ascii date, | ||
| + | >> "GEOGRAPHIC" character(1), | ||
| + | >> "SERVICE" string(39), | ||
| + | >> "STATES" character(10), | ||
| + | >> "LOCATION" string(31), | ||
| + | >> "COUNTRY" character(2), | ||
| + | >> "AREA_SERVED" string(511), | ||
| + | >> "IN_SERVICE" character(1), | ||
| + | >> "IN_SERVICE_DATE" ascii date, | ||
| + | >> "OVERLAY" character(1), | ||
| + | >> "OVERLAY_COMPLEX" character(20), | ||
| + | >> "PARENT" character(32), | ||
| + | >> "TIME_ZONE1" character(1), | ||
| + | >> "TIME_ZONE2" character(1), | ||
| + | >> constraint AREACODES_AREACODE_PK primary ("AREACODE") | ||
| + | >> ); | ||
| + | Table AREACODES attached | ||
| + | </code> | ||
| + | |||
| + | The newly attached tables can be seen using the SHOW TABLES command in OdxSQL: | ||
| + | |||
| + | <code> | ||
| + | > show tables | ||
| + | |||
| + | Tables | ||
| + | ------ | ||
| + | |||
| + | Environment | ||
| + | Database | ||
| + | Table Length Cols Rows Type | ||
| + | ---------------------------------------------------------------------------- | ||
| + | USER_ENVIRONMENT | ||
| + | SIMPLE | ||
| + | COUNTRIES 98 7 239 FIXED | ||
| + | STATES 44 6 76 FIXED | ||
| + | GENDERS 33 2 2 FIXED | ||
| + | HOUSEHOLDS 99 6 1,909 FIXED | ||
| + | INDIVIDUALS 159 7 5,000 FIXED | ||
| + | $TEMPDB | ||
| + | *AREACODES 747 21 800 FIXED | ||
| + | </code> | ||
| ==== ==== | ==== ==== | ||