This shows you the differences between two versions of the page.
|
integration:rdbms:sqlserver:example [2011/04/07 17:54] 127.0.0.1 external edit |
integration:rdbms:sqlserver:example [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ~~NOTOC~~ | ||
| - | |||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | |||
| - | ====== Integration: Relational Databases ====== | ||
| - | |||
| - | ===== SQL Server ===== | ||
| - | |||
| - | [[integration:rdbms:sqlserver:home|Overview]] | | ||
| - | [[integration:rdbms:sqlserver:environments|Environments]] | | ||
| - | [[integration:rdbms:sqlserver:databases|Databases]] | | ||
| - | [[integration:rdbms:sqlserver:tables|Tables]] | | ||
| - | [[integration:rdbms:sqlserver:constraints|Constraints]] | | ||
| - | [[integration:rdbms:sqlserver:datatypes|Datatypes]] | | ||
| - | [[integration:rdbms:sqlserver:queries|Queries]] | | ||
| - | [[integration:rdbms:sqlserver:updates|Updates]] | | ||
| - | **[[integration:rdbms:sqlserver:example|Example]]** | ||
| - | |||
| - | ---- | ||
| - | |||
| - | ==== Example ==== | ||
| - | |||
| - | The following example is a simple database using a SQL Server database. The Omnidex Environment File is displayed below; however, the entire example can be downloaded as a ZIP file at the following link: **{{:integration:rdbms:sqlserver:sqlserver_example.zip|Download ZIP File}}**. This ZIP file also includes files that can be used to create the SQL Server database. | ||
| - | |||
| - | The following statements will create and build an Omnidex Environment against a SQL Server database: | ||
| - | <code> | ||
| - | > use sql/create_all.sql | ||
| - | -> use sql/simple.sql | ||
| - | -> create environment | ||
| - | >> in "simple.xml" | ||
| - | >> with delete; | ||
| - | Environment created in simple.xml | ||
| - | -> ; | ||
| - | -> create database "SIMPLE" | ||
| - | >> type odbc | ||
| - | >> subtype mysql | ||
| - | >> filedsn "dat/simple.dsn" | ||
| - | >> user "simple" | ||
| - | >> password "simple" | ||
| - | >> index_directory "idx" | ||
| - | >> in "simple.xml"; | ||
| - | Database SIMPLE created in simple.xml | ||
| - | -> ; | ||
| - | -> create table "COUNTRIES" | ||
| - | >> physical "SIMPLE.COUNTRIES" | ||
| - | >> ( | ||
| - | >> "COUNTRY" CHARACTER(2) omnidex, | ||
| - | >> "DESCRIPTION" STRING(47) quicktext, | ||
| - | >> "LATITUDE" FLOAT omnidex usage "LATITUDE", | ||
| - | >> "LONGITUDE" FLOAT omnidex usage "LONGITUDE", | ||
| - | >> "CAPITAL" STRING(31) quicktext, | ||
| - | >> "CAPITAL_LAT" FLOAT omnidex usage "LATITUDE", | ||
| - | >> "CAPITAL_LONG" FLOAT omnidex usage "LONGITUDE", | ||
| - | >> constraint COUNTRIES_COUNTRY_PK primary ("COUNTRY"), | ||
| - | >> native COUNTRIES_COUNTRY_IDX ("COUNTRY") | ||
| - | >> ) | ||
| - | >> in "simple.xml"; | ||
| - | Table COUNTRIES created in simple.xml | ||
| - | -> ; | ||
| - | -> create table "STATES" | ||
| - | >> physical "SIMPLE.STATES" | ||
| - | >> ( | ||
| - | >> "STATE" CHARACTER(2) omnidex, | ||
| - | >> "DESCRIPTION" STRING(31) quicktext, | ||
| - | >> "STATE_CODE" CHARACTER(2) omnidex, | ||
| - | >> "REGION" CHARACTER(2) omnidex, | ||
| - | >> "COUNTRY" CHARACTER(2) omnidex, | ||
| - | >> "TAX_RATE" FLOAT omnidex, | ||
| - | >> constraint STATES_STATE_PK primary ("STATE"), | ||
| - | >> constraint STATES_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES", | ||
| - | >> native STATES_STATE_IDX ("STATE"), | ||
| - | >> ) | ||
| - | >> in "simple.xml"; | ||
| - | Table STATES created in simple.xml | ||
| - | -> ; | ||
| - | -> create table "GENDERS" | ||
| - | >> physical "SIMPLE.GENDERS" | ||
| - | >> ( | ||
| - | >> "GENDER" CHARACTER(1) omnidex, | ||
| - | >> "DESCRIPTION" STRING(31) quicktext, | ||
| - | >> constraint GENDERS_GENDER_PK primary ("GENDER"), | ||
| - | >> native GENDERS_GENDER_IDX ("GENDER") | ||
| - | >> ) | ||
| - | >> in "simple.xml"; | ||
| - | Table GENDERS created in simple.xml | ||
| - | -> ; | ||
| - | -> create table "HOUSEHOLDS" | ||
| - | >> physical "SIMPLE.HOUSEHOLDS" | ||
| - | >> ( | ||
| - | >> "HOUSEHOLD" CHARACTER(12) omnidex, | ||
| - | >> "ADDRESS" CHARACTER(50) quicktext, | ||
| - | >> "CITY" CHARACTER(28) quicktext, | ||
| - | >> "STATE" CHARACTER(2) omnidex, | ||
| - | >> "ZIP" CHARACTER(5) omnidex, | ||
| - | >> "COUNTRY" CHARACTER(2) omnidex, | ||
| - | >> constraint HOUSEHOLDS_HOUSEHOLD_PK primary ("HOUSEHOLD"), | ||
| - | >> constraint HOUSEHOLDS_STATE_FK foreign ("STATE") references "STATES", | ||
| - | >> constraint HOUSEHOLDS_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES" | ||
| - | , | ||
| - | >> native HOUSEHOLDS_HOUSEHOLD_IDX ("HOUSEHOLD") | ||
| - | >> ) | ||
| - | >> in "simple.xml"; | ||
| - | Table HOUSEHOLDS created in simple.xml | ||
| - | -> ; | ||
| - | -> create table "INDIVIDUALS" | ||
| - | >> physical "SIMPLE.INDIVIDUALS" | ||
| - | >> ( | ||
| - | >> "INDIVIDUAL" CHARACTER(12) omnidex, | ||
| - | >> "HOUSEHOLD" CHARACTER(12) omnidex, | ||
| - | >> "NAME" CHARACTER(50) quicktext, | ||
| - | >> "GENDER" CHARACTER(1) omnidex bitmap, | ||
| - | >> "BIRTHDATE" ODBC DATETIME omnidex, | ||
| - | >> "PHONE" CHARACTER(14) omnidex, | ||
| - | >> "EMAIL" CHARACTER(60) quicktext, | ||
| - | >> constraint INDIVIDUALS_INDIVIDUAL_PK primary ("INDIVIDUAL"), | ||
| - | >> constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSE | ||
| - | HOLDS", | ||
| - | >> constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS", | ||
| - | >> native INDIVIDUALS_INDIVIDUAL_IDX ("INDIVIDUAL") | ||
| - | >> ) | ||
| - | >> in "simple.xml"; | ||
| - | Table INDIVIDUALS created in simple.xml | ||
| - | -> ; | ||
| - | -> connect simple.xml | ||
| - | Connected to D:\class\lab_mys\simple.xml | ||
| - | -> | ||
| - | -> ; Update the Omnidex indexes | ||
| - | -> update indexes; | ||
| - | Updated indexes for COUNTRIES (239 rows, 0.016 cpu, 0.235 elapsed) | ||
| - | Updated indexes for STATES (76 rows, 0.000 cpu, 0.031 elapsed) | ||
| - | Updated indexes for GENDERS (2 rows, 0.000 cpu, 0.031 elapsed) | ||
| - | Updated indexes for HOUSEHOLDS (1,909 rows, 0.016 cpu, 0.094 elapsed) | ||
| - | Updated indexes for INDIVIDUALS (5,000 rows, 0.016 cpu, 0.766 elapsed) | ||
| - | Omnidex indexes updated for environment USER_ENVIRONMENT | ||
| - | -> ; | ||
| - | -> ; Update statistics to improve performance optimization; | ||
| - | -> update statistics; | ||
| - | Updated statistics for COUNTRIES (239 rows, 0.047 cpu, 0.062 elapsed) | ||
| - | Updated statistics for STATES (76 rows, 0.016 cpu, 0.031 elapsed) | ||
| - | Updated statistics for GENDERS (2 rows, 0.016 cpu, 0.016 elapsed) | ||
| - | Updated statistics for HOUSEHOLDS (1,909 rows, 0.063 cpu, 0.047 elapsed) | ||
| - | Updated statistics for INDIVIDUALS (5,000 rows, 0.109 cpu, 0.110 elapsed) | ||
| - | Statistics updated for environment USER_ENVIRONMENT | ||
| - | -> ; | ||
| - | -> ; Update the text database to enable PowerSearch; | ||
| - | -> update text; | ||
| - | Updated text for COUNTRIES (0.016 cpu, 0.031 elapsed) | ||
| - | Updated text for STATES (0.000 cpu, 0.000 elapsed) | ||
| - | Updated text for GENDERS (0.000 cpu, 0.016 elapsed) | ||
| - | Updated text for HOUSEHOLDS (0.016 cpu, 0.015 elapsed) | ||
| - | Updated text for INDIVIDUALS (0.016 cpu, 0.016 elapsed) | ||
| - | Text metadata updated for environment USER_ENVIRONMENT | ||
| - | -> ; | ||
| - | -> disconnect | ||
| - | Disconnected | ||
| - | ></code> | ||
| - | |||
| - | ===== ===== | ||
| - | |||
| - | **[[integration:rdbms:sqlserver:updates|Prev]]** | ||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | |||
| - | See also: | ||
| - | |||
| - | {{page>:integration:rdbms:see_also&nofooter&noeditbtn}} | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||