13 10 Things That May Be Different

LANSA Application Design

13.10 Things That May Be Different

SQL and ODBC do not handle all of  their table operations in the same manner as file operations on the IBM i. Here are some important points which you should be aware of:

  • An OPEN RDML command under the IBM i physically opens the database file. Under Visual LANSA using SQL/ODBC however, an OPEN RDML command is a logical open only, as a physical database file open only occurs when actually performing an operation such as FETCH or UPDATE on the file. Likewise a CLOSE RDML command only logically closes the database file when using SQL/ODBC under Visual LANSA. Any applications that may rely on an error being returned from an OPEN or CLOSE RDML command should be reviewed (e.g.: to check if a file/table exists).
  • On this SELECT loop point .... when fields A, B and C are selected in a SELECT loop like this:

         SELECT FIELDS(#A #B #C) FROM_FILE(...)

                WHERE(...............)

            .......

            .......

         ENDSELECT

 

they have a predictable and consistent value within the loop across all platforms.   They do not have predictable and consistent values outside the loop. So:

         SELECT FIELDS(#A #B #C) FROM_FILE(...)

            .......

            IF     COND(#A < 35.5)

            .......

            ENDIF

            .......

         ENDSELECT

 

 

   is a predictable piece of logic. While:

         SELECT FIELDS(#A #B #C) FROM_FILE(...)

                WHERE(...............)

         .......

         .......

         ENDSELECT

 

         IF     COND(#A < 35.5)

         .......

         ENDIF

 

 

   in any form or variation, is an unpredictable piece of logic.   Again, this type of logic would not be expected in sensibly constructed applications, so do not worry too much about this point.   The value of A (and B and C), in terms of data read from the selection table, after exit from the SELECT loop, are actually defined as "not defined".This means that their values at the termination of a SELECT / ENDSELECT loop are not predictable or consistent across platforms.

  • SQL/ODBC indexes will only be created for logical view definitions that have access path "IMMED".
  • Record locking is not supported. You should use the LOCK_OBJECT built in function instead.
  • Alternate collating sequences for logical files are not supported.
  • IBM i database dependencies e.g. multi-member files are not supported. Files will be implemented as SQL/ODBC tables and as such are made up of simple columns (fields) and rows (records). Additional indexes and views of the table may be created. It is suggested that your applications use files in this simple manner.
  • SELECT/OMIT criteria for logical view definitions with all SELECT or all OMIT operations are fully supported for row reading purposes only. Mixing SELECT and OMIT operations may not produce the same results when using SQL/ODBC views as using the IBM i logical views and will require testing.
  • The emulation facilities for the IBM i select/omit criteria are only provided to support the reading of selected information from database tables. They are not provided to (or designed to) allow you to control what information is inserted or updated into database table rows. If you are using an application design that relies on the use of uniquely keyed logical files (with associated select/omit criteria) to control the content of the your database then you may have to revise this component of your design.