Integration: Relational Databases

SQL Server

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: 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:

> 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                          sqlserver
>>  version                       "9"
>>  filedsn                       "dat/simple.dsn"
>>  user                          "simple"
>>  password                      "simple"
>>  index_directory               "idx"
>>  in                            "simple.xml";
Database SIMPLE created in simple.xml
-> ;
-> create table          "COUNTRIES"
>>  physical             "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             "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             "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             "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             "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 "HOUSEHOLDS",
>>   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_ssv\simple.xml
->
-> ; Update the Omnidex indexes
-> update indexes;
Updated indexes for COUNTRIES (239 rows, 0.016 cpu, 0.516 elapsed)
Updated indexes for STATES (76 rows, 0.000 cpu, 0.031 elapsed)
Updated indexes for GENDERS (2 rows, 0.000 cpu, 0.015 elapsed)
Updated indexes for HOUSEHOLDS (1,909 rows, 0.016 cpu, 0.125 elapsed)
Updated indexes for INDIVIDUALS (5,000 rows, 0.016 cpu, 0.578 elapsed)
Omnidex indexes updated for environment USER_ENVIRONMENT
-> ;
-> ; Update statistics to improve performance optimization;
-> update statistics;
Updated statistics for COUNTRIES (239 rows, 0.266 cpu, 0.281 elapsed)
Updated statistics for STATES (76 rows, 0.016 cpu, 0.016 elapsed)
Updated statistics for GENDERS (2 rows, 0.000 cpu, 0.015 elapsed)
Updated statistics for HOUSEHOLDS (1,909 rows, 0.063 cpu, 0.063 elapsed)
Updated statistics for INDIVIDUALS (5,000 rows, 0.109 cpu, 0.125 elapsed)
Statistics updated for environment USER_ENVIRONMENT
-> ;
-> ; Update the text database to enable PowerSearch;
-> update text;
Updated text for COUNTRIES (0.016 cpu, 0.016 elapsed)
Updated text for STATES (0.016 cpu, 0.015 elapsed)
Updated text for GENDERS (0.000 cpu, 0.000 elapsed)
Updated text for HOUSEHOLDS (0.047 cpu, 0.062 elapsed)
Updated text for INDIVIDUALS (0.078 cpu, 0.079 elapsed)
Text metadata updated for environment USER_ENVIRONMENT
-> ;
-> disconnect
Disconnected

Additional Resources

See also:

 
Back to top
integration/rdbms/sqlserver/example.txt · Last modified: 2016/06/28 22:38 (external edit)