What’s New in SIR2002 and How to Use it

1. What’s New in SIR2002 and How to Use it SIR2002

2. SIR2000 - Review • VisualPQL • Dialogs and Message Processing • New Commands • New Functions • Pop Up Boxes • ‘EXECUTE DBMS’ • New User Interface • ODBC • Multiple Databases

3. SIR2000 - Review • Descriptive, Plot, Graph Procedures • Internet Enabled • New Master Communication • Additional File Support • Unlimited Program Size • No ‘Table Space’ • Spreadsheet

4. SIR2002 Major New Features • User Interface Enhancements • Database Enhancements • VisualPQL Enhancements • Dialog and Menu Enhancements • PQLForms • Screen Painter for Dialogs • Screen Painter for PQLForms • ODBC Enhanced

5. Enter into window, submit with ctrl-R Search window Data/Spreadsheet menu access to tabfiles & database using indexes Paged Dialogs e.g. Procedures , Record Schema, Settings Program/member Improved search Create new procedure file Painters available from program menu Secondary Index on schema ‘Rewind’ Button SIR2002 User Interface

6. Database Enhancements • Secondary Indexes • Extended Strings • String Compression • Auto Increment Keys • SIR SPREADSHEET • New Grid • Secondary Indexes • Duplicate Value Label Check

7. Secondary Indexes • Analogous to tabfile secondary indexes • Multiple variables as keys in index • Multiple indexes on a record type • Creation - command or schema menu • Automatic maintenance • List Stats

8. Secondary Indexes Creation • Database/Secondary Index Menu • Command • CREATE [UNIQUE] DBINDEX index_name ON database.recname ( var_name [ ASC | DESC ] [ , ... ] ) [UPPER] • Creates the index structure • Creates the actual index from values of any existing records

9. Secondary Indexes Maintenance • Index is automatically maintained if records are added, deleted or modified • Maintenance automatic through all modules – Utilities, PQL, Master, Forms, SQL • Indexes rebuilt if database recovered from Import or Reload • Delete index • DROP DBINDEX index_name ON [database.] recname | ALL

10. Secondary IndexesPlaces Used • VisualPQL • PROCESS RECORD … INDEXED BY … • LOOKUP RECORD ... INDEXED BY … • PQLForms • SCREEN RECORD ... INDEXED BY … • SPREADSHEET utility • SIR SPEADSHEET RECTYPE = n /INDEXED BY …..

11. Extended Strings • Maximum Database String Variables size increased to 4094 • Database Strings Compressed • Database keys fixed length • Max. 320 characters in total • Sort keys same limits • Secondary Index keys same limits

12. Auto Increment Keys • Final Part of Key • Named Integer Variable • Specify as SORT IDS name (I) • Ascending number from previous record of same type in Case • Specify as 0 in REC IS (key,0) • If value specified, it is used

13. SIR2000 SPREADSHEET • Third Party Active-X Control • Windows Only • Needs Complete Data Set • Slow • Size Limits • No resize

14. SIR2002 GRID • Runs on all platforms • Displays immediately • No limitations on number of rows • Resizable • Used in SIR SPREADSHEET utility • Enhanced for secondary indexes

15. PROCESS REC …. INDEXED BY Extended Strings Extended Constants Redefinition of arrays Sorting Arrays GRID Command LOOKUP Command 50 extra functions (tabfile schema) File Handling Extended number of text files open Text files between windows and Unix HEX Format on READ and WRITE LABEL for headings on Quick Report FORMAT for fields on CSV VisualPQL Enhancements

16. PROCESS RECORD ... INDEXED BY • All existing clauses used in conjunction with INDEXED BY • AFTER, FROM, THRU, etc. • Makes the case and record current for update as necessary • RECORD IS constructs do not support the INDEXED BY clause

17. Extended Strings and Constants • String size increased to 4094 • Local Variables • Database Variables • Database Strings Compressed • Maximum constant limit of 32k removed • Limit is 4096 constants of any size • Database keys fixed length • Max. 320 characters in total • Sort keys same limits • Secondary Index keys same limits

18. Array Processing REDEFINE ARRAY array_name (dim1, dim2,...) • Alters dimensions • Alters number and/or value of any dimension • Array can grow or shrink • Existing values mapped to new dimensions • New values are set to missing. • NOARRAYMSG • SORT array_name [BY key_array_varname] [(n)] [DESCENDING]

19. GRID Command GRID title list_of_arrays (1 or 2 dimension) [HEADERS=(list_of_col_headers)] [RESPONSE = integer_varname| array_varname] [SIZE=rows] [DISPLAY=row,width] [UPDATE | NOUPDATE]

20. PQL Procedure (unchanged) SPREAD SHEET [/VARIABLES= varlist ] [/SORT=[(n)]variable[(A)|(D)], ...] [/BOOLEAN= (logical_expression)] [/SAMPLE= fraction [,seed ] ] [/TITLE= 'spreadsheet title']

21. LOOKUP command • LOOKUP RECORD dbname.recname | TABLE tabfile.table [INDEXED BY indexname] [GET VARS ALL| target_varlist| local_varlist = target_varlist}] [VIA keylist] [USING caseid,keylist] [FORWARD | BACKWARD] [RESULT num_varname] [WHERE (condition)]

22. New Functions • Secondary Indexes • DBINDS Number of indexes on database • DBINDN Name of index • DBINDR Record indexed • DBINDV Variables in index • DBINDV Name of nth variable in index

23. New Functions • DEDIT • Various ‘painter’ dialog functions • Files & Directories • FILECNT Count of files in named directory • FILEIS If name exists as file or directory • FILEN nth file in directory • FILESTAT Various data about named file e.g. size • FILETIME Various dates/times (creation/access) of file in SIR formats • MAKEDIR Creates a directory • DELDIR Deletes a directory

24. 1 gid group (UNIX) 2 st_atime Access Time 3 st_ctime Creation Time 4/9 st_dev Drive number 5 st_ino inode (UNIX) 6 st_mode Bit mask for file-mode information. 7 st_mtime Modification time 8 st_nlink Always 1 on non-NTFS file systems 10 st_size Size (bytes) 11 uid User (UNIX) New Functions FILESTAT Function

25. New Functions • FILETIME Function returns SIR times or dates about a file • 1 Time of last access • 2 Date of last access • 3 Time of creation • 4 Date of creation • 5 Time of last modification • 6 Date of last modification

26. Schema NVALLAB Synonym for NLABELS NVARLAB Number of lines in variable label NVARSC Number of variables in a record including common vars RECDOC Number of lines of documentation RECDOCN Nth line of documentation VALLABSN Label for value label n for variable VALLABSP Position of value label associated with specified value VALLABSV Value for value label n for variable VARLABSN Nth line of variable label VARNAMEC Name of variable using counts including common vars New Functions

27. New Functions • Tabfile Schema • TABINDU Index unique • TABRECS Number of rows on table • TABVINFN Various numeric data about a table column • TABVINFS Various character data about a table column • TABVRANG Valid or missing value ranges • TABVVALI Validates a table variable • TABVVLAB Label for value label n for table col • TABVVVAL Value for value label n for table col

28. New Functions • Main Output Window • WINCNT Number of lines in output windowWINLIN Nth line from the output windowWINMOVE Moves and resizes main windowWINPOS Moves to line in output window and highlights lineWINSELL Selected LineWINSELP Position in the line selected

29. File Handling • Extended number of text files open • Text files between windows and Unix • Binary File READ and WRITE Special Formats • HEXw Binary string, w digits(up to 4094) • IBw Binary integer, w digits(1,2 or 4) • RBw Binary real, w digits (4 or 8)

30. Dialog and Menu Enhancements • 4000+ Elements in single dialog • SHOW/HIDE elements in dialog • POPUP Menus in menus and dialogs • HELP Message type • TIMER Message type • DEDIT dialog type • SET ITEM FONT

31. ‘Tabbed’ or Paged Dialogs • 4000+ Elements • SET DIALOG TITLE string_exp • SHOW ITEM id • HIDE ITEM id

32. Pop Up Menus • DISPLAY POPUP LIST (string_exp,string_exp,..) | array_name,no_of_items ANCHOR id | AT row,col RESPONSE num_varname [SEPARATOR n,n...] [POSTYPE num_exp] • Displays pop up menu in menu or dialog • Positioned relative to ANCHOR id or at the position specified by the AT clause. • RESPONSE returns selected item (or –1)

33. Help Message • Help Dialog Message • MESSAGE HELP FID@__ • END MESSAGE • ? On dialog – drag to item

34. Timer Message • MESSAGE TIMER • Message processing block • Automatically generated • Used to refresh the display of image/ animation • ENABLE TIMER n • start automatic generation of messages every n tenths of a second • DISABLE TIMER stops messages

35. SET ITEM FONT • Sets font on text display in dialog • Size – Small, Standard, Large • Bold • Italic • Underline • Font Face

36. PQLForms • Replacement for Forms • Record at a time GUI screens • Data Browsing and Data Entry • Integrated with VisualPQL

37. PQLForms • Extensions to VisualPQL • Include standard VisualPQL in source as necessary • Generates standard VisualPQL • Keeps Forms ‘style’ • SCREEN per record/table • FIELD command • Next/Previous, etc. • Predefined Logic • Clauses on commands to edit, check data, etc. • Standard Buttons for users to navigate • Dialog Based

38. PQLForms Structure FORM – Overall Program SCREEN – Menu / Record / Row (PAGE – New Page of displayed fields) FIELD – Variable (Prompt,Data,Label) CALL – Button for user to pass control to other SCREEN END SCREEN END FORM

39. PQLForms Structure form . screen record EMPLOYEE . field id . field name . field currpos . call screen OCCUP . end screen . screen record OCCUP . field id . field position . field startsal . end screen end form

40. Example PQLForm

41. Using Forms • Run as normal from file or member • Save as :E • Ensure databases/tabfiles connected • Standard Buttons • Prompt/Data/Labels • Enter for Keys • Enter for Data

42. Screen Painter • Point & Click • Dialogs • PQLForms • Integrated with member editing

43. Dialog & Forms ‘Painter’ • Interactive, visual tool • Create and position dialog elements • Create and position forms elements • Link forms elements to database and table variables • Integrate with specification of all clauses • New VisualPQL dialog type

44. VisualPQL - DEDIT • DEDIT type,id,arg1,arg2 • DCONTROL - Types 1 to 12 LABEL EDIT BUTTON CHECK RADIO CHOICE LIST TEXT HLINE VLINE LBOX IMAGE • Messages – Types 0 to 11 INITIAL STOP NEW CLONECLONES MOVE SIZE DELETE PROPERTIES LIST TEST HELP

45. Dialog Painter • Integrates layout with logic specification • Use Members or Files • Edit menu recognises source

46. Form Painter • Similar to Dialog Painter • Linked to database & tabfile variables • Generates new Forms programs

47. SIR 2002 SIR2002