What is the Borland Local InterBase Server
The Borland Local InterBase Server is a single-user Windows-based version of Borland's InterBase Workgroup Server, an SQL-compliant relational database management system RDBMS . The Local InterBase Server includes Windows ISQL and the Server Manager, a Windows tool that can be used with Local InterBase Server or a remote InterBase server. Using the Local Interbase Server, you can access local databases through Windows ISQL or through a SQL application program. Figure 1-1 shows the relationships...
Using Subqueries
Suppose you want to retrieve a list of employees who work in the same country as a particular employee whose ID is 144. You would first need to find out what country this employee works in. Enter this query SELECT JOB_COUNTRY FROM EMPLOYEE WHERE EMP_NO 144 This query returns USA. With this information, you can form your next query SELECT EMP_NO, LAST_NAME FROM EMPLOYEE WHERE JOB_COUNTRY USA This query returns a list of employees in the USA, the same country as employee number 144. You can...
Inserting Data From an External File
Note This section covers an optional topic and may be skipped without losing any continuity. However, it is an important topic not covered in detail elsewhere in the documentation. An external table is a special kind of table that stores its data in an ASCII file separate from the database. It may occasionally want to import data from an ASCII file into a database for example, if the data was originally entered in another application or at a remote location . You can populate a table with data...
Column NameData Type Default Value and CHECK Constraints 2
MNGR_NO EMPNO SMALLINT Nullable FOREIGN KEY - references EMPLOYEE EMP_NO BUDGET BUDGET DOUBLE PRECISION Nullable CHECK VALUE gt 10000 AND VALUE lt 2000000 PHONE_NO PHONENUMBER VARCHAR 20 Nullable The JOB table contains a record for each job in the company. The three columns JOB_CODE, JOB_GRADE, and JOB_COUNTRY are the primary key that uniquely identifies a job. JOB_COUNTRY references the COUNTRY table, which identifies the currency of each country. Table C-6, JOB Table shows the contents of the...
Long Driver NameShort Driver NameInterBase Subtype
Paradox ascii ascii 0 default , 1, 100, 101 Borland DAN Latin-1 BLLT1DA0 139 Long Driver Name Short Driver Name InterBase Subtype Long Driver Name Short Driver Name InterBase Subtype Note For information on InterBase subtypes that correspond to dBASE language drivers, contact Borland Technical Support. Note For information on InterBase subtypes that correspond to dBASE language drivers, contact Borland Technical Support.
Performing an Immediate Database Sweep
To perform a database sweep, choose Maintenance I Database Sweep from the menu bar in the Database Maintenance window. This operation runs an immediate sweep of the database, releasing space held by records which were rolled back and by out-of-date record versions. Sweeps are also done automatically at a specified interval see Adjusting Database Sweeping, in this chapter. Important Sweeping a database does not require it to be shut down. You can perform sweeping at any time, but it can impact...
Using Aggregate Functions
SQL provides aggregate functions that calculate a single value from a group of values. A group of values is all data in a particular column for a given set of rows, such as the job code listed in all rows of the JOB table. Aggregate functions may be used in a SELECT clause, or anywhere a value is used in a SELECT statement. The following table lists the aggregate functions supported by InterBase Table 9-5 Aggregate Functions Function What It Does AVG value Returns the average value for a group...
Upgrading to a New Ondisk Structure
New major releases of the InterBase server often contain changes to the on-disk structure ODS . If the ODS has changed, and you want to take advantage of any new InterBase features, upgrade your databases to the new ODS. You need not upgrade databases to use a new version of InterBase. The new versions can still access databases created with a previous version, but cannot take advantage of any new InterBase features. To upgrade existing databases to a new ODS, perform the following steps 1....
Testing the InterBase Connection
To test whether you can connect to InterBase successfully, use the InterBase Connection Utility CONNECT.EXE . This utility is stored in the same directory as the BDE files. 1. Choose File I Run from the Program Manager menu bar. The Run dialog box appears. 2. In the Command Line text box, enter the command to run CONNECT. If you installed BDE files in C BDE, the command is C BDE CONNECT.EXE. 3. Choose OK. The InterBase Connect Utility dialog box appears. 4. Enter information in each text box 4....
Connecting to a Data Source Using a Logon Dialog Box
Some ODBC applications display a Logon dialog box when you are connecting to a data source. For InterBase, the dialog box is as follows In this dialog box, do the following 1. Enter the name of the server and database you want to access case-sensitive or click the arrow to the right of the box to select a server name you specified in the Setup dialog box. This must be a full connection string including the server name, the network protocol separator, directory path and database file name, as...
Configuring Data Sources
If you have an ODBC administrator installed on your system, you can configure an InterBase data source as follows 1. Start the ODBC Administrator by double-clicking on the ODBC icon in the Control Panel application in the Main program group. A dialog box with a list of data sources appears. 2. If you are configuring a new data source, click Add. A list of installed drivers appears. Select Borland InterBase, and click OK. If you are configuring an existing data source, select the data source...
Error Message
Could not drop database lt string gt database might be in use Could not open file name lt string gt Could not read from file lt string gt Could not write to file lt string gt Data type n not understood Database format n is too old to restore to Database lt string gt already exists. To replace it, use the -R switch Do not recognize lt string gt attribute n -- continuing Error accessing BLOB column lt string gt -- continuing Error committing metadata for table lt string gt Exiting before...
Grouping Query Results
You can use the optional GROUP BY clause to organize data retrieved from aggregate functions. Each column name that appears in a GROUP BY clause must also appear in the SELECT clause. And each SELECT clause in a query can have only one GROUP BY clause. Suppose you want to display the maximum allowable salary for each job code gg3 and job grade in the United States. Enter this query SELECT JOB_CODE, JOB_GRADE, MAX_SALARY FROM JOB WHERE JOB_COUNTRY USA You should see these results shown in part...
Inner Joins
There are three types of inner joins Equi-joins link rows based on common values or equality relationships in the join columns. Joins that link rows based on comparisons other than equality in the join columns. There is not an officially recognized name for these types of joins, but for simplicity's sake they may be categorized as comparative joins, or non-equi-joins. Reflexive or self-joins, compare values within a column of a single table. To specify a SELECT statement as an inner join, list...
Restore Options
The restore options are shown in check boxes on the right side of the Database Restore dialog box. If a check box has an X inside, then the option is selected. If the box is empty, the option is not selected. Start Page o Page Size 11024 i Replace Existing Database l i Commit Alter Each Table j Restore Without Shadow Deactivate indexes l i Do Not Restore Validity Conditions r Verbose Output The Start Page is the page on which to start the restore. In most cases, this should be left as the...
InterBase Data Type Translations
Certain database operations cause SQL Link to convert data from Paradox or dBASE format to InterBase format. For example, a BDE application that copies or appends data from a local table to an InterBase table causes SQL Link to convert the local data to InterBase format before performing the copy or append operation. Other database operations cause a conversion in the opposite direction, from InterBase format to Paradox or dBASE format. For example, suppose you run a local query against one or...
Using the HAVING Clause
Just as a WHERE clause reduces the number of rows returned by a SELECT clause, the HAVING clause can be used to reduce the number of rows returned by a GROUP BY clause. Like the WHERE clause, a HAVING clause has a search condition. In a HAVING clause, the search condition typically corresponds to an aggregate function used in the SELECT clause. For example, you can modify the previous query to display only the head departments whose total budgets are greater than 2,000,000. Change the query as...
Starting a Transaction With SET TRANSACTION
SET TRANSACTION issued without parameters starts a transaction with the following default behavior READ WRITE WAIT ISOLATION LEVEL SNAPSHOT The following table summarizes these settings Table 3-2 Transaction Default Behavior Parameter Setting Purpose Access Mode READ WRITE Access mode. This transaction can select, insert, update, Lock Resolution WAIT Lock resolution. This transaction waits for locked tables and rows to be released to see if it can then update them before reporting a lock...
Configuring Database Properties
To view and configure database properties, choose Database Properties from the menu bar in the Database Maintenance window or click on the Database Properties SpeedBar button. The Database Properties dialog box will then appear This dialog box contains a Summary Information area that displays properties but does not allow modification of them and a Configuration area that does allow modification of the parameters. User name of the database owner. Secondary file names and sizes. The...
Extracting Metadata
Windows ISQL enables you to extract metadata for the entire database and for a specific table or view. To extract data definition statements metadata from a database to a file, choose Extract SQL Metadata for Database The following dialog box will open If you choose Yes, then another dialog box will open, enabling you to enter the name of the file to which to extract the metadata. If you choose No, then the metadata will be displayed to the ISQL Output area only. If you choose Cancel, then the...
Restoring to Multiple Files
You might want to restore a database to multiple files to distribute it among different disks, which provides more flexibility in allocating system resources. To restore a database to multiple database files, click on the Multi-file button in the Database Restore dialog box. The following dialog box opens To specify the database files to restore to, type the file name in the File Path text field and then type the number of pages for that file in the text field below it. The minimum number of...
Borland Language Drivers for InterBase
The following table lists language drivers available for use with InterBase and their corresponding InterBase subtypes. The language driver you choose must use the same collation sequence as your server, and the same character set as the one your server uses to pass data to your BDE application. The default can be set at either a database or a table level. InterBase supports subtypes for different fields in the same relation. However, rules of a language driver you specify will apply to a...
Executing SQL Statements
In Windows ISQL, you can execute SQL statements Interactively, one statement at a time. From a file containing an SQL script. To execute an SQL statement interactively, type it in the SQL Statement area and choose Run or press Alt U. The statement will be echoed, and up to 32K of the results displayed in the ISQL Output area. Any output beyond 32K will be scrolled out of the ISQL Output Area. Tip You can copy text from other Windows applications such as the Notepad text editor and paste it into...
Error Message 1
Expected volume number m, found volume n Expected XDR record length Failed in put_blr_gen_id Failed in store_blr_gen_id Failed to create database lt string gt Index lt string gt omitted because mof the expected n keys were found Input and output have the same name. Disallowed. Length given for initial file m is less than minimum Missing parameter for the number of bytes to be skipped Multiple sources or destinations specified No table name for data Page size specified n bytes rounded up to n...
Database
Relational databases store all their data in tables. A table is a data structure consisting of an unordered set of horizontal rows, each containing the same number of vertical columns. The intersection of an individual row and column is a field that contains a specific piece of information. Much of the power of relational databases comes from defining the relations among the tables. InterBase stores information about metadata in special tables, called system tables. System tables have...
Administering Security
View the list of authorized users for the server. Modify user information user name, password . Remove users' authorization. To perform any of these tasks, you must log in to the server as SYSDBA with password masterkey and choose Tasks Security The InterBase Security dialog box will then open
Using the String Operator in Search Conditions
The string operator, also referred to as a concatenation operator, II, joins two or more character strings into a single string. Character strings can be constants or HP values retrieved from a column. For example, enter the following SELECT DEPARTMENT, LAST_NAME is the manager FROM DEPARTMENT, EMPLOYEE WHERE MNGR_NO EMP_NO Corporate Headquarters Bender is the manager Sales and Marketing MacDonald is the manager Engineering Nelson is the manager Finance Steadman is the manager Quality Assurance...
Changing Windows ISQL Settings
The Session menu enables you to change ISQL settings for the current session and display information about the database and its metadata. Choose Session I Basic ISQL Settings to open a dialog box displaying all the basic settings that can be toggled on or off Each setting has a corresponding check box. If there is an X in the box, then the setting is on. Otherwise, it is off. Click on the check box or the setting name to toggle the setting. The following table summarizes basic ISQL settings...
Filenaming Conventions
InterBase is available on a wide variety of platforms. In most cases users in a heterogenous networking environment can access their InterBase database files regardless of platform differences between client and server machines if they know the target platform's file naming conventions. Because file-naming conventions differ widely from platform to platform, and because the core InterBase documentation set is the same for each of these platforms, all file names in text and in examples are...
Column NameData Type Default Value and CHECK Constraints 5
PRIMARY KEY CHECK YEAR gt 1993 Data Type, Default Value, and CHECK Constraints CHECK VALUE '000' OR VALUE gt '0' AND VALUE lt '999' OR VALUE IS NULL CHECK VALUE gt 10000 AND VALUE lt 2000000 Table C-12, COUNTRY Table shows the contents of the COUNTRY table. Data Type, Default Value, and CHECK Constraints The SALARY_HISTORY table contains a record for each time an employee's salary changes. It is automatically maintained by the SAVE_SALARY_CHANGE trigger- Table C-13, SALARY_HISTORY Table shows...
ISQL SET Statements
SET Statements are used to configure the ISQL environment from a script file. Changes to the session setting from SET statements in a script affect the session only while the script is running. After a script completes, the session settings prior to running the script will be restored. You cannot enter ISQL SET statements interactively in the SQL Statement area. When using ISQL interactively, perform these same functions with the Session menu items. SET GENERATOR and SET TRANSACTION without a...
Validation Options
You can select three options with Database Validation By default, database validation reports and releases only page structures. When you select the Validate record fragments option, validation reports and releases record structures as well as page structures. By default, validating a database updates it, if necessary. To prevent updating, select the Read-only validation option. A checksum is a page-by-page analysis of data to verify its integrity. A bad checksum means that a database page has...
Using the WHERE Clause
The WHERE clause of the SELECT statement follows the SELECT and FROM clauses. If an ORDER BY clause is used, the WHERE clause must precede it. The WHERE clause tests data to see whether it meets certain conditions, and the SELECT statement only returns the rows that meet the condition. For example, the statement SELECT LAST_NAME, FIRST_NAME, PHONE_EXT FROM EMPLOYEE WHERE LAST_NAME Green returns only rows for which LAST_NAME is Green. The text following the WHERE keyword, in this case is called...
System Requirements
The Local InterBase Server installation program asks if you want to install the InterBase ODBC driver. If you choose to install the InterBase ODBC driver, the installation program copies all necessary DLLs to the WINDOWS SYSTEM directory, and set up Borland InterBase as the driver name and InterBase as the database source. If you attempt to configure a data source and you do not have the INTERBASE directory on your path or the driver DLLs in your WINDOWS SYSTEM directory, the following message...
Set Blobdisplay
Specifies subtype of BLOB data to display. Syntax set blobdisplay n all off Integer specifying the BLOB subtype to display. Use 0 for BLOB data of an unknown subtype use 1 default for BLOB data of a text sub type, and other integer values for other subtypes. Turn off display of BLOB data of all subtypes. Description SET BLOBDISPLAY has the following uses To display BLOB data of a particular subtype, use SET BLOBDISPLAY n. By default, ISQL displays BLOB data of text subtype n 1 . To display BLOB...
Primary Keys and Foreign Keys
A primary key is a column or set of columns that uniquely identifies a row. In practice, every table should have a primary key. In the employee table, EMP_NO should be a primary key for EMPLOYEE because the employee number uniquely identifies an employee and DEPT_NO should be the primary key for DEPARTMENT because it uniquely identifies a department. A foreign key is a column in one table that is the primary key column for another table. Primary key and foreign key constraints are defined with...
Deleting Data
To remove one or more rows of data from a table, use the DELETE statement. A simple DELETE has the following syntax As with UPDATE, the WHERE clause specifies a search condition that determines the rows to delete. Search conditions can be combined or can be formed using a subquery. Caution A WHERE clause is not required in a DELETE statement. If you fail to include a WHERE clause, you will delete all rows in the table. Enter the following statement to delete rows from the EMPLOYEE table for...
Ending a Transaction
When a transaction's tasks are complete, or an error prevents a transaction from completing, the transaction must be ended to set the database to a consistent state. There are two statements that end transactions COMMIT makes a transaction's changes permanent in the database. It signals that a transaction completed all its actions successfully. ROLLBACK undoes a transaction's changes, returning the database to its previous state, before the transaction started. ROLLBACK is typically used when...
Logical Operators
Up until now, the examples presented have included only one search condition. However, you can include any number of search conditions in a WHERE clause by combining them with the logical operators AND or OR. When AND appears between search conditions, both conditions must be true for a row to be retrieved. For example, enter this query SELECT DEPT_NO, LAST_NAME, FIRST_NAME, HIRE_DATE FROM EMPLOYEE WHERE DEPT_NO 623 AND HIRE_DATE gt 01-Jan-1992 The query returns information on employees in...
Using Data Definition Files
To define the rest of the domains in the database, you can use a data definition file. A data definition file also referred to as an ISQL script file contains ISQL statements, and is created with an editor such as Windows Notepad and run by Windows ISQL. Data definition files can be very useful, because you can enter multiple SQL statements with all the tools that a text editor provides, including cut, copy, and paste. This makes repetitive tasks much easier. In practice, most data definition...
A Simple Select Procedure
The first procedure defined in PROCS.SQL is named GET_EMP_PROJ CREATE PROCEDURE GET_EMP_PROJ EMP_NO SMALLINT RETURNS PROJ_ID CHAR 5 AS FOR SELECT PROJ_ID FROM EMPLOYEE_PROJECT WHERE EMP_NO EMP_NO INTO PROJ_ID DO This is a select procedure that takes an employee number as its input parameter EMP_NO, specified in parentheses after the procedure name and returns all the projects to which the employee is assigned PROJ_ID, specified after RETURNS . It uses a FOR SELECT . . . DO statement to retrieve...
Pattern Matching
Besides comparing values, search conditions can also test character strings for a particular pattern. If data is found that matches a given pattern, the row is retrieved. There are a great many pattern matching operators. This section will only discuss some of the most commonly used ones LIKE, STARTING WITH, IS NULL, and BETWEEN. The LIKE operator lets you use wildcard characters in matching text. Wildcard characters are characters that have special meanings when used in a search condition. A...
Set Names
Specifies the active character set to use in database transactions. Name of the active character set. Default NONE. Description SET NAMES specifies the character set to use for subsequent database connections in ISQL. It enables you to override the default character set for a database. To return to using the default character set, use SET NAMES with no argument. Use SET NAMES before connecting to the database whose character set you want to specify. For a complete list of character sets...
Outer Joins
Outer joins produce a result table containing columns from every row in one table and a subset of rows from another table. Outer join syntax is very similar to that of inner joins FROM lt left_table gt LEFT RIGHT FULL OUTER JOIN lt right_table gt ON lt searchcondition gt WHERE lt searchcondition gt However, with outer joins, you need to specify the type of join to perform. There are three possibilities A left outer join retrieves all rows from the left table in a join, and retrieves any rows...
Set Count
Specifies whether to display number of rows retrieved by queries. Turns on display of the rows returned Turns off display of the rows returned Description By default, when a SELECT statement retrieves rows from a query, no message appears to say how many rows were retrieved. Use SET COUNT ON to change the default behavior and display the message. To restore the default behavior, use SET COUNT OFF. Tip The ON and OFF keywords are optional. If they are omitted, SET COUNT switches from one mode to...
Using CAST to Convert Data Types
Normally, only similar data types can be compared in search conditions, but you can work around this by using CAST . Use the CAST function in search conditions to translate one data type into another. The syntax for CAST is CAST lt value gt NULL AS datatype For example, the following WHERE clause uses CAST to translate a CHAR data type, INTERVIEW_DATE, to a DATE data type. This conversion lets you compare INTERVIEW_DATE to another DATE column, HIRE_DATE . . . WHERE HIRE_DATE CAST INTERVIEW_DATE...
Controlling Performance of Forced Writes
When InterBase performs forced writes also referred to as synchronous writes , it physically writes data to disk whenever the database performs an internal write operation. If forced writes are not enabled, then even though InterBase performs a write, the data may not be physically written to disk, because operating systems buffer disk writes. If there is a system failure before the data is written to disk, then information can be lost. Performing forced writes ensures data integrity and...
Set Autoddl
Specifies whether DDL statements are committed automatically after being executed or committed only after an explicit COMMIT. ON Turns on automatic commitment of DDL default . OFF Turns off automatic commitment of DDL. Description SET AUTODDL is used to turn on or off the automatic commitment of data definition language DDL statements. By default, DDL statements are automatically committed immediately after they are executed, in a separate transaction. This is the recommended behavior. If the...
Set Stats
Specifies whether to display performance statistics after the results of a query. Turns on display of performance statistics. Turns off display of performance statistics default . Description By default, when a SELECT statement retrieves rows from a query, ISQL does not display performance statistics after the results. Use SET STATS ON to change the default behavior and display performance statistics. To restore the default behavior, use SET STATS OFF. Performance statistics include Current...
Ending the ISQL Session
Whenever you finish your work with ISQL, you should commit it to make it per- pg manent. Choose File I Commit Work. If you want to continue the tutorial, do not exit Windows ISQL continue to the next chapter. If you've had enough for now, you can end your ISQL session by choosing File I Exit to disconnect from the database and exit ISQL. If you want to keep Windows ISQL running, you can choose File I Disconnect from Database to disconnect from the database only. Now that you have gained some...