ODBC-201

1. ODBC-201 Creating Context definitions for your own data

2. Creating Context Definitions • RUN CONTEXT/TOOLS • Application • Application Data File • Master File Record • Context Definition

3. Application • Defines a database or group of Tables. • “Root Directory”. • Required Fields: • APP_ID - Application Code • APP_NAME - Application Name • Optional Fields (Lorica): • APP_PROGRAM - Relative Path for Programs • APP_DATA - Relative Path for Data • APP_SOURCE - Relative Path for Source

4. Application Data File • A Table or Data File within the Application. • Required Fields: • FILE_ID - Table ID or Code . • DESCRIPTION - Table Description. • APP_DATA - Relative path for data file. • MASTER - Master Data File Name. • KEY1-KEY9 - Index File Names (Optional). • Optional Fields: • Security Group Codes (Not Covered today).

5. Master File Record • A Record Layout for a Table • Usually 1 per File/Table (There May be more) • Required Fields: • RECORD_ID - Layout Code (Often Data File). • DESCRIPTION - Layout Description. • PREFIX - 3 Character code “EM_”. • Optional Fields: • PREFIX - Either Prefix or Suffix is required. • SUFFIX - Neither is actually used in ODBC. • RECORD_ID - Used for multiple Record Layouts.

6. Context Definition • Actual File Layouts • Each Field is defined with attributes • Required Fields: • FIELD_NAME - 15 Character Code • DISK_STORAGE- Field Type (C,PD,BH,Etc) • DTE_FMT - Date Format (CCYYMMDD) • ORIG_CAPTION- English Description of Field • RECORD_POS - Field Starting Position • RECORD_LEN - Field Length • DECIMAL_POS - Number of Decimal Positions.

7. Context Definition – Field_Name • 15 Characters. • Duplicated not Allowed. • Codes used within ODBC. • Avoid Spaces and Symbols (~!@#$%^&*-+=). • Not all Windows Apps can use 15 Characters. • This is a critical design component. • Be Consistent: • Define & use abbreviations (Cty,ST,PH). • These codes will be used for programming. • Dates should include _DATE ot DATE_.

8. Context Definition – Disk Storage • Same as found in your “FORM” Statements. • Field Format: • C - Characters. • PD - Packed Decimals. • N - Numbers. • ZD - Zoned Decimals. • B,BH,BL - Binary Values. • DT,DH,DL - Binary Dates (In Julian or DAYS). • Etc - Other formats work as well!

9. Context Definition – Dte_Fmt • Defines a Date field, and the format stored in BR. • Field Name “_DATE” or “DATE_” also defines a date. • DTE_FMT is Optional (BR attempts to guess). • Use it anyways, it is less confusing later. • Sample Valid Date Formats: • DAYS • CCYYMMDD • MMDDCCYY • MM/DD/YY • YY/MM/DD

10. Context Definition – Orig_Caption • English Description of a Field. • Not actually used within ODBC. • Fill this in, good file layouts are important. • You may find this field usefull within BR.

11. Context Definition – Record Info • Required Components to read the field. • RECORD_POS: • Starting Position for Field. • Filler – I usually Name the Field FILLER+POS FILLER201. • RECORD_LEN: • Actual Field Length. • For example PD 6.2 is a 6 Character Packed Decimal. • ODBC will figure out the “True Length”. • DECIMAL_POS: • Number of Decimal Positions. • Only for numbers.

12. Context Definition – Other Fields • Other Fields are used by Lorica • Window Caption • Window Form • Report Caption • Report Form • Edit Routine • Pack Routine • Unpack Routine • Key Seq – This legacy field refers to indexes.

13. Context Manager • Context Manager is delivered with ODBC. • May be Used to Create and Maintain Files. • Is a great way to review existing file layouts. • Creates paper documentation. • Somewhat difficult to automate.

14. Context.wb/Context • New Library Delivered with this conference. • Is a Program to Import File Layouts. • Works with Context Manager for use in BR. • Library to be used within existing BR Apps: • Open Tables & Index Files. • Identify Open Tables. • Create Form Statements from Context. • Pack Existing Form Statements. • Open CSV or Tab delimited Files • Parse CSV or Tab delimited Files. • Assign SQL Variables for Program Use.

15. Importing your Own Tables • FILEAPP.TXT – Tab Delimited File to define Tables. • Required Fields: • APP_ID - Application Code • APP_NAME - Application Name • APP_DATA - Data File Path • FILE_ID - Table Name • DESCRIPTION - Table Description • MASTER - Master File Name • KEY1-9 - Index File Names (Optional) • CONTEXT - Path to File Layouts to be imported. • _Context – Sample App “ODBC Layouts”. • Create using Excel, save as Tab Delimited File.

16. Defining your own File Layouts. • Create 1 Text file per Table1. • Use MS-Excel or Write your Own Converter. • Required Fields: • FIELD_NAME - 15 Character Code • DISK_STORAGE- Field Type (C,PD,BH,Etc) • DTE_FMT - Date Format (CCYYMMDD) • ORIG_CAPTION- English Description of Field • RECORD_POS - Field Starting Position • RECORD_LEN - Field Length • DECIMAL_POS - Number of Decimal Positions.

17. Import FILEAPP.TXT into Context • RUN Context\Context.wb • Program will automatically create new records. • Existing Records will be updated. • Run Again to update layouts. • Delivered with this seminar: • FILEAPP.TXT – Sample Application. • Contains ODBC layouts for the Context Manager. • _CONTEXT – Folder containing layouts.

18. Workshop – Create your Own. • Create your own FILEAPP.TXT • Define an Application • Define one or more tables • Create layouts for one or more tables. • Still Have Time? look at Context.wb • 30 fnInit_Context – Open tables & define vars. • 40 fnopen_csv – Open FILAPP.TXT. • Intended to make things very simple. • Feel free to enhance routine for other fields.

19. Workshop – Create Layouts • [YourFile.txt] – File Layouts for your own File. • Documentation Spreadsheet: • In CONTEXT\Sample Context Fields.xls • Examples in CONTEXT\_CONTEXT folder. • Future Ideas: • Convert your own Layouts to import layout. • Bypass importer, and import directly to context. • Convert Gabriel’s SQL layouts to Context Manger. • Convert Context Manager to Gabriel’s.