Updates on Informix 4GL

1
Updates on Informix 4GL
Jonathan LefflerArchitect, Foundation Engineering
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

3
Minor Features in I4GL 7.3

• Based on almost-standard CSDK 2.30
• GSS limit increased to 2 GB
• Add --_at_ comment parallel to -- in D4GL
• fgl_getkey() function
• Bug fixes (of course)

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

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

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.

7
CENTURY attribute for PROMPT

• PROMPT statement can also be used to enter DATE
  (or DATETIME) values.
• PROMPT Enter DOB FOR dob
• ATTRIBUTE(CENTURYP)
• Same meaning as in form
• Provided for completeness and consistency

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

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

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

11
SQL Blocks - Input

• Use to identify I4GL variables
• SQL
• SELECT TRIM(SomeColumn) -new
• FROM SomeTable
• WHERE PK_Column rec.pk_column
• END SQL

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,
• arri.part3
• END SQL

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,
• arri.part3
• END SQL

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 gt val
• END SQL

15
Connection Management

• Direct support for
• CONNECT
• DISCONNECT
• SET CONNECTION

16
Connection Management

• For example
• CONNECT TO stores7_at_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!)

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

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

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

20
Other non-preparable statements

• OPEN WITH REOPTIMIZE
• Not supported ?
• SET DEFERRED_PREPARE
• Not supported ?

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

22
Dynamic Reports

• Configure size of report pages at run time
• Configure any destination in single statement
• One START REPORT a possibility

23
Dynamic Report

• Two new clauses
• OUTPUT and DESTINATION
• WITH
• OUTPUT pipe DESTINATION command
• But can use variables for both strings

24
Dynamic Reports

• WITH clause
• PAGE LENGTH
• LEFT MARGIN
• RIGHT MARGIN
• TOP MARGIN
• BOTTOM MARGIN
• TOP OF PAGE

25
Dynamic Report

• Hence can use database to store
• Printer commands
• Page sizes
• Control strings

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

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

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

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

30
Dynamic INPUT ARRAY

• INSERT KEY attribute
• Programmatically disable INSERT key
• Can still append after initialized data
• DELETE KEY attribute
• Programmatically disable DELETE key

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

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

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

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

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

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 ?

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

38
Informix 4GL for DB2

• Only a possibility
• Under discussion, thats 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?

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?

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

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

42
The End

• Thank you for listening
• Any Questions?