===================================================================== ======================== README FILE InterBase 4.0 For The Local InterBase Server ======================== This file contains important information not in the printed InterBase documents and includes restrictions and known problems. This README file covers the following topics: --1-- General Issues --2-- Server Manager --3-- Windows/ISQL --4-- International --5-- InterBase Document Updates --6-- InterBase ODBC Driver --1-- General Issues ====================== * InterBase Location The Local InterBase Server installation determines where InterBase is installed using the entry in the WIN.INI file. If you previously had the InterBase Windows Client installed, make sure that the installation removes the "SET INTERBASE=" line from your AUTOEXEC.BAT file. * Registration Local InterBase Server included with Delphi and with Delphi Client/Server provides you with a useful platform for prototyping and developing client/server applications. There are strict limitations in redistributing components of Local InterBase Server. Please read DEPLOY.TXT for a complete list of the deployable files. If you use Local InterBase Server extensively, you will be interested in information on samples and future product enhancements. To receive the information you must register your copy of Delphi Client/Server with Borland. Refer to the 'Three Good Reasons' card. * InterBase Print or Online Document Set The online document set for Local InterBase Server includes: * The Local InterBase Server User's Guide - introduces single-user InterBase and explains the use of the fundamental elements of database access. * The InterBase Workgroup Server Data Definition Guide - explains how to create, alter, and delete database objects with ISQL. * The InterBase Workgroup Server Language Reference - Describes SQL syntax and usage. Not all of the features described in the Data Definition Guide and the Language Reference are implemented in local InterBase Server. Refer to the list below for a list of those features that are not supported. * Remote Configuration For information about remote configuration of your Local InterBase Server, refer to the InterBase Client for Windows User's Guide which comes with your InterBase Workgroup Server documentation set. Chapter Two provides a detailed section on troubleshooting and network configuration. * Unsupported Features Local InterBase Server does not support the following: - User Defined Functions - BLOB Filters - event alerters - Write Ahead Log (WAL) - Array Datatype - database shutdown or restart - database shadowing * On-Disk Structure The Local InterBase Server and InterBase for NT share the same database on-disk structure. Databases on either of those platforms can be copied from one environment to another (when no one is accessing the database). Compatibility with NetWare 4.0 is planned for a future release. * Query Control The Local InterBase Server uses a single-user model. Each query gets exclusive control of the engine until that query is finished. Be careful with long-running queries. * Sweeping the Database InterBase uses a technique of sweeping the database to remove old copies of records from the database. By default, at the end of 20,000 transactions in a database a sweep will be done for you. The cursor indicating a sweep is in progress is an hourglass labeled "SWEEP". To alter the sweep interval, use Server Manager's Database Maintenance option. During a sweep, your computer will be busy. To preserve integrity, the database will be inaccessible until the sweep is completed. * Access to Mapped Drives on Remote Machines Local InterBase Server is designed to provide a local database application development environment for a single user. Some users might want to store large databases on mapped (remote) drives. Access to mapped remote drives is possible, but not supported. When the Local InterBase Server opens a database on a mapped drive, it opens that database with an exclusive lock, preventing access by others on the network. Instead of attaching to the remote database directly from Local InterBase Server, attach to the remote database through the remote InterBase server (e.g. InterBase for Windows NT). Make sure to specify the remote InterBase server name in the path (i.e., :). Specifying the server name will cause the remote InterBase server to treat the Local InterBase Server user just like a normal networked user in the multi-user environment, with the standard multi-user access, capabilities and privileges. * Remote Events Events from a remote InterBase server through the Local InterBase Server can cause exhaustion of global selectors when the application is repeatedly waiting on events and the application is long-running. * Dropping Tables Before attempting to DROP a table, first issue an explicit COMMIT; statement. Some operations, such as a SELECT, will set an 'in use' flag that will not be cleared until a COMMIT is done. If you try to drop a table and get an 'object is in use' error message, issue an explicit COMMIT; and try the drop again. * Decimal Representations Internally, InterBase represents decimal numbers as data type SHORT with a scale factor, or as a type LONG with a scale factor, if the specified precision is small enough to fit in these datatypes. Embedded SQL applications need to adjust decimal numbers of 9 digits or less by taking the scale factor into account (that is, divide the number by 10 to the exponent of scale factor). For example, for the three following decimal representations: a(4,2) b(9,3) c(15,2) short/100 long/1000 double/100 select a, b, c into :a, :b, :c from tablex print (a/10**2, b/10**3, c/10**2) * Local Variables In DSQL, CHAR and VARCHAR columns fetched from a table are not NULL terminated. In C and C++ applications, be sure that local variables are declared with an extra byte of storage for a NULL terminator, and that the application also inserts a NULL ('\0') in the final byte. * Character Columns In DSQL, CHAR columns are interpreted by PREPARE and DESCRIBE as SQL_TEXT + 1. * Text Representations In DSQL, when casting non-text data into a text representation, use the SQL cast operator in the DSQL statements rather than setting SQL type in the SQLDA. For example, rather than using: SELECT date . . . instead use: SELECT cast (date as character(20)) . . . . * File Name Conventions Local InterBase Server cannot access databases with names that do not conform to the 8.3 file naming convention. If a file has in its pathname a name longer than 8 characters (and a 3-character extension) the attachment will fail. * Maximum Size of a Data Element Local InterBase Server cannot store an array or a BLOB of size greater than 64 Kbytes. * Maximum Number of Columns in a Table The maximum number of columns in a table is limited by memory. Local InterBase Server users might experience difficulty accessing tables with more than 1000 columns. * Maxiumum Size of Key in a Sort clause For queries that include a sorting clause (sort by, distinct, order by, group by) the maximum size of the Local InterBase Server sort key is 32K. The size of the key equals the sum of the sizes of the columns included in the sorting clause. For example, sorting on two char fields that are each 17K wide will not work properly. * Maximum Database Page Size The Local InterBase Server has a maximum database page size of 1024 bytes. This size is also the default for database creation. Accessing a database with page size greater than 1024 may result in unexpected behavior. * Database Access When attempting to access a database using ODBC through IDAPI, make sure to include a non-null user name and password. * Restoring Databases User Defined Functions (UDFs) are not supported by Local InterBase Server. When you restore a Version 3.x database that uses UDFs, make sure that the UDF library is not present. * Floating Point Overflows Applications that cause a floating point exception may experience unexpected behavior. * Sample C Programs The Sample C application programs are not included in Local InterBase Server. --2-- Server Manager ====================== * Starting Server Manager When it starts, the server manager checks for a local database engine. If a local engine is present, the server manager adds an entry to the server/database tree for the "InterBase Local Server". The user can then begin managing the server and attaching to local databases. The user can choose File/Login (and specify a user name and password) to connect to a remote server. If the server manager does not detect a local engine, it will start in its remote configuration with an empty server/database tree. The user needs to log in to a remote server before attaching to a database. * Security Local InterBase Server does not require a user name and password. If the user passes a null user name and null password to the local engine, the engine will not check the security database (isc4.gdb). If the user passes a user name and password, they will be validated against the security database and access to the database will fail if they are not found. The Local InterBase Server includes the InterBase security features for compatibility and development testing purposes. It is up to the application developer to determine whether access is restricted. If a password is required by the application, Local InterBase Server will check that password against the security database. * Environment Variables The environment variables ISC_USER and ISC_PASSWORD will, if they are set, be used as the default user name and password by Server Manager. If you use these variables, make sure that the user defined by them actually exists in the local security database, ISC4.GDB. Add them to ISC4.GDB using Server Manager. If they are not in ISC4.GDB, you might get an error message that your user name and password are undefined. Use of these variables is not recommended because an unauthorized user can find out the definitions and possibly use that information to circumvent database security features. To remove the environment variables, exit Windows, reset both variables to zero, then restart Windows. * Unsupported Features The following features are not supported by Local InterBase Server and will appear grayed-out on the server manager menus: Main Window: Tasks/Lock Manager Statistics Tasks/Database statistics Tasks/Disable WAL Database Maintenance window: Database/Shutdown Database/Restart Database/Properties: The Enable Forced Writes check box in the Properties dialog box will be grayed-out. * Verbose Output Messages For backup/restore, the output text window is limited to 32K. If you choose the verbose option on a large database, you may see a dialog box stating the buffer space was exceeded and prompting you to save the results to a file. If you choose 'yes', all future results will be saved to the specified file and the text in the window will scroll off the top. If you choose 'no', the backup will still complete, but the verbose output will stop. * Slash Character When issuing a connect, backup, or restore to a database on a Unix server, be sure to always start the pathname with the slash character (/). * Backup File Default Location The Server Manager backup utility places the backup file in a specific default directory. On an NT server, the default directory for the backup file is WINNT\SYSTEM32. On a NetWare server the default directory for the backup file is the SYS volume's root directory. If you want the backup file to be written in a different directory, specify the complete pathname when the Server Manager prompts for the Backup File or Device. * Dashboard compatibility Dashboard users may experience difficulties when using Server Manager's User Security features. If you encounter problems, exit Dashboard before starting Server Manager. --3-- Windows/ISQL ==================== * Windows/ISQL, through the Local InterBase Server, allows access to remote and local servers. * Starting WISQL When it starts, WISQL checks for a local database engine. If a local engine is present, the "Local Server" radio button will be enabled in the "Connect to Database" dialog box. The user can then connect to a local database by specifying the optional user name and password If WISQL does not detect a local engine, the "Local Server" radio button will be disabled. Any database connection will be remote, requiring a username and password. * The SQL Output area is limited to 32 KBytes of output. Output that exceeds this amount will be appended to the end of the buffer, and the initial lines in the buffer will scroll off. For queries which return a large amount of data, the cursor will become an hourglass, and when the query completes, the last 32K of output will be displayed in the Output Area. To see results that exceed 32K, click on "Save to File" and view the file. * Maximum row size (size of each field, plus 1 byte per field) is limited to 64K bytes. On a Windows Client, ISQL requests that have NULL indicator fields require a smaller maximum row size, because each NULL indicator field adds an additional 2 bytes to retrieval requirements. --4-- International ===================== * The Local InterBase Server international feature set is significantly different from that used in previous InterBase implementations. A Version 3.3 database which used international subtypes must be backed up using that version's GBAK utility and restored using the GBAK from Local InterBase Server. * If a database is created using CHARACTER SET and COLLATE clauses with the NOT NULL attribute, and the schema is then extracted using ISQL -a, the extracted schema will contain a syntax error. This must be corrected before the schema file can be run using ISQL -i to recreate the database. * An international version of the example employee database has been added to the example/v4 directory. This example database, named 'intlemp.gdb', uses the international character set ISO8859_1 to define some character and blob fields. The database also has stored procedures which sort data. There are no associated example programs. * When setting up a Delphi alias for the intlemp.gdb database using the BDE (SQL Links), set the "Language Driver" on the Alias screen to BLLT1FR0. This tells both Delphi and InterBase to expect data in the Latin 1 character set. The driver uses French collation, but any Latin 1 driver can be used. * When viewing international data from the Windows SQL tool or from Delphi, an international language driver should be used. If data display problems occur, check the language driver to be sure that the character set for the driver matches the character set of the database data. In Windows SQL the use of the language driver is controlled by the Advanced features pull-down menu and the Character Set On selection list. This feature executes a SET NAMES command. You must re-connect to the database after using this feature. When using Delphi, the database alias must be set up to use the appropriate language driver by using the Database Desktop or the Alias page of the BDE configuration tool. * SQL II character count semantics for column sizes are not enforced for double-byte characters. Defining a field as CHAR(3) CHARACTER SET EUCJ_0208 (double-byte) should allow only 3 characters to be stored (in 6 bytes of internal storage). Currently, if the data inserted is single-byte, up to 6 single byte characters are accepted into the field before an error is produced. (6180) * The OCTETS character set is not a valid parameter to SET NAMES in ESQL and it is not available as a character set choice in Windows ISQL's Session/Advanced Settings menu. --5-- InterBase Document Updates ================================== InterBase Language Reference ---------------------------- The reserved word 'DISPLAY' is not included in the list of keywords in Appendix A of the InterBase Language Reference. Local InterBase Server User's Guide ----------------------------------- The dialog box that controls connecting to a database differs from the explanation present in the User's Guide. The User's Guide does not show the Local and Remote server buttons (Page 40). If those buttons are present, refer to the on-line help for information. Similarly, the discussion of creating a database should mention that a database can be created on a remote server as well as the local server (Page 40 and also Page 73). User Name and password are required for connection to a remote database server. Refer to the on-line help for information on connecting to remote databases. The discussion on the Server/Database Tree should mention both local and remote databases. When Server Manager is used with Local InterBase Server, only local branches of the tree are accessible (Page 139). After a connection is made to a remote server, the databases on the remote server are shown in Server Manager windows. In Appendix C of the Local InterBase User's Guide (Page 189), the first sentence of the second paragraph states, "The database (EMPLOYEE.GDB) is created with the data definition file, CREATEDB.SQL". That statement is no longer correct. The EMPLOYEE.GDB database is already built and included with Local InterBase Server. The user does not have to build it. --6-- InterBase ODBC Driver ============================= For more on-line help about the InterBase ODBC driver, start the ODBC administrator and choose InterBase (Borland InterBase) SETUP | HELP. 6.1 InterBase ODBC 1.0 ---------------------- * ODBC defines conformance levels for drivers in two areas: 1. the ODBC API 2. the ODBC SQL Grammar * The InterBase ODBC driver is ODBC 1.0 compliant. It supports API Level 1 and Core SQL Grammar. * To work with this ODBC driver, you need an ODBC application such as Borland ReportSmith, Microsoft Access, Visual Basic, or other commercially available ODBC applications. * If you are writing your own ODBC application, refer to Microsoft ODBC SDK for information on how to make direct calls to an ODBC driver. 6.2 Installation ---------------- * The Local InterBase Server install procedure will install the InterBase ODBC driver. 6.3 ODBC Administrator ---------------------- * If you have an ODBC administrator installed, you can use it to setup new data sources. * You need to add this line to your CONTROL.INI file in the [MMCPL] section: [MMCPL] ODBC=C:\WINDOWS\SYSTEM\ODBCINST.DLL * The InterBase ODBC driver should appear in the driver list box when you invoke ODBC Administrator. * The ODBC Administrator is used to create and manage data sources. Each data source is identified by a unique name. InterBase data source appear in the upper list. The lower list contains the name(s) of ODBC driver(s). 6.4 ODBC Data Sources --------------------- * New data sources are created by selecting InterBase (Borland InterBase) ODBC driver from the lower list and choosing the Add New Name button after which additional dialogs will be displayed prompting you for the necessary details. * Existing data sources can be configured or removed by selecting the data source and choosing Setup. For InterBase 4.0, you will need to enter the following: Description: A brief description of your data source (any text). Database Name: / Default Logon ID: For example, using SVR3 NT server with named pipes protocol: Description: InterBase workgroup server (accounting) Database Name: \\SVR3\D:\USERS\FRED\MYDB.GDB 6.5 ODBCINST.INI File --------------------- * The install adds these lines to your ODBCINST.INI file: [ODBC Drivers] Borland InterBase=Installed * Install adds these lines to the bottom of your ODBCINST.INI file: [Borland InterBase] Driver=C:\WINDOWS\SYSTEM\BLINT04.DLL Setup=C:\WINDOWS\SYSTEM\BLINT04.DLL 6.6 ODBC.INI File ----------------- * The install adds these lines to [ODBC Data Sources] section of your ODBC.INI file: [ODBC Data Sources] InterBase= Borland InterBase [InterBase] Driver=C:\WINDOWS\SYSTEM\BLINT04.DLL * Use the ODBC administrator to add the following lines to your ODBC.INI Description= Database= LogonID= 6.7 Driver Options ------------------ * Driver options are used to provide compatibility with certain ODBC commercial applications. * You can specify options for use with the InterBase ODBC driver by adding the following line to the ODBC.INI file: Workarounds=n where n is an integer that is the sum of all the active options. For example, to use option 1 and 2, add this line to your ODBC.INI file: Workarounds=3 When using options, it is recommended that you create a separate data source for each application by specifying a new source name, description, and database. Option Numbers -------------- * Option 1 If an ODBC driver reports to Microsoft Access 1.1 that its SQL_CURSOR_COMMIT_BEHAVIOR or SQL_CURSOR_ROLLBACK_BEHAVIOR is 0, then Access will open tables read-only. If this option is on, the ODBC driver returns 1, allowing Access to open tables read-write. * Option 2 Some applications cannot handle database qualifiers. If this option is on, the driver reports that qualifiers are not supported. * Option 4 Visual Basic 3.0 sometimes requires two connections to a database. Because some databases support only one connection, the second attempt fails. Turning this option on causes our drivers to detect this condition and have the two ODBC connections share a single physical connection to the database. * Option 16 If an ODBC driver in SQLStatistics reports to Access 1.1 an INDEX_QUALIFIER that contains a period, Access raises a "tablename is not a valid name" error. Turning this option on causes the driver to return no INDEX_QUALIFIER, allowing Access to open the table. 6.8 Microsoft Access and Visual Basic Users ------------------------------------------- * Users of Microsoft Access 1.1 and Visual Basic 3.0 should use: Workarounds=17 * For data sources that support a single connection, use: Workarounds=21 (options 1, 4, and 16). * VISDATA sample application with VB 3.0 will require: Workarounds=29. This allows you to update InterBase tables. 6.9 Limitations and Known Problems ---------------------------------- * InterBase ODBC driver supports 4.0 databases only. * SQL Views are not updatable in Access 2.0 * If you're working with Report Smith/IDAPI and using InterBase ODBC driver, you may encounter problems with licensing. Rename QEBI.LIC in windows/system. The shipping version of ReportSmith with ODBC drivers should not conflict with InterBase ODBC. * When using Access to link external tables, Access establishes a connection to your InterBase server using the user and password you supplied. Any subsequent links to InterBase tables use that same user and password regardless of new user and password supplied in subsequent connections. In order to connect as a different user, you need to exit and invoke Access again. * Access displays garbage if a table contains array data type column. You can work around this by using Access Query or Report and select non-array columns. You cannot create an Access form on a table that contain array columns. If you open the entire table, Access will display #Name? in each column. 6.10 Default transaction mode in InterBase 4.0 ODBC driver ---------------------------------------------------------- * This driver uses NO WAIT READ_COMMITTED RECORD_VERSION mode. For more information, please refer to the SET TRANSACTION in the Language Reference manual. =====================================================================