By: Sam Patterson
Abstract: InterBase XE Readme
Updated: September, 2010
This file contains important information that may not appear in the online Help. Please read this file in its entirety. The readme has has three main sections:
There are several new features available for this release:
Following are the system requirements for installing and running InterBase XE 32-bit and 64-bit Native Binary applications on Windows.
Microsoft Windows 32-bit operating systems: Microsoft Windows Vista (32-bit) Microsoft Windows Windows 7 (32-bit) Microsoft Windows XP (SP3) Microsoft Windows 2008 The 64-bit Edition can only be installed on: Microsoft Windows Vista 64 Microsoft Windows Windows 7 (64-bit) Microsoft Windows 2008 R2 (64-bit)
Microsoft Windows: Intel x86 or x86-64
All platforms: JDK 6
Hard disk space
44 MB for compact installation56 MB for full installation (includes PDF help files)
For both the 32-bit and 64-bit editions, if you are still using dialect 1, you must migrate to dialect 3. Performance monitor tables will not work in the counters and will generate errors. For more information please reference Understanding SQL dialects in the Operations Guide.
The ib_install.exe is delivered with the 32-bit Edition issue. You will need to run this installer if you want to to use the 32-bit Edition on Windows.
The topics below cover the critical information you need to implement the 64-bit application.
Client Library Name Changes for 64-Bit DLL's The following table displays the new client library names for the 64-bit DLL's.
This is a new InterBase client DLL for native 64-bit applications. It is being used by 64-bit InterBase command-line tools currently, and will need to be deployed with 64-bit applications built by customers (in place of gds32.dll which is for the 32-bit target).
This is a new InterBase XML DLL for native 64-bit applications. It will need to be deployed with 64-bit applications built by customers (in place of ibxml.dll which is for the 32-bit target) if they are using the InterBase XML api.
This is a new InterBase UTILS DLL for native 64-bit applications. It will need to be deployed with 64-bit applications built by customers (in place of ib_util.dll which is for the 32-bit target).
Import library for building 64-bit applications targeting the ibclient64.dll.
Import library for building 64-bit applications targeting the ibxml64.dll.
Import library for building 64-bit applications targeting the ib_util64.dll.
New interfaces have been implemented which support the following:
For all API the parameter pos is ignored and assumed to be 1, hence the complete BLOB is returned. For example in the method OutputStream setBinaryStream(long pos) the parameter pos is ignored. The same is true for all other methods which take the "pos" parameter.public long position(byte pattern, long start) and public long position (java.sql.Blob blob, long start) are not supported.
Special implementation of the java.io.inputStream for InterBase Blob (and Clobs). Use the read() methods from this stream to access the underlying data.
The following methods have been implemented in this release: In the java.sql.PreparedStatement class
In the java.sql.Result class
This release implements stronger password protection on InterBase databases to comply with password requirements from the Payment Card Industry - Data Security Standard (PCI DSS). This additional functionality supports a longer effective password length, resulting in stronger password protection.
Getting StartedThe DES-CRYPT password algorithm has been replaced with a modern cryptographic hash function that is more widely accepted by organizations in private industry and government. The design uses SHA-1, which generates a fixed length 160-bit hash.
1. Before starting, it is strongly recommended that you backup your old admin.ib from the current installation before installing the new InterBase. This allows you to restore it, if needed.2. If you are upgrading to a newer version you may want to continue using your admin.ib from an earlier InterBase. If so, enable stronger password protection on that admin.ib by using the following SQL commands after IB has been installed on the server:
isql admin.ib -user SYSDBA -pass xxxxxxxsql> ALTER DATABASE SET PASSWORD DIGEST 'SHA-1';sql> CREATE DOMAIN PASSWORD_DIGEST AS VARCHAR(16) CHARACTER SET ASCII;sql> ALTER TABLE USERS ADD PASSWORD_DIGEST PASSWORD_DIGEST;sql> UPDATE USERS SET PASSWORD_DIGEST = 'DES-CRYPT';sql> COMMIT;
NOTE: If it is a completely new installation of InterBase you do not need to run the SQL commands.The ALTER DATABASE command can only be run by the database owner or SYSDBA. This command modifies RDB$DATABASE.RDB$PASSWORD_DIGEST to the string value "SHA-1". This means that all new password hash generation for new or existing user accounts in the USERS table will use the SHA-1 hash function.The password hash function can be reset to DES-CRYPT using the same DDL:ALTER DATABASE SET PASSWORD DIGEST 'DES-CRYPT';The admin database is now prepped so that new user accounts or modifying the password of existing accounts will generate SHA-1 password hashes against plaintext passwords up to an untruncated length of 32 significant bytes.
GSEC [add | modify], IBConsole, and the IB Services API support the SHA-1 password hash algorithm. Any of these tools can be used to maintain the passwords of server-wide user accounts. If an existing user account has had its password changed then that user must log in to the server using the new IB client library.
Caution: There will be backward compatibility problems if the converted admin.ib database is backed up and restored by an older IB engine after the password hashes have been converted to SHA-1. Older IB engines will not understand the different password hashes and will cause unrecoverable login errors.
There is now a larger database cache setting for 64-bit InterBase. The limit for the 64-bit engine is 75 million pages, as compared to 750K pages for 32-bit engines.
InterBase XE now provides support for the EXECUTE STATEMENT functionality.
User Interface/UsabilityThis feature enhances the InterBase Stored Procedure language. Once this is implemented, Stored Procedure developers can embed three variations of EXECUTE STATEMENT within their Stored Procedures. The variations depend on the number of rows returned from the EXECUTE STATEMENT command. The three cases are: No rows or data returned; One row of data returned; and Variable number of rows returned.
No Rows or Data Returned
EXECUTE STATEMENT <statement>
<statement> ::= a SQL statement returning no rows of data
CREATE PROCEDURE EXEC_STMT_NO_RET (proc_name varchar(20))
ASDECLARE VARIABLE EMPNO INT;
DECLARE VARIABLE EXECSTMT;
SELECT MAX(EMP_NO) from EMPLOYEE into EMPNO;
EXECSTMT = 'EXECUTE PROCEDURE' || proc_name || '( ' || cast (EMPNO as varchar(10)) || ')';
EXECUTE STATEMENT EXECSTMT;
One row of data returned
EXECUTE STATEMENT <select-statement> INTO :<var>[, :<var>..]
<select-statement>::= SQL statement returning one or no rows of data
<var> ::= valid procedure variable, the ":" is optional.
CREATE PROCEDURE EXEC_STMT_SINGLETON (TABLE_NAME VARCHAR(50))
DECLARE VARIABLE MAXEMPNO
EXECUTE STATEMENT 'SELECT MAX(EMP_NO) FROM' || TABLE_NAME INTO :MAXEMPNO;
Any number of data rows returned
FOR EXECUTE STATEMENT <select-statement> INTO :<var> [, :<var> ..]
<var> ::= valid procedure variable, the ":" is optional.
CREATE PROCEDURE EXEC_STMT_ANY (TABLE_NAME VARCHAR(50), INT_FIELD INTEGER)
ASDECLARE VARIABLE IFIELD INTEGER
FOR EXECUTE STATEMENT
'SELECT' || INT_FIELD || 'FROM' || TABLE_NAME INTO :IFIELD
IF (IFIELD = 0) THEN
INT_RETVAR = INT_RETVAR+IFIELD;
New Error Messages Added The following table displays the new error messages added:
EXECUTE STATEMENT could not prepare statement : <string>
SQL statement invalid as it returns no records. SQL : <string>
Parameter mis-match for the statement : <string>
Could not execute statement : <string>
EXECUTE STATEMENT fetch error
EXECUTE STATEMENT in this form must return single row, not multiple rows.
Sql statement not allowed in EXECUTE STATEMENT : <string>
Requirements/ConstraintsThere are constraints and peculiarities with using EXECUTE STATEMENTS:
Migration Issues This feature is only available in InterBase XE and is not backward compatible.
Sweeping a database is a systematic way of removing outdated records. Periodic sweeping prevents a database from growing too large. In the past sweeping slowed system performance and users disabled the automatic database sweep function because of the impact on product operations.InterBase databases periodically need to be swept. Otherwise the main memory allocated for each transaction's bitmap increases to the point where performance becomes unacceptable. The longer sweep takes to complete, the more main memory requirements increase for starting new transactions.With the implementation of the fast sweep optimization in InterBase XE, the memory allocation issue has been mitigated. The user has the option to configure their databases for automatic sweep. In cases where large databases have large archival or infrequently modified tables, a database sweep will have minimal impact on the performance of running transactional operations.
User Interface/UsabilityThere is no new user interface or action required by the user to enable this functionality. Manual sweep initiated by the GFIX command line tool, IBConsole, or programmatically, as well as automatic sweep configuration on a database, use the fast sweep mechanism.
Requirements and Constraints
Migration IssuesA database needs to be created or backed up and then restored to ODS15.
The term blocking factor is used to denote the number of records stored in a block. InterBase employs a single database-wide blocking factor that maximizes the number of rows that can be stored on a data page. In InterBase XE, ODS 15 databases introduce table-specific blocking factors that optimize how many rows can be stored on a data page and minimizes the row number assigned to a row. In addition, the engine minimizes the size of a number of run-time data structures. This is especially important as tables storing a very large number of rows and accessed by a large number of database connections can cause excessive memory consumption.
User BenefitDatabases with tables containing a large number of rows or is expected to grow to a large number of rows benefit from table-specific blocking factors. It allows those tables to store more rows in the same record number space, while using less memory for run-time retrieval data structures.
User Interface/UsabilityThere is no user interface or any action required by the user to enable this functionality. The table-specific blocking factors are set automatically after a database restore.The blocking factor values for individual tables can be observed in the system columns:
Requirements and Constraints
Migration IssuesIf an ODS 15 database has been created and loaded with data, taking a backup and restoring will set the table-specific blocking factors.
With the ODS 15 databases the maximum index key size limit is increased. Now larger column data can use this for both single-byte character sets and multi-byte (such as UTF8) columns.Because InterBase XE supports UTF8 and multiple other multi-byte character sets, the limit has been increased. For example, a single-column key using 4-byte UTF8 character would calculate to 1020/4 = 254 UTF8 characters with a 4KB page size.ODS 15 databases automatically allow index definitions where the underlying key size is now a factor of the database page size.
Requirements and ConstraintsDatabases created with engines enabled with this functionality cannot be moved back to older versions of InterBase.Also a database restore to a smaller page size will fail if indexes with a large key size cannot fit within limit specified above.No user interface or actions are required by the user to enable this functionality. Each time a database restore is performed, the indices are recreated.
Migration IssuesOnly ODS 15 and later databases have support for larger index keys. If you want to use this facility, restore your database to ODS 15. Other indices that use a smaller size than 252 bytes continue to have the same on-disk storage without any penalty.
Run the executable ib_install.exe (for 32-bit) and ib64_install.exe (for 64-bit), and then choose one of the following:
Server and ClientAfter you choose an install location; this will replace the binaries in the existing InterBase installation. Make sure to stop the InterBase Server before installing.
Client only This will install all of InterBase command line tools as well as the IBConsole.exe and the InterBase JDBC Driver.
Extract Files only This will extract the files in a single directory to be installed at a later time by the user.
The following are resolved defects for InterBase XE. Additional bug fixes are listed in the Release Notes.pdf.
Internal Defect #
External Defect # (QC)
InterBase XE Update: Sept-2010, Windows
RAID 279996: An error occurs when trying to create or change a password that begins with a question mark.
NullPointerException (instead of an sql error message) thrown when losing the network connection and trying to run a query.
RAID 273825: getMetaData(). getPrecision() returning wrong value.
RAID 251980: JOIN of 2 stored procedures give "arithmetic exception" error.
RAID 278498: Increase request impure space size from the current 256K limit to a larger value.
RAID 260612: QLI crashes as soon as "SHOW FUNCTIONS" command is given.
RAID 260671: OnlineDump with multiple files.
RAID 260629: Inserting from VARCHAR field in one table to BLOB in another table inserts additional junk in the target table.
no T privilege with grant option on table/view TableName.
RAID 146832: ORDER BY using an index on a DATE column gives wrong order.
RAID 279571: Non-SYSDBA db owner SELECT fails even with GRANTED rights on the table.
RAID 273806: Optimizer regression in JOINS since InterBase 2007 Service Pack changes.
COALESCE and aggregate functions.
IndexOutOfBoundsException thrown when calling PreparedStatement executeBatch().
Unexpected behavior of database linger.
Parameterized query params order in SQLDA/SQLVAR not the same as visual order of question marks.
Possible Optimizer regression with fixes in the InterBase 2007 SP3.
Create Database from JDBC application does not respect character set requested.
RAID 272543: RDB$FILTERS does not have a unique index on filter name leading to duplicate filters.
RAID 270024: InterBase cannot use 2GB+ database cache even if Large Address Aware flag was set.
RAID 271600: "Request Synchronization error" reported on 8-core system with heavy activity.
RAID 270959: Accessing ODS 10.x database leads to server crash.
RAID 269627: Unique superkey license files belonging to the same "addon" package are all not loaded by the licensing engine.
When you open the Properties form for a table or procedure and select the Permissions tab, the column headers rarely appear. If you select another tab and then come back to the Permissions tab, the column headers will be there.
I add a UDF with a param of type DATE, the UDF is created, but when I try to show the property of the UDF, IBConsole shows the Message: 'unsupported Datatype' The UDF cannot be dropped in IBConsole. The UDF works fine in ISQL, and can be dropped in ISQL This happend also with TIMESTAMP and TIME.
Create a table in an InterBase database and fill it with data. Then export the table to a Comma Seperated Value file using IBConsole and the export works fine. Check the exported file using MS Excel and it appears to be fine. Then empty the table. But when I try and import data from the .csv file that I exported the table to, I get the following message: Access Violation at address xxxxxxxx in module 'IBConsole.exe' Read of address 000000000. This is the case with any .CSV file that I try to import data from.
Database Restore Fails: "No current record for fetch operation".
DB Restore can't be done on IBconsole.
The name of the directory that contains the IBConsole.xml file is misspelled.
When trying to change the data type for a domain, an error appears.
Metadata names longer than 22 characters are truncated.
When trying to connect as a different user from the InteractiveSQL, the connection window opens but it doesn't connect as a different user and an error message appears.
When disconnecting from a database using the InteractiveSQL then trying to reconnect to the database, an error message appears.
When using the IBConsole for creating a journal, a journal archive, and to archive the database, the Archive Database option returns an error message (this fails for all types of connections.
When creating a journal, a journal archive and a database archive for a database, using Interactive SQL, the 'Archive Database' option doesn't refresh after disconnecting from the database and then reconnecting to the database.
IBConsole Open Table, Error.
Interactive SQL in IBConsole: truncated RDB$ names.
When creating a database then a generator using Interactive SQL, an error appears. You then have to use Task Manager to terminate the process.
When using a field that can be null in the Primary Key constraint definition, then removing it from there, an error appears.
When creating an external function with an entry point larger than 67 chars, an unclear error message appears.
When altering a view to add a second column, the table name is added incorrectly.
When altering a trigger by adding a local variable with the same name as another variable, then changing it back to the way it was, the changes cannot be made.
When choosing a value larger than 10 digits for the input type of a blob filter, an error message appears and the Blob Filter Editor window closes.
When choosing the 'New Connection' option from the Tools menu to connect to a different database then what is running a query, the IBConsole shuts down.
When trying to see the License Manager from the Tools menu of the IBConsole, an error message appears.
When trying to create a blob filter with a duplicate name, the Cancel button doesn’t work.
When creating a database with a name that already exists, the Cancel button doesn't work.
Text cannot be copied inside the name area of procedures and triggers, even if the focus is inside the name area; the text is added inside the body area.
When using the IBConsole to encrypt a database, then deleting the encryption from the system table of encryptions and changing the value of EUA, an AV error message appears.
When performing a database sweep using the IBConsole, the text of the confirmation message isn't correctly aligned.
For InterBase XE, License Manager from the Tools menu of the IBConsole doesn't open, and an AV error message is shown instead.
When using the IBConsole to run a couple of queries without committing, disconnecting the database creates an AV error message.
When connecting to a database as sysdso using IBConsole and trying to view data from a table that the user has select permission on, an error message appears.
EUA_USER and EUA_PASSWORD options must be added to Database Restore.
IBConsole should be using the new camel-cased and case-sensitive OTW parameter names (for IB OpenSSL clients).
SELECT COUNT(*) and SELECT AVG should return a SQL_INT64 type. If there is any code in IBConsole that uses 32-bit integer type local variable to receive this value, it should be changed to a 64-bit integer type.
IBConsole requires full path to backup file.
Documentation Updates: Sept-2010, Windows
Language Reference Guide: Chapter 3: WHEN...DO. Updated GDSCODE errcode. Also updated Table 5.5 with correct GDSCODE usage.
Operation Guide: Appendix B: Table B.1 InterBase Specifications. Updated maximum size of external table file.
Developer’s Guide: Chapter 14 - Applying Cached Updates with a Dataset Component Method. Replaced old procedure example with a new IBTransaction1 example.
Language Reference Guide: Chapter 6 - Table 6.34. Added table RDB$USERS.
Embedded SQL Guide: Chapter 13- Topic: SQL Datatype Macro Constants. Updated information on sqlsubtype settings.
Developer’s Guide: Chapter 6 - Table 6.7. Updated integer information.
API Guide: Chapter 3 - Environmental Variables Used by Interbase. Added two new variables: IB_PROTOCOL and INTERBASE.
Language Reference Guide: Chapter 6 - Topic: RDB$DATABASE. Added missing fields to table 6.1.
Language Reference Guide: Chapter 2 - ALTER TABLE, ALTER DATABASE, CREATE ENCRYPTION, CREATE TABLE. Added note to each topic: For detailed information on encryption and decryption, see the topics “Encrypting Data”(page 13-9) and “Decrypting Data” (page 13-11) in the Data Definition Guide.
Operation Guide: Chapter 4 - Topic Using ibmgr to Start and Stop the Server. Instructions for starting InterBase under the interbase user account have been updated.
API Guide: Chapter 5. Added new topic: Use of Commit/Rollback with Multidatabase Transaction.
Developers Guide: Chapter 6: Explanation of dsc_flags. Added DSC-systems information to Table 6.5.
Updated installation information. Desktop and ToGo setups are only available for Windows O.S.
Embedded SQL Guide: Chapter 6 - Topic Using Comparison Operators in Expressions. Added Table 6.4 listing comparison operators in expressions.
Developer’s Guide: Accessing InterClient extensions to the JDBC. Added a suitable example for how to cast the JDBC driver object.
isc_dpb_archive_journals and isc_dpb_archive_database not listed in documentation.
Need the new Keywords introduced in InterBase 7.5 and above.
RAID Issue 112111. Operation Guide: Chapter 11 - Topic Edit. On Windows platforms, EDIT calls the text editor specified by the EDITOR environment variable. If this environment variable is not defined, then EDIT uses the Microsoft Notepad editor.
RAID Issue 251988.Operation Guide: Chapter 9 - Journaling Tips and Best Practices. Updated CHECKPOINT LENGTH information.
RAID Issue 218084. Language Reference Guide: Chapter 6. TMP$STATE field not documented in description for TMP$DATABASE.
RAID Issue 263959. Data Definition Guide: Chapter 13. gbak examples on page 13-17 have invalid syntax.
RAID Issue 238178. Language Reference Guide. Chapter 2. The SQL example for the CASE statement won't work because it is truncated.
RAID Issue 194123. Language Reference Guide. Chapter 2. Yearday for extract is listed as returning values 1-366. It should say 0-365. Hour and minute are similarly wrong. They are listed as going from 1-23 and 1-59. They should list 0-23 and 0-59.
RAID Issue 256520. API Guide. Chapter 15.Signature for "isc_dsql_batch_execute()" incorrectly includes "isc_db_handle *db_handle". Signature for "isc_dsql_batch_execute()" incorrectly includes declaration using "ULONTSignature for "isc_dsql_batch_execute()" incorrectly includes declaration "int dialect" when actually the argument is implemented as an "unsigned short" in ibase.h
RAID Issue 240455. API Guide. Chapter 15. isc_string_too_large error is generated when SQL is greater than 64K. 64K should be changed to 2 GB.
RAID Issue 218053. Language Reference Guide. Chapter 2. ALTER DATABASE describes it as "Adds secondary files to the current database." This does not consider other new actions that can now be taken. Changed to "Changes the characteristics of a database".
RAID Issue 120748. API Guide. Chapter 3. Remove ISC_DATABASE from Table 3.1. It is not implemented any longer.
RAID Issue 106998.Operations Guide. Chapter 7. Error “OBJECT database_name IS IN USE” is incorrect.
RAID Issue 192587. Data Definition Guide. Chapter 8. Documentation says you can’t use UNION when creating a VIEW in DSQL. This hasn’t been true since IB 5.6.
RAID Issue 276427. Data Definition Guide. Chapter 14. UTF_8 Maximum character size is changed from 1 byte to VARCHAR(63).
Copyright © 2010 Embarcadero Technologies Inc. All CodeGear brand and product names are trademarks or registered trademarks of Embarcadero Technologies in the United States and other countries. All other marks are the property of their respective owners.
Trial Download Week!
Click here to download a free 30-day trial of InterBase XE3 Server or ToGo editions. Or download a free non-expiring Developer Edition >
More InterBase Info
InterBase XE3 Product Info
Free Developer Edition download
InterBase on Google+
Follow @InterBase_ on Twitter
Server Response from: ETNASC01