Updates on Informix 4GL

1. Updates on Informix 4GL Jonathan LefflerArchitect, Foundation Engineering Updates to Informix 4GL

2. Agenda • Minor Features in I4GL 7.30 • CENTURY fields • SQL Blocks • Connection Management • Dynamic Reports • Dynamic INPUT ARRAY • String concatenation ‘||’ • Informix 4GL in the Future Updates to Informix 4GL

3. Minor Features in I4GL 7.3 • Based on almost-standard CSDK 2.30 • GSS limit increased to 2 GB • Add --@ comment parallel to --# in D4GL • fgl_getkey() function • Bug fixes (of course) Updates to Informix 4GL

4. CENTURY Fields • Also known as DBCENTURY fields • New ATTRIBUTE in form • Also attribute for PROMPT • Overrides DBCENTURY environment variable • Only affects entry of dates with 2-digits for year Updates to Informix 4GL

5. CENTURY Fields • The CENTURY attribute is simple to use: f000 = FORMONLY.dob TYPE DATE, CENTURY = “P”, COMMENTS = “Enter date of birth”; • When user types 2-digits for year • interpreted as past date • Applies to: • DATE • DATETIME Updates to Informix 4GL

6. CENTURY Fields • Four possible values • P past • C closest • F future • R present century • If not set, DBCENTURY applies. • If DBCENTURY not set, R applies. Updates to Informix 4GL

7. CENTURY attribute for PROMPT • PROMPT statement can also be used to enter DATE (or DATETIME) values. PROMPT “Enter DOB” FOR dob ATTRIBUTE(CENTURY=“P”) • Same meaning as in form • Provided for completeness and consistency Updates to Informix 4GL

8. DATE Entry Guidelines • Always display 4 digits for the year • No exceptions in form input fields • If user types 4 digit year, that overrides: • CENTURY field attribute • DBCENTURY environment variable • Try to avoid FORMAT or PICTURE • Effectively ignores DBDATE otherwise Updates to Informix 4GL

9. SQL Blocks • Support for preparable SQL statements • Connection management not in SQL block • Nor other non-preparable statements • Future proofing • Single statement per SQL block • Not CREATE PROCEDURE • Must be preparable statement Updates to Informix 4GL

10. SQL Blocks - Simple • Parameter-less SQL statements SQL CREATE TRIGGER t1_sometable UPDATE FOR SomeTable REFERENCING NEW AS newrow ON EVERY ROW (EXECUTE PROCEDURE LogUpdate(“SomeTable”, newrow.pk_column)) END SQL Updates to Informix 4GL

11. SQL Blocks - Input • Use $ to identify I4GL variables SQL SELECT TRIM(SomeColumn) || ‘-new’ FROM SomeTable WHERE PK_Column = $rec.pk_column END SQL Updates to Informix 4GL

12. SQL Blocks - Output • Use $ to identify I4GL variables • Use INTO clause in correct position SQL EXECUTE PROCEDURE someproc(12, 19) INTO $var1, $rec.part2, $arr[i].part3 END SQL Updates to Informix 4GL

13. SQL Blocks - Input & Output • Use $ to identify I4GL variables • Use INTO clause in correct position SQL EXECUTE PROCEDURE someproc($v1, $v2) INTO $var1, $rec.part2, $arr[i].part3 END SQL Updates to Informix 4GL

14. SQL Blocks - Cursors • Special notation for cursors • Use $ to identify I4GL variables DECLARE c_select SCROLL CURSOR WITH HOLD FOR SQL SELECT TRIM(BOTH SomeCol) || ‘-old’ INTO $str FROM SomeTable WHERE OtherCol > $val END SQL Updates to Informix 4GL

15. Connection Management • Direct support for • CONNECT • DISCONNECT • SET CONNECTION Updates to Informix 4GL

16. Connection Management • For example: CONNECT TO ‘stores7@dbserver’ AS $conn1 USER $username USING $password WITH CONCURRENT TRANSACTIONS • Always use VARCHAR for password • Trailing blanks are not otherwise significant • Database can be specified by variable • Names can be literals (password cannot!) Updates to Informix 4GL

17. Connection Management • SET CONNECTION $conn1 • SET CONNECTION CURRENT DORMANT • DISCONNECT $conn1 • DISCONNECT CURRENT • DISCONNECT ALL • CONNECT TO DEFAULT • SET CONNECTION DEFAULT DORMANT • DISCONNECT DEFAULT Updates to Informix 4GL

18. Connection Management • Can have multiple simultaneous connections • To OnLine and SE • Only one can be active at any time • Others are dormant • I4GL cannot be multi-threaded reliably • Dormancy is most important with threads Updates to Informix 4GL

19. Other non-preparable statements • EXECUTE IMMEDIATE • No input or output parameters • CREATE PROCEDURE FROM • Designed for CREATE PROCEDURE • File can contain any arbitrary SQL • Cannot write CREATE PROCEDURE in SQL block Updates to Informix 4GL

20. Other non-preparable statements • OPEN WITH REOPTIMIZE • Not supported  • SET DEFERRED_PREPARE • Not supported  Updates to Informix 4GL

21. Future Proofing • SQL blocks are not parsed very much • New SQL syntax can be used • If it can be prepared and executed • Or prepared and declared • Provides some future proofing • Better syntactic analysis in future • Does not support new non-preparable SQL • Uses PREPARE on contents of SQL block • Non-preparable SQL requires special handling Updates to Informix 4GL

22. Dynamic Reports • Configure size of report pages at run time • Configure any destination in single statement • One START REPORT a possibility Updates to Informix 4GL

23. Dynamic Report • Two new clauses • OUTPUT and DESTINATION • WITH • OUTPUT “pipe” DESTINATION “command” • But can use variables for both strings Updates to Informix 4GL

24. Dynamic Reports • WITH clause • PAGE LENGTH • LEFT MARGIN • RIGHT MARGIN • TOP MARGIN • BOTTOM MARGIN • TOP OF PAGE Updates to Informix 4GL

25. Dynamic Report • Hence can use database to store • Printer commands • Page sizes • Control strings Updates to Informix 4GL

26. Dynamic Report • All variables except reportname START REPORT reportname TO OUTPUT otype DESTINATION dstring WITH PAGE LENGTH = plength, TOP MARGIN = tmargin, BOTTOM MARGIN = bmargin, LEFT MARGIN = lmargin Updates to Informix 4GL

27. Dynamic Report • Need ability to output zero-width strings  • Printer control strings • Possible notations • PRINT str ZERO_WIDTH • PRINT ZERO(str) • Would simplify column counting • And printer special effects Updates to Informix 4GL

28. Dynamic INPUT ARRAY • Many new features for I4GL • COUNT, MAX_COUNT attributes • CURRENT ROW DISPLAY attributes • INSERT KEY, DELETE KEY attributes • CANCEL INSERT, CANCEL DELETE • fgl_dialog_setcurrline() function Updates to Informix 4GL

29. Dynamic INPUT ARRAY • COUNT attribute • Replacement for SET_COUNT() • Number of initialized rows of data • MAX_COUNT attribute • Specifies effective size of array • Can be smaller than defined size Updates to Informix 4GL

30. Dynamic INPUT ARRAY • INSERT KEY attribute • Programmatically disable INSERT key • Can still append after initialized data • DELETE KEY attribute • Programmatically disable DELETE key Updates to Informix 4GL

31. Dynamic INPUT ARRAY • CANCEL INSERT • Allowed in BEFORE INSERT only • Behaves like NEXT FIELD • No further actions taken • Can control insert conditionally • No rows inserted in previous data • Can suppress append operations Updates to Informix 4GL

32. Dynamic INPUT ARRAY • CANCEL DELETE • Allowed in BEFORE DELETE only • Behaves like NEXT FIELD • No further actions taken • Can control delete conditionally • Eg cannot delete pre-existing rows Updates to Informix 4GL

33. Dynamic INPUT ARRAY • CURRENT ROW DISPLAY attribute • Takes string defining display attributes for current row • Disjoint from other display attributes specified in INPUT ARRAY • Of most use in character mode • D4GL GUI mode already highlights current row Updates to Informix 4GL

34. Dynamic INPUT ARRAY • Function fgl_dialog_setcurrline() • Renamed D4GL dialog.setcurrline() • Specify row on screen array, and • Specify row in program array • Display is aligned appropriately Updates to Informix 4GL

35. String Concatenation Operator ‘||’ • Part of SQL-89 • Servers support since version 5.00 • Now added to inline I4GL • Same semantics as SQL version • NULL || string is NULL • string || NULL is NULL • Hence different from I4GL comma operator Updates to Informix 4GL

36. String Concatenation Operator ‘||’ • Cannot be used in inline SQL statements • Not 4.10 SQL syntax • Can be used in function calls • Cannot be used in DISPLAY  Updates to Informix 4GL

37. Informix 4GL in the Future • Planning a 7.31 release • Support for long identifiers • As found in IDS 9.2x and later • 128 characters for object names • 32 characters for object owners • Identifiers in I4GL will be raised to 128 too • Direct use of ClientSDK • Probably 2.80 • Will allow upgrades to CSDK independently of upgrades to I4GL Updates to Informix 4GL

38. Informix 4GL for DB2 • Only a possibility • Under discussion, that’s all • Maybe the direction of the future • A lot of it will not be all that hard • Type mapping will be hardest • DATETIME YEAR TO MONTH • DATETIME MONTH TO MINUTE • INTERVAL  duration? Updates to Informix 4GL

39. Other Possible Enhancements • Support for IUS built-in types: • LVARCHAR • BOOLEAN • INT8, SERIAL8 • Add named constants • CONSTANT xyz INTEGER = 32 • VARIABLE pqr INTEGER • Alternative to DEFINE pqr INTEGER • TYPE to define record types, etc? • Other core language features from NewEra? Updates to Informix 4GL

40. Other Possible Enhancements • COLLATE keyword • Controls character string comparisons • Depends entirely on support in servers too • Use shared libraries to enhance p-code runner • No need to build a custom runner • Just distribute shared library • Specify on command line • fglgo -l $INFORMIXDIR/lib/user/module.so program.4gi • Rescue fgllib and fgllink from NewEra • In place of rdslib and rdslink on IIUG web site Updates to Informix 4GL

41. Other Possible Enhancements • GUI • Lose cursor mangling altogether • Distinguish localized character types: • CHAR from NCHAR • VARCHAR from NVARCHAR • Replace Informix curses library with another • Either local machine standard library • Or a modern curses library distributed with I4GL Updates to Informix 4GL

42. The End Thank you for listening Any Questions? Updates to Informix 4GL