Release Notes for ESQL/C & INET for NT 3.5 version 5.01.TE1

================================= RELEASE NOTES FOR INFORMIX 5.01.TE1 PRODUCT RELEASE As Of 01-31-95 ================================= TABLE OF CONTENTS I. OVERVIEW OF RELEASE NOTES II. PRODUCTS CONTAINED IN THE 5.0 SERVERS, CONNECTIVITY, & EMBEDDED PRODUCTS RELEASE III. SPECIAL CONSIDERATIONS FOR DEVELOPERS USING 5.0 RELEASE SOFTWARE IV. INFORMIX DATABASE SERVER PRODUCTS V. INFORMIX EMBEDDED LANGUAGES VI. PRODUCT DEFICIENCIES
I. OVERVIEW OF RELEASE NOTES The purpose of these release notes is to make the user aware of changes in products that might affect existing applications. The release notes contain information on changes from previous versions, known problems, and workarounds. Problem IDs are assigned to known problems to assist you in identifying the problems to Client Services personnel. The release notes document is not intended to be all-inclusive; it is a tool to assist you in the update process. Please consult Informix product manuals for additional information on product features and for clarification of product behavior.
II. PRODUCTS CONTAINED IN THIS 5.01 CLIENT/SERVER RELEASE The following products are contained in the 5.0 servers and connectivity products release: Product Name Generic Name ------------ ------------ INFORMIX-ESQL/C for Windows Application Development Tool INFORMIX-NET for Windows Client/Server Product The following major features have been introduced in this 5.01 release: INFORMIX-ESQL/C for Windows INFORMIX-NET for Windows Dynamic SQL X/Open Interface
III. SPECIAL CONSIDERATIONS FOR DEVELOPERS USING 5.0 RELEASE SOFTWARE COMPATIBILITY OF APPLICATION DEVELOPMENT TOOLS AND DATABASE SERVERS =================================================================== Informix ESQL/C and Informix-NET have been updated to compatible with NT 3.5 in this release. Informix ESQL/C is certified to be compatible with Visual C++ 2.0 in this release. ORDER OF INSTALLATION ===================== The order of installation for Informix products is important. For DOS/Windows client products, install in version number order. COMPATIBILITY ITEMS FOR EXISTING APPLICATIONS ============================================= Some of the changes, bug fixes or enhancements made in Version 5.0 could potentially require changes to existing applications. We expect that incompatibilities will be rare; however, we are providing the information so you can determine if you have any applications that may require updating. Many of the following items have references to additional information contained in the documentation. 1. Prior to Version 4.1, the database server products allowed explicit table name qualification (<table name>.<column name>) in the column list of INSERT statements and the SET clause of UPDATE statements. For example, INSERT INTO tab (tab.col1, tab.col2) VALUES (1, 2); UPDATE tab SET tab.col = 10; The above syntax is incompatible with the ANSI standard, so Version 4.10.UD1 was modified to return a syntax error if a column was explicitly qualified with a table name in these contexts. The modified syntax which produces results equivalent to the above syntax is as follows: INSERT INTO tab (col1, col2) VALUES (1, 2); UPDATE tab SET col = 10; However, due to backward compatibility problems that this change imposed, in versions later than Version 4.10.UD2, and in Version 5.0, the table.column syntax is allowed again. If the user has the environment variable DBANSIWARN set or uses the -ansi option at runtime, an ANSI warning is returned to the user when s/he uses the above non-ANSI syntax. 2. New syntax has been introduced to support the creation of private synonyms in non-ANSI databases. A private synonym can only be accessed by the owner of the synonym, or by explicitly naming the owner of the private synonym. Existing synonyms in non-ANSI databases are public synonyms and can be accessed by all users. In ANSI databases, synonyms are always private. If a public and a private synonym have the same name, precedence is given to the private synonym. Prior to Version 4.0, synonyms were always private. A change was made in Version 4.00.UC1 that created some problems with backward compatibility. This has been corrected and now private synonyms are supported again in non-ANSI databases. The new syntax is as follows: CREATE [PUBLIC | PRIVATE] SYNONYM [owner.]<synonym> FOR [dbname[@sitename]:][owner.]<table> 3. In Version 4.1 of the database server products, a change was made to return error -360 (Cannot modify table or view used in subquery.) if you attempted a delete that contained a subquery that reference the table being deleted. This conforms to the ANSI standard: prior behavior was a product deficiency in the previous versions of our software. In versions prior to 4.1, such a subquery could return unpredictable results depending on the physical sequence of affected rows. Version 4.1 and higher return the -360 error, thereby ensuring integrity. This modification was also made to resolve some inconsistencies between SELECT, DELETE, and INSERT. 4. There is a new command-line option introduced for static cursor names and statement-id names to be consistent with the behavior of case sensitivity in the Informix Embedded products, Version 4.1. In the following example, \$prepare st from "select * from tab1"; \$prepare ST from "insert into tab2 values (1,2)"; \$declare curname cursor for st; \$declare CURNAME cursor for ST; Informix embedded products, Version 4.1 did not return an error in the case of the statement-id and cursor names. In Version 5.0, this syntax is not allowed as both the cursor names and statement-id names are now case insensitive. The database server products have always treated the cursor names as case insensitive. If this change presents a compatibility problem for existing embedded language applications, you may instruct the preprocessor to be sensitive to case differences in statement-id and cursor names, effectively retaining the 4.X behavior. To do so, specify the -cs parameter in your preprocessor command line. However, we do not recommend using this new option except as a temporary solution, as it will be removed in Version 6.0 Please make a note to change your applications accordingly. Note that dynamically named cursors and statement-ids have always been case insensitive. 5. Beginning with Version 5.0, when a statement is prepared and executed, the database server performs extra validation checks to make sure that each time the prepared statement is executed, the information gathered at preparation time is still valid. If a DROP TABLE, ALTER TABLE, or RENAME TABLE statement affects a table referenced in the statement, the prepared statement is invalidated. If the statement is invalid, an error is returned, and the user must reprepare the statement. For example, in the following sample ESQL/C program, the open of the cursor will now return an error because the table is dropped after the statement is prepared. \$prepare qid from "select * from tab"; \$drop table tab; \$create table tab(a int); \$declare c cursor for qid; \$open xx; /* this will return an error -242 or -710 */ 6. Currently Version 5.0 conforms with the ANSI SQL89 standard, dated August 1987. Thus, for views created with the WITH CHECK OPTION, the insert statement below succeeds. In other words, a data row inserted into a view created with the WITH CHECK OPTION must result in the WHERE clause of the view definition evaluating to true or unknown. CREATE TABLE t(a int); CREATE VIEW v AS SELECT * FROM t WHERE a > 10 WITH CHECK OPTION; INSERT INTO v VALUES(NULL); Subsequently, the SQL89 standard has been changed to correct an errata. As a result of this correction, the insert above should now fail. In other words, rows inserted into views created with the WITH CHECK OPTION must result in the WHERE clause of the view definition evaluating to true and only true. In order to conform with the corrected ANSI standard, in a future version, the insert statement above will no longer succeed. 7. Due to certain limitations in the Version 4.0 application development tools and database server products, certain statements used within a client/server environment render unexpected results if Version 4.0 application development tools are used in conjunction with Version 4.1 or Version 5.0 database server products. If the user attempts to create a database in the root directory on a remote server, the user may see a -330: Cannot create database. Always specify a full pathname, or create the database locally on the remote server. If the user attempts to close a database, a -457: SQL engine terminated unexpectedly may occur. This may cause some inconveniences in applications where error handling is being utilized to trap errors. To alleviate these problems, it is recommended to maintain only Version 4.1 and Version 5.0 products in a client/server environment. 8. The following is a list of the compatibility issues for triggers between 5.01 and 5.0 servers: A. Triggers and Cursor Operations When a 5.0 database server performs a cursor operation that activates a trigger on a 5.01 database server, the 5.01 database server returns an error. A 5.0 database server is not able to send a message that the 5.01 database server needs to process triggers correctly for a cursor operation. B. Stored Procedures as Triggered Actions When you use a stored procedure as a triggered action and the procedure is not part of a DML statement, a 5.01 database server returns an error if the procedure is a remote procedure that will execute on a 5.0 database server. The 5.0 database server is not able to receive a message that the 5.01 database server normally sends to ensure that the procedure executes properly. C. Cascading Triggers If a 5.01 version of INFORMIX-OnLine executes a remote statement on a 5.0 version of OnLine as part of a triggered action, and if logging is specified, the maximum number of cascading triggers becomes 5 rather than 61. This is due to the limit of savepoints in version 5.0 of INFORMIX-OnLine. If logging is not specified, the maximum number of cascading triggers remains 61. If the maximum number of triggers are exceeded in this case, the database server returns ISAM error -124 rather than SQL error -748 ("Exceeded limit on maximum number of cascaded triggers"). The returned SQL error indicates only that the triggered statement failed.
IV. INFORMIX DATABASE SERVER PRODUCTS KEYWORD LIMITATIONS =================== In Version 5.0, with the introduction of referential and entity integrity and stored procedures, new keywords were introduced to the SQL language. As a result, certain uses of these new keywords may now result in ambiguities that did not exist prior to Version 5.0. For example, the following statement now returns a syntax error because "check" is an SQL keyword; in the following context, it can be interpreted as being part of a check constraint definition. CREATE TABLE tab (check INT); For a complete list of keyword limitation, refer to "The Informix Guide to SQL: Reference" Manual, Chapter 7. ADDITIONAL FEATURE FUNCTIONALITY ================================ Sort Merge Joins ---------------- Sort merge is a new table join technique implemented in Version 5.0. Sort merge join provides an alternative to the existing nested loop table join where temporary indexes are used. The query optimizer will choose the best path of execution to produce the fastest possible join: either loop join, sort merge, or a combination of the two. Loop joins will create a temporary table and index on the inner table of a join, if such an index does not already exist. A sort merge join instead sorts either one or both of the tables involved in the join. Consequently, if sort merge joins are chosen, sort temp space is required rather than temp space within dbspaces (in the case of INFORMIX-OnLine). The sort temp space is determined by the value of DBTEMP. Unique Constraints ------------------ In Version 5.0, database server products no longer require that a user drop a unique index before creating a unique constraint on the same set of columns. For example, the following sequence of statements will now work. Unique constraints can now coexist with unique indexes. CREATE TABLE tab(a INT); CREATE UNIQUE INDEX i_tab ON tab(a); ALTER TABLE tab ADD CONSTRAINT UNIQUE(a); If the constraint "a" is later dropped, the "i_tab" index remains. New System Catalog Tables ------------------------- Ten new system catalog tables have been introduced in Version 5.0 of the database server products (only eight in INFORMIX-SE). For more information, please refer to Chapter 2 in "The Informix Guide to SQL: Reference" for additional information. The new catalogs are: sysreferences -- referential constraints syschecks -- check constraints sysdefaults -- default values syscoldepend -- check constraint dependencies sysprocedures -- stored procedures sysprocbody -- stored procedure contents sysprocplan -- query plans for stored procedures sysprocauth -- privileges for stored procedures sysblobs -- blob storage information (OnLine only) sysopclstr -- optical clusters (OnLine only) In addition, the widths of the "tabauth" column in systabauth and the "colauth" column in syscolauth have increased by one character. This was necessary to store the additional references privilege required for referential integrity. The new system catalog tables are automatically created the first time the user opens an existing database using a 5.0 database server product. The two existing character columns are increased in width also at this time. Once the database has been converted, you can no longer use a Version 4.1 database server product. Triggers -------- An SQL trigger is a mechanism that automatically sets off a specified set of SQL statements when a triggering event occurs on a table. It enables you to automate logically related changes to the database. Triggers are stored in the database and, therefore, can reduce the amount of code that is required in programs that interact with the database. Two SQL statements, CREATE TRIGGER and DROP TRIGGER, allow you to create triggers on and drop triggers from tables, respectively. Two system catalog tables, systriggers and systrigbody, store information about the triggers in the database.
V. INFORMIX EMBEDDED LANGUAGES Scope of SQL statement identifiers ================================== In Informix embedded SQL products Version X.10.03 and earlier, prepared SQL statement names and cursor names (as specified in the PREPARE and DECLARE statements) were local to the compilation unit (i.e., .eco source module) in which they were defined. A cursor or prepared statement declared in any paragraph in that source file could be used by any other paragraph in that same source file, but it could not be referenced from other source files. This practice is consistent with the other Informix embedded SQL products and with INFORMIX-4GL. This feature allowed programmers to use identical statement names in separate source files, with each file having its own object identified with that name. The base file name would be prepended to the internal statement name during the preprocessing phase so that the database engine would still be able to uniquely identify the statements. However, this functionality introduced some problems of its own. Since the database engine only recognizes the first 18 characters of a statement name, long source file names and/or statement names which were nonunique in the first few characters could cause the statement names to collide and be misassigned. In Version 4.0, this functionality was changed to consider all database server statement names to be global to the entire program. This practice avoids the disadvantages mentioned in the previous paragraph and is more in line with ANSI requirements. Although well-intentioned, this change can result in behavior changes in applications compiled from multiple source files that had similarly-named cursors or prepared SQL statements. In Version 5.0, we have introduced a new preprocessor option that restores the X.10.03 prepared statement and cursor naming conventions. By specifying the "-local" flag on the command line, the older convention for internally naming cursors and prepared statements can be chosen. Example: esql -local [other options] main.ec func1.ec func2.ec The objects main, func1, and func2 will then have their SQL cursor and prepared statement names internally prepended with the inode number of the files "main", "func1", and "func2", respectively. For example, I<hex representation of inode><cursor_name> So, the preprocessor could generate the following name for cursor "cust_cur" while processing a file with inode number 12000: I2EE0cust_cur (2EE0 is hex number for 12000). If a cursor named "cust_cur" was defined in all three files, each would be recognized as a separate cursor by the database server. If longer names are truncated, an error will return if the resulting names are identical. DESCRIBE WITH FLOATS ==================== A DESCRIBE of a prepared statement such as the following: select f from tab where tab is defined as: create table tab ( f float ); will describe f as of type DECIMAL instead of FLOAT in some heterogeneous client/server configurations where the floating point formats of the two computers are different. One example of the format difference is the storage methods that the hardware employs. Some chip vendors use the IEEE floating formats, but one stores the float's most significant byte first while the other chip stores the most significant byte last. Some other hardware platforms use their own proprietary formats. Because of the potential problems converting between these known and unknown formats, we chose to use our own decimal format as the intermediary vehicle in transfering floating point data. LIMITS IN INFORMIX-ESQL/C ========================= Size of host object name: 132 chars Size of host object subscript: 132 chars Size of indicator object name: 132 chars Size of value for fetch relative: 132 chars Size of SQL identifier (table name, etc): 132 chars Size of number constant: 132 chars Size of macro name: 132 chars Size of macro value: 132 chars Size of statement name: 132 chars Size of quoted string literal: 256 chars Number of statement_ids 64 Number of levels of host-language block nesting 16 Number of levels of include-file nesting: 8 Length of line: 512 chars Size of statement label: 70 chars Number of nested records: 100
VI. PRODUCT DEFICIENCIES There are no known deficiencies.